sqlite3_20_million_export.dpr

最新版本的sqlite封装for delphi

program SQLite3_20_Million_Export;

{$APPTYPE CONSOLE}

{$I SQLite3.inc}

uses
  Windows, SysUtils, Classes,
  SQLite3Lib;

const
  CSV_FILE_NAME = '..\..\20_million.csv';
  DB_FILE_NAME = '..\..\20_million_generate.db3';
  Comma: AnsiChar = ',';
  CRLF: array[0..1] of AnsiChar = (#13, #10);

var
  DB: TSQLiteDB;
  Stmt: TSQLiteStmt;
  i, j: Integer;
  s8: Utf8String = '';
  Stream: TFileStream;
  Buffer: TMemoryStream;
  totalsec: Integer;
  tc_start: Cardinal;
begin
  try
    { Initialize the SQLite3 library prior to using any other SQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      tc_start := GetTickCount;
      Stream := TFileStream.Create(CSV_FILE_NAME, fmCreate);
      try
        sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB));
        try
          sqlite3_check(sqlite3_prepare(DB,
            'SELECT * FROM t ORDER BY i1', -1, @Stmt, nil), DB);
          try
            i := 0;
            Buffer := TMemoryStream.Create; // As Temp buffer.
            try
              while sqlite3_check(sqlite3_step(Stmt), DB) = SQLITE_ROW do
                begin
                  // t1
                  Buffer.Write(sqlite3_column_text(Stmt, 0)^,
                    sqlite3_column_bytes(Stmt, 0));
                  Buffer.Write(Comma, Length(Comma));
                  // t2
                  Buffer.Write(sqlite3_column_text(Stmt, 1)^,
                    sqlite3_column_bytes(Stmt, 1));
                  Buffer.Write(Comma, Length(Comma));
                  // i1
                  Buffer.Write(sqlite3_column_text(Stmt, 2)^,
                    sqlite3_column_bytes(Stmt, 2));
                  // r1 - r6
                  for j := 3 to 8 do
                    begin
                      Buffer.Write(Comma, Length(Comma));
                      s8 := FloatToSqlStr(sqlite3_column_double(Stmt, j), '.', 18, 4);
                      Buffer.Write(Pointer(s8)^, Length(s8));
                    end;

                  Buffer.Write(CRLF, SizeOf(CRLF));

                  Inc(i);
                  if i mod $1000 = 0 then
                    begin
                      { Flush buffer to file every $1000 records. }
                      Stream.Write(Buffer.Memory^, Buffer.Position);
                      Buffer.Seek(0, soFromBeginning);
                      Write('.');
                    end;
                end;
              { Flush remaining buffer. }
              Stream.Write(Buffer.Memory^, Buffer.Position);
            finally
              Buffer.Free;
            end;
          finally
            sqlite3_finalize(Stmt);
          end;
        finally
          Stream.Free;
        end;

      finally
        sqlite3_check(sqlite3_close(DB), DB);
        WriteLn;
        totalsec := (GetTickCount - tc_start) div 1000;
        WriteLn('Total Time: ',
          totalsec div 60, ' min, ', totalsec mod 60, 'sec');
      end;

    finally
      { Deallocate any resources that were allocated by
      sqlite3_initialize() above. }
      sqlite3_shutdown;
    end;

  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - Press ENTER to Exit');
  ReadLn;
end.