xqbase.pas

TxQuery is an SQL engine implemented in a TDataSet descendant component, that can parse SQL syntax,

Unit xqbase;

{*******************************************************}
{                                                       }
{       Base classes used in TxQuery dataset            }
{                                                       }
{       Copyright (c) 2002 Alfonso moreno               }
{                                                       }
{     Written by:                                       }
{       Alfonso Moreno                                  }
{       Hermosillo, Sonora, Mexico.                     }
{       Internet:  [e-mail]                   }
{                  [e-mail]                 }
{       http://www.sigmap.com/txquery.htm               }
{                                                       }
{*******************************************************}

{$I XQ_FLAG.INC}
Interface

Uses
  SysUtils, Windows, Classes, Dialogs, Db,
  xqmiscel, SparsArr, Qbaseexpr, QExprYacc
{$IFDEF WITHBDE}
  , DBTables, bde
{$ENDIF}
  ;

Const
  SQuote = ['''', '"'];
  NBoolean: Array[Boolean] Of String[5] = ( 'FALSE', 'TRUE' );

Type
  {-------------------------------------------------------------------------------}
  {                          Main exception                                       }
  {-------------------------------------------------------------------------------}

  ExQueryError = Class( Exception );

  {-------------------------------------------------------------------------------}
  {                          Some base types needed                               }
  {-------------------------------------------------------------------------------}

  PFloat = ^Double;
  PInteger = ^Integer;
  PWordBool = ^WordBool;

  {-------------------------------------------------------------------------------}
  {                          Forward declarations                                 }
  {-------------------------------------------------------------------------------}
  TCreateFields = Class;
  TColumnList = Class;
  TTableList = Class;
  TOrderByList = Class;
  TUpdateList = Class;
  TWhereOptimizeList = Class;
  TCreateTableList = Class;
  TInsertList = Class;
  TSrtFields = Class;
  TxqSortList = Class;
  TAggregateList = Class;
  TMemMapFile = Class;

  {-------------------------------------------------------------------------------}
  {                          Declare enumerations                                 }
  {-------------------------------------------------------------------------------}

  TRelOperator = ( roNone, roAnd, roOr );

  TAggregateKind = ( akSUM,
                     akAVG,
                     akSTDEV,
                     akMIN,
                     akMAX,
                     akCOUNT );

  TRelationalOperator = ( ropBETWEEN,
                          ropGT,
                          ropGE,
                          ropLT,
                          ropLE,
                          ropNEQ );

  TSubQueryKind = ( skAny,
                    skAll );

  TSQLStatement = ( ssSelect,
                    ssUpdate,
                    ssDelete,
                    ssInsert,
                    ssUnion,
                    ssCreateTable,
                    ssAlterTable,
                    ssCreateIndex,
                    ssDropTable,
                    ssDropIndex,
                    ssPackTable,
                    ssZapTable,
                    ssReindexTable );

  {TMemorizeJoin = ( mjNone,
                    mjUsingMemory,
                    mjUsingFile );  }

  {-------------------------------------------------------------------------------}
  {                  Defines TAggregateItem                                       }
  {-------------------------------------------------------------------------------}

  TAggregateItem = Class
  Private
    fAggregateList: TAggregateList; { belongs to }
    fAggregateStr: String; { the expression as it is issued in the SQL statement }
    fColIndex: Integer; { the index in the ColumnList where this aggregate is temporary evaluated }
    fAggregate: TAggregateKind; { used if ColumnKind = ckAggregate                          }
    fIsDistinctAg: Boolean; { syntax is SELECT COUNT(distinct pricelist) FROM customer }
    fSparseList: TAggSparseList; { a sparse array for aggregates values in every record }
  Public
    Constructor Create( AggregateList: TAggregateList );
    Destructor Destroy; Override;
    Property AggregateStr: String Read fAggregateStr Write fAggregateStr;
    Property ColIndex: Integer Read fColIndex Write fColIndex;
    Property Aggregate: TAggregateKind Read fAggregate Write fAggregate;
    Property IsDistinctAg: Boolean Read fIsDistinctAg Write fIsDistinctAg;
    Property SparseList: TAggSparseList Read fSparseList Write fSparseList;
  End;

  {-------------------------------------------------------------------------------}
  {                  Defines TAggregateList                                       }
  {-------------------------------------------------------------------------------}

  TAggregateList = Class
  Private
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TAggregateItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TAggregateItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Procedure Assign( AggregateList: TAggregateList );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TAggregateItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  SELECT section data                                          }
  {-------------------------------------------------------------------------------}

  TColumnItem = Class
  Private
    fColumnList: TColumnList; { the column list that this column item belongs to }
    fColumnExpr: String; { the expression in this column }
    fAsAlias: String; { column Name (used later in where, group by, etc), default is FieldName   }
    { also is used as title in browse                                          }
    fIsAsExplicit: Boolean; { explicity defined in SQL (ex.: SELECT Sales+Bonus As TotalSales FROM...) }
    fResolver: TExprParser; { object used to evaluate ColumnExpr                                       }
    fAutoFree: Boolean; { Auto free fResolver class                                                }
    fIsTemporaryCol: Boolean; { Is a column used for some calculations (temporarily)                     }
    fCastType: Word; { column must be casted to this type (CAST expr AS DATE)                   }
    fCastLen: Word; { only used if casting to CHAR(n) n=CastLen                                }
    fAggregateList: TAggregateList; { the list of aggregates for this column: SUM(expression) / SUM(expression) }
    fSubQueryList: TList;
      { the list of subqueries for this column (SELECT AMOUNTPAID FROM custno WHERE custno=1000) / (SELECT AMOUNTPAID FROM custno WHERE custno=2000) }
  Public
    Constructor Create( ColumnList: TColumnList );
    Destructor Destroy; Override;

    Property ColumnExpr: String Read fColumnExpr Write fColumnExpr;
    Property AsAlias: String Read fAsAlias Write fAsAlias;
    Property IsAsExplicit: Boolean Read fIsAsExplicit Write fIsAsExplicit;
    Property IsTemporaryCol: Boolean Read fIsTemporaryCol Write fIsTemporaryCol;
    Property CastType: Word Read fCastType Write fCastType;
    Property CastLen: Word Read fCastLen Write fCastLen;
    Property Resolver: TExprParser Read fResolver Write fResolver;
    Property AutoFree: Boolean Read fAutoFree Write fAutoFree;
    Property AggregateList: TAggregateList Read fAggregateList Write fAggregateList;
    Property SubqueryList: TList Read fSubQueryList Write fSubQueryList;
  End;

  TColumnList = Class
  Private
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TColumnItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TColumnItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Procedure DeleteAggregate( RecNo: Integer );
    Procedure SortAggregateWithList( SortList: TxqSortList );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TColumnItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  FROM section data                                            }
  {-------------------------------------------------------------------------------}

  TTableItem = Class
  Private
    fTableList: TTableList;
    fTableName: String;
    fAlias: String;
    fDataSet: TDataSet; { the attached dataset }
    fIsFullPath: Boolean;
    { for using in syntax like: SELECT * FROM subquery1, a, subquery2, ... etc}
    fNumSubquery: Integer;
  Public
    Constructor Create( TableList: TTableList );

    Property TableName: String Read fTableName Write fTableName;
    Property Alias: String Read fAlias Write fAlias;
    Property DataSet: TDataSet Read fDataSet Write fDataSet;
    Property IsFullPath: Boolean Read fIsFullPath Write fIsFullpath;
    Property NumSubquery: Integer read fNumSubquery write fNumSubquery;
  End;

  TTableList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TTableItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TTableItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Function IndexOfDataSet( DataSet: TDataSet ): Integer;
    Function IndexOfTableName( Const tableName: String ): Integer; // 1.56 fix
    Function IndexOfAlias( Const Alias: String ): Integer; // 1.56 fix

    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TTableItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {   ORDER BY section data - used in ORDER BY and GROUP BY                       }
  {-------------------------------------------------------------------------------}

  TOrderByItem = Class
  Private
    fOrderByList: TOrderByList;
    fColIndex: Integer;
    fAlias: String; { field name used to order                                   }
    fDesc: Boolean; { Descending? default = false = Ascending;                   }
  Public
    Constructor Create( OrderByList: TOrderByList );
    Property ColIndex: Integer Read fColIndex Write fColIndex;
    Property Alias: String Read fAlias Write fAlias;
    Property Desc: Boolean Read fDesc Write fDesc;
  End;

  TOrderByList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TOrderByItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TOrderByItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TOrderByItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  UPDATE statement section data                                }
  {-------------------------------------------------------------------------------}

  TUpdateItem = Class
  Private
    fUpdateList: TUpdateList;
    fColName: String;
    fColExpr: String;
    fResolver: TExprParser;
    fField: TField;
  Public
    Constructor Create( UpdateList: TUpdateList );
    Destructor Destroy; Override;

    Property ColName: String Read fColName Write fColName;
    Property ColExpr: String Read fColExpr Write fColExpr;
    Property Resolver: TExprParser Read fResolver Write fResolver;
    Property Field: TField Read fField Write fField;
  End;

  TUpdateList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TUpdateItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TUpdateItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TUpdateItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  WHERE optimization section data                              }
  {-------------------------------------------------------------------------------}

  TWhereOptimizeItem = Class
  Private
    fWhereOptimizeList: TWhereOptimizeList;
    fDataSet: TDataSet;
    fFieldNames: String;
    fRangeStart: String;
    fRangeEnd: String;
    fRelOperator: TRelationalOperator;
    fCanOptimize: Boolean; { Can optimize the result set generation with this config. }
  Public
    Constructor Create( WhereOptimizeList: TWhereOptimizeList );

    Property DataSet: TDataSet Read fDataSet Write fDataSet;
    Property FieldNames: String Read fFieldNames Write fFieldNames;
    Property RangeStart: String Read fRangeStart Write fRangeStart;
    Property RangeEnd: String Read fRangeEnd Write fRangeEnd;
    Property RelOperator: TRelationalOperator Read fRelOperator Write fRelOperator;
    Property CanOptimize: Boolean Read fCanOptimize Write fCanOptimize;
  End;

  TWhereOptimizeList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TWhereOptimizeItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Procedure Assign( OptimizeList: TWhereOptimizeList );
    Function Add: TWhereOptimizeItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TWhereOptimizeItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  CREATE TABLE section data                                    }
  {-------------------------------------------------------------------------------}

  TCreateField = Class
  Private
    fCreateFields: TCreateFields;
    fFieldName: String;
    fFieldType: Integer;
    fScale: Integer;
    fPrecision: Integer;
    fSize: Integer;
    fBlobType: Integer;
    fMustDrop: Boolean; // used only in DROP TABLE
  Public
    Constructor Create( CreateFields: TCreateFields );
    Property FieldName: String Read fFieldName Write fFieldName;
    Property FieldType: Integer Read fFieldType Write fFieldType;
    Property Scale: Integer Read fScale Write fScale;
    Property Precision: Integer Read fPrecision Write fPrecision;
    Property Size: Integer Read fSize Write fSize;
    Property BlobType: Integer Read fBlobType Write fBlobType;
    Property MustDrop: Boolean Read fMustDrop Write fMustDrop; // used only in DROP TABLE
  End;

  TCreateFields = Class
  Private
    fList: TList;
    Function Get( Index: Integer ): TCreateField;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Procedure Clear;
    Procedure AddField( Const AName: String; AFieldType, AScale, APrecision,
      ASize, ABlobType: Integer; AMustDrop: Boolean );
    Function Count: Integer;
    Property Items[Index: Integer]: TCreateField Read Get; Default;
  End;

  TCreateTableItem = Class
  Private
    fCreateTableList: TCreateTableList;
    fFields: TCreateFields;
    fTableName: String;
    fPrimaryKey: TStringList;
  Public
    Constructor Create( CreateTableList: TCreateTableList );
    Destructor Destroy; Override;
    Function FieldCount: Integer;
    Property Fields: TCreateFields Read fFields;
    Property TableName: String Read fTableName Write fTableName;
    Property PrimaryKey: TStringList Read fPrimaryKey;
  End;

  TCreateTableList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TCreateTableItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TCreateTableItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Property Count: Integer Read GetCount;
    Property Items[Index: Integer]: TCreateTableItem Read GetItem; Default;
  End;

  {-------------------------------------------------------------------------------}
  {                  INSERT INTO section data                                     }
  {-------------------------------------------------------------------------------}

  TInsertItem = Class
  Private
    fInsertList: TInsertList;
    fTableName: String;
    fIsFullPath: Boolean;
    fFieldNames: TStringList;
    fExprList: TStringList;
    fResolverList: TList;
    fDataSet: TDataSet;
  Public
    Constructor Create( InsertList: TInsertList );
    Destructor Destroy; Override;

    Property TableName: String Read fTableName Write fTableName;
    Property IsFullPath: Boolean Read fIsFullPath Write fIsFullPath;
    Property FieldNames: TStringList Read fFieldNames;
    Property ExprList: TStringList Read fExprList;
    Property DataSet: TDataSet Read fDataSet Write fDataSet;
    Property ResolverList: TList Read fResolverList;
  End;

  TInsertList = Class
    fItems: TList;
    Function GetCount: Integer;
    Function GetItem( Index: Integer ): TInsertItem;
  Public
    Constructor Create;
    Destructor Destroy; Override;
    Function Add: TInsertItem;
    Procedure Clear;
    Procedure Delete( Index: Integer );
    Property Count: Integer Read GetCount;