{ A DISQLite3 virtual table example using a TStringList for data storage. }

program SQLite3_VirtualTable;

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

{.$DEFINE DI_Debug}

uses
  {$IFDEF FastMM}FastMM4, {$ENDIF}SysUtils, Classes,
  DISQLite3Api, DISQLite3VtStringList,
  DISQLite3_Demos_Common in '..\DISQLite3_Common_Units\DISQLite3_Demos_Common.pas';

function AuthorizerCallback(
  UserData: Pointer;
  Arg2: Integer;
  Arg3: PAnsiChar;
  Arg4: PAnsiChar;
  Arg5: PAnsiChar;
  Arg6: PAnsiChar
  ): Integer;
begin
  {$IFDEF DI_Debug}
  case Arg2 of
    SQLITE_CREATE_VTABLE: WriteLn('CREATE VTABLE authorized');
    SQLITE_DROP_VTABLE: WriteLn('DROP VTABLE authorized');
  end;
  {$ENDIF DI_Debug}
  Result := SQLITE_OK;
end;

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

var
  DB: TDISQLite3DatabaseHandle;

procedure WriteResults(const ASql: AnsiString);
var
  Stmt: TDISQLite3StatementHandle;
begin
  WriteLn;
  WriteLn('--------------------------------------------------------------------');
  WriteLn('SQL: ', ASql);

  sqlite3_check(sqlite3_prepare(DB,
    PAnsiChar(ASql), Length(ASql), @Stmt, nil), DB);
  try
    WritePreparedStatement(DB, Stmt);
  finally
    sqlite3_finalize(Stmt);
  end;
end;

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

var
  i: Integer;
  SL: TStringList;
begin
  { Disable FPU exceptions. No need to restore, setting is process specific. }
  Set8087CW($133F);

  try
    { Initialize the DISQLite3 library prior to using any other DISQLite3
      functionality. See also sqlite3_shutdown() below.}
    sqlite3_initialize;
    try
      { Create a TStringList which we pass to sqlite3_create_module. }
      SL := TStringList.Create;
      SL.AddObject('String Zero', TObject(1));

      sqlite3_check(sqlite3_open(':memory:', @DB), DB);
      try
        sqlite3_set_authorizer(DB, AuthorizerCallback, Pointer($1234));

        { This creates a virtual table module, using our SL TStringList for
          data storage. All virtual tables using this module will share this
          TStringList. For multiple string lists, create multiple modules
          with different names or modify DISQLite3VtStringList.pas to handle
          multiple string lists. }
        sqlite3_check(sqlite3_create_module(DB, 'testmodule', @TStringList_Module, SL), DB);

        { This creates the virtual table "tablename". By default, the table will
          have a single column named "Value". This can not be changed. }
        sqlite3_exec_fast(DB, 'CREATE VIRTUAL TABLE tablename USING testmodule;');

        { Uncomment the next line to initialize the stringlist with the contents from a file. }
        // sqlite3_exec_fast(DB, 'CREATE VIRTUAL TABLE tablename USING testmodule(''C:\Temp\Test.txt'');');

        { Now play a little with the new virtual table ... }

        WriteResults('SELECT * FROM tablename;');

        WriteResults('INSERT INTO tablename VALUES (''String One'');');
        WriteResults('SELECT * FROM tablename;');

        WriteResults('INSERT INTO tablename VALUES (''String Two'');');
        WriteResults('SELECT * FROM tablename;');

        WriteResults('begin');
        WriteResults('UPDATE tablename SET Value = ''String One Modified'' WHERE Value = ''String One'';');
        WriteResults('commit');
        WriteResults('SELECT * FROM tablename;');

        { Drop the table after playing. }

        WriteResults('DROP TABLE tablename;');

        { Write out contents of the string list. }
        WriteLn;
        WriteLn('--------------------------------------------------------------------');
        WriteLn('String List now contains:');
        WriteLn;
        for i := 0 to SL.Count - 1 do
          WriteLn(SL[i]);

      finally
        sqlite3_check(sqlite3_close(DB), DB);
        SL.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 - ENTER to exit.');
  ReadLn;
end.

