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 => 50013});
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 /*!50014 DEFINER=`BOGUS` */
531 /*! VIEW `vs_asset` AS
532 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
533 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
534 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
535 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
536 `a`.`foreign_asset_id` AS `foreign_asset_id`,
537 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
538 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
539 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
540 `m`.`user_id` AS `user_access`
541 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
544 /*! VIEW `vs_asset2` AS
545 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
546 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
547 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
548 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
549 `a`.`foreign_asset_id` AS `foreign_asset_id`,
550 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
551 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
552 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
553 `m`.`user_id` AS `user_access`
554 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
556 /*!50001 CREATE OR REPLACE */
557 /*! VIEW `vs_asset3` AS
558 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
559 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
560 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
561 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
562 `a`.`foreign_asset_id` AS `foreign_asset_id`,
563 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
564 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
565 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
566 `m`.`user_id` AS `user_access`
567 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
569 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
572 DECLARE rval TIMESTAMP;
573 IF ( millis_since_1970 = 0 )
577 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
581 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
583 DECLARE hasMoreRows BOOL DEFAULT TRUE;
584 DECLARE t_group_id INT;
585 DECLARE t_user_id INT ;
586 DECLARE t_user_name VARCHAR (512) ;
587 DECLARE t_message VARCHAR (512) ;
589 DROP TABLE IF EXISTS group_acl;
590 DROP TABLE IF EXISTS user_group;
591 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
593 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
594 FROM asset d, acl_entry e, alterpoint_principal p
595 WHERE d.acl_id = e.acl
596 AND p.id = e.principal AND d.acl_id = t_acl_id;
598 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
599 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
600 WHERE a.id = b.user_ref AND b.elt = c.id;
602 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
603 FROM group_acl, user_group
604 WHERE group_acl.group_id = user_group.group_id ;
609 my $schema = $tr->schema;
610 is( $schema->is_valid, 1, 'Schema is valid' );
611 my @tables = $schema->get_tables;
612 is( scalar @tables, 1, 'Right number of tables (1)' );
613 my $table1 = shift @tables;
614 is( $table1->name, 'one', 'Found "one" table' );
616 my @fields = $table1->get_fields;
617 is(scalar @fields, 3, 'Right number of fields (3) on table one');
618 my $tableTypeFound = 0;
619 my $charsetFound = 0;
620 for my $t1_option_ref ( $table1->options ) {
621 my($key, $value) = %{$t1_option_ref};
622 if ( $key eq 'TYPE' ) {
623 is($value, 'INNODB', 'Table has right table type option' );
625 } elsif ( $key eq 'CHARACTER SET' ) {
626 is($value, 'latin1', 'Table has right character set option' );
630 fail('Table did not have a type option') unless $tableTypeFound;
631 fail('Table did not have a character set option') unless $charsetFound;
633 my $t1f1 = shift @fields;
634 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
635 is( $t1f1->size, 255, 'Field is right size' );
636 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
637 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
638 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
640 my $t1f2 = shift @fields;
641 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
642 ok( !$t1f2->is_nullable, 'Field is not nullable' );
644 $t1f2->default_value,
645 \'CURRENT_TIMESTAMP',
646 'Field has right default value'
648 is_deeply( $t1f2->extra('on update'), \'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
650 my $t1f3 = shift @fields;
651 is( $t1f3->data_type, 'datetime', 'Field is a datetime' );
652 ok( !$t1f3->is_nullable, 'Field is not nullable' );
654 $t1f3->default_value,
655 \'CURRENT_TIMESTAMP',
656 'Field has right default value'
659 my @views = $schema->get_views;
660 is( scalar @views, 3, 'Right number of views (3)' );
662 my ($view1, $view2, $view3) = @views;
663 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
664 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
665 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
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 @options = $view1->options;
685 'ALGORITHM=UNDEFINED',
686 'DEFINER=`cmdomain`@`localhost`',
687 'SQL SECURITY DEFINER',
689 'Only version 50013 options parsed',
692 my @procs = $schema->get_procedures;
693 is( scalar @procs, 2, 'Right number of procedures (2)' );
694 my $proc1 = shift @procs;
695 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
696 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
697 my $proc2 = shift @procs;
698 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
699 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
702 # Tests for collate table option
704 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
705 my $data = parse($tr,
707 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
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 $table1 = shift @tables;
715 is( $table1->name, 'test', 'Found "test" table' );
718 my $collate = "Not found!";
719 my $charset = "Not found!";
720 for my $t1_option_ref ( $table1->options ) {
721 my($key, $value) = %{$t1_option_ref};
722 $collate = $value if $key eq 'COLLATE';
723 $charset = $value if $key eq 'CHARACTER SET';
725 is($collate, 'latin1_bin', "Collate found");
726 is($charset, 'latin1', "Character set found");
729 # Test the mysql version parser (probably needs to migrate to t/utils.t)
732 '3.23.2' => 3.023002,
735 '5.01.0' => 5.001000,
747 for my $target (keys %$parse_as) {
748 for my $str (keys %{$parse_as->{$target}}) {
750 SQL::Translator::Utils::parse_mysql_version ($str, $target),
752 $parse_as->{$target}{$str},
753 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
758 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
759 ok ($@, 'Exception thrown on invalid version string');
762 my $tr = SQL::Translator->new;
763 my $data = q|create table merge_example (
764 id int(11) NOT NULL auto_increment,
765 shape_field geometry NOT NULL,
767 SPATIAL KEY shape_field (shape_field)
768 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
770 my $val = parse($tr, $data);
771 my $schema = $tr->schema;
772 is( $schema->is_valid, 1, 'Schema is valid' );
773 my @tables = $schema->get_tables;
774 is( scalar @tables, 1, 'Right number of tables (1)' );
775 my $table = shift @tables;
776 is( $table->name, 'merge_example', 'Found "merge_example" table' );
778 my $tableTypeFound = 0;
780 for my $t_option_ref ( $table->options ) {
781 my($key, $value) = %{$t_option_ref};
782 if ( $key eq 'ENGINE' ) {
783 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
785 } elsif ( $key eq 'UNION' ) {
786 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
787 "UNION option has correct set");
792 fail('Table did not have a type option') unless $tableTypeFound;
793 fail('Table did not have a union option') unless $unionFound;
795 my @fields = $table->get_fields;
796 is( scalar @fields, 2, 'Right number of fields (2)' );
797 my $f1 = shift @fields;
798 my $f2 = shift @fields;
799 is( $f1->name, 'id', 'First field name is "id"' );
800 is( $f1->data_type, 'int', 'Type is "int"' );
801 is( $f1->size, 11, 'Size is "11"' );
802 is( $f1->is_nullable, 0, 'Field cannot be null' );
803 is( $f1->is_primary_key, 1, 'Field is PK' );
805 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
806 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
807 is( $f2->is_nullable, 0, 'Field cannot be null' );
808 is( $f2->is_primary_key, 0, 'Field is not PK' );
810 my @indices = $table->get_indices;
811 is( scalar @indices, 1, 'Right number of indices (1)' );
812 my $i1 = shift @indices;
813 is( $i1->name, 'shape_field', 'No name on index' );
814 is( $i1->type, SPATIAL, 'Spatial index' );
816 my @constraints = $table->get_constraints;
817 is( scalar @constraints, 1, 'Right number of constraints (1)' );
818 my $c = shift @constraints;
819 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
820 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
825 q|create table quote (
826 id int(11) NOT NULL auto_increment,
829 q|create table quote (
830 id int(11) NOT NULL auto_increment,
833 q|create table quote (
834 id int(11) NOT NULL auto_increment,
838 for my $data (@data) {
839 my $tr = SQL::Translator->new;
841 my $val = parse($tr, $data);
842 my $schema = $tr->schema;
843 is( $schema->is_valid, 1, 'Schema is valid' );
844 my @tables = $schema->get_tables;
845 is( scalar @tables, 1, 'Right number of tables (1)' );
846 my $table = shift @tables;
847 is( $table->name, 'quote', 'Found "quote" table' );
849 my $tableTypeFound = 0;
850 for my $t_option_ref ( $table->options ) {
851 my($key, $value) = %{$t_option_ref};
852 if ( $key eq 'ENGINE' ) {
853 is($value, 'innodb', 'Table has right table engine option' );
858 fail('Table did not have a type option') unless $tableTypeFound;
860 my @fields = $table->get_fields;
861 my $f1 = shift @fields;
862 is( $f1->name, 'id', 'First field name is "id"' );
863 is( $f1->data_type, 'int', 'Type is "int"' );
864 is( $f1->size, 11, 'Size is "11"' );
865 is( $f1->is_nullable, 0, 'Field cannot be null' );
866 is( $f1->is_primary_key, 1, 'Field is PK' );
871 my $tr = SQL::Translator->new;
872 my $data = q|create table "sessions" (
873 id char(32) not null default '0' primary key,
874 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
875 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
876 key using btree (ssn)
879 my $val = parse($tr, $data);
880 my $schema = $tr->schema;
881 is( $schema->is_valid, 1, 'Schema is valid' );
882 my @tables = $schema->get_tables;
883 is( scalar @tables, 1, 'Right number of tables (1)' );
884 my $table = shift @tables;
885 is( $table->name, 'sessions', 'Found "sessions" table' );
887 my @fields = $table->get_fields;
888 is( scalar @fields, 3, 'Right number of fields (3)' );
889 my $f1 = shift @fields;
890 my $f2 = shift @fields;
891 my $f3 = shift @fields;
892 is( $f1->name, 'id', 'First field name is "id"' );
893 is( $f1->data_type, 'char', 'Type is "char"' );
894 is( $f1->size, 32, 'Size is "32"' );
895 is( $f1->is_nullable, 0, 'Field cannot be null' );
896 is( $f1->default_value, '0', 'Default value is "0"' );
897 is( $f1->is_primary_key, 1, 'Field is PK' );
899 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
900 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
901 is( $f2->size, 12, 'Size is "12"' );
902 is( $f2->is_nullable, 0, 'Field can not be null' );
903 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
904 is( $f2->is_primary_key, 0, 'Field is not PK' );
906 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
907 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
908 is( $f3->name, 'user', 'Second field name is "user"' );
909 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
910 is( $f3->size, 20, 'Size is "20"' );
911 is( $f3->is_nullable, 0, 'Field can not be null' );
912 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
913 is( $f3->is_primary_key, 0, 'Field is not PK' );
917 # silence PR::D from spewing on STDERR
918 local $::RD_ERRORS = 0;
919 local $::RD_WARN = 0;
920 local $::RD_HINT = 0;
921 my $tr = SQL::Translator->new;
922 my $data = q|create table "sessions" (
923 id char(32) not null default,
924 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
925 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
926 key using btree (ssn)
929 my $val= parse($tr,$data);
930 ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
934 # make sure empty string default value still works
935 my $tr = SQL::Translator->new;
936 my $data = q|create table "sessions" (
937 id char(32) not null DEFAULT '',
938 ssn varchar(12) NOT NULL default "",
939 key using btree (ssn)
941 my $val= parse($tr,$data);
943 my @fields = $tr->schema->get_table('sessions')->get_fields;
944 is (scalar @fields, 2, 'Both fields parsed correctly');
946 my $def = $_->default_value;
947 ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
952 # test rt70437 and rt71468
953 my $file = "$Bin/data/mysql/cashmusic_db.sql";
954 ok (-f $file,"File exists");
955 my $tr = SQL::Translator->new( parser => 'MySQL');
956 ok ($tr->translate($file),'File translated');
957 ok (!$tr->error, 'no error');
958 ok (my $schema = $tr->schema, 'got schema');