mORMot and Open Source friends
Check-in [cae7936c88]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:finalized ORM external table field mapping, using e.g.
aModel.Props[aClass].ExternalDB.MapField(..)
including regression tests
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:cae7936c88302cf9dd418fba37eb0ed3777ced72
User & Date: abouchez 2014-03-13 11:05:13
Original Comment: finalized ORM external table field mapping, using e.g. aModel.Props[aClass].ExternalDB.MapField(..) - including regression tests
Context
2014-03-13
13:24
fixed ticket [51a9c086f3] about THttpApiServer.SetHTTPQueueLength() check-in: 8937d37696 user: abouchez tags: trunk
11:05
finalized ORM external table field mapping, using e.g.
aModel.Props[aClass].ExternalDB.MapField(..)
including regression tests
check-in: cae7936c88 user: abouchez tags: trunk
2014-03-12
16:26
one step forward for field mapping support for external database ORM - remaining task will be JSON creation with the mapped internal field names instead of external field names (with potential on-the-fly value lookup/conversion in the future) check-in: d0b95f7818 user: abouchez tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to SQLite3/mORMot.pas.

5507
5508
5509
5510
5511
5512
5513

5514
5515
5516
5517
5518
5519
5520
....
5588
5589
5590
5591
5592
5593
5594




5595
5596
5597
5598
5599
5600
5601
....
6528
6529
6530
6531
6532
6533
6534
6535

6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
....
7569
7570
7571
7572
7573
7574
7575
7576
7577
7578
7579
7580
7581
7582
7583
....
7600
7601
7602
7603
7604
7605
7606
7607
7608
7609
7610
7611
7612
7613
7614
....
7622
7623
7624
7625
7626
7627
7628

7629
7630
7631
7632
7633
7634
7635
7636
7637
7638
7639
7640
.....
20410
20411
20412
20413
20414
20415
20416
20417
20418
20419
20420




20421
20422
20423
20424
20425
20426
20427
20428
20429
20430




20431
20432
20433
20434
20435
20436
20437
20438
20439
20440
20441
20442
20443
20444
20445
20446
.....
20467
20468
20469
20470
20471
20472
20473

20474
20475
20476
20477
20478
20479
20480
20481
20482
20483
20484
20485

20486
20487
20488
20489
20490
20491
20492
20493
20494
20495
20496




20497




20498
20499
20500
20501
20502
20503
20504
.....
20558
20559
20560
20561
20562
20563
20564
20565
20566
20567
20568
20569
20570
20571
20572
.....
24205
24206
24207
24208
24209
24210
24211

24212

24213
24214
24215
24216
24217
24218
24219
24220
24221
24222
24223
24224
24225
.....
34092
34093
34094
34095
34096
34097
34098
34099
34100
34101
34102
34103
34104
34105
34106
34107
34108
34109
34110
34111

34112
34113
34114
34115
34116
34117
34118
34119
34120
34121
34122
34123
34124
34125
.....
34262
34263
34264
34265
34266
34267
34268
34269
34270
34271
34272
34273
34274
34275
34276
34277
34278
34279
34280
.....
34436
34437
34438
34439
34440
34441
34442
34443
34444
34445
34446
34447
34448
34449

34450
34451
34452
34453
34454
34455
34456
34457
34458
34459
34460
34461
34462
34463
34464
34465
34466
34467
34468
34469
34470

34471
34472
34473
34474
34475
34476
34477
.....
34684
34685
34686
34687
34688
34689
34690
34691
34692
34693
34694
34695
34696
34697
34698
34699
.....
34717
34718
34719
34720
34721
34722
34723


34724
34725
34726
34727
34728
34729
34730
.....
34759
34760
34761
34762
34763
34764
34765
34766
34767
34768
34769

34770
34771
34772
34773
34774
34775
34776
.....
34905
34906
34907
34908
34909
34910
34911

34912
34913
34914
34915
34916
34917
34918
34919
34920
34921
34922
34923
34924
34925
34926
34927
34928
.....
34964
34965
34966
34967
34968
34969
34970
34971
34972
34973
34974
34975
34976
34977
34978
.....
35003
35004
35005
35006
35007
35008
35009
35010
35011
35012
35013
35014
35015
35016
35017
35018
    fProps: TSQLModelRecordProperties;
    /// storage of main read-only properties
    fConnectionProperties: TObject;
    fTableName: RawUTF8;
    fRowIDFieldName: RawUTF8;
    fFieldNames: TRawUTF8DynArray;
    fSQL: TSQLModelRecordPropertiesSQL;

    /// fill fRowIDFieldName/fSQL with the current information
    procedure ComputeSQL;
  public
    /// add a custom field mapping
    // - will re-compute all needed SQL statements as needed, and initialize
    // fSortedFieldsName[] and fSortedFieldsIndex[] internal sorted arrays
    // - can be used e.g. as
................................................................................
    // database engines (e.g. Oracle)
    // - can be customized e.g. via
    // ! aModel.Props[TSQLMyExternal].ExternalDB.MapField('ID','ExternalID');
    property RowIDFieldName: RawUTF8 read fRowIDFieldName;
    /// the external field names, following fProps.Props.Field[] order
    // - excluding ID/RowID field, which is stored in RowIDFieldName
    property FieldNames: TRawUTF8DynArray read fFieldNames;




  end;

  /// ORM properties associated to a TSQLRecord within a given model
  // - "stable" / common properties derivated from RTTI are shared in the
  // TSQLRecordProperties instance
  // - since the same TSQLRecord can be defined in several models, with diverse
  // implementation patterns (e.g. internal in one, external in another),
................................................................................

  /// describe a service provider method arguments
  TServiceMethodArgumentDynArray = array of TServiceMethodArgument;

  /// possible service provider method options, e.g. about logging or execution
  // - see TServiceMethodOptions for a description of each available option
  TServiceMethodOption = (
    optExecLockedPerInterface

    {$ifndef LVCL},
    optExecInMainThread, optFreeInMainThread,
    optExecInPerInterfaceThread, optFreeInPerInterfaceThread,
    optVariantCopiedByReference
    {$endif}
  );

  /// set of per-method execution options for an interface-based service provider
  // - by default, mehthod executions are concurrent, for better server
  // responsiveness; if you set optExecLockedPerInterface, all methods of
................................................................................
    /// this virtual constructor will be called at instance creation
    constructor Create; virtual;
  end;

  TInterfacedObjectWithCustomCreateClass = class of TInterfacedObjectWithCustomCreate;
  TPersistentWithCustomCreateClass = class of TPersistentWithCustomCreate;

{$ifndef LVCL}
  /// a procedure-based background thread associated to a TSQLRestServer instance
  // - in addition to standard TSynBackgroundThreadProcedure behavior, this
  // inherited class will also notify the Server of this thread, calling
  // BeginCurrentThread and EndCurrentThread methods as required
  // - used e.g. when opt*InPerInterfaceThread is defined in
  // TServiceFactoryServer options
  TSynBackgroundThreadSQLRestServerProcedure = class(TSynBackgroundThreadProcedure)
................................................................................
    fServer: TSQLRestServer;
    // will call BeginCurrentThread / EndCurrentThread
    procedure Execute; override;
  public
    /// create the thread, ready to execute background process
    constructor Create(aServer: TSQLRestServer);
  end;
{$endif}

  /// a service provider implemented on the server side
  // - each registered interface has its own TServiceFactoryServer instance,
  // available as one TSQLServiceContainerServer item from TSQLRest.Services property
  // - will handle the implementation class instances of a given interface
  // - by default, all methods are allowed to execution: you can call AllowAll,
  // DenyAll, Allow or Deny in order to specify your exact security policy
................................................................................
    fInstanceLock: TRTLCriticalSection;
    fImplementationClass: TInterfacedClass;
    fImplementationClassWithCustomCreate: Boolean;
    fImplementationClassInterfaceEntry: PInterfaceEntry;
    fSharedInterface: IInterface;
    fByPassAuthentication: boolean;
    fResultAsJSONObject: boolean;

    /// union of all fExecution[].Options
    {$ifndef LVCL}
    fAnyOptions: TServiceMethodOptions;
    fBackgroundThread: TSynBackgroundThreadProcedure;
    {$endif}
    procedure SetTimeoutSecInt(value: cardinal);
    function GetTimeoutSec: cardinal;
    /// get an implementation Inst.Instance for the given Inst.InstanceID
    // - is called by ExecuteMethod() in sicClientDrive mode
    // - returns true for successfull {"method":"_free_".. call (aMethodIndex=-1)
    // - otherwise, fill Inst.Instance with the matching implementation (or nil)
    function InternalInstanceRetrieve(var Inst: TServiceFactoryServerInstance;
................................................................................
    TContent = (TableSimpleFields, UpdateSimple, UpdateSetAll, InsertAll);
  procedure SetSQL(W: TTextWriter;
    withID, withTableName: boolean; var result: RawUTF8;
    content: TContent=TableSimpleFields);
  var f: integer;
  begin
    W.CancelAll;
    if withID and (content=TableSimpleFields) then
      if withTableName then
        W.Add('%.%,',[TableName,RowIDFieldName]) else
        W.AddStrings([RowIDFieldName,',']);




    with fProps.Props do
    for f := 0 to Fields.Count-1 do
    with Fields.List[f] do
    if SQLFieldType in COPIABLE_FIELDS then // sftMany fields do not exist
      case content of
      TableSimpleFields:
        if f in SimpleFieldsBits[soSelect] then begin
          if withTableName then
            W.AddStrings([TableName,'.']);
          W.AddString(fFieldNames[f]);




          W.Add(',');
        end;
      UpdateSimple:
        if f in SimpleFieldsBits[soSelect] then
          W.AddStrings([fFieldNames[f],'=?,']);
      UpdateSetAll:
        W.AddStrings([fFieldNames[f],'=?,']);
      InsertAll:
        W.AddStrings([fFieldNames[f],',']);
      end;
    W.CancelLastComma;
    W.SetText(result);
  end;
var W: TTextWriter;
begin
  W := TTextWriter.CreateOwnedStream(1024);
................................................................................
  if ExternalTableName='' then
    fTableName := Props.Props.SQLTableName else
    fTableName := ExternalTableName;
  fConnectionProperties := ExternalDataBase;
  fProps := Props;
  fRowIDFieldName := 'ID';
  Props.Props.Fields.NamesToRawUTF8DynArray(fFieldNames);

  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.MapField(
  const InternalName, ExternalName: RawUTF8): PSQLModelRecordPropertiesExternal;
var int: integer;
begin
  int := fProps.Props.Fields.IndexByNameOrExcept(InternalName);
  if int<0 then
    fRowIDFieldName := ExternalName else
    fFieldNames[int] := ExternalName;
  ComputeSQL;

  result := @self;
end;

procedure TSQLModelRecordPropertiesExternal.MapFields(
  const InternalExternalPairs: array of RawUTF8);
var i,int: Integer;
begin
  for i := 0 to (length(InternalExternalPairs) shr 1)-1 do begin
    int := fProps.Props.Fields.IndexByNameOrExcept(InternalExternalPairs[i*2]);
    if int<0 then
      fRowIDFieldName := InternalExternalPairs[i*2+1] else




      fFieldNames[int] := InternalExternalPairs[i*2+1];




  end;
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.InternalToExternal(const FieldName: RawUTF8): RawUTF8;
var int: integer;
begin
................................................................................
function TSQLModelRecordPropertiesExternal.AppendFieldName(
  FieldIndex: Integer; var Text: RawUTF8): boolean;
begin
  result := false; // success
  if FieldIndex=VIRTUAL_TABLE_ROWID_COLUMN then
    Text := Text+RowIDFieldName else
  if cardinal(FieldIndex)>=cardinal(Length(FieldNames)) then
    result := true else
    Text := Text+FieldNames[FieldIndex];
end;


{ TSQLModelRecordProperties }

constructor TSQLModelRecordProperties.Create(aModel: TSQLModel;
................................................................................
    Session := CONST_AUTHENTICATION_NOT_USED;
  result := true;
end;

{$ifndef LVCL}
type
  TThreadHook = class(TThread);



  TCallMethodSynchro = record
    Action: (syncCallMethod, syncInstanceRelease);
    CallMethodArgs: pointer;
    InstanceToRelease: TInterfacedObjectWithCustomCreate;
  end;
{$endif}

procedure TSQLRestServerURIContext.Execute(Command: TSQLRestServerURIContextCommand);
procedure TimeOut;
begin
  {$ifdef WITHLOG}
  Log.Log(sllServer,'TimeOut %.Execute(%) after % ms',[ClassName,
    GetEnumName(TypeInfo(TSQLRestServerURIContextCommand),ord(Command))^,
................................................................................
  if self=nil then
    result := '' else
    result := Contract; // just return the current value
end;

procedure TServiceFactoryServerInstance.SafeFreeInstance(Factory: TServiceFactoryServer);
var Obj: TInterfacedObject;
{$ifndef LVCL}
    Synch: TCallMethodSynchro;
{$endif}
begin
  InstanceID := 0;
  Obj := Instance;
  Instance := nil;
  {$ifndef LVCL}
  if (optFreeInMainThread in Factory.fAnyOptions) and
     (GetCurrentThreadID<>MainThreadID) then
    {$ifdef DELPHI6OROLDER}TThreadHook(nil).Synchronize(
    {$else}                TThread.Synchronize(nil,
    {$endif}  TInterfacedObjectWithCustomCreate(Obj).InternalRelease) else

  if (optFreeInPerInterfaceThread in Factory.fAnyOptions) and
     Assigned(Factory.fBackgroundThread) then begin
    Synch.Action := syncInstanceRelease;
    Synch.InstanceToRelease := TInterfacedObjectWithCustomCreate(Obj);
    Factory.fBackgroundThread.RunAndWait(@Synch);
  end else
  {$endif}
    IInterface(Obj)._Release;
end;

function TServiceFactoryServer.InternalInstanceRetrieve(
  var Inst: TServiceFactoryServerInstance; aMethodIndex: integer): boolean;
procedure AddNew;
var i: integer;
................................................................................
  end;
  Ctxt.ServiceInstanceID := Inst.InstanceID;
  // 2. call method implementation
  try
    entry := Inst.Instance.GetInterfaceEntry(fInterface.fInterfaceIID);
    if entry=nil then
      exit;
    {$ifndef LVCL}
    if optExecInPerInterfaceThread in fExecution[Ctxt.ServiceMethodIndex].Options then
      if fBackgroundThread=nil then
        fBackgroundThread := TSynBackgroundThreadSQLRestServerProcedure.Create(RestServer);
    {$endif}
    ThreadServer := @ServiceContext;
    WR := TJSONSerializer.CreateOwnedStream;
    try
      with ThreadServer^ do begin
        Factory := self;
        Request := Ctxt;
      end; // RunningThread is already set at thread initialization
................................................................................
var m,i: integer;
begin
  if self<>nil then begin
    if (fInstanceCreation=sicPerThread) and (optExecLockedPerInterface in aOptions) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if (fInstanceCreation=sicPerThread) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'opt*In*Thread option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);

    if (optExecLockedPerInterface in aOptions) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface with opt*In*Thread options for I%s interface',
        [fInterfaceURI]);
    {$endif}
    if high(aMethod)<0 then
      for i := 0 to fInterface.fMethodsCount-1 do
        fExecution[i].Options := aOptions else
    for m := 0 to high(aMethod) do
      fExecution[fInterface.CheckMethodIndex(aMethod[m])].Options := aOptions;
    {$ifndef LVCL}
    fAnyOptions := [];
    for i := 0 to fInterface.fMethodsCount-1 do
      fAnyOptions := fAnyOptions+fExecution[i].Options;
    if (optFreeInPerInterfaceThread in fAnyOptions) and
       not (optExecInPerInterfaceThread in fAnyOptions) then
      raise EServiceException.CreateFmt(
        'optFreeInPerInterfaceThread without optExecInPerInterfaceThread for I%s interface',
        [fInterfaceURI]);

    if ([optExecInMainThread,optFreeInMainThread]*fAnyOptions<>[]) and
       ([optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*fAnyOptions<>[]) then
      raise EServiceException.CreateFmt(
        'Concurrent opt*InMainThread and opt*InPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$endif}
  end;
................................................................................
    mov [esi].TCallMethodArgs.res64.Hi,edx
@e: mov esp,ebp
    pop ebp
    pop esi
end;
{$endif CPU64}

{$ifndef LVCL}

procedure BackgroundExecuteProc(Call: Pointer);
var Synch: ^TCallMethodSynchro absolute Call;
    ThreadServer: PServiceRunningContext;
    backup: TServiceRunningContext;
begin
  case Synch.Action of
  syncCallMethod: begin
................................................................................
procedure CallMethodSynch(Args: pointer);
var Synch: TCallMethodSynchro;
begin
  Synch.Action := syncCallMethod;
  Synch.CallMethodArgs := Args;
  BackgroundExecuteProc(@Synch);
end;



type
  TCollectionClass = class of TInterfacedCollection;

{$endif LVCL}

function TServiceMethod.ArgResultIndex(ArgName: PUTF8Char; ArgNameLen: integer): integer;
................................................................................
    WideStrings: TWideStringDynArray;
    Records: array of TBytes;
    Value: pointer;
    i,a: integer;
    wasString, valid: boolean;
    Val: PUTF8Char;
    r: TCallMethodArgs;
{$ifndef LVCL}
    SyncMethod: TMethod;
    Synch: TCallMethodSynchro;
{$endif}

    Stack: array[0..MAX_EXECSTACK-1] of byte;
    Int64s: array[0..MAX_METHOD_ARGS-1] of Int64;
    Objects: array[0..MAX_METHOD_ARGS-1] of TObject;
    DynArrays: array[0..MAX_METHOD_ARGS-1] of TDynArrayFake;
    Values: array[0..MAX_METHOD_ARGS-1] of PPointer;
begin
  result := false;
................................................................................
        r.callContext := @ServiceContext; // to be copied into main threadvar
        SyncMethod.Code := @CallMethodSynch;
        SyncMethod.Data := @r; // fake call: PCallMethodArgs(self)^=Params
        {$ifdef DELPHI6OROLDER}TThreadHook(r.callContext^.RunningThread).Synchronize(
        {$else}                TThread.Synchronize(r.callContext^.RunningThread,
        {$endif}  TThreadMethod(SyncMethod));
      end else

      if optExecInPerInterfaceThread in Options then
        if not Assigned(BackgroundExecutionThread) then
          raise EInterfaceFactoryException.Create(
            'optExecInPerInterfaceThread with BackgroundExecutionThread=nil') else begin
        r.callContext := @ServiceContext; // to be copied into background threadvar
        Synch.Action := syncCallMethod;
        Synch.CallMethodArgs := @r;
        BackgroundExecutionThread.RunAndWait(@Synch);
      end else
      {$endif}
        CallMethod(r);
    end;
    // 4. send back any result
    if Res<>nil then begin
      // 4.1 handle custom content (not JSON object answer)
      if (r.resKind=smvRecord) and ArgsResultIsServiceCustomAnswer then
        with PServiceCustomAnswer(Values[ArgsResultIndex])^ do
................................................................................
        end;
        {$endif}
        end;
    end;
  end;
end;

{$ifndef LVCL}

{ TSynBackgroundThreadSQLRestServerProcedure }

constructor TSynBackgroundThreadSQLRestServerProcedure.Create(aServer: TSQLRestServer);
begin
  inherited Create(BackgroundExecuteProc,nil);
  fServer := aServer;
................................................................................
  try
    inherited Execute;
  finally
    fServer.EndCurrentThread(self);
  end;
end;

{$endif LVCL}


{ TServiceContainerClient }

function TServiceContainerClient.Info(aTypeInfo: PTypeInfo): TServiceFactory;
begin
  result := inherited Info(aTypeInfo);
  if result=nil then







>







 







>
>
>
>







 







|
>


<







 







<







 







<







 







>

<

<
<







 







|

|
|
>
>
>
>









|
>
>
>
>




|

|

|







 







>





<

<
<
<
<
<
>









|
|
>
>
>
>

>
>
>
>







 







|







 







>

>





<







 







<

<










>






<







 







<



<







 







<

|




>












<








>







 







<
<







 







>
>







 







|

<
|
>







 







>









<







 







<







 







<
<







5507
5508
5509
5510
5511
5512
5513
5514
5515
5516
5517
5518
5519
5520
5521
....
5589
5590
5591
5592
5593
5594
5595
5596
5597
5598
5599
5600
5601
5602
5603
5604
5605
5606
....
6533
6534
6535
6536
6537
6538
6539
6540
6541
6542
6543

6544
6545
6546
6547
6548
6549
6550
....
7574
7575
7576
7577
7578
7579
7580

7581
7582
7583
7584
7585
7586
7587
....
7604
7605
7606
7607
7608
7609
7610

7611
7612
7613
7614
7615
7616
7617
....
7625
7626
7627
7628
7629
7630
7631
7632
7633

7634


7635
7636
7637
7638
7639
7640
7641
.....
20411
20412
20413
20414
20415
20416
20417
20418
20419
20420
20421
20422
20423
20424
20425
20426
20427
20428
20429
20430
20431
20432
20433
20434
20435
20436
20437
20438
20439
20440
20441
20442
20443
20444
20445
20446
20447
20448
20449
20450
20451
20452
20453
20454
20455
.....
20476
20477
20478
20479
20480
20481
20482
20483
20484
20485
20486
20487
20488

20489





20490
20491
20492
20493
20494
20495
20496
20497
20498
20499
20500
20501
20502
20503
20504
20505
20506
20507
20508
20509
20510
20511
20512
20513
20514
20515
20516
20517
.....
20571
20572
20573
20574
20575
20576
20577
20578
20579
20580
20581
20582
20583
20584
20585
.....
24218
24219
24220
24221
24222
24223
24224
24225
24226
24227
24228
24229
24230
24231
24232

24233
24234
24235
24236
24237
24238
24239
.....
34106
34107
34108
34109
34110
34111
34112

34113

34114
34115
34116
34117
34118
34119
34120
34121
34122
34123
34124
34125
34126
34127
34128
34129
34130

34131
34132
34133
34134
34135
34136
34137
.....
34274
34275
34276
34277
34278
34279
34280

34281
34282
34283

34284
34285
34286
34287
34288
34289
34290
.....
34446
34447
34448
34449
34450
34451
34452

34453
34454
34455
34456
34457
34458
34459
34460
34461
34462
34463
34464
34465
34466
34467
34468
34469
34470
34471

34472
34473
34474
34475
34476
34477
34478
34479
34480
34481
34482
34483
34484
34485
34486
34487
.....
34694
34695
34696
34697
34698
34699
34700


34701
34702
34703
34704
34705
34706
34707
.....
34725
34726
34727
34728
34729
34730
34731
34732
34733
34734
34735
34736
34737
34738
34739
34740
.....
34769
34770
34771
34772
34773
34774
34775
34776
34777

34778
34779
34780
34781
34782
34783
34784
34785
34786
.....
34915
34916
34917
34918
34919
34920
34921
34922
34923
34924
34925
34926
34927
34928
34929
34930
34931

34932
34933
34934
34935
34936
34937
34938
.....
34974
34975
34976
34977
34978
34979
34980

34981
34982
34983
34984
34985
34986
34987
.....
35012
35013
35014
35015
35016
35017
35018


35019
35020
35021
35022
35023
35024
35025
    fProps: TSQLModelRecordProperties;
    /// storage of main read-only properties
    fConnectionProperties: TObject;
    fTableName: RawUTF8;
    fRowIDFieldName: RawUTF8;
    fFieldNames: TRawUTF8DynArray;
    fSQL: TSQLModelRecordPropertiesSQL;
    fFieldNamesMatchInternal: TSQLFieldBits;
    /// fill fRowIDFieldName/fSQL with the current information
    procedure ComputeSQL;
  public
    /// add a custom field mapping
    // - will re-compute all needed SQL statements as needed, and initialize
    // fSortedFieldsName[] and fSortedFieldsIndex[] internal sorted arrays
    // - can be used e.g. as
................................................................................
    // database engines (e.g. Oracle)
    // - can be customized e.g. via
    // ! aModel.Props[TSQLMyExternal].ExternalDB.MapField('ID','ExternalID');
    property RowIDFieldName: RawUTF8 read fRowIDFieldName;
    /// the external field names, following fProps.Props.Field[] order
    // - excluding ID/RowID field, which is stored in RowIDFieldName
    property FieldNames: TRawUTF8DynArray read fFieldNames;
    /// each bit set, following fProps.Props.Field[]+1 order (i.e. 0=ID,
    // 1=Field[0], ...), indicates that this external field name
    // has not been mapped
    property FieldNamesMatchInternal: TSQLFieldBits read fFieldNamesMatchInternal;
  end;

  /// ORM properties associated to a TSQLRecord within a given model
  // - "stable" / common properties derivated from RTTI are shared in the
  // TSQLRecordProperties instance
  // - since the same TSQLRecord can be defined in several models, with diverse
  // implementation patterns (e.g. internal in one, external in another),
................................................................................

  /// describe a service provider method arguments
  TServiceMethodArgumentDynArray = array of TServiceMethodArgument;

  /// possible service provider method options, e.g. about logging or execution
  // - see TServiceMethodOptions for a description of each available option
  TServiceMethodOption = (
    optExecLockedPerInterface,
    optExecInPerInterfaceThread, optFreeInPerInterfaceThread
    {$ifndef LVCL},
    optExecInMainThread, optFreeInMainThread,

    optVariantCopiedByReference
    {$endif}
  );

  /// set of per-method execution options for an interface-based service provider
  // - by default, mehthod executions are concurrent, for better server
  // responsiveness; if you set optExecLockedPerInterface, all methods of
................................................................................
    /// this virtual constructor will be called at instance creation
    constructor Create; virtual;
  end;

  TInterfacedObjectWithCustomCreateClass = class of TInterfacedObjectWithCustomCreate;
  TPersistentWithCustomCreateClass = class of TPersistentWithCustomCreate;


  /// a procedure-based background thread associated to a TSQLRestServer instance
  // - in addition to standard TSynBackgroundThreadProcedure behavior, this
  // inherited class will also notify the Server of this thread, calling
  // BeginCurrentThread and EndCurrentThread methods as required
  // - used e.g. when opt*InPerInterfaceThread is defined in
  // TServiceFactoryServer options
  TSynBackgroundThreadSQLRestServerProcedure = class(TSynBackgroundThreadProcedure)
................................................................................
    fServer: TSQLRestServer;
    // will call BeginCurrentThread / EndCurrentThread
    procedure Execute; override;
  public
    /// create the thread, ready to execute background process
    constructor Create(aServer: TSQLRestServer);
  end;


  /// a service provider implemented on the server side
  // - each registered interface has its own TServiceFactoryServer instance,
  // available as one TSQLServiceContainerServer item from TSQLRest.Services property
  // - will handle the implementation class instances of a given interface
  // - by default, all methods are allowed to execution: you can call AllowAll,
  // DenyAll, Allow or Deny in order to specify your exact security policy
................................................................................
    fInstanceLock: TRTLCriticalSection;
    fImplementationClass: TInterfacedClass;
    fImplementationClassWithCustomCreate: Boolean;
    fImplementationClassInterfaceEntry: PInterfaceEntry;
    fSharedInterface: IInterface;
    fByPassAuthentication: boolean;
    fResultAsJSONObject: boolean;
    fBackgroundThread: TSynBackgroundThreadProcedure;
    /// union of all fExecution[].Options

    fAnyOptions: TServiceMethodOptions;


    procedure SetTimeoutSecInt(value: cardinal);
    function GetTimeoutSec: cardinal;
    /// get an implementation Inst.Instance for the given Inst.InstanceID
    // - is called by ExecuteMethod() in sicClientDrive mode
    // - returns true for successfull {"method":"_free_".. call (aMethodIndex=-1)
    // - otherwise, fill Inst.Instance with the matching implementation (or nil)
    function InternalInstanceRetrieve(var Inst: TServiceFactoryServerInstance;
................................................................................
    TContent = (TableSimpleFields, UpdateSimple, UpdateSetAll, InsertAll);
  procedure SetSQL(W: TTextWriter;
    withID, withTableName: boolean; var result: RawUTF8;
    content: TContent=TableSimpleFields);
  var f: integer;
  begin
    W.CancelAll;
    if withID and (content=TableSimpleFields) then begin
      if withTableName then
        W.Add('%.%',[TableName,RowIDFieldName]) else
        W.AddString(RowIDFieldName);
      if 0 in FieldNamesMatchInternal then
        W.Add(',') else
        W.AddShort(' as ID,');
    end;
    with fProps.Props do
    for f := 0 to Fields.Count-1 do
    with Fields.List[f] do
    if SQLFieldType in COPIABLE_FIELDS then // sftMany fields do not exist
      case content of
      TableSimpleFields:
        if f in SimpleFieldsBits[soSelect] then begin
          if withTableName then
            W.AddStrings([TableName,'.']);
          W.AddString(FieldNames[f]);
          if not(f+1 in FieldNamesMatchInternal) then begin
            W.AddShort(' as ');
            W.AddString(Name); // AS [InternalName] to get expected JSON column
          end;
          W.Add(',');
        end;
      UpdateSimple:
        if f in SimpleFieldsBits[soSelect] then
          W.AddStrings([FieldNames[f],'=?,']);
      UpdateSetAll:
        W.AddStrings([FieldNames[f],'=?,']);
      InsertAll:
        W.AddStrings([FieldNames[f],',']);
      end;
    W.CancelLastComma;
    W.SetText(result);
  end;
var W: TTextWriter;
begin
  W := TTextWriter.CreateOwnedStream(1024);
................................................................................
  if ExternalTableName='' then
    fTableName := Props.Props.SQLTableName else
    fTableName := ExternalTableName;
  fConnectionProperties := ExternalDataBase;
  fProps := Props;
  fRowIDFieldName := 'ID';
  Props.Props.Fields.NamesToRawUTF8DynArray(fFieldNames);
  FillChar(fFieldNamesMatchInternal,sizeof(fFieldNamesMatchInternal),255);
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.MapField(
  const InternalName, ExternalName: RawUTF8): PSQLModelRecordPropertiesExternal;

begin





  MapFields([InternalName,ExternalName]);
  result := @self;
end;

procedure TSQLModelRecordPropertiesExternal.MapFields(
  const InternalExternalPairs: array of RawUTF8);
var i,int: Integer;
begin
  for i := 0 to (length(InternalExternalPairs) shr 1)-1 do begin
    int := fProps.Props.Fields.IndexByNameOrExcept(InternalExternalPairs[i*2]);
    if int<0 then begin
      fRowIDFieldName := InternalExternalPairs[i*2+1];
      if IdemPropNameU(fRowIDFieldName,'ID') then
        include(fFieldNamesMatchInternal,0) else     // [0]=ID
        exclude(fFieldNamesMatchInternal,0);
    end else begin
      fFieldNames[int] := InternalExternalPairs[i*2+1];
      if IdemPropNameU(fFieldNames[int],fProps.Props.Fields.List[int].Name) then
        include(fFieldNamesMatchInternal,int+1) else // [0]=ID
        exclude(fFieldNamesMatchInternal,int+1);
    end;
  end;
  ComputeSQL;
end;

function TSQLModelRecordPropertiesExternal.InternalToExternal(const FieldName: RawUTF8): RawUTF8;
var int: integer;
begin
................................................................................
function TSQLModelRecordPropertiesExternal.AppendFieldName(
  FieldIndex: Integer; var Text: RawUTF8): boolean;
begin
  result := false; // success
  if FieldIndex=VIRTUAL_TABLE_ROWID_COLUMN then
    Text := Text+RowIDFieldName else
  if cardinal(FieldIndex)>=cardinal(Length(FieldNames)) then
    result := true else // FieldIndex out of range
    Text := Text+FieldNames[FieldIndex];
end;


{ TSQLModelRecordProperties }

constructor TSQLModelRecordProperties.Create(aModel: TSQLModel;
................................................................................
    Session := CONST_AUTHENTICATION_NOT_USED;
  result := true;
end;

{$ifndef LVCL}
type
  TThreadHook = class(TThread);
{$endif}

type
  TCallMethodSynchro = record
    Action: (syncCallMethod, syncInstanceRelease);
    CallMethodArgs: pointer;
    InstanceToRelease: TInterfacedObjectWithCustomCreate;
  end;


procedure TSQLRestServerURIContext.Execute(Command: TSQLRestServerURIContextCommand);
procedure TimeOut;
begin
  {$ifdef WITHLOG}
  Log.Log(sllServer,'TimeOut %.Execute(%) after % ms',[ClassName,
    GetEnumName(TypeInfo(TSQLRestServerURIContextCommand),ord(Command))^,
................................................................................
  if self=nil then
    result := '' else
    result := Contract; // just return the current value
end;

procedure TServiceFactoryServerInstance.SafeFreeInstance(Factory: TServiceFactoryServer);
var Obj: TInterfacedObject;

    Synch: TCallMethodSynchro;

begin
  InstanceID := 0;
  Obj := Instance;
  Instance := nil;
  {$ifndef LVCL}
  if (optFreeInMainThread in Factory.fAnyOptions) and
     (GetCurrentThreadID<>MainThreadID) then
    {$ifdef DELPHI6OROLDER}TThreadHook(nil).Synchronize(
    {$else}                TThread.Synchronize(nil,
    {$endif}  TInterfacedObjectWithCustomCreate(Obj).InternalRelease) else
  {$endif}
  if (optFreeInPerInterfaceThread in Factory.fAnyOptions) and
     Assigned(Factory.fBackgroundThread) then begin
    Synch.Action := syncInstanceRelease;
    Synch.InstanceToRelease := TInterfacedObjectWithCustomCreate(Obj);
    Factory.fBackgroundThread.RunAndWait(@Synch);
  end else

    IInterface(Obj)._Release;
end;

function TServiceFactoryServer.InternalInstanceRetrieve(
  var Inst: TServiceFactoryServerInstance; aMethodIndex: integer): boolean;
procedure AddNew;
var i: integer;
................................................................................
  end;
  Ctxt.ServiceInstanceID := Inst.InstanceID;
  // 2. call method implementation
  try
    entry := Inst.Instance.GetInterfaceEntry(fInterface.fInterfaceIID);
    if entry=nil then
      exit;

    if optExecInPerInterfaceThread in fExecution[Ctxt.ServiceMethodIndex].Options then
      if fBackgroundThread=nil then
        fBackgroundThread := TSynBackgroundThreadSQLRestServerProcedure.Create(RestServer);

    ThreadServer := @ServiceContext;
    WR := TJSONSerializer.CreateOwnedStream;
    try
      with ThreadServer^ do begin
        Factory := self;
        Request := Ctxt;
      end; // RunningThread is already set at thread initialization
................................................................................
var m,i: integer;
begin
  if self<>nil then begin
    if (fInstanceCreation=sicPerThread) and (optExecLockedPerInterface in aOptions) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);

    if (fInstanceCreation=sicPerThread) and
       ([{$ifndef LVCL}optExecInMainThread,optFreeInMainThread,{$endif}
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'opt*In*Thread option not compatible with sicPerThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if (optExecLockedPerInterface in aOptions) and
       ([optExecInMainThread,optFreeInMainThread,
         optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*aOptions<>[]) then
      raise EServiceException.CreateFmt(
        'optExecLockedPerInterface with opt*In*Thread options for I%s interface',
        [fInterfaceURI]);
    {$endif}
    if high(aMethod)<0 then
      for i := 0 to fInterface.fMethodsCount-1 do
        fExecution[i].Options := aOptions else
    for m := 0 to high(aMethod) do
      fExecution[fInterface.CheckMethodIndex(aMethod[m])].Options := aOptions;

    fAnyOptions := [];
    for i := 0 to fInterface.fMethodsCount-1 do
      fAnyOptions := fAnyOptions+fExecution[i].Options;
    if (optFreeInPerInterfaceThread in fAnyOptions) and
       not (optExecInPerInterfaceThread in fAnyOptions) then
      raise EServiceException.CreateFmt(
        'optFreeInPerInterfaceThread without optExecInPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$ifndef LVCL}
    if ([optExecInMainThread,optFreeInMainThread]*fAnyOptions<>[]) and
       ([optExecInPerInterfaceThread,optFreeInPerInterfaceThread]*fAnyOptions<>[]) then
      raise EServiceException.CreateFmt(
        'Concurrent opt*InMainThread and opt*InPerInterfaceThread for I%s interface',
        [fInterfaceURI]);
    {$endif}
  end;
................................................................................
    mov [esi].TCallMethodArgs.res64.Hi,edx
@e: mov esp,ebp
    pop ebp
    pop esi
end;
{$endif CPU64}



procedure BackgroundExecuteProc(Call: Pointer);
var Synch: ^TCallMethodSynchro absolute Call;
    ThreadServer: PServiceRunningContext;
    backup: TServiceRunningContext;
begin
  case Synch.Action of
  syncCallMethod: begin
................................................................................
procedure CallMethodSynch(Args: pointer);
var Synch: TCallMethodSynchro;
begin
  Synch.Action := syncCallMethod;
  Synch.CallMethodArgs := Args;
  BackgroundExecuteProc(@Synch);
end;

{$ifndef LVCL}

type
  TCollectionClass = class of TInterfacedCollection;

{$endif LVCL}

function TServiceMethod.ArgResultIndex(ArgName: PUTF8Char; ArgNameLen: integer): integer;
................................................................................
    WideStrings: TWideStringDynArray;
    Records: array of TBytes;
    Value: pointer;
    i,a: integer;
    wasString, valid: boolean;
    Val: PUTF8Char;
    r: TCallMethodArgs;
    {$ifndef LVCL}
    SyncMethod: TMethod;

    {$endif}
    Synch: TCallMethodSynchro;
    Stack: array[0..MAX_EXECSTACK-1] of byte;
    Int64s: array[0..MAX_METHOD_ARGS-1] of Int64;
    Objects: array[0..MAX_METHOD_ARGS-1] of TObject;
    DynArrays: array[0..MAX_METHOD_ARGS-1] of TDynArrayFake;
    Values: array[0..MAX_METHOD_ARGS-1] of PPointer;
begin
  result := false;
................................................................................
        r.callContext := @ServiceContext; // to be copied into main threadvar
        SyncMethod.Code := @CallMethodSynch;
        SyncMethod.Data := @r; // fake call: PCallMethodArgs(self)^=Params
        {$ifdef DELPHI6OROLDER}TThreadHook(r.callContext^.RunningThread).Synchronize(
        {$else}                TThread.Synchronize(r.callContext^.RunningThread,
        {$endif}  TThreadMethod(SyncMethod));
      end else
      {$endif}
      if optExecInPerInterfaceThread in Options then
        if not Assigned(BackgroundExecutionThread) then
          raise EInterfaceFactoryException.Create(
            'optExecInPerInterfaceThread with BackgroundExecutionThread=nil') else begin
        r.callContext := @ServiceContext; // to be copied into background threadvar
        Synch.Action := syncCallMethod;
        Synch.CallMethodArgs := @r;
        BackgroundExecutionThread.RunAndWait(@Synch);
      end else

        CallMethod(r);
    end;
    // 4. send back any result
    if Res<>nil then begin
      // 4.1 handle custom content (not JSON object answer)
      if (r.resKind=smvRecord) and ArgsResultIsServiceCustomAnswer then
        with PServiceCustomAnswer(Values[ArgsResultIndex])^ do
................................................................................
        end;
        {$endif}
        end;
    end;
  end;
end;



{ TSynBackgroundThreadSQLRestServerProcedure }

constructor TSynBackgroundThreadSQLRestServerProcedure.Create(aServer: TSQLRestServer);
begin
  inherited Create(BackgroundExecuteProc,nil);
  fServer := aServer;
................................................................................
  try
    inherited Execute;
  finally
    fServer.EndCurrentThread(self);
  end;
end;




{ TServiceContainerClient }

function TServiceContainerClient.Info(aTypeInfo: PTypeInfo): TServiceFactory;
begin
  result := inherited Info(aTypeInfo);
  if result=nil then

Changes to SQLite3/mORMotDB.pas.

206
207
208
209
210
211
212
213


214
215
216
217
218
219
220
...
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
...
576
577
578
579
580
581
582
583



584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
...
639
640
641
642
643
644
645
646
647
648
649
650
651



652
653
654
655
656
657
658




659
660
661
662
663
664
665
...
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
...
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
...
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
....
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
....
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
....
1318
1319
1320
1321
1322
1323
1324
1325

1326
1327
1328
1329
1330
1331
1332
....
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
....
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
....
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
    // to remote database engine (e.g. Oracle bound arrays or MS SQL Bulk insert)
    procedure InternalBatchStop; override;
    /// called internally by EngineAdd/EngineUpdate/EngineDelete in batch mode
    function InternalBatchAdd(const aValue: RawUTF8; aID: integer): integer;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
    // - overriden method to handle most potential simple queries, e.g. like
    // $ SELECT Field1,RowID FROM table WHERE RowID=... AND/OR/NOT Field2=
    // - change 'RowID' into 'ID' column name, and SQLTableName into fTableName


    // - any 'LIMIT #' clause will be changed into the appropriate SQL statement
    // - handle statements to avoid slow virtual table loop over all rows, like
    // $ SELECT count(*) FROM table
    function AdaptSQLForEngineList(var SQL: RawUTF8): boolean; override;
    /// run INSERT of UPDATE from the corresponding JSON object
    // - Occasion parameter shall be only either soInsert or soUpate
    // - each JSON field will be bound with the proper SQL type corresponding to
................................................................................
    CreateColumns: TSQLDBColumnPropertyDynArray;
begin
  inherited Create(aClass,aServer,aFileName,aBinaryFile);
  // initialize external DB properties
  if fStoredClassProps=nil then
    raise EBusinessLayerException.CreateFmt(
      'StoredClassProps needed for %s',[StoredClassRecordProps.SQLTableName]);
  fTableName := fStoredClassProps.ExternalDB.TableName;
  fProperties := fStoredClassProps.ExternalDB.ConnectionProperties as TSQLDBConnectionProperties;
  if fProperties=nil then
    raise EBusinessLayerException.CreateFmt(
      'No external DB defined for %s',[StoredClassRecordProps.SQLTableName]);
  if Owner<>nil then
    try
      Owner.ServerTimeStamp := fProperties.ThreadSafeConnection.ServerTimeStamp;
    except
................................................................................
      on E: Exception do ; // ignore any error here
    end;
  // create corresponding external table if necessary, and retrieve its fields info
  fProperties.GetFields(fTableName,fFieldsExternal);
  if fFieldsExternal=nil then begin
    // table is not yet existing -> try to create it
    with aClass.RecordProps do begin
      SetLength(CreateColumns,Fields.Count);



      f := 0;
      for i := 0 to Fields.Count-1 do
        if PropInfoToExternalField(Fields.List[i],CreateColumns[f]) then
          inc(f);
      if f<>Fields.Count then
        SetLength(CreateColumns,f); // just ignore non handled field types
    end;
    SQL := fProperties.SQLCreate(fTableName,CreateColumns);
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        fProperties.GetFields(fTableName,fFieldsExternal); // fields from DB after create
        if fFieldsExternal=nil then
          raise EORMException.CreateFmt(
            '%s: external table creation %s failed: GetFields() returned nil - SQL="%s"',
            [fStoredClass.ClassName,fTableName,SQL]);
................................................................................
end;

function TSQLRestServerStaticExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var Prop: ShortString; // to avoid any temporary memory allocation
    P: PUTF8Char;
    W: TTextWriter;

  function PropHandleField: boolean;
  var int: integer;
  begin
    result := true;
    if IsRowIDShort(Prop) then begin
      W.AddString(StoredClassProps.ExternalDB.RowIDFieldName);



      exit;
    end;
    Prop[ord(Prop[0])+1] := #0; // make ASCIIZ
    int := StoredClassRecordProps.Fields.IndexByName(@Prop[1]);
    if int<0 then
      result := false else
      W.AddString(StoredClassProps.ExternalDB.FieldNames[int]);




  end;
  procedure GetFieldProp;
  var i,L: integer;
      B: PUTF8Char;
  begin
    Prop[0] := #0;
    if P^=#0 then
................................................................................
    W.AddShort(Prop);
    W.Add(' ');
    GetFieldProp;
  end;
  function NextPropHandleField: boolean;
  begin
    GetFieldProp;
    result := PropHandleField;
  end;
  function NextPropHandleInternalTable: boolean;
  begin
    GetFieldProp;
    with StoredClassRecordProps do
      if IdemPropName(Prop,pointer(SQLTableName),length(SQLTableName)) then begin
        W.AddString(fTableName);
        result := true;
      end else
        result := false;
  end;

label Order,Limit;
var Pos: record AfterSelect, WhereClause, Limit, LimitRowCount: integer; end;
    B: PUTF8Char;
    err: integer;
    NewSQL: RawUTF8;
................................................................................
        W.AddShort('count(*)');
        if P^ in [#0,';'] then begin
          result := NextPropHandleInternalTable;
          exit;
        end;
        break; // will process 'select count(*) from tablename where ...'
      end else
      if not PropHandleField then
        exit; // unknown field name
      if P^=',' then begin
        W.Add(',');
        inc(P);
      end else begin
        GetFieldProp;
        if Prop<>'FROM' then
................................................................................
        if Prop<>'' then
          exit; // unexpected clause 
      if not (GotoNextNotSpace(P)^ in [#0,';']) then
        exit; // allow only one column name or one LIMIT ### expression
    end else
    if Prop='WHERE' then
    repeat
      WritePropAndGetFieldProp;
      Pos.WhereClause := W.TextLength+1;
      if Prop='NOT' then
        WritePropAndGetFieldProp; // allow  field1=456 AND NOT field2='Toto'
      if (Prop='') or not PropHandleField then
        exit; // unknown field name or 'LIMIT' / 'ORDER BY' clause
      B := P;
      if P^='=' then
        inc(P) else
      if P^ in ['>','<'] then
        if P[1] in ['=','>'] then
          inc(P,2) else
................................................................................
end;

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
var Stmt: ISQLDBStatement;
begin
  if ReturnedRowCount<>nil then
    raise ESQLDBException.Create('TSQLRestServerStaticExternal.EngineList(ReturnedRowCount<>nil)');
  Stmt := PrepareInlinedForRows(SQL);
  if Stmt=nil then
    result := '' else
    Stmt.ExecutePreparedAndFetchAllAsJSON(ForceAJAX or (not NoAJAXJSON),result);
end;

function TSQLRestServerStaticExternal.EngineRetrieve(TableModelIndex, ID: integer): RawUTF8;
................................................................................
end;

function TSQLRestServerStaticExternal.TableRowCount(Table: TSQLRecordClass): integer;
var Rows: ISQLDBRows;
begin
  if (self=nil) or (Table<>fStoredClass) then
    result := 0 else begin
    Rows := ExecuteDirect('SELECT count(*) FROM %',[fTableName],[],true);
    if (Rows=nil) or not Rows.Step then
      result := 0 else
      result := Rows.ColumnInt(0);
  end;
end;

function TSQLRestServerStaticExternal.EngineRetrieveBlob(
................................................................................

function TSQLRestServerStaticExternal.ExecuteInlined(SQLFormat: PUTF8Char;
  const Args: array of const; ExpectResults: Boolean): ISQLDBRows;
begin
  result := ExecuteInlined(FormatUTF8(SQLFormat,Args),ExpectResults);
end;

function TSQLRestServerStaticExternal.PrepareDirectForRows(SQLFormat: PUTF8Char; const Args, Params: array of const): ISQLDBStatement;

var Query: ISQLDBStatement;
begin
  result := nil;
  if self=nil then
    exit;
  Query := fProperties.NewThreadSafeStatementPrepared(SQLFormat,Args,true);
  if Query<>nil then
................................................................................
  const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: integer): integer;
var Decoder: TJSONObjectDecoder;
    SQL: RawUTF8;
    Types: TSQLDBFieldTypeArray;
    ExternalFields: TRawUTF8DynArray;
    InsertedID, F: integer;
    Query: ISQLDBStatement;
    P: PUTF8Char;
begin
  result := 0;
  Lock(false); // avoid race condition against max(ID)
  try
    case Occasion of
    soInsert: begin
      InsertedID := JSONRetrieveIDField(pointer(SentData));
................................................................................
    // execute statement
    Query := fProperties.NewThreadSafeStatementPrepared(SQL,false);
    if Query=nil then
      exit;
    try
      for F := 0 to Decoder.FieldCount-1 do
      if F in Decoder.FieldNull then
        Query.BindNull(F+1) else begin
        P := pointer(Decoder.FieldValues[F]);
        case Types[F] of
        ftInt64:    Query.Bind(F+1,GetInt64(P));
        ftDouble:   Query.Bind(F+1,GetExtended(P));
        ftDate:     Query.BindDateTime(F+1,Iso8601ToDateTimePUTF8Char(P));
        ftCurrency: Query.BindCurrency(F+1,StrToCurrency(P));
        ftBlob:     Query.BindBlob(F+1,Decoder.FieldValues[F]);
        ftUTF8:     Query.BindTextU(F+1,Decoder.FieldValues[F]);
        else raise ESQLDBException.CreateFmt(
          'ExecuteFromJSON: Invalid Types[%d]=%d',[F,ord(result)]);
        end;
      end;
      Query.ExecutePrepared;
    except
      exit; // leave result=0
    end;
    // mark success
    if UpdatedID=0 then
      result := InsertedID else
................................................................................
  if (self<>nil) and (Static<>nil) and
     (oldRowID=newRowID) and (newRowID>0) then // don't allow ID change
    with Static as TSQLRestServerStaticExternal, StoredClassProps.ExternalDB do
      result := ExecuteDirectSQLVar('update % set % where %=?',
        [fTableName,SQL.UpdateSetAll,RowIDFieldName],Values,oldRowID,true) else
    result := false;
end;



initialization
  // all our SynDB related functions shall log to main TSQLLog
  SynDBLog := TSQLLog;
end.







|
>
>







 







|
|







 







|
>
>
>
|



|


|







 







|



|
|
>
>
>






|
>
>
>
>







 







|





|
|
|
|
|







 







|







 







|



|







 







|







 







|







 







|
>







 







<







 







|
|
<
<
<
<
<
<
<
<
<
<
<







 







<






206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
...
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
...
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
...
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
...
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
...
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
...
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
....
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
....
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
....
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
....
1523
1524
1525
1526
1527
1528
1529

1530
1531
1532
1533
1534
1535
1536
....
1556
1557
1558
1559
1560
1561
1562
1563
1564











1565
1566
1567
1568
1569
1570
1571
....
1802
1803
1804
1805
1806
1807
1808

1809
1810
1811
1812
1813
1814
    // to remote database engine (e.g. Oracle bound arrays or MS SQL Bulk insert)
    procedure InternalBatchStop; override;
    /// called internally by EngineAdd/EngineUpdate/EngineDelete in batch mode
    function InternalBatchAdd(const aValue: RawUTF8; aID: integer): integer;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
    // - overriden method to handle most potential simple queries, e.g. like
    // $ SELECT Field1,RowID FROM table WHERE RowID=... AND/OR/NOT Field2=
    // - change 'RowID' into 'ID' column name, internal field names into
    // mapped external field names ('AS [InternalFieldName]' if needed), and
    // SQLTableName into fTableName
    // - any 'LIMIT #' clause will be changed into the appropriate SQL statement
    // - handle statements to avoid slow virtual table loop over all rows, like
    // $ SELECT count(*) FROM table
    function AdaptSQLForEngineList(var SQL: RawUTF8): boolean; override;
    /// run INSERT of UPDATE from the corresponding JSON object
    // - Occasion parameter shall be only either soInsert or soUpate
    // - each JSON field will be bound with the proper SQL type corresponding to
................................................................................
    CreateColumns: TSQLDBColumnPropertyDynArray;
begin
  inherited Create(aClass,aServer,aFileName,aBinaryFile);
  // initialize external DB properties
  if fStoredClassProps=nil then
    raise EBusinessLayerException.CreateFmt(
      'StoredClassProps needed for %s',[StoredClassRecordProps.SQLTableName]);
  fTableName := StoredClassProps.ExternalDB.TableName;
  fProperties := StoredClassProps.ExternalDB.ConnectionProperties as TSQLDBConnectionProperties;
  if fProperties=nil then
    raise EBusinessLayerException.CreateFmt(
      'No external DB defined for %s',[StoredClassRecordProps.SQLTableName]);
  if Owner<>nil then
    try
      Owner.ServerTimeStamp := fProperties.ThreadSafeConnection.ServerTimeStamp;
    except
................................................................................
      on E: Exception do ; // ignore any error here
    end;
  // create corresponding external table if necessary, and retrieve its fields info
  fProperties.GetFields(fTableName,fFieldsExternal);
  if fFieldsExternal=nil then begin
    // table is not yet existing -> try to create it
    with aClass.RecordProps do begin
      SetLength(CreateColumns,Fields.Count+1);
      CreateColumns[0].ColumnName := StoredClassProps.ExternalDB.RowIDFieldName;
      CreateColumns[0].ColumnType := ftUnknown;
      CreateColumns[0].ColumnUnique := true;
      f := 1;
      for i := 0 to Fields.Count-1 do
        if PropInfoToExternalField(Fields.List[i],CreateColumns[f]) then
          inc(f);
      if f<>Length(CreateColumns) then
        SetLength(CreateColumns,f); // just ignore non handled field types
    end;
    SQL := fProperties.SQLCreate(fTableName,CreateColumns,false);
    if SQL<>'' then
      if ExecuteDirect(pointer(SQL),[],[],false)<>nil then begin
        fProperties.GetFields(fTableName,fFieldsExternal); // fields from DB after create
        if fFieldsExternal=nil then
          raise EORMException.CreateFmt(
            '%s: external table creation %s failed: GetFields() returned nil - SQL="%s"',
            [fStoredClass.ClassName,fTableName,SQL]);
................................................................................
end;

function TSQLRestServerStaticExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var Prop: ShortString; // to avoid any temporary memory allocation
    P: PUTF8Char;
    W: TTextWriter;

  function PropHandleField(WithAliasIfNeeded: boolean): boolean;
  var int: integer;
  begin
    result := true;
    if IsRowIDShort(Prop) then
    with StoredClassProps.ExternalDB do begin
      W.AddString(RowIDFieldName);
      if WithAliasIfNeeded and not(0 in FieldNamesMatchInternal) then
        W.AddShort(' as ID');
      exit;
    end;
    Prop[ord(Prop[0])+1] := #0; // make ASCIIZ
    int := StoredClassRecordProps.Fields.IndexByName(@Prop[1]);
    if int<0 then
      result := false else
      with StoredClassProps.ExternalDB do begin
        W.AddString(FieldNames[int]);
        if WithAliasIfNeeded and not(int+1 in FieldNamesMatchInternal) then
          W.AddStrings([' as ',StoredClassRecordProps.Fields.List[int].Name]);
      end;
  end;
  procedure GetFieldProp;
  var i,L: integer;
      B: PUTF8Char;
  begin
    Prop[0] := #0;
    if P^=#0 then
................................................................................
    W.AddShort(Prop);
    W.Add(' ');
    GetFieldProp;
  end;
  function NextPropHandleField: boolean;
  begin
    GetFieldProp;
    result := PropHandleField(false);
  end;
  function NextPropHandleInternalTable: boolean;
  begin
    GetFieldProp;
    with StoredClassRecordProps do
    if IdemPropName(Prop,pointer(SQLTableName),length(SQLTableName)) then begin
      W.AddString(fTableName);
      result := true;
    end else
      result := false;
  end;

label Order,Limit;
var Pos: record AfterSelect, WhereClause, Limit, LimitRowCount: integer; end;
    B: PUTF8Char;
    err: integer;
    NewSQL: RawUTF8;
................................................................................
        W.AddShort('count(*)');
        if P^ in [#0,';'] then begin
          result := NextPropHandleInternalTable;
          exit;
        end;
        break; // will process 'select count(*) from tablename where ...'
      end else
      if not PropHandleField(true) then
        exit; // unknown field name
      if P^=',' then begin
        W.Add(',');
        inc(P);
      end else begin
        GetFieldProp;
        if Prop<>'FROM' then
................................................................................
        if Prop<>'' then
          exit; // unexpected clause 
      if not (GotoNextNotSpace(P)^ in [#0,';']) then
        exit; // allow only one column name or one LIMIT ### expression
    end else
    if Prop='WHERE' then
    repeat
      WritePropAndGetFieldProp; // write as 'where' 'and' 'or'
      Pos.WhereClause := W.TextLength+1;
      if Prop='NOT' then
        WritePropAndGetFieldProp; // allow  field1=456 AND NOT field2='Toto'
      if (Prop='') or not PropHandleField(false) then
        exit; // unknown field name or 'LIMIT' / 'ORDER BY' clause
      B := P;
      if P^='=' then
        inc(P) else
      if P^ in ['>','<'] then
        if P[1] in ['=','>'] then
          inc(P,2) else
................................................................................
end;

function TSQLRestServerStaticExternal.EngineList(const SQL: RawUTF8;
  ForceAJAX: Boolean; ReturnedRowCount: PPtrInt): RawUTF8;
var Stmt: ISQLDBStatement;
begin
  if ReturnedRowCount<>nil then
    raise ESQLDBException.CreateFmt('%s.EngineList(ReturnedRowCount<>nil)',[ClassName]);
  Stmt := PrepareInlinedForRows(SQL);
  if Stmt=nil then
    result := '' else
    Stmt.ExecutePreparedAndFetchAllAsJSON(ForceAJAX or (not NoAJAXJSON),result);
end;

function TSQLRestServerStaticExternal.EngineRetrieve(TableModelIndex, ID: integer): RawUTF8;
................................................................................
end;

function TSQLRestServerStaticExternal.TableRowCount(Table: TSQLRecordClass): integer;
var Rows: ISQLDBRows;
begin
  if (self=nil) or (Table<>fStoredClass) then
    result := 0 else begin
    Rows := ExecuteDirect('select count(*) from %',[fTableName],[],true);
    if (Rows=nil) or not Rows.Step then
      result := 0 else
      result := Rows.ColumnInt(0);
  end;
end;

function TSQLRestServerStaticExternal.EngineRetrieveBlob(
................................................................................

function TSQLRestServerStaticExternal.ExecuteInlined(SQLFormat: PUTF8Char;
  const Args: array of const; ExpectResults: Boolean): ISQLDBRows;
begin
  result := ExecuteInlined(FormatUTF8(SQLFormat,Args),ExpectResults);
end;

function TSQLRestServerStaticExternal.PrepareDirectForRows(SQLFormat: PUTF8Char;
  const Args, Params: array of const): ISQLDBStatement;
var Query: ISQLDBStatement;
begin
  result := nil;
  if self=nil then
    exit;
  Query := fProperties.NewThreadSafeStatementPrepared(SQLFormat,Args,true);
  if Query<>nil then
................................................................................
  const SentData: RawUTF8; Occasion: TSQLOccasion; UpdatedID: integer): integer;
var Decoder: TJSONObjectDecoder;
    SQL: RawUTF8;
    Types: TSQLDBFieldTypeArray;
    ExternalFields: TRawUTF8DynArray;
    InsertedID, F: integer;
    Query: ISQLDBStatement;

begin
  result := 0;
  Lock(false); // avoid race condition against max(ID)
  try
    case Occasion of
    soInsert: begin
      InsertedID := JSONRetrieveIDField(pointer(SentData));
................................................................................
    // execute statement
    Query := fProperties.NewThreadSafeStatementPrepared(SQL,false);
    if Query=nil then
      exit;
    try
      for F := 0 to Decoder.FieldCount-1 do
      if F in Decoder.FieldNull then
        Query.BindNull(F+1) else
        Query.Bind(F+1,Types[F],Decoder.FieldValues[F],true);











      Query.ExecutePrepared;
    except
      exit; // leave result=0
    end;
    // mark success
    if UpdatedID=0 then
      result := InsertedID else
................................................................................
  if (self<>nil) and (Static<>nil) and
     (oldRowID=newRowID) and (newRowID>0) then // don't allow ID change
    with Static as TSQLRestServerStaticExternal, StoredClassProps.ExternalDB do
      result := ExecuteDirectSQLVar('update % set % where %=?',
        [fTableName,SQL.UpdateSetAll,RowIDFieldName],Values,oldRowID,true) else
    result := false;
end;



initialization
  // all our SynDB related functions shall log to main TSQLLog
  SynDBLog := TSQLLog;
end.

Changes to SynDB.pas.

701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
...
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
....
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
....
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
....
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
....
3684
3685
3686
3687
3688
3689
3690

3691
3692
3693
3694

3695
3696
3697
3698
3699
3700
3701
....
4294
4295
4296
4297
4298
4299
4300
4301
4302
4303

4304
4305
4306
4307
4308
4309









4310
4311
4312
4313
4314
4315


4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
....
4451
4452
4453
4454
4455
4456
4457
4458
4459
4460
4461
4462
4463
4464
4465
....
4605
4606
4607
4608
4609
4610
4611

4612
4613




4614
4615
4616
4617
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628
4629
....
5012
5013
5014
5015
5016
5017
5018
5019
5020
5021

5022
5023
5024
5025
5026
5027
5028
5029
5030


5031
5032
5033
5034
5035
5036
5037


5038
5039
5040
5041
5042
5043
5044
5045
    procedure Bind(Param: Integer; const Data: TSQLVar;
      IO: TSQLDBParamInOutType=paramIn); overload;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      IO: TSQLDBParamInOutType=paramIn); overload;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call }
    procedure Bind(const Params: array of const;
................................................................................
    // - this default implementation just returns nothing
    function SQLGetTableNames: RawUTF8; virtual;
    /// should initialize fForeignKeys content with all foreign keys of this
    // database
    // - used by GetForeignKey method
    procedure GetForeignKeys; virtual; abstract;
    /// will use fSQLCreateField[Max] to create the SQL column definition
    // - this default virtual implementation will handle properly
    // SQLite3/MSSQL/Oracle/Jet syntax (but not MySQL due to UNIQUE Constraint)
    function SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8; virtual;
    /// wrapper around GetIndexes() + set Fields[].ColumnIndexed in consequence
    // - used by some overriden versions of GetFields() method
    procedure GetIndexesAndSetFieldsColumnIndexed(const aTableName: RawUTF8;
      var Fields: TSQLDBColumnDefineDynArray);
    /// check if the exception or its error message is about DB connection error
    // - will be used by TSQLDBConnection.LastErrorWasAboutConnection method
................................................................................
    /// convert a textual column data type, as retrieved e.g. from SQLGetField,
    // into our internal primitive types
    // - default implementation will always return ftUTF8
    function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; virtual;
    /// returns the SQL statement used to create a Table
    // - should return the SQL "CREATE" statement needed to create a table with
    // the specified field/column names and types
    // - a "ID Int64 PRIMARY KEY" column is always added at first position,
    // and will expect the ORM to create an unique RowID value sent at INSERT
    // (could use "select max(ID) from table" to retrieve the last value) -
    // note that 'ID' is used instead of 'RowID' since it fails on Oracle e.g.
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
    function SQLCreate(const aTableName: RawUTF8;
      const aFields: TSQLDBColumnPropertyDynArray): RawUTF8; virtual;
    /// returns the SQL statement used to add a column to a Table
    // - should return the SQL "ALTER TABLE" statement needed to add a column to
    // an existing table
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
................................................................................
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null) - e.g. as
    // computed by TJSONObjectDecoder.Decode()
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call
     - this default implementation will call corresponding Bind*() method }
................................................................................
      while Rows.Step do begin
        // init when first row of data is available
        if Ins=nil then begin
          SQL := Rows.ColumnsToSQLInsert(aTableName,Fields);
          Properties.GetTableNames(Tables);
          if FindRawUTF8(Tables,TableName,false)<0 then
            with Properties do
              ExecuteNoResult(SQLCreate(aTableName,Fields),[]);
          Ins := NewStatement;
          Ins.Prepare(SQL,false);
        end;
        // write row data
        Ins.BindFromRows(Fields,Rows);
        Ins.ExecutePrepared;
        Ins.Reset;
................................................................................
  if byte(fBatchSendingAbilities)=0 then // if not already handled by driver
    case aDBMS of
    dSQlite,dMySQL,dPostgreSQL,dNexusDB,dMSSQL,dDB2, // INSERT with multi VALUES
    //dFirebird,  EXECUTE BLOCK with params is slower (at least for embedded)
    dOracle: begin // Oracle expects weird INSERT ALL INTO ... statement
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsert;

    end;
    dFirebird: begin // will run EXECUTE BLOCK without parameters
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsertFirebird;

    end;
    end;
end;

destructor TSQLDBConnectionProperties.Destroy;
begin
  fMainConnection.Free;
................................................................................
  dNexusDB: result := 'DATE '+Iso8601;
  dDB2: result := 'TIMESTAMP '''+TrimTInIso+'''';
  else  result := ''''+Iso8601+'''';
  end;
end;

function TSQLDBConnectionProperties.SQLCreate(const aTableName: RawUTF8;
  const aFields: TSQLDBColumnPropertyDynArray): RawUTF8;
var i: integer;
    F: RawUTF8;

const EXE_FMT: PUTF8Char = 'CREATE TABLE % (ID % PRIMARY KEY, %)'; // Delphi 5
begin // use 'ID' instead of 'RowID' here since some DB (e.g. Oracle) use it
  result := '';
  if high(aFields)<0 then
    exit; // nothing to create
  for i := 0 to high(aFields) do begin









    F := SQLFieldCreate(aFields[i]);
    if i<>high(aFields) then
      F := F+',';
    result := result+F;
  end;
  if IsRowID(pointer(aFields[0].ColumnName)) then


    result := 'CREATE TABLE '+aTableName+' ('+result+')' else
    // fSQLCreateField[ftUnknown] is the datatype for ID field
    result := FormatUTF8(EXE_FMT,[aTableName,fSQLCreateField[ftUnknown],result]);
  case DBMS of
  dDB2: result := result+' CCSID Unicode';
  end;
end;

function TSQLDBConnectionProperties.SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8;
begin
  if (aField.ColumnType=ftUTF8) and (aField.ColumnAttr-1<fSQLCreateFieldMax) then
    result := FormatUTF8(pointer(fSQLCreateField[ftNull]),[aField.ColumnAttr]) else
    if IsRowID(pointer(aField.ColumnName)) then // ID -> ftUnknown (=INTEGER)
      result := fSQLCreateField[ftUnknown] else
      result := fSQLCreateField[aField.ColumnType];
  if aField.ColumnNonNullable or aField.ColumnUnique then
    result := result+' NOT NULL';
  if aField.ColumnUnique then
    result := result+' UNIQUE'; // see http://www.w3schools.com/sql/sql_unique.asp 
  result := aField.ColumnName+result;
end;

................................................................................
    maxf: integer;
procedure ComputeSQL(rowcount,offset: integer);
var f,r,p,len: integer;
begin
  if (fDBMS<>dFireBird) and (rowcount=prevrowcount) then
    exit;
  prevrowcount := rowcount;
  with TTextWriter.CreateOwnedStream(4096) do
  try
    case Props.DBMS of
    dFirebird: begin
      AddShort('execute block('#10);
      p := 0;
      for r := offset to offset+rowcount-1 do begin
        for f := 0 to maxf do begin
................................................................................
    else begin
      ComputeSQL(RowCount-currentRow,currentRow);
      SQLCached := false; // truncate number of parameters should not be unique
    end;
    if SQLCached then
      Query := Props.NewThreadSafeStatementPrepared(SQL,false) else begin
      Stmt := Props.NewThreadSafeStatement;

      Stmt.Prepare(SQL,false);
      Query := Stmt;




    end;
    if Query=nil then
      raise ESQLDBException.CreateFmt('%s.MultipleValuesInsert() Prepare(%s)',
        [ClassName,SQL]);
    try
      p := 1;
      for i := 1 to prevrowcount do begin
        for f := 0 to maxf do begin
          Query.Bind(p,FieldTypes[f],FieldValues[f,currentRow]);
          inc(p);
        end;
        inc(currentRow);
      end;
      Query.ExecutePrepared;
    finally
      Query := nil;
................................................................................
    ftBlob:     BindBlob(Param,VBlob,VBlobLen,IO);
    else raise ESQLDBException.CreateFmt(
      '%s.Bind(Param=%d,VType=%d)',[fStatementClassName,Param,ord(VType)]);
  end;
end;

procedure TSQLDBStatement.Bind(Param: Integer; ParamType: TSQLDBFieldType;
  const Value: RawUTF8; IO: TSQLDBParamInOutType=paramIn);
var tmp: RawUTF8;
begin

  if Value='null' then // bind null (ftUTF8 should be '"null"')
    BindNull(Param,IO) else
    case ParamType of
      ftNull:     BindNull(Param,IO);
      ftInt64:    Bind(Param,GetInt64(pointer(Value)),IO);
      ftDouble:   Bind(Param,GetExtended(pointer(Value)),IO);
      ftCurrency: BindCurrency(Param,StrToCurrency(pointer(Value)),IO);
      ftBlob:     BindBlob(Param,Value,IO); // already decoded
      ftDate: begin


        UnQuoteSQLString(pointer(Value),tmp);
        BindDateTime(Param,Iso8601ToDateTime(tmp),IO);
      end;
      ftUTF8:
        if ((Value='') or (Value=#39#39)) and
            fConnection.fProperties.StoreVoidStringAsNull then
          BindNull(Param,IO) else begin


          UnQuoteSQLString(pointer(Value),tmp);
          BindTextU(Param,tmp,IO);
        end;
      else raise ESQLDBException.CreateFmt('Invalid %s.Bind(%d,TSQLDBFieldType(%d),%s)',
        [fStatementClassName,Param,ord(ParamType),Value]);
    end;
end;








|







 







|
|







 







|








|







 







|







 







|







 







>




>







 







|


>






>
>
>
>
>
>
>
>
>
|




|
>
>












<
<
|







 







|







 







>
|
|
>
>
>
>








|







 







|


>
|








>
>
|






>
>
|







701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
...
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
....
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
....
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
....
3623
3624
3625
3626
3627
3628
3629
3630
3631
3632
3633
3634
3635
3636
3637
....
3684
3685
3686
3687
3688
3689
3690
3691
3692
3693
3694
3695
3696
3697
3698
3699
3700
3701
3702
3703
....
4296
4297
4298
4299
4300
4301
4302
4303
4304
4305
4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
4323
4324
4325
4326
4327
4328
4329
4330
4331
4332
4333
4334
4335
4336
4337
4338
4339
4340
4341


4342
4343
4344
4345
4346
4347
4348
4349
....
4463
4464
4465
4466
4467
4468
4469
4470
4471
4472
4473
4474
4475
4476
4477
....
4617
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628
4629
4630
4631
4632
4633
4634
4635
4636
4637
4638
4639
4640
4641
4642
4643
4644
4645
4646
....
5029
5030
5031
5032
5033
5034
5035
5036
5037
5038
5039
5040
5041
5042
5043
5044
5045
5046
5047
5048
5049
5050
5051
5052
5053
5054
5055
5056
5057
5058
5059
5060
5061
5062
5063
5064
5065
5066
5067
    procedure Bind(Param: Integer; const Data: TSQLVar;
      IO: TSQLDBParamInOutType=paramIn); overload;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null)
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call }
    procedure Bind(const Params: array of const;
................................................................................
    // - this default implementation just returns nothing
    function SQLGetTableNames: RawUTF8; virtual;
    /// should initialize fForeignKeys content with all foreign keys of this
    // database
    // - used by GetForeignKey method
    procedure GetForeignKeys; virtual; abstract;
    /// will use fSQLCreateField[Max] to create the SQL column definition
    // - this default virtual implementation will handle properly all supported
    // database engines, assuming aField.ColumnType=ftUnknown for ID
    function SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8; virtual;
    /// wrapper around GetIndexes() + set Fields[].ColumnIndexed in consequence
    // - used by some overriden versions of GetFields() method
    procedure GetIndexesAndSetFieldsColumnIndexed(const aTableName: RawUTF8;
      var Fields: TSQLDBColumnDefineDynArray);
    /// check if the exception or its error message is about DB connection error
    // - will be used by TSQLDBConnection.LastErrorWasAboutConnection method
................................................................................
    /// convert a textual column data type, as retrieved e.g. from SQLGetField,
    // into our internal primitive types
    // - default implementation will always return ftUTF8
    function ColumnTypeNativeToDB(const aNativeType: RawUTF8; aScale: integer): TSQLDBFieldType; virtual;
    /// returns the SQL statement used to create a Table
    // - should return the SQL "CREATE" statement needed to create a table with
    // the specified field/column names and types
    // - if aAddID is TRUE, "ID Int64 PRIMARY KEY" column is added as first,
    // and will expect the ORM to create an unique RowID value sent at INSERT
    // (could use "select max(ID) from table" to retrieve the last value) -
    // note that 'ID' is used instead of 'RowID' since it fails on Oracle e.g.
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
    function SQLCreate(const aTableName: RawUTF8;
      const aFields: TSQLDBColumnPropertyDynArray; aAddID: boolean): RawUTF8; virtual;
    /// returns the SQL statement used to add a column to a Table
    // - should return the SQL "ALTER TABLE" statement needed to add a column to
    // an existing table
    // - this default implementation will use internal fSQLCreateField and
    // fSQLCreateFieldMax protected values, which contains by default the
    // ANSI SQL Data Types and maximum 1000 inlined WideChars: inherited classes
    // may change the default fSQLCreateField* content or override this method
................................................................................
      IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    /// bind one RawUTF8 encoded value
    // - the leftmost SQL parameter has an index of 1
    // - the value should match the BindArray() format, i.e. be stored as in SQL
    // (i.e. number, 'quoted string', 'YYYY-MM-DD hh:mm:ss', null) - e.g. as
    // computed by TJSONObjectDecoder.Decode()
    procedure Bind(Param: Integer; ParamType: TSQLDBFieldType; const Value: RawUTF8;
      ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn); overload; virtual;
    {/ bind an array of const values
     - parameters marked as ? should be specified as method parameter in Params[]
     - BLOB parameters can be bound with this method, when set after encoding
       via BinToBase64WithMagic() call
     - TDateTime parameters can be bound with this method, when encoded via
       a DateToSQL() or DateTimeToSQL() call
     - this default implementation will call corresponding Bind*() method }
................................................................................
      while Rows.Step do begin
        // init when first row of data is available
        if Ins=nil then begin
          SQL := Rows.ColumnsToSQLInsert(aTableName,Fields);
          Properties.GetTableNames(Tables);
          if FindRawUTF8(Tables,TableName,false)<0 then
            with Properties do
              ExecuteNoResult(SQLCreate(aTableName,Fields,false),[]);
          Ins := NewStatement;
          Ins.Prepare(SQL,false);
        end;
        // write row data
        Ins.BindFromRows(Fields,Rows);
        Ins.ExecutePrepared;
        Ins.Reset;
................................................................................
  if byte(fBatchSendingAbilities)=0 then // if not already handled by driver
    case aDBMS of
    dSQlite,dMySQL,dPostgreSQL,dNexusDB,dMSSQL,dDB2, // INSERT with multi VALUES
    //dFirebird,  EXECUTE BLOCK with params is slower (at least for embedded)
    dOracle: begin // Oracle expects weird INSERT ALL INTO ... statement
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsert;
      fBatchMaxSentAtOnce := 4096; // MultipleValuesInsert will do chunking
    end;
    dFirebird: begin // will run EXECUTE BLOCK without parameters
      fBatchSendingAbilities := [cCreate];
      fOnBatchInsert := MultipleValuesInsertFirebird;
      fBatchMaxSentAtOnce := 4096; // MultipleValuesInsert will do chunking
    end;
    end;
end;

destructor TSQLDBConnectionProperties.Destroy;
begin
  fMainConnection.Free;
................................................................................
  dNexusDB: result := 'DATE '+Iso8601;
  dDB2: result := 'TIMESTAMP '''+TrimTInIso+'''';
  else  result := ''''+Iso8601+'''';
  end;
end;

function TSQLDBConnectionProperties.SQLCreate(const aTableName: RawUTF8;
  const aFields: TSQLDBColumnPropertyDynArray; aAddID: boolean): RawUTF8;
var i: integer;
    F: RawUTF8;
    AddPrimaryKey: RawUTF8;
const EXE_FMT: PUTF8Char = 'CREATE TABLE % (ID % PRIMARY KEY, %)'; // Delphi 5
begin // use 'ID' instead of 'RowID' here since some DB (e.g. Oracle) use it
  result := '';
  if high(aFields)<0 then
    exit; // nothing to create
  for i := 0 to high(aFields) do begin
    if (not aAddID) and (aFields[i].ColumnType=ftUnknown) then begin
      F := aFields[i].ColumnName+' '+fSQLCreateField[aFields[i].ColumnType]+' NOT NULL';
      case DBMS of
      dSQLite, dMSSQL, dOracle, dJet, dPostgreSQL, dFirebird, dNexusDB:
        F := F+' PRIMARY KEY';
      dDB2, dMySQL:
        AddPrimaryKey := aFields[i].ColumnName;
      end;
    end else
      F := SQLFieldCreate(aFields[i]);
    if i<>high(aFields) then
      F := F+',';
    result := result+F;
  end;
  if AddPrimaryKey<>'' then
    result := result+', PRIMARY KEY('+AddPrimaryKey+')';
  if not aAddID then
    result := 'CREATE TABLE '+aTableName+' ('+result+')' else
    // fSQLCreateField[ftUnknown] is the datatype for ID field
    result := FormatUTF8(EXE_FMT,[aTableName,fSQLCreateField[ftUnknown],result]);
  case DBMS of
  dDB2: result := result+' CCSID Unicode';
  end;
end;

function TSQLDBConnectionProperties.SQLFieldCreate(const aField: TSQLDBColumnProperty): RawUTF8;
begin
  if (aField.ColumnType=ftUTF8) and (aField.ColumnAttr-1<fSQLCreateFieldMax) then
    result := FormatUTF8(pointer(fSQLCreateField[ftNull]),[aField.ColumnAttr]) else


    result := fSQLCreateField[aField.ColumnType];
  if aField.ColumnNonNullable or aField.ColumnUnique then
    result := result+' NOT NULL';
  if aField.ColumnUnique then
    result := result+' UNIQUE'; // see http://www.w3schools.com/sql/sql_unique.asp 
  result := aField.ColumnName+result;
end;

................................................................................
    maxf: integer;
procedure ComputeSQL(rowcount,offset: integer);
var f,r,p,len: integer;
begin
  if (fDBMS<>dFireBird) and (rowcount=prevrowcount) then
    exit;
  prevrowcount := rowcount;
  with TTextWriter.CreateOwnedStream(8192) do
  try
    case Props.DBMS of
    dFirebird: begin
      AddShort('execute block('#10);
      p := 0;
      for r := offset to offset+rowcount-1 do begin
        for f := 0 to maxf do begin
................................................................................
    else begin
      ComputeSQL(RowCount-currentRow,currentRow);
      SQLCached := false; // truncate number of parameters should not be unique
    end;
    if SQLCached then
      Query := Props.NewThreadSafeStatementPrepared(SQL,false) else begin
      Stmt := Props.NewThreadSafeStatement;
      try
        Stmt.Prepare(SQL,false);
        Query := Stmt;
      except
        on Exception do
          Stmt.Free; // avoid memory leak in case of invalid SQL statement
      end; // exception leaves Query=nil to raise exception
    end;
    if Query=nil then
      raise ESQLDBException.CreateFmt('%s.MultipleValuesInsert() Prepare(%s)',
        [ClassName,SQL]);
    try
      p := 1;
      for i := 1 to prevrowcount do begin
        for f := 0 to maxf do begin
          Query.Bind(p,FieldTypes[f],FieldValues[f,currentRow],false);
          inc(p);
        end;
        inc(currentRow);
      end;
      Query.ExecutePrepared;
    finally
      Query := nil;
................................................................................
    ftBlob:     BindBlob(Param,VBlob,VBlobLen,IO);
    else raise ESQLDBException.CreateFmt(
      '%s.Bind(Param=%d,VType=%d)',[fStatementClassName,Param,ord(VType)]);
  end;
end;

procedure TSQLDBStatement.Bind(Param: Integer; ParamType: TSQLDBFieldType;
  const Value: RawUTF8; ValueAlreadyUnquoted: boolean; IO: TSQLDBParamInOutType=paramIn);
var tmp: RawUTF8;
begin
  if (not ValueAlreadyUnquoted) and (Value='null') then
    // bind null (ftUTF8 should be '"null"')
    BindNull(Param,IO) else
    case ParamType of
      ftNull:     BindNull(Param,IO);
      ftInt64:    Bind(Param,GetInt64(pointer(Value)),IO);
      ftDouble:   Bind(Param,GetExtended(pointer(Value)),IO);
      ftCurrency: BindCurrency(Param,StrToCurrency(pointer(Value)),IO);
      ftBlob:     BindBlob(Param,Value,IO); // already decoded
      ftDate: begin
        if ValueAlreadyUnquoted then
          tmp := Value else
          UnQuoteSQLString(pointer(Value),tmp);
        BindDateTime(Param,Iso8601ToDateTime(tmp),IO);
      end;
      ftUTF8:
        if ((Value='') or (Value=#39#39)) and
            fConnection.fProperties.StoreVoidStringAsNull then
          BindNull(Param,IO) else begin
          if ValueAlreadyUnquoted then
            tmp := Value else
            UnQuoteSQLString(pointer(Value),tmp);
          BindTextU(Param,tmp,IO);
        end;
      else raise ESQLDBException.CreateFmt('Invalid %s.Bind(%d,TSQLDBFieldType(%d),%s)',
        [fStatementClassName,Param,ord(ParamType),Value]);
    end;
end;

Changes to SynSelfTests.pas.

7272
7273
7274
7275
7276
7277
7278
7279
7280
7281
7282
7283
7284
7285
7286
7287
7288
7289
7290
7291
7292
7293



7294
7295
7296
7297
7298
7299
7300
....
7313
7314
7315
7316
7317
7318
7319
7320
7321
7322
7323
7324
7325
7326
7327
....
7365
7366
7367
7368
7369
7370
7371
7372
7373
7374
7375
7376
7377
7378
7379





7380
7381
7382
7383
7384
7385
7386
7387
7388
7389
7390
7391
7392



7393
7394





7395


7396

7397
7398
7399
7400
7401
7402
7403
7404
7405
7406
7407
7408
7409
7410
7411


procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
    Tables: TRawUTF8DynArray;
    aID, i, n: integer;
    ok: Boolean;
    BatchID: TIntegerDynArray;
    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(SQLITE_MEMORY_DATABASE_NAME,'','','');
  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
................................................................................
      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 backup testing 
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
................................................................................
            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);







|

|












>
>
>







 







<







 







|







>
>
>
>
>
|
|
|
|
|
|
|
|
|
|
|
|
|
>
>
>
|

>
>
>
>
>

>
>

>







|







7272
7273
7274
7275
7276
7277
7278
7279
7280
7281
7282
7283
7284
7285
7286
7287
7288
7289
7290
7291
7292
7293
7294
7295
7296
7297
7298
7299
7300
7301
7302
7303
....
7316
7317
7318
7319
7320
7321
7322

7323
7324
7325
7326
7327
7328
7329
....
7367
7368
7369
7370
7371
7372
7373
7374
7375
7376
7377
7378
7379
7380
7381
7382
7383
7384
7385
7386
7387
7388
7389
7390
7391
7392
7393
7394
7395
7396
7397
7398
7399
7400
7401
7402
7403
7404
7405
7406
7407
7408
7409
7410
7411
7412
7413
7414
7415
7416
7417
7418
7419
7420
7421
7422
7423
7424
7425
7426
7427
7428
7429


procedure TTestExternalDatabase.Test(StaticVirtualTableDirect: boolean);
var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
    RBlob: TSQLRecordOnlyBlob;
    Tables: TRawUTF8DynArray;
    i,n, aID: integer;
    ok: Boolean;
    BatchID,BatchIDUpdate: TIntegerDynArray;
    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(SQLITE_MEMORY_DATABASE_NAME,'','','');
  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'));
  fExternalModel.Props[TSQLRecordPeopleExt].ExternalDB.
    MapField('ID','Key').
    MapField('YearOfDeath','YOD');
  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
................................................................................
      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;

        while RInt.FillOne do begin
          if RInt.fID<100 then // some real entries for backup testing 
            aExternalClient.Add(RInt,true,true);
          RExt.Data := RInt.Data;
          RExt.FirstName := RInt.FirstName;
          RExt.LastName := RInt.LastName;
          RExt.YearOfBirth := RInt.YearOfBirth;
................................................................................
            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 BatchID[high(BatchID)] 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;
            if RInt.fID>4000 then begin
              if aExternalClient.BatchCount=0 then
                aExternalClient.BatchStart(TSQLRecordPeopleExt);
              Check(aExternalClient.BatchUpdate(RExt)>=0,'BatchUpdate 1/100 rows');
            end else begin
              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;
          end;
        Check(aExternalClient.BatchSend(BatchIDUpdate)=HTML_SUCCESS);
        Check(length(BatchIDUpdate)=110);
        for i := 1 to BatchID[high(BatchID)] do
          if i and 127=0 then
          if i>4000 then begin
            if aExternalClient.BatchCount=0 then
              aExternalClient.BatchStart(TSQLRecordPeopleExt);
            Check(aExternalClient.BatchDelete(i)>=0,'BatchDelete 1/128 rows');
          end else
            Check(aExternalClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');
        Check(aExternalClient.BatchSend(BatchIDUpdate)=HTML_SUCCESS);
        Check(length(BatchIDUpdate)=55);
        n := aExternalClient.TableRowCount(TSQLRecordPeople);
        Check(aExternalClient.Server.TableRowCount(TSQLRecordPeopleExt)=10925);
        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 BatchID[high(BatchID)] 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);