xqyacc.y

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

                ;

expr_field : select_expr as_identifier
             { addColumn( $<string>1, false ); }
           | _IDENTIFIER _PERIOD _MULT
             { CurrentAnalizer.TableAllFields.Add( $<string>1 );
               CurrentAnalizer.AddFieldIfNot( $<string>1 + '.*' );
             }
           | RW_CAST _LPAREN select_expr RW_AS data_type _RPAREN as_identifier
             { AddColumn( $<string>3, False ); }
           ;

as_identifier : /* empty */
              | as_optional _IDENTIFIER
                { fAsAlias:= $<string>2;}
              ;

as_optional : /* empty */
            | RW_AS
            ;

select_expr  : define_field
             | define_constant
             | select_func
             | trim_function
             | extract_function
             | substring_function
             | case_function
             | subquery                        { $<string>$ := AddSubQueryInSelect; }

             | select_expr _PLUS select_expr   { $<string>$ := $<string>1 + ' + '  + $<string>3; }
             | select_expr _SUB select_expr    { $<string>$ := $<string>1 + ' - '  + $<string>3; }
             | select_expr _MULT select_expr   { $<string>$ := $<string>1 + ' * '  + $<string>3; }
             | select_expr _DIV select_expr    { $<string>$ := $<string>1 + ' / '  + $<string>3; }
             | select_expr _EXP select_expr    { $<string>$ := $<string>1 + ' ^ '  + $<string>3; }
             | select_expr RW_MOD select_expr  { $<string>$ := $<string>1 + ' MOD '  + $<string>3; }
             | select_expr RW_IDIV select_expr { $<string>$ := $<string>1 + ' DIV '  + $<string>3; }
             | select_expr RW_SHL select_expr  { $<string>$ := $<string>1 + ' SHL '  + $<string>3; }
             | select_expr RW_SHR select_expr  { $<string>$ := $<string>1 + ' SHR '  + $<string>3; }

             | select_expr _GT select_expr   { $<string>$ := $<string>1 + ' > '  + $<string>3; }
             | select_expr _LT select_expr   { $<string>$ := $<string>1 + ' < '  + $<string>3; }
             | select_expr _NEQ select_expr  { $<string>$ := $<string>1 + ' <> ' + $<string>3; }
             | select_expr _GE select_expr   { $<string>$ := $<string>1 + ' >= ' + $<string>3; }
             | select_expr _LE select_expr   { $<string>$ := $<string>1 + ' <= ' + $<string>3; }
             | select_expr _EQ select_expr   { $<string>$ := $<string>1 + ' = '  + $<string>3; }

             | select_expr RW_AND select_expr { $<string>$ := $<string>1 + ' AND ' + $<string>3; }
             | select_expr RW_OR  select_expr { $<string>$ := $<string>1 + ' AND ' + $<string>3; }

             | _SUB select_expr %prec UMINUS  { $<string>$ := $<string>1 + $<string>2;}
             | _PLUS select_expr %prec UMINUS { $<string>$ := $<string>1 + $<string>2;}

             | _LPAREN select_expr _RPAREN    { $<string>$ := $<string>1 + $<string>2 + $<string>3;}

               /* Aggregate functions follows */
             | RW_SUM _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akSUM, $<string>4); }
             | RW_MIN _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akMIN, $<string>4); }
             | RW_MAX _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akMAX, $<string>4); }
             | RW_AVG _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akAVG, $<string>4); }
             | RW_STDEV _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akSTDEV, $<string>4); }
             | RW_COUNT _LPAREN _MULT _RPAREN
               { $<string>$ := AddAggregate(akCOUNT, '0'); }
             | RW_COUNT _LPAREN distinct_aggr select_expr _RPAREN
               { $<string>$ := AddAggregate(akCOUNT, $<string>4); }
             ;

distinct_aggr : /* empty */
              | RW_DISTINCT  { Self.fIsDistinctAggr:= True; }
              ;

select_func : _IDENTIFIER _LPAREN _RPAREN
              { $<string>$ := $<string>1 + '()';}
            | _IDENTIFIER _LPAREN select_list_param _RPAREN
              { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4;}
            ;

select_list_param : _MULT
                  | list_param_expr
                  ;

list_param_expr : select_expr
                | list_param_expr _COMA select_expr
                  { $<string>$:= $<string>1 + ', ' + $<string>3; }
                ;

trim_function : RW_TRIM _LPAREN trim_position _STRING RW_FROM where_expr _RPAREN
                {
                  $<string>$ := Format('SQLTRIM(%s, %s, %d)',[$<string>4, $<string>6, fTrimPosition]);
                }
              ;

trim_position : RW_LEADING   {fTrimPosition := 0;}
              | RW_TRAILING  {fTrimPosition := 1;}
              | RW_BOTH      {fTrimPosition := 2;}
              ;

extract_function : RW_EXTRACT _LPAREN extract_field RW_FROM where_expr _RPAREN
       { case fExtractField of
           0: $<string>$ := Format('YEAR(%s)',  [$<string>5]);
           1: $<string>$ := Format('MONTH(%s)', [$<string>5]);
           2: $<string>$ := Format('DAY(%s)',   [$<string>5]);
           3: $<string>$ := Format('HOUR(%s)',  [$<string>5]);
           4: $<string>$ := Format('MIN(%s)',   [$<string>5]);
           5: $<string>$ := Format('SEC(%s)',   [$<string>5]);
         end;
       }
     ;

extract_field : RW_YEAR   { fExtractField:= 0; }
              | RW_MONTH  { fExtractField:= 1; }
              | RW_DAY    { fExtractField:= 2; }
              | RW_HOUR   { fExtractField:= 3; }
              | RW_MINUTE { fExtractField:= 4; }
              | RW_SECOND { fExtractField:= 5; }
              ;

substring_function : RW_SUBSTRING _LPAREN where_expr RW_FROM _UINTEGER for_length _RPAREN
                     { if Length(fForLength) > 0 then
                          $<string>$ := Format('COPY(%s,%s,%s)',[$<string>3,$<string>5,fForLength])
                       else
                          $<string>$ := Format('COPY(%s,%s,LENGTH(%s))',[$<string>3,$<string>5,$<string>3]);
                       fForLength := '';}
       ;

for_length : /* empty */
           | RW_FOR _UINTEGER {fForLength:= $<string>2;}
           ;

data_type : RW_CHAR _LPAREN _UINTEGER _RPAREN
            { fCastType := RW_CHAR;
              fCastLen  := StrToInt( $<string>3 ); }
          | RW_INTEGER                          { fCastType := RW_INTEGER; }
          | RW_BOOLEAN                          { fCastType := RW_BOOLEAN; }
          | RW_DATE                             { fCastType := RW_DATE; }
          | RW_TIME                             { fCastType := RW_TIME; }
          | RW_DATETIME                         { fCastType := RW_DATETIME; }
          | RW_FLOAT                            { fCastType := RW_FLOAT; }
          | RW_MONEY                            { fCastType := RW_MONEY; }
          ;

/* CASE...WHEN...THEN...ELSE...END clause */

case_function : start_case when_list else_clause RW_END
                { $<string>$ := $<string>1 + ' ' + fwhenlist + ' ' + $<string>3 + ' ' + $<string>4; }
              ;

start_case : RW_CASE
            { fwhenlist := ''; }
           ;

when_list : when_clause
          | when_list when_clause
          ;

when_clause : RW_WHEN select_expr RW_THEN when_constant
              { fwhenlist := fwhenlist + ' ' + $<string>1 + ' ' + $<string>2 + ' ' + $<string>3 + ' ' + $<string>4;
                $<string>$ := $<string>1 + ' ' + $<string>2 + ' ' + $<string>3 + ' ' + $<string>4;
              }
            ;

else_clause : /* empty */
            | RW_ELSE when_constant  { $<string>$ := $<string>1 + ' ' +$<string>2; }
            ;

when_constant : _UINTEGER
              | _SINTEGER
              | _NUMERIC
              | _STRING
              | RW_TRUE
              | RW_FALSE
              ;

define_field : _IDENTIFIER
               { $<string>$ := Format('\f"%s"',[$<string>1]);
                 CurrentAnalizer.AddFieldIfNot( $<string>1 );
               }
             | qualified_field
             ;

qualified_field : _IDENTIFIER _PERIOD _IDENTIFIER
                  { $<string>$ := Format('\f"%s.%s"',[$<string>1, $<string>3]);
                    CurrentAnalizer.AddFieldIfNot( $<string>1 + '.' + $<string>3);
                  }
                ;

define_param : _COLON _IDENTIFIER
               { $<string>$ := CurrentAnalizer.ReplaceParams( $<string>1 + $<string>2 ); }
             ;

/* FROM clause */
from_clause : RW_FROM list_tables
            | RW_FROM table_identifier list_join
            ;

list_tables : table_identifier
            | list_tables _COMA table_identifier
            ;

table_identifier : _IDENTIFIER
                   { AddTable($<string>1, '', False);}
                 | _IDENTIFIER _IDENTIFIER
                   { AddTable( $<string>1, $<string>2, False); }
                 | _STRING
                   { AddTable( $<string>1, '', true );}
                 | _STRING _IDENTIFIER
                   { AddTable($<string>1, $<string>2, True); }
                 | subquery _IDENTIFIER
                   { With CurrentAnalizer.TableList.Add Do
                     Begin
                       NumSubquery:= CurrentAnalizer.SubqueryList.Count-1;
                       TableName:= $<string>2;
                       Alias:= $<string>2;
                     End;
                   }
                 ;


/* Explicit JOINing */
list_join : list_join_table
          | list_join list_join_table
          ;

list_join_table : join_action table_identifier RW_ON join_expression
                  { fTempJoinOnItem.JoinExpression:= $<string>4;
                    AddJoin;
                  }
                ;

join_action : RW_JOIN                     { fTempJoinOnItem.JoinAction := jkLeftInnerJoin;      }
            | RW_INNER RW_JOIN            { fTempJoinOnItem.JoinAction := jkLeftInnerJoin;      }
            | RW_LEFT RW_INNER RW_JOIN    { fTempJoinOnItem.JoinAction := jkLeftInnerJoin;  }
            | RW_LEFT RW_JOIN             { fTempJoinOnItem.JoinAction := jkLeftInnerJoin;  }
            | RW_OUTER RW_JOIN            { fTempJoinOnItem.JoinAction := jkLeftOuterJoin;  }
            | RW_LEFT RW_OUTER RW_JOIN    { fTempJoinOnItem.JoinAction := jkLeftOuterJoin;  }
            | RW_RIGHT RW_INNER RW_JOIN   { fTempJoinOnItem.JoinAction := jkRightInnerJoin; }
            | RW_RIGHT RW_JOIN            { fTempJoinOnItem.JoinAction := jkRightInnerJoin; }
            | RW_RIGHT RW_OUTER RW_JOIN   { fTempJoinOnItem.JoinAction := jkRightOuterJoin; }
            | RW_FULL RW_OUTER RW_JOIN    { fTempJoinOnItem.JoinAction := jkFullOuterJoin;  }
            ;

join_expression : left_join_field _EQ right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }
                | left_join_field _NEQ right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }
                | left_join_field _GE right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }
                | left_join_field _LE right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }
                | left_join_field _GT right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }
                | left_join_field _LT right_join_field
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ;
                    SetJoinTestTbls( $<string>1, $<string>3 ); }

                | _LPAREN join_expression _RPAREN
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3 ; }
                | join_expression RW_AND join_expression
                  { $<string>$:= $<string>1 + #32 + $<string>2 + #32 + $<string>3 ; }
                | join_expression RW_OR join_expression
                  { $<string>$:= $<string>1 + #32 + $<string>2 + #32 + $<string>3 ; }
                | join_expression _PLUS join_expression
                  {$<string>$ := $<string>1 + ' + ' + $<string>3;}
                | join_expression _SUB join_expressionr
                  {$<string>$ := $<string>1 + ' - ' + $<string>3;}
                | join_expression _MULT join_expression
                  {$<string>$ := $<string>1 + ' * ' + $<string>3;}
                | join_expression _DIV join_expression
                  {$<string>$ := $<string>1 + ' / ' + $<string>3;}
                | _SUB join_expression %prec UMINUS
                  {$<string>$ := $<string>1 + $<string>2;}
                | _PLUS join_expression %prec UMINUS
                  {$<string>$ := $<string>1 + $<string>2;}
                | join_expression RW_NOT RW_LIKE _STRING escape_character
                  { if fEscapeChar = '' then fEscapeChar := #39#39;
                    $<string>$ := Format('SQLNOTLIKE(%s, %s, %s)',[$<string>1, $<string>4, fEscapeChar]);
                    fEscapeChar:= '';
                  }
                | join_expression RW_LIKE _STRING escape_character
                  { if fEscapeChar = '' then fEscapeChar := #39#39;
                    $<string>$ := Format('SQLLIKE(%s, %s, %s)',[$<string>1, $<string>3, fEscapeChar]);
                    fEscapeChar:= '';
                  }
                | join_expression in_operator _LPAREN in_predicate _RPAREN
                  { $<string>$ := CreateInListExpression( $<string>1 ); }
                | join_expression RW_BETWEEN define_constant RW_AND define_constant
                  { $<string>$ := Format('(%s >= %s) AND (%s <= %s)',
                    [$<string>1, $<string>3, $<string>1, $<string>5]); }
                | _IDENTIFIER _LPAREN _RPAREN
                  { $<string>$ := $<string>1 + '()'; }
                | _IDENTIFIER _LPAREN where_list_param _RPAREN
                  { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
                | RW_LEFT _LPAREN where_list_param _RPAREN
                  { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
                | RW_RIGHT _LPAREN where_list_param _RPAREN
                  { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
                ;

left_join_field : _IDENTIFIER _PERIOD _IDENTIFIER
                  { $<string>$:= $<string>1 + $<string>2 + $<string>3; }
                | _IDENTIFIER
                  { $<string>$:= '[dummy].' + $<string>1; }
                ;

right_join_field : _IDENTIFIER _PERIOD _IDENTIFIER
                   { $<string>$:= $<string>1 + $<string>2 + $<string>3; }
                 | _IDENTIFIER
                   { $<string>$:= '[dummy].' + $<string>1; }
                 ;

/* WHERE clause */
where_expr : define_field
           | define_constant
           | trim_function
           | extract_function
           | substring_function
           | is_null
           | _IDENTIFIER _LPAREN _RPAREN
             { $<string>$ := $<string>1 + '()'; }
           | _IDENTIFIER _LPAREN where_list_param _RPAREN
             { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
           | RW_LEFT _LPAREN where_list_param _RPAREN
             { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
           | RW_RIGHT _LPAREN where_list_param _RPAREN
             { $<string>$ := $<string>1 + $<string>2 + $<string>3 + $<string>4; }
           | where_expr RW_BETWEEN define_field RW_AND define_field
             { $<string>$ := Format('(%s >= %s) AND (%s <= %s)',
               [$<string>1, $<string>3, $<string>1, $<string>5]); }
           | where_expr RW_BETWEEN define_constant RW_AND define_constant
             { $<string>$ := Format('(%s >= %s) AND (%s <= %s)',
               [$<string>1, $<string>3, $<string>1, $<string>5]);
               AddWhereOptimize( $<string>1, $<string>3, $<string>5, ropBETWEEN ); }
           | where_expr in_operator _LPAREN in_predicate _RPAREN
             { $<string>$ := CreateInListExpression( $<string>1 ); }
           | where_expr in_operator anyall_subquery
             { if Pos('NOT',UpperCase($<string>2)) = 0 then
                 $<string>$ := $<string>1 + Format(' = (subquery %d)', [CurrentAnalizer.SubqueryList.Count-1])
              else
                 $<string>$ := $<string>1 + Format(' <> (subquery %d)', [CurrentAnalizer.SubqueryList.Count-1]);
             }
           | where_expr RW_LIKE _STRING escape_character
             { if fEscapeChar = '' then fEscapeChar := #39#39;
               $<string>$ := Format('SQLLIKE(%s, %s, %s)',[$<string>1, $<string>3, fEscapeChar]);
               fEscapeChar:= '';
             }
           | where_expr RW_NOT RW_LIKE _STRING escape_character
             { if fEscapeChar = '' then fEscapeChar := #39#39;
               $<string>$ := Format('SQLNOTLIKE(%s, %s, %s)',[$<string>1, $<string>4, fEscapeChar]);
               fEscapeChar:= '';
             }

           | _SUB where_expr %prec UMINUS     {$<string>$ := $<string>1 + $<string>2;}
           | _PLUS where_expr %prec UMINUS    {$<string>$ := $<string>1 + $<string>2;}

           | where_expr _PLUS where_expr    {$<string>$ := $<string>1 + ' + ' + $<string>3;}
           | where_expr _SUB where_expr     {$<string>$ := $<string>1 + ' - ' + $<string>3;}
           | where_expr _MULT where_expr    {$<string>$ := $<string>1 + ' * ' + $<string>3;}
           | where_expr _DIV where_expr     {$<string>$ := $<string>1 + ' / ' + $<string>3;}
           | where_expr _EXP where_expr     {$<string>$ := $<string>1 + ' ^ ' + $<string>3;}
           | where_expr RW_MOD where_expr   {$<string>$ := $<string>1 + ' MOD ' + $<string>3;}
           | where_expr RW_IDIV where_expr  {$<string>$ := $<string>1 + ' DIV ' + $<string>3;}
           | where_expr RW_SHL where_expr   {$<string>$ := $<string>1 + ' SHL ' + $<string>3;}
           | where_expr RW_SHR where_expr   {$<string>$ := $<string>1 + ' SHR ' + $<string>3;}

           | where_expr _EQ where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddJoinCandidate( $<string>1, $<string>3 );
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropBETWEEN);  }
           | where_expr _GE where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropGE); }
           | where_expr _LE where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropLE); }
           | where_expr _GT where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropGT); }
           | where_expr _LT where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropLT); }
           | where_expr _NEQ where_expr
             { $<string>$ := $<string>1 + #32 + $<string>2 + #32 + $<string>3;
               AddWhereOptimize( $<string>1, $<string>3, $<string>3, ropNEQ);   }
           | where_expr RW_AND where_expr
             { $<string>$ := $<string>1 + ' AND ' + $<string>3;  }
           | where_expr RW_OR where_expr