5 use SQL::Translator::Constants qw(:sqlt_types :sqlt_constants);
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,
12 ssn varchar(12) unique key,
14 fulltext key `session_fulltext` (a_session)
17 #my $val = $tr->parse();
18 # my $val = $tr->parse($data);
19 my $schema = $tr->parse($data);
20 is( $schema->is_valid, 1, 'Schema is valid' );
21 my @tables = $schema->get_tables;
22 is( scalar @tables, 1, 'Right number of tables (1)' );
23 my $table = shift @tables;
24 is( $table->name, 'sessions', 'Found "sessions" table' );
26 my @fields = $table->get_fields;
27 is( scalar @fields, 4, 'Right number of fields (4)' );
29 my $f1 = shift @fields;
30 my $f2 = shift @fields;
31 is( $f1->name, 'id', 'First field name is "id"' );
32 is( $f1->data_type, 'char', 'Type is "char"' );
33 is( $f1->size, 32, 'Size is "32"' );
34 is( $f1->is_nullable, 0, 'Field cannot be null' );
35 is( $f1->default_value, '0', 'Default value is "0"' );
36 is( $f1->is_primary_key, 1, 'Field is PK' );
38 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
39 is( $f2->data_type, 'text', 'Type is "text"' );
40 is( $f2->size, 65_535, 'Size is "65,535"' );
41 is( $f2->is_nullable, 1, 'Field can be null' );
42 is( $f2->default_value, undef, 'Default value is undefined' );
43 is( $f2->is_primary_key, 0, 'Field is not PK' );
45 my @indices = $table->get_indices;
46 is( scalar @indices, 2, 'Right number of indices (2)' );
49 is( $i->type, 'FULLTEXT', 'Found fulltext' );
51 my @constraints = $table->get_constraints;
52 is( scalar @constraints, 2, 'Right number of constraints (2)' );
53 my $c1 = shift @constraints;
54 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
55 is( join(',', $c1->fields), 'id', 'Constraint is on "id"' );
56 my $c2 = shift @constraints;
57 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
58 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
62 my $tr = SQL::Translator->new({ from => 'MySQL' });
63 my $schema = $tr->parse(
65 CREATE TABLE `check` (
66 check_id int(7) unsigned zerofill NOT NULL default '0000000'
67 auto_increment primary key,
68 successful date NOT NULL default '0000-00-00',
69 unsuccessful date default '0000-00-00',
70 i1 int(11) default '0' not null,
71 s1 set('a','b','c') default 'b',
72 e1 enum('a','b','c') default 'c',
73 name varchar(30) default NULL,
74 foo_type enum('vk','ck') NOT NULL default 'vk',
76 time_stamp2 timestamp,
80 KEY name_idx (name(10))
81 ) TYPE=MyISAM PACK_KEYS=1;
85 # my $schema = $tr->schema;
86 is( $schema->is_valid, 1, 'Schema is valid' );
87 my @tables = $schema->get_tables;
88 is( scalar @tables, 1, 'Right number of tables (1)' );
89 my $table = shift @tables;
90 is( $table->name, 'check', 'Found "check" table' );
92 my @fields = $table->get_fields;
93 is( scalar @fields, 10, 'Right number of fields (10)' );
94 my $f1 = shift @fields;
95 is( $f1->name, 'check_id', 'First field name is "check_id"' );
96 is( $f1->data_type, 'int', 'Type is "int"' );
97 is( $f1->size, 7, 'Size is "7"' );
98 is( $f1->is_nullable, 0, 'Field cannot be null' );
99 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
100 is( $f1->is_primary_key, 1, 'Field is PK' );
101 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
102 my %extra = $f1->extra;
103 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
104 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
106 my $f2 = shift @fields;
107 is( $f2->name, 'successful', 'Second field name is "successful"' );
108 is( $f2->data_type, 'date', 'Type is "date"' );
109 is( $f2->size, 0, 'Size is "0"' );
110 is( $f2->is_nullable, 0, 'Field cannot be null' );
111 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
112 is( $f2->is_primary_key, 0, 'Field is not PK' );
114 my $f3 = shift @fields;
115 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
116 is( $f3->data_type, 'date', 'Type is "date"' );
117 is( $f3->size, 0, 'Size is "0"' );
118 is( $f3->is_nullable, 1, 'Field can be null' );
119 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
120 is( $f3->is_primary_key, 0, 'Field is not PK' );
122 my $f4 = shift @fields;
123 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
124 is( $f4->data_type, 'int', 'Type is "int"' );
125 is( $f4->size, 11, 'Size is "11"' );
126 is( $f4->is_nullable, 0, 'Field cannot be null' );
127 is( $f4->default_value, '0', 'Default value is "0"' );
128 is( $f4->is_primary_key, 0, 'Field is not PK' );
130 my $f5 = shift @fields;
131 is( $f5->name, 's1', 'Fifth field name is "s1"' );
132 is( $f5->data_type, 'set', 'Type is "set"' );
133 is( $f5->size, 1, 'Size is "1"' );
134 is( $f5->is_nullable, 1, 'Field can be null' );
135 is( $f5->default_value, 'b', 'Default value is "b"' );
136 is( $f5->is_primary_key, 0, 'Field is not PK' );
137 my %f5extra = $f5->extra;
138 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
140 my $f6 = shift @fields;
141 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
142 is( $f6->data_type, 'enum', 'Type is "enum"' );
143 is( $f6->size, 1, 'Size is "1"' );
144 is( $f6->is_nullable, 1, 'Field can be null' );
145 is( $f6->default_value, 'c', 'Default value is "c"' );
146 is( $f6->is_primary_key, 0, 'Field is not PK' );
147 my %f6extra = $f6->extra;
148 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
150 my $f7 = shift @fields;
151 is( $f7->name, 'name', 'Seventh field name is "name"' );
152 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
153 is( $f7->size, 30, 'Size is "30"' );
154 is( $f7->is_nullable, 1, 'Field can be null' );
155 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
156 is( $f7->is_primary_key, 0, 'Field is not PK' );
158 my $f8 = shift @fields;
159 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
160 is( $f8->data_type, 'enum', 'Type is "enum"' );
161 is( $f8->size, 2, 'Size is "2"' );
162 is( $f8->is_nullable, 0, 'Field cannot be null' );
163 is( $f8->default_value, 'vk', 'Default value is "vk"' );
164 is( $f8->is_primary_key, 0, 'Field is not PK' );
165 my %f8extra = $f8->extra;
166 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
168 my $f9 = shift @fields;
169 is( $f9->name, 'date', 'Ninth field name is "date"' );
170 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
171 is( $f9->size, 0, 'Size is "0"' );
172 is( $f9->is_nullable, 1, 'Field can be null' );
173 is( $f9->default_value, undef, 'Default value is undefined' );
174 is( $f9->is_primary_key, 0, 'Field is not PK' );
176 my $f10 = shift @fields;
177 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
178 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
179 is( $f10->size, 0, 'Size is "0"' );
180 is( $f10->is_nullable, 1, 'Field can be null' );
181 is( $f10->default_value, undef, 'Default value is undefined' );
182 is( $f10->is_primary_key, 0, 'Field is not PK' );
184 my @indices = $table->get_indices;
185 is( scalar @indices, 3, 'Right number of indices (3)' );
187 my $i1 = shift @indices;
188 is( $i1->name, '', 'No name on index' );
189 is( $i1->type, NORMAL, 'Normal index' );
190 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
192 my $i2 = shift @indices;
193 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
194 is( $i2->type, NORMAL, 'Normal index' );
195 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
197 my $i3 = shift @indices;
198 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
199 is( $i3->type, NORMAL, 'Normal index' );
200 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
202 my @constraints = $table->get_constraints;
203 is( scalar @constraints, 2, 'Right number of constraints (2)' );
205 my $c1 = shift @constraints;
206 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
207 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
209 my $c2 = shift @constraints;
210 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
211 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
215 my $tr = SQL::Translator->new;
216 my $data = parse($tr,
218 CREATE TABLE orders (
219 order_id integer NOT NULL auto_increment,
220 member_id varchar(255) comment 'fk to member',
221 billing_address_id int,
222 shipping_address_id int,
224 status smallint NOT NULL,
225 store_id varchar(255) NOT NULL REFERENCES store,
227 shipping_charge decimal(8,2),
228 price_paid decimal(8,2),
229 PRIMARY KEY (order_id),
231 KEY (billing_address_id),
232 KEY (shipping_address_id),
233 KEY (member_id, store_id),
234 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
235 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
236 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
237 ) TYPE=INNODB COMMENT = 'orders table comment';
239 CREATE TABLE address (
240 address_id int NOT NULL auto_increment,
241 recipient varchar(255) NOT NULL,
242 address1 varchar(255) NOT NULL,
243 address2 varchar(255),
244 city varchar(255) NOT NULL,
245 state varchar(255) NOT NULL,
246 postal_code varchar(255) NOT NULL,
248 PRIMARY KEY (address_id)
253 my $schema = $tr->schema;
254 is( $schema->is_valid, 1, 'Schema is valid' );
255 my @tables = $schema->get_tables;
256 is( scalar @tables, 2, 'Right number of tables (2)' );
258 my $t1 = shift @tables;
259 is( $t1->name, 'orders', 'Found "orders" table' );
260 is( $t1->comments, 'orders table comment', 'Table comment OK' );
262 my @fields = $t1->get_fields;
263 is( scalar @fields, 10, 'Right number of fields (10)' );
265 my $f1 = shift @fields;
266 is( $f1->name, 'order_id', 'First field name is "order_id"' );
267 is( $f1->data_type, 'int', 'Type is "int"' );
268 is( $f1->size, 11, 'Size is "11"' );
269 is( $f1->is_nullable, 0, 'Field cannot be null' );
270 is( $f1->default_value, undef, 'Default value is undefined' );
271 is( $f1->is_primary_key, 1, 'Field is PK' );
272 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
274 my $f2 = shift @fields;
275 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
276 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
277 is( $f2->size, 255, 'Size is "255"' );
278 is( $f2->is_nullable, 1, 'Field can be null' );
279 is( $f2->comments, 'fk to member', 'Field comment OK' );
280 is( $f2->default_value, undef, 'Default value is undefined' );
282 my $f3 = shift @fields;
283 is( $f3->name, 'billing_address_id',
284 'Third field name is "billing_address_id"' );
285 is( $f3->data_type, 'int', 'Type is "int"' );
286 is( $f3->size, 11, 'Size is "11"' );
288 my $f4 = shift @fields;
289 is( $f4->name, 'shipping_address_id',
290 'Fourth field name is "shipping_address_id"' );
291 is( $f4->data_type, 'int', 'Type is "int"' );
292 is( $f4->size, 11, 'Size is "11"' );
294 my $f5 = shift @fields;
295 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
296 is( $f5->data_type, 'int', 'Type is "int"' );
297 is( $f5->size, 11, 'Size is "11"' );
299 my $f6 = shift @fields;
300 is( $f6->name, 'status', 'Sixth field name is "status"' );
301 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
302 is( $f6->size, 6, 'Size is "6"' );
303 is( $f6->is_nullable, 0, 'Field cannot be null' );
305 my $f7 = shift @fields;
306 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
307 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
308 is( $f7->size, 255, 'Size is "255"' );
309 is( $f7->is_nullable, 0, 'Field cannot be null' );
310 is( $f7->is_foreign_key, 1, 'Field is a FK' );
311 my $fk_ref = $f7->foreign_key_reference;
312 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
313 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
315 my $f8 = shift @fields;
316 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
317 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
318 is( $f8->size, '8,2', 'Size is "8,2"' );
320 my $f9 = shift @fields;
321 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
322 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
323 is( $f9->size, '8,2', 'Size is "8,2"' );
325 my $f10 = shift @fields;
326 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
327 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
328 is( $f10->size, '8,2', 'Size is "8,2"' );
330 my @indices = $t1->get_indices;
331 is( scalar @indices, 4, 'Right number of indices (4)' );
333 my $i1 = shift @indices;
334 is( $i1->type, NORMAL, 'First index is normal' );
335 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
337 my $i2 = shift @indices;
338 is( $i2->type, NORMAL, 'Second index is normal' );
339 is( join(',', $i2->fields), 'billing_address_id',
340 'Index is on "billing_address_id"' );
342 my $i3 = shift @indices;
343 is( $i3->type, NORMAL, 'Third index is normal' );
344 is( join(',', $i3->fields), 'shipping_address_id',
345 'Index is on "shipping_address_id"' );
347 my $i4 = shift @indices;
348 is( $i4->type, NORMAL, 'Third index is normal' );
349 is( join(',', $i4->fields), 'member_id,store_id',
350 'Index is on "member_id,store_id"' );
352 my @constraints = $t1->get_constraints;
353 is( scalar @constraints, 5, 'Right number of constraints (5)' );
355 my $c1 = shift @constraints;
356 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
357 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
359 my $c2 = shift @constraints;
360 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
361 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
362 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
363 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
365 my $c3 = shift @constraints;
366 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
367 is( join(',', $c3->fields), 'billing_address_id',
368 'Constraint is on "billing_address_id"' );
369 is( $c3->reference_table, 'address', 'To table "address"' );
370 is( join(',', $c3->reference_fields), 'address_id',
371 'To field "address_id"' );
373 my $c4 = shift @constraints;
374 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
375 is( join(',', $c4->fields), 'shipping_address_id',
376 'Constraint is on "shipping_address_id"' );
377 is( $c4->reference_table, 'address', 'To table "address"' );
378 is( join(',', $c4->reference_fields), 'address_id',
379 'To field "address_id"' );
381 my $c5 = shift @constraints;
382 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
383 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
384 is( $c5->reference_table, 'store', 'To table "store"' );
385 is( join(',', map { $_ || '' } $c5->reference_fields), '',
386 'No reference fields defined' );
388 my $t2 = shift @tables;
389 is( $t2->name, 'address', 'Found "address" table' );
391 my @t2_fields = $t2->get_fields;
392 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
397 # ALTER TABLE ADD FOREIGN KEY
398 # trailing comma on last create definition
399 # Ignoring INSERT statements
402 my $tr = SQL::Translator->new;
403 my $data = parse($tr,
408 id integer NOT NULL auto_increment,
409 two_id integer NOT NULL auto_increment,
416 id int NOT NULL auto_increment,
417 one_id int NOT NULL auto_increment,
421 FOREIGN KEY (one_id) REFERENCES one (id),
424 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
426 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
430 my $schema = $tr->schema;
431 is( $schema->is_valid, 1, 'Schema is valid' );
432 my $db_name = $schema->name;
433 is( $db_name, 'database_name', 'Database name extracted from USE' );
434 my @tables = $schema->get_tables;
435 is( scalar @tables, 2, 'Right number of tables (2)' );
436 my $table1 = shift @tables;
437 is( $table1->name, 'one', 'Found "one" table' );
438 my $table2 = shift @tables;
439 is( $table2->name, 'two', 'Found "two" table' );
441 my @constraints = $table1->get_constraints;
442 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
444 my $t1c1 = shift @constraints;
445 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
446 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
448 my $t1c2 = shift @constraints;
449 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
450 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
451 is( $t1c2->reference_table, 'two', 'To table "two"' );
452 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
454 @constraints = $table2->get_constraints;
455 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
457 my $t2c1 = shift @constraints;
458 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
459 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
461 my $t2c2 = shift @constraints;
462 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
463 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
464 is( $t2c2->reference_table, 'one', 'To table "one"' );
465 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
469 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
470 # char fields with character set and collate qualifiers
471 # timestamp fields with on update qualifier
472 # charset table option
475 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
476 my $data = parse($tr,
479 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
480 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
481 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
482 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
483 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
487 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
488 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
489 ) TYPE=INNODB DEFAULT CHARSET=latin1;
491 /*!50001 CREATE ALGORITHM=UNDEFINED */
492 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
493 /*! VIEW `vs_asset` AS
494 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
495 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
496 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
497 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
498 `a`.`foreign_asset_id` AS `foreign_asset_id`,
499 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
500 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
501 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
502 `m`.`user_id` AS `user_access`
503 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
506 /*! VIEW `vs_asset2` AS
507 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
508 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
509 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
510 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
511 `a`.`foreign_asset_id` AS `foreign_asset_id`,
512 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
513 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
514 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
515 `m`.`user_id` AS `user_access`
516 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
518 /*!50001 CREATE OR REPLACE */
519 /*! VIEW `vs_asset3` AS
520 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
521 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
522 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
523 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
524 `a`.`foreign_asset_id` AS `foreign_asset_id`,
525 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
526 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
527 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
528 `m`.`user_id` AS `user_access`
529 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
531 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
534 DECLARE rval TIMESTAMP;
535 IF ( millis_since_1970 = 0 )
539 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
543 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
545 DECLARE hasMoreRows BOOL DEFAULT TRUE;
546 DECLARE t_group_id INT;
547 DECLARE t_user_id INT ;
548 DECLARE t_user_name VARCHAR (512) ;
549 DECLARE t_message VARCHAR (512) ;
551 DROP TABLE IF EXISTS group_acl;
552 DROP TABLE IF EXISTS user_group;
553 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
555 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
556 FROM asset d, acl_entry e, alterpoint_principal p
557 WHERE d.acl_id = e.acl
558 AND p.id = e.principal AND d.acl_id = t_acl_id;
560 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
561 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
562 WHERE a.id = b.user_ref AND b.elt = c.id;
564 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
565 FROM group_acl, user_group
566 WHERE group_acl.group_id = user_group.group_id ;
571 my $schema = $tr->schema;
572 is( $schema->is_valid, 1, 'Schema is valid' );
573 my @tables = $schema->get_tables;
574 is( scalar @tables, 1, 'Right number of tables (1)' );
575 my $table1 = shift @tables;
576 is( $table1->name, 'one', 'Found "one" table' );
578 my @fields = $table1->get_fields;
579 is(scalar @fields, 2, 'Right number of fields (2) on table one');
580 my $tableTypeFound = 0;
581 my $charsetFound = 0;
582 for my $t1_option_ref ( $table1->options ) {
583 my($key, $value) = %{$t1_option_ref};
584 if ( $key eq 'TYPE' ) {
585 is($value, 'INNODB', 'Table has right table type option' );
587 } elsif ( $key eq 'CHARACTER SET' ) {
588 is($value, 'latin1', 'Table has right character set option' );
592 fail('Table did not have a type option') unless $tableTypeFound;
593 fail('Table did not have a character set option') unless $charsetFound;
595 my $t1f1 = shift @fields;
596 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
597 is( $t1f1->size, 255, 'Field is right size' );
598 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
599 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
600 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
602 my $t1f2 = shift @fields;
603 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
604 ok( !$t1f2->is_nullable, 'Field is not nullable' );
606 $t1f2->default_value,
607 \'CURRENT_TIMESTAMP',
608 'Field has right default value'
610 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
612 my @views = $schema->get_views;
613 is( scalar @views, 3, 'Right number of views (3)' );
614 my ($view3, $view1, $view2) = @views;
615 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
616 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
617 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
618 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
619 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
620 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
622 my @procs = $schema->get_procedures;
623 is( scalar @procs, 2, 'Right number of procedures (2)' );
624 my $proc1 = shift @procs;
625 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
626 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
627 my $proc2 = shift @procs;
628 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
629 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
632 # Tests for collate table option
634 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
635 my $data = parse($tr,
637 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
640 my $schema = $tr->schema;
641 is( $schema->is_valid, 1, 'Schema is valid' );
642 my @tables = $schema->get_tables;
643 is( scalar @tables, 1, 'Right number of tables (1)' );
644 my $table1 = shift @tables;
645 is( $table1->name, 'test', 'Found "test" table' );
648 my $collate = "Not found!";
649 my $charset = "Not found!";
650 for my $t1_option_ref ( $table1->options ) {
651 my($key, $value) = %{$t1_option_ref};
652 $collate = $value if $key eq 'COLLATE';
653 $charset = $value if $key eq 'CHARACTER SET';
655 is($collate, 'latin1_bin', "Collate found");
656 is($charset, 'latin1', "Character set found");
659 # Test the mysql version parser (probably needs to migrate to t/utils.t)
662 '3.23.2' => 3.023002,
665 '5.01.0' => 5.001000,
677 for my $target (keys %$parse_as) {
678 for my $str (keys %{$parse_as->{$target}}) {
680 SQL::Translator::Utils::parse_mysql_version ($str, $target),
682 $parse_as->{$target}{$str},
683 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
688 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
689 ok ($@, 'Exception thrown on invalid version string');
692 my $tr = SQL::Translator->new;
693 my $data = q|create table merge_example (
694 id int(11) NOT NULL auto_increment,
695 shape_field geometry NOT NULL,
697 SPATIAL KEY shape_field (shape_field)
698 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
700 my $val = parse($tr, $data);
701 my $schema = $tr->schema;
702 is( $schema->is_valid, 1, 'Schema is valid' );
703 my @tables = $schema->get_tables;
704 is( scalar @tables, 1, 'Right number of tables (1)' );
705 my $table = shift @tables;
706 is( $table->name, 'merge_example', 'Found "merge_example" table' );
708 my $tableTypeFound = 0;
710 for my $t_option_ref ( $table->options ) {
711 my($key, $value) = %{$t_option_ref};
712 if ( $key eq 'ENGINE' ) {
713 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
715 } elsif ( $key eq 'UNION' ) {
716 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
717 "UNION option has correct set");
722 fail('Table did not have a type option') unless $tableTypeFound;
723 fail('Table did not have a union option') unless $unionFound;
725 my @fields = $table->get_fields;
726 is( scalar @fields, 2, 'Right number of fields (2)' );
727 my $f1 = shift @fields;
728 my $f2 = shift @fields;
729 is( $f1->name, 'id', 'First field name is "id"' );
730 is( $f1->data_type, 'int', 'Type is "int"' );
731 is( $f1->size, 11, 'Size is "11"' );
732 is( $f1->is_nullable, 0, 'Field cannot be null' );
733 is( $f1->is_primary_key, 1, 'Field is PK' );
735 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
736 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
737 is( $f2->is_nullable, 0, 'Field cannot be null' );
738 is( $f2->is_primary_key, 0, 'Field is not PK' );
740 my @indices = $table->get_indices;
741 is( scalar @indices, 1, 'Right number of indices (1)' );
742 my $i1 = shift @indices;
743 is( $i1->name, 'shape_field', 'No name on index' );
744 is( $i1->type, SPATIAL, 'Spatial index' );
746 my @constraints = $table->get_constraints;
747 is( scalar @constraints, 1, 'Right number of constraints (1)' );
748 my $c = shift @constraints;
749 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
750 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
755 q|create table quote (
756 id int(11) NOT NULL auto_increment,
759 q|create table quote (
760 id int(11) NOT NULL auto_increment,
763 q|create table quote (
764 id int(11) NOT NULL auto_increment,
768 for my $data (@data) {
769 my $tr = SQL::Translator->new;
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, 'quote', 'Found "quote" table' );
779 my $tableTypeFound = 0;
780 for my $t_option_ref ( $table->options ) {
781 my($key, $value) = %{$t_option_ref};
782 if ( $key eq 'ENGINE' ) {
783 is($value, 'innodb', 'Table has right table engine option' );
788 fail('Table did not have a type option') unless $tableTypeFound;
790 my @fields = $table->get_fields;
791 my $f1 = shift @fields;
792 is( $f1->name, 'id', 'First field name is "id"' );
793 is( $f1->data_type, 'int', 'Type is "int"' );
794 is( $f1->size, 11, 'Size is "11"' );
795 is( $f1->is_nullable, 0, 'Field cannot be null' );
796 is( $f1->is_primary_key, 1, 'Field is PK' );
801 my $tr = SQL::Translator->new;
802 my $data = q|create table "sessions" (
803 id char(32) not null default '0' primary key,
804 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
805 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
808 my $val = parse($tr, $data);
809 my $schema = $tr->schema;
810 is( $schema->is_valid, 1, 'Schema is valid' );
811 my @tables = $schema->get_tables;
812 is( scalar @tables, 1, 'Right number of tables (1)' );
813 my $table = shift @tables;
814 is( $table->name, 'sessions', 'Found "sessions" table' );
816 my @fields = $table->get_fields;
817 is( scalar @fields, 3, 'Right number of fields (3)' );
818 my $f1 = shift @fields;
819 my $f2 = shift @fields;
820 my $f3 = shift @fields;
821 is( $f1->name, 'id', 'First field name is "id"' );
822 is( $f1->data_type, 'char', 'Type is "char"' );
823 is( $f1->size, 32, 'Size is "32"' );
824 is( $f1->is_nullable, 0, 'Field cannot be null' );
825 is( $f1->default_value, '0', 'Default value is "0"' );
826 is( $f1->is_primary_key, 1, 'Field is PK' );
828 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
829 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
830 is( $f2->size, 12, 'Size is "12"' );
831 is( $f2->is_nullable, 0, 'Field can not be null' );
832 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
833 is( $f2->is_primary_key, 0, 'Field is not PK' );
835 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
836 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
837 is( $f3->name, 'user', 'Second field name is "user"' );
838 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
839 is( $f3->size, 20, 'Size is "20"' );
840 is( $f3->is_nullable, 0, 'Field can not be null' );
841 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
842 is( $f3->is_primary_key, 0, 'Field is not PK' );