myldbexpressions.pas

一个本地database引擎,支持中文T_Sql查询,兼容DELPHI标准数据库控件

    // process assign New Cursor Buffer
    procedure AssignCursorBuffer(Buffer: TMYLDBRecordBuffer; ApplyToParentQueryFields: Boolean);
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer;
    // return Size of Data (for strings and arrays)
    function GetPrecision: Integer;
    // is expression contains no nodes
    function IsEmpty: Boolean;
    // is expression a Field (for join)
    function IsField: Boolean;
    // is expression a fields combination
    function IsFieldsExpression: Boolean;
    // Move AndNodes To RootNode
    procedure MoveAndNodesToRoot;
    // Field Name, Table Name
    procedure GetFieldInfo(var TableName: String; var FieldName: String);
    // makes filter string from related parts and sets it to AO
    procedure ApplyFilterParts(AO: TMYLDBAO; ParentQueryAO: TMYLDBAO;
                ParentCursor: TMYLDBCursor;
                AllowRecurse: Boolean = True; ForHavingClause: Boolean = False);
    // replace pseudonyms to original names (f1 -> table1.field1)
    procedure ReplacePseudonyms(SelectList: array of TMYLDBSelectListItem);
    // makes join field lists
    function ExtractJoinConditions(
                                    AO1, AO2: TMYLDBAO;
                                    FieldList1, FieldList2: TMYLDBFields
                                  ): Integer;
    // create conditions for index scan
    procedure TryCreateIndexScanConditionsFromNode(
                             Node:           TMYLDBExprNode;
                             IndexDefs:      TMYLDBIndexDefs;
                             ScanConditions: TMYLDBScanSearchConditionArray;
                             SessionID: TMYLDBSessionID);
    // remove extracted nodes (they included in list, but list contains more)
    procedure RemoveExtractedNodes(ScanConditions: TMYLDBScanSearchConditionArray;
                                   ScanConditionNo, ScanEndConditionNo: Integer);
    // create conditions for index scan
    procedure CreateIndexScanConditions(
                             IndexDefs:               TMYLDBIndexDefs;
                             ScanConditions:          TMYLDBScanSearchConditionArray;
                             SessionID:               TMYLDBSessionID);
    // try to add conditions on another index fields
    function ExtendMultiFieldIndexConditionFromNode(Node: TMYLDBExprNode;
       ScanCondition: TMYLDBScanSearchCondition; IndexDef: TMYLDBIndexDef): Boolean;
    // try to add conditions on another index fields
    function ExtendMultiFieldIndexCondition(ScanCondition: TMYLDBScanSearchCondition;
                                            IndexDef: TMYLDBIndexDef): Boolean;
    // add extracted node
    procedure AddNode(Node: TMYLDBExprNode);
    // add and move nodes from another expression: self => (self AND Expr2)
    procedure AddAndMoveNodesFromExpression(Expr: TMYLDBExpression);
    // check if agregate expr, const or only grouped fields are used
    function CanBeUsedInGroupBy(GroupByFields: String): Boolean;
   public
    property InMemory: Boolean read FInMemory write FInMemory;
  end;




  TMYLDBExprNode = class(TObject)
   private
    Children: TList;                  // Children nodes
    Operator: TMYLDBDataOperator;      // '<',  '>', AND, NOT, ...
    Value:  TMYLDBVariant;
    FCaseInsensitive: boolean;
    FPartialKey: boolean;
   private
    // converts some WideStrings to Strings
    procedure PatchWideStrings; virtual;
   public
    // constructors
    constructor Create(
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node1, Node2: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node1, Node2, Node3: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    // destroys
    destructor Destroy; override;
    // return Value
    function GetDataValue: TMYLDBVariant;  virtual; abstract;
    // calc subquery field values again
    procedure ResetSubqueryFieldValues; virtual;
    // Return subquery field value
    function GetSubqueryFieldValue(FieldNo: Integer): TMYLDBVariant; virtual;
    // is expression contains aggregated function
    function IsAggregated: Boolean; virtual;
    function IsAggregatedCountAll: Boolean; virtual;
    // Init for group function
    procedure Init; virtual;
    // Accumulate for group functions
    procedure Accumulate; virtual;
    procedure SetCountAll(RecordCount: Integer); virtual;

    // process assign AO
    procedure AssignAO(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); virtual;
    // process assign Cursor
    procedure AssignCursor(Cursor: TMYLDBCursor; ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); virtual;
    // process assign New Cursor Buffer
    procedure AssignCursorBuffer(Buffer: TMYLDBRecordBuffer; ApplyToParentQueryFields: Boolean); virtual;

    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; virtual;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; virtual;
    // return Data Precision
    function GetPrecision: Integer; virtual;

    // can be used by the AO?
    function CanBeAssigned(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor; ForHavingClause: Boolean = False): Boolean; virtual;

    // node is field?
    function CanUseNodeAsField(Node: TMYLDBExprNode): Boolean;
    // node is const or correlated field fomr parent query?
    function CanUseNodeAsConst(Node: TMYLDBExprNode): Boolean;
    // can use index for comparison?
    function CanUseIndex(IndexColumnNo: Integer; ConditionSearchOperator: TMYLDBSearchCondition; IndexDef: TMYLDBIndexDef; SessionID: TMYLDBSessionID = INVALID_SESSION_ID): Boolean; virtual;
  private
    // fill scan condition props
    procedure FillScanCondition(Value:         TMYLDBVariant;
                                Cursor:        TMYLDBCursor;
                                IndexColumnNo: Integer;
                                FieldNo:       Integer;
                                IndexDef:      TMYLDBIndexDef;
                                Expression:    TMYLDBExpression;
                                ScanCondition: TMYLDBScanSearchCondition);

  public
    // add index scan condition
    procedure AddAsIndexScanCondition(
                   ScanConditions: TMYLDBScanSearchConditionArray;
                   IndexDef:       TMYLDBIndexDef;
                   Expression:  TMYLDBExpression); virtual;
    // add checks to multi-field index scan condition
    procedure ExtendIndexScanCondition(
                   ScanCondition: TMYLDBScanSearchCondition;
                   IndexDef:      TMYLDBIndexDef;
                   Expression:    TMYLDBExpression); virtual;

    // is node a join condition?
    function IsJoinCondition(AO1, AO2: TMYLDBAO): Boolean; virtual;

    // replace pseudonyms to original names (f1 -> table1.field1)
    procedure ReplacePseudonyms(SelectList: array of TMYLDBSelectListItem); virtual;

    // check if agregate expr, const or only grouped fields are used
    function CanBeUsedInGroupBy(GroupByFields: String): Boolean; virtual;
  end;

  TMYLDBExprNodeConst = class(TMYLDBExprNode)
   public
    // return Value
    function GetDataValue: TMYLDBVariant; override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;

    function IsLikePatternEqualToPartialComparePattern: Boolean;
    procedure TruncateForPartialCompare;

    // can be used by the AO?
    function CanBeAssigned(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor; ForHavingClause: Boolean = False): Boolean; override;
    function IsUpperCase: Boolean;
    function IsLowerCase: Boolean;
  end;


  TMYLDBExprNodeField = class(TMYLDBExprNode)
   private
    LCursor:              TMYLDBCursor; // Cursor
    LAO:                  TMYLDBAO;
    LRecordBuffer:        PChar;
    FTableName:           string;  // table name - Table1 (Table1.Field1)
    FFieldName:           string;  // field name - Field1
    FFieldNameInAO:       string;  // field name - could be like Field1_1
    FFieldNo:             Integer;
    FFieldOffsetInBuffer: Integer;
    FFieldType:           TMYLDBAdvancedFieldType;
    FBaseFieldType:       TMYLDBBaseFieldType;
    FFieldSize:           Integer;
    FFieldPrecision:      Integer;
    FGetFromParent:       Boolean;
    FScanOnlyVisibleFieldsInAO: Boolean;
   public
    constructor Create(Cursor: TMYLDBCursor; FieldName: String; TableName: string = ''); overload;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // try to assign AO
    function TryAssignAO(AOToAssign: TMYLDBAO): Boolean;
    // process assign AO
    procedure AssignAO(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); override;
    // find field No in cursor
    function FindFieldInCursor(Cursor: TMYLDBCursor; var FieldNo: Integer): Boolean;
    // process assign Cursor and its buffer
    procedure AssignCursor(Cursor: TMYLDBCursor; ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); override;
    // process assign New Cursor Buffer
    procedure AssignCursorBuffer(Buffer: TMYLDBRecordBuffer; ApplyToParentQueryFields: Boolean); override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Data Size
    function GetDataSize: Integer;  override;

    // can be used by the AO?
    function CanBeAssigned(AO, ParentQueryAO: TMYLDBAO;
                           ParentCursor: TMYLDBCursor; ForHavingClause: Boolean = False): Boolean; override;
    // fills Field Item
    procedure FillItem(var Item: TMYLDBSelectListItem);
    // replace pseudonyms to original names (f1 -> table1.field1)
    procedure ReplacePseudonyms(SelectList: array of TMYLDBSelectListItem); override;
    // check if agregate expr, const or only grouped fields are used
    function CanBeUsedInGroupBy(GroupByFields: String): Boolean; override;

   public
    property TableName: String read FTableName;
    property FieldName: String read FFieldName;
    property FieldNo: Integer read FFieldNo;
    property FieldSize: Integer read FFieldSize;
    property BaseFieldType: TMYLDBBaseFieldType read FBaseFieldType;
  end;

  TMYLDBExprNodeComparison = class(TMYLDBExprNode)
   private
    F3ValueLogic: Boolean; // 3 Value Logic (TRUE, FALSE, NULL)
    FQuantifier: TMYLDBQuantifier;
   private
    procedure PatchWideStrings; override;
    // x > y
    function GetDataValueByCompare: TMYLDBVariant;
    // x > all (select ...)
    function GetDataValueByQuantifiedCompare: TMYLDBVariant;
   public
    // Constructor
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node1, Node2: TMYLDBExprNode;
                       TrueFalseNullLogic: boolean = true;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    // Constructor
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Quantifier: TMYLDBQuantifier;
                       Node1, Node2: TMYLDBExprNode;
                       TrueFalseNullLogic: boolean = true;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    procedure GetFieldAndConstNodes(var FieldNode: TMYLDBExprNodeField; var ConstNode: TMYLDBExprNodeConst);
    // can use index for comparison?
    function CanUseIndex(IndexColumnNo: Integer; ConditionSearchOperator: TMYLDBSearchCondition; IndexDef: TMYLDBIndexDef; SessionID: TMYLDBSessionID = INVALID_SESSION_ID): Boolean; override;
    // add index scan condition
    procedure AddAsIndexScanCondition(
                   ScanConditions: TMYLDBScanSearchConditionArray;
                   IndexDef:       TMYLDBIndexDef;
                   Expression:  TMYLDBExpression); override;
    // add checks to multi-field index scan condition
    procedure ExtendIndexScanCondition(
                   ScanCondition: TMYLDBScanSearchCondition;
                   IndexDef:      TMYLDBIndexDef;
                   Expression:    TMYLDBExpression); override;
    // is node a join condition?
    function IsJoinCondition(AO1, AO2: TMYLDBAO): Boolean; override;
  end;


  TMYLDBExprNodeBoolean = class(TMYLDBExprNode)
   private
    TempVal: TMYLDBVariant;
    EscapeStr: String;
   private
    // process a Like b
    procedure Like;
    // process IN (SELECT ...)
    procedure bInSubquery;
    // process IN (...)
    procedure bIn;
    // process A BETWEEN B AND C
    procedure Between;
    // process EXISTS
    procedure Exists;
   public
    constructor Create(
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false;
                       aEscapeStr: String=''
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                      ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node1, Node2: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    constructor Create(
                       Op: TMYLDBDataOperator;
                       Node1, Node2, Node3: TMYLDBExprNode;
                       CaseInsensitive: boolean = true;
                       PartialKey: boolean = false
                       ); overload;
    destructor Destroy; override;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // can use index for comparison?
    function CanUseIndex(ColumnNo: Integer; ConditionSearchOperator: TMYLDBSearchCondition;
     IndexDef: TMYLDBIndexDef; SessionID: TMYLDBSessionID = INVALID_SESSION_ID): Boolean; override;
    // add index scan condition
    procedure AddAsIndexScanCondition(
                   ScanConditions: TMYLDBScanSearchConditionArray;