end;

  {{ A Virtual Table cursor for reading a TSQLDBStatement content
    - this is the cursor class associated to TSQLVirtualTableExternal }
  TSQLVirtualTableCursorExternal = class(TSQLVirtualTableCursor)
  protected
    fStatement: ISQLDBStatement;

    fHasData: boolean;
  public
    /// called to begin a search in the virtual table, creating a SQL query
    // - the TSQLVirtualTablePrepared parameters were set by
    // TSQLVirtualTable.Prepare and will contain both WHERE and ORDER BY statements
    // (retrieved by x_BestIndex from a TSQLite3IndexInfo structure)
    // - Prepared will contain all prepared constraints and the corresponding
................................................................................
    /// called after Search() to check if there is data to be retrieved
    // - should return false if reached the end of matching data
    function HasData: boolean; override;
    /// called to go to the next row of matching data
    // - should return false on low-level database error (but true in case of a
    // valid call, even if HasData will return false, i.e. no data match)
    function Next: boolean; override;


  end;
  
  {{ A SynDB-based virtual table for accessing any external database
   - for ORM access, you should use VirtualTableExternalRegister method to
     associated this virtual table module to any TSQLRecord class
   - transactions are handled by this module, according to the external database }
  TSQLVirtualTableExternal = class(TSQLVirtualTable)
................................................................................
const
  SQL_OPER_WITH_PARAM: array[soEqualTo..soGreaterThanOrEqualTo] of RawUTF8 = (
    '=?','<>?','<?','<=?','>?','>=?');

function TSQLVirtualTableCursorExternal.Search(
  const Prepared: TSQLVirtualTablePrepared): boolean;
var i: integer;
    SQL: RawUTF8;
    Params: TVarDataDynArray;
begin
  result := false;
  if (Self=nil) or (fStatement<>nil) or (Table=nil) or (Table.Static=nil) then
    exit;
  with Table.Static as TSQLRestServerStaticExternal do begin

    // compute the SQL query corresponding to this prepared request
    SQL := fSelectAllDirectSQL;
    if Prepared.WhereCount<>0 then begin
      SetLength(Params,Prepared.WhereCount);
      for i := 0 to Prepared.WhereCount-1 do
      with Prepared.Where[i] do begin
        if Operation>high(SQL_OPER_WITH_PARAM) then
          exit; // invalid specified operator -> abort search
        if i=0 then
          SQL := SQL+' where ' else
          SQL := SQL+' and ';
        if StoredClassRecordProps.AppendFieldName(Column,SQL,true) then
          exit; // invalid column index -> abort search
        SQL := SQL+SQL_OPER_WITH_PARAM[Operation];
        move(Value,Params[i],SizeOf(Value)); // fast copy bound parameter value
      end;
    end;
    // e.g. 'select FirstName,..,ID from PeopleExternal where FirstName=? and LastName=?'
    for i := 0 to Prepared.OrderByCount-1 do
    with Prepared.OrderBy[i] do begin
      if i=0 then
        SQL := SQL+' order by ' else
        SQL := SQL+', ';
      if StoredClassRecordProps.AppendFieldName(Column,SQL,true) then
        exit; // invalid column index -> abort search
      if Desc then
        SQL := SQL+' desc';
    end;

    // execute the SQL statement
    try
      fStatement := fProperties.NewThreadSafeStatementPrepared(SQL,true);
      if fStatement<>nil then begin
        fStatement.Bind(Params);
        fStatement.ExecutePrepared;
        result := Next; // on execution success, go to the first row
      end;
    except
      on Exception do

  end;

  {{ A Virtual Table cursor for reading a TSQLDBStatement content
    - this is the cursor class associated to TSQLVirtualTableExternal }
  TSQLVirtualTableCursorExternal = class(TSQLVirtualTableCursor)
  protected
    fStatement: ISQLDBStatement;
    fSQL: RawUTF8;
    fHasData: boolean;
  public
    /// called to begin a search in the virtual table, creating a SQL query
    // - the TSQLVirtualTablePrepared parameters were set by
    // TSQLVirtualTable.Prepare and will contain both WHERE and ORDER BY statements
    // (retrieved by x_BestIndex from a TSQLite3IndexInfo structure)
    // - Prepared will contain all prepared constraints and the corresponding
................................................................................
    /// called after Search() to check if there is data to be retrieved
    // - should return false if reached the end of matching data
    function HasData: boolean; override;
    /// called to go to the next row of matching data
    // - should return false on low-level database error (but true in case of a
    // valid call, even if HasData will return false, i.e. no data match)
    function Next: boolean; override;
    /// read-only access to the SELECT statement
    property SQL: RawUTF8 read fSQL;
  end;
  
  {{ A SynDB-based virtual table for accessing any external database
   - for ORM access, you should use VirtualTableExternalRegister method to
     associated this virtual table module to any TSQLRecord class
   - transactions are handled by this module, according to the external database }
  TSQLVirtualTableExternal = class(TSQLVirtualTable)
................................................................................
const
  SQL_OPER_WITH_PARAM: array[soEqualTo..soGreaterThanOrEqualTo] of RawUTF8 = (
    '=?','<>?','<?','<=?','>?','>=?');

function TSQLVirtualTableCursorExternal.Search(
  const Prepared: TSQLVirtualTablePrepared): boolean;
var i: integer;

    Params: TVarDataDynArray;
begin
  result := false;
  if (Self=nil) or (Table=nil) or (Table.Static=nil) then
    exit;
  with Table.Static as TSQLRestServerStaticExternal do begin
    if fSQL='' then begin
      // compute the SQL query corresponding to this prepared request
      fSQL := fSelectAllDirectSQL;
      if Prepared.WhereCount<>0 then begin
        SetLength(Params,Prepared.WhereCount);
        for i := 0 to Prepared.WhereCount-1 do
        with Prepared.Where[i] do begin
          if Operation>high(SQL_OPER_WITH_PARAM) then
            exit; // invalid specified operator -> abort search
          if i=0 then
            fSQL := fSQL+' where ' else
            fSQL := fSQL+' and ';
          if StoredClassRecordProps.AppendFieldName(Column,fSQL,true) then
            exit; // invalid column index -> abort search
          fSQL := fSQL+SQL_OPER_WITH_PARAM[Operation];
          move(Value,Params[i],SizeOf(Value)); // fast copy bound parameter value
        end;
      end;
      // e.g. 'select FirstName,..,ID from PeopleExternal where FirstName=? and LastName=?'
      for i := 0 to Prepared.OrderByCount-1 do
      with Prepared.OrderBy[i] do begin
        if i=0 then
          fSQL := fSQL+' order by ' else
          fSQL := fSQL+', ';
        if StoredClassRecordProps.AppendFieldName(Column,fSQL,true) then
          exit; // invalid column index -> abort search
        if Desc then
          fSQL := fSQL+' desc';
      end;
    end;
    // execute the SQL statement
    try
      fStatement := fProperties.NewThreadSafeStatementPrepared(fSQL,true);
      if fStatement<>nil then begin
        fStatement.Bind(Params);
        fStatement.ExecutePrepared;
        result := Next; // on execution success, go to the first row
      end;
    except
      on Exception do

  // test.db3 SQlite3 file available in the current directory, populated with
  // some TSQLRecordPeople rows
  // - note that SQL statement caching at SQLite3 engine level makes those test
  // 2 times faster: nice proof of performance improvement
  TTestExternalDatabase = class(TSynTestCase)
  protected
    fExternalModel: TSQLModel;

    procedure Test(StaticVirtualTableDirect: boolean);
  public
    /// release used instances (e.g. server) and memory
    procedure CleanUp; override;
  published
    /// initialize needed RESTful client (and server) instances
    // - i.e. a RESTful direct access to an external DB
................................................................................
    fName: RawUTF8;
    fPeople: TSQLRecordPeopleExt;
  published
    property Name: RawUTF8 index 30 read fName write fName;
    property People: TSQLRecordPeopleExt read fPeople write fPeople;
  end;

  // class hooks to force DMBS property for TTestExternalDatabase.AutoAdaptSQL
  TSQLDBConnectionPropertiesHook = class(TSQLDBConnectionProperties);
  TSQLRestServerStaticExternalHook = class(TSQLRestServerStaticExternal);

procedure TTestExternalDatabase.ExternalRecords;
var SQL: RawUTF8;
begin
  if CheckFailed(fExternalModel=nil) then exit; // should be called once
  fExternalModel := TSQLModel.Create(
    [TSQLRecordPeople,TSQLRecordPeopleExt,TSQLRecordOnlyBlob,TSQLRecordTestJoin]);
  ReplaceParamsByNames(StringOfChar('?',200),SQL);
  Check(Hash32(SQL)=$AD27D1E0,'excludes :IF :OF');
end;

procedure TTestExternalDatabase.AutoAdaptSQL;
var SQLOrigin: RawUTF8;
procedure Test(aDBMS: TSQLDBDefinition; AdaptShouldWork: boolean;
  const SQLExpected: RawUTF8='');
var Props: TSQLDBConnectionProperties;
    SQL: RawUTF8;
begin
  Props := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
  try
    Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,Props,'SampleRecord'));
    with TSQLRestServerStaticExternalHook.Create(TSQLRecordPeopleExt,nil) do
    try
      SQL := SQLOrigin;
      TSQLDBConnectionPropertiesHook(Props).fDBMS := aDBMS;
      Check((Props.DBMS=aDBMS)or(aDBMS=dUnknown));
      Check(AdaptSQLForEngineList(SQL)=AdaptShouldWork);
      Check((SQL=SQLExpected)or not AdaptShouldWork);
    finally
      Free;
    end;
  finally
    Props.Free;
  end;
end;
procedure Test2(const Orig,Expected: RawUTF8);
var DBMS: TSQLDBDefinition;
begin
  SQLOrigin := Orig;
  for DBMS := low(DBMS) to high(DBMS) do
    Test(DBMS,true,Expected);
end;
begin
  Test2('select rowid,firstname from PeopleExt where rowid=2',
        'select id,firstname from SampleRecord where id=2');
  Test2('select rowid,firstname from PeopleExt where rowid=2 order by RowID',
        'select id,firstname from SampleRecord where id=2 order by ID');
  Test2('select rowid,firstname from PeopleExt where firstname like :(''test''): order by lastname',
        'select id,firstname from SampleRecord where firstname like :(''test''): order by lastname');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 order by LastName limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2 order by LastName');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2 order by LastName');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2 order by LastName');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where firstname=:(''test''): limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and firstname=:(''test''):');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dMySQL,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  SQLOrigin := 'select id,firstname from PeopleExt limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord');
  Test(dMySQL,true,'select id,firstname from SampleRecord limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord limit 2');
  SQLOrigin := 'select id,firstname from PeopleExt order by firstname limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 order by firstname');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord order by firstname');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord order by firstname');
  Test(dMySQL,true,'select id,firstname from SampleRecord order by firstname limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord order by firstname limit 2');
end;


procedure TTestExternalDatabase.CleanUp;
begin
  FreeAndNil(fExternalModel);
  inherited;
end;

procedure TTestExternalDatabase.ExternalViaREST;
begin
  Test(true);
end;

procedure TTestExternalDatabase.ExternalViaVirtualTable;
begin
  Test(false);
end;

procedure TTestExternalDatabase.CryptedDatabase;
var R,R2: TSQLRecordPeople;
    Model: TSQLModel;
    aID: integer;
    Client, Client2: TSQLRestClientDB;
    Res: TIntegerDynArray;
procedure CheckFilledRow;
begin
  Check(R.FillRewind);
  while R.FillOne do
  if not CheckFailed(R2.FillOne) then begin
    Check(R.ID<>0);
    Check(R2.ID<>0);
    Check(R.FirstName=R2.FirstName);
    Check(R.LastName=R2.LastName);
    Check(R.YearOfBirth=R2.YearOfBirth);
    Check(R.YearOfDeath=R2.YearOfDeath);
  end;
end;
{$ifdef CPU32}
const password = 'pass';
{$else}
const password = '';
{$endif}
begin
  DeleteFile('testpass.db3');
  Model := TSQLModel.Create([TSQLRecordPeople]);
  try
    Client := TSQLRestClientDB.Create(Model,nil,'test.db3',TSQLRestServerDB,false,'');
    try
      R := TSQLRecordPeople.CreateAndFillPrepare(Client,'');
      try
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,password);
        try
          Client2.Server.DB.Synchronous := smOff;
          Client2.Server.DB.LockingMode := lmExclusive;
          Client2.Server.DB.WALMode := true;
          Client2.Server.CreateMissingTables;
          Check(Client2.TransactionBegin(TSQLRecordPeople));
          Check(Client2.BatchStart(TSQLRecordPeople));
          Check(Client2.BatchSend(Res)=200,'Void batch');
          Client2.Commit;
          Check(Client2.TransactionBegin(TSQLRecordPeople));
          Check(Client2.BatchStart(TSQLRecordPeople));
          while R.FillOne do begin
            Check(R.ID<>0);
            Check(Client2.BatchAdd(R,true)>=0);
          end;
          Check(Client2.BatchSend(Res)=200,'INSERT batch');
          Client2.Commit;
        finally
          Client2.Free;
        end;
        Check(IsSQLite3File('testpass.db3'));
        Check(IsSQLite3FileEncrypted('testpass.db3')=(password<>''));
        // try to read then update the crypted file
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,password);
        try
          Client2.Server.DB.Synchronous := smOff;
          Client2.Server.DB.LockingMode := lmExclusive;
          R2 := TSQLRecordPeople.CreateAndFillPrepare(Client2,'');
          try
            CheckFilledRow;
            R2.FirstName := 'One';
            aID := Client2.Add(R2,true);
            Check(aID<>0);
            R2.FillPrepare(Client2,'');
            CheckFilledRow;
            R2.ClearProperties;
            Check(R2.FirstName='');
            Check(Client2.Retrieve(aID,R2));
            Check(R2.FirstName='One');
          finally
            R2.Free;
          end;
        finally
          Client2.Free;
        end;
        Check(IsSQLite3File('testpass.db3'));
        Check(IsSQLite3FileEncrypted('testpass.db3')=(password<>''));
        {$ifdef CPU32}
        // now read it after uncypher
        ChangeSQLEncryptTablePassWord('testpass.db3',password,'');
        Check(IsSQLite3File('testpass.db3'));
        Check(not IsSQLite3FileEncrypted('testpass.db3'));
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,'');
        try
          R2 := TSQLRecordPeople.CreateAndFillPrepare(Client2,'');
          try
            CheckFilledRow;
            R2.ClearProperties;
            Check(R2.FirstName='');
            Check(Client2.Retrieve(aID,R2));
            Check(R2.FirstName='One');
          finally
            R2.Free;
          end;
        finally
          Client2.Free;
        end;
        {$endif}
      finally
        R.Free;
      end;
    finally
      Client.Free;
    end;
  finally
    Model.Free;
  end;
end;


procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
//    Tables: TRawUTF8DynArray;
    aID, i, n: integer;
    ok: Boolean;
    aExternalClient: TSQLRestClientDB;
    fProperties: TSQLDBConnectionProperties;
    Start, Updated: TTimeLog; // will work with both TModTime and TCreateTime properties
begin
  // run tests over an in-memory SQLite3 database (much faster than file)
  fProperties := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordOnlyBlob,fProperties,'OnlyBlobExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordTestJoin,fProperties,'TestJoinExternal'));
  aExternalClient := TSQLRestClientDB.Create(fExternalModel,nil,'test.db3',TSQLRestServerDB);
  try
    aExternalClient.Server.DB.Synchronous := smOff;
    aExternalClient.Server.DB.LockingMode := lmExclusive;
{    aExternalClient.Server.DB.GetTableNames(Tables);
    if FindRawUTF8(Tables,'PeopleExt',false)>=0 then
      aExternalClient.Server.EngineExecuteAll('drop table PeopleExt'); }
    Start := aExternalClient.ServerTimeStamp;
    aExternalClient.Server.StaticVirtualTableDirect := StaticVirtualTableDirect;
    aExternalClient.Server.CreateMissingTables;
    Check(aExternalClient.Server.CreateSQLMultiIndex(
      TSQLRecordPeopleExt,['FirstName','LastName'],false));
    RInt := TSQLRecordPeople.CreateAndFillPrepare(aExternalClient,'');
    try
      Check(RInt.FillTable<>nil);
      Check(RInt.FillTable.RowCount>0);
      Check(not aExternalClient.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=0);
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=0);
      RExt := TSQLRecordPeopleExt.Create;
      try
        n := 0;
        aID := 0;
        while RInt.FillOne do begin
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
          RExt.YearOfDeath := RInt.YearOfDeath;
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          aID := aExternalClient.Add(RExt,true);
          inc(n);
          Check(aID<>0);
          Check(RExt.LastChange>=Start);
          Check(RExt.CreatedAt>=Start);
          RExt.ClearProperties;
          Check(RExt.YearOfBirth=0);
          Check(RExt.FirstName='');
          Check(aExternalClient.Retrieve(aID,RExt));
          Check(RExt.FirstName=RInt.FirstName);
          Check(RExt.LastName=RInt.LastName);
          Check(RExt.YearOfBirth=RInt.YearOfBirth);
          Check(RExt.YearOfDeath=RInt.YearOfDeath);
          Check(RExt.YearOfBirth<>RExt.YearOfDeath);
        end;
        Check(aExternalClient.TableHasRows(TSQLRecordPeopleExt));
        Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=n);
        Check(aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
        Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=n);
        Check(RInt.FillRewind);
        while RInt.FillOne do begin
          RExt.FillPrepare(aExternalClient,'FirstName=? and LastName=?',
            [RInt.FirstName,RInt.LastName]); // query will use index -> fast :)
          while RExt.FillOne do begin
            Check(RExt.FirstName=RInt.FirstName);
            Check(RExt.LastName=RInt.LastName);
            Check(RExt.YearOfBirth=RInt.YearOfBirth);
            Check(RExt.YearOfDeath=RInt.YearOfDeath);
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
          end;
        end;
        Updated := aExternalClient.ServerTimeStamp;
        for i := 1 to aID do
          if i mod 100=0 then begin
            RExt.fLastChange := 0;
            RExt.CreatedAt := 0;
            Check(aExternalClient.Retrieve(i,RExt,true),'for update');
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
            Check(RExt.CreatedAt<=Updated);
            RExt.YearOfBirth := RExt.YearOfDeath;
            Check(aExternalClient.Update(RExt),'Update 1/100 rows');
            Check(aExternalClient.UnLock(RExt));
            Check(RExt.LastChange>=Updated);
            RExt.ClearProperties;
            Check(RExt.YearOfDeath=0);
            Check(RExt.YearOfBirth=0);
            Check(RExt.CreatedAt=0);
            Check(aExternalClient.Retrieve(i,RExt),'after update');
            Check(RExt.YearOfBirth=RExt.YearOfDeath);
            Check(RExt.CreatedAt>=Start);
            Check(RExt.CreatedAt<=Updated);
            Check(RExt.LastChange>=Updated);
          end;
        for i := 1 to aID do
          if i and 127=0 then
            Check(aExternalClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');
        n := aExternalClient.TableRowCount(TSQLRecordPeople);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        aExternalClient.Server.BackupGZ(aExternalClient.Server.DB.FileName+'.gz');
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        for i := 1 to aID do begin
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          RExt.YearOfBirth := 0;
          ok := aExternalClient.Retrieve(i,RExt,false);
          Check(ok=(i and 127<>0),'deletion');
          if ok then begin
            Check(RExt.CreatedAt>=Start);
            Check(RExt.CreatedAt<=Updated);
            if i mod 100=0 then begin
              Check(RExt.YearOfBirth=RExt.YearOfDeath,'Update');
              Check(RExt.LastChange>=Updated);
            end else begin
              Check(RExt.YearOfBirth<>RExt.YearOfDeath,'Update');
              Check(RExt.LastChange>=Start);
              Check(RExt.LastChange<=Updated);
            end;
          end;
        end;
        Check(aExternalClient.Retrieve(1,RInt));
        Check(RInt.fID=1);
        {$ifndef CPU64}
        RInt.YearOfBirth := 1972;
        Check(aExternalClient.Update(RInt)); // for RestoreGZ() below
        Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
        {$endif} // life backup/restore does not work with current sqlite3-64.dll
      finally
        RExt.Free;
      end;
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordOnlyBlob));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordOnlyBlob)=0);
      RBlob := TSQLRecordOnlyBlob.Create;
      try
        aExternalClient.ForceBlobTransfert := true;
        aExternalClient.TransactionBegin(TSQLRecordOnlyBlob);
        for i := 1 to 1000 do begin
          Rblob.Data := Int32ToUtf8(i);
          Check(aExternalClient.Add(RBlob,true)=i);
          Check(RBlob.ID=i);
        end;
        aExternalClient.Commit;
        for i := 1 to 1000 do begin
          Check(aExternalClient.Retrieve(i,RBlob));
          Check(GetInteger(pointer(RBlob.Data))=i);
        end;
        aExternalClient.TransactionBegin(TSQLRecordOnlyBlob);
        for i := 1000 downto 1 do begin
          RBlob.ID := i;
          RBlob.Data := Int32ToUtf8(i*2);
          Check(aExternalClient.Update(RBlob));
        end;
        aExternalClient.Commit;
        for i := 1 to 1000 do begin
          Check(aExternalClient.Retrieve(i,RBlob));
          Check(GetInteger(pointer(RBlob.Data))=i*2);
        end;
      finally
        RBlob.Free;
      end;
      Check(aExternalClient.TableHasRows(TSQLRecordOnlyBlob));
      Check(aExternalClient.TableRowCount(TSQLRecordOnlyBlob)=1000);
      Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
      RInt.ClearProperties;
      {$ifndef CPU64}
      aExternalClient.Retrieve(1,RInt);
      Check(RInt.fID=1);
      Check(RInt.FirstName='Salvador1');
      Check(RInt.YearOfBirth=1972);
      Check(aExternalClient.Server.RestoreGZ(aExternalClient.Server.DB.FileName+'.gz'));
      {$endif} // life backup/restore does not work with current sqlite3-64.dll
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
      Check(aExternalClient.TableHasRows(TSQLRecordPeople));
      Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
      RInt.ClearProperties;
      aExternalClient.Retrieve(1,RInt);
      Check(RInt.fID=1);
      Check(RInt.FirstName='Salvador1');
      Check(RInt.YearOfBirth=1904);
    finally
      RInt.Free;
    end;
  finally
    aExternalClient.Free;
    fProperties.Free;
  end;
end;


{$endif LVCL}

type
{$ifdef INCLUDE_FTS3}
  TSQLFTSTest = class(TSQLRecordFTS3)
  private
    fSubject: RawUTF8;
    fBody: RawUTF8;
  published
    property Subject: RawUTF8 read fSubject write fSubject;
................................................................................
    fGUID: TGUID;
    fGUIDStr: TGUID;
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;
  public
    property GUID: TGUID read fGUID write fGUID;
    property GUIDStr: TGUID read fGUIDStr write fGUIDStr;
  end;





class procedure TSQLRecordCustomProps.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  Props.RegisterCustomFixedSizeRecordProperty(self,sizeof(TGUID),'GUID',
    @TSQLRecordCustomProps(nil).fGUID);
end;

procedure TTestSQLite3Engine._TSQLRestClientDB;
var V,V2: TSQLRecordPeople;
    VA: TSQLRecordPeopleArray;
{$ifndef LVCL}
    VO: TSQLRecordPeopleObject;
{$endif}
    FV: TFV;
    ModelC: TSQLModel;
    Client: TSQLRestClientDB;
    ClientDist: TSQLRestClientURI;
    Server: TSQLRestServer;
    aStatic: TSQLRestServerStaticInMemory;
    Curr: Currency;
    DaVinci, s: RawUTF8;
    Refreshed: boolean;
    J: TSQLTableJSON;
    i, n, nupd, ndx: integer;
    IntArray, Results: TIntegerDynArray;
    List: TObjectList;
    Data: TSQLRawBlob;
    DataS: THeapMemoryStream;
    a,b: double;
procedure checks(Leonard: boolean; Client: TSQLRestClient; const msg: string);
var ID: integer;
begin
  ID := V.ID; // ClearProperties do ID := 0;
  V.ClearProperties; // reset values
  Check(Client.Retrieve(ID,V),msg); // internaly call URL()
  if Leonard then
    Check(V.FirstName='Leonard') else
    Check(V.FirstName='Leonardo1',msg);
  Check(V.LastName=DaVinci,msg);
  Check(V.YearOfBirth=1452,msg);
  Check(V.YearOfDeath=1519,msg);
end;
procedure TestMany(aClient: TSQLRestClient);
var MS: TSQLASource;
    MD, MD2: TSQLADest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
  procedure CheckOK;
  begin


    Check(MS.FillTable.RowCount>=length(sId));
    while MS.FillOne do begin
      Check(MS.DestList.Source.fID=MS.fID);
      Check(MS.DestList.Dest.SignatureTime<>0);
      MS.ClearProperties;
      MS.DestList.Source.ClearProperties;
      MS.DestList.Dest.ClearProperties;
    end;
    MS.FillClose;
  end;
begin
  MS := TSQLASource.Create;
  MD := TSQLADest.Create;

  try
    MD.fSignatureTime := Iso8601Now;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
    Check(aClient.TransactionBegin(TSQLASource)); // faster process
    for i := 1 to high(dID) do begin
................................................................................
    CheckOK;
    Check(MS.FillPrepareMany(aClient,'DestList.Dest.SignatureTime<>?',[],[0]));
    CheckOK;
    Check(MS.FillPrepareMany(aClient,
      'DestList.Dest.SignatureTime<>% and id>=? and DestList.AssociationTime<>0 '+
      'and SignatureTime=DestList.Dest.SignatureTime '+
      'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));


    Check(MS.FillTable.RowCount=length(sID));
    for i := 1 to high(sID) do begin
      MS.SignatureTime := 0;
      MS.DestList.Dest.SignatureTime := 0;
      if CheckFailed(MS.FillOne) then
        break;
      Check(MS.fID=sID[i]);
................................................................................
      end else
        Check(not MS.DestList.ManySelect(aClient,sID[i],dID[i]));
  finally
    MD.Free;
    MS.Free;
  end;
end;




























































































































































































































































































































































































































































































procedure TestDynArray(aClient: TSQLRestClient);
var i, j, k, l: integer;
    IDs: TIntegerDynArray;
begin
  VA.ClearProperties;
  for i := 1 to n do begin
    aClient.Retrieve(i,VA);
................................................................................
         exit;
       end;
    result := true;
  finally
    Free;
    T.Free;
  end;









































end;
procedure Direct(const URI: RawUTF8; Hash: cardinal);
var call: TSQLRestURIParams;
begin
  call.Method :='GET';
  call.Url := TSQLRestServerAuthenticationDefault.ClientSessionSign(Client,URI);
  call.RestAccessRights := @SUPERVISOR_ACCESS_RIGHTS;
  Server.URI(call);
  Check(Hash32(call.OutBody)=Hash);
end;

begin
  V := TSQLRecordPeople.Create;
  VA := TSQLRecordPeopleArray.Create;
{$ifndef LVCL}
  VO := TSQLRecordPeopleObject.Create;
{$endif}
  V2 := nil;
................................................................................
{$ifdef INCLUDE_FTS3}
      TestFTS3(Client);
{$endif}
      TestDynArray(Client);
{$ifndef LVCL}
      TestObject(Client);
{$endif}
      TestMany(Client);
      // RegisterVirtualTableModule(TSQLVirtualTableJSON) already done
      TestVirtual(Client,false,'Virtual Table access via SQLite',TSQLRecordDali1);
      TestVirtual(Client,false,'Virtual Table access via SQLite',TSQLRecordDali2);
      TestVirtual(Client,true,'Direct Virtual Table access',TSQLRecordDali1);
      TestVirtual(Client,true,'Direct Virtual Table access',TSQLRecordDali2);
      // remote client access test
      Check(Client.Server.ExportServerNamedPipe('Test'),'declare Test server');
      ClientDist := TSQLRestClientURINamedPipe.Create(ModelC,'Test');
      try
        Check(ClientDist.SetUser('User','synopse'));
{$ifdef INCLUDE_FTS3}
        TestFTS3(ClientDist);
{$endif}TestDynArray(ClientDist);
{$ifndef LVCL}
        TestObject(ClientDist);
{$endif}TestMany(ClientDist);
        TestVirtual(ClientDist,false,'Remote Virtual Table access via SQLite',TSQLRecordDali1);
        TestVirtual(ClientDist,false,'Remote Virtual Table access via SQLite',TSQLRecordDali2);
        TestVirtual(ClientDist,true,'Remote Direct Virtual Table access',TSQLRecordDali1);
        TestVirtual(ClientDist,true,'Remote Direct Virtual Table access',TSQLRecordDali2);
        Check(Test(ClientDist.List([TSQLRecordPeople],'*',s)),'through URI and JSON');
        for i := 0 to high(IntArray) do begin
          Check(ClientDist.RetrieveBlob(TSQLRecordPeople,IntArray[i],'Data',Data));
          Check((length(Data)=4) and (PInteger(pointer(Data))^=IntArray[i]));
          V2.ID := IntArray[i]; // debug use - do NOT set ID in your programs!
          Check(V2.DataAsHex(ClientDist)=SynCommons.BinToHex(Data));
          a := Random;
          b := Random;
          CheckSame(TSQLRecordPeople.Sum(Client,a,b,false),a+b);
          CheckSame(TSQLRecordPeople.Sum(Client,a,b,true),a+b);
        end;
        V.FirstName := 'Leonardo1';
        Check(ClientDist.Update(V));
        checks(false,ClientDist,'check remote UPDATE/POST');
        V.FirstName := 'Leonard';
        Check(ClientDist.Update(V));
        checks(true,ClientDist,'check remote UPDATE/POST');
//          time := GetTickCount; while time=GetTickCount do; time := GetTickCount;
        for i := 1 to 400 do // speed test: named pipes are OK
          checks(true,ClientDist,'caching speed test');
//          writeln('NamedPipe connection time is ',GetTickCount-time,'ms');
      finally
        ClientDist.Free;
      end;
      if IsMemory then begin // this is a bit time consuming, so do it once
        Server := TSQLRestServerTest.Create(ModelC,false);
        try
          Server.NoAJAXJSON := true;
          DeleteFile('People.json');
          DeleteFile('People.data');
          Server.StaticDataCreate(TSQLRecordPeople,'People.data',true);

  // test.db3 SQlite3 file available in the current directory, populated with
  // some TSQLRecordPeople rows
  // - note that SQL statement caching at SQLite3 engine level makes those test
  // 2 times faster: nice proof of performance improvement
  TTestExternalDatabase = class(TSynTestCase)
  protected
    fExternalModel: TSQLModel;
    fPeopleData: TSQLTable;
    procedure Test(StaticVirtualTableDirect: boolean);
  public
    /// release used instances (e.g. server) and memory
    procedure CleanUp; override;
  published
    /// initialize needed RESTful client (and server) instances
    // - i.e. a RESTful direct access to an external DB
................................................................................
    fName: RawUTF8;
    fPeople: TSQLRecordPeopleExt;
  published
    property Name: RawUTF8 index 30 read fName write fName;
    property People: TSQLRecordPeopleExt read fPeople write fPeople;
  end;














































































































































































































































































































































































































































{$ifdef INCLUDE_FTS3}
  TSQLFTSTest = class(TSQLRecordFTS3)
  private
    fSubject: RawUTF8;
    fBody: RawUTF8;
  published
    property Subject: RawUTF8 read fSubject write fSubject;
................................................................................
    fGUID: TGUID;
    fGUIDStr: TGUID;
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;
  public
    property GUID: TGUID read fGUID write fGUID;
    property GUIDStr: TGUID read fGUIDStr write fGUIDStr;
  end;

  // class hooks to force DMBS property for TTestExternalDatabase.AutoAdaptSQL
  TSQLDBConnectionPropertiesHook = class(TSQLDBConnectionProperties);
  TSQLRestServerStaticExternalHook = class(TSQLRestServerStaticExternal);

class procedure TSQLRecordCustomProps.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  Props.RegisterCustomFixedSizeRecordProperty(self,sizeof(TGUID),'GUID',
    @TSQLRecordCustomProps(nil).fGUID);
end;

/// will be re-used by both TTestSQLite3Engine and TTestExternalDatabase







procedure InternalTestMany(Test: TSynTestCase; aClient: TSQLRestClient);



























var MS: TSQLASource;
    MD, MD2: TSQLADest;
    i: integer;
    sID, dID: array[1..100] of Integer;
    res: TIntegerDynArray;
  procedure CheckOK;
  begin
    if Test.CheckFailed(MS.FillTable<>nil) then
      exit;
    Test.Check(MS.FillTable.RowCount>=length(sId));
    while MS.FillOne do begin
      Test.Check(MS.DestList.Source.fID=MS.fID);
      Test.Check(MS.DestList.Dest.SignatureTime<>0);
      MS.ClearProperties;
      MS.DestList.Source.ClearProperties;
      MS.DestList.Dest.ClearProperties;
    end;
    MS.FillClose;
  end;
begin
  MS := TSQLASource.Create;
  MD := TSQLADest.Create;
  with Test do
  try
    MD.fSignatureTime := Iso8601Now;
    MS.fSignatureTime := MD.fSignatureTime;
    Check(MS.DestList<>nil);
    Check(MS.DestList.InheritsFrom(TSQLRecordMany));
    Check(aClient.TransactionBegin(TSQLASource)); // faster process
    for i := 1 to high(dID) do begin
................................................................................
    CheckOK;
    Check(MS.FillPrepareMany(aClient,'DestList.Dest.SignatureTime<>?',[],[0]));
    CheckOK;
    Check(MS.FillPrepareMany(aClient,
      'DestList.Dest.SignatureTime<>% and id>=? and DestList.AssociationTime<>0 '+
      'and SignatureTime=DestList.Dest.SignatureTime '+
      'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));
    if CheckFailed(MS.FillTable<>nil) then
      exit;
    Check(MS.FillTable.RowCount=length(sID));
    for i := 1 to high(sID) do begin
      MS.SignatureTime := 0;
      MS.DestList.Dest.SignatureTime := 0;
      if CheckFailed(MS.FillOne) then
        break;
      Check(MS.fID=sID[i]);
................................................................................
      end else
        Check(not MS.DestList.ManySelect(aClient,sID[i],dID[i]));
  finally
    MD.Free;
    MS.Free;
  end;
end;


procedure TTestExternalDatabase.ExternalRecords;
var SQL: RawUTF8;
begin
  if CheckFailed(fExternalModel=nil) then exit; // should be called once
  fExternalModel := TSQLModel.Create(
    [TSQLRecordPeopleExt,TSQLRecordOnlyBlob,TSQLRecordTestJoin,
     TSQLASource,TSQLADest,TSQLADests,TSQLRecordPeople]);
  ReplaceParamsByNames(StringOfChar('?',200),SQL);
  Check(Hash32(SQL)=$AD27D1E0,'excludes :IF :OF');
end;

procedure TTestExternalDatabase.AutoAdaptSQL;
var SQLOrigin: RawUTF8;
procedure Test(aDBMS: TSQLDBDefinition; AdaptShouldWork: boolean;
  const SQLExpected: RawUTF8='');
var Props: TSQLDBConnectionProperties;
    SQL: RawUTF8;
begin
  Props := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
  try
    Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,
      Props,'SampleRecord'));
    with TSQLRestServerStaticExternalHook.Create(TSQLRecordPeopleExt,nil) do
    try
      SQL := SQLOrigin;
      TSQLDBConnectionPropertiesHook(Props).fDBMS := aDBMS;
      Check((Props.DBMS=aDBMS)or(aDBMS=dUnknown));
      Check(AdaptSQLForEngineList(SQL)=AdaptShouldWork);
      Check((SQL=SQLExpected)or not AdaptShouldWork);
    finally
      Free;
    end;
  finally
    Props.Free;
  end;
end;
procedure Test2(const Orig,Expected: RawUTF8);
var DBMS: TSQLDBDefinition;
begin
  SQLOrigin := Orig;
  for DBMS := low(DBMS) to high(DBMS) do
    Test(DBMS,true,Expected);
end;
begin
  Test2('select rowid,firstname from PeopleExt where rowid=2',
        'select id,firstname from SampleRecord where id=2');
  Test2('select rowid,firstname from PeopleExt where rowid=2 order by RowID',
        'select id,firstname from SampleRecord where id=2 order by ID');
  Test2('select rowid,firstname from PeopleExt where firstname like :(''test''): order by lastname',
        'select id,firstname from SampleRecord where firstname like :(''test''): order by lastname');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where rowid=2 order by LastName limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and id=2 order by LastName');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where id=2 order by LastName');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where id=2 order by LastName');
  Test(dMySQL,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where id=2 order by LastName limit 2');
  SQLOrigin := 'select rowid,firstname from PeopleExt where firstname=:(''test''): limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 and firstname=:(''test''):');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord where firstname=:(''test''):');
  Test(dMySQL,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord where firstname=:(''test''): limit 2');
  SQLOrigin := 'select id,firstname from PeopleExt limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord');
  Test(dMySQL,true,'select id,firstname from SampleRecord limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord limit 2');
  SQLOrigin := 'select id,firstname from PeopleExt order by firstname limit 2';
  Test(dUnknown,false);
  Test(dDefault,false);
  Test(dOracle,true,'select id,firstname from SampleRecord where rownum<=2 order by firstname');
  Test(dMSSQL,true,'select top(2) id,firstname from SampleRecord order by firstname');
  Test(dJet,true,'select top 2 id,firstname from SampleRecord order by firstname');
  Test(dMySQL,true,'select id,firstname from SampleRecord order by firstname limit 2');
  Test(dSQLite,true,'select id,firstname from SampleRecord order by firstname limit 2');
end;


procedure TTestExternalDatabase.CleanUp;
begin
  FreeAndNil(fExternalModel);
  FreeAndNil(fPeopleData);
  inherited;
end;

procedure TTestExternalDatabase.ExternalViaREST;
begin
  Test(true);
end;

procedure TTestExternalDatabase.ExternalViaVirtualTable;
begin
  Test(false);
end;

procedure TTestExternalDatabase.CryptedDatabase;
var R,R2: TSQLRecordPeople;
    Model: TSQLModel;
    aID: integer;
    Client, Client2: TSQLRestClientDB;
    Res: TIntegerDynArray;
procedure CheckFilledRow;
begin
  Check(R.FillRewind);
  while R.FillOne do
  if not CheckFailed(R2.FillOne) then begin
    Check(R.ID<>0);
    Check(R2.ID<>0);
    Check(R.FirstName=R2.FirstName);
    Check(R.LastName=R2.LastName);
    Check(R.YearOfBirth=R2.YearOfBirth);
    Check(R.YearOfDeath=R2.YearOfDeath);
  end;
end;
{$ifdef CPU32}
const password = 'pass';
{$else}
const password = '';
{$endif}
begin
  DeleteFile('testpass.db3');
  Model := TSQLModel.Create([TSQLRecordPeople]);
  try
    Client := TSQLRestClientDB.Create(Model,nil,'test.db3',TSQLRestServerDB,false,'');
    try
      R := TSQLRecordPeople.Create;
      Assert(fPeopleData=nil);
      fPeopleData := Client.List([TSQLRecordPeople],'*');
      R.FillPrepare(fPeopleData);
      try
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,password);
        try
          Client2.Server.DB.Synchronous := smOff;
          Client2.Server.DB.LockingMode := lmExclusive;
          Client2.Server.DB.WALMode := true;
          Client2.Server.CreateMissingTables;
          Check(Client2.TransactionBegin(TSQLRecordPeople));
          Check(Client2.BatchStart(TSQLRecordPeople));
          Check(Client2.BatchSend(Res)=200,'Void batch');
          Client2.Commit;
          Check(Client2.TransactionBegin(TSQLRecordPeople));
          Check(Client2.BatchStart(TSQLRecordPeople));
          while R.FillOne do begin
            Check(R.ID<>0);
            Check(Client2.BatchAdd(R,true)>=0);
          end;
          Check(Client2.BatchSend(Res)=200,'INSERT batch');
          Client2.Commit;
        finally
          Client2.Free;
        end;
        Check(IsSQLite3File('testpass.db3'));
        Check(IsSQLite3FileEncrypted('testpass.db3')=(password<>''));
        // try to read then update the crypted file
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,password);
        try
          Client2.Server.DB.Synchronous := smOff;
          Client2.Server.DB.LockingMode := lmExclusive;
          R2 := TSQLRecordPeople.CreateAndFillPrepare(Client2,'');
          try
            CheckFilledRow;
            R2.FirstName := 'One';
            aID := Client2.Add(R2,true);
            Check(aID<>0);
            R2.FillPrepare(Client2,'');
            CheckFilledRow;
            R2.ClearProperties;
            Check(R2.FirstName='');
            Check(Client2.Retrieve(aID,R2));
            Check(R2.FirstName='One');
          finally
            R2.Free;
          end;
        finally
          Client2.Free;
        end;
        Check(IsSQLite3File('testpass.db3'));
        Check(IsSQLite3FileEncrypted('testpass.db3')=(password<>''));
        {$ifdef CPU32}
        // now read it after uncypher
        ChangeSQLEncryptTablePassWord('testpass.db3',password,'');
        Check(IsSQLite3File('testpass.db3'));
        Check(not IsSQLite3FileEncrypted('testpass.db3'));
        Client2 := TSQLRestClientDB.Create(Model,nil,'testpass.db3',TSQLRestServerDB,false,'');
        try
          R2 := TSQLRecordPeople.CreateAndFillPrepare(Client2,'');
          try
            CheckFilledRow;
            R2.ClearProperties;
            Check(R2.FirstName='');
            Check(Client2.Retrieve(aID,R2));
            Check(R2.FirstName='One');
          finally
            R2.Free;
          end;
        finally
          Client2.Free;
        end;
        {$endif}
      finally
        R.Free;
      end;
    finally
      Client.Free;
    end;
  finally
    Model.Free;
  end;
end;


procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
    Tables: TRawUTF8DynArray;
    aID, i, n: integer;
    ok: Boolean;
    aExternalClient: TSQLRestClientDB;
    fProperties: TSQLDBConnectionProperties;
    Start, Updated: TTimeLog; // will work with both TModTime and TCreateTime properties
begin
  // run tests over an in-memory SQLite3 external database (much faster than file)
  fProperties := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordPeopleExt,fProperties,'PeopleExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordOnlyBlob,fProperties,'OnlyBlobExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLRecordTestJoin,fProperties,'TestJoinExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLASource,fProperties,'SourceExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADest,fProperties,'DestExternal'));
  Check(VirtualTableExternalRegister(fExternalModel,TSQLADests,fProperties,'DestsExternal'));
  DeleteFile('testExternal.db3'); // need a file for backup testing 
  aExternalClient := TSQLRestClientDB.Create(fExternalModel,nil,'testExternal.db3',TSQLRestServerDB);
  try
    aExternalClient.Server.DB.Synchronous := smOff;
    aExternalClient.Server.DB.LockingMode := lmExclusive;
    aExternalClient.Server.DB.GetTableNames(Tables);
    Check(Tables=nil); // we reset the testExternal.db3 file
    Start := aExternalClient.ServerTimeStamp;
    aExternalClient.Server.StaticVirtualTableDirect := StaticVirtualTableDirect;
    aExternalClient.Server.CreateMissingTables;
    Check(aExternalClient.Server.CreateSQLMultiIndex(
      TSQLRecordPeopleExt,['FirstName','LastName'],false));
    //InternalTestMany(self,aExternalClient);
    assert(fPeopleData<>nil);
    RInt := TSQLRecordPeople.Create;
    try
      RInt.FillPrepare(fPeopleData);
      Check(RInt.FillTable<>nil);
      Check(RInt.FillTable.RowCount>0);
      Check(not aExternalClient.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=0);
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=0);
      RExt := TSQLRecordPeopleExt.Create;
      try
        n := 0;
        aID := 0;
        while RInt.FillOne do begin
          if RInt.fID<100 then // some real entries for for backup testing 
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
          RExt.YearOfDeath := RInt.YearOfDeath;
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          aID := aExternalClient.Add(RExt,true);
          inc(n);
          Check(aID<>0);
          Check(RExt.LastChange>=Start);
          Check(RExt.CreatedAt>=Start);
          RExt.ClearProperties;
          Check(RExt.YearOfBirth=0);
          Check(RExt.FirstName='');
          Check(aExternalClient.Retrieve(aID,RExt));
          Check(RExt.FirstName=RInt.FirstName);
          Check(RExt.LastName=RInt.LastName);
          Check(RExt.YearOfBirth=RInt.YearOfBirth);
          Check(RExt.YearOfDeath=RInt.YearOfDeath);
          Check(RExt.YearOfBirth<>RExt.YearOfDeath);
        end;
        Check(n=fPeopleData.RowCount);
        Check(aExternalClient.TableHasRows(TSQLRecordPeopleExt));
        Check(aExternalClient.TableRowCount(TSQLRecordPeopleExt)=n);
        Check(aExternalClient.Server.TableHasRows(TSQLRecordPeopleExt));
        Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=n);
        Check(RInt.FillRewind);
        while RInt.FillOne do begin
          RExt.FillPrepare(aExternalClient,'FirstName=? and LastName=?',
            [RInt.FirstName,RInt.LastName]); // query will use index -> fast :)
          while RExt.FillOne do begin
            Check(RExt.FirstName=RInt.FirstName);
            Check(RExt.LastName=RInt.LastName);
            Check(RExt.YearOfBirth=RInt.YearOfBirth);
            Check(RExt.YearOfDeath=RInt.YearOfDeath);
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
          end;
        end;
        Updated := aExternalClient.ServerTimeStamp;
        for i := 1 to aID do
          if i mod 100=0 then begin
            RExt.fLastChange := 0;
            RExt.CreatedAt := 0;
            Check(aExternalClient.Retrieve(i,RExt,true),'for update');
            Check(RExt.YearOfBirth<>RExt.YearOfDeath);
            Check(RExt.CreatedAt<=Updated);
            RExt.YearOfBirth := RExt.YearOfDeath;
            Check(aExternalClient.Update(RExt),'Update 1/100 rows');
            Check(aExternalClient.UnLock(RExt));
            Check(RExt.LastChange>=Updated);
            RExt.ClearProperties;
            Check(RExt.YearOfDeath=0);
            Check(RExt.YearOfBirth=0);
            Check(RExt.CreatedAt=0);
            Check(aExternalClient.Retrieve(i,RExt),'after update');
            Check(RExt.YearOfBirth=RExt.YearOfDeath);
            Check(RExt.CreatedAt>=Start);
            Check(RExt.CreatedAt<=Updated);
            Check(RExt.LastChange>=Updated);
          end;
        for i := 1 to aID do
          if i and 127=0 then
            Check(aExternalClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');
        n := aExternalClient.TableRowCount(TSQLRecordPeople);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        aExternalClient.Server.BackupGZ(aExternalClient.Server.DB.FileName+'.gz');
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
        Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
        for i := 1 to aID do begin
          RExt.fLastChange := 0;
          RExt.CreatedAt := 0;
          RExt.YearOfBirth := 0;
          ok := aExternalClient.Retrieve(i,RExt,false);
          Check(ok=(i and 127<>0),'deletion');
          if ok then begin
            Check(RExt.CreatedAt>=Start);
            Check(RExt.CreatedAt<=Updated);
            if i mod 100=0 then begin
              Check(RExt.YearOfBirth=RExt.YearOfDeath,'Update');
              Check(RExt.LastChange>=Updated);
            end else begin
              Check(RExt.YearOfBirth<>RExt.YearOfDeath,'Update');
              Check(RExt.LastChange>=Start);
              Check(RExt.LastChange<=Updated);
            end;
          end;
        end;
        RInt.ClearProperties;
        Check(aExternalClient.Retrieve(1,RInt));
        Check(RInt.fID=1);
        {$ifndef CPU64}
        RInt.YearOfBirth := 1972;
        Check(aExternalClient.Update(RInt)); // for RestoreGZ() below
        Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
        {$endif} // life backup/restore does not work with current sqlite3-64.dll
      finally
        RExt.Free;
      end;
      Check(not aExternalClient.Server.TableHasRows(TSQLRecordOnlyBlob));
      Check(aExternalClient.Server.TableRowCount(TSQLRecordOnlyBlob)=0);
      RBlob := TSQLRecordOnlyBlob.Create;
      try
        aExternalClient.ForceBlobTransfert := true;
        aExternalClient.TransactionBegin(TSQLRecordOnlyBlob);
        for i := 1 to 1000 do begin
          Rblob.Data := Int32ToUtf8(i);
          Check(aExternalClient.Add(RBlob,true)=i);
          Check(RBlob.ID=i);
        end;
        aExternalClient.Commit;
        for i := 1 to 1000 do begin
          Check(aExternalClient.Retrieve(i,RBlob));
          Check(GetInteger(pointer(RBlob.Data))=i);
        end;
        aExternalClient.TransactionBegin(TSQLRecordOnlyBlob);
        for i := 1000 downto 1 do begin
          RBlob.ID := i;
          RBlob.Data := Int32ToUtf8(i*2);
          Check(aExternalClient.Update(RBlob));
        end;
        aExternalClient.Commit;
        for i := 1 to 1000 do begin
          Check(aExternalClient.Retrieve(i,RBlob));
          Check(GetInteger(pointer(RBlob.Data))=i*2);
        end;
      finally
        RBlob.Free;
      end;
      Check(aExternalClient.TableHasRows(TSQLRecordOnlyBlob));
      Check(aExternalClient.TableRowCount(TSQLRecordOnlyBlob)=1000);
      Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
      RInt.ClearProperties;
      {$ifndef CPU64}
      aExternalClient.Retrieve(1,RInt);
      Check(RInt.fID=1);
      Check(RInt.FirstName='Salvador1');
      Check(RInt.YearOfBirth=1972);
      Check(aExternalClient.Server.RestoreGZ(aExternalClient.Server.DB.FileName+'.gz'));
      {$endif} // life backup/restore does not work with current sqlite3-64.dll
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeople]=nil);
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordPeopleExt]<>nil);
      Check(aExternalClient.Server.StaticVirtualTable[TSQLRecordOnlyBlob]<>nil);
      Check(aExternalClient.TableHasRows(TSQLRecordPeople));
      Check(aExternalClient.TableRowCount(TSQLRecordPeople)=n);
      RInt.ClearProperties;
      aExternalClient.Retrieve(1,RInt);
      Check(RInt.fID=1);
      Check(RInt.FirstName='Salvador1');
      Check(RInt.YearOfBirth=1904);
    finally
      RInt.Free;
    end;
  finally
    aExternalClient.Free;
    fProperties.Free;
  end;
end;


{$endif LVCL}

procedure TTestSQLite3Engine._TSQLRestClientDB;
var V,V2: TSQLRecordPeople;
    VA: TSQLRecordPeopleArray;
{$ifndef LVCL}
    VO: TSQLRecordPeopleObject;
{$endif}
    FV: TFV;
    ModelC: TSQLModel;
    Client: TSQLRestClientDB;
    Server: TSQLRestServer;
    aStatic: TSQLRestServerStaticInMemory;
    Curr: Currency;
    DaVinci, s: RawUTF8;
    Refreshed: boolean;
    J: TSQLTableJSON;
    i, n, nupd, ndx: integer;
    IntArray, Results: TIntegerDynArray;
    List: TObjectList;
    Data: TSQLRawBlob;
    DataS: THeapMemoryStream;
    a,b: double;
procedure checks(Leonard: boolean; Client: TSQLRestClient; const msg: string);
var ID: integer;
begin
  ID := V.ID; // ClearProperties do ID := 0;
  V.ClearProperties; // reset values
  Check(Client.Retrieve(ID,V),msg); // internaly call URL()
  if Leonard then
    Check(V.FirstName='Leonard') else
    Check(V.FirstName='Leonardo1',msg);
  Check(V.LastName=DaVinci,msg);
  Check(V.YearOfBirth=1452,msg);
  Check(V.YearOfDeath=1519,msg);
end;
procedure TestDynArray(aClient: TSQLRestClient);
var i, j, k, l: integer;
    IDs: TIntegerDynArray;
begin
  VA.ClearProperties;
  for i := 1 to n do begin
    aClient.Retrieve(i,VA);
................................................................................
         exit;
       end;
    result := true;
  finally
    Free;
    T.Free;
  end;
end;
procedure TestClientDist(ClientDist: TSQLRestClientURI);
var i: integer;
begin
  try
    Check(ClientDist.SetUser('User','synopse'));
{$ifdef INCLUDE_FTS3}
    TestFTS3(ClientDist);
{$endif}TestDynArray(ClientDist);
{$ifndef LVCL}
    TestObject(ClientDist);
{$endif}
    InternalTestMany(self,ClientDist);
    TestVirtual(ClientDist,false,'Remote Virtual Table access via SQLite',TSQLRecordDali1);
    TestVirtual(ClientDist,false,'Remote Virtual Table access via SQLite',TSQLRecordDali2);
    TestVirtual(ClientDist,true,'Remote Direct Virtual Table access',TSQLRecordDali1);
    TestVirtual(ClientDist,true,'Remote Direct Virtual Table access',TSQLRecordDali2);
    Check(Test(ClientDist.List([TSQLRecordPeople],'*',s)),'through URI and JSON');
    for i := 0 to high(IntArray) do begin
      Check(ClientDist.RetrieveBlob(TSQLRecordPeople,IntArray[i],'Data',Data));
      Check((length(Data)=4) and (PInteger(pointer(Data))^=IntArray[i]));
      V2.ID := IntArray[i]; // debug use - do NOT set ID in your programs!
      Check(V2.DataAsHex(ClientDist)=SynCommons.BinToHex(Data));
      a := Random;
      b := Random;
      CheckSame(TSQLRecordPeople.Sum(Client,a,b,false),a+b);
      CheckSame(TSQLRecordPeople.Sum(Client,a,b,true),a+b);
    end;
    V.FirstName := 'Leonardo1';
    Check(ClientDist.Update(V));
    checks(false,ClientDist,'check remote UPDATE/POST');
    V.FirstName := 'Leonard';
    Check(ClientDist.Update(V));
    checks(true,ClientDist,'check remote UPDATE/POST');
//          time := GetTickCount; while time=GetTickCount do; time := GetTickCount;
    for i := 1 to 400 do // speed test: named pipes are OK
      checks(true,ClientDist,'caching speed test');
//          writeln('NamedPipe connection time is ',GetTickCount-time,'ms');
  finally
    ClientDist.Free;
  end;
end;
procedure Direct(const URI: RawUTF8; Hash: cardinal);
var call: TSQLRestURIParams;
begin
  call.Method :='GET';
  call.Url := TSQLRestServerAuthenticationDefault.ClientSessionSign(Client,URI);
  call.RestAccessRights := @SUPERVISOR_ACCESS_RIGHTS;
  Server.URI(call);
  Check(Hash32(call.OutBody)=Hash);
end;
var ClientDist: TSQLRestClientURI;
begin
  V := TSQLRecordPeople.Create;
  VA := TSQLRecordPeopleArray.Create;
{$ifndef LVCL}
  VO := TSQLRecordPeopleObject.Create;
{$endif}
  V2 := nil;
................................................................................
{$ifdef INCLUDE_FTS3}
      TestFTS3(Client);
{$endif}
      TestDynArray(Client);
{$ifndef LVCL}
      TestObject(Client);
{$endif}
      InternalTestMany(self,Client);
      // RegisterVirtualTableModule(TSQLVirtualTableJSON) already done
      TestVirtual(Client,false,'Virtual Table access via SQLite',TSQLRecordDali1);
      TestVirtual(Client,false,'Virtual Table access via SQLite',TSQLRecordDali2);
      TestVirtual(Client,true,'Direct Virtual Table access',TSQLRecordDali1);
      TestVirtual(Client,true,'Direct Virtual Table access',TSQLRecordDali2);
      // remote client access test (via named pipes)
      Check(Client.Server.ExportServerNamedPipe('Test'),'declare Test server');
      TestClientDist(TSQLRestClientURINamedPipe.Create(ModelC,'Test'));
      // test per-one and batch requests



































      if IsMemory then begin // this is a bit time consuming, so do it once
        Server := TSQLRestServerTest.Create(ModelC,false);
        try
          Server.NoAJAXJSON := true;
          DeleteFile('People.json');
          DeleteFile('People.data');
          Server.StaticDataCreate(TSQLRecordPeople,'People.data',true);