projects / dbsrgits/SQL-Translator-2.0-ish.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
add constraints after all tables are added
[dbsrgits/SQL-Translator-2.0-ish.git] / t / 02mysql-parser.t
CommitLineData
dbca887e 1use strict;
2e718597 2use warnings;
dbca887e 3use Test::More;
4use SQL::Translator;
dbca887e 5use SQL::Translator::Constants qw(:sqlt_types :sqlt_constants);
dbca887e 6
7{
8 my $tr = SQL::Translator->new({ from => 'MySQL' });
9 my $data = q|create table "sessions" (
10 id char(32) not null default '0' primary key,
11 a_session text,
12 ssn varchar(12) unique key,
13 age int key,
14 fulltext key `session_fulltext` (a_session)
15 );|;
16
7dcf6bc2 17 my $val = $tr->parse($data);
18 my $schema = $tr->schema;
dbca887e 19 is( $schema->is_valid, 1, 'Schema is valid' );
20 my @tables = $schema->get_tables;
21 is( scalar @tables, 1, 'Right number of tables (1)' );
22 my $table = shift @tables;
23 is( $table->name, 'sessions', 'Found "sessions" table' );
24
25 my @fields = $table->get_fields;
26 is( scalar @fields, 4, 'Right number of fields (4)' );
27
28 my $f1 = shift @fields;
29 my $f2 = shift @fields;
30 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 31 is( $f1->data_type, 'char', 'Type is "char"' );
dbca887e 32 is( $f1->size, 32, 'Size is "32"' );
33 is( $f1->is_nullable, 0, 'Field cannot be null' );
34 is( $f1->default_value, '0', 'Default value is "0"' );
35 is( $f1->is_primary_key, 1, 'Field is PK' );
36
37 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
92a61e09 38 is( $f2->data_type, 'text', 'Type is "text"' );
f35e598e 39 is( $f2->size, 0, 'Size is "0"' );
dbca887e 40 is( $f2->is_nullable, 1, 'Field can be null' );
41 is( $f2->default_value, undef, 'Default value is undefined' );
42 is( $f2->is_primary_key, 0, 'Field is not PK' );
43
44 my @indices = $table->get_indices;
45 is( scalar @indices, 2, 'Right number of indices (2)' );
46
47 my $i = pop @indices;
48 is( $i->type, 'FULLTEXT', 'Found fulltext' );
49
50 my @constraints = $table->get_constraints;
51 is( scalar @constraints, 2, 'Right number of constraints (2)' );
92a61e09 52 my $c1 = shift @constraints;
53 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
54 is( join(',', $c1->fields), 'id', 'Constraint is on "id"' );
dbca887e 55 my $c2 = shift @constraints;
56 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
57 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
58}
59
60{
61 my $tr = SQL::Translator->new({ from => 'MySQL' });
7dcf6bc2 62 my $val = $tr->parse(
dbca887e 63 q[
64 CREATE TABLE `check` (
65 check_id int(7) unsigned zerofill NOT NULL default '0000000'
66 auto_increment primary key,
67 successful date NOT NULL default '0000-00-00',
68 unsuccessful date default '0000-00-00',
69 i1 int(11) default '0' not null,
70 s1 set('a','b','c') default 'b',
71 e1 enum('a','b','c') default 'c',
72 name varchar(30) default NULL,
73 foo_type enum('vk','ck') NOT NULL default 'vk',
74 date timestamp,
75 time_stamp2 timestamp,
76 KEY (i1),
77 UNIQUE (date, i1),
78 KEY date_idx (date),
7dcf6bc2 79 KEY name_idx (name)
dbca887e 80 ) TYPE=MyISAM PACK_KEYS=1;
81 ]
7dcf6bc2 82 ); ## KEY name_idx (name(10))
dbca887e 83
7dcf6bc2 84 my $schema = $tr->schema;
dbca887e 85 is( $schema->is_valid, 1, 'Schema is valid' );
86 my @tables = $schema->get_tables;
87 is( scalar @tables, 1, 'Right number of tables (1)' );
88 my $table = shift @tables;
89 is( $table->name, 'check', 'Found "check" table' );
90
91 my @fields = $table->get_fields;
92 is( scalar @fields, 10, 'Right number of fields (10)' );
93 my $f1 = shift @fields;
94 is( $f1->name, 'check_id', 'First field name is "check_id"' );
92a61e09 95 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 96 is( $f1->size, 7, 'Size is "7"' );
97 is( $f1->is_nullable, 0, 'Field cannot be null' );
98 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
99 is( $f1->is_primary_key, 1, 'Field is PK' );
100 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
101 my %extra = $f1->extra;
102 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
103 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
104
105 my $f2 = shift @fields;
106 is( $f2->name, 'successful', 'Second field name is "successful"' );
107 is( $f2->data_type, 'date', 'Type is "date"' );
108 is( $f2->size, 0, 'Size is "0"' );
109 is( $f2->is_nullable, 0, 'Field cannot be null' );
110 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
111 is( $f2->is_primary_key, 0, 'Field is not PK' );
112
113 my $f3 = shift @fields;
114 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
115 is( $f3->data_type, 'date', 'Type is "date"' );
116 is( $f3->size, 0, 'Size is "0"' );
117 is( $f3->is_nullable, 1, 'Field can be null' );
118 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
119 is( $f3->is_primary_key, 0, 'Field is not PK' );
120
121 my $f4 = shift @fields;
122 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
92a61e09 123 is( $f4->data_type, 'int', 'Type is "int"' );
dbca887e 124 is( $f4->size, 11, 'Size is "11"' );
125 is( $f4->is_nullable, 0, 'Field cannot be null' );
126 is( $f4->default_value, '0', 'Default value is "0"' );
127 is( $f4->is_primary_key, 0, 'Field is not PK' );
128
129 my $f5 = shift @fields;
130 is( $f5->name, 's1', 'Fifth field name is "s1"' );
131 is( $f5->data_type, 'set', 'Type is "set"' );
f35e598e 132 is( $f5->size, 0, 'Size is "0"' );
dbca887e 133 is( $f5->is_nullable, 1, 'Field can be null' );
134 is( $f5->default_value, 'b', 'Default value is "b"' );
135 is( $f5->is_primary_key, 0, 'Field is not PK' );
136 my %f5extra = $f5->extra;
137 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
138
139 my $f6 = shift @fields;
140 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
141 is( $f6->data_type, 'enum', 'Type is "enum"' );
f35e598e 142 is( $f6->size, 0, 'Size is "0"' );
dbca887e 143 is( $f6->is_nullable, 1, 'Field can be null' );
144 is( $f6->default_value, 'c', 'Default value is "c"' );
145 is( $f6->is_primary_key, 0, 'Field is not PK' );
146 my %f6extra = $f6->extra;
147 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
148
149 my $f7 = shift @fields;
150 is( $f7->name, 'name', 'Seventh field name is "name"' );
151 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
152 is( $f7->size, 30, 'Size is "30"' );
153 is( $f7->is_nullable, 1, 'Field can be null' );
154 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
155 is( $f7->is_primary_key, 0, 'Field is not PK' );
156
157 my $f8 = shift @fields;
158 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
159 is( $f8->data_type, 'enum', 'Type is "enum"' );
f35e598e 160 is( $f8->size, 0, 'Size is "0"' );
dbca887e 161 is( $f8->is_nullable, 0, 'Field cannot be null' );
162 is( $f8->default_value, 'vk', 'Default value is "vk"' );
163 is( $f8->is_primary_key, 0, 'Field is not PK' );
164 my %f8extra = $f8->extra;
165 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
166
167 my $f9 = shift @fields;
168 is( $f9->name, 'date', 'Ninth field name is "date"' );
169 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
170 is( $f9->size, 0, 'Size is "0"' );
171 is( $f9->is_nullable, 1, 'Field can be null' );
172 is( $f9->default_value, undef, 'Default value is undefined' );
173 is( $f9->is_primary_key, 0, 'Field is not PK' );
174
175 my $f10 = shift @fields;
176 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
177 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
178 is( $f10->size, 0, 'Size is "0"' );
179 is( $f10->is_nullable, 1, 'Field can be null' );
180 is( $f10->default_value, undef, 'Default value is undefined' );
181 is( $f10->is_primary_key, 0, 'Field is not PK' );
182
183 my @indices = $table->get_indices;
184 is( scalar @indices, 3, 'Right number of indices (3)' );
185
186 my $i1 = shift @indices;
187 is( $i1->name, '', 'No name on index' );
188 is( $i1->type, NORMAL, 'Normal index' );
189 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
190
191 my $i2 = shift @indices;
192 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
193 is( $i2->type, NORMAL, 'Normal index' );
194 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
195
196 my $i3 = shift @indices;
197 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
198 is( $i3->type, NORMAL, 'Normal index' );
8149a03a 199 is( join(',', $i3->fields ), 'name', 'Index is on field "name(10)"' ); ## FIX ME
dbca887e 200
201 my @constraints = $table->get_constraints;
202 is( scalar @constraints, 2, 'Right number of constraints (2)' );
203
204 my $c1 = shift @constraints;
205 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
206 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
207
208 my $c2 = shift @constraints;
209 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
210 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
211}
212
213{
7dcf6bc2 214 my $tr = SQL::Translator->new({ from => 'MySQL' });
215 my $val = $tr->parse(
dbca887e 216 q[
217 CREATE TABLE orders (
218 order_id integer NOT NULL auto_increment,
219 member_id varchar(255) comment 'fk to member',
220 billing_address_id int,
221 shipping_address_id int,
222 credit_card_id int,
223 status smallint NOT NULL,
224 store_id varchar(255) NOT NULL REFERENCES store,
225 tax decimal(8,2),
226 shipping_charge decimal(8,2),
227 price_paid decimal(8,2),
228 PRIMARY KEY (order_id),
229 KEY (status),
230 KEY (billing_address_id),
231 KEY (shipping_address_id),
232 KEY (member_id, store_id),
233 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
234 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
235 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
236 ) TYPE=INNODB COMMENT = 'orders table comment';
237
238 CREATE TABLE address (
239 address_id int NOT NULL auto_increment,
240 recipient varchar(255) NOT NULL,
241 address1 varchar(255) NOT NULL,
242 address2 varchar(255),
243 city varchar(255) NOT NULL,
244 state varchar(255) NOT NULL,
245 postal_code varchar(255) NOT NULL,
246 phone varchar(255),
247 PRIMARY KEY (address_id)
248 ) TYPE=INNODB;
0510f676 249
250 CREATE TABLE store (
251 id int NOT NULL auto_increment,
252 name varchar(255) NOT NULL
253 ) TYPE=INNODB;
254
255 CREATE TABLE order_status (
256 id int NOT NULL,
257 status varchar(255) NOT NULL
258 ) TYPE=INNODB;
dbca887e 259 ]
260 ) or die $tr->error;
261
262 my $schema = $tr->schema;
263 is( $schema->is_valid, 1, 'Schema is valid' );
264 my @tables = $schema->get_tables;
9b0e2cdb 265 is( scalar @tables, 4, 'Right number of tables (4)' );
dbca887e 266
267 my $t1 = shift @tables;
268 is( $t1->name, 'orders', 'Found "orders" table' );
269 is( $t1->comments, 'orders table comment', 'Table comment OK' );
270
271 my @fields = $t1->get_fields;
272 is( scalar @fields, 10, 'Right number of fields (10)' );
273
274 my $f1 = shift @fields;
275 is( $f1->name, 'order_id', 'First field name is "order_id"' );
f35e598e 276 is( $f1->data_type, 'integer', 'Type is "integer"' );
277 is( $f1->size, 0, 'Size is "0"' );
dbca887e 278 is( $f1->is_nullable, 0, 'Field cannot be null' );
279 is( $f1->default_value, undef, 'Default value is undefined' );
280 is( $f1->is_primary_key, 1, 'Field is PK' );
281 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
282
283 my $f2 = shift @fields;
284 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
285 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
286 is( $f2->size, 255, 'Size is "255"' );
287 is( $f2->is_nullable, 1, 'Field can be null' );
288 is( $f2->comments, 'fk to member', 'Field comment OK' );
289 is( $f2->default_value, undef, 'Default value is undefined' );
290
291 my $f3 = shift @fields;
292 is( $f3->name, 'billing_address_id',
293 'Third field name is "billing_address_id"' );
92a61e09 294 is( $f3->data_type, 'int', 'Type is "int"' );
f35e598e 295 is( $f3->size, 0, 'Size is "0"' );
dbca887e 296
297 my $f4 = shift @fields;
298 is( $f4->name, 'shipping_address_id',
299 'Fourth field name is "shipping_address_id"' );
92a61e09 300 is( $f4->data_type, 'int', 'Type is "int"' );
f35e598e 301 is( $f4->size, 0, 'Size is "0"' );
dbca887e 302
303 my $f5 = shift @fields;
304 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
92a61e09 305 is( $f5->data_type, 'int', 'Type is "int"' );
f35e598e 306 is( $f5->size, 0, 'Size is "0"' );
dbca887e 307
308 my $f6 = shift @fields;
309 is( $f6->name, 'status', 'Sixth field name is "status"' );
310 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
f35e598e 311 is( $f6->size, 0, 'Size is "0"' );
dbca887e 312 is( $f6->is_nullable, 0, 'Field cannot be null' );
313
314 my $f7 = shift @fields;
315 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
316 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
317 is( $f7->size, 255, 'Size is "255"' );
318 is( $f7->is_nullable, 0, 'Field cannot be null' );
319 is( $f7->is_foreign_key, 1, 'Field is a FK' );
320 my $fk_ref = $f7->foreign_key_reference;
1d8914e7 321 isa_ok( $fk_ref, 'SQL::Translator::Object::Constraint', 'FK' );
dbca887e 322 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
323
324 my $f8 = shift @fields;
325 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
326 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
327 is( $f8->size, '8,2', 'Size is "8,2"' );
328
329 my $f9 = shift @fields;
330 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
331 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
332 is( $f9->size, '8,2', 'Size is "8,2"' );
333
334 my $f10 = shift @fields;
335 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
336 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
337 is( $f10->size, '8,2', 'Size is "8,2"' );
338
339 my @indices = $t1->get_indices;
340 is( scalar @indices, 4, 'Right number of indices (4)' );
341
342 my $i1 = shift @indices;
343 is( $i1->type, NORMAL, 'First index is normal' );
344 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
345
346 my $i2 = shift @indices;
347 is( $i2->type, NORMAL, 'Second index is normal' );
348 is( join(',', $i2->fields), 'billing_address_id',
349 'Index is on "billing_address_id"' );
350
351 my $i3 = shift @indices;
352 is( $i3->type, NORMAL, 'Third index is normal' );
353 is( join(',', $i3->fields), 'shipping_address_id',
354 'Index is on "shipping_address_id"' );
355
356 my $i4 = shift @indices;
357 is( $i4->type, NORMAL, 'Third index is normal' );
358 is( join(',', $i4->fields), 'member_id,store_id',
359 'Index is on "member_id,store_id"' );
360
361 my @constraints = $t1->get_constraints;
362 is( scalar @constraints, 5, 'Right number of constraints (5)' );
363
364 my $c1 = shift @constraints;
365 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
366 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
367
368 my $c2 = shift @constraints;
369 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
370 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
371 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
372 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
373
374 my $c3 = shift @constraints;
375 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
376 is( join(',', $c3->fields), 'billing_address_id',
377 'Constraint is on "billing_address_id"' );
378 is( $c3->reference_table, 'address', 'To table "address"' );
379 is( join(',', $c3->reference_fields), 'address_id',
380 'To field "address_id"' );
381
382 my $c4 = shift @constraints;
383 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
384 is( join(',', $c4->fields), 'shipping_address_id',
385 'Constraint is on "shipping_address_id"' );
386 is( $c4->reference_table, 'address', 'To table "address"' );
387 is( join(',', $c4->reference_fields), 'address_id',
388 'To field "address_id"' );
389
390 my $c5 = shift @constraints;
391 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
392 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
393 is( $c5->reference_table, 'store', 'To table "store"' );
394 is( join(',', map { $_ || '' } $c5->reference_fields), '',
395 'No reference fields defined' );
396
397 my $t2 = shift @tables;
398 is( $t2->name, 'address', 'Found "address" table' );
399
400 my @t2_fields = $t2->get_fields;
401 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
402}
403
404# djh Tests for:
405# USE database ;
406# ALTER TABLE ADD FOREIGN KEY
407# trailing comma on last create definition
408# Ignoring INSERT statements
409#
410{
7dcf6bc2 411 my $tr = SQL::Translator->new({ from => 'MySQL' });
412 my $data = $tr->parse(
dbca887e 413 q[
414 USE database_name;
415
416 CREATE TABLE one (
417 id integer NOT NULL auto_increment,
418 two_id integer NOT NULL auto_increment,
419 some_data text,
420 PRIMARY KEY (id),
421 INDEX (two_id),
422 ) TYPE=INNODB;
423
424 CREATE TABLE two (
425 id int NOT NULL auto_increment,
426 one_id int NOT NULL auto_increment,
427 some_data text,
428 PRIMARY KEY (id),
429 INDEX (one_id),
430 FOREIGN KEY (one_id) REFERENCES one (id),
431 ) TYPE=INNODB;
432
433 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
434
435 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
436 ]
437 ) or die $tr->error;
438
439 my $schema = $tr->schema;
440 is( $schema->is_valid, 1, 'Schema is valid' );
441 my $db_name = $schema->name;
442 is( $db_name, 'database_name', 'Database name extracted from USE' );
443 my @tables = $schema->get_tables;
444 is( scalar @tables, 2, 'Right number of tables (2)' );
445 my $table1 = shift @tables;
446 is( $table1->name, 'one', 'Found "one" table' );
447 my $table2 = shift @tables;
448 is( $table2->name, 'two', 'Found "two" table' );
449
450 my @constraints = $table1->get_constraints;
451 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
452
453 my $t1c1 = shift @constraints;
454 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
455 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
456
457 my $t1c2 = shift @constraints;
458 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
459 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
460 is( $t1c2->reference_table, 'two', 'To table "two"' );
461 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
462
463 @constraints = $table2->get_constraints;
464 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
465
466 my $t2c1 = shift @constraints;
467 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
468 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
469
470 my $t2c2 = shift @constraints;
471 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
472 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
473 is( $t2c2->reference_table, 'one', 'To table "one"' );
474 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
475}
476
477# cch Tests for:
478# comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
479# char fields with character set and collate qualifiers
480# timestamp fields with on update qualifier
481# charset table option
482#
483{
7dcf6bc2 484 my $tr = SQL::Translator->new({ from => 'MySQL', parser_args => { mysql_parser_version => 50003 } });
485 my $data = $tr->parse(
486 q[
dbca887e 487 DELIMITER ;;
488 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
489 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
490 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
491 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
492 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
493
494 DELIMITER ;
495 CREATE TABLE one (
496 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
497 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
498 ) TYPE=INNODB DEFAULT CHARSET=latin1;
499
500 /*!50001 CREATE ALGORITHM=UNDEFINED */
501 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
502 /*! VIEW `vs_asset` AS
503 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
504 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
505 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
506 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
507 `a`.`foreign_asset_id` AS `foreign_asset_id`,
508 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
509 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
510 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
511 `m`.`user_id` AS `user_access`
512 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
513 DELIMITER ;;
514 /*!50001 CREATE */
515 /*! VIEW `vs_asset2` AS
516 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
517 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
518 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
519 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
520 `a`.`foreign_asset_id` AS `foreign_asset_id`,
521 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
522 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
523 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
524 `m`.`user_id` AS `user_access`
525 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
526 DELIMITER ;;
527 /*!50001 CREATE OR REPLACE */
528 /*! VIEW `vs_asset3` AS
529 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
530 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
531 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
532 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
533 `a`.`foreign_asset_id` AS `foreign_asset_id`,
534 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
535 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
536 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
537 `m`.`user_id` AS `user_access`
538 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
539 DELIMITER ;;
540 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
541 DETERMINISTIC
542 BEGIN
543 DECLARE rval TIMESTAMP;
544 IF ( millis_since_1970 = 0 )
545 THEN
546 SET rval = NULL;
547 ELSE
548 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
549 END IF;
550 RETURN rval;
551 END */;;
552 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
553 BEGIN
554 DECLARE hasMoreRows BOOL DEFAULT TRUE;
555 DECLARE t_group_id INT;
556 DECLARE t_user_id INT ;
557 DECLARE t_user_name VARCHAR (512) ;
558 DECLARE t_message VARCHAR (512) ;
559
560 DROP TABLE IF EXISTS group_acl;
561 DROP TABLE IF EXISTS user_group;
562 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
563
564 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
565 FROM asset d, acl_entry e, alterpoint_principal p
566 WHERE d.acl_id = e.acl
567 AND p.id = e.principal AND d.acl_id = t_acl_id;
568
569 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
570 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
571 WHERE a.id = b.user_ref AND b.elt = c.id;
572
573 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
574 FROM group_acl, user_group
575 WHERE group_acl.group_id = user_group.group_id ;
576 END */;;
577 ]
578 ) or die $tr->error;
579
580 my $schema = $tr->schema;
581 is( $schema->is_valid, 1, 'Schema is valid' );
582 my @tables = $schema->get_tables;
583 is( scalar @tables, 1, 'Right number of tables (1)' );
584 my $table1 = shift @tables;
585 is( $table1->name, 'one', 'Found "one" table' );
586
587 my @fields = $table1->get_fields;
588 is(scalar @fields, 2, 'Right number of fields (2) on table one');
589 my $tableTypeFound = 0;
590 my $charsetFound = 0;
591 for my $t1_option_ref ( $table1->options ) {
592 my($key, $value) = %{$t1_option_ref};
593 if ( $key eq 'TYPE' ) {
594 is($value, 'INNODB', 'Table has right table type option' );
595 $tableTypeFound = 1;
596 } elsif ( $key eq 'CHARACTER SET' ) {
597 is($value, 'latin1', 'Table has right character set option' );
598 $charsetFound = 1;
599 }
600 }
601 fail('Table did not have a type option') unless $tableTypeFound;
602 fail('Table did not have a character set option') unless $charsetFound;
603
604 my $t1f1 = shift @fields;
605 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
606 is( $t1f1->size, 255, 'Field is right size' );
607 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
608 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
609 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
610
611 my $t1f2 = shift @fields;
612 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
613 ok( !$t1f2->is_nullable, 'Field is not nullable' );
614 is_deeply(
615 $t1f2->default_value,
616 \'CURRENT_TIMESTAMP',
617 'Field has right default value'
618 );
619 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
620
621 my @views = $schema->get_views;
622 is( scalar @views, 3, 'Right number of views (3)' );
623 my ($view3, $view1, $view2) = @views;
624 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
625 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
626 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
627 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
628 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
629 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
630
631 my @procs = $schema->get_procedures;
632 is( scalar @procs, 2, 'Right number of procedures (2)' );
633 my $proc1 = shift @procs;
634 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
635 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
636 my $proc2 = shift @procs;
637 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
638 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
639}
640
641# Tests for collate table option
642{
7dcf6bc2 643 my $tr = SQL::Translator->new({ from => 'MySQL', parser_args => { mysql_parser_version => 50003 } });
644 my $data = $tr->parse(
dbca887e 645 q[
646 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
647 ] );
648
649 my $schema = $tr->schema;
650 is( $schema->is_valid, 1, 'Schema is valid' );
651 my @tables = $schema->get_tables;
652 is( scalar @tables, 1, 'Right number of tables (1)' );
653 my $table1 = shift @tables;
654 is( $table1->name, 'test', 'Found "test" table' );
655
656
657 my $collate = "Not found!";
658 my $charset = "Not found!";
659 for my $t1_option_ref ( $table1->options ) {
660 my($key, $value) = %{$t1_option_ref};
661 $collate = $value if $key eq 'COLLATE';
662 $charset = $value if $key eq 'CHARACTER SET';
663 }
664 is($collate, 'latin1_bin', "Collate found");
665 is($charset, 'latin1', "Character set found");
666}
667
668# Test the mysql version parser (probably needs to migrate to t/utils.t)
669my $parse_as = {
670 perl => {
671 '3.23.2' => 3.023002,
672 '4' => 4.000000,
673 '50003' => 5.000003,
674 '5.01.0' => 5.001000,
675 '5.1' => 5.001000,
676 },
677 mysql => {
678 '3.23.2' => 32302,
679 '4' => 40000,
680 '50003' => 50003,
681 '5.01.0' => 50100,
682 '5.1' => 50100,
683 },
684};
685
deb9f57f 686my $tr = SQL::Translator->new;
dbca887e 687for my $target (keys %$parse_as) {
688 for my $str (keys %{$parse_as->{$target}}) {
689 cmp_ok (
deb9f57f 690 $tr->engine_version($str, $target),
dbca887e 691 '==',
692 $parse_as->{$target}{$str},
693 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
694 );
695 }
696}
697
deb9f57f 698eval { $tr->engine_version ('bogus5.1') };
dbca887e 699ok ($@, 'Exception thrown on invalid version string');
700
701{
deb9f57f 702 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 703 my $data = q|create table merge_example (
704 id int(11) NOT NULL auto_increment,
705 shape_field geometry NOT NULL,
706 PRIMARY KEY (id),
707 SPATIAL KEY shape_field (shape_field)
708 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
709
7dcf6bc2 710 my $val = $tr->parse($data);
dbca887e 711 my $schema = $tr->schema;
712 is( $schema->is_valid, 1, 'Schema is valid' );
713 my @tables = $schema->get_tables;
714 is( scalar @tables, 1, 'Right number of tables (1)' );
715 my $table = shift @tables;
716 is( $table->name, 'merge_example', 'Found "merge_example" table' );
717
718 my $tableTypeFound = 0;
719 my $unionFound = 0;
720 for my $t_option_ref ( $table->options ) {
721 my($key, $value) = %{$t_option_ref};
722 if ( $key eq 'ENGINE' ) {
723 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
724 $tableTypeFound = 1;
725 } elsif ( $key eq 'UNION' ) {
726 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
727 "UNION option has correct set");
728 $unionFound = 1;
729 }
730 }
731
732 fail('Table did not have a type option') unless $tableTypeFound;
733 fail('Table did not have a union option') unless $unionFound;
734
735 my @fields = $table->get_fields;
736 is( scalar @fields, 2, 'Right number of fields (2)' );
737 my $f1 = shift @fields;
738 my $f2 = shift @fields;
739 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 740 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 741 is( $f1->size, 11, 'Size is "11"' );
742 is( $f1->is_nullable, 0, 'Field cannot be null' );
743 is( $f1->is_primary_key, 1, 'Field is PK' );
744
745 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
746 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
747 is( $f2->is_nullable, 0, 'Field cannot be null' );
748 is( $f2->is_primary_key, 0, 'Field is not PK' );
749
750 my @indices = $table->get_indices;
751 is( scalar @indices, 1, 'Right number of indices (1)' );
752 my $i1 = shift @indices;
753 is( $i1->name, 'shape_field', 'No name on index' );
754 is( $i1->type, SPATIAL, 'Spatial index' );
755
756 my @constraints = $table->get_constraints;
757 is( scalar @constraints, 1, 'Right number of constraints (1)' );
758 my $c = shift @constraints;
759 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
760 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
761}
762
763{
764 my @data = (
765 q|create table quote (
766 id int(11) NOT NULL auto_increment,
767 PRIMARY KEY (id)
768 ) ENGINE="innodb";|,
769 q|create table quote (
770 id int(11) NOT NULL auto_increment,
771 PRIMARY KEY (id)
772 ) ENGINE='innodb';|,
773 q|create table quote (
774 id int(11) NOT NULL auto_increment,
775 PRIMARY KEY (id)
776 ) ENGINE=innodb;|,
777 );
778 for my $data (@data) {
7dcf6bc2 779 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 780
7dcf6bc2 781 my $val = $tr->parse($data);
dbca887e 782 my $schema = $tr->schema;
783 is( $schema->is_valid, 1, 'Schema is valid' );
784 my @tables = $schema->get_tables;
785 is( scalar @tables, 1, 'Right number of tables (1)' );
786 my $table = shift @tables;
787 is( $table->name, 'quote', 'Found "quote" table' );
788
789 my $tableTypeFound = 0;
790 for my $t_option_ref ( $table->options ) {
791 my($key, $value) = %{$t_option_ref};
792 if ( $key eq 'ENGINE' ) {
793 is($value, 'innodb', 'Table has right table engine option' );
794 $tableTypeFound = 1;
795 }
796 }
797
798 fail('Table did not have a type option') unless $tableTypeFound;
799
800 my @fields = $table->get_fields;
801 my $f1 = shift @fields;
802 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 803 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 804 is( $f1->size, 11, 'Size is "11"' );
805 is( $f1->is_nullable, 0, 'Field cannot be null' );
806 is( $f1->is_primary_key, 1, 'Field is PK' );
807 }
808}
809
810{
7dcf6bc2 811 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 812 my $data = q|create table "sessions" (
813 id char(32) not null default '0' primary key,
814 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
815 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
816 );|;
817
7dcf6bc2 818 my $val = $tr->parse($data);
dbca887e 819 my $schema = $tr->schema;
820 is( $schema->is_valid, 1, 'Schema is valid' );
821 my @tables = $schema->get_tables;
822 is( scalar @tables, 1, 'Right number of tables (1)' );
823 my $table = shift @tables;
824 is( $table->name, 'sessions', 'Found "sessions" table' );
825
826 my @fields = $table->get_fields;
827 is( scalar @fields, 3, 'Right number of fields (3)' );
828 my $f1 = shift @fields;
829 my $f2 = shift @fields;
830 my $f3 = shift @fields;
831 is( $f1->name, 'id', 'First field name is "id"' );
832 is( $f1->data_type, 'char', 'Type is "char"' );
833 is( $f1->size, 32, 'Size is "32"' );
834 is( $f1->is_nullable, 0, 'Field cannot be null' );
835 is( $f1->default_value, '0', 'Default value is "0"' );
836 is( $f1->is_primary_key, 1, 'Field is PK' );
837
838 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
839 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
840 is( $f2->size, 12, 'Size is "12"' );
841 is( $f2->is_nullable, 0, 'Field can not be null' );
842 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
843 is( $f2->is_primary_key, 0, 'Field is not PK' );
844
845 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
846 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
847 is( $f3->name, 'user', 'Second field name is "user"' );
848 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
849 is( $f3->size, 20, 'Size is "20"' );
850 is( $f3->is_nullable, 0, 'Field can not be null' );
851 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
852 is( $f3->is_primary_key, 0, 'Field is not PK' );
853}
4177b4ea 854
855done_testing;
SQL::Translator (GSOC work)