9 use SQL::Translator::Schema::Constants;
10 use SQL::Translator::Utils qw//;
11 use Test::SQL::Translator qw(maybe_plan);
15 maybe_plan(337, "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','ck') NOT NULL default 'vk',
85 time_stamp2 timestamp,
86 foo_enabled bit(1) default b'0',
87 bar_enabled bit(1) default b"1",
89 UNIQUE (date, i1) USING BTREE,
91 KEY name_idx (name(10))
92 ) TYPE=MyISAM PACK_KEYS=1;
96 my $schema = $tr->schema;
97 is( $schema->is_valid, 1, 'Schema is valid' );
98 my @tables = $schema->get_tables;
99 is( scalar @tables, 1, 'Right number of tables (1)' );
100 my $table = shift @tables;
101 is( $table->name, 'check', 'Found "check" table' );
103 my @fields = $table->get_fields;
104 is( scalar @fields, 12, 'Right number of fields (12)' );
105 my $f1 = shift @fields;
106 is( $f1->name, 'check_id', 'First field name is "check_id"' );
107 is( $f1->data_type, 'int', 'Type is "int"' );
108 is( $f1->size, 7, 'Size is "7"' );
109 is( $f1->is_nullable, 0, 'Field cannot be null' );
110 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
111 is( $f1->is_primary_key, 1, 'Field is PK' );
112 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
113 my %extra = $f1->extra;
114 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
115 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
117 my $f2 = shift @fields;
118 is( $f2->name, 'successful', 'Second field name is "successful"' );
119 is( $f2->data_type, 'date', 'Type is "date"' );
120 is( $f2->size, 0, 'Size is "0"' );
121 is( $f2->is_nullable, 0, 'Field cannot be null' );
122 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
123 is( $f2->is_primary_key, 0, 'Field is not PK' );
125 my $f3 = shift @fields;
126 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
127 is( $f3->data_type, 'date', 'Type is "date"' );
128 is( $f3->size, 0, 'Size is "0"' );
129 is( $f3->is_nullable, 1, 'Field can be null' );
130 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
131 is( $f3->is_primary_key, 0, 'Field is not PK' );
133 my $f4 = shift @fields;
134 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
135 is( $f4->data_type, 'int', 'Type is "int"' );
136 is( $f4->size, 11, 'Size is "11"' );
137 is( $f4->is_nullable, 0, 'Field cannot be null' );
138 is( $f4->default_value, '0', 'Default value is "0"' );
139 is( $f4->is_primary_key, 0, 'Field is not PK' );
141 my $f5 = shift @fields;
142 is( $f5->name, 's1', 'Fifth field name is "s1"' );
143 is( $f5->data_type, 'set', 'Type is "set"' );
144 is( $f5->size, 1, 'Size is "1"' );
145 is( $f5->is_nullable, 1, 'Field can be null' );
146 is( $f5->default_value, 'b', 'Default value is "b"' );
147 is( $f5->is_primary_key, 0, 'Field is not PK' );
148 my %f5extra = $f5->extra;
149 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
151 my $f6 = shift @fields;
152 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
153 is( $f6->data_type, 'enum', 'Type is "enum"' );
154 is( $f6->size, 1, 'Size is "1"' );
155 is( $f6->is_nullable, 1, 'Field can be null' );
156 is( $f6->default_value, 'c', 'Default value is "c"' );
157 is( $f6->is_primary_key, 0, 'Field is not PK' );
158 my %f6extra = $f6->extra;
159 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
161 my $f7 = shift @fields;
162 is( $f7->name, 'name', 'Seventh field name is "name"' );
163 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
164 is( $f7->size, 30, 'Size is "30"' );
165 is( $f7->is_nullable, 1, 'Field can be null' );
166 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
167 is( $f7->is_primary_key, 0, 'Field is not PK' );
169 my $f8 = shift @fields;
170 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
171 is( $f8->data_type, 'enum', 'Type is "enum"' );
172 is( $f8->size, 2, 'Size is "2"' );
173 is( $f8->is_nullable, 0, 'Field cannot be null' );
174 is( $f8->default_value, 'vk', 'Default value is "vk"' );
175 is( $f8->is_primary_key, 0, 'Field is not PK' );
176 my %f8extra = $f8->extra;
177 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
179 my $f9 = shift @fields;
180 is( $f9->name, 'date', 'Ninth field name is "date"' );
181 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
182 is( $f9->size, 0, 'Size is "0"' );
183 is( $f9->is_nullable, 1, 'Field can be null' );
184 is( $f9->default_value, undef, 'Default value is undefined' );
185 is( $f9->is_primary_key, 0, 'Field is not PK' );
187 my $f10 = shift @fields;
188 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
189 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
190 is( $f10->size, 0, 'Size is "0"' );
191 is( $f10->is_nullable, 1, 'Field can be null' );
192 is( $f10->default_value, undef, 'Default value is undefined' );
193 is( $f10->is_primary_key, 0, 'Field is not PK' );
195 my $f11 = shift @fields;
196 is( $f11->name, 'foo_enabled', 'Eleventh field name is "foo_enabled"' );
197 is( $f11->data_type, 'bit', 'Type is "bit"' );
198 is( $f11->size, 1, 'Size is "1"' );
199 is( $f11->is_nullable, 1, 'Field can be null' );
200 is( $f11->default_value, '0', 'Default value is 0' );
201 is( $f11->is_primary_key, 0, 'Field is not PK' );
203 my $f12 = shift @fields;
204 is( $f12->name, 'bar_enabled', 'Twelveth field name is "bar_enabled"' );
205 is( $f12->data_type, 'bit', 'Type is "bit"' );
206 is( $f12->size, 1, 'Size is "1"' );
207 is( $f12->is_nullable, 1, 'Field can be null' );
208 is( $f12->default_value, '1', 'Default value is 1' );
209 is( $f12->is_primary_key, 0, 'Field is not PK' );
211 my @indices = $table->get_indices;
212 is( scalar @indices, 3, 'Right number of indices (3)' );
214 my $i1 = shift @indices;
215 is( $i1->name, '', 'No name on index' );
216 is( $i1->type, NORMAL, 'Normal index' );
217 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
219 my $i2 = shift @indices;
220 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
221 is( $i2->type, NORMAL, 'Normal index' );
222 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
224 my $i3 = shift @indices;
225 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
226 is( $i3->type, NORMAL, 'Normal index' );
227 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
229 my @constraints = $table->get_constraints;
230 is( scalar @constraints, 2, 'Right number of constraints (2)' );
232 my $c1 = shift @constraints;
233 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
234 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
236 my $c2 = shift @constraints;
237 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
238 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
242 my $tr = SQL::Translator->new;
243 my $data = parse($tr,
245 CREATE TABLE orders (
246 order_id integer NOT NULL auto_increment,
247 member_id varchar(255) comment 'fk to member',
248 billing_address_id int,
249 shipping_address_id int,
251 status smallint NOT NULL,
252 store_id varchar(255) NOT NULL REFERENCES store,
254 shipping_charge decimal(8,2),
255 price_paid decimal(8,2),
256 PRIMARY KEY (order_id) USING BTREE,
257 KEY (status) USING BTREE,
258 KEY USING BTREE (billing_address_id),
259 KEY (shipping_address_id),
260 KEY (member_id, store_id),
261 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
262 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
263 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
264 ) TYPE=INNODB COMMENT = 'orders table comment';
266 CREATE TABLE address (
267 address_id int NOT NULL auto_increment,
268 recipient varchar(255) NOT NULL,
269 address1 varchar(255) NOT NULL,
270 address2 varchar(255),
271 city varchar(255) NOT NULL,
272 state varchar(255) NOT NULL,
273 postal_code varchar(255) NOT NULL,
275 PRIMARY KEY (address_id)
280 my $schema = $tr->schema;
281 is( $schema->is_valid, 1, 'Schema is valid' );
282 my @tables = $schema->get_tables;
283 is( scalar @tables, 2, 'Right number of tables (2)' );
285 my $t1 = shift @tables;
286 is( $t1->name, 'orders', 'Found "orders" table' );
287 is( $t1->comments, 'orders table comment', 'Table comment OK' );
289 my @fields = $t1->get_fields;
290 is( scalar @fields, 10, 'Right number of fields (10)' );
292 my $f1 = shift @fields;
293 is( $f1->name, 'order_id', 'First field name is "order_id"' );
294 is( $f1->data_type, 'int', 'Type is "int"' );
295 is( $f1->size, 11, 'Size is "11"' );
296 is( $f1->is_nullable, 0, 'Field cannot be null' );
297 is( $f1->default_value, undef, 'Default value is undefined' );
298 is( $f1->is_primary_key, 1, 'Field is PK' );
299 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
301 my $f2 = shift @fields;
302 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
303 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
304 is( $f2->size, 255, 'Size is "255"' );
305 is( $f2->is_nullable, 1, 'Field can be null' );
306 is( $f2->comments, 'fk to member', 'Field comment OK' );
307 is( $f2->default_value, undef, 'Default value is undefined' );
309 my $f3 = shift @fields;
310 is( $f3->name, 'billing_address_id',
311 'Third field name is "billing_address_id"' );
312 is( $f3->data_type, 'int', 'Type is "int"' );
313 is( $f3->size, 11, 'Size is "11"' );
315 my $f4 = shift @fields;
316 is( $f4->name, 'shipping_address_id',
317 'Fourth field name is "shipping_address_id"' );
318 is( $f4->data_type, 'int', 'Type is "int"' );
319 is( $f4->size, 11, 'Size is "11"' );
321 my $f5 = shift @fields;
322 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
323 is( $f5->data_type, 'int', 'Type is "int"' );
324 is( $f5->size, 11, 'Size is "11"' );
326 my $f6 = shift @fields;
327 is( $f6->name, 'status', 'Sixth field name is "status"' );
328 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
329 is( $f6->size, 6, 'Size is "6"' );
330 is( $f6->is_nullable, 0, 'Field cannot be null' );
332 my $f7 = shift @fields;
333 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
334 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
335 is( $f7->size, 255, 'Size is "255"' );
336 is( $f7->is_nullable, 0, 'Field cannot be null' );
337 is( $f7->is_foreign_key, 1, 'Field is a FK' );
338 my $fk_ref = $f7->foreign_key_reference;
339 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
340 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
342 my $f8 = shift @fields;
343 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
344 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
345 is( $f8->size, '8,2', 'Size is "8,2"' );
347 my $f9 = shift @fields;
348 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
349 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
350 is( $f9->size, '8,2', 'Size is "8,2"' );
352 my $f10 = shift @fields;
353 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
354 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
355 is( $f10->size, '8,2', 'Size is "8,2"' );
357 my @indices = $t1->get_indices;
358 is( scalar @indices, 4, 'Right number of indices (4)' );
360 my $i1 = shift @indices;
361 is( $i1->type, NORMAL, 'First index is normal' );
362 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
364 my $i2 = shift @indices;
365 is( $i2->type, NORMAL, 'Second index is normal' );
366 is( join(',', $i2->fields), 'billing_address_id',
367 'Index is on "billing_address_id"' );
369 my $i3 = shift @indices;
370 is( $i3->type, NORMAL, 'Third index is normal' );
371 is( join(',', $i3->fields), 'shipping_address_id',
372 'Index is on "shipping_address_id"' );
374 my $i4 = shift @indices;
375 is( $i4->type, NORMAL, 'Third index is normal' );
376 is( join(',', $i4->fields), 'member_id,store_id',
377 'Index is on "member_id,store_id"' );
379 my @constraints = $t1->get_constraints;
380 is( scalar @constraints, 5, 'Right number of constraints (5)' );
382 my $c1 = shift @constraints;
383 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
384 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
386 my $c2 = shift @constraints;
387 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
388 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
389 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
390 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
392 my $c3 = shift @constraints;
393 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
394 is( join(',', $c3->fields), 'billing_address_id',
395 'Constraint is on "billing_address_id"' );
396 is( $c3->reference_table, 'address', 'To table "address"' );
397 is( join(',', $c3->reference_fields), 'address_id',
398 'To field "address_id"' );
400 my $c4 = shift @constraints;
401 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
402 is( join(',', $c4->fields), 'shipping_address_id',
403 'Constraint is on "shipping_address_id"' );
404 is( $c4->reference_table, 'address', 'To table "address"' );
405 is( join(',', $c4->reference_fields), 'address_id',
406 'To field "address_id"' );
408 my $c5 = shift @constraints;
409 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
410 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
411 is( $c5->reference_table, 'store', 'To table "store"' );
412 is( join(',', map { $_ || '' } $c5->reference_fields), '',
413 'No reference fields defined' );
415 my $t2 = shift @tables;
416 is( $t2->name, 'address', 'Found "address" table' );
418 my @t2_fields = $t2->get_fields;
419 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
424 # ALTER TABLE ADD FOREIGN KEY
425 # trailing comma on last create definition
426 # Ignoring INSERT statements
429 my $tr = SQL::Translator->new;
430 my $data = parse($tr,
435 id integer NOT NULL auto_increment,
436 two_id integer NOT NULL auto_increment,
443 id int NOT NULL auto_increment,
444 one_id int NOT NULL auto_increment,
448 FOREIGN KEY (one_id) REFERENCES one (id),
451 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
453 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
457 my $schema = $tr->schema;
458 is( $schema->is_valid, 1, 'Schema is valid' );
459 my $db_name = $schema->name;
460 is( $db_name, 'database_name', 'Database name extracted from USE' );
461 my @tables = $schema->get_tables;
462 is( scalar @tables, 2, 'Right number of tables (2)' );
463 my $table1 = shift @tables;
464 is( $table1->name, 'one', 'Found "one" table' );
465 my $table2 = shift @tables;
466 is( $table2->name, 'two', 'Found "two" table' );
468 my @constraints = $table1->get_constraints;
469 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
471 my $t1c1 = shift @constraints;
472 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
473 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
475 my $t1c2 = shift @constraints;
476 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
477 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
478 is( $t1c2->reference_table, 'two', 'To table "two"' );
479 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
481 @constraints = $table2->get_constraints;
482 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
484 my $t2c1 = shift @constraints;
485 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
486 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
488 my $t2c2 = shift @constraints;
489 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
490 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
491 is( $t2c2->reference_table, 'one', 'To table "one"' );
492 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
496 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
497 # char fields with character set and collate qualifiers
498 # timestamp fields with on update qualifier
499 # charset table option
502 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
503 my $data = parse($tr,
506 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
507 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
508 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
509 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
510 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
514 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
515 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
516 ) TYPE=INNODB DEFAULT CHARSET=latin1;
518 /*!50001 CREATE ALGORITHM=UNDEFINED */
519 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
520 /*! VIEW `vs_asset` AS
521 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
522 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
523 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
524 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
525 `a`.`foreign_asset_id` AS `foreign_asset_id`,
526 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
527 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
528 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
529 `m`.`user_id` AS `user_access`
530 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
533 /*! VIEW `vs_asset2` AS
534 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
535 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
536 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
537 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
538 `a`.`foreign_asset_id` AS `foreign_asset_id`,
539 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
540 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
541 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
542 `m`.`user_id` AS `user_access`
543 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
545 /*!50001 CREATE OR REPLACE */
546 /*! VIEW `vs_asset3` AS
547 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
548 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
549 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
550 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
551 `a`.`foreign_asset_id` AS `foreign_asset_id`,
552 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
553 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
554 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
555 `m`.`user_id` AS `user_access`
556 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
558 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
561 DECLARE rval TIMESTAMP;
562 IF ( millis_since_1970 = 0 )
566 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
570 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
572 DECLARE hasMoreRows BOOL DEFAULT TRUE;
573 DECLARE t_group_id INT;
574 DECLARE t_user_id INT ;
575 DECLARE t_user_name VARCHAR (512) ;
576 DECLARE t_message VARCHAR (512) ;
578 DROP TABLE IF EXISTS group_acl;
579 DROP TABLE IF EXISTS user_group;
580 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
582 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
583 FROM asset d, acl_entry e, alterpoint_principal p
584 WHERE d.acl_id = e.acl
585 AND p.id = e.principal AND d.acl_id = t_acl_id;
587 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
588 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
589 WHERE a.id = b.user_ref AND b.elt = c.id;
591 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
592 FROM group_acl, user_group
593 WHERE group_acl.group_id = user_group.group_id ;
598 my $schema = $tr->schema;
599 is( $schema->is_valid, 1, 'Schema is valid' );
600 my @tables = $schema->get_tables;
601 is( scalar @tables, 1, 'Right number of tables (1)' );
602 my $table1 = shift @tables;
603 is( $table1->name, 'one', 'Found "one" table' );
605 my @fields = $table1->get_fields;
606 is(scalar @fields, 2, 'Right number of fields (2) on table one');
607 my $tableTypeFound = 0;
608 my $charsetFound = 0;
609 for my $t1_option_ref ( $table1->options ) {
610 my($key, $value) = %{$t1_option_ref};
611 if ( $key eq 'TYPE' ) {
612 is($value, 'INNODB', 'Table has right table type option' );
614 } elsif ( $key eq 'CHARACTER SET' ) {
615 is($value, 'latin1', 'Table has right character set option' );
619 fail('Table did not have a type option') unless $tableTypeFound;
620 fail('Table did not have a character set option') unless $charsetFound;
622 my $t1f1 = shift @fields;
623 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
624 is( $t1f1->size, 255, 'Field is right size' );
625 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
626 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
627 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
629 my $t1f2 = shift @fields;
630 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
631 ok( !$t1f2->is_nullable, 'Field is not nullable' );
633 $t1f2->default_value,
634 \'CURRENT_TIMESTAMP',
635 'Field has right default value'
637 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
639 my @views = $schema->get_views;
640 is( scalar @views, 3, 'Right number of views (3)' );
642 my ($view1, $view2, $view3) = @views;
643 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
644 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
645 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
646 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
647 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
648 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
650 my @procs = $schema->get_procedures;
651 is( scalar @procs, 2, 'Right number of procedures (2)' );
652 my $proc1 = shift @procs;
653 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
654 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
655 my $proc2 = shift @procs;
656 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
657 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
660 # Tests for collate table option
662 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
663 my $data = parse($tr,
665 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
668 my $schema = $tr->schema;
669 is( $schema->is_valid, 1, 'Schema is valid' );
670 my @tables = $schema->get_tables;
671 is( scalar @tables, 1, 'Right number of tables (1)' );
672 my $table1 = shift @tables;
673 is( $table1->name, 'test', 'Found "test" table' );
676 my $collate = "Not found!";
677 my $charset = "Not found!";
678 for my $t1_option_ref ( $table1->options ) {
679 my($key, $value) = %{$t1_option_ref};
680 $collate = $value if $key eq 'COLLATE';
681 $charset = $value if $key eq 'CHARACTER SET';
683 is($collate, 'latin1_bin', "Collate found");
684 is($charset, 'latin1', "Character set found");
687 # Test the mysql version parser (probably needs to migrate to t/utils.t)
690 '3.23.2' => 3.023002,
693 '5.01.0' => 5.001000,
705 for my $target (keys %$parse_as) {
706 for my $str (keys %{$parse_as->{$target}}) {
708 SQL::Translator::Utils::parse_mysql_version ($str, $target),
710 $parse_as->{$target}{$str},
711 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
716 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
717 ok ($@, 'Exception thrown on invalid version string');
720 my $tr = SQL::Translator->new;
721 my $data = q|create table merge_example (
722 id int(11) NOT NULL auto_increment,
723 shape_field geometry NOT NULL,
725 SPATIAL KEY shape_field (shape_field)
726 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
728 my $val = parse($tr, $data);
729 my $schema = $tr->schema;
730 is( $schema->is_valid, 1, 'Schema is valid' );
731 my @tables = $schema->get_tables;
732 is( scalar @tables, 1, 'Right number of tables (1)' );
733 my $table = shift @tables;
734 is( $table->name, 'merge_example', 'Found "merge_example" table' );
736 my $tableTypeFound = 0;
738 for my $t_option_ref ( $table->options ) {
739 my($key, $value) = %{$t_option_ref};
740 if ( $key eq 'ENGINE' ) {
741 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
743 } elsif ( $key eq 'UNION' ) {
744 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
745 "UNION option has correct set");
750 fail('Table did not have a type option') unless $tableTypeFound;
751 fail('Table did not have a union option') unless $unionFound;
753 my @fields = $table->get_fields;
754 is( scalar @fields, 2, 'Right number of fields (2)' );
755 my $f1 = shift @fields;
756 my $f2 = shift @fields;
757 is( $f1->name, 'id', 'First field name is "id"' );
758 is( $f1->data_type, 'int', 'Type is "int"' );
759 is( $f1->size, 11, 'Size is "11"' );
760 is( $f1->is_nullable, 0, 'Field cannot be null' );
761 is( $f1->is_primary_key, 1, 'Field is PK' );
763 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
764 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
765 is( $f2->is_nullable, 0, 'Field cannot be null' );
766 is( $f2->is_primary_key, 0, 'Field is not PK' );
768 my @indices = $table->get_indices;
769 is( scalar @indices, 1, 'Right number of indices (1)' );
770 my $i1 = shift @indices;
771 is( $i1->name, 'shape_field', 'No name on index' );
772 is( $i1->type, SPATIAL, 'Spatial index' );
774 my @constraints = $table->get_constraints;
775 is( scalar @constraints, 1, 'Right number of constraints (1)' );
776 my $c = shift @constraints;
777 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
778 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
783 q|create table quote (
784 id int(11) NOT NULL auto_increment,
787 q|create table quote (
788 id int(11) NOT NULL auto_increment,
791 q|create table quote (
792 id int(11) NOT NULL auto_increment,
796 for my $data (@data) {
797 my $tr = SQL::Translator->new;
799 my $val = parse($tr, $data);
800 my $schema = $tr->schema;
801 is( $schema->is_valid, 1, 'Schema is valid' );
802 my @tables = $schema->get_tables;
803 is( scalar @tables, 1, 'Right number of tables (1)' );
804 my $table = shift @tables;
805 is( $table->name, 'quote', 'Found "quote" table' );
807 my $tableTypeFound = 0;
808 for my $t_option_ref ( $table->options ) {
809 my($key, $value) = %{$t_option_ref};
810 if ( $key eq 'ENGINE' ) {
811 is($value, 'innodb', 'Table has right table engine option' );
816 fail('Table did not have a type option') unless $tableTypeFound;
818 my @fields = $table->get_fields;
819 my $f1 = shift @fields;
820 is( $f1->name, 'id', 'First field name is "id"' );
821 is( $f1->data_type, 'int', 'Type is "int"' );
822 is( $f1->size, 11, 'Size is "11"' );
823 is( $f1->is_nullable, 0, 'Field cannot be null' );
824 is( $f1->is_primary_key, 1, 'Field is PK' );
829 my $tr = SQL::Translator->new;
830 my $data = q|create table "sessions" (
831 id char(32) not null default '0' primary key,
832 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
833 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
834 key using btree (ssn)
837 my $val = parse($tr, $data);
838 my $schema = $tr->schema;
839 is( $schema->is_valid, 1, 'Schema is valid' );
840 my @tables = $schema->get_tables;
841 is( scalar @tables, 1, 'Right number of tables (1)' );
842 my $table = shift @tables;
843 is( $table->name, 'sessions', 'Found "sessions" table' );
845 my @fields = $table->get_fields;
846 is( scalar @fields, 3, 'Right number of fields (3)' );
847 my $f1 = shift @fields;
848 my $f2 = shift @fields;
849 my $f3 = shift @fields;
850 is( $f1->name, 'id', 'First field name is "id"' );
851 is( $f1->data_type, 'char', 'Type is "char"' );
852 is( $f1->size, 32, 'Size is "32"' );
853 is( $f1->is_nullable, 0, 'Field cannot be null' );
854 is( $f1->default_value, '0', 'Default value is "0"' );
855 is( $f1->is_primary_key, 1, 'Field is PK' );
857 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
858 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
859 is( $f2->size, 12, 'Size is "12"' );
860 is( $f2->is_nullable, 0, 'Field can not be null' );
861 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
862 is( $f2->is_primary_key, 0, 'Field is not PK' );
864 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
865 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
866 is( $f3->name, 'user', 'Second field name is "user"' );
867 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
868 is( $f3->size, 20, 'Size is "20"' );
869 is( $f3->is_nullable, 0, 'Field can not be null' );
870 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
871 is( $f3->is_primary_key, 0, 'Field is not PK' );
875 # silence PR::D from spewing on STDERR
876 local ($::RD_ERRORS, $::RD_WARN,$::RD_HINT,$::RD_TRACE);
877 my $tr = SQL::Translator->new;
878 my $data = q|create table "sessions" (
879 id char(32) not null default,
880 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
881 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
882 key using btree (ssn)
885 my $val= parse($tr,$data);
886 ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
890 # make sure empty string default value still works
891 my $tr = SQL::Translator->new;
892 my $data = q|create table "sessions" (
893 id char(32) not null DEFAULT '',
894 ssn varchar(12) NOT NULL default "",
895 key using btree (ssn)
897 my $val= parse($tr,$data);
899 my @fields = $tr->schema->get_table('sessions')->get_fields;
900 is (scalar @fields, 2, 'Both fields parsed correctly');
902 my $def = $_->default_value;
903 ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
908 # test rt70437 and rt71468
909 my $file = "$Bin/data/mysql/cashmusic_db.sql";
910 ok (-f $file,"File exists");
911 my $tr = SQL::Translator->new( parser => 'MySQL');
912 ok ($tr->translate($file),'File translated');
913 ok (!$tr->error, 'no error');
914 ok (my $schema = $tr->schema, 'got schema');