{ This DISQLite3 project demonstrates how to track the number of database rows
  changed (inserted, updated, or deleted) by an application.

  * property TDISQLite3DataBase.Changes: Integer;
      Returns number of database rows that were changed by the most recent
      SQL statement. The SQL function CHANGES() returns the same value.

  * property TDISQLite3DataBase.TotalChanges: Integer;
      Returns the total number of database rows that were changed since the
      database connection was created. The SQL function equivalent is TOTALCHANGES().

  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_Changes;

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

uses
  {$IFDEF FastMM}{$IFDEF COMPILER_6_UP}FastMM4, {$ENDIF}{$ENDIF}
  SysUtils, DISQLite3Api, DISQLite3Database;

{$R ..\Yunqa.res}

const
  INSERT_COUNT = 10000;

var
  DB: TDISQLite3Database;
  Stmt: TDISQLite3Statement;
  i: Integer;
begin
  { Disable all FPU and SSE exceptions. }
  Set8087CW($133F); {$IFDEF CPUX64}SetMXCSR($1F80); {$ENDIF CPUX64}

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      DB := TDISQLite3Database.Create(nil);
      try
        { Create a new test database. }
        DB.DatabaseName := ChangeFileExt(ParamStr(0), '.db3');
        DB.CreateDatabase;

        { Create a new test table. }
        DB.Execute('CREATE TABLE test (a INTEGER PRIMARY KEY, b TEXT);');

        { Insert records into table. }
        Write('Inserting ', INSERT_COUNT, ' records ... ');
        DB.StartTransaction;
        try
          Stmt := DB.Prepare('INSERT INTO test(a, b) VALUES (?, ?);');
          try
            for i := 0 to INSERT_COUNT do
              begin
                Stmt.Bind_Int(1, i);
                Stmt.Bind_Str16(2, IntToStr(i));
                Stmt.StepAndReset;

                { Check that DB.Changes reflects that one record was inserted. }
                if DB.Changes <> 1 then
                  WriteLn('!!! Error: DB.Changes <> 1 !!!');
              end;
          finally
            Stmt.Free;
          end;
          DB.Commit;
          WriteLn('OK');
        except
          { If there was an error: Rollback the transaction. }
          on e: Exception do
            begin
              WriteLn('Error: ', e.Message);
              Write('Rolling back transaction ... ');
              DB.Rollback;
              WriteLn('OK');
            end;
        end;

        { Delete records and output number of records deleted. }
        WriteLn;
        Write('DELETE FROM test WHERE a < 1001; ... ');
        DB.Execute('DELETE FROM test WHERE a < 1001;');
        WriteLn('OK');
        WriteLn(DB.Changes, ' records deleted');

        { Update records and output number of records updated. }
        WriteLn;
        Write('UPDATE test SET b = b || '' updated'' WHERE a < 3003; ... ');
        DB.Execute('UPDATE test SET b = b || '' updated'' WHERE a < 3003;');
        WriteLn('OK');
        WriteLn(DB.Changes, ' records updated');

        { Delete all records: Since this operation is optimized by dropping and
          recreating the table. Because of this optimization, the change count for
          "DELETE FROM table" will be zero regardless of the number of elements
          that were originally in the table. To get an accurate count of the
          number of rows deleted, use "DELETE FROM table WHERE 1" instead. }
        WriteLn;
        Write('DELETE FROM test; ... ');
        DB.Execute('DELETE FROM test;');
        WriteLn('OK');
        WriteLn(DB.Changes, ' records deleted - Surprised? Read source comment for an explanation!');

        { Report the number of total changes to the DB since this DB connection
          was created. }
        WriteLn;
        WriteLn('Total Changes: ', DB.TotalChanges);
      finally
        DB.Free;
      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.

