unit MYLDBSQLProcessor;

{$I MYLDBVer.inc}

interface

uses Classes, SysUtils, Windows, DB,

     {$IFDEF DEBUG_LOG}
     MYLDBDebug,
     {$ENDIF}

{$IFNDEF D6H}
     MYLDBD4Routines,
{$ENDIF}

     MYLDBLexer,
     MYLDBBase,
     MYLDBRelationalAlgebra,
     MYLDBTypes,
     MYLDBExpressions,
     MYLDBVariant,
     MYLDBCompression,
     MYLDBConst,
     MYLDBConverts,
     MYLDBExcept;

 type
  TMYLDBSQLUnion = class;


////////////////////////////////////////////////////////////////////////////////
//
// TMYLDBTableReference
//
////////////////////////////////////////////////////////////////////////////////

  // reference to the "table" in FROM clause
  TMYLDBTableReference = class (TObject)
   public
    TableType:        TMYLDBTableType;      // 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:         TMYLDBJoinType;       // inner | left | ...
    LeftTable:        TMYLDBTableReference; // left table in join
    RightTable:       TMYLDBTableReference; // right table in join
    UsingFields:      TStringList;        // join column list
    SearchCondition:  TMYLDBExpression;     // ON '(t1.Field1 = t2.Field2)'
    SubQuery:         TMYLDBSQLUnion;       // subquery
    ExternalDBToFree: TObject;

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


////////////////////////////////////////////////////////////////////////////////
//
// TMYLDBSQLCommand
// base class for TMYLDBSQLSelect, TMYLDBSQLUpdate, ...
//
////////////////////////////////////////////////////////////////////////////////

   // base class for TMYLDBSQLSelect, TMYLDBSQLUpdate, ...
  TMYLDBSQLCommand = class (TObject)
   protected
    LLex:         TMYLDBLexer;   // 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: TMYLDBFields);

    // 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: TMYLDBLexer; Query: TDataSet);
    // parses query
    procedure Parse; virtual; abstract;
    // executes query
    procedure ExecSQL(
                      query: TDataset;
                      IsRoot,
                      RequestLive: boolean;
                      var ReadOnly: boolean;
                      ParentQueryAO: TMYLDBAO = nil;
                      ParentCursor: TMYLDBCursor = nil
                     ); virtual; abstract;
    // get result cursor
    function GetResultCursor: TMYLDBCursor; virtual;
    // gets result dataset
    function GetResultDataset: TDataset; virtual;
    // check pseudonym
    function CheckPseudonym(var Pseudonym: String): Boolean;
  end;//TMYLDBSQLCommand



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


 TMYLDBSQLCursorCommand = class (TMYLDBSQLCommand)
  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 TMYLDBSortSpecification;
   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: TMYLDBAO);

  public
   RootAO:                TMYLDBAO;   // top level AO

   // creates object
   constructor Create(Lexer: TMYLDBLexer; 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: TMYLDBAO = nil;
                      ParentCursor: TMYLDBCursor = nil
                    ); override;
   // check if correlated subquery apllied
   function AllConditionsApplied: Boolean; virtual; abstract;
   // builds AO tree
   function BuildAOTree(query: TDataset; RequestLive: Boolean; ParentQueryAO: TMYLDBAO;
    ParentCursor: TMYLDBCursor; AllowNotAppliedConditions: Boolean): TMYLDBAO; virtual; abstract;

   // get result cursor
   function GetResultCursor: TMYLDBCursor; override;
   // get result dataset
   function GetResultDataset: TDataset; override;
 end;//TMYLDBSQLCursorCommand



////////////////////////////////////////////////////////////////////////////////
//
// TMYLDBSQLProcessor
//
////////////////////////////////////////////////////////////////////////////////


  TMYLDBSQLProcessor = class (TObject)
   private
    MYLDBQuery:       TDataSet;
    FReadOnly:      Boolean;
    FRequestLive:   Boolean;
    FInMemory:      Boolean;
    FRowsAffected:  TMYLDBRecordNo;
    FLex:           TMYLDBLexer;

    FSqlText:       String;
    FSQLParams:     TMYLDBSQLParams;
    FCursor:        TMYLDBCursor;

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

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

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

    procedure ClearQueries;

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

    function OpenQuery: TMYLDBCursor;
    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: TMYLDBRecordNo read FRowsAffected;
    property SQLParams: TMYLDBSQLParams read FSQLParams;
  end; // TMYLDBSQLProcessor



////////////////////////////////////////////////////////////////////////////////
//
// TMYLDBSQLSelect
//
////////////////////////////////////////////////////////////////////////////////


 TMYLDBSQLSelect = class (TMYLDBSQLCursorCommand)
  protected
   Distinct:              Boolean; // ALL | DISTINCT
   TopRowCount:           Integer; // TOP (-1 | n)
   FirstRowNo:            Integer; // TOP row count [, first row]
   AllFields:             Boolean; // Select *
   SelectList:            array of TMYLDBSelectListItem; // fields list
   SelectListCount:       integer; // count of array elements
   FromTables:            array of TMYLDBTableReference; // From clause
   FromTablesCount:       integer; // count of array elements
   SearchCondition:       TMYLDBExpression;  // WHERE clause
   GroupByFields:         TMYLDBFields; // GROUP BY field1, f2, ...
   HavingCondition:       TMYLDBExpression;  // 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: TMYLDBExpression): Boolean;
   // USING <join columns>
   function ParseNamedColumnsJoin(var Fields: TStringList): Boolean;
   // CROSS JOIN | INNER JOIN | ...
   function ParseJoin(var tblRef: TMYLDBTableReference): Boolean;
   // Subquery: SELECT FROM (SELECT ...)
   function ParseFromSubquery(tblRef: TMYLDBTableReference): Boolean;
   // <table name> [ [ AS ] <correlation name> ...
   function ParseTableReference(tblRef: TMYLDBTableReference=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: TMYLDBTableReference): TMYLDBAO;
   // creates and adjusts joined table AO
   function CreateJoinedTableAO(var TableRef: TMYLDBTableReference;
                       ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor;
                       AllowNotAppliedConditions: Boolean): TMYLDBAO;
   // creates and adjusts subquery AO
   function CreateSubQueryAO(var TableRef: TMYLDBTableReference; RequestLive: Boolean;
                             ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor;
                             AllowNotAppliedConditions: Boolean): TMYLDBAO;
   // creates and adjusts AO
   function CreateAO(var TableRef: TMYLDBTableReference; RequestLive: Boolean;
          ParentQueryAO: TMYLDBAO;
          ParentCursor: TMYLDBCursor; AllowNotAppliedConditions: Boolean): TMYLDBAO;
   // builds one-table AO
   function BuildOneTableTree(RequestLive: Boolean; ParentQueryAO: TMYLDBAO;
          ParentCursor: TMYLDBCursor; AllowNotAppliedConditions: Boolean): TMYLDBAO;
   // builds multi-tables AO tree
   function BuildMultiTablesTree(RequestLive: Boolean; ParentQueryAO: TMYLDBAO;
          ParentCursor: TMYLDBCursor; AllowNotAppliedConditions: Boolean): TMYLDBAO;
  public
   // creates object
   constructor Create(Lexer: TMYLDBLexer; 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: TMYLDBAO;
                      ParentCursor: TMYLDBCursor; AllowNotAppliedConditions: Boolean): TMYLDBAO; override;
 end;//TMYLDBSQLSelect



////////////////////////////////////////////////////////////////////////////////
//
// TMYLDBQueryExprNode
//
////////////////////////////////////////////////////////////////////////////////

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

  // creates
  constructor Create; overload;
  // creates copy
  constructor Create(Src: TMYLDBQueryExprNode); overload;
  // destroys