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

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

Overview
SHA1:abbdfa55635c939af0719c7249d7993118bc074f
Date: 2014-11-28 16:32:05
User: ab
Comment:{589} MongoDB ODM now supports complex WHERE clause with several AND/OR expressions, and even the IS NULL / IS NOT NULL, IN () or LIKE operators!
Tags And Properties
Context
2014-11-29
08:30
[c13710637e] {590} refresh contributor list (user: ab, tags: trunk)
2014-11-28
16:32
[abbdfa5563] {589} MongoDB ODM now supports complex WHERE clause with several AND/OR expressions, and even the IS NULL / IS NOT NULL, IN () or LIKE operators! (user: ab, tags: trunk)
12:00
[f003456042] {588} TSynTableStatement.Where[] is now an array to allow complex WHERE clause as expected by [94ff704bb1] (user: ab, tags: trunk)
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to SQLite3/Documentation/Synopse SQLite3 Framework.pro.

5780
5781
5782
5783
5784
5785
5786
5787
5788
5789
5790
5791
5792
5793
5794
5795
5796
5797
5798
5799
5800





















5801
5802
5803
5804
5805
5806
5807
!  finally
!    R.Free;
!  end;
You can define a WHERE clause, as if the back-end where a regular SQL database:
!    R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);
!    try
!    ...
Current implementation understand one condition over one single field, with {\f1\fs20 = > >= < <= IN} clauses. More advanced queries are possible, but won't be handled as SQL, but via direct access to the {\f1\fs20 TMongoDBCollection}.
To perform a query and retrieve the content of several documents, you can use regular {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods:
!!  R := TSQLORM.CreateAndFillPrepare(Client,'');
!  try
!    n := 0;
!!    while R.FillOne do begin
!      // here R instance contains all values of one document, excluding BLOBs
!      inc(n);
!    end;
!    assert(n=COLL_COUNT);
!  finally
!    R.Free;
!  end;
A WHERE clause can also be defined for {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods.





















:   BATCH mode
In addition to individual @*CRUD@ operations, our {\i MongoDB} is able to use BATCH mode for adding or deleting documents.
You can write the exact same code as with any SQL back-end:
!  Client.BatchStart(TSQLORM);
!  R := TSQLORM.Create;
!  try
!    for i := 1 to COLL_COUNT do begin







|

|










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







5780
5781
5782
5783
5784
5785
5786
5787
5788
5789
5790
5791
5792
5793
5794
5795
5796
5797
5798
5799
5800
5801
5802
5803
5804
5805
5806
5807
5808
5809
5810
5811
5812
5813
5814
5815
5816
5817
5818
5819
5820
5821
5822
5823
5824
5825
5826
5827
5828
!  finally
!    R.Free;
!  end;
You can define a WHERE clause, as if the back-end where a regular SQL database:
!    R := TSQLORM.CreateAndFillPrepare(Client,'ID=?',[i]);
!    try
!    ...
:  ODM complex queries
To perform a query and retrieve the content of several documents, you can use regular {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods:
!!  R := TSQLORM.CreateAndFillPrepare(Client,WHERE_CLAUSE,[WHERE_PARAMETERS]);
!  try
!    n := 0;
!!    while R.FillOne do begin
!      // here R instance contains all values of one document, excluding BLOBs
!      inc(n);
!    end;
!    assert(n=COLL_COUNT);
!  finally
!    R.Free;
!  end;
A WHERE clause can also be defined for {\f1\fs20 CreateAndFillPrepare} or {\f1\fs20 FillPrepare} methods. This WHERE clause could contain several expressions, joined with {\f1\fs20 AND} / {\f1\fs20 OR}.\line Each of those expressions could use:
- The simple comparators {\f1\fs20 = < <= <> > >=},
- An {\f1\fs20 IN (....)} clause,
- {\f1\fs20 IS NULL} / {\f1\fs20 IS NOT NULL} tests,
- Or even a {\f1\fs20 LIKE} operation.
The {\i mORMot} ODM will convert this SQL-like statement into the optimized {\f1\fs20 MongoDB} query expression, using e.g. a regular expression for the {\f1\fs20 LIKE} operator.
Here are some typical WHERE clauses, and the corresponding {\i MongoDB} query document as generated by the ODM:
|%50%50
|\b WHERE clause|{\i MongoDB} Query\b0
|{\f1\fs20 'Name=?',['Name 43']}|{\f1\fs20 \{Name:"Name 43"\}}
|{\f1\fs20 'Age<?',[51]}|{\f1\fs20 \{Age:\{$lt:51\}\}}
|{\f1\fs20 'Age in (1,10,20)'}|{\f1\fs20 \{Age:\{$in:[1,10,20]\}\}}
|{\f1\fs20 'Age in (1,10,20) and ID=?',[10]}|{\f1\fs20 \{Age:\{$in:[1,10,20]\},_id:10\}}
|{\f1\fs20 'Age in (10,20) or ID=?',[30]}|{\f1\fs20 \{$or:[\{Age:\{$in:[10,20]\}\},\{_id:30\}]\}}
|{\f1\fs20 'Name like ?',['name 1%']}|{\f1\fs20 \{Name:/^name 1/i\}}
|{\f1\fs20 'Name like ?',['name 1']}|{\f1\fs20 \{Name:/^name 1$/i\}}
|{\f1\fs20 'Name like ?',['%ame 1%']}|{\f1\fs20 \{Name:/ame 1/i\}}
|{\f1\fs20 'Data is null'}|{\f1\fs20 \{Data:null\}}
|{\f1\fs20 'Data is not null'}|{\f1\fs20 \{Data:\{$ne:null\}\}}
|%
Note that parenthesis and mixed {\f1\fs20 AND} {\f1\fs20 OR} expressions are not handled yet. You could always execute any complex query (e.g. aggregations or {\i Map/Reduce}) by using directly the {\f1\fs20 TMongoCollection} methods.
But for most cases, {\i mORMot} allows to share the same exact code between your regular SQL databases or NoSQL engines. You do not need to learn the {\i MongoDB} query syntax: the ODM would compute the right expression for you, depending on the database engine it runs on.
:   BATCH mode
In addition to individual @*CRUD@ operations, our {\i MongoDB} is able to use BATCH mode for adding or deleting documents.
You can write the exact same code as with any SQL back-end:
!  Client.BatchStart(TSQLORM);
!  R := TSQLORM.Create;
!  try
!    for i := 1 to COLL_COUNT do begin

Changes to SQLite3/Samples/24 - MongoDB/MongoDBTestCases.pas.

463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
...
502
503
504
505
506
507
508















































































509
510
511
512
513
514
515
...
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
...
604
605
606
607
608
609
610




611
612
613
614
615
616
617
  end;
  NotifyTestSpeed('rows retrieved',COLL_COUNT,fMongoClient.BytesTransmitted-bytes);
end;

procedure TTestORM.RetrieveOneWithWhereClause;
var R: TSQLORM;
    i,n: integer;
    bytes: Int64;
begin
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    R := TSQLORM.CreateAndFillPrepare(fClient,'ID=?',[i]);
    try
      n := 0;
      while R.FillOne do begin
................................................................................
      inc(n);
      TestOne(R,n);
    end;
    Check(n=50);
  finally
    R.Free;
  end;















































































end;

procedure TTestORM.Update;
var R: TSQLORM;
    bytes: Int64;
    n: integer;
begin
................................................................................
  end;
end;

procedure TTestORM.Blobs;
var R: TSQLORM;
    i, n: integer;
    blob,blobRead: TSQLRawBlob;
    bytes: Int64;
begin
  SetLength(blob,8);
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    PIntegerArray(blob)[0] := i;
    PIntegerArray(blob)[1] := i*$01020304;
    Check(fClient.UpdateBlob(TSQLORM,i,'Data',blob));
................................................................................
      Check(R.Data=blob);
      R.Data := '';
    end;
    Check(n=COLL_COUNT);
  finally
    R.Free;
  end;




end;

procedure TTestORM.Delete;
var i,n: integer;
    ExpectedCount: integer;
    bytes: Int64;
    temp: string;







|







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







 







>
>
>
>







463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
...
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
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
...
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
...
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
  end;
  NotifyTestSpeed('rows retrieved',COLL_COUNT,fMongoClient.BytesTransmitted-bytes);
end;

procedure TTestORM.RetrieveOneWithWhereClause;
var R: TSQLORM;
    i,n: integer;
    bytes,i64: Int64;
begin
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    R := TSQLORM.CreateAndFillPrepare(fClient,'ID=?',[i]);
    try
      n := 0;
      while R.FillOne do begin
................................................................................
      inc(n);
      TestOne(R,n);
    end;
    Check(n=50);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (1,10,20)',[51]);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      case n of
      1: i := 1;
      2: i := 10;
      3: i := 20;
      else i := 0;
      end;
      TestOne(R,i);
    end;
    Check(n=3);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (1,10,20) and ID=?',[10]);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      TestOne(R,10);
    end;
    Check(n=1);
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Age in (10,20) or ID=?',[30]);
  try  
    n := 0;
    while R.FillOne do begin
      inc(n);
      TestOne(R,n*10);
    end;
    Check(n=3,'{$or:[{Age:{$in:[10,20]}},{_id:30}]}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['name 1%']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n>10,'{Name:/^name 1/i}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['name 1']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n=1,'{Name:/^name 1$/i}');
  finally
    R.Free;
  end;
  R := TSQLORM.CreateAndFillPrepare(fClient,'Name like ?',['%ame 1%']);
  try
    n := 0;
    while R.FillOne do begin
      inc(n);
      Check(IdemPChar(pointer(R.Name),'NAME 1'));
      TestOne(R,R.Age);
    end;
    Check(n>10,'{Name:/ame 1/i}');
  finally
    R.Free;
  end;
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is null',[],[],i64));
  check(i64=COLL_COUNT,'{Data:null}');
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is not null',[],[],i64));
  check(i64=0,'{Data:{$ne:null}}');
end;

procedure TTestORM.Update;
var R: TSQLORM;
    bytes: Int64;
    n: integer;
begin
................................................................................
  end;
end;

procedure TTestORM.Blobs;
var R: TSQLORM;
    i, n: integer;
    blob,blobRead: TSQLRawBlob;
    bytes,i64: Int64;
begin
  SetLength(blob,8);
  bytes := fMongoClient.BytesTransmitted;
  for i := 1 to COLL_COUNT do begin
    PIntegerArray(blob)[0] := i;
    PIntegerArray(blob)[1] := i*$01020304;
    Check(fClient.UpdateBlob(TSQLORM,i,'Data',blob));
................................................................................
      Check(R.Data=blob);
      R.Data := '';
    end;
    Check(n=COLL_COUNT);
  finally
    R.Free;
  end;
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is null',[],[],i64));
  check(i64=0,'{Data:null}');
  check(fClient.OneFieldValue(TSQLORM,'count(*)','Data is not null',[],[],i64));
  check(i64=COLL_COUNT,'{Data:{$ne:null}}');
end;

procedure TTestORM.Delete;
var i,n: integer;
    ExpectedCount: integer;
    bytes: Int64;
    temp: string;

Changes to SQLite3/mORMot.pas.

24852
24853
24854
24855
24856
24857
24858


24859
24860
24861
24862
24863
24864
24865

24866
24867
24868
24869
24870
24871
24872
.....
31584
31585
31586
31587
31588
31589
31590
31591
31592
31593
31594
31595
31596
31597
31598
31599
31600
31601
31602
var SQL: RawUTF8;
    i: integer;
    T: TSQLTableJSON;
begin
  result := false;
  if (self<>nil) and (Table<>nil) and (length(FieldName)=length(FieldValue)) then
  with Table.RecordProps do begin


    for i := 0 to high(FieldName) do
      if not IsFieldName(FieldName[i]) then
        exit else // prevent SQL error
        if SQL='' then
          SQL := 'SELECT '+FieldName[i] else
          SQL := SQL+','+FieldName[i];
    SQL := SQL+' FROM '+SQLTableName+' WHERE '+WhereClause+' LIMIT 1;';

    T := ExecuteList([Table],SQL);
    if T<>nil then
    try
      if (T.FieldCount<>length(FieldName)) or (T.RowCount<=0) then
        exit;
      // get field values from the first (and unique) row
      for i := 0 to T.FieldCount-1 do
................................................................................
                inc(result);
                if (Stmt.Limit>0) and (result>=Stmt.Limit) then
                  break;
              end;
            end else
              goto err;
    {$endif}
    opIs: // handle IS NULL and IS NOT NULL operators
      if Stmt.Where[0].Field>0 then begin
        Prop := fStoredClassRecordProps.Fields.List[Stmt.Where[0].Field-1];
        if Prop.InheritsFrom(TSQLPropInfoRTTIRawBlob) then begin
          IsNull := IdemPropName(Stmt.Where[0].Value,'NULL');
          for i := 0 to fValue.Count-1 do
          if TSQLPropInfoRTTIRawBlob(Prop).IsNull(fValue.List[i])=IsNull then begin
            TSQLRecord(fValue.List[i]).GetJSONValues(W);
            W.Add(',');
            inc(result);
            if (Stmt.Limit>0) and (result>=Stmt.Limit) then
              break;







>
>
|
|
|
|
|
|
|
>







 







|



|







24852
24853
24854
24855
24856
24857
24858
24859
24860
24861
24862
24863
24864
24865
24866
24867
24868
24869
24870
24871
24872
24873
24874
24875
.....
31587
31588
31589
31590
31591
31592
31593
31594
31595
31596
31597
31598
31599
31600
31601
31602
31603
31604
31605
var SQL: RawUTF8;
    i: integer;
    T: TSQLTableJSON;
begin
  result := false;
  if (self<>nil) and (Table<>nil) and (length(FieldName)=length(FieldValue)) then
  with Table.RecordProps do begin
    if (length(FieldName)=1) and IdemPChar(pointer(FieldName[0]),'COUNT(*)') then
      SQL := 'SELECT COUNT(*) FROM '+SQLTableName+' WHERE '+WhereClause else begin
      for i := 0 to high(FieldName) do
        if not IsFieldName(FieldName[i]) then
          exit else // prevent SQL error
          if SQL='' then
            SQL := 'SELECT '+FieldName[i] else
            SQL := SQL+','+FieldName[i];
      SQL := SQL+' FROM '+SQLTableName+' WHERE '+WhereClause+' LIMIT 1;';
    end;
    T := ExecuteList([Table],SQL);
    if T<>nil then
    try
      if (T.FieldCount<>length(FieldName)) or (T.RowCount<=0) then
        exit;
      // get field values from the first (and unique) row
      for i := 0 to T.FieldCount-1 do
................................................................................
                inc(result);
                if (Stmt.Limit>0) and (result>=Stmt.Limit) then
                  break;
              end;
            end else
              goto err;
    {$endif}
    opIsNull, opIsNotNull:
      if Stmt.Where[0].Field>0 then begin
        Prop := fStoredClassRecordProps.Fields.List[Stmt.Where[0].Field-1];
        if Prop.InheritsFrom(TSQLPropInfoRTTIRawBlob) then begin
          IsNull := Stmt.Where[0].Operator=opIsNull;
          for i := 0 to fValue.Count-1 do
          if TSQLPropInfoRTTIRawBlob(Prop).IsNull(fValue.List[i])=IsNull then begin
            TSQLRecord(fValue.List[i]).GetJSONValues(W);
            W.Add(',');
            inc(result);
            if (Stmt.Limit>0) and (result>=Stmt.Limit) then
              break;

Changes to SQLite3/mORMotMongoDB.pas.

113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
...
801
802
803
804
805
806
807

808
809
810
811
812



813






814

815
816
817




















818
819
820
821
822
823
824
825
826













827
828


829

830
831
832
833
834
835

836
837
838
839
840
841
842
...
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874



875
876
877
878
879
880



881
882
883
884
885
886
887
    function EngineList(const SQL: RawUTF8; ForceAJAX: Boolean=false; ReturnedRowCount: PPtrInt=nil): RawUTF8; override;
    function EngineAdd(TableModelIndex: integer; const SentData: RawUTF8): TID; override;
    function EngineUpdate(TableModelIndex: integer; ID: TID; const SentData: RawUTF8): boolean; override;
    function EngineUpdateField(TableModelIndex: integer;
      const SetFieldName, SetValue, WhereFieldName, WhereValue: RawUTF8): boolean; override;
    function EngineDeleteWhere(TableModelIndex: Integer;const SQLWhere: RawUTF8;
      const IDs: TIDDynArray): boolean; override;
    // BLOBs should be access directly, not through slower JSON Base64 encoding
    function EngineRetrieveBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; out BlobData: TSQLRawBlob): boolean; override;
    function EngineUpdateBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; const BlobData: TSQLRawBlob): boolean; override;
    // method not implemented: always return false
    function EngineExecute(const aSQL: RawUTF8): boolean; override;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
................................................................................
    Query,Projection: variant;
    Res: TBSONDocument;
    ResCount: PtrInt;
    extFieldNames: TRawUTF8DynArray;
    Stmt: TSynTableStatement;
    bits: TSQLFieldBits;
    withID: boolean;

procedure ComputeQuery;
const // see http://docs.mongodb.org/manual/reference/operator/query
  QUERY_OPS: array[opNotEqualTo..opIn] of RawUTF8 = (
    '$ne','$lt','$lte','$gt','$gte','$in');
var QueryFieldName: RawUTF8;



begin






  if (Length(Stmt.Where)<>1) or (Stmt.Where[0].Field<0) then begin

    SetVariantNull(Query); // only a SINGLE expression is allowed yet
    exit;
  end;




















  QueryFieldName := fStoredClassProps.ExternalDB.FieldNameByIndex(Stmt.Where[0].Field-1);
  case Stmt.Where[0].Operator of
  opEqualTo:
    Query := BSONVariant([QueryFieldName,Stmt.Where[0].ValueVariant]);
  opIs: // http://docs.mongodb.org/manual/faq/developers/#faq-developers-query-for-nulls
    if IdemPropName(Stmt.Where[0].Value,'null') then
      Query := BSONVariant('{%:{$type:10}}',[QueryFieldName],[]) else
      Query := BSONVariant('{%:{$not:{type:10}}}',[QueryFieldName],[])
  else













    Query := BSONVariant([QueryFieldName,
      '{',QUERY_OPS[Stmt.Where[0].Operator],Stmt.Where[0].ValueVariant,'}']);


  end;

end;
procedure SetCount(aCount: integer);
begin
  result := FormatUTF8('[{"Count(*)":%}]'#$A,[aCount]);
  ResCount := 1;
end;

begin // same logic as in TSQLRestStorageInMemory.EngineList()
  ResCount := 0;
  if self=nil then begin
    result := '';
    exit;
  end;
  StorageLock(false);
................................................................................
        result := '[{"RowID":1}]'#$A;
        ResCount := 1;
      end else begin
      Stmt := TSynTableStatement.Create(SQL,
        fStoredClassRecordProps.Fields.IndexByName,
        fStoredClassRecordProps.SimpleFieldsBits[soSelect]);
      try
        if (Stmt.SQLStatement='') or  // parsing failed
           (length(Stmt.Where)<>1) or // only a SINGLE expression is allowed yet
           not IdemPropNameU(Stmt.TableName,fStoredClassRecordProps.SQLTableName) then begin
          // invalid request -> return '' to mark error
          result := '';
          exit;
        end;
        if Stmt.Where[0].Field=SYNTABLESTATEMENTWHERECOUNT then
          // was "SELECT Count(*) FROM TableName;"
          SetCount(TableRowCount(fStoredClass)) else
        if Stmt.SelectFields=nil then begin
          if Stmt.IsSelectCountWhere then
            // was "SELECT Count(*) FROM TableName WHERE ..."
            if Stmt.Where[0].Field<0 then
              SetCount(TableRowCount(fStoredClass)) else begin
              ComputeQuery;



              SetCount(fCollection.FindCount(Query));
            end;
          exit; // also invalid "SELECT FROM Table"
        end;
        // save rows as JSON, with appropriate search according to Where* arguments
        ComputeQuery;



        Stmt.SelectFieldBits(bits,withID);
        BSONProjectionSet(Projection,withID,bits,@extFieldNames);
        if Stmt.Limit=0 then
          Stmt.Limit := maxInt;
        Res := fCollection.FindBSON(Query,Projection,Stmt.Limit,Stmt.Offset);
        MS := TRawByteStringStream.Create;
        try







|







 







>
|
<
<
<
|
>
>
>

>
>
>
>
>
>
|
>
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<
<
<
<
<
<
>
>
>
>
>
>
>
>
>
>
>
>
>
|
<
>
>

>






>







 







|
<
|












|
>
>
>





|
>
>
>







113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
...
801
802
803
804
805
806
807
808
809



810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846








847
848
849
850
851
852
853
854
855
856
857
858
859
860

861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
...
888
889
890
891
892
893
894
895

896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
    function EngineList(const SQL: RawUTF8; ForceAJAX: Boolean=false; ReturnedRowCount: PPtrInt=nil): RawUTF8; override;
    function EngineAdd(TableModelIndex: integer; const SentData: RawUTF8): TID; override;
    function EngineUpdate(TableModelIndex: integer; ID: TID; const SentData: RawUTF8): boolean; override;
    function EngineUpdateField(TableModelIndex: integer;
      const SetFieldName, SetValue, WhereFieldName, WhereValue: RawUTF8): boolean; override;
    function EngineDeleteWhere(TableModelIndex: Integer;const SQLWhere: RawUTF8;
      const IDs: TIDDynArray): boolean; override;
    // BLOBs should be accessed directly, not through slower JSON Base64 encoding
    function EngineRetrieveBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; out BlobData: TSQLRawBlob): boolean; override;
    function EngineUpdateBlob(TableModelIndex: integer; aID: TID;
      BlobField: PPropInfo; const BlobData: TSQLRawBlob): boolean; override;
    // method not implemented: always return false
    function EngineExecute(const aSQL: RawUTF8): boolean; override;
    /// TSQLRestServer.URI use it for Static.EngineList to by-pass virtual table
................................................................................
    Query,Projection: variant;
    Res: TBSONDocument;
    ResCount: PtrInt;
    extFieldNames: TRawUTF8DynArray;
    Stmt: TSynTableStatement;
    bits: TSQLFieldBits;
    withID: boolean;

function ComputeQuery: boolean;



var FieldName: RawUTF8;
    B: TBSONWriter;
    start,startOR,startORItem: cardinal;
    n,w: integer;
begin
  result := false;
  if Stmt.SQLStatement='' then begin
    InternalLog('%.EngineList: Invalid SQL statement "%"',[self,SQL],sllError);
    exit;
  end;
  n := Length(Stmt.Where);
  if (n<1) or (Stmt.Where[0].Field<0) then begin // no WHERE clause or COUNT(*)
    result := true;
    SetVariantNull(Query); // void query -> returns all rows
    exit;
  end;
  B := TBSONWriter.Create(TRawByteStringStream);
  try
    startORItem := 0; // makes compiler happy
    start := B.BSONDocumentBegin;
    if (n>1) and Stmt.Where[1].JoinedOR then begin
      for w := 2 to n-1 do
      if not Stmt.Where[w].JoinedOR then begin
        InternalLog('%.EngineList: Unhandled mixed AND/OR for "%"',[self,SQL],sllError);
        exit;
      end;
      B.BSONWrite('$or',betArray); // e.g. {$or:[{quantity:{$lt:20}},{price:10}]}
      startOR := B.BSONDocumentBegin;
    end else
      startOR := cardinal(-1);
    for w := 0 to n-1 do begin
      if Integer(startOR)>=0 then begin
        B.BSONWrite(UInt32ToUtf8(w),betDoc);
        startORItem := B.BSONDocumentBegin;
      end;
      with Stmt.Where[w] do begin
        FieldName := fStoredClassProps.ExternalDB.FieldNameByIndex(Field-1);








        if not B.BSONWriteQueryOperator(FieldName,Operator,ValueVariant) then begin
          InternalLog('%.EngineList: Unhandled operator % for field "%" in "%"',[
            self,GetEnumName(TypeInfo(TSynTableStatementOperator),ord(Operator))^,
            FieldName,SQL],sllError);
          exit;
        end;
      end;
      if Integer(startOR)>=0 then
        B.BSONDocumentEnd(startORItem);
    end;
    if Integer(startOR)>=0 then
      B.BSONDocumentEnd(startOR);
    B.BSONDocumentEnd(start);
    B.ToBSONVariant(Query);

  finally
    B.Free;
  end;
  result := true; // indicates success
end;
procedure SetCount(aCount: integer);
begin
  result := FormatUTF8('[{"Count(*)":%}]'#$A,[aCount]);
  ResCount := 1;
end;

begin // same logic as in TSQLRestStorageInMemory.EngineList()
  ResCount := 0;
  if self=nil then begin
    result := '';
    exit;
  end;
  StorageLock(false);
................................................................................
        result := '[{"RowID":1}]'#$A;
        ResCount := 1;
      end else begin
      Stmt := TSynTableStatement.Create(SQL,
        fStoredClassRecordProps.Fields.IndexByName,
        fStoredClassRecordProps.SimpleFieldsBits[soSelect]);
      try
        if (Stmt.SQLStatement='') or (length(Stmt.Where)=0) or  // parsing failed

          not IdemPropNameU(Stmt.TableName,fStoredClassRecordProps.SQLTableName) then begin
          // invalid request -> return '' to mark error
          result := '';
          exit;
        end;
        if Stmt.Where[0].Field=SYNTABLESTATEMENTWHERECOUNT then
          // was "SELECT Count(*) FROM TableName;"
          SetCount(TableRowCount(fStoredClass)) else
        if Stmt.SelectFields=nil then begin
          if Stmt.IsSelectCountWhere then
            // was "SELECT Count(*) FROM TableName WHERE ..."
            if Stmt.Where[0].Field<0 then
              SetCount(TableRowCount(fStoredClass)) else begin
              if not ComputeQuery then begin
                result := ''; // indicates error
                exit;
              end;
              SetCount(fCollection.FindCount(Query));
            end;
          exit; // also invalid "SELECT FROM Table"
        end;
        // save rows as JSON, with appropriate search according to Where* arguments
        if not ComputeQuery then begin
          result := ''; // indicates error
          exit;
        end;
        Stmt.SelectFieldBits(bits,withID);
        BSONProjectionSet(Projection,withID,bits,@extFieldNames);
        if Stmt.Limit=0 then
          Stmt.Limit := maxInt;
        Res := fCollection.FindBSON(Query,Projection,Stmt.Limit,Stmt.Offset);
        MS := TRawByteStringStream.Create;
        try

Changes to SynCommons.pas.

8576
8577
8578
8579
8580
8581
8582
8583

8584
8585
8586
8587
8588
8589
8590
.....
40498
40499
40500
40501
40502
40503
40504
40505
40506
40507
40508
40509
40510



40511
40512
40513
40514
40515
40516



40517
40518
40519
40520
40521
40522
40523
40524
40525
.....
40529
40530
40531
40532
40533
40534
40535


40536
40537
40538
40539
40540
40541
40542
     opEqualTo,
     opNotEqualTo,
     opLessThan,
     opLessThanOrEqualTo,
     opGreaterThan,
     opGreaterThanOrEqualTo,
     opIn,
     opIs,

     opLike);

  TSynTableFieldProperties = class;

  /// one recognized WHERE expression for TSynTableStatement
  TSynTableStatementWhere = record
    /// expressions are evaluated as AND unless this field is set to TRUE
................................................................................
       else
         Where.Operator := opLessThan;
       end;
  'i','I':
    case P[1] of
    's','S': begin
      P := GotoNextNotSpace(P+2);
      {$ifndef NOVARIANTS}
      SetVariantNull(Where.ValueVariant);
      {$endif}
      if IdemPChar(P,'NULL') then begin
        Where.Value := 'null';
        Where.Operator := opIs;



        inc(P,4);
        result := true;
      end else
      if IdemPChar(P,'NOT NULL') then begin
        Where.Value := 'not null';
        Where.Operator := opIs;



        inc(P,8);
        result := true;
      end;
      exit;
    end;
    {$ifndef NOVARIANTS}
    'n','N': begin
       Where.Operator := opIn;
       P := GotoNextNotSpace(P+2);
................................................................................
       inc(P);
       while P^<>')' do
         if P^=#0 then
           exit else
           inc(P);
       inc(P);
       SetString(Where.Value,PAnsiChar(B),P-B);


       Where.Value[1] := '[';
       Where.Value[P-B] := ']';
       TDocVariantData(Where.ValueVariant).InitJSONInPlace(
         pointer(Where.Value),JSON_OPTIONS[true]);
       result := true;
       exit;
    end;







|
>







 







<
<
<


|
>
>
>





|
>
>
>

|







 







>
>







8576
8577
8578
8579
8580
8581
8582
8583
8584
8585
8586
8587
8588
8589
8590
8591
.....
40499
40500
40501
40502
40503
40504
40505



40506
40507
40508
40509
40510
40511
40512
40513
40514
40515
40516
40517
40518
40519
40520
40521
40522
40523
40524
40525
40526
40527
40528
40529
.....
40533
40534
40535
40536
40537
40538
40539
40540
40541
40542
40543
40544
40545
40546
40547
40548
     opEqualTo,
     opNotEqualTo,
     opLessThan,
     opLessThanOrEqualTo,
     opGreaterThan,
     opGreaterThanOrEqualTo,
     opIn,
     opIsNull,
     opIsNotNull,
     opLike);

  TSynTableFieldProperties = class;

  /// one recognized WHERE expression for TSynTableStatement
  TSynTableStatementWhere = record
    /// expressions are evaluated as AND unless this field is set to TRUE
................................................................................
       else
         Where.Operator := opLessThan;
       end;
  'i','I':
    case P[1] of
    's','S': begin
      P := GotoNextNotSpace(P+2);



      if IdemPChar(P,'NULL') then begin
        Where.Value := 'null';
        Where.Operator := opIsNull;
        Where.ValueSQL := P;
        Where.ValueSQLLen := 4;
        TVarData(Where.ValueVariant).VType := varNull;
        inc(P,4);
        result := true;
      end else
      if IdemPChar(P,'NOT NULL') then begin
        Where.Value := 'not null';
        Where.Operator := opIsNotNull;
        Where.ValueSQL := P;
        Where.ValueSQLLen := 8;
        TVarData(Where.ValueVariant).VType := varNull;
        inc(P,8);
        result := true; // leave ValueVariant=unassigned
      end;
      exit;
    end;
    {$ifndef NOVARIANTS}
    'n','N': begin
       Where.Operator := opIn;
       P := GotoNextNotSpace(P+2);
................................................................................
       inc(P);
       while P^<>')' do
         if P^=#0 then
           exit else
           inc(P);
       inc(P);
       SetString(Where.Value,PAnsiChar(B),P-B);
       Where.ValueSQL := B;
       Where.ValueSQLLen := P-B;
       Where.Value[1] := '[';
       Where.Value[P-B] := ']';
       TDocVariantData(Where.ValueVariant).InitJSONInPlace(
         pointer(Where.Value),JSON_OPTIONS[true]);
       result := true;
       exit;
    end;

Changes to SynMongoDB.pas.

516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
...
558
559
560
561
562
563
564






565
566
567
568
569
570
571
....
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991



2992
2993
2994
2995
2996
2997
2998
....
3210
3211
3212
3213
3214
3215
3216





















































3217
3218
3219
3220
3221
3222
3223
    /// write a string (UTF-8) value from a memory buffer
    procedure BSONWriteString(const name: RawUTF8; value: PUTF8Char; valueLen: integer); 
    /// write a binary (BLOB) value
    procedure BSONWrite(const name: RawUTF8; Data: pointer; DataLen: integer); overload;
    /// write an ObjectID value
    procedure BSONWrite(const name: RawUTF8; const value: TBSONObjectID); overload;
    /// write a RegEx value
    procedure BSONWriteRegEx(const name: RawUTF8; const RegEx: RawByteString);
    /// write a data/time value
    procedure BSONWriteDateTime(const name: RawUTF8; const value: TDateTime);
    /// write an element with no value
    // - elemType can be either betNull, betMinKey or betMaxKey
    procedure BSONWrite(const name: RawUTF8; elemtype: TBSONElementType); overload;
    /// write an element with no value
    procedure BSONWrite(const name: RawUTF8; const elem: TBSONElement); overload;
................................................................................
    /// write an object specified as name/value pairs as a BSON document
    // - data must be supplied two by two, as Name,Value pairs, e.g.
    // ! aBSONWriter.BSONWriteObject(['name','John','year',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ObjFast(...))
    procedure BSONWriteObject(const NameValuePairs: array of const);
    /// write a projection specified as fieldname:1 pairs as a BSON document
    procedure BSONWriteProjection(const FieldNamesCSV: RawUTF8);






    /// write an array specified as a list of items as a BSON document
    // - data must be supplied as a list of values e.g.
    // ! aBSONWriter.BSONWriteArray(['John',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ArrFast(...))
    procedure BSONWriteArray(const Items: array of const);
    /// write an array of integers as a BSON Document
    procedure BSONWriteArrayOfInteger(const Integers: array of integer);
................................................................................

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: TBSONObjectID);
begin
  BSONWrite(name,betObjectID);
  Write(@value,sizeof(value));
end;

procedure TBSONWriter.BSONWriteRegEx(const name: RawUTF8; const RegEx: RawByteString);
begin
  BSONWrite(name,betRegEx);
  Write(pointer(RegEx),length(RegEx));



end;

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: RawUTF8;
  isJavaScript: boolean=false);
const TYP: array[boolean] of TBSONElementType = (betString,betJS);
var L: integer;
begin
................................................................................
begin
  CSVToRawUTF8DynArray(pointer(FieldNamesCSV),FieldNames);
  Start := BSONDocumentBegin;
  for i := 0 to high(FieldNames) do
    BSONWrite(FieldNames[i],1);
  BSONDocumentEnd(Start);
end;






















































procedure TBSONWriter.BSONWriteObject(const NameValuePairs: array of const);
var Start: cardinal;
    Name: RawUTF8;
    i: integer;
begin
  Start := BSONDocumentBegin;







|







 







>
>
>
>
>
>







 







|

|

>
>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
...
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
....
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
....
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
3249
3250
3251
3252
3253
3254
3255
3256
3257
3258
3259
3260
3261
3262
3263
3264
3265
3266
3267
3268
3269
3270
3271
3272
3273
3274
3275
3276
3277
3278
3279
3280
3281
3282
3283
3284
3285
    /// write a string (UTF-8) value from a memory buffer
    procedure BSONWriteString(const name: RawUTF8; value: PUTF8Char; valueLen: integer); 
    /// write a binary (BLOB) value
    procedure BSONWrite(const name: RawUTF8; Data: pointer; DataLen: integer); overload;
    /// write an ObjectID value
    procedure BSONWrite(const name: RawUTF8; const value: TBSONObjectID); overload;
    /// write a RegEx value
    procedure BSONWriteRegEx(const name: RawUTF8; const RegEx,Options: RawByteString);
    /// write a data/time value
    procedure BSONWriteDateTime(const name: RawUTF8; const value: TDateTime);
    /// write an element with no value
    // - elemType can be either betNull, betMinKey or betMaxKey
    procedure BSONWrite(const name: RawUTF8; elemtype: TBSONElementType); overload;
    /// write an element with no value
    procedure BSONWrite(const name: RawUTF8; const elem: TBSONElement); overload;
................................................................................
    /// write an object specified as name/value pairs as a BSON document
    // - data must be supplied two by two, as Name,Value pairs, e.g.
    // ! aBSONWriter.BSONWriteObject(['name','John','year',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ObjFast(...))
    procedure BSONWriteObject(const NameValuePairs: array of const);
    /// write a projection specified as fieldname:1 pairs as a BSON document
    procedure BSONWriteProjection(const FieldNamesCSV: RawUTF8);
    /// write an object as query parameter
    // - will handle all SQL operators, including IN (), IS NULL or LIKE 
    // - see @http://docs.mongodb.org/manual/reference/operator/query
    // - returns TRUE on success, FALSE if the operator is not implemented yet
    function BSONWriteQueryOperator(const name: RawUTF8; operator: TSynTableStatementOperator;
      const Value: variant): boolean;
    /// write an array specified as a list of items as a BSON document
    // - data must be supplied as a list of values e.g.
    // ! aBSONWriter.BSONWriteArray(['John',1972]);
    // - this method wil be faster than using a BSONWriteDoc(_ArrFast(...))
    procedure BSONWriteArray(const Items: array of const);
    /// write an array of integers as a BSON Document
    procedure BSONWriteArrayOfInteger(const Integers: array of integer);
................................................................................

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: TBSONObjectID);
begin
  BSONWrite(name,betObjectID);
  Write(@value,sizeof(value));
end;

procedure TBSONWriter.BSONWriteRegEx(const name: RawUTF8; const RegEx,Options: RawByteString);
begin
  BSONWrite(name,betRegEx); // cstring cstring
  Write(pointer(RegEx),length(RegEx));
  Write1(0);
  Write(pointer(Options),length(Options));
  Write1(0);
end;

procedure TBSONWriter.BSONWrite(const name: RawUTF8; const value: RawUTF8;
  isJavaScript: boolean=false);
const TYP: array[boolean] of TBSONElementType = (betString,betJS);
var L: integer;
begin
................................................................................
begin
  CSVToRawUTF8DynArray(pointer(FieldNamesCSV),FieldNames);
  Start := BSONDocumentBegin;
  for i := 0 to high(FieldNames) do
    BSONWrite(FieldNames[i],1);
  BSONDocumentEnd(Start);
end;

function TBSONWriter.BSONWriteQueryOperator(const name: RawUTF8;
  operator: TSynTableStatementOperator; const Value: variant): boolean;
const
  QUERY_OPS: array[opNotEqualTo..opIn] of RawUTF8 = (
    '$ne','$lt','$lte','$gt','$gte','$in');
var start: cardinal;
    wasString: boolean;
    like: RawUTF8;
    len: integer;
begin
  result := false; // error on premature exit
  case Operator of
  // http://docs.mongodb.org/manual/faq/developers/#faq-developers-query-for-nulls
  // {$type:10} would return only existing fields, but our ODM do not insert
  // blobs by default -> do not use {$type:10} trick but plain {field:null}
  opIsNull:
    operator := opEqualTo;     // here Value=null
  opIsNotNull:
    operator := opNotEqualTo;  // here Value=null
  end;
  case Operator of
  opEqualTo:
    BSONWriteVariant(name,Value);
  opNotEqualTo..opIn: begin
    BSONWrite(name,betDoc);
    start := BSONDocumentBegin;
    BSONWriteVariant(QUERY_OPS[operator],Value);
    BSONDocumentEnd(start);
  end;
  opLike: begin
    VariantToUTF8(Value,like,wasString);
    len := length(like);
    if (len=0) or not wasString then
      exit;
    if like[1]='%' then
      if len=1 then // LIKE '%' is invalid 
        exit else
        if like[len]='%' then
          if len=2 then
            exit else // LIKE '%%' is invalid
            like := copy(like,2,len-2) else    // LIKE '%a%' -> /a/
          like := copy(like,2,len-1)+'$' else  // LIKE '%a'  -> /a$/
      if like[len]='%' then
        like := '^'+copy(like,1,len-1) else    // LIKE 'a%'  -> /^a/
        like := '^'+like+'$';                  // LIKE 'a'   -> /^a$/
    BSONWriteRegEx(name,like,'i'); // /like/i for case-insensitivity
  end;
  else
    exit; // unhandled operator
  end;
  result := true;
end;

procedure TBSONWriter.BSONWriteObject(const NameValuePairs: array of const);
var Start: cardinal;
    Name: RawUTF8;
    i: integer;
begin
  Start := BSONDocumentBegin;

Changes to SynSelfTests.pas.

5930
5931
5932
5933
5934
5935
5936
5937
5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
5951
5952
5953
5954
5955
5956
5957
5958
5959
5960
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Stmt.Where[0].Field=0);
  Check(Stmt.Where[0].Operator=opNotEqualTo);
  Check(Stmt.Where[0].ValueInteger=100);
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIs);
  Check(Stmt.Where[1].Value='not null');
  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  New('select data,iD from tab where firstname like "monet" or data is null limit 20 offset 10');
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Props.Fields.List[Stmt.Where[0].Field-1].Name='FirstName');
  Check(Stmt.Where[0].Operator=opLike);
  Check(Stmt.Where[0].Value='monet');
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIs);
  Check(Stmt.Where[1].Value='null');
  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  Stmt.Free;
end;







|
<













|
<







5930
5931
5932
5933
5934
5935
5936
5937

5938
5939
5940
5941
5942
5943
5944
5945
5946
5947
5948
5949
5950
5951

5952
5953
5954
5955
5956
5957
5958
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Stmt.Where[0].Field=0);
  Check(Stmt.Where[0].Operator=opNotEqualTo);
  Check(Stmt.Where[0].ValueInteger=100);
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIsNotNull);

  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  New('select data,iD from tab where firstname like "monet" or data is null limit 20 offset 10');
  Check(Stmt.TableName='tab');
  Check(length(Stmt.Where)=2);
  Check(Props.Fields.List[Stmt.Where[0].Field-1].Name='FirstName');
  Check(Stmt.Where[0].Operator=opLike);
  Check(Stmt.Where[0].Value='monet');
  Check(Stmt.Where[1].JoinedOR);
  Check(Props.Fields.List[Stmt.Where[1].Field-1].Name='Data');
  Check(Stmt.Where[1].Operator=opIsNull);

  Check(Stmt.Limit=20);
  Check(Stmt.Offset=10);
  Check((length(Stmt.SelectFields)=2)and(Stmt.SelectFields[1]=0)and
    (Props.Fields.List[Stmt.SelectFields[0]-1].Name='Data'));
  Check(Stmt.OrderByField=nil);
  Stmt.Free;
end;

Changes to SynopseCommit.inc.

1
'1.18.588'
|
1
'1.18.589'