6 #use SQL::Translator::Schema::Constants;
7 use SQL::Translator::Constants qw(:sqlt_types :sqlt_constants);
8 #use SQL::Translator::Utils qw//;
9 use Test::SQL::Translator qw(maybe_plan);
12 maybe_plan(317, "SQL::Translator::Parser::DDL::MySQL");
13 # SQL::Translator::Parser::DDL::MySQL->import('parse');
17 my $tr = SQL::Translator->new({ from => 'MySQL' });
18 my $data = q|create table "sessions" (
19 id char(32) not null default '0' primary key,
21 ssn varchar(12) unique key,
23 fulltext key `session_fulltext` (a_session)
26 #my $val = $tr->parse();
27 # my $val = $tr->parse($data);
28 my $schema = $tr->parse($data);
29 is( $schema->is_valid, 1, 'Schema is valid' );
30 my @tables = $schema->get_tables;
31 is( scalar @tables, 1, 'Right number of tables (1)' );
32 my $table = shift @tables;
33 is( $table->name, 'sessions', 'Found "sessions" table' );
35 my @fields = $table->get_fields;
36 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)' );
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 $c1 = shift @constraints;
63 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
64 is( join(',', $c1->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({ from => 'MySQL' });
72 my $schema = $tr->parse(
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,
89 KEY name_idx (name(10))
90 ) TYPE=MyISAM PACK_KEYS=1;
94 # my $schema = $tr->schema;
95 is( $schema->is_valid, 1, 'Schema is valid' );
96 my @tables = $schema->get_tables;
97 is( scalar @tables, 1, 'Right number of tables (1)' );
98 my $table = shift @tables;
99 is( $table->name, 'check', 'Found "check" table' );
101 my @fields = $table->get_fields;
102 is( scalar @fields, 10, 'Right number of fields (10)' );
103 my $f1 = shift @fields;
104 is( $f1->name, 'check_id', 'First field name is "check_id"' );
105 is( $f1->data_type, 'int', 'Type is "int"' );
106 is( $f1->size, 7, 'Size is "7"' );
107 is( $f1->is_nullable, 0, 'Field cannot be null' );
108 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
109 is( $f1->is_primary_key, 1, 'Field is PK' );
110 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
111 my %extra = $f1->extra;
112 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
113 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
115 my $f2 = shift @fields;
116 is( $f2->name, 'successful', 'Second field name is "successful"' );
117 is( $f2->data_type, 'date', 'Type is "date"' );
118 is( $f2->size, 0, 'Size is "0"' );
119 is( $f2->is_nullable, 0, 'Field cannot be null' );
120 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
121 is( $f2->is_primary_key, 0, 'Field is not PK' );
123 my $f3 = shift @fields;
124 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
125 is( $f3->data_type, 'date', 'Type is "date"' );
126 is( $f3->size, 0, 'Size is "0"' );
127 is( $f3->is_nullable, 1, 'Field can be null' );
128 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
129 is( $f3->is_primary_key, 0, 'Field is not PK' );
131 my $f4 = shift @fields;
132 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
133 is( $f4->data_type, 'int', 'Type is "int"' );
134 is( $f4->size, 11, 'Size is "11"' );
135 is( $f4->is_nullable, 0, 'Field cannot be null' );
136 is( $f4->default_value, '0', 'Default value is "0"' );
137 is( $f4->is_primary_key, 0, 'Field is not PK' );
139 my $f5 = shift @fields;
140 is( $f5->name, 's1', 'Fifth field name is "s1"' );
141 is( $f5->data_type, 'set', 'Type is "set"' );
142 is( $f5->size, 1, 'Size is "1"' );
143 is( $f5->is_nullable, 1, 'Field can be null' );
144 is( $f5->default_value, 'b', 'Default value is "b"' );
145 is( $f5->is_primary_key, 0, 'Field is not PK' );
146 my %f5extra = $f5->extra;
147 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
149 my $f6 = shift @fields;
150 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
151 is( $f6->data_type, 'enum', 'Type is "enum"' );
152 is( $f6->size, 1, 'Size is "1"' );
153 is( $f6->is_nullable, 1, 'Field can be null' );
154 is( $f6->default_value, 'c', 'Default value is "c"' );
155 is( $f6->is_primary_key, 0, 'Field is not PK' );
156 my %f6extra = $f6->extra;
157 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
159 my $f7 = shift @fields;
160 is( $f7->name, 'name', 'Seventh field name is "name"' );
161 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
162 is( $f7->size, 30, 'Size is "30"' );
163 is( $f7->is_nullable, 1, 'Field can be null' );
164 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
165 is( $f7->is_primary_key, 0, 'Field is not PK' );
167 my $f8 = shift @fields;
168 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
169 is( $f8->data_type, 'enum', 'Type is "enum"' );
170 is( $f8->size, 2, 'Size is "2"' );
171 is( $f8->is_nullable, 0, 'Field cannot be null' );
172 is( $f8->default_value, 'vk', 'Default value is "vk"' );
173 is( $f8->is_primary_key, 0, 'Field is not PK' );
174 my %f8extra = $f8->extra;
175 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
177 my $f9 = shift @fields;
178 is( $f9->name, 'date', 'Ninth field name is "date"' );
179 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
180 is( $f9->size, 0, 'Size is "0"' );
181 is( $f9->is_nullable, 1, 'Field can be null' );
182 is( $f9->default_value, undef, 'Default value is undefined' );
183 is( $f9->is_primary_key, 0, 'Field is not PK' );
185 my $f10 = shift @fields;
186 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
187 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
188 is( $f10->size, 0, 'Size is "0"' );
189 is( $f10->is_nullable, 1, 'Field can be null' );
190 is( $f10->default_value, undef, 'Default value is undefined' );
191 is( $f10->is_primary_key, 0, 'Field is not PK' );
193 my @indices = $table->get_indices;
194 is( scalar @indices, 3, 'Right number of indices (3)' );
196 my $i1 = shift @indices;
197 is( $i1->name, '', 'No name on index' );
198 is( $i1->type, NORMAL, 'Normal index' );
199 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
201 my $i2 = shift @indices;
202 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
203 is( $i2->type, NORMAL, 'Normal index' );
204 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
206 my $i3 = shift @indices;
207 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
208 is( $i3->type, NORMAL, 'Normal index' );
209 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
211 my @constraints = $table->get_constraints;
212 is( scalar @constraints, 2, 'Right number of constraints (2)' );
214 my $c1 = shift @constraints;
215 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
216 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
218 my $c2 = shift @constraints;
219 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
220 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
224 my $tr = SQL::Translator->new;
225 my $data = parse($tr,
227 CREATE TABLE orders (
228 order_id integer NOT NULL auto_increment,
229 member_id varchar(255) comment 'fk to member',
230 billing_address_id int,
231 shipping_address_id int,
233 status smallint NOT NULL,
234 store_id varchar(255) NOT NULL REFERENCES store,
236 shipping_charge decimal(8,2),
237 price_paid decimal(8,2),
238 PRIMARY KEY (order_id),
240 KEY (billing_address_id),
241 KEY (shipping_address_id),
242 KEY (member_id, store_id),
243 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
244 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
245 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
246 ) TYPE=INNODB COMMENT = 'orders table comment';
248 CREATE TABLE address (
249 address_id int NOT NULL auto_increment,
250 recipient varchar(255) NOT NULL,
251 address1 varchar(255) NOT NULL,
252 address2 varchar(255),
253 city varchar(255) NOT NULL,
254 state varchar(255) NOT NULL,
255 postal_code varchar(255) NOT NULL,
257 PRIMARY KEY (address_id)
262 my $schema = $tr->schema;
263 is( $schema->is_valid, 1, 'Schema is valid' );
264 my @tables = $schema->get_tables;
265 is( scalar @tables, 2, 'Right number of tables (2)' );
267 my $t1 = shift @tables;
268 is( $t1->name, 'orders', 'Found "orders" table' );
269 is( $t1->comments, 'orders table comment', 'Table comment OK' );
271 my @fields = $t1->get_fields;
272 is( scalar @fields, 10, 'Right number of fields (10)' );
274 my $f1 = shift @fields;
275 is( $f1->name, 'order_id', 'First field name is "order_id"' );
276 is( $f1->data_type, 'int', 'Type is "int"' );
277 is( $f1->size, 11, 'Size is "11"' );
278 is( $f1->is_nullable, 0, 'Field cannot be null' );
279 is( $f1->default_value, undef, 'Default value is undefined' );
280 is( $f1->is_primary_key, 1, 'Field is PK' );
281 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
283 my $f2 = shift @fields;
284 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
285 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
286 is( $f2->size, 255, 'Size is "255"' );
287 is( $f2->is_nullable, 1, 'Field can be null' );
288 is( $f2->comments, 'fk to member', 'Field comment OK' );
289 is( $f2->default_value, undef, 'Default value is undefined' );
291 my $f3 = shift @fields;
292 is( $f3->name, 'billing_address_id',
293 'Third field name is "billing_address_id"' );
294 is( $f3->data_type, 'int', 'Type is "int"' );
295 is( $f3->size, 11, 'Size is "11"' );
297 my $f4 = shift @fields;
298 is( $f4->name, 'shipping_address_id',
299 'Fourth field name is "shipping_address_id"' );
300 is( $f4->data_type, 'int', 'Type is "int"' );
301 is( $f4->size, 11, 'Size is "11"' );
303 my $f5 = shift @fields;
304 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
305 is( $f5->data_type, 'int', 'Type is "int"' );
306 is( $f5->size, 11, 'Size is "11"' );
308 my $f6 = shift @fields;
309 is( $f6->name, 'status', 'Sixth field name is "status"' );
310 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
311 is( $f6->size, 6, 'Size is "6"' );
312 is( $f6->is_nullable, 0, 'Field cannot be null' );
314 my $f7 = shift @fields;
315 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
316 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
317 is( $f7->size, 255, 'Size is "255"' );
318 is( $f7->is_nullable, 0, 'Field cannot be null' );
319 is( $f7->is_foreign_key, 1, 'Field is a FK' );
320 my $fk_ref = $f7->foreign_key_reference;
321 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
322 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
324 my $f8 = shift @fields;
325 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
326 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
327 is( $f8->size, '8,2', 'Size is "8,2"' );
329 my $f9 = shift @fields;
330 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
331 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
332 is( $f9->size, '8,2', 'Size is "8,2"' );
334 my $f10 = shift @fields;
335 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
336 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
337 is( $f10->size, '8,2', 'Size is "8,2"' );
339 my @indices = $t1->get_indices;
340 is( scalar @indices, 4, 'Right number of indices (4)' );
342 my $i1 = shift @indices;
343 is( $i1->type, NORMAL, 'First index is normal' );
344 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
346 my $i2 = shift @indices;
347 is( $i2->type, NORMAL, 'Second index is normal' );
348 is( join(',', $i2->fields), 'billing_address_id',
349 'Index is on "billing_address_id"' );
351 my $i3 = shift @indices;
352 is( $i3->type, NORMAL, 'Third index is normal' );
353 is( join(',', $i3->fields), 'shipping_address_id',
354 'Index is on "shipping_address_id"' );
356 my $i4 = shift @indices;
357 is( $i4->type, NORMAL, 'Third index is normal' );
358 is( join(',', $i4->fields), 'member_id,store_id',
359 'Index is on "member_id,store_id"' );
361 my @constraints = $t1->get_constraints;
362 is( scalar @constraints, 5, 'Right number of constraints (5)' );
364 my $c1 = shift @constraints;
365 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
366 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
368 my $c2 = shift @constraints;
369 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
370 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
371 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
372 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
374 my $c3 = shift @constraints;
375 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
376 is( join(',', $c3->fields), 'billing_address_id',
377 'Constraint is on "billing_address_id"' );
378 is( $c3->reference_table, 'address', 'To table "address"' );
379 is( join(',', $c3->reference_fields), 'address_id',
380 'To field "address_id"' );
382 my $c4 = shift @constraints;
383 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
384 is( join(',', $c4->fields), 'shipping_address_id',
385 'Constraint is on "shipping_address_id"' );
386 is( $c4->reference_table, 'address', 'To table "address"' );
387 is( join(',', $c4->reference_fields), 'address_id',
388 'To field "address_id"' );
390 my $c5 = shift @constraints;
391 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
392 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
393 is( $c5->reference_table, 'store', 'To table "store"' );
394 is( join(',', map { $_ || '' } $c5->reference_fields), '',
395 'No reference fields defined' );
397 my $t2 = shift @tables;
398 is( $t2->name, 'address', 'Found "address" table' );
400 my @t2_fields = $t2->get_fields;
401 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
406 # ALTER TABLE ADD FOREIGN KEY
407 # trailing comma on last create definition
408 # Ignoring INSERT statements
411 my $tr = SQL::Translator->new;
412 my $data = parse($tr,
417 id integer NOT NULL auto_increment,
418 two_id integer NOT NULL auto_increment,
425 id int NOT NULL auto_increment,
426 one_id int NOT NULL auto_increment,
430 FOREIGN KEY (one_id) REFERENCES one (id),
433 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
435 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
439 my $schema = $tr->schema;
440 is( $schema->is_valid, 1, 'Schema is valid' );
441 my $db_name = $schema->name;
442 is( $db_name, 'database_name', 'Database name extracted from USE' );
443 my @tables = $schema->get_tables;
444 is( scalar @tables, 2, 'Right number of tables (2)' );
445 my $table1 = shift @tables;
446 is( $table1->name, 'one', 'Found "one" table' );
447 my $table2 = shift @tables;
448 is( $table2->name, 'two', 'Found "two" table' );
450 my @constraints = $table1->get_constraints;
451 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
453 my $t1c1 = shift @constraints;
454 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
455 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
457 my $t1c2 = shift @constraints;
458 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
459 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
460 is( $t1c2->reference_table, 'two', 'To table "two"' );
461 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
463 @constraints = $table2->get_constraints;
464 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
466 my $t2c1 = shift @constraints;
467 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
468 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
470 my $t2c2 = shift @constraints;
471 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
472 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
473 is( $t2c2->reference_table, 'one', 'To table "one"' );
474 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
478 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
479 # char fields with character set and collate qualifiers
480 # timestamp fields with on update qualifier
481 # charset table option
484 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
485 my $data = parse($tr,
488 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
489 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
490 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
491 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
492 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
496 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
497 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
498 ) TYPE=INNODB DEFAULT CHARSET=latin1;
500 /*!50001 CREATE ALGORITHM=UNDEFINED */
501 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
502 /*! VIEW `vs_asset` AS
503 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
504 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
505 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
506 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
507 `a`.`foreign_asset_id` AS `foreign_asset_id`,
508 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
509 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
510 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
511 `m`.`user_id` AS `user_access`
512 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
515 /*! VIEW `vs_asset2` AS
516 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
517 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
518 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
519 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
520 `a`.`foreign_asset_id` AS `foreign_asset_id`,
521 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
522 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
523 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
524 `m`.`user_id` AS `user_access`
525 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
527 /*!50001 CREATE OR REPLACE */
528 /*! VIEW `vs_asset3` AS
529 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
530 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
531 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
532 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
533 `a`.`foreign_asset_id` AS `foreign_asset_id`,
534 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
535 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
536 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
537 `m`.`user_id` AS `user_access`
538 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
540 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
543 DECLARE rval TIMESTAMP;
544 IF ( millis_since_1970 = 0 )
548 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
552 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
554 DECLARE hasMoreRows BOOL DEFAULT TRUE;
555 DECLARE t_group_id INT;
556 DECLARE t_user_id INT ;
557 DECLARE t_user_name VARCHAR (512) ;
558 DECLARE t_message VARCHAR (512) ;
560 DROP TABLE IF EXISTS group_acl;
561 DROP TABLE IF EXISTS user_group;
562 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
564 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
565 FROM asset d, acl_entry e, alterpoint_principal p
566 WHERE d.acl_id = e.acl
567 AND p.id = e.principal AND d.acl_id = t_acl_id;
569 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
570 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
571 WHERE a.id = b.user_ref AND b.elt = c.id;
573 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
574 FROM group_acl, user_group
575 WHERE group_acl.group_id = user_group.group_id ;
580 my $schema = $tr->schema;
581 is( $schema->is_valid, 1, 'Schema is valid' );
582 my @tables = $schema->get_tables;
583 is( scalar @tables, 1, 'Right number of tables (1)' );
584 my $table1 = shift @tables;
585 is( $table1->name, 'one', 'Found "one" table' );
587 my @fields = $table1->get_fields;
588 is(scalar @fields, 2, 'Right number of fields (2) on table one');
589 my $tableTypeFound = 0;
590 my $charsetFound = 0;
591 for my $t1_option_ref ( $table1->options ) {
592 my($key, $value) = %{$t1_option_ref};
593 if ( $key eq 'TYPE' ) {
594 is($value, 'INNODB', 'Table has right table type option' );
596 } elsif ( $key eq 'CHARACTER SET' ) {
597 is($value, 'latin1', 'Table has right character set option' );
601 fail('Table did not have a type option') unless $tableTypeFound;
602 fail('Table did not have a character set option') unless $charsetFound;
604 my $t1f1 = shift @fields;
605 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
606 is( $t1f1->size, 255, 'Field is right size' );
607 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
608 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
609 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
611 my $t1f2 = shift @fields;
612 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
613 ok( !$t1f2->is_nullable, 'Field is not nullable' );
615 $t1f2->default_value,
616 \'CURRENT_TIMESTAMP',
617 'Field has right default value'
619 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
621 my @views = $schema->get_views;
622 is( scalar @views, 3, 'Right number of views (3)' );
623 my ($view3, $view1, $view2) = @views;
624 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
625 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
626 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
627 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
628 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
629 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
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',
817 my $val = parse($tr, $data);
818 my $schema = $tr->schema;
819 is( $schema->is_valid, 1, 'Schema is valid' );
820 my @tables = $schema->get_tables;
821 is( scalar @tables, 1, 'Right number of tables (1)' );
822 my $table = shift @tables;
823 is( $table->name, 'sessions', 'Found "sessions" table' );
825 my @fields = $table->get_fields;
826 is( scalar @fields, 3, 'Right number of fields (3)' );
827 my $f1 = shift @fields;
828 my $f2 = shift @fields;
829 my $f3 = shift @fields;
830 is( $f1->name, 'id', 'First field name is "id"' );
831 is( $f1->data_type, 'char', 'Type is "char"' );
832 is( $f1->size, 32, 'Size is "32"' );
833 is( $f1->is_nullable, 0, 'Field cannot be null' );
834 is( $f1->default_value, '0', 'Default value is "0"' );
835 is( $f1->is_primary_key, 1, 'Field is PK' );
837 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
838 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
839 is( $f2->size, 12, 'Size is "12"' );
840 is( $f2->is_nullable, 0, 'Field can not be null' );
841 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
842 is( $f2->is_primary_key, 0, 'Field is not PK' );
844 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
845 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
846 is( $f3->name, 'user', 'Second field name is "user"' );
847 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
848 is( $f3->size, 20, 'Size is "20"' );
849 is( $f3->is_nullable, 0, 'Field can not be null' );
850 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
851 is( $f3->is_primary_key, 0, 'Field is not PK' );