9 use SQL::Translator::Schema::Constants;
10 use SQL::Translator::Utils qw//;
11 use Test::SQL::Translator qw(maybe_plan);
14 maybe_plan(317, "SQL::Translator::Parser::MySQL");
15 SQL::Translator::Parser::MySQL->import('parse');
19 my $tr = SQL::Translator->new;
20 my $data = q|create table "sessions" (
21 id char(32) not null default '0' primary key,
23 ssn varchar(12) unique key,
25 fulltext key `session_fulltext` (a_session)
28 my $val = parse($tr, $data);
29 my $schema = $tr->schema;
30 is( $schema->is_valid, 1, 'Schema is valid' );
31 my @tables = $schema->get_tables;
32 is( scalar @tables, 1, 'Right number of tables (1)' );
33 my $table = shift @tables;
34 is( $table->name, 'sessions', 'Found "sessions" table' );
36 my @fields = $table->get_fields;
37 is( scalar @fields, 4, 'Right number of fields (4)' );
38 my $f1 = shift @fields;
39 my $f2 = shift @fields;
40 is( $f1->name, 'id', 'First field name is "id"' );
41 is( $f1->data_type, 'char', 'Type is "char"' );
42 is( $f1->size, 32, 'Size is "32"' );
43 is( $f1->is_nullable, 0, 'Field cannot be null' );
44 is( $f1->default_value, '0', 'Default value is "0"' );
45 is( $f1->is_primary_key, 1, 'Field is PK' );
47 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
48 is( $f2->data_type, 'text', 'Type is "text"' );
49 is( $f2->size, 65_535, 'Size is "65,535"' );
50 is( $f2->is_nullable, 1, 'Field can be null' );
51 is( $f2->default_value, undef, 'Default value is undefined' );
52 is( $f2->is_primary_key, 0, 'Field is not PK' );
54 my @indices = $table->get_indices;
55 is( scalar @indices, 2, 'Right number of indices (2)' );
57 is( $i->type, 'FULLTEXT', 'Found fulltext' );
59 my @constraints = $table->get_constraints;
60 is( scalar @constraints, 2, 'Right number of constraints (2)' );
61 my $c = shift @constraints;
62 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
63 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
64 my $c2 = shift @constraints;
65 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
66 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
70 my $tr = SQL::Translator->new;
73 CREATE TABLE `check` (
74 check_id int(7) unsigned zerofill NOT NULL default '0000000'
75 auto_increment primary key,
76 successful date NOT NULL default '0000-00-00',
77 unsuccessful date default '0000-00-00',
78 i1 int(11) default '0' not null,
79 s1 set('a','b','c') default 'b',
80 e1 enum('a','b','c') default 'c',
81 name varchar(30) default NULL,
82 foo_type enum('vk','ck') NOT NULL default 'vk',
84 time_stamp2 timestamp,
86 UNIQUE (date, i1) USING BTREE,
88 KEY name_idx (name(10))
89 ) TYPE=MyISAM PACK_KEYS=1;
93 my $schema = $tr->schema;
94 is( $schema->is_valid, 1, 'Schema is valid' );
95 my @tables = $schema->get_tables;
96 is( scalar @tables, 1, 'Right number of tables (1)' );
97 my $table = shift @tables;
98 is( $table->name, 'check', 'Found "check" table' );
100 my @fields = $table->get_fields;
101 is( scalar @fields, 10, 'Right number of fields (10)' );
102 my $f1 = shift @fields;
103 is( $f1->name, 'check_id', 'First field name is "check_id"' );
104 is( $f1->data_type, 'int', 'Type is "int"' );
105 is( $f1->size, 7, 'Size is "7"' );
106 is( $f1->is_nullable, 0, 'Field cannot be null' );
107 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
108 is( $f1->is_primary_key, 1, 'Field is PK' );
109 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
110 my %extra = $f1->extra;
111 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
112 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
114 my $f2 = shift @fields;
115 is( $f2->name, 'successful', 'Second field name is "successful"' );
116 is( $f2->data_type, 'date', 'Type is "date"' );
117 is( $f2->size, 0, 'Size is "0"' );
118 is( $f2->is_nullable, 0, 'Field cannot be null' );
119 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
120 is( $f2->is_primary_key, 0, 'Field is not PK' );
122 my $f3 = shift @fields;
123 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
124 is( $f3->data_type, 'date', 'Type is "date"' );
125 is( $f3->size, 0, 'Size is "0"' );
126 is( $f3->is_nullable, 1, 'Field can be null' );
127 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
128 is( $f3->is_primary_key, 0, 'Field is not PK' );
130 my $f4 = shift @fields;
131 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
132 is( $f4->data_type, 'int', 'Type is "int"' );
133 is( $f4->size, 11, 'Size is "11"' );
134 is( $f4->is_nullable, 0, 'Field cannot be null' );
135 is( $f4->default_value, '0', 'Default value is "0"' );
136 is( $f4->is_primary_key, 0, 'Field is not PK' );
138 my $f5 = shift @fields;
139 is( $f5->name, 's1', 'Fifth field name is "s1"' );
140 is( $f5->data_type, 'set', 'Type is "set"' );
141 is( $f5->size, 1, 'Size is "1"' );
142 is( $f5->is_nullable, 1, 'Field can be null' );
143 is( $f5->default_value, 'b', 'Default value is "b"' );
144 is( $f5->is_primary_key, 0, 'Field is not PK' );
145 my %f5extra = $f5->extra;
146 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
148 my $f6 = shift @fields;
149 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
150 is( $f6->data_type, 'enum', 'Type is "enum"' );
151 is( $f6->size, 1, 'Size is "1"' );
152 is( $f6->is_nullable, 1, 'Field can be null' );
153 is( $f6->default_value, 'c', 'Default value is "c"' );
154 is( $f6->is_primary_key, 0, 'Field is not PK' );
155 my %f6extra = $f6->extra;
156 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
158 my $f7 = shift @fields;
159 is( $f7->name, 'name', 'Seventh field name is "name"' );
160 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
161 is( $f7->size, 30, 'Size is "30"' );
162 is( $f7->is_nullable, 1, 'Field can be null' );
163 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
164 is( $f7->is_primary_key, 0, 'Field is not PK' );
166 my $f8 = shift @fields;
167 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
168 is( $f8->data_type, 'enum', 'Type is "enum"' );
169 is( $f8->size, 2, 'Size is "2"' );
170 is( $f8->is_nullable, 0, 'Field cannot be null' );
171 is( $f8->default_value, 'vk', 'Default value is "vk"' );
172 is( $f8->is_primary_key, 0, 'Field is not PK' );
173 my %f8extra = $f8->extra;
174 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
176 my $f9 = shift @fields;
177 is( $f9->name, 'date', 'Ninth field name is "date"' );
178 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
179 is( $f9->size, 0, 'Size is "0"' );
180 is( $f9->is_nullable, 1, 'Field can be null' );
181 is( $f9->default_value, undef, 'Default value is undefined' );
182 is( $f9->is_primary_key, 0, 'Field is not PK' );
184 my $f10 = shift @fields;
185 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
186 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
187 is( $f10->size, 0, 'Size is "0"' );
188 is( $f10->is_nullable, 1, 'Field can be null' );
189 is( $f10->default_value, undef, 'Default value is undefined' );
190 is( $f10->is_primary_key, 0, 'Field is not PK' );
192 my @indices = $table->get_indices;
193 is( scalar @indices, 3, 'Right number of indices (3)' );
195 my $i1 = shift @indices;
196 is( $i1->name, '', 'No name on index' );
197 is( $i1->type, NORMAL, 'Normal index' );
198 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
200 my $i2 = shift @indices;
201 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
202 is( $i2->type, NORMAL, 'Normal index' );
203 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
205 my $i3 = shift @indices;
206 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
207 is( $i3->type, NORMAL, 'Normal index' );
208 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
210 my @constraints = $table->get_constraints;
211 is( scalar @constraints, 2, 'Right number of constraints (2)' );
213 my $c1 = shift @constraints;
214 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
215 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
217 my $c2 = shift @constraints;
218 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
219 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
223 my $tr = SQL::Translator->new;
224 my $data = parse($tr,
226 CREATE TABLE orders (
227 order_id integer NOT NULL auto_increment,
228 member_id varchar(255) comment 'fk to member',
229 billing_address_id int,
230 shipping_address_id int,
232 status smallint NOT NULL,
233 store_id varchar(255) NOT NULL REFERENCES store,
235 shipping_charge decimal(8,2),
236 price_paid decimal(8,2),
237 PRIMARY KEY (order_id) USING BTREE,
238 KEY (status) USING BTREE,
239 KEY USING BTREE (billing_address_id),
240 KEY (shipping_address_id),
241 KEY (member_id, store_id),
242 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
243 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
244 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
245 ) TYPE=INNODB COMMENT = 'orders table comment';
247 CREATE TABLE address (
248 address_id int NOT NULL auto_increment,
249 recipient varchar(255) NOT NULL,
250 address1 varchar(255) NOT NULL,
251 address2 varchar(255),
252 city varchar(255) NOT NULL,
253 state varchar(255) NOT NULL,
254 postal_code varchar(255) NOT NULL,
256 PRIMARY KEY (address_id)
261 my $schema = $tr->schema;
262 is( $schema->is_valid, 1, 'Schema is valid' );
263 my @tables = $schema->get_tables;
264 is( scalar @tables, 2, 'Right number of tables (2)' );
266 my $t1 = shift @tables;
267 is( $t1->name, 'orders', 'Found "orders" table' );
268 is( $t1->comments, 'orders table comment', 'Table comment OK' );
270 my @fields = $t1->get_fields;
271 is( scalar @fields, 10, 'Right number of fields (10)' );
273 my $f1 = shift @fields;
274 is( $f1->name, 'order_id', 'First field name is "order_id"' );
275 is( $f1->data_type, 'int', 'Type is "int"' );
276 is( $f1->size, 11, 'Size is "11"' );
277 is( $f1->is_nullable, 0, 'Field cannot be null' );
278 is( $f1->default_value, undef, 'Default value is undefined' );
279 is( $f1->is_primary_key, 1, 'Field is PK' );
280 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
282 my $f2 = shift @fields;
283 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
284 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
285 is( $f2->size, 255, 'Size is "255"' );
286 is( $f2->is_nullable, 1, 'Field can be null' );
287 is( $f2->comments, 'fk to member', 'Field comment OK' );
288 is( $f2->default_value, undef, 'Default value is undefined' );
290 my $f3 = shift @fields;
291 is( $f3->name, 'billing_address_id',
292 'Third field name is "billing_address_id"' );
293 is( $f3->data_type, 'int', 'Type is "int"' );
294 is( $f3->size, 11, 'Size is "11"' );
296 my $f4 = shift @fields;
297 is( $f4->name, 'shipping_address_id',
298 'Fourth field name is "shipping_address_id"' );
299 is( $f4->data_type, 'int', 'Type is "int"' );
300 is( $f4->size, 11, 'Size is "11"' );
302 my $f5 = shift @fields;
303 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
304 is( $f5->data_type, 'int', 'Type is "int"' );
305 is( $f5->size, 11, 'Size is "11"' );
307 my $f6 = shift @fields;
308 is( $f6->name, 'status', 'Sixth field name is "status"' );
309 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
310 is( $f6->size, 6, 'Size is "6"' );
311 is( $f6->is_nullable, 0, 'Field cannot be null' );
313 my $f7 = shift @fields;
314 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
315 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
316 is( $f7->size, 255, 'Size is "255"' );
317 is( $f7->is_nullable, 0, 'Field cannot be null' );
318 is( $f7->is_foreign_key, 1, 'Field is a FK' );
319 my $fk_ref = $f7->foreign_key_reference;
320 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
321 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
323 my $f8 = shift @fields;
324 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
325 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
326 is( $f8->size, '8,2', 'Size is "8,2"' );
328 my $f9 = shift @fields;
329 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
330 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
331 is( $f9->size, '8,2', 'Size is "8,2"' );
333 my $f10 = shift @fields;
334 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
335 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
336 is( $f10->size, '8,2', 'Size is "8,2"' );
338 my @indices = $t1->get_indices;
339 is( scalar @indices, 4, 'Right number of indices (4)' );
341 my $i1 = shift @indices;
342 is( $i1->type, NORMAL, 'First index is normal' );
343 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
345 my $i2 = shift @indices;
346 is( $i2->type, NORMAL, 'Second index is normal' );
347 is( join(',', $i2->fields), 'billing_address_id',
348 'Index is on "billing_address_id"' );
350 my $i3 = shift @indices;
351 is( $i3->type, NORMAL, 'Third index is normal' );
352 is( join(',', $i3->fields), 'shipping_address_id',
353 'Index is on "shipping_address_id"' );
355 my $i4 = shift @indices;
356 is( $i4->type, NORMAL, 'Third index is normal' );
357 is( join(',', $i4->fields), 'member_id,store_id',
358 'Index is on "member_id,store_id"' );
360 my @constraints = $t1->get_constraints;
361 is( scalar @constraints, 5, 'Right number of constraints (5)' );
363 my $c1 = shift @constraints;
364 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
365 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
367 my $c2 = shift @constraints;
368 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
369 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
370 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
371 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
373 my $c3 = shift @constraints;
374 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
375 is( join(',', $c3->fields), 'billing_address_id',
376 'Constraint is on "billing_address_id"' );
377 is( $c3->reference_table, 'address', 'To table "address"' );
378 is( join(',', $c3->reference_fields), 'address_id',
379 'To field "address_id"' );
381 my $c4 = shift @constraints;
382 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
383 is( join(',', $c4->fields), 'shipping_address_id',
384 'Constraint is on "shipping_address_id"' );
385 is( $c4->reference_table, 'address', 'To table "address"' );
386 is( join(',', $c4->reference_fields), 'address_id',
387 'To field "address_id"' );
389 my $c5 = shift @constraints;
390 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
391 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
392 is( $c5->reference_table, 'store', 'To table "store"' );
393 is( join(',', map { $_ || '' } $c5->reference_fields), '',
394 'No reference fields defined' );
396 my $t2 = shift @tables;
397 is( $t2->name, 'address', 'Found "address" table' );
399 my @t2_fields = $t2->get_fields;
400 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
405 # ALTER TABLE ADD FOREIGN KEY
406 # trailing comma on last create definition
407 # Ignoring INSERT statements
410 my $tr = SQL::Translator->new;
411 my $data = parse($tr,
416 id integer NOT NULL auto_increment,
417 two_id integer NOT NULL auto_increment,
424 id int NOT NULL auto_increment,
425 one_id int NOT NULL auto_increment,
429 FOREIGN KEY (one_id) REFERENCES one (id),
432 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
434 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
438 my $schema = $tr->schema;
439 is( $schema->is_valid, 1, 'Schema is valid' );
440 my $db_name = $schema->name;
441 is( $db_name, 'database_name', 'Database name extracted from USE' );
442 my @tables = $schema->get_tables;
443 is( scalar @tables, 2, 'Right number of tables (2)' );
444 my $table1 = shift @tables;
445 is( $table1->name, 'one', 'Found "one" table' );
446 my $table2 = shift @tables;
447 is( $table2->name, 'two', 'Found "two" table' );
449 my @constraints = $table1->get_constraints;
450 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
452 my $t1c1 = shift @constraints;
453 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
454 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
456 my $t1c2 = shift @constraints;
457 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
458 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
459 is( $t1c2->reference_table, 'two', 'To table "two"' );
460 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
462 @constraints = $table2->get_constraints;
463 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
465 my $t2c1 = shift @constraints;
466 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
467 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
469 my $t2c2 = shift @constraints;
470 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
471 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
472 is( $t2c2->reference_table, 'one', 'To table "one"' );
473 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
477 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
478 # char fields with character set and collate qualifiers
479 # timestamp fields with on update qualifier
480 # charset table option
483 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
484 my $data = parse($tr,
487 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
488 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
489 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
490 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
491 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
495 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
496 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
497 ) TYPE=INNODB DEFAULT CHARSET=latin1;
499 /*!50001 CREATE ALGORITHM=UNDEFINED */
500 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
501 /*! VIEW `vs_asset` AS
502 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
503 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
504 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
505 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
506 `a`.`foreign_asset_id` AS `foreign_asset_id`,
507 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
508 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
509 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
510 `m`.`user_id` AS `user_access`
511 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
514 /*! VIEW `vs_asset2` AS
515 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
516 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
517 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
518 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
519 `a`.`foreign_asset_id` AS `foreign_asset_id`,
520 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
521 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
522 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
523 `m`.`user_id` AS `user_access`
524 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
526 /*!50001 CREATE OR REPLACE */
527 /*! VIEW `vs_asset3` AS
528 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
529 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
530 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
531 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
532 `a`.`foreign_asset_id` AS `foreign_asset_id`,
533 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
534 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
535 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
536 `m`.`user_id` AS `user_access`
537 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
539 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
542 DECLARE rval TIMESTAMP;
543 IF ( millis_since_1970 = 0 )
547 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
551 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
553 DECLARE hasMoreRows BOOL DEFAULT TRUE;
554 DECLARE t_group_id INT;
555 DECLARE t_user_id INT ;
556 DECLARE t_user_name VARCHAR (512) ;
557 DECLARE t_message VARCHAR (512) ;
559 DROP TABLE IF EXISTS group_acl;
560 DROP TABLE IF EXISTS user_group;
561 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
563 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
564 FROM asset d, acl_entry e, alterpoint_principal p
565 WHERE d.acl_id = e.acl
566 AND p.id = e.principal AND d.acl_id = t_acl_id;
568 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
569 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
570 WHERE a.id = b.user_ref AND b.elt = c.id;
572 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
573 FROM group_acl, user_group
574 WHERE group_acl.group_id = user_group.group_id ;
579 my $schema = $tr->schema;
580 is( $schema->is_valid, 1, 'Schema is valid' );
581 my @tables = $schema->get_tables;
582 is( scalar @tables, 1, 'Right number of tables (1)' );
583 my $table1 = shift @tables;
584 is( $table1->name, 'one', 'Found "one" table' );
586 my @fields = $table1->get_fields;
587 is(scalar @fields, 2, 'Right number of fields (2) on table one');
588 my $tableTypeFound = 0;
589 my $charsetFound = 0;
590 for my $t1_option_ref ( $table1->options ) {
591 my($key, $value) = %{$t1_option_ref};
592 if ( $key eq 'TYPE' ) {
593 is($value, 'INNODB', 'Table has right table type option' );
595 } elsif ( $key eq 'CHARACTER SET' ) {
596 is($value, 'latin1', 'Table has right character set option' );
600 fail('Table did not have a type option') unless $tableTypeFound;
601 fail('Table did not have a character set option') unless $charsetFound;
603 my $t1f1 = shift @fields;
604 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
605 is( $t1f1->size, 255, 'Field is right size' );
606 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
607 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
608 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
610 my $t1f2 = shift @fields;
611 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
612 ok( !$t1f2->is_nullable, 'Field is not nullable' );
614 $t1f2->default_value,
615 \'CURRENT_TIMESTAMP',
616 'Field has right default value'
618 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
620 my @views = $schema->get_views;
621 is( scalar @views, 3, 'Right number of views (3)' );
623 my ($view1, $view2, $view3) = @views;
624 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
625 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
626 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
627 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
628 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
629 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
631 my @procs = $schema->get_procedures;
632 is( scalar @procs, 2, 'Right number of procedures (2)' );
633 my $proc1 = shift @procs;
634 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
635 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
636 my $proc2 = shift @procs;
637 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
638 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
641 # Tests for collate table option
643 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
644 my $data = parse($tr,
646 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
649 my $schema = $tr->schema;
650 is( $schema->is_valid, 1, 'Schema is valid' );
651 my @tables = $schema->get_tables;
652 is( scalar @tables, 1, 'Right number of tables (1)' );
653 my $table1 = shift @tables;
654 is( $table1->name, 'test', 'Found "test" table' );
657 my $collate = "Not found!";
658 my $charset = "Not found!";
659 for my $t1_option_ref ( $table1->options ) {
660 my($key, $value) = %{$t1_option_ref};
661 $collate = $value if $key eq 'COLLATE';
662 $charset = $value if $key eq 'CHARACTER SET';
664 is($collate, 'latin1_bin', "Collate found");
665 is($charset, 'latin1', "Character set found");
668 # Test the mysql version parser (probably needs to migrate to t/utils.t)
671 '3.23.2' => 3.023002,
674 '5.01.0' => 5.001000,
686 for my $target (keys %$parse_as) {
687 for my $str (keys %{$parse_as->{$target}}) {
689 SQL::Translator::Utils::parse_mysql_version ($str, $target),
691 $parse_as->{$target}{$str},
692 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
697 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
698 ok ($@, 'Exception thrown on invalid version string');
701 my $tr = SQL::Translator->new;
702 my $data = q|create table merge_example (
703 id int(11) NOT NULL auto_increment,
704 shape_field geometry NOT NULL,
706 SPATIAL KEY shape_field (shape_field)
707 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
709 my $val = parse($tr, $data);
710 my $schema = $tr->schema;
711 is( $schema->is_valid, 1, 'Schema is valid' );
712 my @tables = $schema->get_tables;
713 is( scalar @tables, 1, 'Right number of tables (1)' );
714 my $table = shift @tables;
715 is( $table->name, 'merge_example', 'Found "merge_example" table' );
717 my $tableTypeFound = 0;
719 for my $t_option_ref ( $table->options ) {
720 my($key, $value) = %{$t_option_ref};
721 if ( $key eq 'ENGINE' ) {
722 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
724 } elsif ( $key eq 'UNION' ) {
725 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
726 "UNION option has correct set");
731 fail('Table did not have a type option') unless $tableTypeFound;
732 fail('Table did not have a union option') unless $unionFound;
734 my @fields = $table->get_fields;
735 is( scalar @fields, 2, 'Right number of fields (2)' );
736 my $f1 = shift @fields;
737 my $f2 = shift @fields;
738 is( $f1->name, 'id', 'First field name is "id"' );
739 is( $f1->data_type, 'int', 'Type is "int"' );
740 is( $f1->size, 11, 'Size is "11"' );
741 is( $f1->is_nullable, 0, 'Field cannot be null' );
742 is( $f1->is_primary_key, 1, 'Field is PK' );
744 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
745 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
746 is( $f2->is_nullable, 0, 'Field cannot be null' );
747 is( $f2->is_primary_key, 0, 'Field is not PK' );
749 my @indices = $table->get_indices;
750 is( scalar @indices, 1, 'Right number of indices (1)' );
751 my $i1 = shift @indices;
752 is( $i1->name, 'shape_field', 'No name on index' );
753 is( $i1->type, SPATIAL, 'Spatial index' );
755 my @constraints = $table->get_constraints;
756 is( scalar @constraints, 1, 'Right number of constraints (1)' );
757 my $c = shift @constraints;
758 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
759 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
764 q|create table quote (
765 id int(11) NOT NULL auto_increment,
768 q|create table quote (
769 id int(11) NOT NULL auto_increment,
772 q|create table quote (
773 id int(11) NOT NULL auto_increment,
777 for my $data (@data) {
778 my $tr = SQL::Translator->new;
780 my $val = parse($tr, $data);
781 my $schema = $tr->schema;
782 is( $schema->is_valid, 1, 'Schema is valid' );
783 my @tables = $schema->get_tables;
784 is( scalar @tables, 1, 'Right number of tables (1)' );
785 my $table = shift @tables;
786 is( $table->name, 'quote', 'Found "quote" table' );
788 my $tableTypeFound = 0;
789 for my $t_option_ref ( $table->options ) {
790 my($key, $value) = %{$t_option_ref};
791 if ( $key eq 'ENGINE' ) {
792 is($value, 'innodb', 'Table has right table engine option' );
797 fail('Table did not have a type option') unless $tableTypeFound;
799 my @fields = $table->get_fields;
800 my $f1 = shift @fields;
801 is( $f1->name, 'id', 'First field name is "id"' );
802 is( $f1->data_type, 'int', 'Type is "int"' );
803 is( $f1->size, 11, 'Size is "11"' );
804 is( $f1->is_nullable, 0, 'Field cannot be null' );
805 is( $f1->is_primary_key, 1, 'Field is PK' );
810 my $tr = SQL::Translator->new;
811 my $data = q|create table "sessions" (
812 id char(32) not null default '0' primary key,
813 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
814 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
815 key using btree (ssn)
818 my $val = parse($tr, $data);
819 my $schema = $tr->schema;
820 is( $schema->is_valid, 1, 'Schema is valid' );
821 my @tables = $schema->get_tables;
822 is( scalar @tables, 1, 'Right number of tables (1)' );
823 my $table = shift @tables;
824 is( $table->name, 'sessions', 'Found "sessions" table' );
826 my @fields = $table->get_fields;
827 is( scalar @fields, 3, 'Right number of fields (3)' );
828 my $f1 = shift @fields;
829 my $f2 = shift @fields;
830 my $f3 = shift @fields;
831 is( $f1->name, 'id', 'First field name is "id"' );
832 is( $f1->data_type, 'char', 'Type is "char"' );
833 is( $f1->size, 32, 'Size is "32"' );
834 is( $f1->is_nullable, 0, 'Field cannot be null' );
835 is( $f1->default_value, '0', 'Default value is "0"' );
836 is( $f1->is_primary_key, 1, 'Field is PK' );
838 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
839 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
840 is( $f2->size, 12, 'Size is "12"' );
841 is( $f2->is_nullable, 0, 'Field can not be null' );
842 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
843 is( $f2->is_primary_key, 0, 'Field is not PK' );
845 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
846 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
847 is( $f3->name, 'user', 'Second field name is "user"' );
848 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
849 is( $f3->size, 20, 'Size is "20"' );
850 is( $f3->is_nullable, 0, 'Field can not be null' );
851 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
852 is( $f3->is_primary_key, 0, 'Field is not PK' );