with no data (as expected by TSQLRestClientURI.UpdateFromServer)
  - renamed ESQLException into ESQLite3Exception
  - engine is now compiled including tracing within the FTS3 extension - added
    sqlite3_trace() function prototype to register your own tracing callback

  Version 1.17
  - updated SQLite3 engine to version 3.7.13

  - added TSQLDataBase.CacheFlush method (needed by SQLite3DB)
  - added TSQLDataBase.Synchronous and TSQLDataBase.WALMode properties
  - added TSQLDataBase.ExecuteNoException() overloaded methods
  - fixed ticket [8dc4d49ea9] in TSQLDataBase.GetFieldNames()about result array
    truncated to 64

    Todo:
................................................................................
end;

// we override default WinRead() and WinWrite() functions below, in order
// to add our proprietary (but efficient) encryption engine
// - should be modified to match other Operating Systems than Windows

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
................................................................................
function TSQLDataBase.LastInsertRowID: Int64;
begin
  if (self=nil) or (DB=0) then
    result := 0 else
    try
      Lock('');
      result := sqlite3_last_insert_rowid(DB);




      fLog.Log(sllDB,'LastInsertRowID=%',result,self);


    finally
      UnLock;
    end;
end;

function TSQLDataBase.LastChangeCount: integer;
begin
  if (self=nil) or (DB=0) then
    result := 0 else
    try
      Lock('');
      result := sqlite3_changes(DB);




      fLog.Log(sllDB,'LastChangeCount=%',result,self);


    finally
      UnLock;
    end;
end;

procedure TSQLDataBase.GetTableNames(var Names: TRawUTF8DynArray);
begin // SQL statement taken from official SQLite3 FAQ
  SetLength(Names,Execute(SQL_GET_TABLE_NAMES,Names));


  fLog.Log(sllDebug,'TableNames',TypeInfo(TRawUTF8DynArray),Names,self);


end;

procedure TSQLDataBase.GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
var R: TSQLRequest;
    n: integer;
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  Lock('');
  try
    try
      R.Prepare(fDB,FormatUTF8('PRAGMA table_info(%);',[TableName])); // ESQLite3Exception
      n := 0;
      repeat
        if R.Step<>SQLITE_ROW then break;
        if n=length(Names) then
          SetLength(Names,n+MAX_SQLFIELDS);
        Names[n] := sqlite3_column_text(R.Request,1); // cid,name,type,notnull,dflt_value,pk
        inc(n);
................................................................................
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  EnterCriticalSection(fLock); // cache access is also protected by fLock
  if isSelect(pointer(aSQL)) then begin
    result := fCache.Find(aSQL,aResultCount); // try to get JSON result from cache
    if result<>'' then begin

      fLog.Log(sllSQL,aSQL,self);
      fLog.Log(sllCache,'from cache',self);
      fLog.Log(sllResult,result,self);

      LeaveCriticalSection(fLock); // found in cache -> leave critical section


    end else
      fLog.Log(sllCache,'not in cache',self);

  end else begin
    // UPDATE, INSERT or any non SELECT statement
    CacheFlush;
    result := '';
  end;
end;

procedure TSQLDataBase.UnLockJSON(const aJSONResult: RawUTF8; aResultCount: PtrInt);
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server

  fLog.Log(sllResult,aJSONResult,self);

  fCache.Add(aJSONResult,aResultCount); // if a reset was made just before, Add() does nothing
  UnLock; // leave fLock
end;

function TSQLDataBase.Backup(const BackupFileName: TFileName): boolean;
begin
  fLog.Enter(self);
................................................................................
    try
      fLog.Log(sllTrace,'close',self);
      DBClose;
      fLog.Log(sllTrace,'copy file',self);
      result := CopyFile(pointer(fFileName),pointer(BackupFileName),false);
    finally
      fLog.Log(sllTrace,'reopen',self);
      DBOpen; 
    end;
  finally
    UnLock;
  end;
end;

procedure TSQLDataBase.DBClose;
................................................................................
  fLog.Enter;
  if Cyphers<>nil then
    i := Cypher.Find(PSQLDBStruct(fDB)^.DB0^.Btree^.pBt^.pPager^.fd^.h) else
    i := -1;
  sqlite3_close(fDB);
  if i>=0 then begin
    Cypher.Delete(i); // do it after file closing

    fLog.Log(sllDB,'end of encryption');

  end;
  fDB := 0;
end;

function TSQLDataBase.DBOpen: integer;
var utf8: RawUTF8;
    i: integer;
    Cyph: TSQLCypher;
begin
  fLog.Enter;
  utf8 := StringToUTF8(fFileName);
  result := sqlite3_open(pointer(utf8),fDB);
  if result<>SQLITE_OK then begin

    fLog.Log(sllError,'open("%") failed',utf8,self);

    sqlite3_close(fDB); // should always be closed, even on failure
    fDB := 0;
    exit;
  end;
  Cyph.Handle := PSQLDBStruct(fDB)^.DB0^.Btree^.pBt^.pPager^.fd^.h;
  fLog.Log(sllDB,'open("%") with handle=%',[utf8,Cyph.Handle],self);
  if fCypherBlock<>'' then begin
    if Cyphers=nil then begin
      Cypher.Init(TypeInfo(TSQLCypherDynArray),Cyphers,@CypherCount);
      Cypher.Compare := SortDynArrayInteger;
    end;
    i := Cypher.Find(Cyph.Handle);
    if i>=0 then

      fLog.Log(sllError,'Handle reused for %',utf8) else begin


      Cyph.CypherBuf := fCypherBlock;
      Cypher.Add(Cyph);

      fLog.Log(sllDB,'encryption enabled');

    end;
  end;
  // the SQLite3 standard NOCASE collation is used for AnsiString and is very fast
  // our custom fast UTF-8 case insensitive compare, using NormToUpper[] for all 8 bits values
  sqlite3_create_collation(DB,'SYSTEMNOCASE',SQLITE_UTF8,nil,Utf8SQLCompNoCase);
  // our custom fast ISO-8601 date time encoded
  sqlite3_create_collation(DB,'ISO8601',SQLITE_UTF8,nil,Utf8SQLDateTime);
................................................................................
begin
  ExecuteNoException('PRAGMA user_version',tmp);
  result := tmp;
end;

procedure TSQLDataBase.SetUserVersion(const Value: cardinal);
begin
  ExecuteNoException(FormatUTF8('PRAGMA user_version=%;',[Value]));
end;

procedure TSQLDataBase.SetSynchronous(const Value: TSQLSynchronousMode);
begin
  ExecuteNoException(FormatUTF8('PRAGMA synchronous=%;',[ord(Value)]));
end;

function TSQLDataBase.GetSynchronous: TSQLSynchronousMode;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA synchronous ',tmp);
  result := TSQLSynchronousMode(tmp);
................................................................................
  result := IdemPropNameU(tmp,'wal');
end;

procedure TSQLDataBase.SetBusyTimeout(const ms: Integer);
begin
  if self=nil then
    exit;




  fLog.Log(sllDB,'SetBusyTimeout=%',ms,self);


  sqlite3_busy_timeout(DB,ms);
  fBusyTimeout := ms;
end;

procedure TSQLDataBase.CacheFlush;
begin
  if self=nil then
    exit;
  if InternalState<>nil then
    inc(InternalState^);
  if fCache.Reset then

    fLog.Log(sllCache,'cache flushed',self);

end;

procedure TSQLDataBase.RegisterSQLFunction(aFunction: TSQLDataBaseSQLFunction);
var i: integer;
begin
  if (self=nil) or (aFunction=nil) then
    exit;
................................................................................
  for i := 0 to fSQLFunctions.Count-1 do
    with TSQLDataBaseSQLFunction(fSQLFunctions.List[i]) do
    if (FunctionParametersCount=aFunction.FunctionParametersCount) and
       IdemPropNameU(FunctionName,aFunction.FunctionName) then begin
      aFunction.Free;
      exit; // already registered with the same name and parameters count
    end;

  fLog.Log(sllDB,'RegisterSQLFunction "%"',aFunction.FunctionName,self);

  fSQLFunctions.Add(aFunction);
  if DB<>0 then
    // DB already opened -> register this custom function
    aFunction.CreateFunction(DB);
end;

procedure TSQLDataBase.RegisterSQLFunction(aDynArrayTypeInfo: pointer;
................................................................................
function TSQLDataBaseSQLFunction.CreateFunction(DB: TSQLite3DB): Integer;
begin
  if self<>nil then begin
    result := sqlite3_create_function_v2(DB,pointer(fSQLName),
      FunctionParametersCount,SQLITE_ANY,self,fInternalFunction,nil,nil,nil);
    {$ifdef WITHLOG}
    if result<>SQLITE_OK then 
      SynSQLite3Log.Add.Log(sllError,'register SQL function %() failed',FunctionName,self);
    {$endif}
  end else
    result := SQLITE_ERROR;
end;


{ TSQLDataBaseSQLFunctionDynArray }

    with no data (as expected by TSQLRestClientURI.UpdateFromServer)
  - renamed ESQLException into ESQLite3Exception
  - engine is now compiled including tracing within the FTS3 extension - added
    sqlite3_trace() function prototype to register your own tracing callback

  Version 1.17
  - updated SQLite3 engine to version 3.7.13
  - allow compilation with Delphi 5
  - added TSQLDataBase.CacheFlush method (needed by SQLite3DB)
  - added TSQLDataBase.Synchronous and TSQLDataBase.WALMode properties
  - added TSQLDataBase.ExecuteNoException() overloaded methods
  - fixed ticket [8dc4d49ea9] in TSQLDataBase.GetFieldNames()about result array
    truncated to 64

    Todo:
................................................................................
end;

// we override default WinRead() and WinWrite() functions below, in order
// to add our proprietary (but efficient) encryption engine
// - should be modified to match other Operating Systems than Windows

type
{$ifndef DELPHI5OROLDER} // Delphi 5 is already aligning records by 4 bytes
{$A4} // bcc32 default alignment is 4 bytes
{$endif}
  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
................................................................................
function TSQLDataBase.LastInsertRowID: Int64;
begin
  if (self=nil) or (DB=0) then
    result := 0 else
    try
      Lock('');
      result := sqlite3_last_insert_rowid(DB);
      {$ifdef WITHLOG}
      {$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,'LastInsertRowID='+Int64ToUTF8(result),self);
      {$else}
      fLog.Log(sllDB,'LastInsertRowID=%',result,self);
      {$endif}
      {$endif}
    finally
      UnLock;
    end;
end;

function TSQLDataBase.LastChangeCount: integer;
begin
  if (self=nil) or (DB=0) then
    result := 0 else
    try
      Lock('');
      result := sqlite3_changes(DB);
      {$ifdef WITHLOG}
      {$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,'LastChangeCount='+Int64ToUTF8(result),self);
      {$else}
      fLog.Log(sllDB,'LastChangeCount=%',result,self);
      {$endif}
      {$endif}
    finally
      UnLock;
    end;
end;

procedure TSQLDataBase.GetTableNames(var Names: TRawUTF8DynArray);
begin // SQL statement taken from official SQLite3 FAQ
  SetLength(Names,Execute(SQL_GET_TABLE_NAMES,Names));
  {$ifdef WITHLOG}
  {$ifndef DELPHI5OROLDER}
  fLog.Log(sllDebug,'TableNames',TypeInfo(TRawUTF8DynArray),Names,self);
  {$endif}
  {$endif}
end;

procedure TSQLDataBase.GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
var R: TSQLRequest;
    n: integer;
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  Lock('');
  try
    try
      R.Prepare(fDB,'PRAGMA table_info('+TableName+');'); // ESQLite3Exception
      n := 0;
      repeat
        if R.Step<>SQLITE_ROW then break;
        if n=length(Names) then
          SetLength(Names,n+MAX_SQLFIELDS);
        Names[n] := sqlite3_column_text(R.Request,1); // cid,name,type,notnull,dflt_value,pk
        inc(n);
................................................................................
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  EnterCriticalSection(fLock); // cache access is also protected by fLock
  if isSelect(pointer(aSQL)) then begin
    result := fCache.Find(aSQL,aResultCount); // try to get JSON result from cache
    if result<>'' then begin
      {$ifdef WITHLOG}
      fLog.Log(sllSQL,aSQL,self);
      fLog.Log(sllCache,'from cache',self);
      fLog.Log(sllResult,result,self);
      {$endif}
      LeaveCriticalSection(fLock); // found in cache -> leave critical section
    end
    {$ifdef WITHLOG}
    else
      fLog.Log(sllCache,'not in cache',self);
    {$endif}
  end else begin
    // UPDATE, INSERT or any non SELECT statement
    CacheFlush;
    result := '';
  end;
end;

procedure TSQLDataBase.UnLockJSON(const aJSONResult: RawUTF8; aResultCount: PtrInt);
begin
  if self=nil then
    exit; // avoid GPF in case of call from a static-only server
  {$ifdef WITHLOG}
  fLog.Log(sllResult,aJSONResult,self);
  {$endif}
  fCache.Add(aJSONResult,aResultCount); // if a reset was made just before, Add() does nothing
  UnLock; // leave fLock
end;

function TSQLDataBase.Backup(const BackupFileName: TFileName): boolean;
begin
  fLog.Enter(self);
................................................................................
    try
      fLog.Log(sllTrace,'close',self);
      DBClose;
      fLog.Log(sllTrace,'copy file',self);
      result := CopyFile(pointer(fFileName),pointer(BackupFileName),false);
    finally
      fLog.Log(sllTrace,'reopen',self);
      DBOpen;
    end;
  finally
    UnLock;
  end;
end;

procedure TSQLDataBase.DBClose;
................................................................................
  fLog.Enter;
  if Cyphers<>nil then
    i := Cypher.Find(PSQLDBStruct(fDB)^.DB0^.Btree^.pBt^.pPager^.fd^.h) else
    i := -1;
  sqlite3_close(fDB);
  if i>=0 then begin
    Cypher.Delete(i); // do it after file closing
    {$ifdef WITHLOG}
    fLog.Log(sllDB,'end of encryption');
    {$endif}
  end;
  fDB := 0;
end;

function TSQLDataBase.DBOpen: integer;
var utf8: RawUTF8;
    i: integer;
    Cyph: TSQLCypher;
begin
  fLog.Enter;
  utf8 := StringToUTF8(fFileName);
  result := sqlite3_open(pointer(utf8),fDB);
  if result<>SQLITE_OK then begin
    {$ifdef WITHLOG}
    fLog.Log(sllError,'open("'+utf8+'") failed',self);
    {$endif}
    sqlite3_close(fDB); // should always be closed, even on failure
    fDB := 0;
    exit;
  end;
  Cyph.Handle := PSQLDBStruct(fDB)^.DB0^.Btree^.pBt^.pPager^.fd^.h;
  //fLog.Log(sllDB,'open("%") with handle=%',[utf8,Cyph.Handle],self);
  if fCypherBlock<>'' then begin
    if Cyphers=nil then begin
      Cypher.Init(TypeInfo(TSQLCypherDynArray),Cyphers,@CypherCount);
      Cypher.Compare := SortDynArrayInteger;
    end;
    i := Cypher.Find(Cyph.Handle);
    if i>=0 then begin
      {$ifdef WITHLOG}
      fLog.Log(sllError,'Handle reused for '+utf8);
      {$endif}
    end else begin
      Cyph.CypherBuf := fCypherBlock;
      Cypher.Add(Cyph);
      {$ifdef WITHLOG}
      fLog.Log(sllDB,'encryption enabled');
      {$endif}
    end;
  end;
  // the SQLite3 standard NOCASE collation is used for AnsiString and is very fast
  // our custom fast UTF-8 case insensitive compare, using NormToUpper[] for all 8 bits values
  sqlite3_create_collation(DB,'SYSTEMNOCASE',SQLITE_UTF8,nil,Utf8SQLCompNoCase);
  // our custom fast ISO-8601 date time encoded
  sqlite3_create_collation(DB,'ISO8601',SQLITE_UTF8,nil,Utf8SQLDateTime);
................................................................................
begin
  ExecuteNoException('PRAGMA user_version',tmp);
  result := tmp;
end;

procedure TSQLDataBase.SetUserVersion(const Value: cardinal);
begin
  ExecuteNoException('PRAGMA user_version='+Int32ToUTF8(Value));
end;

procedure TSQLDataBase.SetSynchronous(const Value: TSQLSynchronousMode);
begin
  ExecuteNoException('PRAGMA synchronous='+Int32ToUTF8(ord(Value)));
end;

function TSQLDataBase.GetSynchronous: TSQLSynchronousMode;
var tmp: Int64;
begin
  ExecuteNoException('PRAGMA synchronous ',tmp);
  result := TSQLSynchronousMode(tmp);
................................................................................
  result := IdemPropNameU(tmp,'wal');
end;

procedure TSQLDataBase.SetBusyTimeout(const ms: Integer);
begin
  if self=nil then
    exit;
  {$ifdef WITHLOG}
  {$ifdef DELPHI5OROLDER}
  fLog.Log(sllDB,'SetBusyTimeout='+Int32ToUTF8(ms),self);
  {$else}
  fLog.Log(sllDB,'SetBusyTimeout=%',ms,self);
  {$endif}
  {$endif}
  sqlite3_busy_timeout(DB,ms);
  fBusyTimeout := ms;
end;

procedure TSQLDataBase.CacheFlush;
begin
  if self=nil then
    exit;
  if InternalState<>nil then
    inc(InternalState^);
  if fCache.Reset then
  {$ifdef WITHLOG}
    fLog.Log(sllCache,'cache flushed',self);
  {$endif}
end;

procedure TSQLDataBase.RegisterSQLFunction(aFunction: TSQLDataBaseSQLFunction);
var i: integer;
begin
  if (self=nil) or (aFunction=nil) then
    exit;
................................................................................
  for i := 0 to fSQLFunctions.Count-1 do
    with TSQLDataBaseSQLFunction(fSQLFunctions.List[i]) do
    if (FunctionParametersCount=aFunction.FunctionParametersCount) and
       IdemPropNameU(FunctionName,aFunction.FunctionName) then begin
      aFunction.Free;
      exit; // already registered with the same name and parameters count
    end;
  {$ifdef WITHLOG}
  fLog.Log(sllDB,'RegisterSQLFunction '+aFunction.FunctionName,self);
  {$endif}
  fSQLFunctions.Add(aFunction);
  if DB<>0 then
    // DB already opened -> register this custom function
    aFunction.CreateFunction(DB);
end;

procedure TSQLDataBase.RegisterSQLFunction(aDynArrayTypeInfo: pointer;
................................................................................
function TSQLDataBaseSQLFunction.CreateFunction(DB: TSQLite3DB): Integer;
begin
  if self<>nil then begin
    result := sqlite3_create_function_v2(DB,pointer(fSQLName),
      FunctionParametersCount,SQLITE_ANY,self,fInternalFunction,nil,nil,nil);
    {$ifdef WITHLOG}
    if result<>SQLITE_OK then 
      SynSQLite3Log.Add.Log(sllError,'register SQL function failed: '+FunctionName,self);
    {$endif}
  end else
    result := SQLITE_ERROR;
end;


{ TSQLDataBaseSQLFunctionDynArray }

    {$ifndef PUREPASCAL}
    comp1,comp2,dec1,dec2: array of byte;
    complen1,complen2: integer;
    {$endif}
begin
  for i := 0 to 1000 do begin
    t := RandomString(i*8);
    s := t;
    UniqueString(AnsiString(s));
    Check(CompressSynLZ(s,true)='synlz');
    Check(CompressSynLZ(s,false)='synlz');
    Check(s=t);
    {$ifndef PUREPASCAL}
    SetLength(comp1,SynLZcompressdestlen(length(s)));
    complen1 := SynLZcompress1asm(Pointer(s),length(s),pointer(comp1));
    Check(complen1<length(comp1));
................................................................................
procedure TTestServiceOrientedArchitecture.ServiceInitialization;
  function Ask(Method, Params: RawUTF8; ExpectedResult: cardinal): RawUTF8;
  var resp,data: RawUTF8;
  begin
    Params := ' [ '+Params+' ]';
    case fClient.Server.ServicesRouting of
    rmREST: begin
      data := Params; UniqueString(AnsiString(data));
      Check(fClient.URI('root/calculator.'+Method,'POST',@resp,nil,@Params).Lo=ExpectedResult);
      if ExpectedResult=200 then begin
        Check(fClient.URI('root/CALCulator.'+Method+'?'+UrlEncode(data),'POST',@data).Lo=ExpectedResult);
        Check(data=resp,'optional URI-encoded-inlined parameters use');
      end;
    end;
    rmJSON_RPC: begin

    {$ifndef PUREPASCAL}
    comp1,comp2,dec1,dec2: array of byte;
    complen1,complen2: integer;
    {$endif}
begin
  for i := 0 to 1000 do begin
    t := RandomString(i*8);
    SetString(s,PAnsiChar(pointer(t)),length(t)); // =UniqueString

    Check(CompressSynLZ(s,true)='synlz');
    Check(CompressSynLZ(s,false)='synlz');
    Check(s=t);
    {$ifndef PUREPASCAL}
    SetLength(comp1,SynLZcompressdestlen(length(s)));
    complen1 := SynLZcompress1asm(Pointer(s),length(s),pointer(comp1));
    Check(complen1<length(comp1));
................................................................................
procedure TTestServiceOrientedArchitecture.ServiceInitialization;
  function Ask(Method, Params: RawUTF8; ExpectedResult: cardinal): RawUTF8;
  var resp,data: RawUTF8;
  begin
    Params := ' [ '+Params+' ]';
    case fClient.Server.ServicesRouting of
    rmREST: begin
      SetString(data,PAnsiChar(pointer(Params)),length(Params)); // =UniqueString
      Check(fClient.URI('root/calculator.'+Method,'POST',@resp,nil,@Params).Lo=ExpectedResult);
      if ExpectedResult=200 then begin
        Check(fClient.URI('root/CALCulator.'+Method+'?'+UrlEncode(data),'POST',@data).Lo=ExpectedResult);
        Check(data=resp,'optional URI-encoded-inlined parameters use');
      end;
    end;
    rmJSON_RPC: begin