Merge lp:~kalebral-deactivatedaccount/randgen/add-drizzle-config-files into lp:randgen

[Lee Bieber]

[Lee Bieber (kalebral-deactivatedaccount)]

Added some drizzle configuration files

[Bernt M Johnsen (bernt-johnsen)]

ok.

review: Approve

=== added file 'conf/collations_drizzle.yy'
--- conf/collations_drizzle.yy	1970-01-01 00:00:00 +0000
+++ conf/collations_drizzle.yy	2010-01-31 04:22:11 +0000
@@ -0,0 +1,22 @@
+query:
+	delete | insert | update ;
+
+xid_event:
+	START TRANSACTION | COMMIT ;
+insert:
+	INSERT INTO table_name ( field_name ) VALUES ( ' letter ' ) ;
+
+update:
+	UPDATE table_name ' letter ' WHERE field_name oper ' letter ';
+
+delete:
+	DELETE FROM table_name WHERE field_name oper ' letter ';
+
+table_name:
+	_table ;
+
+field_name:
+	`col_varchar_key` | `col_varchar_nokey` ;
+
+oper:
+	= | > | < | >= | <= | <> ;

=== added file 'conf/outer_join_drizzle.zz'
--- conf/outer_join_drizzle.zz	1970-01-01 00:00:00 +0000
+++ conf/outer_join_drizzle.zz	2010-01-31 04:22:10 +0000
@@ -0,0 +1,16 @@
+$tables = {
+        names => ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 'AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG', 'HH', 'II', 'JJ', 'KK', 'LL', 'MM', 'NN', 'OO', 'PP'],
+        rows => [0, 1, 2, 5, 6, 7, 8, 9, 10, 20, 21, 22, 23, 24, 25, 100],
+        engines => ['Innodb']
+};
+
+$fields = {
+        types => [ 'int', 'varchar(10)', 'varchar(1024)' ],
+        indexes => [undef, 'key' ],
+        null => [undef ]
+};
+
+$data => {
+        numbers => [ 'digit', 'null', undef ],
+        strings => [ 'letter', 'english' , 'string(1024)']
+};

=== added file 'conf/range_access_drizzle.yy'
--- conf/range_access_drizzle.yy	1970-01-01 00:00:00 +0000
+++ conf/range_access_drizzle.yy	2010-01-31 04:22:11 +0000
@@ -0,0 +1,373 @@
+################################################################################
+# 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 ;
+

=== added file 'conf/range_access_drizzle.zz'
--- conf/range_access_drizzle.zz	1970-01-01 00:00:00 +0000
+++ conf/range_access_drizzle.zz	2010-01-31 04:22:11 +0000
@@ -0,0 +1,17 @@
+$tables = {
+        names => ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z', 'AA', 'BB', 'CC', 'DD', 'EE', 'FF', 'GG', 'HH', 'II', 'JJ', 'KK', 'LL', 'MM', 'NN', 'OO', 'PP'],
+        rows => [0, 1, 10, 20, 25, 50, , 75, 100],
+        engines => [ 'Innodb' ]
+};
+
+$fields = {
+        types => [ 'int', 'varchar(10)', 'varchar(1024)' , 'date' , 'datetime'],
+        indexes => [undef, 'key' ],
+        null => [undef ],
+        charsets => ['utf8']
+};
+
+$data => {
+        numbers => [ 'digit', 'null', undef ],
+        strings => [ 'letter', 'english' , 'string(1024)', 'quid' ]
+};