- TSQLDatabase constructors now accepts an optional Password parameter,
    associated to the supplied file name, in order to use database encryption

  Version 1.16
  - updated SQLite3 engine to version 3.7.9
  - new IsSQLite3FileEncrypted() function
  - new parameter in TSQLDataBase.ExecuteJSON, LockJSON, UnLockJSON methods,
    for an optional integer pointer, to return the count of row data   


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

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

................................................................................
  // local or shared TSQLRequest
  // - the TSQLRequest may be shared and prepared before the call for even
  // faster access than with a local TSQLRequest 
  // - no TSQLDataBase or higher levels objects can be used inside this method,
  // since all locking and try..finally protection is outside it
  // - can optionnaly trigger a ESQLException on any error
  TOnSQLStoredProc = procedure(Statement: TSQLRequest) of object;



























  /// simple wrapper for direct SQLite3 database manipulation
  // - embed the SQLite3 database calls into a common object
  // - thread-safe call of all SQLite3 queries (SQLITE_THREADSAFE 0 in sqlite.c)
  // - can cache last results for SELECT statements, if property UseCache is true:
  //  this can speed up most read queries, for web server or client UI e.g.
  TSQLDataBase = class
................................................................................
    procedure Execute(const aSQL: RawUTF8; out ID: RawUTF8); overload;
    /// Execute one SQL statement returning its results in JSON format
    // - the BLOB data is encoded as '"\uFFF0base64encodedbinary"'
    function ExecuteJSON(const aSQL: RawUTF8; Expand: boolean=false; aResultCount: PPtrInt=nil): RawUTF8;
    {{ begin a transaction
     - Execute SQL statements with Execute() procedure below
     - must be ended with Commit on success
     - must be aborted with Rollback after an ESQLException raised }

    procedure TransactionBegin;
    {{ end a transaction: write all Execute() statements to the disk }
    procedure Commit;
    {{ abort a transaction: restore the previous state of the database }
    procedure RollBack;
    {{ return the last Insert Rowid }
    function LastInsertRowID: Int64;
    {{ get all table names contained in this database file }
................................................................................
begin
  if (self=nil) or not fTransactionActive then
    exit;
  Execute('ROLLBACK TRANSACTION;');
  fTransactionActive := false;
end;

procedure TSQLDataBase.TransactionBegin;



begin
  if self=nil then 
    exit; // avoid GPF in case of call from a static-only server
  if fTransactionActive then begin
    Execute('ROLLBACK TRANSACTION;');
    fTransactionActive := false;
  end;
  Execute('BEGIN TRANSACTION;');
  fTransactionActive := true;
end;

procedure TSQLDataBase.Commit;
begin
  if (Self<>nil) and fTransactionActive then begin
    Execute('COMMIT TRANSACTION;');

  - TSQLDatabase constructors now accepts an optional Password parameter,
    associated to the supplied file name, in order to use database encryption

  Version 1.16
  - updated SQLite3 engine to version 3.7.9
  - new IsSQLite3FileEncrypted() function
  - new parameter in TSQLDataBase.ExecuteJSON, LockJSON, UnLockJSON methods,
    for an optional integer pointer, to return the count of row data
  - added an optional behavior parameter to TSQLDataBase.TransactionBegin method

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

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

................................................................................
  // local or shared TSQLRequest
  // - the TSQLRequest may be shared and prepared before the call for even
  // faster access than with a local TSQLRequest 
  // - no TSQLDataBase or higher levels objects can be used inside this method,
  // since all locking and try..finally protection is outside it
  // - can optionnaly trigger a ESQLException on any error
  TOnSQLStoredProc = procedure(Statement: TSQLRequest) of object;

  {{ TSQLDataBase.TransactionBegin can be deferred, immediate, or exclusive
   - tbDeferred means that no locks are acquired on the database until the
   database is first accessed. Thus with a deferred transaction, the BEGIN
   statement itself does nothing to the filesystem. Locks are not acquired
   until the first read or write operation. The first read operation against
   a database creates a SHARED lock and the first write operation creates a
   RESERVED lock. Because the acquisition of locks is deferred until they are
   needed, it is possible that another thread or process could create a
   separate transaction and write to the database after the BEGIN on the
   current thread has executed.
   - If the transaction is tbImmediate, then RESERVED locks are acquired
   on all databases as soon as the BEGIN command is executed, without waiting
   for the database to be used. After a BEGIN IMMEDIATE, no other database
   connection will be able to write to the database or do a BEGIN IMMEDIATE
   or BEGIN EXCLUSIVE. Other processes can continue to read from the database,
   however.
   - A tbExclusive transaction causes EXCLUSIVE locks to be acquired on all
   databases. After a BEGIN EXCLUSIVE, no other database connection except
   for read_uncommitted connections will be able to read the database and
   no other connection without exception will be able to write the database
   until the transaction is complete. }
  TSQLDataBaseTransactionBehaviour = (
    tbDeferred,
    tbImmediate,
    tbExclusive);

  /// simple wrapper for direct SQLite3 database manipulation
  // - embed the SQLite3 database calls into a common object
  // - thread-safe call of all SQLite3 queries (SQLITE_THREADSAFE 0 in sqlite.c)
  // - can cache last results for SELECT statements, if property UseCache is true:
  //  this can speed up most read queries, for web server or client UI e.g.
  TSQLDataBase = class
................................................................................
    procedure Execute(const aSQL: RawUTF8; out ID: RawUTF8); overload;
    /// Execute one SQL statement returning its results in JSON format
    // - the BLOB data is encoded as '"\uFFF0base64encodedbinary"'
    function ExecuteJSON(const aSQL: RawUTF8; Expand: boolean=false; aResultCount: PPtrInt=nil): RawUTF8;
    {{ begin a transaction
     - Execute SQL statements with Execute() procedure below
     - must be ended with Commit on success
     - must be aborted with Rollback after an ESQLException raised
    - The default transaction behavior is tbDeferred }
    procedure TransactionBegin(aBehavior: TSQLDataBaseTransactionBehaviour = tbDeferred);
    {{ end a transaction: write all Execute() statements to the disk }
    procedure Commit;
    {{ abort a transaction: restore the previous state of the database }
    procedure RollBack;
    {{ return the last Insert Rowid }
    function LastInsertRowID: Int64;
    {{ get all table names contained in this database file }
................................................................................
begin
  if (self=nil) or not fTransactionActive then
    exit;
  Execute('ROLLBACK TRANSACTION;');
  fTransactionActive := false;
end;

procedure TSQLDataBase.TransactionBegin(aBehavior: TSQLDataBaseTransactionBehaviour = tbDeferred);
const
  TBTOKENS: array[TSQLDataBaseTransactionBehaviour] of RawUTF8 = (
    '', 'IMMEDIATE ', 'EXCLUSIVE '); // see http://www.sqlite.org/lang_transaction.html
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  if fTransactionActive then begin
    Execute('ROLLBACK TRANSACTION;');
    fTransactionActive := false;
  end;
  Execute('BEGIN '+TBTOKENS[aBehavior]+'TRANSACTION;');
  fTransactionActive := true;
end;

procedure TSQLDataBase.Commit;
begin
  if (Self<>nil) and fTransactionActive then begin
    Execute('COMMIT TRANSACTION;');