[dbsrgits/SQL-Translator.git] / share / Grammar / DB2.gra


{
    my ( %tables, $table_order, @table_comments, @views, @triggers );
}

#
# The "eofile" rule makes the parser fail if any "statement" rule
# fails.  Otherwise, the first successful match by a "statement"
# won't cause the failure needed to know that the parse, as a whole,
# failed. -ky
#
startrule : statement(s) eofile {
    $return      = {
        tables   => \%tables,
        views    => \@views,
        triggers => \@triggers,
    }
}

eofile : /^\Z/

statement : 
    comment
    | create
    | <error>

comment : /^\s*-{2}.*\n/
    {
        my $comment =  $item[1];
        $comment    =~ s/^\s*(-{2})\s*//;
        $comment    =~ s/\s*$//;
        $return     = $comment;
    }


create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
{
    my $table_name = $item{'table_name'}{'name'};
    $return =  {
        table      => $table_name,
        schema     => $item{'trigger_name'}{'schema'},
        name       => $item{'trigger_name'}{'name'},
        when       => 'before',
        db_event   => $item{'type'}->{'event'},
        fields     => $item{'type'}{'fields'},
        condition  => $item{'triggered_action'}{'condition'},
        reference  => $item{'reference_b'},
        granularity => $item[9],
        action     => $item{'triggered_action'}{'statement'}
    };

    push @triggers, $return;
}

create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
{
    my $table_name = $item{'table_name'}{'name'};
    $return = {
        table      => $table_name,
        schema     => $item{'trigger_name'}{'schema'},
        name       => $item{'trigger_name'}{'name'},
        when       => 'after',
        db_event   => $item{'type'}{'event'},
        fields     => $item{'type'}{'fields'},
        condition  => $item{'triggered_action'}{'condition'},
        reference  => $item{'reference_a'},
        granularity => $item[9],
        action     => $item{'triggered_action'}{'statement'}
    };

    push @triggers, $return;
}

create: CREATE /FEDERATED|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
{
    $return = {
        name   => $item{view_name}{name},
        sql    => $item{SQL_procedure_statement},
        with   => $item{'with_expression(?)'},
        fields => $item{'column_list(?)'}
    };
    push @views, $return;
}

# create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)

# col_list_or_of: column_list | /OF/i ( root_view_definition | subview_definition )

with_expression: /WITH/i common_table_expression(s /,/)
{
    $return = $item{'common_table_expression'};
}

SQL_procedure_statement: /[^;]*/ /(;|\z)/ { $return = $item[1] . $item[2] }

column_list: '(' column_name(s /,/) ')'
{
    $return = join(' ', '(', @{$item[2]}, ')');
}

CREATE: /create/i

TRIGGER: /trigger/i

VIEW: /view/i

INNER: /inner/i

LEFT: /left/i

RIGHT: /right/i

FULL: /full/i

OUTER: /outer/i

WHERE: /where/i
 
trigger_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } } 

table_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } } 

view_name: SCHEMA '.' NAME
    { $return = { schema => $item[1], name => $item[3] } }
    | NAME
    { $return = { name => $item[1] } } 

column_name: NAME

identifier: NAME

correlation_name: NAME

numeric_constant: /\d+/

SCHEMA: /\w+/

SCHEMA: /\w{1,128}/

NAME: /\w+/

NAME: /\w{1,18}/
 
options: /WITH/i ( /CASCADED/i | /LOCAL/i ) /CHECK\s+OPTION/i

# root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'

# subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)

# oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)

# with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name | typed_view_name ) | /READ\s+ONLY/i )(s /,/) )(s /,/)

# under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i

common_table_expression: table_name column_list /AS/i get_bracketed
{
    $return = { name  => $item{table_name}{name}, 
                query => $item[4]
                };
}

get_bracketed:  
{ 
    extract_bracketed($text, '(');
}

common_table_expression: table_name column_list /AS/i '(' fullselect ')'

# fullselect: ( subselect | '(' fullselect ')' | values_clause ) ( ( /UNION/i | /UNION/i /ALL/i | /EXCEPT/i | /EXCEPT/i /ALL/i | /INTERSECT/i | /INTERSECT/i /ALL/i ) ( subselect | '(' fullselect ')' | values_clause ) )(s)

# values_clause: /VALUES/i values_row(s /,/) 

# values_row: ( expression | /NULL/i ) | '(' ( expression | /NULL/i )(s /,/) ')'

# subselect:  select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)

# select_clause: SELECT ( /ALL/i | /DISTINCT )(?) ( '*' | ( expression ( /AS|/i new_column_name )(?) | exposed_name '.*' )(s /,/) )

# from_clause: /FROM/i table_name(s /,/)

# from_clause: /FROM/i table_reference(s /,/)

# table_reference:  
#     ( 
#       ( nickname 
#       | table_name 
#       | view_name 
#       ) 
#     | ( /ONLY/i
#       | /OUTER/i 
#       ) '(' 
#       ( table_name 
#       | view_name 
#       ) ')' 
#     ) correlation_clause(?) 
#   | TABLE '(' function_name '(' expression(s? /,/) ')' ')'  correlation_clause 
#   | TABLE(?) '(' fullselect ')' correlation_clause 
#   | joined_table 
  

# correlation_clause: /AS/i(?) correlation_name column_list(?)

# joined_table: 
#    table_reference ( INNER 
#                     | outer 
#                     )(?) JOIN table_reference ON join_condition
#   | '(' joined_table ')'
  
# outer: ( LEFT | RIGHT | FULL ) OUTER(?)

where_clause: WHERE search_condition

# group_by_clause: /GROUP\s+BY/i ( grouping_expression 
#                                | grouping_sets
#                                | super_groups
#                                )(s /,/)

# grouping_expression: expression

# orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i | /DESC/i)(?) )(s /,/)

# sort_key: simple_column_name | simple_integer | sort_key_expression

# # Name of one of the selected columns!
# simple_column_name: NAME

# simple_integer: /\d+/ 
#   { $item[1] <= $numberofcolumns && $item[1] > 1 }

# sort_key_expression: expression
#   { expression from select columns list, grouping_expression, column function.. }

# grouping_sets: /GROUPING\s+SETS/i '(' ( 
#                                         ( grouping_expression 
#                                         | super_groups 
#                                         ) 
#                                       | '(' ( grouping_expression 
#                                             | super_groups 
#                                             )(s /,/) ')' 
#                                       )(s /,/) ')' 

# super_groups: /ROLLUP/i '(' grouping_expression_list ')' 
#            | /CUBE/i '(' grouping_expression_list ')'
#            | grand_total

# grouping_expression_list:  ( grouping_expression 
#                            | '(' grouping_expression(s /,/) ')' 
#                            )(s /,/)

# grand_total: '(' ')'

# having_clause: /HAVING/i search_condition

when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}

triggered_action: when_clause(?) SQL_procedure_statement
{ $return = { 'condition' => $item[1][0],
              'statement' => $item{'SQL_procedure_statement'} };
}

before: /NO CASCADE BEFORE/i

after: /AFTER/i

type: /UPDATE/i /OF/i column_name(s /,/) 
{ $return = { event  => 'update_on',
              fields => $item[3] }
}

type: ( /INSERT/i | /DELETE/i | /UPDATE/i )
{ $return = { event => $item[1] } }

reference_b: /REFERENCING/i old_new_corr(0..2)
{ $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }

reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2) 
{ $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]})  ) }

old_new_corr: /OLD/i /(AS)?/i correlation_name 
{ $return = join(' ', @item[1..3] ) }
| /NEW/i /(AS)?/i correlation_name
{ $return = join(' ', @item[1..3] ) }

old_new_table: /OLD_TABLE/i /(AS)?/i identifier
{ $return = join(' ', @item[1..3] ) }
| /NEW_TABLE/i /(AS)?/i identifier
{ $return = join(' ', @item[1..3] ) }

# Just parsing simple search conditions for now.
search_condition: /[^)]+/

expression: ( 
              ( '+' 
              | '-' 
              )(?) 
              ( function
              | '(' expression ')'
              | constant
              | column_name
              | host_variable
              | special_register
              | '(' scalar_fullselect ')' 
              | labeled_duration
              | case_expression
              | cast_specification
#              | dereference_operation
              | OLAP_function
              | method_invocation
              | subtype_treatment
              | sequence_reference 
              )
            )(s /operator/)

operator: ( /CONCAT/i | '||' ) | '/' | '*' | '+' | '-' 

function: ( /SYSIBM\.|/i sysibm_function 
          | /SYSFUN\.|/i sysfun_function
          | userdefined_function 
          ) '(' func_args(s /,/)  ')'

constant: int_const | float_const | dec_const | char_const | hex_const | grastr_const

func_args: expression

sysibm_function: ( /ABS/i | /ABSVAL/i )
                | /AVG/i
                | /BIGINT/i
                | /BLOB/i
                | /CHAR/i
                | /CLOB/i
                | /COALESCE/i
                | ( /CONCAT/ | '||' )
                | ( /CORRELATION/i | /CORR/ )
                | /COUNT/i
                | /COUNT_BIG/i
                | (/COVARIANCE/i | /COVAR/i )
                | /DATE/i
                | /DAY/i
                | /DAYS/i
                | /DBCLOB/i
                | ( /DECIMAL/i | /DEC/i )
                | /DECRYPT_BIN/i
                | /DECRYPT_CHAR/i
                | /DEREF/i
                | /DIGITS/i
                | /DLCOMMENT/i
                | /DLLINKTYPE/i
                | /DLURLCOMPLETE/i
                | /DLURLPATH/i
                | /DLURLPATHONLY/i
                | /DLURLSCHEME/i
                | /DLURLSERVER/i
                | /DLVALUE/i
                | ( /DOUBLE/i | /DOUBLE_PRECISION/i )
                | /ENCRYPT/i
                | /EVENT_MON_STATE/i
                | /FLOAT/i
                | /GETHINT/i
                | /GENERATE_UNIQUE/i
                | /GRAPHIC/i
                | /GROUPING/i
                | /HEX/i
                | /HOUR/i
                | /IDENTITY_VAL_LOCAL/i
                | ( /INTEGER/i | /INT/ )
                | ( /LCASE/i | /LOWER/ )
                | /LENGTH/i
                | /LONG_VARCHAR/i
                | /LONG_VARGRAPHIC/i
                | /LTRIM/i
                | /MAX/i
                | /MICROSECOND/i
                | /MIN/i
                | /MINUTE/i
                | /MONTH/i
                | /MULTIPLY_ACT/i
                | /NODENUMBER/i
                | /NULLIF/i
                | /PARTITON/i
                | /POSSTR/i
                | /RAISE_ERROR/i
                | /REAL/i
                | /REC2XML/i
                | /REGR_AVGX/i
                | /REGR_AVGY/i
                | /REGR_COUNT/i
                | ( /REGR_INTERCEPT/i | /REGR_ICPT/i )
                | /REGR_R2/i
                | /REGR_SLOPE/i
                | /REGR_SXX/i
                | /REGR_SXY/i
                | /REGR_SYY/i
                | /RTRIM/i
                | /SECOND/i
                | /SMALLINT/i
                | /STDDEV/i
                | /SUBSTR/i
                | /SUM/i
                | /TABLE_NAME/i
                | /TABLE_SCHEMA/i
                | /TIME/i
                | /TIMESTAMP/i
                | /TRANSLATE/i
                | /TYPE_ID/i
                | /TYPE_NAME/i
                | /TYPE_SCHEMA/i
                | ( /UCASE/i | /UPPER/i )
                | /VALUE/i
                | /VARCHAR/i
                | /VARGRAPHIC/i
                | ( /VARIANCE/i | /VAR/i )
                | /YEAR/i

sysfun: ( /ABS/i | /ABSVAL/i )
                | /ACOS/i
                | /ASCII/i
                | /ASIN/i
                | /ATAN/i
                | /ATAN2/i
                | ( /CEIL/i | /CEILING/i )
                | /CHAR/i
                | /CHR/i
                | /COS/i
                | /COT/i
                | /DAYNAME/i
                | /DAYOFWEEK/i
                | /DAYOFWEEK_ISO/i
                | /DAYOFYEAR/i
                | /DEGREES/i
                | /DIFFERENCE/i
                | /DOUBLE/i
                | /EXP/i
                | /FLOOR/i
                | /GET_ROUTINE_SAR/i
                | /INSERT/i
                | /JULIAN_DAY/i
                | /LCASE/i
                | /LEFT/i
                | /LN/i
                | /LOCATE/i
                | /LOG/i
                | /LOG10/i
                | /LTRIM/i
                | /MIDNIGHT_SECONDS/i
                | /MOD/i
                | /MONTHNAME/i
                | /POWER/i
                | /PUT_ROUTINE_SAR/i
                | /QUARTER/i
                | /RADIANS/i
                | /RAND/i
                | /REPEAT/i
                | /REPLACE/i
                | /RIGHT/i
                | /ROUND/i
                | /RTRIM/I
                | /SIGN/i
                | /SIN/i
                | /SOUNDEX/i
                | /SPACE/i
                | /SQLCACHE_SNAPSHOT/i
                | /SQRT/i
                | /TAN/i
                | /TIMESTAMP_ISO/i
                | /TIMESTAMPDIFF/i
                | ( /TRUNCATE/i | /TRUNC/i )
                | /UCASE/i
                | /WEEK/i
                | /WEEK_ISO/i

scalar_fullselect: '(' fullselect ')'

labeled_duration: ld_type ld_duration

ld_type: function 
       | '(' expression ')' 
       | constant 
       | column_name 
       | host_variable

ld_duration: /YEARS?/i 
           | /MONTHS?/i 
           | /DAYS?/i 
           | /HOURS?/i 
           | /MINUTES?/i
           | /SECONDS?/i
           | /MICROSECONDS?/i

case_expression: /CASE/i ( searched_when_clause 
                         | simple_when_clause 
                         ) 
                         ( /ELSE\s+NULL/i 
                         | /ELSE/i result_expression 
                         )(?) /END/i

searched_when_clause: ( /WHEN/i search_condition /THEN/i 
                        ( result_expression 
                        | /NULL/i
                        )
                      )(s)

simple_when_clause: expression ( /WHEN/i search_condition /THEN/i 
                                 ( result_expression 
                                 | /NULL/i
                                 )
                               )(s)

result_expression: expression 

cast_specification: /CAST/i '(' ( expression 
                                | /NULL/i
                                | parameter_marker
                                ) /AS/i data_type 
                                  ( /SCOPE/ ( typed_table_name 
                                            | typed_view_name
                                            )
                                  )(?) ')'

dereference_operation: scoped_reference_expression '->' name1 
                      (  '(' expression(s) ')' )(?)
#                         ( '(' expression(s /,/) ')' )(?)


scoped_reference_expression: expression 
{ # scoped, reference 
}

name1: NAME

OLAP_function: ranking_function 
             | numbering_function
             | aggregation_function

ranking_function: ( /RANK/ '()' 
                  | /DENSE_RANK|DENSERANK/i '()' 
                  ) /OVER/i '(' window_partition_clause(?) window_order_clause ')'

numbering_function: /ROW_NUMBER|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?) 
                      ( window_order_clause window_aggregation_group_clause(?) 
                      )(?) 
                      ( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i 
                      | window_aggregation_group_clause
                      )(?) ')'

window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)

window_order_clause: /ORDER\s+BY/i 
                      ( sort_key_expression 
                        ( asc_option 
                        | desc_option 
                        )(?)
                      )(s /,/)

asc_option: /ASC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)

desc_option: /DESC/i ( /NULLS\s+FIRST/i | /NULLS\s+LAST/i )(?)

window_aggregation_group_clause: ( /ROWS/i
                                 | /RANGE/i
                                 ) 
                                 ( group_start
                                 | group_between
                                 | group_end
                                 )

group_start: /UNBOUNDED\s+PRECEDING/i 
           | unsigned_constant /PRECEDING/i
           | /CURRENT\s+ROW/i

group_between: /BETWEEN/i group_bound1 /AND/i group_bound2

group_bound1: /UNBOUNDED\s+PRECEDING/i 
           | unsigned_constant /PRECEDING/i
           | unsigned_constant /FOLLOWING/i
           | /CURRENT\s+ROW/i

group_bound2: /UNBOUNDED\s+PRECEDING/i 
           | unsigned_constant /PRECEDING/i
           | unsigned_constant /FOLLOWING/i
           | /CURRENT\s+ROW/i

group_end: /UNBOUNDED\s+PRECEDING/i 
           | unsigned_constant /FOLLOWING/i 

method_invocation: subject_expression '..' method_name
                    ( '(' expression(s) ')' 
#                    ( '(' expression(s /,/) ')' 
                    )(?)

subject_expression: expression
{ # with static result type that is a used-defined struct type 
}

method_name: NAME
{ # must be a method of subject_expression 
}

subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'

sequence_reference: nextval_expression
                  | prevval_expression

nextval_expression: /NEXTVAL\s+FOR/i sequence_name

prevval_expression: /PREVVAL\s+FOR/i sequence_name

sequence_name: NAME


search_condition: /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' ) cond(s?)

cond: ( /AND/i | /OR/i ) /NOT|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) | '(' search_condition ')' )

predicate: basic_p | quantified_p | between_p | exists_p | in_p | like_p | null_p | type_p 

basic_p: expression /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ expression

quantified_p: expression1 /(=|<>|<|>|<=|=>|\^=|\^<|\^>|\!=)/ /SOME|ANY|ALL/i '(' fullselect ')'
Commit	Line	Data
3c1274c1	1
	2	{
	3	my ( %tables, $table_order, @table_comments, @views, @triggers );
	4	}
	5
	6	#
	7	# The "eofile" rule makes the parser fail if any "statement" rule
	8	# fails. Otherwise, the first successful match by a "statement"
	9	# won't cause the failure needed to know that the parse, as a whole,
	10	# failed. -ky
	11	#
	12	startrule : statement(s) eofile {
	13	$return = {
	14	tables => \%tables,
	15	views => \@views,
	16	triggers => \@triggers,
	17	}
	18	}
	19
	20	eofile : /^\Z/
	21
	22	statement :
	23	comment
	24	\| create
	25	\| <error>
	26
	27	comment : /^\s-{2}.\n/
	28	{
	29	my $comment = $item[1];
	30	$comment =~ s/^\s(-{2})\s//;
	31	$comment =~ s/\s*$//;
	32	$return = $comment;
	33	}
	34
	35
	36	create: CREATE TRIGGER trigger_name before type /ON/i table_name reference_b(?) /FOR EACH ROW/i 'MODE DB2SQL' triggered_action
	37	{
	38	my $table_name = $item{'table_name'}{'name'};
	39	$return = {
	40	table => $table_name,
	41	schema => $item{'trigger_name'}{'schema'},
	42	name => $item{'trigger_name'}{'name'},
	43	when => 'before',
	44	db_event => $item{'type'}->{'event'},
	45	fields => $item{'type'}{'fields'},
	46	condition => $item{'triggered_action'}{'condition'},
	47	reference => $item{'reference_b'},
	48	granularity => $item[9],
	49	action => $item{'triggered_action'}{'statement'}
	50	};
	51
	52	push @triggers, $return;
	53	}
	54
	55	create: CREATE TRIGGER trigger_name after type /ON/i table_name reference_a(?) /FOR EACH ROW\|FOR EACH STATEMENT/i 'MODE DB2SQL' triggered_action
	56	{
	57	my $table_name = $item{'table_name'}{'name'};
	58	$return = {
	59	table => $table_name,
	60	schema => $item{'trigger_name'}{'schema'},
	61	name => $item{'trigger_name'}{'name'},
	62	when => 'after',
	63	db_event => $item{'type'}{'event'},
	64	fields => $item{'type'}{'fields'},
65	condition => $item{'triggered_action'}{'condition'},
66	reference => $item{'reference_a'},
67	granularity => $item[9],
68	action => $item{'triggered_action'}{'statement'}
69	};
70
71	push @triggers, $return;
72	}
73
74	create: CREATE /FEDERATED\|/i VIEW view_name column_list(?) /AS/i with_expression(?) SQL_procedure_statement
75	{
76	$return = {
77	name => $item{view_name}{name},
78	sql => $item{SQL_procedure_statement},
79	with => $item{'with_expression(?)'},
80	fields => $item{'column_list(?)'}
81	};
82	push @views, $return;
83	}
84
85	# create: CREATE /FEDERATED/i VIEW view_name col_list_or_of(?) /AS/i with_expression(?) fullselect options(?)
86
87	# col_list_or_of: column_list \| /OF/i ( root_view_definition \| subview_definition )
88
89	with_expression: /WITH/i common_table_expression(s /,/)
90	{
91	$return = $item{'common_table_expression'};
92	}
93
94	SQL_procedure_statement: /[^;]*/ /(;\|\z)/ { $return = $item[1] . $item[2] }
95
96	column_list: '(' column_name(s /,/) ')'
97	{
98	$return = join(' ', '(', @{$item[2]}, ')');
99	}
100
101	CREATE: /create/i
102
103	TRIGGER: /trigger/i
104
105	VIEW: /view/i
106
107	INNER: /inner/i
108
109	LEFT: /left/i
110
111	RIGHT: /right/i
112
113	FULL: /full/i
114
115	OUTER: /outer/i
116
117	WHERE: /where/i
118
119	trigger_name: SCHEMA '.' NAME
120	{ $return = { schema => $item[1], name => $item[3] } }
121	\| NAME
122	{ $return = { name => $item[1] } }
123
124	table_name: SCHEMA '.' NAME
125	{ $return = { schema => $item[1], name => $item[3] } }
126	\| NAME
127	{ $return = { name => $item[1] } }
128
129	view_name: SCHEMA '.' NAME
130	{ $return = { schema => $item[1], name => $item[3] } }
131	\| NAME
132	{ $return = { name => $item[1] } }
133
134	column_name: NAME
135
136	identifier: NAME
137
138	correlation_name: NAME
139
140	numeric_constant: /\d+/
141
142	SCHEMA: /\w+/
143
144	SCHEMA: /\w{1,128}/
145
146	NAME: /\w+/
147
148	NAME: /\w{1,18}/
149
150	options: /WITH/i ( /CASCADED/i \| /LOCAL/i ) /CHECK\s+OPTION/i
151
152	# root_view_definition: /MODE\s+DB2SQL/i '(' oid_column ( /,/ with_options )(?) ')'
153
154	# subview_definition: /MODE\s+DB2SQL/i under_clause ( '(' with_options ')' )(?) /EXTEND/i(?)
155
156	# oid_column: /REF\s+IS/i oid_column_name /USER\s+GENERATED\s+UNCHECKED/i(?)
157
158	# with_options: ( column_name /WITH\s+OPTIONS/i ( /SCOPE/i ( typed_table_name \| typed_view_name ) \| /READ\s+ONLY/i )(s /,/) )(s /,/)
159
160	# under_clause: /UNDER/i superview_name /INHERIT\s+SELECT\s+PRIVILEGES/i
161
162	common_table_expression: table_name column_list /AS/i get_bracketed
163	{
164	$return = { name => $item{table_name}{name},
165	query => $item[4]
166	};
167	}
168
169	get_bracketed:
170	{
171	extract_bracketed($text, '(');
172	}
173
174	common_table_expression: table_name column_list /AS/i '(' fullselect ')'
175
176	# fullselect: ( subselect \| '(' fullselect ')' \| values_clause ) ( ( /UNION/i \| /UNION/i /ALL/i \| /EXCEPT/i \| /EXCEPT/i /ALL/i \| /INTERSECT/i \| /INTERSECT/i /ALL/i ) ( subselect \| '(' fullselect ')' \| values_clause ) )(s)
177
178	# values_clause: /VALUES/i values_row(s /,/)
179
180	# values_row: ( expression \| /NULL/i ) \| '(' ( expression \| /NULL/i )(s /,/) ')'
181
182	# subselect: select_clause from_clause where_clause(?) group_by_clause(?) having_clause(?)
183
184	# select_clause: SELECT ( /ALL/i \| /DISTINCT )(?) ( '' \| ( expression ( /AS\|/i new_column_name )(?) \| exposed_name '.' )(s /,/) )
185
186	# from_clause: /FROM/i table_name(s /,/)
187
188	# from_clause: /FROM/i table_reference(s /,/)
189
190	# table_reference:
191	# (
192	# ( nickname
193	# \| table_name
194	# \| view_name
195	# )
196	# \| ( /ONLY/i
197	# \| /OUTER/i
198	# ) '('
199	# ( table_name
200	# \| view_name
201	# ) ')'
202	# ) correlation_clause(?)
203	# \| TABLE '(' function_name '(' expression(s? /,/) ')' ')' correlation_clause
204	# \| TABLE(?) '(' fullselect ')' correlation_clause
205	# \| joined_table
206
207
208	# correlation_clause: /AS/i(?) correlation_name column_list(?)
209
210	# joined_table:
211	# table_reference ( INNER
212	# \| outer
213	# )(?) JOIN table_reference ON join_condition
214	# \| '(' joined_table ')'
215
216	# outer: ( LEFT \| RIGHT \| FULL ) OUTER(?)
217
218	where_clause: WHERE search_condition
219
220	# group_by_clause: /GROUP\s+BY/i ( grouping_expression
221	# \| grouping_sets
222	# \| super_groups
223	# )(s /,/)
224
225	# grouping_expression: expression
226
227	# orderby_clause: /ORDER\s+BY/i ( sort_key ( /ASC/i \| /DESC/i)(?) )(s /,/)
228
229	# sort_key: simple_column_name \| simple_integer \| sort_key_expression
230
231	# # Name of one of the selected columns!
232	# simple_column_name: NAME
233
234	# simple_integer: /\d+/
235	# { $item[1] <= $numberofcolumns && $item[1] > 1 }
236
237	# sort_key_expression: expression
238	# { expression from select columns list, grouping_expression, column function.. }
239
240	# grouping_sets: /GROUPING\s+SETS/i '(' (
241	# ( grouping_expression
242	# \| super_groups
243	# )
244	# \| '(' ( grouping_expression
245	# \| super_groups
246	# )(s /,/) ')'
247	# )(s /,/) ')'
248
249	# super_groups: /ROLLUP/i '(' grouping_expression_list ')'
250	# \| /CUBE/i '(' grouping_expression_list ')'
251	# \| grand_total
252
253	# grouping_expression_list: ( grouping_expression
254	# \| '(' grouping_expression(s /,/) ')'
255	# )(s /,/)
256
257	# grand_total: '(' ')'
258
259	# having_clause: /HAVING/i search_condition
260
261	when_clause: /WHEN/i '(' search_condition ')' {$return = $item[3]}
262
263	triggered_action: when_clause(?) SQL_procedure_statement
264	{ $return = { 'condition' => $item[1][0],
265	'statement' => $item{'SQL_procedure_statement'} };
266	}
267
268	before: /NO CASCADE BEFORE/i
269
270	after: /AFTER/i
271
272	type: /UPDATE/i /OF/i column_name(s /,/)
273	{ $return = { event => 'update_on',
274	fields => $item[3] }
275	}
276
277	type: ( /INSERT/i \| /DELETE/i \| /UPDATE/i )
278	{ $return = { event => $item[1] } }
279
280	reference_b: /REFERENCING/i old_new_corr(0..2)
281	{ $return = join(' ', $item[1], join(' ', @{$item[2]}) ) }
282
283	reference_a: /REFERENCING/i old_new_corr(0..2) old_new_table(0..2)
284	{ $return = join(' ', $item[1], join(' ', @{$item[2]}), join(' ', @{$item[3]}) ) }
285
286	old_new_corr: /OLD/i /(AS)?/i correlation_name
287	{ $return = join(' ', @item[1..3] ) }
288	\| /NEW/i /(AS)?/i correlation_name
289	{ $return = join(' ', @item[1..3] ) }
290
291	old_new_table: /OLD_TABLE/i /(AS)?/i identifier
292	{ $return = join(' ', @item[1..3] ) }
293	\| /NEW_TABLE/i /(AS)?/i identifier
294	{ $return = join(' ', @item[1..3] ) }
295
296	# Just parsing simple search conditions for now.
297	search_condition: /[^)]+/
298
299	expression: (
300	( '+'
301	\| '-'
302	)(?)
303	( function
304	\| '(' expression ')'
305	\| constant
306	\| column_name
307	\| host_variable
308	\| special_register
309	\| '(' scalar_fullselect ')'
310	\| labeled_duration
311	\| case_expression
312	\| cast_specification
313	# \| dereference_operation
314	\| OLAP_function
315	\| method_invocation
316	\| subtype_treatment
317	\| sequence_reference
318	)
319	)(s /operator/)
320
321	operator: ( /CONCAT/i \| '\|\|' ) \| '/' \| '*' \| '+' \| '-'
322
323	function: ( /SYSIBM\.\|/i sysibm_function
324	\| /SYSFUN\.\|/i sysfun_function
325	\| userdefined_function
326	) '(' func_args(s /,/) ')'
327
328	constant: int_const \| float_const \| dec_const \| char_const \| hex_const \| grastr_const
329
330	func_args: expression
331
332	sysibm_function: ( /ABS/i \| /ABSVAL/i )
333	\| /AVG/i
334	\| /BIGINT/i
335	\| /BLOB/i
336	\| /CHAR/i
337	\| /CLOB/i
338	\| /COALESCE/i
339	\| ( /CONCAT/ \| '\|\|' )
340	\| ( /CORRELATION/i \| /CORR/ )
341	\| /COUNT/i
342	\| /COUNT_BIG/i
343	\| (/COVARIANCE/i \| /COVAR/i )
344	\| /DATE/i
345	\| /DAY/i
346	\| /DAYS/i
347	\| /DBCLOB/i
348	\| ( /DECIMAL/i \| /DEC/i )
349	\| /DECRYPT_BIN/i
350	\| /DECRYPT_CHAR/i
351	\| /DEREF/i
352	\| /DIGITS/i
353	\| /DLCOMMENT/i
354	\| /DLLINKTYPE/i
355	\| /DLURLCOMPLETE/i
356	\| /DLURLPATH/i
357	\| /DLURLPATHONLY/i
358	\| /DLURLSCHEME/i
359	\| /DLURLSERVER/i
360	\| /DLVALUE/i
361	\| ( /DOUBLE/i \| /DOUBLE_PRECISION/i )
362	\| /ENCRYPT/i
363	\| /EVENT_MON_STATE/i
364	\| /FLOAT/i
365	\| /GETHINT/i
366	\| /GENERATE_UNIQUE/i
367	\| /GRAPHIC/i
368	\| /GROUPING/i
369	\| /HEX/i
370	\| /HOUR/i
371	\| /IDENTITY_VAL_LOCAL/i
372	\| ( /INTEGER/i \| /INT/ )
373	\| ( /LCASE/i \| /LOWER/ )
374	\| /LENGTH/i
375	\| /LONG_VARCHAR/i
376	\| /LONG_VARGRAPHIC/i
377	\| /LTRIM/i
378	\| /MAX/i
379	\| /MICROSECOND/i
380	\| /MIN/i
381	\| /MINUTE/i
382	\| /MONTH/i
383	\| /MULTIPLY_ACT/i
384	\| /NODENUMBER/i
385	\| /NULLIF/i
386	\| /PARTITON/i
387	\| /POSSTR/i
388	\| /RAISE_ERROR/i
389	\| /REAL/i
390	\| /REC2XML/i
391	\| /REGR_AVGX/i
392	\| /REGR_AVGY/i
393	\| /REGR_COUNT/i
394	\| ( /REGR_INTERCEPT/i \| /REGR_ICPT/i )
395	\| /REGR_R2/i
396	\| /REGR_SLOPE/i
397	\| /REGR_SXX/i
398	\| /REGR_SXY/i
399	\| /REGR_SYY/i
400	\| /RTRIM/i
401	\| /SECOND/i
402	\| /SMALLINT/i
403	\| /STDDEV/i
404	\| /SUBSTR/i
405	\| /SUM/i
406	\| /TABLE_NAME/i
407	\| /TABLE_SCHEMA/i
408	\| /TIME/i
409	\| /TIMESTAMP/i
410	\| /TRANSLATE/i
411	\| /TYPE_ID/i
412	\| /TYPE_NAME/i
413	\| /TYPE_SCHEMA/i
414	\| ( /UCASE/i \| /UPPER/i )
415	\| /VALUE/i
416	\| /VARCHAR/i
417	\| /VARGRAPHIC/i
418	\| ( /VARIANCE/i \| /VAR/i )
419	\| /YEAR/i
420
421	sysfun: ( /ABS/i \| /ABSVAL/i )
422	\| /ACOS/i
423	\| /ASCII/i
424	\| /ASIN/i
425	\| /ATAN/i
426	\| /ATAN2/i
427	\| ( /CEIL/i \| /CEILING/i )
428	\| /CHAR/i
429	\| /CHR/i
430	\| /COS/i
431	\| /COT/i
432	\| /DAYNAME/i
433	\| /DAYOFWEEK/i
434	\| /DAYOFWEEK_ISO/i
435	\| /DAYOFYEAR/i
436	\| /DEGREES/i
437	\| /DIFFERENCE/i
438	\| /DOUBLE/i
439	\| /EXP/i
440	\| /FLOOR/i
441	\| /GET_ROUTINE_SAR/i
442	\| /INSERT/i
443	\| /JULIAN_DAY/i
444	\| /LCASE/i
445	\| /LEFT/i
446	\| /LN/i
447	\| /LOCATE/i
448	\| /LOG/i
449	\| /LOG10/i
450	\| /LTRIM/i
451	\| /MIDNIGHT_SECONDS/i
452	\| /MOD/i
453	\| /MONTHNAME/i
454	\| /POWER/i
455	\| /PUT_ROUTINE_SAR/i
456	\| /QUARTER/i
457	\| /RADIANS/i
458	\| /RAND/i
459	\| /REPEAT/i
460	\| /REPLACE/i
461	\| /RIGHT/i
462	\| /ROUND/i
463	\| /RTRIM/I
464	\| /SIGN/i
465	\| /SIN/i
466	\| /SOUNDEX/i
467	\| /SPACE/i
468	\| /SQLCACHE_SNAPSHOT/i
469	\| /SQRT/i
470	\| /TAN/i
471	\| /TIMESTAMP_ISO/i
472	\| /TIMESTAMPDIFF/i
473	\| ( /TRUNCATE/i \| /TRUNC/i )
474	\| /UCASE/i
475	\| /WEEK/i
476	\| /WEEK_ISO/i
477
478	scalar_fullselect: '(' fullselect ')'
479
480	labeled_duration: ld_type ld_duration
481
482	ld_type: function
483	\| '(' expression ')'
484	\| constant
485	\| column_name
486	\| host_variable
487
488	ld_duration: /YEARS?/i
489	\| /MONTHS?/i
490	\| /DAYS?/i
491	\| /HOURS?/i
492	\| /MINUTES?/i
493	\| /SECONDS?/i
494	\| /MICROSECONDS?/i
495
496	case_expression: /CASE/i ( searched_when_clause
497	\| simple_when_clause
498	)
499	( /ELSE\s+NULL/i
500	\| /ELSE/i result_expression
501	)(?) /END/i
502
503	searched_when_clause: ( /WHEN/i search_condition /THEN/i
504	( result_expression
505	\| /NULL/i
506	)
507	)(s)
508
509	simple_when_clause: expression ( /WHEN/i search_condition /THEN/i
510	( result_expression
511	\| /NULL/i
512	)
513	)(s)
514
515	result_expression: expression
516
517	cast_specification: /CAST/i '(' ( expression
518	\| /NULL/i
519	\| parameter_marker
520	) /AS/i data_type
521	( /SCOPE/ ( typed_table_name
522	\| typed_view_name
523	)
524	)(?) ')'
525
526	dereference_operation: scoped_reference_expression '->' name1
527	( '(' expression(s) ')' )(?)
528	# ( '(' expression(s /,/) ')' )(?)
529
530
531
532	scoped_reference_expression: expression
533	{ # scoped, reference
534	}
535
536	name1: NAME
537
538	OLAP_function: ranking_function
539	\| numbering_function
540	\| aggregation_function
541
542	ranking_function: ( /RANK/ '()'
543	\| /DENSE_RANK\|DENSERANK/i '()'
544	) /OVER/i '(' window_partition_clause(?) window_order_clause ')'
545
546	numbering_function: /ROW_NUMBER\|ROWNUMBER/i '()' /OVER/i '(' window_partition_clause(?)
547	( window_order_clause window_aggregation_group_clause(?)
548	)(?)
549	( /RANGE\s+BETWEEN\s+UNBOUNDED\s+PRECEDING\s+AND\s+UNBBOUNDED\s+FOLLOWING/i
550	\| window_aggregation_group_clause
551	)(?) ')'
552
553	window_partition_clause: /PARTITION\s+BY/i partitioning_expression(s /,/)
554
555	window_order_clause: /ORDER\s+BY/i
556	( sort_key_expression
557	( asc_option
558	\| desc_option
559	)(?)
560	)(s /,/)
561
562	asc_option: /ASC/i ( /NULLS\s+FIRST/i \| /NULLS\s+LAST/i )(?)
563
564	desc_option: /DESC/i ( /NULLS\s+FIRST/i \| /NULLS\s+LAST/i )(?)
565
566	window_aggregation_group_clause: ( /ROWS/i
567	\| /RANGE/i
568	)
569	( group_start
570	\| group_between
571	\| group_end
572	)
573
574	group_start: /UNBOUNDED\s+PRECEDING/i
575	\| unsigned_constant /PRECEDING/i
576	\| /CURRENT\s+ROW/i
577
578	group_between: /BETWEEN/i group_bound1 /AND/i group_bound2
579
580	group_bound1: /UNBOUNDED\s+PRECEDING/i
581	\| unsigned_constant /PRECEDING/i
582	\| unsigned_constant /FOLLOWING/i
583	\| /CURRENT\s+ROW/i
584
585	group_bound2: /UNBOUNDED\s+PRECEDING/i
586	\| unsigned_constant /PRECEDING/i
587	\| unsigned_constant /FOLLOWING/i
588	\| /CURRENT\s+ROW/i
589
590	group_end: /UNBOUNDED\s+PRECEDING/i
591	\| unsigned_constant /FOLLOWING/i
592
593	method_invocation: subject_expression '..' method_name
594	( '(' expression(s) ')'
595	# ( '(' expression(s /,/) ')'
596	)(?)
597
598	subject_expression: expression
599	{ # with static result type that is a used-defined struct type
600	}
601
602	method_name: NAME
603	{ # must be a method of subject_expression
604	}
605
606	subtype_treatment: /TREAT/i '(' expression /AS/i data_type ')'
607
608	sequence_reference: nextval_expression
609	\| prevval_expression
610
611	nextval_expression: /NEXTVAL\s+FOR/i sequence_name
612
613	prevval_expression: /PREVVAL\s+FOR/i sequence_name
614
615	sequence_name: NAME
616
617
618	search_condition: /NOT\|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) \| '(' search_condition ')' ) cond(s?)
619
620	cond: ( /AND/i \| /OR/i ) /NOT\|/i ( predicate ( /SELECTIVITY/i numeric_constant )(?) \| '(' search_condition ')' )
621
622	predicate: basic_p \| quantified_p \| between_p \| exists_p \| in_p \| like_p \| null_p \| type_p
623
624	basic_p: expression /(=\|<>\|<\|>\|<=\|=>\|\^=\|\^<\|\^>\|\!=)/ expression
625
626	quantified_p: expression1 /(=\|<>\|<\|>\|<=\|=>\|\^=\|\^<\|\^>\|\!=)/ /SOME\|ANY\|ALL/i '(' fullselect ')'
627