/// SQLite3 3.6.23.1 embedded Database engine
// - this unit is a part of the freeware Synopse SQLite3 database framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.7
unit SQLite3;

{
    This file is part of Synopse SQLite3 database framework.

    Synopse SQLite3 database framework. Copyright (C) 2010 Arnaud Bouchez
      Synopse Informatique - http://synopse.info
................................................................................
  the provisions above, a recipient may use your version of this file under
  the terms of any one of the MPL, the GPL or the LGPL.

  ***** END LICENSE BLOCK *****



       SQLite3 3.6.23.1 database engine
      **********************************

     Brand new SQLite3 library to be used with Delphi
    - FLEXIBLE: in process, local or remote access (JSON RESTFUL HTTP server)
    - STANDARD: full UTF-8 and Unicode, SQLite3 engine (enhanced but not hacked)
    - PRECISE: individual records can be locked for update
    - SECURE: tested, multi-thread oriented, atomic commit, encryption ready
    - SIMPLE: staticaly linked into a single Delphi unit (no external dll required)
................................................................................
    - SQLite3 database layer updated to version 3.6.23.1

    Version 1.7
    - alter table with newly added fields to a TSQLRecord

    Version 1.8
    - includes Unitary Testing class and functions




    Todo:
    - port to other systems than Delphi+Win32 (use external DLL?)

}

interface
................................................................................
    {{ get all table names contained in this database file }
    procedure GetTableNames(var Names: TRawUTF8DynArray);
    {{ get all field names for a specified Table }
    procedure GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
    {{ retrieve the user_version stored in the SQLite3 database file
      - user-version is a 32-bit signed integers stored in the database header }
    function user_version: cardinal;













    {{ open a BLOB incrementally for read[/write] access
     - find a BLOB located in row RowID, column ColumnName, table TableName
      in database DBName; in other words, the same BLOB that would be selected by:
      ! SELECT ColumnName FROM DBName.TableName WHERE rowid = RowID;
     - use after a TSQLRequest.BindZero() to reserve Blob memory
     - if RowID=-1, then the last inserted RowID is used
     - will raise an ESQLException on any error }
................................................................................
  - Applications should finalize all prepared statements and close all BLOB handles
    associated with the sqlite3 object prior to attempting to close the object
    (sqlite3_next_stmt() interface can be used for this task)
  - if invoked while a transaction is open, the transaction is automatically rolled back }
function sqlite3_close(DB: TSQLHandle): integer; {$ifdef USEC}cdecl;{$endif} external;

{{ Return the version of the SQLite database engine, in ascii format
  - currently returns '3.6.23.1' }
function sqlite3_libversion: PUTF8Char; {$ifdef USEC}cdecl;{$endif} external;

{{ Returns English-language text that describes an error,
   using UTF-8 encoding (which, with English text, is the same as Ansi).
  - Memory to hold the error message string is managed internally.
     The application does not need to worry about freeing the result.
     However, the error string might be overwritten or deallocated by
................................................................................

  /// this test case will test most functions, classes and types defined and
  // implemented in the SQLite3 unit, i.e. the SQLite3 engine itself,
  // with a memory-based approach
  // - this class will also test the TSQLRestServerStatic class, and its
  // 100% Delphi simple database engine
  TTestMemoryBased = class(TTestSQLite3Engine);








implementation



{ ************ direct access to sqlite3.c / sqlite3.obj consts and functions }
{
................................................................................
 - the following defines must be added in the beginning of the sqlite3.c file:

//#define SQLITE_ENABLE_FTS3
//  this unit is FTS3-ready, but not compiled with it by default
//  but for FTS3 to compile, you will have to change:
//    isspace -> sqlite3Isspace in fts3isspace() function,
//    tolower -> sqlite3Tolower in simpleNext() function,
//    isalnum -> sqlite3Isalnum in simpleCreate() function

//  this conditional is defined at compile time, in order to create sqlite3fts3.obj
#define SQLITE_DEFAULT_MEMSTATUS 0
//  don't need any debug here
#define SQLITE_THREADSAFE 0
//  assuming multi-thread safety is made by caller
#define SQLITE_OMIT_SHARED_CACHE
// no need of shared cache in a threadsafe calling model
#define SQLITE_OMIT_AUTOINIT
//  sqlite3_initialize() is done in initialization section below -> no AUTOINIT
#define SQLITE_OMIT_DEPRECATED
//  spare some code size
#define SQLITE_OMIT_TRACE 1
// trace is time consuming (at least under windows)
#define SQLITE_OMIT_LOAD_EXTENSION
// we don't need extension in an embedded engine






//#define SQLITE_OMIT_LOOKASIDE
// since we use FastMM4, LookAside is not needed but seems mandatory in c source

and, in the sqlite3.c source file, the following functions are made external
in order to allow our proprietary but simple and efficient encryption system:

extern int winRead(
................................................................................
  FileClose(F);
end;

// we override default WinRead() and WinWrite() functions below, in order
// to add our proprietary (but efficient) encryption engine

type
{$A4} // C alignment is 4 bytes
  TSQLFile = record // called winFile (expand sqlite3_file) in sqlite3.c
    pMethods: pointer;     // sqlite3_io_methods_ptr

    h: THandle;            // Handle for accessing the file
    bulk: cardinal;        // lockType+sharedLockByte are word-aligned
    lastErrno: cardinal;   // The Windows errno from the last I/O error
    // asm code generated from c is [esi+12] for lastErrNo -> OK
  end;
{$A+}

function WinWrite(var F: TSQLFile; buf: PByte; buflen: integer; off: Int64): integer; {$ifdef USEC}cdecl;{$endif}
// Write data from a buffer into a file.  Return SQLITE_OK on success
// or some other error code on failure
var n: integer;
................................................................................
    Execute('PRAGMA user_version',tmp);
    result := tmp;
  except
    on Exception do
      result := 0;
  end;
end;












{ TSQLRequest }

procedure TSQLRequest.Bind(Param: Integer; Value: Int64);
begin
  sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,Param,Value));
end;
................................................................................

{ ************ Unit-Testing classes and functions }

{ TTestSQLite3 }

procedure TTestSQLite3.Sqlite3DatabaseEngine;
begin
  AddCase([TTestFileBased,TTestMemoryBased]);
end;

{ TTestSQLite3Engine }

destructor TTestSQLite3Engine.Destroy;
begin
  Demo.Free;
................................................................................
    Owner.CustomVersions := Owner.CustomVersions+#13#10'SQlite3 engine used: '+
      sqlite3_libversion;
  IsMemory := InheritsFrom(TTestMemoryBased);
  if IsMemory then
    TempFileName := ':memory:' else begin
    TempFileName := 'test.db3';
    DeleteFile(pointer(TempFileName)); // use a temporary file
  end;
  if not IsMemory then
    CreateSQLEncryptTable('password1');

  Demo := TSQLDataBase.Create(TempFileName);
  Demo.UseCache := true; // use the cache for the JSON requests


  Demo.Execute(
    ' CREATE TABLE IF NOT EXISTS People (' +
    ' ID INTEGER PRIMARY KEY,'+
    ' FirstName TEXT COLLATE SYSTEMNOCASE,' +
    ' LastName TEXT,' +
    ' Data BLOB,'+
    ' YearOfBirth INTEGER,' +
................................................................................
  if not IsMemory then begin // check file encryption password change
    FreeAndNil(Demo); // if any exception occurs in Create(), Demo.Free is OK
    ChangeSQLEncryptTablePassWord(TempFileName,'password1','');
    ChangeSQLEncryptTablePassWord(TempFileName,'','NewPass');
    CreateSQLEncryptTable('NewPass');
    Demo := TSQLDataBase.Create(TempFileName); // reuse the temporary file
    Demo.UseCache := true; // use the cache for the JSON requests


    Check(Hash32(Demo.ExecuteJSON(Req))=$56CB147E,'ExecuteJSON crypted');
  end;
  Demo.GetTableNames(Names);
  Check(length(Names)=1);
  Check(Names[0]='People');
end;

/// SQLite3 3.7 draft embedded Database engine
// - this unit is a part of the freeware Synopse SQLite3 database framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.8
unit SQLite3;

{
    This file is part of Synopse SQLite3 database framework.

    Synopse SQLite3 database framework. Copyright (C) 2010 Arnaud Bouchez
      Synopse Informatique - http://synopse.info
................................................................................
  the provisions above, a recipient may use your version of this file under
  the terms of any one of the MPL, the GPL or the LGPL.

  ***** END LICENSE BLOCK *****



       SQLite3 3.7 database engine
      *****************************

     Brand new SQLite3 library to be used with Delphi
    - FLEXIBLE: in process, local or remote access (JSON RESTFUL HTTP server)
    - STANDARD: full UTF-8 and Unicode, SQLite3 engine (enhanced but not hacked)
    - PRECISE: individual records can be locked for update
    - SECURE: tested, multi-thread oriented, atomic commit, encryption ready
    - SIMPLE: staticaly linked into a single Delphi unit (no external dll required)
................................................................................
    - SQLite3 database layer updated to version 3.6.23.1

    Version 1.7
    - alter table with newly added fields to a TSQLRecord

    Version 1.8
    - includes Unitary Testing class and functions
    - update engine to version 3.7 draft (snapshot as of 2010-06-25 11:35 UTC)
    - SetWALMode() method for enabling Write-Ahead Logging for the database
    - the RTREE extension is now compiled by default into the engine

    Todo:
    - port to other systems than Delphi+Win32 (use external DLL?)

}

interface
................................................................................
    {{ get all table names contained in this database file }
    procedure GetTableNames(var Names: TRawUTF8DynArray);
    {{ get all field names for a specified Table }
    procedure GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
    {{ retrieve the user_version stored in the SQLite3 database file
      - user-version is a 32-bit signed integers stored in the database header }
    function user_version: cardinal;
    {{ enable the Write-Ahead Logging for the database
      - beginning with version 3.7.0 of the SQLite3 engine, a new "Write-Ahead Log"
        option (hereafter referred to as "WAL") is available
      - WAL might be very slightly slower (perhaps 1% or 2% slower) than the
        traditional rollback-journal approach in applications that do mostly reads
        and seldom write; but WAL provides more concurrency as readers do not block
        writers and a writer does not block readers. Reading and writing can
        proceed concurrently. With our SQLite3 framework, it's not needed.
      - by default, this option is not set: only implement if you really need it,
        but our SQlite3 framework use locked actions, so there should be no
        benefit  }
    procedure SetWALMode(Value: Boolean);
                 
    {{ open a BLOB incrementally for read[/write] access
     - find a BLOB located in row RowID, column ColumnName, table TableName
      in database DBName; in other words, the same BLOB that would be selected by:
      ! SELECT ColumnName FROM DBName.TableName WHERE rowid = RowID;
     - use after a TSQLRequest.BindZero() to reserve Blob memory
     - if RowID=-1, then the last inserted RowID is used
     - will raise an ESQLException on any error }
................................................................................
  - Applications should finalize all prepared statements and close all BLOB handles
    associated with the sqlite3 object prior to attempting to close the object
    (sqlite3_next_stmt() interface can be used for this task)
  - if invoked while a transaction is open, the transaction is automatically rolled back }
function sqlite3_close(DB: TSQLHandle): integer; {$ifdef USEC}cdecl;{$endif} external;

{{ Return the version of the SQLite database engine, in ascii format
  - currently returns '3.7' }
function sqlite3_libversion: PUTF8Char; {$ifdef USEC}cdecl;{$endif} external;

{{ Returns English-language text that describes an error,
   using UTF-8 encoding (which, with English text, is the same as Ansi).
  - Memory to hold the error message string is managed internally.
     The application does not need to worry about freeing the result.
     However, the error string might be overwritten or deallocated by
................................................................................

  /// this test case will test most functions, classes and types defined and
  // implemented in the SQLite3 unit, i.e. the SQLite3 engine itself,
  // with a memory-based approach
  // - this class will also test the TSQLRestServerStatic class, and its
  // 100% Delphi simple database engine
  TTestMemoryBased = class(TTestSQLite3Engine);

    /// this test case will test most functions, classes and types defined and
  // implemented in the SQLite3 unit, i.e. the SQLite3 engine itself,
  // with a file-based approach
  // - purpose of this class is to test Write-Ahead Logging for the database
  TTestFileBasedWAL = class(TTestFileBased);


implementation



{ ************ direct access to sqlite3.c / sqlite3.obj consts and functions }
{
................................................................................
 - the following defines must be added in the beginning of the sqlite3.c file:

//#define SQLITE_ENABLE_FTS3
//  this unit is FTS3-ready, but not compiled with it by default
//  but for FTS3 to compile, you will have to change:
//    isspace -> sqlite3Isspace in fts3isspace() function,
//    tolower -> sqlite3Tolower in simpleNext() function,
//    isalnum -> sqlite3Isalnum in simpleCreate() function,
//  if you don't use FTS3, dont define this conditional: you'll spare 50KB of code
//  this conditional is defined at compile time, in order to create sqlite3fts3.obj
#define SQLITE_DEFAULT_MEMSTATUS 0
//  don't need any debug here
#define SQLITE_THREADSAFE 0
//  assuming multi-thread safety is made by caller
#define SQLITE_OMIT_SHARED_CACHE 1
// no need of shared cache in a threadsafe calling model
#define SQLITE_OMIT_AUTOINIT 1
//  sqlite3_initialize() is done in initialization section below -> no AUTOINIT
#define SQLITE_OMIT_DEPRECATED 1
//  spare some code size
#define SQLITE_OMIT_TRACE 1
// we don't need sqlite3_profile() and sqlite3_trace() interfaces
#define SQLITE_OMIT_LOAD_EXTENSION 1
// we don't need extension in an embedded engine
#define SQLITE_OMIT_COMPILEOPTION_DIAGS 1
// we don't need Compilation Options Diagnostics in our embedded engine
#define SQLITE_OMIT_PROGRESS_CALLBACK 1
// we don't need sqlite3_progress_handler() API function
#define SQLITE_ENABLE_RTREE 1
// the RTREE extension is now (from v.1.8/3.7) compiled into the engine
//#define SQLITE_OMIT_LOOKASIDE
// since we use FastMM4, LookAside is not needed but seems mandatory in c source

and, in the sqlite3.c source file, the following functions are made external
in order to allow our proprietary but simple and efficient encryption system:

extern int winRead(
................................................................................
  FileClose(F);
end;

// we override default WinRead() and WinWrite() functions below, in order
// to add our proprietary (but efficient) encryption engine

type
{$A4} // bcc32 default alignment is 4 bytes
  TSQLFile = record // called winFile (expand sqlite3_file) in sqlite3.c
    pMethods: pointer;     // sqlite3_io_methods_ptr
    pVfs: pointer;         // The VFS used to open this file (new in version 3.7)
    h: THandle;            // Handle for accessing the file
    bulk: cardinal;        // lockType+sharedLockByte are word-aligned
    lastErrno: cardinal;   // The Windows errno from the last I/O error
    // asm code generated from c is [esi+16] for lastErrNo -> OK
  end;
{$A+}

function WinWrite(var F: TSQLFile; buf: PByte; buflen: integer; off: Int64): integer; {$ifdef USEC}cdecl;{$endif}
// Write data from a buffer into a file.  Return SQLITE_OK on success
// or some other error code on failure
var n: integer;
................................................................................
    Execute('PRAGMA user_version',tmp);
    result := tmp;
  except
    on Exception do
      result := 0;
  end;
end;

procedure TSQLDataBase.SetWALMode(Value: Boolean);
 const CMD: array[boolean] of RawUTF8 = ('DELETE;','WAL;');
 begin
   try
     Execute('PRAGMA journal_mode='+CMD[value]);
  except
    on Exception do
      ;
  end;
end;

{ TSQLRequest }

procedure TSQLRequest.Bind(Param: Integer; Value: Int64);
begin
  sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,Param,Value));
end;
................................................................................

{ ************ Unit-Testing classes and functions }

{ TTestSQLite3 }

procedure TTestSQLite3.Sqlite3DatabaseEngine;
begin
  AddCase([TTestFileBased,TTestFileBasedWAL,TTestMemoryBased]);
end;

{ TTestSQLite3Engine }

destructor TTestSQLite3Engine.Destroy;
begin
  Demo.Free;
................................................................................
    Owner.CustomVersions := Owner.CustomVersions+#13#10'SQlite3 engine used: '+
      sqlite3_libversion;
  IsMemory := InheritsFrom(TTestMemoryBased);
  if IsMemory then
    TempFileName := ':memory:' else begin
    TempFileName := 'test.db3';
    DeleteFile(pointer(TempFileName)); // use a temporary file


    CreateSQLEncryptTable('password1');
  end;
  Demo := TSQLDataBase.Create(TempFileName);
  Demo.UseCache := true; // use the cache for the JSON requests
  if InheritsFrom(TTestFileBasedWAL) then
    Demo.SetWALMode(true); // test Write-Ahead Logging for the database
  Demo.Execute(
    ' CREATE TABLE IF NOT EXISTS People (' +
    ' ID INTEGER PRIMARY KEY,'+
    ' FirstName TEXT COLLATE SYSTEMNOCASE,' +
    ' LastName TEXT,' +
    ' Data BLOB,'+
    ' YearOfBirth INTEGER,' +
................................................................................
  if not IsMemory then begin // check file encryption password change
    FreeAndNil(Demo); // if any exception occurs in Create(), Demo.Free is OK
    ChangeSQLEncryptTablePassWord(TempFileName,'password1','');
    ChangeSQLEncryptTablePassWord(TempFileName,'','NewPass');
    CreateSQLEncryptTable('NewPass');
    Demo := TSQLDataBase.Create(TempFileName); // reuse the temporary file
    Demo.UseCache := true; // use the cache for the JSON requests
    if InheritsFrom(TTestFileBasedWAL) then
      Demo.SetWALMode(true); // test Write-Ahead Logging for the database
    Check(Hash32(Demo.ExecuteJSON(Req))=$56CB147E,'ExecuteJSON crypted');
  end;
  Demo.GetTableNames(Names);
  Check(length(Names)=1);
  Check(Names[0]='People');
end;

      methods)
    - some compatibility fixes for Delphi 2009/2010
    - fixed bug: negative numbers were not updated when calling *.Update()

    Version 1.8
    - includes Unitary Testing class and functions
    - fixed bug in TSQLTable.GetJSONValues: FirstRow parameter not used




  String usage in the Synopse SQLite3 database framework:
    - RawUTF8 is used for every internal data usage, since both SQLite3 and
     JSON do expect UTF-8 encoding
    - WinAnsiString where WinAnsi-encoded AnsiString (code page 1252) are needed

      methods)
    - some compatibility fixes for Delphi 2009/2010
    - fixed bug: negative numbers were not updated when calling *.Update()

    Version 1.8
    - includes Unitary Testing class and functions
    - fixed bug in TSQLTable.GetJSONValues: FirstRow parameter not used
    - update engine to version 3.7 draft (snapshot as of 2010-06-25 11:35 UTC)



  String usage in the Synopse SQLite3 database framework:
    - RawUTF8 is used for every internal data usage, since both SQLite3 and
     JSON do expect UTF-8 encoding
    - WinAnsiString where WinAnsi-encoded AnsiString (code page 1252) are needed

/// HTTP/1.1 RESTFUL JSON SQLite3 Server classes
// - this unit is a part of the freeware Synopse SQLite3 database framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.6
unit SQLite3HttpServer;

{
    This file is part of Synopse SQLite3 database framework.

    Synopse SQLite3 database framework. Copyright (C) 2010 Arnaud Bouchez
      Synopse Informatique - http://synopse.info
................................................................................
end;

{ TTestClientServerAccess }

procedure TTestClientServerAccess._TSQLite3HttpClient;
var Resp: TSQLTable;
begin
  Client := TSQLite3HttpClient.Create('localhost','888',Model);
  Resp := Client.List([TSQLRecordPeople],'*');
  if Check(Resp<>nil) then
    exit;
  try
    Check(Resp.InheritsFrom(TSQLTableJSON));
    Check(Hash32(TSQLTableJSON(Resp).JSONData)=$1AAF56E9);
  finally

/// HTTP/1.1 RESTFUL JSON SQLite3 Server classes
// - this unit is a part of the freeware Synopse SQLite3 database framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.8
unit SQLite3HttpServer;

{
    This file is part of Synopse SQLite3 database framework.

    Synopse SQLite3 database framework. Copyright (C) 2010 Arnaud Bouchez
      Synopse Informatique - http://synopse.info
................................................................................
end;

{ TTestClientServerAccess }

procedure TTestClientServerAccess._TSQLite3HttpClient;
var Resp: TSQLTable;
begin
  Client := TSQLite3HttpClient.Create('127.0.0.1','888',Model);
  Resp := Client.List([TSQLRecordPeople],'*');
  if Check(Resp<>nil) then
    exit;
  try
    Check(Resp.InheritsFrom(TSQLTableJSON));
    Check(Hash32(TSQLTableJSON(Resp).JSONData)=$1AAF56E9);
  finally