9 use SQL::Translator::Schema::Constants;
10 use SQL::Translator::Utils qw//;
11 use Test::SQL::Translator qw(maybe_plan);
15 maybe_plan(343, "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 ) TYPE=INNODB DEFAULT CHARSET=latin1;
527 /*!50001 CREATE ALGORITHM=UNDEFINED */
528 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
529 /*! VIEW `vs_asset` AS
530 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
531 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
532 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
533 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
534 `a`.`foreign_asset_id` AS `foreign_asset_id`,
535 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
536 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
537 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
538 `m`.`user_id` AS `user_access`
539 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
542 /*! VIEW `vs_asset2` AS
543 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
544 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
545 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
546 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
547 `a`.`foreign_asset_id` AS `foreign_asset_id`,
548 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
549 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
550 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
551 `m`.`user_id` AS `user_access`
552 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
554 /*!50001 CREATE OR REPLACE */
555 /*! VIEW `vs_asset3` AS
556 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
557 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
558 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
559 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
560 `a`.`foreign_asset_id` AS `foreign_asset_id`,
561 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
562 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
563 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
564 `m`.`user_id` AS `user_access`
565 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
567 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
570 DECLARE rval TIMESTAMP;
571 IF ( millis_since_1970 = 0 )
575 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
579 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
581 DECLARE hasMoreRows BOOL DEFAULT TRUE;
582 DECLARE t_group_id INT;
583 DECLARE t_user_id INT ;
584 DECLARE t_user_name VARCHAR (512) ;
585 DECLARE t_message VARCHAR (512) ;
587 DROP TABLE IF EXISTS group_acl;
588 DROP TABLE IF EXISTS user_group;
589 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
591 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
592 FROM asset d, acl_entry e, alterpoint_principal p
593 WHERE d.acl_id = e.acl
594 AND p.id = e.principal AND d.acl_id = t_acl_id;
596 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
597 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
598 WHERE a.id = b.user_ref AND b.elt = c.id;
600 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
601 FROM group_acl, user_group
602 WHERE group_acl.group_id = user_group.group_id ;
607 my $schema = $tr->schema;
608 is( $schema->is_valid, 1, 'Schema is valid' );
609 my @tables = $schema->get_tables;
610 is( scalar @tables, 1, 'Right number of tables (1)' );
611 my $table1 = shift @tables;
612 is( $table1->name, 'one', 'Found "one" table' );
614 my @fields = $table1->get_fields;
615 is(scalar @fields, 2, 'Right number of fields (2) on table one');
616 my $tableTypeFound = 0;
617 my $charsetFound = 0;
618 for my $t1_option_ref ( $table1->options ) {
619 my($key, $value) = %{$t1_option_ref};
620 if ( $key eq 'TYPE' ) {
621 is($value, 'INNODB', 'Table has right table type option' );
623 } elsif ( $key eq 'CHARACTER SET' ) {
624 is($value, 'latin1', 'Table has right character set option' );
628 fail('Table did not have a type option') unless $tableTypeFound;
629 fail('Table did not have a character set option') unless $charsetFound;
631 my $t1f1 = shift @fields;
632 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
633 is( $t1f1->size, 255, 'Field is right size' );
634 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
635 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
636 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
638 my $t1f2 = shift @fields;
639 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
640 ok( !$t1f2->is_nullable, 'Field is not nullable' );
642 $t1f2->default_value,
643 \'CURRENT_TIMESTAMP',
644 'Field has right default value'
646 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
648 my @views = $schema->get_views;
649 is( scalar @views, 3, 'Right number of views (3)' );
651 my ($view1, $view2, $view3) = @views;
652 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
653 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
654 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
655 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
656 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
657 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
659 my @procs = $schema->get_procedures;
660 is( scalar @procs, 2, 'Right number of procedures (2)' );
661 my $proc1 = shift @procs;
662 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
663 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
664 my $proc2 = shift @procs;
665 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
666 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
669 # Tests for collate table option
671 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
672 my $data = parse($tr,
674 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
677 my $schema = $tr->schema;
678 is( $schema->is_valid, 1, 'Schema is valid' );
679 my @tables = $schema->get_tables;
680 is( scalar @tables, 1, 'Right number of tables (1)' );
681 my $table1 = shift @tables;
682 is( $table1->name, 'test', 'Found "test" table' );
685 my $collate = "Not found!";
686 my $charset = "Not found!";
687 for my $t1_option_ref ( $table1->options ) {
688 my($key, $value) = %{$t1_option_ref};
689 $collate = $value if $key eq 'COLLATE';
690 $charset = $value if $key eq 'CHARACTER SET';
692 is($collate, 'latin1_bin', "Collate found");
693 is($charset, 'latin1', "Character set found");
696 # Test the mysql version parser (probably needs to migrate to t/utils.t)
699 '3.23.2' => 3.023002,
702 '5.01.0' => 5.001000,
714 for my $target (keys %$parse_as) {
715 for my $str (keys %{$parse_as->{$target}}) {
717 SQL::Translator::Utils::parse_mysql_version ($str, $target),
719 $parse_as->{$target}{$str},
720 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
725 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
726 ok ($@, 'Exception thrown on invalid version string');
729 my $tr = SQL::Translator->new;
730 my $data = q|create table merge_example (
731 id int(11) NOT NULL auto_increment,
732 shape_field geometry NOT NULL,
734 SPATIAL KEY shape_field (shape_field)
735 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
737 my $val = parse($tr, $data);
738 my $schema = $tr->schema;
739 is( $schema->is_valid, 1, 'Schema is valid' );
740 my @tables = $schema->get_tables;
741 is( scalar @tables, 1, 'Right number of tables (1)' );
742 my $table = shift @tables;
743 is( $table->name, 'merge_example', 'Found "merge_example" table' );
745 my $tableTypeFound = 0;
747 for my $t_option_ref ( $table->options ) {
748 my($key, $value) = %{$t_option_ref};
749 if ( $key eq 'ENGINE' ) {
750 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
752 } elsif ( $key eq 'UNION' ) {
753 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
754 "UNION option has correct set");
759 fail('Table did not have a type option') unless $tableTypeFound;
760 fail('Table did not have a union option') unless $unionFound;
762 my @fields = $table->get_fields;
763 is( scalar @fields, 2, 'Right number of fields (2)' );
764 my $f1 = shift @fields;
765 my $f2 = shift @fields;
766 is( $f1->name, 'id', 'First field name is "id"' );
767 is( $f1->data_type, 'int', 'Type is "int"' );
768 is( $f1->size, 11, 'Size is "11"' );
769 is( $f1->is_nullable, 0, 'Field cannot be null' );
770 is( $f1->is_primary_key, 1, 'Field is PK' );
772 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
773 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
774 is( $f2->is_nullable, 0, 'Field cannot be null' );
775 is( $f2->is_primary_key, 0, 'Field is not PK' );
777 my @indices = $table->get_indices;
778 is( scalar @indices, 1, 'Right number of indices (1)' );
779 my $i1 = shift @indices;
780 is( $i1->name, 'shape_field', 'No name on index' );
781 is( $i1->type, SPATIAL, 'Spatial index' );
783 my @constraints = $table->get_constraints;
784 is( scalar @constraints, 1, 'Right number of constraints (1)' );
785 my $c = shift @constraints;
786 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
787 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
792 q|create table quote (
793 id int(11) NOT NULL auto_increment,
796 q|create table quote (
797 id int(11) NOT NULL auto_increment,
800 q|create table quote (
801 id int(11) NOT NULL auto_increment,
805 for my $data (@data) {
806 my $tr = SQL::Translator->new;
808 my $val = parse($tr, $data);
809 my $schema = $tr->schema;
810 is( $schema->is_valid, 1, 'Schema is valid' );
811 my @tables = $schema->get_tables;
812 is( scalar @tables, 1, 'Right number of tables (1)' );
813 my $table = shift @tables;
814 is( $table->name, 'quote', 'Found "quote" table' );
816 my $tableTypeFound = 0;
817 for my $t_option_ref ( $table->options ) {
818 my($key, $value) = %{$t_option_ref};
819 if ( $key eq 'ENGINE' ) {
820 is($value, 'innodb', 'Table has right table engine option' );
825 fail('Table did not have a type option') unless $tableTypeFound;
827 my @fields = $table->get_fields;
828 my $f1 = shift @fields;
829 is( $f1->name, 'id', 'First field name is "id"' );
830 is( $f1->data_type, 'int', 'Type is "int"' );
831 is( $f1->size, 11, 'Size is "11"' );
832 is( $f1->is_nullable, 0, 'Field cannot be null' );
833 is( $f1->is_primary_key, 1, 'Field is PK' );
838 my $tr = SQL::Translator->new;
839 my $data = q|create table "sessions" (
840 id char(32) not null default '0' primary key,
841 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
842 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
843 key using btree (ssn)
846 my $val = parse($tr, $data);
847 my $schema = $tr->schema;
848 is( $schema->is_valid, 1, 'Schema is valid' );
849 my @tables = $schema->get_tables;
850 is( scalar @tables, 1, 'Right number of tables (1)' );
851 my $table = shift @tables;
852 is( $table->name, 'sessions', 'Found "sessions" table' );
854 my @fields = $table->get_fields;
855 is( scalar @fields, 3, 'Right number of fields (3)' );
856 my $f1 = shift @fields;
857 my $f2 = shift @fields;
858 my $f3 = shift @fields;
859 is( $f1->name, 'id', 'First field name is "id"' );
860 is( $f1->data_type, 'char', 'Type is "char"' );
861 is( $f1->size, 32, 'Size is "32"' );
862 is( $f1->is_nullable, 0, 'Field cannot be null' );
863 is( $f1->default_value, '0', 'Default value is "0"' );
864 is( $f1->is_primary_key, 1, 'Field is PK' );
866 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
867 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
868 is( $f2->size, 12, 'Size is "12"' );
869 is( $f2->is_nullable, 0, 'Field can not be null' );
870 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
871 is( $f2->is_primary_key, 0, 'Field is not PK' );
873 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
874 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
875 is( $f3->name, 'user', 'Second field name is "user"' );
876 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
877 is( $f3->size, 20, 'Size is "20"' );
878 is( $f3->is_nullable, 0, 'Field can not be null' );
879 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
880 is( $f3->is_primary_key, 0, 'Field is not PK' );
884 # silence PR::D from spewing on STDERR
885 local $::RD_ERRORS = 0;
886 local $::RD_WARN = 0;
887 local $::RD_HINT = 0;
888 my $tr = SQL::Translator->new;
889 my $data = q|create table "sessions" (
890 id char(32) not null default,
891 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
892 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
893 key using btree (ssn)
896 my $val= parse($tr,$data);
897 ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
901 # make sure empty string default value still works
902 my $tr = SQL::Translator->new;
903 my $data = q|create table "sessions" (
904 id char(32) not null DEFAULT '',
905 ssn varchar(12) NOT NULL default "",
906 key using btree (ssn)
908 my $val= parse($tr,$data);
910 my @fields = $tr->schema->get_table('sessions')->get_fields;
911 is (scalar @fields, 2, 'Both fields parsed correctly');
913 my $def = $_->default_value;
914 ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
919 # test rt70437 and rt71468
920 my $file = "$Bin/data/mysql/cashmusic_db.sql";
921 ok (-f $file,"File exists");
922 my $tr = SQL::Translator->new( parser => 'MySQL');
923 ok ($tr->translate($file),'File translated');
924 ok (!$tr->error, 'no error');
925 ok (my $schema = $tr->schema, 'got schema');