{ ------------------------------------------------------------------------------

  DISQLite3 project to import 20 million records from CSV.
  Use DISQLite3 Pro for best performance.

  Requirements: DIUnicode, available from http://www.yunqa.de

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de

  Copyright (c) 2005-2021 Ralf Junker, Yunqa <delphi@yunqa.de>

------------------------------------------------------------------------------ }

program DISQLite3_20_Million_Import;

{$APPTYPE CONSOLE}
{$I DICompilers.inc}
{$I DISQLite3.inc}

uses
  {$IFDEF FastMM}{$I FastMM_uses.inc}{$ENDIF}
  {$IFDEF HAS_UNITSCOPE}
  Winapi.Windows, System.SysUtils,
  {$ELSE HAS_UNITSCOPE}
  Windows, SysUtils,
  {$ENDIF HAS_UNITSCOPE}
  DICsvParser, // Part of DIUnicode, available from https://www.yunqa.de
  DISQLite3Api;

{$R ..\..\Yunqa.res}

const
  CSV_FILE_NAME = '..\20_million.csv';
  DB_FILE_NAME = '..\20_million_import.db3';
  TRANSACTION_DELTA = 50000;

  {$IFNDEF DISQLite3_Personal}
function ProgressCallback(
  UserData: Pointer): Integer; {$IFDEF DISQLite3_CDECL} cdecl; {$ENDIF}
begin
  Write('.');
  Result := 0;
end;
{$ENDIF !DISQLite3_Personal}

//------------------------------------------------------------------------------

var
  CsvFileSize: Int64;
  DB: sqlite3_ptr;
  e: Extended;
  Stmt: sqlite3_stmt_ptr;
  Count: Integer;
  MemUsed, MemHighWater: Integer;
  Percent: Double;
  tc_start, tc_delta, tc_Index: Cardinal;
  totalsec: Cardinal;
  Parser: TDICsvParser;
begin
  {$IFDEF FastMM}{$I FastMM_init.inc}{$ENDIF}
  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      tc_start := GetTickCount;
      DeleteFile(DB_FILE_NAME);
      Parser := TDICsvParser.Create(nil);
      try
        Parser.SourceFile := CSV_FILE_NAME;
        CsvFileSize := Parser.SourceStream.Size;
        DeleteFile(DB_FILE_NAME);
        sqlite3_check(sqlite3_open(DB_FILE_NAME, @DB));
        try
          { Tweak some DB settings to speed up inserts (DISQLite3 Pro only). }

          { Prevent other users from accessing the database while we are inserting. }
          sqlite3_exec_fast(DB, 'PRAGMA locking_mode=EXCLUSIVE');

          { Set the number of pages the DB engine buffers in memory.
            400000 pages require about 450 MB of memory for this application.
            Increasing this value further will speed up insertions and index
            creation, but will also use more memory. Reduce if you have less
            memory available to avoid OS paging. }
          sqlite3_exec_fast(DB, 'PRAGMA cache_size=400000');

          { Noticably faster inserts at the cost of a little less ACID security. }
          sqlite3_exec_fast(DB, 'PRAGMA synchronous=OFF');

          { Create the table. }
          sqlite3_exec_fast(DB, 'CREATE TABLE t (' +
            't1 TEXT,t2 TEXT,' +
            'i1 INTEGER,' +
            'r1 REAL,r2 REAL,r3 REAL,r4 REAL,r5 REAL,r6 REAL)');

          { Prepare an SQL statement. Prepared statements are the fastes way to
            insert bulk data with DISQLtie3. We will bind values to this statement
            below and then step it once to perform the insert. }
          sqlite3_check(sqlite3_prepare_v2(DB,
            'INSERT INTO t (t1,t2,i1,r1,r2,r3,r4,r5,r6)' +
            'VALUES (?,?,?,?,?,?,?,?,?)', -1, @Stmt, nil), DB);
          try
            Count := 0;
            { Start a transaction to speed up inserts. It will be committed and
              a new transaction started every TRANSACTION_DELTA inserts. }
            sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');

            while Parser.ReadNextData do
              begin
                case Parser.DataCol of
                  0: sqlite3_bind_str(Stmt, 1, sqlite3_encode_utf8(Parser.DataAsStrW));
                  1: sqlite3_bind_str(Stmt, 2, sqlite3_encode_utf8(Parser.DataAsStrW));
                  2: sqlite3_bind_int(Stmt, 3, StrToInt(Parser.DataAsStrW));
                  3: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 4, e);
                  4: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 5, e);
                  5: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 6, e);
                  6: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 7, e);
                  7: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 8, e);
                  8: if SqlStrToFloat(Parser.DataToStrA, e) > 0 then
                      sqlite3_bind_double(Stmt, 9, e);
                end;

                if Parser.RowComplete then
                  begin
                    Inc(Count);
                    sqlite3_check(sqlite3_step(Stmt), DB);
                    sqlite3_check(sqlite3_reset(Stmt), DB);

                    { Every TRANSACTION_DELTA inserts, commit the transaction and
                      start a new one. Also output some performance statistics. }
                    if Count mod TRANSACTION_DELTA = 0 then
                      begin
                        { Commit current transaction. }
                        sqlite3_exec_fast(DB, 'COMMIT');
                        tc_delta := GetTickCount - tc_start;

                        Percent := Parser.SourceStream.Position / CsvFileSize;

                        totalsec := Round(tc_delta / Percent / 1000);
                        WriteLn(Count: 8, ' - ',
                          Count / tc_delta * 1000: 7: 2, ' per sec - ',
                          Percent * 100: 3: 0, '% done - est. total time: ',
                          totalsec div 60, ' min, ', totalsec mod 60, ' sec');
                        { Start new transaction. }
                        sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                      end;
                  end;
              end;

            { Commit current transaction. }
            sqlite3_exec_fast(DB, 'COMMIT');

          finally
            sqlite3_finalize(Stmt);
          end;
        finally
          Parser.FreeSourceStream;
        end;

        WriteLn;
        totalsec := (GetTickCount - tc_start) div 1000;
        WriteLn('Insertion Time: ', totalsec div 60, ' min, ', totalsec mod 60, ' sec');

        {$IFNDEF DISQLite3_Personal}
        sqlite3_progress_handler(DB, 50000, ProgressCallback, nil);
        {$ENDIF !DISQLite3_Personal}

        tc_Index := GetTickCount;
        WriteLn;
        WriteLn('Creating index on date column ... this may take a while ... ');
        sqlite3_exec_fast(DB, 'CREATE INDEX t_i1 ON t (i1)');
        WriteLn;
        totalsec := (GetTickCount - tc_Index) div 1000;
        WriteLn('Index created in ', totalsec div 60, ' min, ', totalsec mod 60, 'sec');

      finally
        Parser.Free;

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

        sqlite3_status(SQLITE_STATUS_MEMORY_USED, @MemUsed, @MemHighWater, 0);

        WriteLn;
        WriteLn('Max database memory usage: ', MemHighWater div 1024 div 1024, ' MB');
      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.

