# Copyright (C) 2010 Sun Microsystems, Inc. All rights reserved.
# Use is subject to license terms.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
# USA

################################################################################
# range_access.yy
# Purpose:  For testing the range optimization for MySQL
# gendata:  Use with conf/range_access.zz as a gendata file
# parameters:
#   queries = 10k+
#   threads = 1
#   no need for an engine argument - we mix storage engines via the gendata file
# uses:
#   valgrind - use MarkErrorLog Validator + --valgrind
#   comparison between MySQL versions / configurations
#   3way compares to javadb and postgres
#
# NOTES:  This grammar will run against either a single or multi-part index 
#         For multi-part indexes, the index will be created, a set of queries
#         will be run, then the index will be dropped
#
# TODO:  It would be nice to dynamically create indexes or to create
#        more complex indexes on multiple tables at a time
#        but this is more than a little tricky
################################################################################


query:
  { $idx_table = '' ; @idx_fields = () ;  "" } query_type ;

query_type:
  single_idx_query_set | dual_int_idx_query_set | dual_char_idx_query_set | tri_int_idx_query_set ;

single_idx_query_set:
  single_idx_query ; single_idx_query ; single_idx_query ; single_idx_query ; single_idx_query ; 

dual_int_idx_query_set:
  new_dual_int_index ; multi_int_idx_query_set ;

dual_char_idx_query_set:
  new_dual_char_index ; multi_char_idx_query_set ;

tri_int_idx_query_set:
  new_tri_int_index ; multi_int_idx_query_set ;

tri_char_idx_query_set:
  new_tri_char_index ; multi_char_idx_query_set ;

wild_query:
  single_idx_query | multi_int_idx_query | multi_char_idx_query ;

multi_int_idx_query_set:
  multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; multi_int_idx_query ; wild_query ; drop_index ;  

multi_char_idx_query_set:
  multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; multi_char_idx_query ; wild_query ; drop_index ;

################################################################################
# index-specific rules
################################################################################

drop_index:
 DROP INDEX `test_idx` ON { $idx_table } ;

index_pre:
  ALTER TABLE index_table ADD INDEX `test_idx` USING index_type ;

new_dual_int_index:
  index_pre (dual_int_idx_field_list) ;

new_dual_char_index:
 index_pre (dual_char_idx_field_list) ;

new_tri_int_index:
  index_pre (tri_int_idx_field_list) ;

new_tri_char_index:
  index_pre (tri_char_idx_field_list) ;

dual_int_idx_field_list:
  `pk`, `col_int_key`  { @idx_fields =("`pk`", "`col_int_key`") ; "" } |
  `col_int_key` , `pk` { @idx_fields =("`col_int_key`", "`pk`") ; "" } | 
  `col_int_key` , `col_int` { @idx_fields =("`col_int_key`", "`col_int`") ;  "" }  ;  

dual_char_idx_field_list:
   `col_varchar_10_utf8`( small_length ) , `col_varchar_1024_utf8`( large_length ) {@idx_fields = ("`col_varchar_10_utf8`", "`col_varchar_1024_utf8`") ; "" } ;

tri_int_idx_field_list:
  `pk`, `col_int_key`, `col_int`  { @idx_fields =("`pk`", "`col_int_key`", "`col_int`") ; "" } |
  `col_int_key` , `pk` , `col_int` { @idx_fields =("`col_int_key`", "`pk`", "`col_int`") ; "" } | 
  `col_int_key` , `col_int`, `pk` { @idx_fields =("`col_int_key`", "`col_int`", "`pk`") ;  "" }  ;  

tri_char_idx_field_list:
   `col_varchar_10_utf8`( small_length ) , `col_varchar_1024_utf8`( large_length ) {@idx_fields = ("`col_varchar_10_utf8`", "`col_varchar_1024_utf8`") ; "" } ;

################################################################################
# single index rules
################################################################################

single_idx_where_list:
    single_int_idx_where_clause | single_char_idx_where_clause |
    single_idx_where_list and_or single_int_idx_where_clause |
    single_idx_where_list and_or single_char_idx_where_clause ;


single_int_idx_where_clause:
   { my @int_idx_fields = ("`pk`" , "`col_int_key`") ; $int_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@int_idx_fields) ; "" } single_int_idx_where_list ;


single_int_idx_where_list:
   single_int_idx_where_list or_and single_int_idx_where_item |
   single_int_idx_where_item | single_int_idx_where_item ;

single_int_idx_where_item:
   { $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
   { $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit[invariant] + increment ) |
   { $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit[invariant] + int_value ) |
   { $int_idx_field } greater_than _digit[invariant] AND { $int_idx_field } less_than ( _digit + int_value ) |
   { $int_idx_field } greater_than _digit AND { $int_idx_field } less_than ( _digit + increment ) |
   { $int_idx_field } comparison_operator int_value |
   { $int_idx_field } not_equal int_value |
   { $int_idx_field } not IN (number_list) |
   { $int_idx_field } not BETWEEN _digit[invariant] AND (_digit[invariant] + int_value ) |
   { $int_idx_field } IS not NULL ;


single_char_idx_where_clause:
  { my @char_idx_fields = ("`col_varchar_10_utf8_key`", "`col_varchar_1024_utf8_key`") ; $char_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@char_idx_fields) ; "" } single_char_idx_where_list ;

single_char_idx_where_list:
  single_char_idx_where_list and_or single_char_idx_where_item |
  single_char_idx_where_item | single_char_idx_where_item ;

single_char_idx_where_item:
  { $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
  { $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
  { $char_idx_field } greater_than _char AND { $char_idx_field } less_than 'z' |
    { $char_idx_field } greater_than char_value AND { $char_idx_field } less_than char_value |
  { $char_idx_field } greater_than char_value AND { $char_idx_field } less_than 'zzzz' |
  { $char_idx_field } IS not NULL |
  { $char_idx_field } not IN (char_list) |
  { $char_idx_field } not LIKE ( char_pattern ) |
  { $char_idx_field } not BETWEEN _char AND 'z' ;

################################################################################
# multi-part index rules
################################################################################

multi_int_idx_where_list:
    multi_int_idx_where_clause | 
    multi_int_idx_where_list and_or multi_int_idx_where_clause | multi_int_idx_where_list and_or multi_int_idx_where_clause ;


multi_int_idx_where_clause:
   {  $int_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_int_idx_where_list ;

# char rules
multi_char_idx_where_list:
    multi_char_idx_where_clause |
    multi_char_idx_where_list and_or multi_char_idx_where_clause | multi_char_idx_where_list and_or multi_char_idx_where_clause ;

multi_char_idx_where_clause:
   {  $char_idx_field = ("table".$prng->int(1,$tables))." . ".$prng->arrayElement(\@idx_fields) ; "" } single_char_idx_where_list ;
  


################################################################################
# general-purpose query rules
################################################################################

single_idx_query:
  { $tables=0 ; $fields = 0 ; "" }  SELECT select_list FROM join WHERE single_idx_where_list opt_where_list order_by_clause ;

multi_int_idx_query:
  { $tables=0 ; $fields = 0 ; "" }  SELECT select_list FROM idx_join WHERE multi_int_idx_where_list opt_where_list order_by_clause ;

multi_char_idx_query:
  { $tables=0 ; $fields = 0 ; "" }  SELECT select_list FROM idx_join WHERE multi_char_idx_where_list opt_where_list order_by_clause ;

select_list:
  select_item | select_item , select_list ;

select_item:
  table_one_two . _field AS { my $f = "field".++$fields ; $f } ; 

join:
   { $stack->push() }      
   table_or_join 
   { $stack->set("left",$stack->get("result")); }
   left_right outer JOIN table_or_join 
   ON 
   join_condition ;

idx_join:
   { $stack->push() }      
   idx_table_for_join 
   { $stack->set("left",$stack->get("result")); }
   left_right outer JOIN table_or_join 
   ON 
   join_condition ;

join_condition:
   int_condition | char_condition ;

int_condition: 
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_indexed = 
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_indexed
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_indexed =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_field_name
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_field_name =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/,
 $table_string); $table_array[1] } . int_indexed
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . int_field_name =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/,
 $table_string); $table_array[1] } . int_field_name
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;

char_condition:
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_field_name =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_field_name 
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_indexed  =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_field_name 
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } |
   { my $left = $stack->get("left"); my %s=map{$_=>1} @$left; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_field_name =
   { my $right = $stack->get("result"); my %s=map{$_=>1} @$right; my @r=(keys %s); my $table_string = $prng->arrayElement(\@r); my @table_array = split(/AS/, $table_string); $table_array[1] } . char_indexed 
   { my $left = $stack->get("left");  my $right = $stack->get("result"); my @n = (); push(@n,@$right); push(@n,@$left); $stack->pop(\@n); return undef } ;

table_or_join:
           table | table | table | table | table | table | 
           table | table | join | join ;

table:
# We use the "AS table" bit here so we can have unique aliases if we use the same table many times
       { $stack->push(); my $x = $prng->arrayElement($executors->[0]->tables())." AS table".++$tables;  my @s=($x); $stack->pop(\@s); $x } ;

idx_table_for_join:
       { $stack->push() ; my $x = $idx_table." AS table".++$tables; my @s=($x); $stack->pop(\@s); $x } ;

join_type:
	INNER JOIN | left_right outer JOIN | STRAIGHT_JOIN ; 

left_right:
	LEFT | LEFT | LEFT | RIGHT ;

outer:
	| | | | OUTER ;

index_type:
  BTREE | HASH ;

index_table:
  { my $idx_table_candidate = $prng->arrayElement($executors->[0]->tables()) ; $idx_table = $idx_table_candidate ; $idx_table } ;

opt_where_list:
  | | | | and_or where_list ;

where_list:
  where_item | where_item | where_item | ( where_list and_or where_item ) ;

where_item:
  existing_table_item . int_field_name comparison_operator int_value |
  existing_table_item . char_field_name comparison_operator _char |
  existing_table_item . int_field_name comparison_operator int_value |
  existing_table_item . int_field_name comparison_operator existing_table_item . int_field_name |
  existing_table_item . char_field_name comparison_operator _char |
  existing_table_item . char_field_name comparison_operator existing_table_item . char_field_name |
  existing_table_item . _field IS not NULL |
  existing_table_item . `pk` IS not NULL |
  single_idx_where_list ;

order_by_clause:
	| | |
        ORDER BY total_order_by desc limit |
	ORDER BY order_by_list  ;

total_order_by:
	{ join(', ', map { "field".$_ } (1..$fields) ) };

order_by_list:
	order_by_item  |
	order_by_item  , order_by_list ;

order_by_item:
	existing_select_item desc ;

desc:
        ASC | | | | DESC ; 

limit:
	| | LIMIT limit_size | LIMIT limit_size OFFSET int_value;

limit_size:
    1 | 2 | 10 | 100 | 1000;

################################################################################
# utility / helper rules
################################################################################

new_table_item:
	_table AS { "table".++$tables };

existing_table_item:
	{ "table".$prng->int(1,$tables) };

existing_select_item:
	{ "field".$prng->int(1,$fields) };

comparison_operator:
  = | > | < | != | <> | <= | >= ;

greater_than:
  > | >= ;

less_than:
  < | <= ;

not_equal:
  <> | != ;

int_value:
   _digit | _digit | _digit | _digit | _digit | digit | other_int ;

other_int:
   _tinyint_unsigned | 20 | 25 | 30 | 35 | 50 | 65 | 75 | 100 ;

char_value:
  _char | _char | _char | _quid | _english ; 

char_pattern:
 char_value | char_value | CONCAT( _char, '%') | 'a%'| _quid | '_' | '_%' ;

increment:
   1 |  1 | 2 | 2 | 5 | 5 | 6 | 10 ; 

large_length:
   200 | 200 | 200 | 200 | 200 | 100 | 200 | 250 | 37 | 50 | 175 | small_length ;

small_length:
   1 | 2 | 5 | 7 | 8 | 9 | 10 | 10 | 10 | 10 ; 

random_length:
  large_length | large_length | small_length ;

int_indexed:
   `pk` | `col_int_key` ;

int_field_name: 
   `pk` | `col_int_key` | `col_int` ;

char_indexed:  
   `col_varchar_10_utf8_key` | `col_varchar_1024_utf8_key`;
 
char_field_name:
   `col_varchar_10_utf8_key` | `col_varchar_1024_utf8_key` | `col_varchar_10_utf8` |  `col_varchar_1024_utf8` ; 

number_list:
   int_value | number_list, int_value ;

char_list: 
   _char | char_list, _char ;

table_one_two:
   table1 | table1 | table1 | table2 | table2 ;

and_or:
   AND | AND | OR ;

or_and:
  OR | OR | OR | AND ;

not:
    | | NOT ;