sqlite3_virtualtable_stringlist.dpr

来自「DELPHI 访问SQLITE3 数据库的VCL控件」· DPR 代码 · 共 126 行

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

program SQLite3_VirtualTable;

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

{.$DEFINE Debug}

uses
  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 DEBUG}
  case Arg2 of
    SQLITE_CREATE_VTABLE: WriteLn('CREATE VTABLE authorized');
    SQLITE_DROP_VTABLE: WriteLn('DROP VTABLE authorized');
  end;
  {$ENDIF 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_check(sqlite3_finalize(Stmt), DB);
  end;
end;

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

var
  i: Integer;
  SL: TStringList;
begin
  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;
  except
    on e: Exception do
      WriteLn(e.Message);
  end;

  WriteLn;
  WriteLn('Done - ENTER to exit.');
  ReadLn;
end.