1
# Copyright (C) 2010 Patrick Crews. All rights reserved.
2
# Use is subject to license terms.
4
# This program is free software; you can redistribute it and/or modify
5
# it under the terms of the GNU General Public License as published by
6
# the Free Software Foundation; version 2 of the License.
8
# This program is distributed in the hope that it will be useful, but
9
# WITHOUT ANY WARRANTY; without even the implied warranty of
10
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
11
# General Public License for more details.
13
# You should have received a copy of the GNU General Public License
14
# along with this program; if not, write to the Free Software
15
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301
17
# drizzledump_migrate.yy
18
# grammar for generating test beds for testing drizzledump's ability
19
# to assist with migration from MySQL databases
20
# This grammar is intended to be run against a MySQL server, the
21
# accompanying Validator will attempt to migrate the MySQL data
22
# to a Drizzle validation server when signalled.
24
# TODO: -Add ability to CREATE/DROP/populate additional databases
25
# -Creation of Foreign Keys
26
# -other deviltry as it comes to mind
27
# -Update grammar for use with other data type columns (mediumint, etc)
32
DROP DATABASE IF EXISTS drizzledump_db ; CREATE DATABASE drizzledump_db ; USE drizzledump_db ; create_test_table_list ; SELECT 1 ;
34
create_test_table_list:
35
# rule for picking one or more tables from the initial test bed
36
# sub-rules handle table population and composition
37
create_test_table_list ; create_test_table_set |
38
create_test_table_list ; create_test_table_set |
39
create_test_table_set ; create_test_table_set ;
40
# create_test_table_set ; create_test_table_set ; create_test_table_set ; create_test_table_set ; create_test_table_set ; create_test_table_set ; create_test_table_set ;
42
create_test_table_set:
43
select_set | like_set ;
46
create_table_like ; populate_table ; modify_table ;
49
create_table_select ; modify_table2 ;
52
# even though all test tables have the same columns, they are created in
53
# different orders, so we randomly choose a table from the test db
54
# to enhance randomness / alter the composition of our test tables
55
CREATE TABLE new_test_table LIKE `test` . _table ;
58
# We fill the test table with rows SELECT'ed from the
59
# existing tables in the test db
63
insert_query_list ; insert_query | insert_query | insert_query ;
66
# We work on one test table at a time, thus we use the $tables variable to let us
67
# reference the current table (started in the create_table rule) here
68
INSERT INTO {"dump_table".$tables } ( insert_column_list ) SELECT insert_column_list FROM `test` . _table insert_where_clause LIMIT small_digit;
71
# we use a WHERE clause on the populating SELECT to increase randomness
75
# We use a set column list because even though all tables have the same
76
# columns, each table has a different order of those columns for
78
`col_mediumint`, `col_mediumint_not_null_key`, `col_mediumint_key`, `col_mediumint_not_null`,
79
`col_char_128_not_null_key`, `col_char_128_not_null`, `col_char_128_key`, `col_char_128`,
80
`col_smallint_key`, `col_smallint_not_null`, `col_smallint_not_null_key`, `col_smallint`,
81
`col_bigint_not_null`, `col_bigint`, `col_bigint_key`, `col_bigint_not_null_key`,
82
`col_enum_key`, `col_enum_not_null_key`, `col_enum_not_null`, `col_enum`,
83
`col_char_10`, `col_char_10_not_null`, `col_char_10_key`, `col_char_10_not_null_key`,
84
`col_int_key`, `col_int`, `col_int_not_null`, `col_int_not_null_key`,
85
`col_tinyint_not_null_key`, `col_tinyint_not_null`, `col_tinyint`, `col_tinyint_key` ,
86
`col_timestamp_not_null`, `col_timestamp_not_null_key`, `col_timestamp`, `col_timestamp_key`,
87
`col_datetime`, `col_datetime_key`, `col_datetime_not_null`, `col_datetime_not_null_key` ,
88
`col_year`, `col_year_key`, `col_year_not_null`, `col_year_not_null_key`,
89
`col_time`, `col_time_key`, `col_time_not_null_key`, `col_time_not_null` ,
90
`col_binary_5`, `col_binary_5_key`, `col_binary_5_not_null`, `col_binary_5_not_null_key`,
91
`col_varbinary_5`, `col_varbinary_5_key`, `col_varbinary_5_not_null`, #`col_varbinary_5_not_null_key` ,
92
`col_text`, `col_text_key`, `col_text_not_null_key`, `col_text_not_null` ;
95
# We alter the tables by ALTERing the table and DROPping COLUMNS
96
# we also include not dropping any columns as an option
97
# TODO: Allow for adding columns
98
# We set the list of droppable columns here so it'll be consistent
99
# during the query generation
100
# NOTE - we don't drop pk as our comparison function relies
101
# on the presence of a primary key (this is a bit of a cheat, perhaps)
103
# We are currently generating individual ALTER / DROP statements.
104
# While it would be nice to also generate compound / multi-DROP ALTER statements,
105
# we would need to ensure that we don't generate bad ALTER's otherwise we
106
# would have a lot of bad queries / not generate as many interesting
111
alter_table_list ; alter_table_item |
112
alter_table_list ; alter_table_item |
113
alter_table_list ; alter_table_item |
114
alter_table_item ; alter_table_item ; alter_table_item |
115
alter_table_item ; alter_table_item ; alter_table_item ; alter_table_item ;
118
ALTER TABLE { "dump_table".$tables } DROP drop_column_name ;
121
`col_char_10` | `col_char_10_key` | `col_char_10_not_null` | `col_char_10_not_null_key` |
122
`col_char_128` | `col_char_128_key` | `col_char_128_not_null` | `col_char_128_not_null_key` |
123
`col_int` | `col_int_key` | `col_int_not_null` | `col_int_not_null_key` |
124
`col_bigint` | `col_bigint_key` | `col_bigint_not_null` | `col_bigint_not_null_key` |
125
`col_enum` | `col_enum_key` | `col_enum_not_null` | `col_enum_not_null_key` |
126
`col_text` | `col_text_key` | `col_text_not_null` | `col_text_not_null_key` ;
129
# alternate test-table generation rule that uses CREATE TABLE...SELECT
130
# for generating tables and data
131
CREATE TABLE new_test_table ( `pk` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(`pk`)) select_statement ;
134
# We have to alias each column in the column list, so we limit ourselves to a 2-table join
135
# for simplicity. We can perhaps look into more complex joins later
136
SELECT column_list FROM `test`. _table AS t1 , `test`. _table AS t2 ;
139
t1 . `col_char_10` AS field0 ,
140
t1 . `col_char_10_key` AS field1 ,
141
t1 . `col_char_10_not_null` AS field2 ,
142
t1 . `col_char_10_not_null_key` AS field3 ,
143
t1 . `col_char_128` AS field4 ,
144
t1 . `col_char_128_key` AS field5 ,
145
t1 . `col_char_128_not_null` AS field6 ,
146
t1 . `col_char_128_not_null_key` AS field7 ,
147
t1 . `col_int` AS field8 ,
148
t1 . `col_int_key` AS field9 ,
149
t1 . `col_int_not_null` AS field10 ,
150
t1 . `col_int_not_null_key` AS field11 ,
151
t1 . `col_bigint` AS field12 ,
152
t1 . `col_bigint_key` AS field13 ,
153
t1 . `col_bigint_not_null` AS field14 ,
154
t1 . `col_bigint_not_null_key` AS field15 ,
155
t1 . `col_enum` AS field16 ,
156
t1 . `col_enum_key` AS field17 ,
157
t1 . `col_enum_not_null` AS field18 ,
158
t1 . `col_enum_not_null_key` AS field19 ,
159
t1 . `col_text` AS field20 ,
160
t1 . `col_text_key` AS field21 ,
161
t1 . `col_text_not_null` AS field22 ,
162
t1 . `col_text_not_null_key` AS field23 ,
163
t2 . `col_char_10` AS field24 ,
164
t2 . `col_char_10_key` AS field25 ,
165
t2 . `col_char_10_not_null` AS field26 ,
166
t2 . `col_char_10_not_null_key` AS field27 ,
167
t2 . `col_int` AS field32 ,
168
t2 . `col_int_key` AS field33 ,
169
t2 . `col_int_not_null` AS field34 ,
170
t2 . `col_int_not_null_key` AS field35 ,
171
t2 . `col_enum` AS field40 ,
172
t2 . `col_enum_key` AS field41 ,
173
t2 . `col_enum_not_null` AS field42 ,
174
t2 . `col_enum_not_null_key` AS field43 ;
181
alter_table_list2 ; alter_table_item2 | alter_table_item2 ;
184
ALTER TABLE { "dump_table".$tables } DROP { "field".$prng->int(0,47) } ;
188
# This rule should generate tables to be dumped named dump_table1, dump_table2, etc
189
{ "dump_table".++$tables } ;
193
# we hack the _table rule a bit here to ensure we have a majority of populated tables being used