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

  DISQLite3 project to import 20 million records from a CSV file using the help
  of a :memory: temporary table to speed up inserts.

  Requirements:

  * Requires DISQLite3 Pro to support attached databases and :memory: databases.
  * 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_Mem_Helper;

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

{$IFDEF DISQLite3_Personal}
!!! This project requires functionality unavailable in DISQLite3 Personal. !!!
!!! To compile, download DISQLite3 Pro from https://www.yunqa.de/delphi/   !!!
{$ENDIF DISQLite3_Personal}

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

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 http://www.yunqa.de
  DISQLite3Api;

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

var
  CsvFileSize: Int64;
  DB: sqlite3_ptr;
  e: Extended;
  Stmt: sqlite3_stmt_ptr;
  Count: Integer;
  MemUsed, MemHighWater: Integer;
  Percent: Double;
  tc_start, tc_delta: 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 main table and index. }
          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)');
          sqlite3_exec_fast(DB, 'CREATE INDEX t_i1 ON t (i1)');

          { Create helper :memory: database and table for fast batch inserts. }
          sqlite3_exec_fast(DB, 'ATTACH '':memory:'' AS m');
          sqlite3_exec_fast(DB, 'CREATE TABLE m.x(' +
            't1 TEXT,t2 TEXT,' +
            'i1 INTEGER,' +
            'r1 REAL,r2 REAL,r3 REAL,r4 REAL,r5 REAL,r6 REAL)');
          { Create an index for the in :memory: helper table. Without an index,
            DISQLite3 will create an on-disk helper file for sorting the
            inserts. }
          sqlite3_exec_fast(DB, 'CREATE INDEX m.x_i1 ON x(i1)');

          { Prepare the helper insert SQL statement. A prepared statements it the
            fastest way to insert bulk data with DISQLtie3. Values will be bound
            to this statement below before it is executed. }
          sqlite3_check(sqlite3_prepare_v2(DB,
            'INSERT INTO x(t1,t2,i1,r1,r2,r3,r4,r5,r6)' +
            'VALUES (?,?,?,?,?,?,?,?,?)', -1, @Stmt, nil), DB);
          try
            Count := 0;
            { Start a transaction to speed up inserts. This transaction will be
              committed and a new one 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
                        { Insert the current batch of records and commit. }
                        sqlite3_exec_fast(DB, 'INSERT INTO t ' +
                          'SELECT * FROM x ORDER by i1');
                        sqlite3_exec_fast(DB, 'DELETE FROM x');
                        sqlite3_exec_fast(DB, 'COMMIT');

                        { Print progress timings and statistics. }
                        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');

                        { Begin new transaction and start over. }
                        sqlite3_exec_fast(DB, 'BEGIN TRANSACTION');
                      end;
                  end;
              end;

            { Insert the final batch and commit. }
            sqlite3_exec_fast(DB, 'INSERT INTO t SELECT * FROM x ORDER by i1');
            sqlite3_exec_fast(DB, 'DELETE FROM x');
            sqlite3_exec_fast(DB, 'COMMIT');

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

      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.

