{ DISQLite3 example project showing how to check the integrity of a database
  file. It reports any errors encountered or just a single 'ok'.

  This application is suitable for batch processing. The Exit Codes are:

    0: No Error.
    1: Validation Failed.
    4: Command line error.
    8: Some other unexpected error.
  255: User stopped the process.

  This project compiles with both DISQLite3 Pro and DISQLite3 Personal. However,
  'PRAGMA integrity_check' is a no-op in DISQLite3 Personal. The code below
  tests if the integrity check was indeed executed and outputs an error message
  if it was not. For production use, be sure to compile this project with
  DISQLite3 Pro.

  Visit the DISQLite3 Internet site for latest information and updates:

    http://www.yunqa.de/delphi/

  Copyright (c) 2007-2009 Ralf Junker, The Delphi Inspiration <delphi@yunqa.de>

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

{.$DEFINE PauseBeforeQuit}// Default: Off

program DISQLite3_Integrity_Check;

{$APPTYPE CONSOLE}

{$I DI.inc}
{$I DISQLite3.inc}

uses
  {$IFDEF FastMM}FastMM4, {$ENDIF}DISystemcompat, Windows, SysUtils, DISQLite3Api;

var
  DB: TDISQLite3DatabaseHandle = nil;

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

{ Callback function to handle CTRL+C and CTRL+BREAK signals. }

function CtrlHandler(dwCtrlType: DWORD): BOOL; stdcall;
begin
  case dwCtrlType of
    CTRL_C_EVENT, CTRL_BREAK_EVENT, CTRL_CLOSE_EVENT, CTRL_LOGOFF_EVENT, CTRL_SHUTDOWN_EVENT:
      begin
        ExitCode := 255;
        { If the user aborted, interrupt the ongoing database operation. }
        if Assigned(DB) then
          sqlite3_interrupt(DB);
        Result := True;
      end;
  else
    Result := False;
  end;
end;

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

{ The main code. Program execution starts here. }

var
  DbFileName: String = '';
  Limit: String = '';
  s8: Utf8String = '';
  Sql8: Utf8String = '';
  Stmt: TDISQLite3StatementHandle;
begin
  ExitCode := 1; // Assume failure.

  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  {$IFDEF PauseBeforeQuit}
  try
    {$ENDIF PauseBeforeQuit}
    try
      { Initialize the DISQLite3 library prior to using any other DISQLite3
        functionality. See also sqlite3_shutdown() below.}
      sqlite3_initialize;
      try
        SetConsoleCtrlHandler(@CtrlHandler, True);

        WriteLn('SQLite Version ', sqlite3_libversion);
        WriteLn;

        if ParamCount < 1 then
          begin
            ExitCode := 4;
            WriteLn('Usage: ' + ExtractFileName(ParamStr(0)), ' <Database File> [Limit]');
            Exit;
          end;

        DbFileName := ParamStr(1);
        if not FileExists(DbFileName) then
          begin
            WriteLn(DbFileName, ' does not exist');
            Exit;
          end;

        { Open the database. Notice the UTF-8 conversion! }
        sqlite3_check(sqlite3_open(PAnsiChar(sqlite3_encode_utf8(DbFileName)), @DB), DB);
        try
          { Assemble the SQL statement. }
          SQL8 := 'PRAGMA integrity_check';
          if StrToIntDef(Limit, -1) > 0 then
            SQL8 := SQL8 + '(' + utf8string(Limit) + ')';

          { Prepare the statement ... }
          sqlite3_check(sqlite3_prepare_v2(DB, PUtf8Char(SQL8), Length(SQL8), @Stmt, nil), DB);
          if Assigned(Stmt) then
            try
              { Execute the statement. }
              case sqlite3_check(sqlite3_step(Stmt), DB) of

                SQLITE_ROW:
                  begin
                    { Check the result. If no errors are found, a single row with
                      the value 'ok' is returned. }
                    s8 := sqlite3_column_str(Stmt, 0);
                    WriteLn(s8);

                    case sqlite3_check(sqlite3_step(Stmt), DB) of

                      { Single row only: Check if 'ok' and adjust ExitCode. }
                      SQLITE_DONE:
                        begin
                          if s8 = 'ok' then
                            ExitCode := 0;
                        end;

                      { Multiple rows: Write results. }
                      SQLITE_ROW:
                        repeat
                          WriteLn(sqlite3_column_str(Stmt, 0));
                        until sqlite3_check(sqlite3_step(Stmt), DB) <> SQLITE_ROW;

                    end;
                  end;

                SQLITE_DONE:
                  begin
                    ExitCode := 4;
                    { No row returned: Report error that PRAGMA integrity_check
                      was not compiled into the library. }
                    WriteLn('"PRAGMA integrity_check" is available with DISQLite3 Pro only.');
                  end;

              end; // case sqlite3_check(sqlite3_step(Stmt), DB)
            finally
              sqlite3_finalize(Stmt);
            end;

        finally
          sqlite3_check(sqlite3_close(DB), DB);
        end;

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

    except
      { If any unexpected error occured, propagate the ExitCode
        and output an appropriate message. }
      on e: Exception do
        begin
          if ExitCode < 8 then
            ExitCode := 8;
          WriteLn(e.Message);
        end;
    end;

    {$IFDEF PauseBeforeQuit}
  finally
    WriteLn;
    WriteLn('Done - Press ENTER to Exit');
    ReadLn;
  end;
  {$ENDIF PauseBeforeQuit}
end.

