abssqlprocessor.pas

Absolute Database 是来替代BDE[Borland数据库引擎]的用于Delphi 和 C++ Builder 开发用的数据库引擎. 它小巧, 高速, 健壮, 易于使用. 它能直接编译进

unit ABSSQLProcessor;

{$I ABSVer.inc}

interface

uses Classes, SysUtils, Windows, DB,

     {$IFDEF DEBUG_LOG}
     ABSDebug,
     {$ENDIF}

{$IFNDEF D6H}
     ABSD4Routines,
{$ENDIF}

     ABSLexer,
     ABSBase,
     ABSRelationalAlgebra,
     ABSTypes,
     ABSExpressions,
     ABSVariant,
     ABSCompression,
     ABSConst,
     ABSConverts,
     ABSExcept;

 type
  TABSSQLUnion = class;


////////////////////////////////////////////////////////////////////////////////
//
// TABSTableReference
//
////////////////////////////////////////////////////////////////////////////////

  // reference to the "table" in FROM clause
  TABSTableReference = class (TObject)
   public
    TableType:        TABSTableType;      // Table | JoinedTable | SubQuery
    DatabaseName:     string;             // database1
    TableName:        string;             // table1
    Pseudonym:        string;             // table1 as t1
    Password:         string;             // table1 PASSWORD 'password'
    InMemory:         Boolean;            // [MEMORY]
    NaturalJoin:      Boolean;            // Natural join?
    JoinType:         TABSJoinType;       // inner | left | ...
    LeftTable:        TABSTableReference; // left table in join
    RightTable:       TABSTableReference; // right table in join
    UsingFields:      TStringList;        // join column list
    SearchCondition:  TABSExpression;     // ON '(t1.Field1 = t2.Field2)'
    SubQuery:         TABSSQLUnion;       // subquery
    ExternalDBToFree: TObject;

    // creates
    constructor Create;
    // destroys
    destructor Destroy; override;
    // makes join (left and right node-table)
    procedure MakeJoin(RightNode: TABSTableReference; JType: TABSJoinType;
                     IsNatural: Boolean; Fields: TStringList;
                     OnCondition: TABSExpression);
    // makes subquery
    procedure MakeSubQuery(Lexer: TABSLexer; Query: TDataSet);
  end;//TABSTableReference


////////////////////////////////////////////////////////////////////////////////
//
// TABSSQLCommand
// base class for TABSSQLSelect, TABSSQLUpdate, ...
//
////////////////////////////////////////////////////////////////////////////////

   // base class for TABSSQLSelect, TABSSQLUpdate, ...
  TABSSQLCommand = class (TObject)
   protected
    LLex:         TABSLexer;   // lexer with expression to parse
    Token:        TToken;      // current token
    LQuery:       TDataSet;
    RowsAffected: Integer;
    IntoMemory:   Boolean;     // into memory table?
   private
    // parses list of columns (without table name): field1, field2, ...
    procedure ParseColumnList(var Fields: TStringList);
    // parses list of fields:  table.field1, field2, ..
    procedure ParseFieldList(var Fields: TABSFields);

    // get current token
    function GetCurrentToken: Boolean;
    // get next token
    function GetNextToken: Boolean; overload;
    // get token and lokks at next token with check for token type restrictions
    function GetNextToken(PermittedTypes: TTokenTypes;
                          NativeErrorCode: integer;
                          ErrorText: String): Boolean; overload;

   protected
    procedure ParseTableNameToken(var DatabaseFileName, TableName, TableAlias, Password: String; var InMemory: Boolean);
    procedure CreateDbAndTableComponents(
                          Query: TDataSet;
                          DatabaseFileName, TableName, Password: String;
                          InMemory: Boolean;
                          var Table: TDataset;
                          var DB: TObject);
    procedure FreeDbAndTableComponents(Table: TDataset; DB: TObject);

   public
    // creates object
    constructor Create(Lexer: TABSLexer; Query: TDataSet);
    // parses query
    procedure Parse; virtual; abstract;
    // executes query
    procedure ExecSQL(
                      query: TDataset;
                      IsRoot,
                      RequestLive: boolean;
                      var ReadOnly: boolean;
                      ParentQueryAO: TABSAO = nil;
                      ParentCursor: TABSCursor = nil
                     ); virtual; abstract;
    // get result cursor
    function GetResultCursor: TABSCursor; virtual;
    // gets result dataset
    function GetResultDataset: TDataset; virtual;
    // check pseudonym
    function CheckPseudonym(var Pseudonym: String): Boolean;
  end;//TABSSQLCommand



////////////////////////////////////////////////////////////////////////////////
//
// TABSSQLCursorCommand
// base class for SQL command with cursor
//
////////////////////////////////////////////////////////////////////////////////


 TABSSQLCursorCommand = class (TABSSQLCommand)
  protected
   ResultDataset:         TDataset;  // query component
   IntoDatabase:          string;    // select into <database>.<table>
   IntoTable:             string;    // select into <database>.<table>
   IntoImmediate:         Boolean;   // select into direct memory table?
   OrderBySpecs:          array of TABSSortSpecification;
   OrderBySpecsCount:     integer;

   function OpenAndLockTablesBeforeSelect: Boolean;
   function UnlockTablesAfterSelect: Boolean;

   // <sort key> [ <collate clause> ] [ <ordering specification> ]
   function ParseSortSpecification: Boolean;
   // ORDER BY
   function ParseOrderByClause: Boolean;
   // applies Order By clause
   procedure ApplyOrderBy(AO: TABSAO);

  public
   RootAO:                TABSAO;   // top level AO
   
   // creates object
   constructor Create(Lexer: TABSLexer; Query: TDataSet);
   // destroys object
   destructor Destroy; override;
   // executes query
   //procedure ExecSQL(query: TDataset; IsRoot: Boolean = False); override;
   procedure ExecSQL(
                      query: TDataset;
                      IsRoot,
                      RequestLive: boolean;
                      var ReadOnly: boolean;
                      ParentQueryAO: TABSAO = nil;
                      ParentCursor: TABSCursor = nil
                    ); override;
   // check if correlated subquery apllied
   function AllConditionsApplied: Boolean; virtual; abstract;
   // builds AO tree
   function BuildAOTree(query: TDataset; RequestLive: Boolean; ParentQueryAO: TABSAO;
    ParentCursor: TABSCursor; AllowNotAppliedConditions: Boolean): TABSAO; virtual; abstract;

   // get result cursor
   function GetResultCursor: TABSCursor; override;
   // get result dataset
   function GetResultDataset: TDataset; override;
 end;//TABSSQLCursorCommand



////////////////////////////////////////////////////////////////////////////////
//
// TABSSQLProcessor
//
////////////////////////////////////////////////////////////////////////////////


  TABSSQLProcessor = class (TObject)
   private
    ABSQuery:       TDataSet;
    FReadOnly:      Boolean;
    FRequestLive:   Boolean;
    FInMemory:      Boolean;
    FRowsAffected:  TABSRecordNo;
    FLex:           TABSLexer;

    FSqlText:       String;
    FSQLParams:     TABSSQLParams;
    FCursor:        TABSCursor;

    Queries:        array of TABSSQLCommand; // objects fullfilled by parser
   private

    // parse SQL script and fullfill SQL command objects
    function ParseNextCommand(Lexer: TABSLexer): Boolean;

    // add SELECT query object
    procedure AddSelectQuery(Lexer: TABSLexer);
    // add INSERT query object
    procedure AddInsertQuery(Lexer: TABSLexer);
    // add UPDATE query object
    procedure AddUpdateQuery(Lexer: TABSLexer);
    // add DELETE query object
    procedure AddDeleteQuery(Lexer: TABSLexer);
    // DDL:
    // add TRUNCATE TABLE
    procedure AddTruncateTableQuery(Lexer: TABSLexer);
    // add CREATE TABLE query object
    procedure AddCreateTableQuery(Lexer: TABSLexer);
    // add DROP TABLE query object
    procedure AddDropTableQuery(Lexer: TABSLexer);
    // add ALTER TABLE
    procedure AddAlterTableQuery(Lexer: TABSLexer);
    // add RENAME TABLE
    procedure AddRenameTableQuery(Lexer: TABSLexer);
    // add CREATE INDEX
    procedure AddCreateIndex(Lexer: TABSLexer);
    // add DROP INDEX
    procedure AddDropIndexQuery(Lexer: TABSLexer);
    // add START TRANSACTION
    procedure AddStartTransaction(Lexer: TABSLexer);
    // add COMMIT
    procedure AddCommit(Lexer: TABSLexer);
    // add ROLLBACK
    procedure AddRollback(Lexer: TABSLexer);

    procedure ClearQueries;

   public
    constructor Create(Query: TDataSet);
    destructor Destroy; override;

    function OpenQuery: TABSCursor;
    procedure ExecuteQuery;

    procedure PrepareStatement(SQLText: PChar);

   public
    property ReadOnly: Boolean read FReadOnly write FReadOnly;
    property RequestLive: Boolean read FRequestLive write FRequestLive;
    property InMemory: Boolean read FInMemory write FInMemory;
    property RowsAffected: TABSRecordNo read FRowsAffected;
    property SQLParams: TABSSQLParams read FSQLParams;
  end; // TABSSQLProcessor



////////////////////////////////////////////////////////////////////////////////
//
// TABSSQLSelect
//
////////////////////////////////////////////////////////////////////////////////


 TABSSQLSelect = class (TABSSQLCursorCommand)
  protected
   Distinct:              Boolean; // ALL | DISTINCT
   TopRowCount:           Integer; // TOP (-1 | n)
   FirstRowNo:            Integer; // TOP row count [, first row]
   AllFields:             Boolean; // Select *
   SelectList:            array of TABSSelectListItem; // fields list
   SelectListCount:       integer; // count of array elements
   FromTables:            array of TABSTableReference; // From clause
   FromTablesCount:       integer; // count of array elements
   SearchCondition:       TABSExpression;  // WHERE clause
   GroupByFields:         TABSFields; // GROUP BY field1, f2, ...
   HavingCondition:       TABSExpression;  // HAVING clause
   FSubqueryHasNotAppliedConditions: Boolean;

   // parse SELECT token
   function ParseSelectToken: Boolean;
   // DISTINCT | ALL ?
   function ParseSetQuantifier: Boolean;
   // TOP n ?
   function ParseTopOperator: Boolean;
   function DuplicatedFieldExists(const TableName, FieldName: String): Boolean;
   function FieldExists(const TableName, FieldName: String): Boolean;
   function GetFieldNameForDuplicatedField(const TableName, FieldName: String): String;
   // table1.* | Table1.Field1 | Fields1 AS F1
   function ParseSelectSubList: Boolean;
   // * | <select sublist>
   function ParseSelectList: Boolean;
   // INTO <target>
   function ParseInto: Boolean;
   // ON <join condition>
   function ParseJoinCondition(var SearchCondition: TABSExpression): Boolean;
   // USING <join columns>
   function ParseNamedColumnsJoin(var Fields: TStringList): Boolean;
   // CROSS JOIN | INNER JOIN | ...
   function ParseJoin(var tblRef: TABSTableReference): Boolean;
   // Subquery: SELECT FROM (SELECT ...)
   function ParseFromSubquery(tblRef: TABSTableReference): Boolean;
   // <table name> [ [ AS ] <correlation name> ...
   function ParseTableReference(tblRef: TABSTableReference=nil): Boolean;
   // FROM ...
   function ParseFromClause: Boolean;
   // WHERE ...
   function ParseWhereClause: Boolean;
   // GROUP BY ...
   function ParseGroupByClause: Boolean;
   // HAVING ...
   function ParseHavingClause: Boolean;

   // gets default database name
   function GetDefaultDatabaseName: string;
   // gets DisableTempFiles value
   function GetDisableTempFiles: boolean;
   // creates and adjusts table AO
   function CreateTableAO(var TableRef: TABSTableReference): TABSAO;
   // creates and adjusts joined table AO
   function CreateJoinedTableAO(var TableRef: TABSTableReference;
                       ParentQueryAO: TABSAO; ParentCursor: TABSCursor;
                       AllowNotAppliedConditions: Boolean): TABSAO;
   // creates and adjusts subquery AO
   function CreateSubQueryAO(var TableRef: TABSTableReference; RequestLive: Boolean;
                             ParentQueryAO: TABSAO; ParentCursor: TABSCursor;
                             AllowNotAppliedConditions: Boolean): TABSAO;
   // creates and adjusts AO
   function CreateAO(var TableRef: TABSTableReference; RequestLive: Boolean;
          ParentQueryAO: TABSAO;
          ParentCursor: TABSCursor; AllowNotAppliedConditions: Boolean): TABSAO;
   // builds one-table AO
   function BuildOneTableTree(RequestLive: Boolean; ParentQueryAO: TABSAO;
          ParentCursor: TABSCursor; AllowNotAppliedConditions: Boolean): TABSAO;
   // builds multi-tables AO tree
   function BuildMultiTablesTree(RequestLive: Boolean; ParentQueryAO: TABSAO;
          ParentCursor: TABSCursor; AllowNotAppliedConditions: Boolean): TABSAO;
  public
   // creates object
   constructor Create(Lexer: TABSLexer; Query: TDataSet);
   // destroys object
   destructor Destroy; override;
   // parses query
   procedure Parse; override;
   // check if correlated subquery apllied
   function AllConditionsApplied: Boolean; override;
   // builds AO tree
   function BuildAOTree(query: TDataset; RequestLive: Boolean; ParentQueryAO: TABSAO;
                      ParentCursor: TABSCursor; AllowNotAppliedConditions: Boolean): TABSAO; override;
 end;//TABSSQLSelect



////////////////////////////////////////////////////////////////////////////////
//
// TABSQueryExprNode
//
////////////////////////////////////////////////////////////////////////////////

 // select | union | except | ...
 TABSQueryExprNode = class (TObject)
  NodeType:         TABSQueryExprType;  // node is: select | union | except | ...
  Left:             TABSQueryExprNode;  // left node in union, except, ...
  Right:            TABSQueryExprNode;  // right table in union, except, ...
  All:              Boolean;            // [ALL] specified?
  Corresponding:    Boolean;            // [CORRESPONDING] specified?
  CorrespondingFields: TStringList;     // column list
  SelectCommand:    TABSSQLSelect;      // SELECT command (if NodeType is select)

  // creates
  constructor Create; overload;
  // creates copy
  constructor Create(Src: TABSQueryExprNode); overload;
  // destroys
  destructor Destroy; override;
  // adds new node to the tree
  procedure AddNode(NewType: TABSQueryExprType; RightNode: TABSQueryExprNode;
                    bAll, bCorresponding: Boolean; ColumnList: TStringList=nil);