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