9 use SQL::Translator::Schema::Constants;
10 use SQL::Translator::Utils qw//;
11 use Test::SQL::Translator qw(maybe_plan);
15 maybe_plan(undef, "SQL::Translator::Parser::MySQL");
16 SQL::Translator::Parser::MySQL->import('parse');
20 my $tr = SQL::Translator->new;
21 my $data = q|create table "sessions" (
22 id char(32) not null default '0' primary key,
24 ssn varchar(12) unique key,
26 fulltext key `session_fulltext` (a_session)
29 my $val = parse($tr, $data);
30 my $schema = $tr->schema;
31 is( $schema->is_valid, 1, 'Schema is valid' );
32 my @tables = $schema->get_tables;
33 is( scalar @tables, 1, 'Right number of tables (1)' );
34 my $table = shift @tables;
35 is( $table->name, 'sessions', 'Found "sessions" table' );
37 my @fields = $table->get_fields;
38 is( scalar @fields, 4, 'Right number of fields (4)' );
39 my $f1 = shift @fields;
40 my $f2 = shift @fields;
41 is( $f1->name, 'id', 'First field name is "id"' );
42 is( $f1->data_type, 'char', 'Type is "char"' );
43 is( $f1->size, 32, 'Size is "32"' );
44 is( $f1->is_nullable, 0, 'Field cannot be null' );
45 is( $f1->default_value, '0', 'Default value is "0"' );
46 is( $f1->is_primary_key, 1, 'Field is PK' );
48 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
49 is( $f2->data_type, 'text', 'Type is "text"' );
50 is( $f2->size, 65_535, 'Size is "65,535"' );
51 is( $f2->is_nullable, 1, 'Field can be null' );
52 is( $f2->default_value, undef, 'Default value is undefined' );
53 is( $f2->is_primary_key, 0, 'Field is not PK' );
55 my @indices = $table->get_indices;
56 is( scalar @indices, 2, 'Right number of indices (2)' );
58 is( $i->type, 'FULLTEXT', 'Found fulltext' );
60 my @constraints = $table->get_constraints;
61 is( scalar @constraints, 2, 'Right number of constraints (2)' );
62 my $c = shift @constraints;
63 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
64 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
65 my $c2 = shift @constraints;
66 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
67 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
71 my $tr = SQL::Translator->new;
74 CREATE TABLE `check` (
75 check_id int(7) unsigned zerofill NOT NULL default '0000000'
76 auto_increment primary key,
77 successful date NOT NULL default '0000-00-00',
78 unsuccessful date default '0000-00-00',
79 i1 int(11) default '0' not null,
80 s1 set('a','b','c') default 'b',
81 e1 enum("a","b","c") default "c",
82 name varchar(30) default NULL,
83 foo_type enum('vk','c''k') NOT NULL default 'vk',
85 time_stamp2 timestamp,
86 foo_enabled bit(1) default b'0',
87 bar_enabled bit(1) default b"1",
88 long_foo_enabled bit(10) default b'1010101',
90 UNIQUE (date, i1) USING BTREE,
92 KEY name_idx (name(10))
93 ) TYPE=MyISAM PACK_KEYS=1;
97 my $schema = $tr->schema;
98 is( $schema->is_valid, 1, 'Schema is valid' );
99 my @tables = $schema->get_tables;
100 is( scalar @tables, 1, 'Right number of tables (1)' );
101 my $table = shift @tables;
102 is( $table->name, 'check', 'Found "check" table' );
104 my @fields = $table->get_fields;
105 is( scalar @fields, 13, 'Right number of fields (13)' );
106 my $f1 = shift @fields;
107 is( $f1->name, 'check_id', 'First field name is "check_id"' );
108 is( $f1->data_type, 'int', 'Type is "int"' );
109 is( $f1->size, 7, 'Size is "7"' );
110 is( $f1->is_nullable, 0, 'Field cannot be null' );
111 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
112 is( $f1->is_primary_key, 1, 'Field is PK' );
113 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
114 my %extra = $f1->extra;
115 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
116 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
118 my $f2 = shift @fields;
119 is( $f2->name, 'successful', 'Second field name is "successful"' );
120 is( $f2->data_type, 'date', 'Type is "date"' );
121 is( $f2->size, 0, 'Size is "0"' );
122 is( $f2->is_nullable, 0, 'Field cannot be null' );
123 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
124 is( $f2->is_primary_key, 0, 'Field is not PK' );
126 my $f3 = shift @fields;
127 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
128 is( $f3->data_type, 'date', 'Type is "date"' );
129 is( $f3->size, 0, 'Size is "0"' );
130 is( $f3->is_nullable, 1, 'Field can be null' );
131 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
132 is( $f3->is_primary_key, 0, 'Field is not PK' );
134 my $f4 = shift @fields;
135 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
136 is( $f4->data_type, 'int', 'Type is "int"' );
137 is( $f4->size, 11, 'Size is "11"' );
138 is( $f4->is_nullable, 0, 'Field cannot be null' );
139 is( $f4->default_value, '0', 'Default value is "0"' );
140 is( $f4->is_primary_key, 0, 'Field is not PK' );
142 my $f5 = shift @fields;
143 is( $f5->name, 's1', 'Fifth field name is "s1"' );
144 is( $f5->data_type, 'set', 'Type is "set"' );
145 is( $f5->size, 1, 'Size is "1"' );
146 is( $f5->is_nullable, 1, 'Field can be null' );
147 is( $f5->default_value, 'b', 'Default value is "b"' );
148 is( $f5->is_primary_key, 0, 'Field is not PK' );
149 my %f5extra = $f5->extra;
150 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
152 my $f6 = shift @fields;
153 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
154 is( $f6->data_type, 'enum', 'Type is "enum"' );
155 is( $f6->size, 1, 'Size is "1"' );
156 is( $f6->is_nullable, 1, 'Field can be null' );
157 is( $f6->default_value, 'c', 'Default value is "c"' );
158 is( $f6->is_primary_key, 0, 'Field is not PK' );
159 my %f6extra = $f6->extra;
160 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
162 my $f7 = shift @fields;
163 is( $f7->name, 'name', 'Seventh field name is "name"' );
164 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
165 is( $f7->size, 30, 'Size is "30"' );
166 is( $f7->is_nullable, 1, 'Field can be null' );
167 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
168 is( $f7->is_primary_key, 0, 'Field is not PK' );
170 my $f8 = shift @fields;
171 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
172 is( $f8->data_type, 'enum', 'Type is "enum"' );
173 is( $f8->size, 3, 'Size is "2"' );
174 is( $f8->is_nullable, 0, 'Field cannot be null' );
175 is( $f8->default_value, 'vk', 'Default value is "vk"' );
176 is( $f8->is_primary_key, 0, 'Field is not PK' );
177 my %f8extra = $f8->extra;
178 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,c\'k', 'List is "vk,c\'k"' );
180 my $f9 = shift @fields;
181 is( $f9->name, 'date', 'Ninth field name is "date"' );
182 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
183 is( $f9->size, 0, 'Size is "0"' );
184 is( $f9->is_nullable, 1, 'Field can be null' );
185 is( $f9->default_value, undef, 'Default value is undefined' );
186 is( $f9->is_primary_key, 0, 'Field is not PK' );
188 my $f10 = shift @fields;
189 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
190 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
191 is( $f10->size, 0, 'Size is "0"' );
192 is( $f10->is_nullable, 1, 'Field can be null' );
193 is( $f10->default_value, undef, 'Default value is undefined' );
194 is( $f10->is_primary_key, 0, 'Field is not PK' );
196 my $f11 = shift @fields;
197 is( $f11->name, 'foo_enabled', 'Eleventh field name is "foo_enabled"' );
198 is( $f11->data_type, 'bit', 'Type is "bit"' );
199 is( $f11->size, 1, 'Size is "1"' );
200 is( $f11->is_nullable, 1, 'Field can be null' );
201 is( $f11->default_value, '0', 'Default value is 0' );
202 is( $f11->is_primary_key, 0, 'Field is not PK' );
204 my $f12 = shift @fields;
205 is( $f12->name, 'bar_enabled', 'Twelveth field name is "bar_enabled"' );
206 is( $f12->data_type, 'bit', 'Type is "bit"' );
207 is( $f12->size, 1, 'Size is "1"' );
208 is( $f12->is_nullable, 1, 'Field can be null' );
209 is( $f12->default_value, '1', 'Default value is 1' );
210 is( $f12->is_primary_key, 0, 'Field is not PK' );
212 my $f13 = shift @fields;
213 is( $f13->name, 'long_foo_enabled', 'Thirteenth field name is "long_foo_enabled"' );
214 is( $f13->data_type, 'bit', 'Type is "bit"' );
215 is( $f13->size, 10, 'Size is "10"' );
216 is( $f13->is_nullable, 1, 'Field can be null' );
217 is( $f13->default_value, '1010101', 'Default value is 1010101' );
218 is( $f13->is_primary_key, 0, 'Field is not PK' );
220 my @indices = $table->get_indices;
221 is( scalar @indices, 3, 'Right number of indices (3)' );
223 my $i1 = shift @indices;
224 is( $i1->name, '', 'No name on index' );
225 is( $i1->type, NORMAL, 'Normal index' );
226 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
228 my $i2 = shift @indices;
229 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
230 is( $i2->type, NORMAL, 'Normal index' );
231 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
233 my $i3 = shift @indices;
234 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
235 is( $i3->type, NORMAL, 'Normal index' );
236 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
238 my @constraints = $table->get_constraints;
239 is( scalar @constraints, 2, 'Right number of constraints (2)' );
241 my $c1 = shift @constraints;
242 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
243 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
245 my $c2 = shift @constraints;
246 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
247 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
251 my $tr = SQL::Translator->new;
252 my $data = parse($tr,
254 CREATE TABLE orders (
255 order_id integer NOT NULL comment ' ' auto_increment,
256 member_id varchar(255) comment 'fk to ''member''',
257 billing_address_id int,
258 shipping_address_id int,
260 status smallint NOT NULL,
261 store_id varchar(255) NOT NULL REFERENCES store,
263 shipping_charge decimal(8,2),
264 price_paid decimal(8,2),
265 PRIMARY KEY (order_id) USING BTREE,
266 KEY (status) USING BTREE,
267 KEY USING BTREE (billing_address_id),
268 KEY (shipping_address_id),
269 KEY (member_id, store_id),
270 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
271 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
272 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
273 ) TYPE=INNODB COMMENT = 'orders table comment';
275 CREATE TABLE address (
276 address_id int NOT NULL auto_increment,
277 recipient varchar(255) NOT NULL,
278 address1 varchar(255) NOT NULL,
279 address2 varchar(255),
280 city varchar(255) NOT NULL,
281 state varchar(255) NOT NULL,
282 postal_code varchar(255) NOT NULL,
284 PRIMARY KEY (address_id)
289 my $schema = $tr->schema;
290 is( $schema->is_valid, 1, 'Schema is valid' );
291 my @tables = $schema->get_tables;
292 is( scalar @tables, 2, 'Right number of tables (2)' );
294 my $t1 = shift @tables;
295 is( $t1->name, 'orders', 'Found "orders" table' );
296 is( $t1->comments, 'orders table comment', 'Table comment OK' );
298 my @fields = $t1->get_fields;
299 is( scalar @fields, 10, 'Right number of fields (10)' );
301 my $f1 = shift @fields;
302 is( $f1->name, 'order_id', 'First field name is "order_id"' );
303 is( $f1->data_type, 'int', 'Type is "int"' );
304 is( $f1->size, 11, 'Size is "11"' );
305 is( $f1->is_nullable, 0, 'Field cannot be null' );
306 is( $f1->default_value, undef, 'Default value is undefined' );
307 is( $f1->is_primary_key, 1, 'Field is PK' );
308 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
309 is_deeply( [$f1->comments],[' '], 'Field comment OK' );
311 my $f2 = shift @fields;
312 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
313 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
314 is( $f2->size, 255, 'Size is "255"' );
315 is( $f2->is_nullable, 1, 'Field can be null' );
316 is( $f2->comments, 'fk to \'member\'', 'Field comment OK' );
317 is( $f2->default_value, undef, 'Default value is undefined' );
319 my $f3 = shift @fields;
320 is( $f3->name, 'billing_address_id',
321 'Third field name is "billing_address_id"' );
322 is( $f3->data_type, 'int', 'Type is "int"' );
323 is( $f3->size, 11, 'Size is "11"' );
325 my $f4 = shift @fields;
326 is( $f4->name, 'shipping_address_id',
327 'Fourth field name is "shipping_address_id"' );
328 is( $f4->data_type, 'int', 'Type is "int"' );
329 is( $f4->size, 11, 'Size is "11"' );
331 my $f5 = shift @fields;
332 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
333 is( $f5->data_type, 'int', 'Type is "int"' );
334 is( $f5->size, 11, 'Size is "11"' );
336 my $f6 = shift @fields;
337 is( $f6->name, 'status', 'Sixth field name is "status"' );
338 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
339 is( $f6->size, 6, 'Size is "6"' );
340 is( $f6->is_nullable, 0, 'Field cannot be null' );
342 my $f7 = shift @fields;
343 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
344 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
345 is( $f7->size, 255, 'Size is "255"' );
346 is( $f7->is_nullable, 0, 'Field cannot be null' );
347 is( $f7->is_foreign_key, 1, 'Field is a FK' );
348 my $fk_ref = $f7->foreign_key_reference;
349 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
350 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
352 my $f8 = shift @fields;
353 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
354 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
355 is( $f8->size, '8,2', 'Size is "8,2"' );
357 my $f9 = shift @fields;
358 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
359 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
360 is( $f9->size, '8,2', 'Size is "8,2"' );
362 my $f10 = shift @fields;
363 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
364 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
365 is( $f10->size, '8,2', 'Size is "8,2"' );
367 my @indices = $t1->get_indices;
368 is( scalar @indices, 4, 'Right number of indices (4)' );
370 my $i1 = shift @indices;
371 is( $i1->type, NORMAL, 'First index is normal' );
372 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
374 my $i2 = shift @indices;
375 is( $i2->type, NORMAL, 'Second index is normal' );
376 is( join(',', $i2->fields), 'billing_address_id',
377 'Index is on "billing_address_id"' );
379 my $i3 = shift @indices;
380 is( $i3->type, NORMAL, 'Third index is normal' );
381 is( join(',', $i3->fields), 'shipping_address_id',
382 'Index is on "shipping_address_id"' );
384 my $i4 = shift @indices;
385 is( $i4->type, NORMAL, 'Third index is normal' );
386 is( join(',', $i4->fields), 'member_id,store_id',
387 'Index is on "member_id,store_id"' );
389 my @constraints = $t1->get_constraints;
390 is( scalar @constraints, 5, 'Right number of constraints (5)' );
392 my $c1 = shift @constraints;
393 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
394 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
396 my $c2 = shift @constraints;
397 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
398 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
399 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
400 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
402 my $c3 = shift @constraints;
403 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
404 is( join(',', $c3->fields), 'billing_address_id',
405 'Constraint is on "billing_address_id"' );
406 is( $c3->reference_table, 'address', 'To table "address"' );
407 is( join(',', $c3->reference_fields), 'address_id',
408 'To field "address_id"' );
410 my $c4 = shift @constraints;
411 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
412 is( join(',', $c4->fields), 'shipping_address_id',
413 'Constraint is on "shipping_address_id"' );
414 is( $c4->reference_table, 'address', 'To table "address"' );
415 is( join(',', $c4->reference_fields), 'address_id',
416 'To field "address_id"' );
418 my $c5 = shift @constraints;
419 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
420 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
421 is( $c5->reference_table, 'store', 'To table "store"' );
422 is( join(',', map { $_ || '' } $c5->reference_fields), '',
423 'No reference fields defined' );
425 my $t2 = shift @tables;
426 is( $t2->name, 'address', 'Found "address" table' );
428 my @t2_fields = $t2->get_fields;
429 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
434 # ALTER TABLE ADD FOREIGN KEY
435 # trailing comma on last create definition
436 # Ignoring INSERT statements
439 my $tr = SQL::Translator->new;
440 my $data = parse($tr,
445 id integer NOT NULL auto_increment,
446 two_id integer NOT NULL auto_increment,
453 id int NOT NULL auto_increment,
454 one_id int NOT NULL auto_increment,
458 FOREIGN KEY (one_id) REFERENCES one (id),
461 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
463 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
467 my $schema = $tr->schema;
468 is( $schema->is_valid, 1, 'Schema is valid' );
469 my $db_name = $schema->name;
470 is( $db_name, 'database_name', 'Database name extracted from USE' );
471 my @tables = $schema->get_tables;
472 is( scalar @tables, 2, 'Right number of tables (2)' );
473 my $table1 = shift @tables;
474 is( $table1->name, 'one', 'Found "one" table' );
475 my $table2 = shift @tables;
476 is( $table2->name, 'two', 'Found "two" table' );
478 my @constraints = $table1->get_constraints;
479 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
481 my $t1c1 = shift @constraints;
482 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
483 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
485 my $t1c2 = shift @constraints;
486 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
487 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
488 is( $t1c2->reference_table, 'two', 'To table "two"' );
489 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
491 @constraints = $table2->get_constraints;
492 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
494 my $t2c1 = shift @constraints;
495 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
496 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
498 my $t2c2 = shift @constraints;
499 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
500 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
501 is( $t2c2->reference_table, 'one', 'To table "one"' );
502 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
506 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
507 # char fields with character set and collate qualifiers
508 # timestamp fields with on update qualifier
509 # charset table option
512 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50013});
513 my $data = parse($tr,
516 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
517 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
518 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
519 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
520 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
524 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
525 `last_modified` timestamp NOT NULL default Current_Timestamp on update CURRENT_TIMESTAMP,
526 `created_at` datetime NOT NULL Default CURRENT_TIMESTAMP(),
527 ) TYPE=INNODB DEFAULT CHARSET=latin1;
529 /*!50001 CREATE ALGORITHM=UNDEFINED */
530 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
531 /*!50014 DEFINER=`BOGUS` */
532 /*! VIEW `vs_asset` AS
533 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
534 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
535 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
536 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
537 `a`.`foreign_asset_id` AS `foreign_asset_id`,
538 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
539 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
540 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
541 `m`.`user_id` AS `user_access`
542 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
545 /*! VIEW `vs_asset2` AS
546 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
547 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
548 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
549 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
550 `a`.`foreign_asset_id` AS `foreign_asset_id`,
551 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
552 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
553 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
554 `m`.`user_id` AS `user_access`
555 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
557 /*!50001 CREATE OR REPLACE */
558 /*! VIEW `vs_asset3` AS
559 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
560 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
561 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
562 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
563 `a`.`foreign_asset_id` AS `foreign_asset_id`,
564 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
565 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
566 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
567 `m`.`user_id` AS `user_access`
568 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
570 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
573 DECLARE rval TIMESTAMP;
574 IF ( millis_since_1970 = 0 )
578 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
582 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
584 DECLARE hasMoreRows BOOL DEFAULT TRUE;
585 DECLARE t_group_id INT;
586 DECLARE t_user_id INT ;
587 DECLARE t_user_name VARCHAR (512) ;
588 DECLARE t_message VARCHAR (512) ;
590 DROP TABLE IF EXISTS group_acl;
591 DROP TABLE IF EXISTS user_group;
592 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
594 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
595 FROM asset d, acl_entry e, alterpoint_principal p
596 WHERE d.acl_id = e.acl
597 AND p.id = e.principal AND d.acl_id = t_acl_id;
599 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
600 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
601 WHERE a.id = b.user_ref AND b.elt = c.id;
603 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
604 FROM group_acl, user_group
605 WHERE group_acl.group_id = user_group.group_id ;
610 my $schema = $tr->schema;
611 is( $schema->is_valid, 1, 'Schema is valid' );
612 my @tables = $schema->get_tables;
613 is( scalar @tables, 1, 'Right number of tables (1)' );
614 my $table1 = shift @tables;
615 is( $table1->name, 'one', 'Found "one" table' );
617 my @fields = $table1->get_fields;
618 is(scalar @fields, 3, 'Right number of fields (3) on table one');
619 my $tableTypeFound = 0;
620 my $charsetFound = 0;
621 for my $t1_option_ref ( $table1->options ) {
622 my($key, $value) = %{$t1_option_ref};
623 if ( $key eq 'TYPE' ) {
624 is($value, 'INNODB', 'Table has right table type option' );
626 } elsif ( $key eq 'CHARACTER SET' ) {
627 is($value, 'latin1', 'Table has right character set option' );
631 fail('Table did not have a type option') unless $tableTypeFound;
632 fail('Table did not have a character set option') unless $charsetFound;
634 my $t1f1 = shift @fields;
635 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
636 is( $t1f1->size, 255, 'Field is right size' );
637 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
638 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
639 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
641 my $t1f2 = shift @fields;
642 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
643 ok( !$t1f2->is_nullable, 'Field is not nullable' );
645 $t1f2->default_value,
646 \'CURRENT_TIMESTAMP',
647 'Field has right default value'
649 is_deeply( $t1f2->extra('on update'), \'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
651 my $t1f3 = shift @fields;
652 is( $t1f3->data_type, 'datetime', 'Field is a datetime' );
653 ok( !$t1f3->is_nullable, 'Field is not nullable' );
655 $t1f3->default_value,
656 \'CURRENT_TIMESTAMP',
657 'Field has right default value'
660 my @views = $schema->get_views;
661 is( scalar @views, 3, 'Right number of views (3)' );
663 my ($view1, $view2, $view3) = @views;
664 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
665 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
666 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
667 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
669 # KYC - commenting this out as I don't understand why this string
670 # should /not/ be detected when it is in the SQL - 2/28/12
671 # like($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
673 is( join(',', $view1->fields),
674 join(',', qw[ asset_id fq_name folder_name asset_name annotation
675 asset_type foreign_asset_id foreign_asset_id2 date_created
676 date_modified container_id creator_id modifier_id user_access
678 'First view has correct fields'
681 my @options = $view1->options;
686 'ALGORITHM=UNDEFINED',
687 'DEFINER=`cmdomain`@`localhost`',
688 'SQL SECURITY DEFINER',
690 'Only version 50013 options parsed',
693 my @procs = $schema->get_procedures;
694 is( scalar @procs, 2, 'Right number of procedures (2)' );
695 my $proc1 = shift @procs;
696 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
697 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
698 my $proc2 = shift @procs;
699 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
700 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
703 # Tests for collate table option
705 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
706 my $data = parse($tr,
708 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
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 $table1 = shift @tables;
716 is( $table1->name, 'test', 'Found "test" table' );
719 my $collate = "Not found!";
720 my $charset = "Not found!";
721 for my $t1_option_ref ( $table1->options ) {
722 my($key, $value) = %{$t1_option_ref};
723 $collate = $value if $key eq 'COLLATE';
724 $charset = $value if $key eq 'CHARACTER SET';
726 is($collate, 'latin1_bin', "Collate found");
727 is($charset, 'latin1', "Character set found");
730 # Test the mysql version parser (probably needs to migrate to t/utils.t)
733 '3.23.2' => 3.023002,
736 '5.01.0' => 5.001000,
748 for my $target (keys %$parse_as) {
749 for my $str (keys %{$parse_as->{$target}}) {
751 SQL::Translator::Utils::parse_mysql_version ($str, $target),
753 $parse_as->{$target}{$str},
754 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
759 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
760 ok ($@, 'Exception thrown on invalid version string');
763 my $tr = SQL::Translator->new;
764 my $data = q|create table merge_example (
765 id int(11) NOT NULL auto_increment,
766 shape_field geometry NOT NULL,
768 SPATIAL KEY shape_field (shape_field)
769 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
771 my $val = parse($tr, $data);
772 my $schema = $tr->schema;
773 is( $schema->is_valid, 1, 'Schema is valid' );
774 my @tables = $schema->get_tables;
775 is( scalar @tables, 1, 'Right number of tables (1)' );
776 my $table = shift @tables;
777 is( $table->name, 'merge_example', 'Found "merge_example" table' );
779 my $tableTypeFound = 0;
781 for my $t_option_ref ( $table->options ) {
782 my($key, $value) = %{$t_option_ref};
783 if ( $key eq 'ENGINE' ) {
784 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
786 } elsif ( $key eq 'UNION' ) {
787 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
788 "UNION option has correct set");
793 fail('Table did not have a type option') unless $tableTypeFound;
794 fail('Table did not have a union option') unless $unionFound;
796 my @fields = $table->get_fields;
797 is( scalar @fields, 2, 'Right number of fields (2)' );
798 my $f1 = shift @fields;
799 my $f2 = shift @fields;
800 is( $f1->name, 'id', 'First field name is "id"' );
801 is( $f1->data_type, 'int', 'Type is "int"' );
802 is( $f1->size, 11, 'Size is "11"' );
803 is( $f1->is_nullable, 0, 'Field cannot be null' );
804 is( $f1->is_primary_key, 1, 'Field is PK' );
806 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
807 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
808 is( $f2->is_nullable, 0, 'Field cannot be null' );
809 is( $f2->is_primary_key, 0, 'Field is not PK' );
811 my @indices = $table->get_indices;
812 is( scalar @indices, 1, 'Right number of indices (1)' );
813 my $i1 = shift @indices;
814 is( $i1->name, 'shape_field', 'No name on index' );
815 is( $i1->type, SPATIAL, 'Spatial index' );
817 my @constraints = $table->get_constraints;
818 is( scalar @constraints, 1, 'Right number of constraints (1)' );
819 my $c = shift @constraints;
820 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
821 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
826 q|create table quote (
827 id int(11) NOT NULL auto_increment,
830 q|create table quote (
831 id int(11) NOT NULL auto_increment,
834 q|create table quote (
835 id int(11) NOT NULL auto_increment,
839 for my $data (@data) {
840 my $tr = SQL::Translator->new;
842 my $val = parse($tr, $data);
843 my $schema = $tr->schema;
844 is( $schema->is_valid, 1, 'Schema is valid' );
845 my @tables = $schema->get_tables;
846 is( scalar @tables, 1, 'Right number of tables (1)' );
847 my $table = shift @tables;
848 is( $table->name, 'quote', 'Found "quote" table' );
850 my $tableTypeFound = 0;
851 for my $t_option_ref ( $table->options ) {
852 my($key, $value) = %{$t_option_ref};
853 if ( $key eq 'ENGINE' ) {
854 is($value, 'innodb', 'Table has right table engine option' );
859 fail('Table did not have a type option') unless $tableTypeFound;
861 my @fields = $table->get_fields;
862 my $f1 = shift @fields;
863 is( $f1->name, 'id', 'First field name is "id"' );
864 is( $f1->data_type, 'int', 'Type is "int"' );
865 is( $f1->size, 11, 'Size is "11"' );
866 is( $f1->is_nullable, 0, 'Field cannot be null' );
867 is( $f1->is_primary_key, 1, 'Field is PK' );
872 my $tr = SQL::Translator->new;
873 my $data = q|create table "sessions" (
874 id char(32) not null default '0' primary key,
875 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
876 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
877 key using btree (ssn)
880 my $val = parse($tr, $data);
881 my $schema = $tr->schema;
882 is( $schema->is_valid, 1, 'Schema is valid' );
883 my @tables = $schema->get_tables;
884 is( scalar @tables, 1, 'Right number of tables (1)' );
885 my $table = shift @tables;
886 is( $table->name, 'sessions', 'Found "sessions" table' );
888 my @fields = $table->get_fields;
889 is( scalar @fields, 3, 'Right number of fields (3)' );
890 my $f1 = shift @fields;
891 my $f2 = shift @fields;
892 my $f3 = shift @fields;
893 is( $f1->name, 'id', 'First field name is "id"' );
894 is( $f1->data_type, 'char', 'Type is "char"' );
895 is( $f1->size, 32, 'Size is "32"' );
896 is( $f1->is_nullable, 0, 'Field cannot be null' );
897 is( $f1->default_value, '0', 'Default value is "0"' );
898 is( $f1->is_primary_key, 1, 'Field is PK' );
900 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
901 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
902 is( $f2->size, 12, 'Size is "12"' );
903 is( $f2->is_nullable, 0, 'Field can not be null' );
904 is( $f2->default_value, "test single quotes like in you're", "Single quote in default value is unescaped properly" );
905 is( $f2->is_primary_key, 0, 'Field is not PK' );
907 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
908 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
909 is( $f3->name, 'user', 'Second field name is "user"' );
910 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
911 is( $f3->size, 20, 'Size is "20"' );
912 is( $f3->is_nullable, 0, 'Field can not be null' );
913 is( $f3->default_value, "test single quotes escaped like you're", "Single quote in default value is unescaped properly" );
914 is( $f3->is_primary_key, 0, 'Field is not PK' );
918 # silence PR::D from spewing on STDERR
919 local $::RD_ERRORS = 0;
920 local $::RD_WARN = 0;
921 local $::RD_HINT = 0;
922 my $tr = SQL::Translator->new;
923 my $data = q|create table "sessions" (
924 id char(32) not null default,
925 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
926 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
927 key using btree (ssn)
930 my $val= parse($tr,$data);
931 ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
935 # make sure empty string default value still works
936 my $tr = SQL::Translator->new;
937 my $data = q|create table "sessions" (
938 id char(32) not null DEFAULT '',
939 ssn varchar(12) NOT NULL default "",
940 key using btree (ssn)
942 my $val= parse($tr,$data);
944 my @fields = $tr->schema->get_table('sessions')->get_fields;
945 is (scalar @fields, 2, 'Both fields parsed correctly');
947 my $def = $_->default_value;
948 ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
953 # test rt70437 and rt71468
954 my $file = "$Bin/data/mysql/cashmusic_db.sql";
955 ok (-f $file,"File exists");
956 my $tr = SQL::Translator->new( parser => 'MySQL');
957 ok ($tr->translate($file),'File translated');
958 ok (!$tr->error, 'no error');
959 ok (my $schema = $tr->schema, 'got schema');