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

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

Overview
Comment:fixed TSQLRestServerStaticInMemory.AdaptSQLForEngineList() to handle most common RESTful requests from client
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 8857333fde0820f7b9b24a76ddb4907490e57144
User & Date: abouchez 2013-05-21 08:59:06
Context
2013-05-21
12:22
updated documentation about using static in-memory Virtual tables from the client side check-in: e7517d9658 user: abouchez tags: trunk
08:59
fixed TSQLRestServerStaticInMemory.AdaptSQLForEngineList() to handle most common RESTful requests from client check-in: 8857333fde user: abouchez tags: trunk
2013-05-20
18:07
updated documentation, especially about useHttpApiRegisteringURI check-in: f782f5d5e8 user: ab tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to SQLite3/Samples/15 - External DB performance/PerfMain.pas.

119
120
121
122
123
124
125

126

127
128
129
130
131
132
133
  OraUser.Text := UTF8ToString(FindIniEntry(Ini,'Oracle','User'));
  OraPass.Text := UTF8ToString(FindIniEntry(Ini,'Oracle','Password'));
  Stats := TObjectList.Create;
end;

procedure TMainForm.BtnRunTestsClick(Sender: TObject);
var T,U,P: RawUTF8;

    Suffix: TFileName;

begin
  {$ifdef CPU64}
  Suffix := '64';
  {$endif}
  ExeVersionRetrieve;
  //SynDBLog.Family.Level := LOG_VERBOSE;  // for debugging
  T := StringToUTF8(OraTNSName.Text);






>

>







119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
  OraUser.Text := UTF8ToString(FindIniEntry(Ini,'Oracle','User'));
  OraPass.Text := UTF8ToString(FindIniEntry(Ini,'Oracle','Password'));
  Stats := TObjectList.Create;
end;

procedure TMainForm.BtnRunTestsClick(Sender: TObject);
var T,U,P: RawUTF8;
    {$ifdef CPU64}
    Suffix: TFileName;
    {$endif CPU64}
begin
  {$ifdef CPU64}
  Suffix := '64';
  {$endif}
  ExeVersionRetrieve;
  //SynDBLog.Family.Level := LOG_VERBOSE;  // for debugging
  T := StringToUTF8(OraTNSName.Text);

Changes to SQLite3/mORMot.pas.

778
779
780
781
782
783
784
785

786
787
788
789
790
791
792
....
9121
9122
9123
9124
9125
9126
9127
9128
9129
9130
9131
9132
9133
9134
9135
9136
9137
.....
24818
24819
24820
24821
24822
24823
24824
24825
24826
24827
24828
24829
24830


24831
24832
24833
24834
24835
24836
24837
.....
24968
24969
24970
24971
24972
24973
24974

24975
24976
24977
24978
24979


24980
24981
24982
24983

24984



24985
24986
24987




24988



24989
24990
24991
24992
24993
24994
24995
.....
25199
25200
25201
25202
25203
25204
25205
25206

25207
25208
25209
25210
25211
25212
25213
.....
25888
25889
25890
25891
25892
25893
25894
25895

25896
25897
25898
25899
25900
25901
25902
    - new protected TSQLRestServer.InternalAdaptSQL method, extracted from URI()
      process to also be called by TSQLRestServer.InternalListJSON() for proper
      TSQLRestServerStatic.AdaptSQLForEngineList(SQL) call
    - new protected TSQLRestServer.InternalUpdateEvent virtual method, to allow
      a server-wide update notification, not coupled to OnUpdateEvent callback -
      see feature request [5688e97251]
    - TSQLRestServerStaticInMemory.AdaptSQLForEngineList() will now handle
      'select count(*') from TableName' statements directly

    - changed TSQLAccessRights and TSQLAuthGroup.SQLAccessRights CSV format
      to use 'first-last,' pattern to regroup set bits (reduce storage size)
    - added overloaded TSQLAccessRights.Edit() method using TSQLOccasions set
    - added reOneSessionPerUser kind of remote execution in TSQLAccessRight
    - introducing TSQLRestClientURI.InternalCheckOpen/InternalClose methods to
      properly handle remote connection and re-connection
    - added TSQLRestClientURI.LastErrorCode/LastErrorMessage/LastErrorException
................................................................................
    /// true if IDs are sorted (which is the default behavior of this class),
    // for fastest ID2Index() by using a binary search algorithm
    fIDSorted: boolean;
    fCommitShouldNotUpdateFile: boolean;
    fBinaryFile: boolean;
    fExpandedJSON: boolean;
    fSearchRec: TSQLRecord;
    fBasicUpperSQLWhere: RawUTF8;
    fBasicSQLCount: RawUTF8;
    fBasicSQLHasRows: RawUTF8;
    fUniqueFields: TObjectList;
    function UniqueFieldsUpdateOK(aRec: TSQLRecord; aUpdateIndex: integer): boolean;
    function UniqueFieldHash(aFieldIndex: integer): TListFieldHash;
    function GetCount: integer;
    function GetItem(Index: integer): TSQLRecord;
    function GetID(Index: integer): integer;
    // optimized search of WhereValue in WhereField (0=RowID,1..=RTTI)
................................................................................
      [fStoredClassRecordProps.SQLTableName,aClass.ClassName]);
  fBinaryFile := aBinaryFile;
  fValue := TObjectList.Create;
  fSearchRec := fStoredClass.Create;
  fIDSorted := true; // sorted by design of this class (may change in children)
  if (ClassType<>TSQLRestServerStaticInMemory) and (fStoredClassProps<>nil) then
    with fStoredClassProps do begin // used by AdaptSQLForEngineList() method
      fBasicUpperSQLWhere := UpperCase(SQL.SelectAll[false]);
      fBasicUpperSQLWhere[length(fBasicUpperSQLWhere)] := ' '; // ';' -> ' '
      fBasicUpperSQLWhere := fBasicUpperSQLWhere+'WHERE ';
    end;
  fBasicSQLCount := 'SELECT COUNT(*) FROM '+fStoredClassRecordProps.SQLTableName;
  fBasicSQLHasRows := 'SELECT RowID FROM '+fStoredClassRecordProps.SQLTableName+' LIMIT 1';


  if not IsZero(fIsUnique) then begin
    fUniqueFields := TObjectList.Create;
    with fStoredClassRecordProps do
    for F := 0 to Fields.Count-1 do
      if F in fIsUnique then
        // CaseInsensitive=true just like
        fUniqueFields.Add(TListFieldHash.Create(fValue,F,Fields.List[F],true));
................................................................................
procedure TSQLRestServerStaticInMemory.DoNothingEvent(aDest: pointer; aRec: TSQLRecord; aIndex: integer);
begin
end;

function TSQLRestServerStaticInMemory.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var P: PUTF8Char;
    Prop: RawUTF8;

begin
  result := inherited AdaptSQLForEngineList(SQL);
  if result then
    exit; // 'select * from table'
  if IdemPropNameU(fBasicSQLCount,SQL) or IdemPropNameU(fBasicSQLHasRows,SQL) then begin


    result := true;
    exit; // 'select count(*) from table' will be handled as static
  end;
  if (fBasicUpperSQLWhere='') or

     not IdemPChar(pointer(SQL),pointer(fBasicUpperSQLWhere)) then



    exit; 
  P := pointer(SQL);
  inc(P,length(fBasicUpperSQLWhere));




  P := GotoNextNotSpace(P);



  Prop := GetNextItem(P,'=');
  if (P=nil) or (fStoredClassRecordProps.Fields.IndexByName(Prop)<0) then
    exit;
  if PWord(P)^=ord(':')+ord('(') shl 8 then
    inc(P,2); // +2 to ignore :(...): parameter
  if P^ in ['''','"'] then
    P := GotoEndOfQuotedString(P) else
................................................................................
    result := '';
    exit;
  end;
  Lock(false);
  try
    if IdemPropNameU(fBasicSQLCount,SQL) then
      SetCount(fValue.Count) else
    if IdemPropNameU(fBasicSQLHasRows,SQL) then

      if fValue.Count=0 then begin
        result := '{"fieldCount":1,"values":["RowID"]}'#$A;
        ResCount := 0;
      end else begin
        result := '{"fieldCount":1,"values":["RowID",'+
          Int32ToUTF8(TSQLRecord(fValue.List[0]).fID)+'"]}'#$A;
        ResCount := 1;
................................................................................

function TSQLRestServerStatic.AdaptSQLForEngineList(var SQL: RawUTF8): boolean; 
begin
  if fStoredClassProps=nil then
    result := false else begin
    result := fStoredClassProps.SQL.SelectAll[false]=SQL;
    if result then
      SQL := fStoredClassProps.SQL.SelectAll[true];

  end;
end;


{ TSQLRestServerFullMemory }

constructor TSQLRestServerFullMemory.Create(aModel: TSQLModel;






|
>







 







|

|







 







|
|
|


|
>
>







 







>




|
>
>



|
>
|
>
>
>
|

|
>
>
>
>

>
>
>







 







|
>







 







|
>







778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
....
9122
9123
9124
9125
9126
9127
9128
9129
9130
9131
9132
9133
9134
9135
9136
9137
9138
.....
24819
24820
24821
24822
24823
24824
24825
24826
24827
24828
24829
24830
24831
24832
24833
24834
24835
24836
24837
24838
24839
24840
.....
24971
24972
24973
24974
24975
24976
24977
24978
24979
24980
24981
24982
24983
24984
24985
24986
24987
24988
24989
24990
24991
24992
24993
24994
24995
24996
24997
24998
24999
25000
25001
25002
25003
25004
25005
25006
25007
25008
25009
25010
25011
25012
.....
25216
25217
25218
25219
25220
25221
25222
25223
25224
25225
25226
25227
25228
25229
25230
25231
.....
25906
25907
25908
25909
25910
25911
25912
25913
25914
25915
25916
25917
25918
25919
25920
25921
    - new protected TSQLRestServer.InternalAdaptSQL method, extracted from URI()
      process to also be called by TSQLRestServer.InternalListJSON() for proper
      TSQLRestServerStatic.AdaptSQLForEngineList(SQL) call
    - new protected TSQLRestServer.InternalUpdateEvent virtual method, to allow
      a server-wide update notification, not coupled to OnUpdateEvent callback -
      see feature request [5688e97251]
    - TSQLRestServerStaticInMemory.AdaptSQLForEngineList() will now handle
      'select count(*') from TableName' statements directly, and any RESTful
      requests from client
    - changed TSQLAccessRights and TSQLAuthGroup.SQLAccessRights CSV format
      to use 'first-last,' pattern to regroup set bits (reduce storage size)
    - added overloaded TSQLAccessRights.Edit() method using TSQLOccasions set
    - added reOneSessionPerUser kind of remote execution in TSQLAccessRight
    - introducing TSQLRestClientURI.InternalCheckOpen/InternalClose methods to
      properly handle remote connection and re-connection
    - added TSQLRestClientURI.LastErrorCode/LastErrorMessage/LastErrorException
................................................................................
    /// true if IDs are sorted (which is the default behavior of this class),
    // for fastest ID2Index() by using a binary search algorithm
    fIDSorted: boolean;
    fCommitShouldNotUpdateFile: boolean;
    fBinaryFile: boolean;
    fExpandedJSON: boolean;
    fSearchRec: TSQLRecord;
    fBasicUpperSQLSelect: array[boolean] of RawUTF8;
    fBasicSQLCount: RawUTF8;
    fBasicSQLHasRows: array[boolean] of RawUTF8;
    fUniqueFields: TObjectList;
    function UniqueFieldsUpdateOK(aRec: TSQLRecord; aUpdateIndex: integer): boolean;
    function UniqueFieldHash(aFieldIndex: integer): TListFieldHash;
    function GetCount: integer;
    function GetItem(Index: integer): TSQLRecord;
    function GetID(Index: integer): integer;
    // optimized search of WhereValue in WhereField (0=RowID,1..=RTTI)
................................................................................
      [fStoredClassRecordProps.SQLTableName,aClass.ClassName]);
  fBinaryFile := aBinaryFile;
  fValue := TObjectList.Create;
  fSearchRec := fStoredClass.Create;
  fIDSorted := true; // sorted by design of this class (may change in children)
  if (ClassType<>TSQLRestServerStaticInMemory) and (fStoredClassProps<>nil) then
    with fStoredClassProps do begin // used by AdaptSQLForEngineList() method
      fBasicUpperSQLSelect[false] := UpperCase(SQL.SelectAll[false]);
      SetLength(fBasicUpperSQLSelect[false],length(fBasicUpperSQLSelect[false])-1); // trim right ';'
      fBasicUpperSQLSelect[true] := StringReplaceAll(fBasicUpperSQLSelect[false],' ROWID,',' ID,');
    end;
  fBasicSQLCount := 'SELECT COUNT(*) FROM '+fStoredClassRecordProps.SQLTableName;
  fBasicSQLHasRows[false] := 'SELECT RowID FROM '+fStoredClassRecordProps.SQLTableName+' LIMIT 1';
  fBasicSQLHasRows[true] := fBasicSQLHasRows[false];
  system.delete(fBasicSQLHasRows[true],8,3);
  if not IsZero(fIsUnique) then begin
    fUniqueFields := TObjectList.Create;
    with fStoredClassRecordProps do
    for F := 0 to Fields.Count-1 do
      if F in fIsUnique then
        // CaseInsensitive=true just like
        fUniqueFields.Add(TListFieldHash.Create(fValue,F,Fields.List[F],true));
................................................................................
procedure TSQLRestServerStaticInMemory.DoNothingEvent(aDest: pointer; aRec: TSQLRecord; aIndex: integer);
begin
end;

function TSQLRestServerStaticInMemory.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
var P: PUTF8Char;
    Prop: RawUTF8;
    WithoutRowID: boolean;
begin
  result := inherited AdaptSQLForEngineList(SQL);
  if result then
    exit; // 'select * from table'
  if IdemPropNameU(fBasicSQLCount,SQL) or
     IdemPropNameU(fBasicSQLHasRows[false],SQL) or
     IdemPropNameU(fBasicSQLHasRows[true],SQL) then begin
    result := true;
    exit; // 'select count(*) from table' will be handled as static
  end;
  if fBasicUpperSQLSelect[false]='' then
   exit;
  if IdemPChar(pointer(SQL),pointer(fBasicUpperSQLSelect[false])) then
    WithoutRowID := false else
    if IdemPChar(pointer(SQL),pointer(fBasicUpperSQLSelect[true])) then
      WithoutRowID := true else
      exit;
  P := pointer(SQL);
  inc(P,length(fBasicUpperSQLSelect[WithoutRowID]));
  if P^ in [#0,';'] then begin
    result := true; // properly ended the WHERE clause as 'SELECT * FROM table'
    exit;
  end;
  P := GotoNextNotSpace(P);
  if not IdemPChar(P,'WHERE ') then
    exit;
  P := GotoNextNotSpace(P+6);
  Prop := GetNextItem(P,'=');
  if (P=nil) or (fStoredClassRecordProps.Fields.IndexByName(Prop)<0) then
    exit;
  if PWord(P)^=ord(':')+ord('(') shl 8 then
    inc(P,2); // +2 to ignore :(...): parameter
  if P^ in ['''','"'] then
    P := GotoEndOfQuotedString(P) else
................................................................................
    result := '';
    exit;
  end;
  Lock(false);
  try
    if IdemPropNameU(fBasicSQLCount,SQL) then
      SetCount(fValue.Count) else
    if IdemPropNameU(fBasicSQLHasRows[false],SQL) or
       IdemPropNameU(fBasicSQLHasRows[true],SQL) then
      if fValue.Count=0 then begin
        result := '{"fieldCount":1,"values":["RowID"]}'#$A;
        ResCount := 0;
      end else begin
        result := '{"fieldCount":1,"values":["RowID",'+
          Int32ToUTF8(TSQLRecord(fValue.List[0]).fID)+'"]}'#$A;
        ResCount := 1;
................................................................................

function TSQLRestServerStatic.AdaptSQLForEngineList(var SQL: RawUTF8): boolean; 
begin
  if fStoredClassProps=nil then
    result := false else begin
    result := fStoredClassProps.SQL.SelectAll[false]=SQL;
    if result then
      SQL := fStoredClassProps.SQL.SelectAll[true] else
      result := fStoredClassProps.SQL.SelectAll[true]=SQL;
  end;
end;


{ TSQLRestServerFullMemory }

constructor TSQLRestServerFullMemory.Create(aModel: TSQLModel;