9 use SQL::Translator::Schema::Constants;
10 use SQL::Translator::Utils qw//;
11 use Test::SQL::Translator qw(maybe_plan);
14 maybe_plan(333, "SQL::Translator::Parser::MySQL");
15 SQL::Translator::Parser::MySQL->import('parse');
19 my $tr = SQL::Translator->new;
20 my $data = q|create table "sessions" (
21 id char(32) not null default '0' primary key,
23 ssn varchar(12) unique key,
25 fulltext key `session_fulltext` (a_session)
28 my $val = parse($tr, $data);
29 my $schema = $tr->schema;
30 is( $schema->is_valid, 1, 'Schema is valid' );
31 my @tables = $schema->get_tables;
32 is( scalar @tables, 1, 'Right number of tables (1)' );
33 my $table = shift @tables;
34 is( $table->name, 'sessions', 'Found "sessions" table' );
36 my @fields = $table->get_fields;
37 is( scalar @fields, 4, 'Right number of fields (4)' );
38 my $f1 = shift @fields;
39 my $f2 = shift @fields;
40 is( $f1->name, 'id', 'First field name is "id"' );
41 is( $f1->data_type, 'char', 'Type is "char"' );
42 is( $f1->size, 32, 'Size is "32"' );
43 is( $f1->is_nullable, 0, 'Field cannot be null' );
44 is( $f1->default_value, '0', 'Default value is "0"' );
45 is( $f1->is_primary_key, 1, 'Field is PK' );
47 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
48 is( $f2->data_type, 'text', 'Type is "text"' );
49 is( $f2->size, 65_535, 'Size is "65,535"' );
50 is( $f2->is_nullable, 1, 'Field can be null' );
51 is( $f2->default_value, undef, 'Default value is undefined' );
52 is( $f2->is_primary_key, 0, 'Field is not PK' );
54 my @indices = $table->get_indices;
55 is( scalar @indices, 2, 'Right number of indices (2)' );
57 is( $i->type, 'FULLTEXT', 'Found fulltext' );
59 my @constraints = $table->get_constraints;
60 is( scalar @constraints, 2, 'Right number of constraints (2)' );
61 my $c = shift @constraints;
62 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
63 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
64 my $c2 = shift @constraints;
65 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
66 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
70 my $tr = SQL::Translator->new;
73 CREATE TABLE `check` (
74 check_id int(7) unsigned zerofill NOT NULL default '0000000'
75 auto_increment primary key,
76 successful date NOT NULL default '0000-00-00',
77 unsuccessful date default '0000-00-00',
78 i1 int(11) default '0' not null,
79 s1 set('a','b','c') default 'b',
80 e1 enum('a','b','c') default "c",
81 name varchar(30) default NULL,
82 foo_type enum('vk','ck') NOT NULL default 'vk',
84 time_stamp2 timestamp,
85 foo_enabled bit(1) default b'0',
86 bar_enabled bit(1) default b"1",
88 UNIQUE (date, i1) USING BTREE,
90 KEY name_idx (name(10))
91 ) TYPE=MyISAM PACK_KEYS=1;
95 my $schema = $tr->schema;
96 is( $schema->is_valid, 1, 'Schema is valid' );
97 my @tables = $schema->get_tables;
98 is( scalar @tables, 1, 'Right number of tables (1)' );
99 my $table = shift @tables;
100 is( $table->name, 'check', 'Found "check" table' );
102 my @fields = $table->get_fields;
103 is( scalar @fields, 12, 'Right number of fields (12)' );
104 my $f1 = shift @fields;
105 is( $f1->name, 'check_id', 'First field name is "check_id"' );
106 is( $f1->data_type, 'int', 'Type is "int"' );
107 is( $f1->size, 7, 'Size is "7"' );
108 is( $f1->is_nullable, 0, 'Field cannot be null' );
109 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
110 is( $f1->is_primary_key, 1, 'Field is PK' );
111 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
112 my %extra = $f1->extra;
113 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
114 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
116 my $f2 = shift @fields;
117 is( $f2->name, 'successful', 'Second field name is "successful"' );
118 is( $f2->data_type, 'date', 'Type is "date"' );
119 is( $f2->size, 0, 'Size is "0"' );
120 is( $f2->is_nullable, 0, 'Field cannot be null' );
121 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
122 is( $f2->is_primary_key, 0, 'Field is not PK' );
124 my $f3 = shift @fields;
125 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
126 is( $f3->data_type, 'date', 'Type is "date"' );
127 is( $f3->size, 0, 'Size is "0"' );
128 is( $f3->is_nullable, 1, 'Field can be null' );
129 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
130 is( $f3->is_primary_key, 0, 'Field is not PK' );
132 my $f4 = shift @fields;
133 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
134 is( $f4->data_type, 'int', 'Type is "int"' );
135 is( $f4->size, 11, 'Size is "11"' );
136 is( $f4->is_nullable, 0, 'Field cannot be null' );
137 is( $f4->default_value, '0', 'Default value is "0"' );
138 is( $f4->is_primary_key, 0, 'Field is not PK' );
140 my $f5 = shift @fields;
141 is( $f5->name, 's1', 'Fifth field name is "s1"' );
142 is( $f5->data_type, 'set', 'Type is "set"' );
143 is( $f5->size, 1, 'Size is "1"' );
144 is( $f5->is_nullable, 1, 'Field can be null' );
145 is( $f5->default_value, 'b', 'Default value is "b"' );
146 is( $f5->is_primary_key, 0, 'Field is not PK' );
147 my %f5extra = $f5->extra;
148 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
150 my $f6 = shift @fields;
151 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
152 is( $f6->data_type, 'enum', 'Type is "enum"' );
153 is( $f6->size, 1, 'Size is "1"' );
154 is( $f6->is_nullable, 1, 'Field can be null' );
155 is( $f6->default_value, 'c', 'Default value is "c"' );
156 is( $f6->is_primary_key, 0, 'Field is not PK' );
157 my %f6extra = $f6->extra;
158 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
160 my $f7 = shift @fields;
161 is( $f7->name, 'name', 'Seventh field name is "name"' );
162 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
163 is( $f7->size, 30, 'Size is "30"' );
164 is( $f7->is_nullable, 1, 'Field can be null' );
165 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
166 is( $f7->is_primary_key, 0, 'Field is not PK' );
168 my $f8 = shift @fields;
169 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
170 is( $f8->data_type, 'enum', 'Type is "enum"' );
171 is( $f8->size, 2, 'Size is "2"' );
172 is( $f8->is_nullable, 0, 'Field cannot be null' );
173 is( $f8->default_value, 'vk', 'Default value is "vk"' );
174 is( $f8->is_primary_key, 0, 'Field is not PK' );
175 my %f8extra = $f8->extra;
176 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
178 my $f9 = shift @fields;
179 is( $f9->name, 'date', 'Ninth field name is "date"' );
180 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
181 is( $f9->size, 0, 'Size is "0"' );
182 is( $f9->is_nullable, 1, 'Field can be null' );
183 is( $f9->default_value, undef, 'Default value is undefined' );
184 is( $f9->is_primary_key, 0, 'Field is not PK' );
186 my $f10 = shift @fields;
187 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
188 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
189 is( $f10->size, 0, 'Size is "0"' );
190 is( $f10->is_nullable, 1, 'Field can be null' );
191 is( $f10->default_value, undef, 'Default value is undefined' );
192 is( $f10->is_primary_key, 0, 'Field is not PK' );
194 my $f11 = shift @fields;
195 is( $f11->name, 'foo_enabled', 'Eleventh field name is "foo_enabled"' );
196 is( $f11->data_type, 'bit', 'Type is "bit"' );
197 is( $f11->size, 1, 'Size is "1"' );
198 is( $f11->is_nullable, 1, 'Field can be null' );
199 is( $f11->default_value, '0', 'Default value is 0' );
200 is( $f11->is_primary_key, 0, 'Field is not PK' );
202 my $f12 = shift @fields;
203 is( $f12->name, 'bar_enabled', 'Twelveth field name is "bar_enabled"' );
204 is( $f12->data_type, 'bit', 'Type is "bit"' );
205 is( $f12->size, 1, 'Size is "1"' );
206 is( $f12->is_nullable, 1, 'Field can be null' );
207 is( $f12->default_value, '1', 'Default value is 1' );
208 is( $f12->is_primary_key, 0, 'Field is not PK' );
210 my @indices = $table->get_indices;
211 is( scalar @indices, 3, 'Right number of indices (3)' );
213 my $i1 = shift @indices;
214 is( $i1->name, '', 'No name on index' );
215 is( $i1->type, NORMAL, 'Normal index' );
216 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
218 my $i2 = shift @indices;
219 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
220 is( $i2->type, NORMAL, 'Normal index' );
221 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
223 my $i3 = shift @indices;
224 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
225 is( $i3->type, NORMAL, 'Normal index' );
226 is( join(',', $i3->fields ), 'name(10)', 'Index is on field "name(10)"' );
228 my @constraints = $table->get_constraints;
229 is( scalar @constraints, 2, 'Right number of constraints (2)' );
231 my $c1 = shift @constraints;
232 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
233 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
235 my $c2 = shift @constraints;
236 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
237 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
241 my $tr = SQL::Translator->new;
242 my $data = parse($tr,
244 CREATE TABLE orders (
245 order_id integer NOT NULL auto_increment,
246 member_id varchar(255) comment 'fk to member',
247 billing_address_id int,
248 shipping_address_id int,
250 status smallint NOT NULL,
251 store_id varchar(255) NOT NULL REFERENCES store,
253 shipping_charge decimal(8,2),
254 price_paid decimal(8,2),
255 PRIMARY KEY (order_id) USING BTREE,
256 KEY (status) USING BTREE,
257 KEY USING BTREE (billing_address_id),
258 KEY (shipping_address_id),
259 KEY (member_id, store_id),
260 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
261 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
262 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
263 ) TYPE=INNODB COMMENT = 'orders table comment';
265 CREATE TABLE address (
266 address_id int NOT NULL auto_increment,
267 recipient varchar(255) NOT NULL,
268 address1 varchar(255) NOT NULL,
269 address2 varchar(255),
270 city varchar(255) NOT NULL,
271 state varchar(255) NOT NULL,
272 postal_code varchar(255) NOT NULL,
274 PRIMARY KEY (address_id)
279 my $schema = $tr->schema;
280 is( $schema->is_valid, 1, 'Schema is valid' );
281 my @tables = $schema->get_tables;
282 is( scalar @tables, 2, 'Right number of tables (2)' );
284 my $t1 = shift @tables;
285 is( $t1->name, 'orders', 'Found "orders" table' );
286 is( $t1->comments, 'orders table comment', 'Table comment OK' );
288 my @fields = $t1->get_fields;
289 is( scalar @fields, 10, 'Right number of fields (10)' );
291 my $f1 = shift @fields;
292 is( $f1->name, 'order_id', 'First field name is "order_id"' );
293 is( $f1->data_type, 'int', 'Type is "int"' );
294 is( $f1->size, 11, 'Size is "11"' );
295 is( $f1->is_nullable, 0, 'Field cannot be null' );
296 is( $f1->default_value, undef, 'Default value is undefined' );
297 is( $f1->is_primary_key, 1, 'Field is PK' );
298 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
300 my $f2 = shift @fields;
301 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
302 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
303 is( $f2->size, 255, 'Size is "255"' );
304 is( $f2->is_nullable, 1, 'Field can be null' );
305 is( $f2->comments, 'fk to member', 'Field comment OK' );
306 is( $f2->default_value, undef, 'Default value is undefined' );
308 my $f3 = shift @fields;
309 is( $f3->name, 'billing_address_id',
310 'Third field name is "billing_address_id"' );
311 is( $f3->data_type, 'int', 'Type is "int"' );
312 is( $f3->size, 11, 'Size is "11"' );
314 my $f4 = shift @fields;
315 is( $f4->name, 'shipping_address_id',
316 'Fourth field name is "shipping_address_id"' );
317 is( $f4->data_type, 'int', 'Type is "int"' );
318 is( $f4->size, 11, 'Size is "11"' );
320 my $f5 = shift @fields;
321 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
322 is( $f5->data_type, 'int', 'Type is "int"' );
323 is( $f5->size, 11, 'Size is "11"' );
325 my $f6 = shift @fields;
326 is( $f6->name, 'status', 'Sixth field name is "status"' );
327 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
328 is( $f6->size, 6, 'Size is "6"' );
329 is( $f6->is_nullable, 0, 'Field cannot be null' );
331 my $f7 = shift @fields;
332 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
333 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
334 is( $f7->size, 255, 'Size is "255"' );
335 is( $f7->is_nullable, 0, 'Field cannot be null' );
336 is( $f7->is_foreign_key, 1, 'Field is a FK' );
337 my $fk_ref = $f7->foreign_key_reference;
338 isa_ok( $fk_ref, 'SQL::Translator::Schema::Constraint', 'FK' );
339 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
341 my $f8 = shift @fields;
342 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
343 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
344 is( $f8->size, '8,2', 'Size is "8,2"' );
346 my $f9 = shift @fields;
347 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
348 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
349 is( $f9->size, '8,2', 'Size is "8,2"' );
351 my $f10 = shift @fields;
352 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
353 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
354 is( $f10->size, '8,2', 'Size is "8,2"' );
356 my @indices = $t1->get_indices;
357 is( scalar @indices, 4, 'Right number of indices (4)' );
359 my $i1 = shift @indices;
360 is( $i1->type, NORMAL, 'First index is normal' );
361 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
363 my $i2 = shift @indices;
364 is( $i2->type, NORMAL, 'Second index is normal' );
365 is( join(',', $i2->fields), 'billing_address_id',
366 'Index is on "billing_address_id"' );
368 my $i3 = shift @indices;
369 is( $i3->type, NORMAL, 'Third index is normal' );
370 is( join(',', $i3->fields), 'shipping_address_id',
371 'Index is on "shipping_address_id"' );
373 my $i4 = shift @indices;
374 is( $i4->type, NORMAL, 'Third index is normal' );
375 is( join(',', $i4->fields), 'member_id,store_id',
376 'Index is on "member_id,store_id"' );
378 my @constraints = $t1->get_constraints;
379 is( scalar @constraints, 5, 'Right number of constraints (5)' );
381 my $c1 = shift @constraints;
382 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
383 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
385 my $c2 = shift @constraints;
386 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
387 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
388 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
389 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
391 my $c3 = shift @constraints;
392 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
393 is( join(',', $c3->fields), 'billing_address_id',
394 'Constraint is on "billing_address_id"' );
395 is( $c3->reference_table, 'address', 'To table "address"' );
396 is( join(',', $c3->reference_fields), 'address_id',
397 'To field "address_id"' );
399 my $c4 = shift @constraints;
400 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
401 is( join(',', $c4->fields), 'shipping_address_id',
402 'Constraint is on "shipping_address_id"' );
403 is( $c4->reference_table, 'address', 'To table "address"' );
404 is( join(',', $c4->reference_fields), 'address_id',
405 'To field "address_id"' );
407 my $c5 = shift @constraints;
408 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
409 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
410 is( $c5->reference_table, 'store', 'To table "store"' );
411 is( join(',', map { $_ || '' } $c5->reference_fields), '',
412 'No reference fields defined' );
414 my $t2 = shift @tables;
415 is( $t2->name, 'address', 'Found "address" table' );
417 my @t2_fields = $t2->get_fields;
418 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
423 # ALTER TABLE ADD FOREIGN KEY
424 # trailing comma on last create definition
425 # Ignoring INSERT statements
428 my $tr = SQL::Translator->new;
429 my $data = parse($tr,
434 id integer NOT NULL auto_increment,
435 two_id integer NOT NULL auto_increment,
442 id int NOT NULL auto_increment,
443 one_id int NOT NULL auto_increment,
447 FOREIGN KEY (one_id) REFERENCES one (id),
450 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
452 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
456 my $schema = $tr->schema;
457 is( $schema->is_valid, 1, 'Schema is valid' );
458 my $db_name = $schema->name;
459 is( $db_name, 'database_name', 'Database name extracted from USE' );
460 my @tables = $schema->get_tables;
461 is( scalar @tables, 2, 'Right number of tables (2)' );
462 my $table1 = shift @tables;
463 is( $table1->name, 'one', 'Found "one" table' );
464 my $table2 = shift @tables;
465 is( $table2->name, 'two', 'Found "two" table' );
467 my @constraints = $table1->get_constraints;
468 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
470 my $t1c1 = shift @constraints;
471 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
472 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
474 my $t1c2 = shift @constraints;
475 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
476 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
477 is( $t1c2->reference_table, 'two', 'To table "two"' );
478 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
480 @constraints = $table2->get_constraints;
481 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
483 my $t2c1 = shift @constraints;
484 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
485 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
487 my $t2c2 = shift @constraints;
488 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
489 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
490 is( $t2c2->reference_table, 'one', 'To table "one"' );
491 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
495 # comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
496 # char fields with character set and collate qualifiers
497 # timestamp fields with on update qualifier
498 # charset table option
501 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
502 my $data = parse($tr,
505 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
506 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
507 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
508 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
509 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
513 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
514 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
515 ) TYPE=INNODB DEFAULT CHARSET=latin1;
517 /*!50001 CREATE ALGORITHM=UNDEFINED */
518 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
519 /*! VIEW `vs_asset` AS
520 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
521 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
522 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
523 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
524 `a`.`foreign_asset_id` AS `foreign_asset_id`,
525 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
526 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
527 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
528 `m`.`user_id` AS `user_access`
529 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
532 /*! VIEW `vs_asset2` AS
533 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
534 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
535 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
536 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
537 `a`.`foreign_asset_id` AS `foreign_asset_id`,
538 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
539 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
540 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
541 `m`.`user_id` AS `user_access`
542 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
544 /*!50001 CREATE OR REPLACE */
545 /*! VIEW `vs_asset3` AS
546 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
547 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
548 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
549 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
550 `a`.`foreign_asset_id` AS `foreign_asset_id`,
551 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
552 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
553 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
554 `m`.`user_id` AS `user_access`
555 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
557 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
560 DECLARE rval TIMESTAMP;
561 IF ( millis_since_1970 = 0 )
565 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
569 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
571 DECLARE hasMoreRows BOOL DEFAULT TRUE;
572 DECLARE t_group_id INT;
573 DECLARE t_user_id INT ;
574 DECLARE t_user_name VARCHAR (512) ;
575 DECLARE t_message VARCHAR (512) ;
577 DROP TABLE IF EXISTS group_acl;
578 DROP TABLE IF EXISTS user_group;
579 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
581 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
582 FROM asset d, acl_entry e, alterpoint_principal p
583 WHERE d.acl_id = e.acl
584 AND p.id = e.principal AND d.acl_id = t_acl_id;
586 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
587 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
588 WHERE a.id = b.user_ref AND b.elt = c.id;
590 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
591 FROM group_acl, user_group
592 WHERE group_acl.group_id = user_group.group_id ;
597 my $schema = $tr->schema;
598 is( $schema->is_valid, 1, 'Schema is valid' );
599 my @tables = $schema->get_tables;
600 is( scalar @tables, 1, 'Right number of tables (1)' );
601 my $table1 = shift @tables;
602 is( $table1->name, 'one', 'Found "one" table' );
604 my @fields = $table1->get_fields;
605 is(scalar @fields, 2, 'Right number of fields (2) on table one');
606 my $tableTypeFound = 0;
607 my $charsetFound = 0;
608 for my $t1_option_ref ( $table1->options ) {
609 my($key, $value) = %{$t1_option_ref};
610 if ( $key eq 'TYPE' ) {
611 is($value, 'INNODB', 'Table has right table type option' );
613 } elsif ( $key eq 'CHARACTER SET' ) {
614 is($value, 'latin1', 'Table has right character set option' );
618 fail('Table did not have a type option') unless $tableTypeFound;
619 fail('Table did not have a character set option') unless $charsetFound;
621 my $t1f1 = shift @fields;
622 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
623 is( $t1f1->size, 255, 'Field is right size' );
624 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
625 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
626 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
628 my $t1f2 = shift @fields;
629 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
630 ok( !$t1f2->is_nullable, 'Field is not nullable' );
632 $t1f2->default_value,
633 \'CURRENT_TIMESTAMP',
634 'Field has right default value'
636 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
638 my @views = $schema->get_views;
639 is( scalar @views, 3, 'Right number of views (3)' );
641 my ($view1, $view2, $view3) = @views;
642 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
643 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
644 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
645 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
646 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
647 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
649 my @procs = $schema->get_procedures;
650 is( scalar @procs, 2, 'Right number of procedures (2)' );
651 my $proc1 = shift @procs;
652 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
653 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
654 my $proc2 = shift @procs;
655 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
656 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
659 # Tests for collate table option
661 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
662 my $data = parse($tr,
664 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
667 my $schema = $tr->schema;
668 is( $schema->is_valid, 1, 'Schema is valid' );
669 my @tables = $schema->get_tables;
670 is( scalar @tables, 1, 'Right number of tables (1)' );
671 my $table1 = shift @tables;
672 is( $table1->name, 'test', 'Found "test" table' );
675 my $collate = "Not found!";
676 my $charset = "Not found!";
677 for my $t1_option_ref ( $table1->options ) {
678 my($key, $value) = %{$t1_option_ref};
679 $collate = $value if $key eq 'COLLATE';
680 $charset = $value if $key eq 'CHARACTER SET';
682 is($collate, 'latin1_bin', "Collate found");
683 is($charset, 'latin1', "Character set found");
686 # Test the mysql version parser (probably needs to migrate to t/utils.t)
689 '3.23.2' => 3.023002,
692 '5.01.0' => 5.001000,
704 for my $target (keys %$parse_as) {
705 for my $str (keys %{$parse_as->{$target}}) {
707 SQL::Translator::Utils::parse_mysql_version ($str, $target),
709 $parse_as->{$target}{$str},
710 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
715 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
716 ok ($@, 'Exception thrown on invalid version string');
719 my $tr = SQL::Translator->new;
720 my $data = q|create table merge_example (
721 id int(11) NOT NULL auto_increment,
722 shape_field geometry NOT NULL,
724 SPATIAL KEY shape_field (shape_field)
725 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
727 my $val = parse($tr, $data);
728 my $schema = $tr->schema;
729 is( $schema->is_valid, 1, 'Schema is valid' );
730 my @tables = $schema->get_tables;
731 is( scalar @tables, 1, 'Right number of tables (1)' );
732 my $table = shift @tables;
733 is( $table->name, 'merge_example', 'Found "merge_example" table' );
735 my $tableTypeFound = 0;
737 for my $t_option_ref ( $table->options ) {
738 my($key, $value) = %{$t_option_ref};
739 if ( $key eq 'ENGINE' ) {
740 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
742 } elsif ( $key eq 'UNION' ) {
743 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
744 "UNION option has correct set");
749 fail('Table did not have a type option') unless $tableTypeFound;
750 fail('Table did not have a union option') unless $unionFound;
752 my @fields = $table->get_fields;
753 is( scalar @fields, 2, 'Right number of fields (2)' );
754 my $f1 = shift @fields;
755 my $f2 = shift @fields;
756 is( $f1->name, 'id', 'First field name is "id"' );
757 is( $f1->data_type, 'int', 'Type is "int"' );
758 is( $f1->size, 11, 'Size is "11"' );
759 is( $f1->is_nullable, 0, 'Field cannot be null' );
760 is( $f1->is_primary_key, 1, 'Field is PK' );
762 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
763 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
764 is( $f2->is_nullable, 0, 'Field cannot be null' );
765 is( $f2->is_primary_key, 0, 'Field is not PK' );
767 my @indices = $table->get_indices;
768 is( scalar @indices, 1, 'Right number of indices (1)' );
769 my $i1 = shift @indices;
770 is( $i1->name, 'shape_field', 'No name on index' );
771 is( $i1->type, SPATIAL, 'Spatial index' );
773 my @constraints = $table->get_constraints;
774 is( scalar @constraints, 1, 'Right number of constraints (1)' );
775 my $c = shift @constraints;
776 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
777 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
782 q|create table quote (
783 id int(11) NOT NULL auto_increment,
786 q|create table quote (
787 id int(11) NOT NULL auto_increment,
790 q|create table quote (
791 id int(11) NOT NULL auto_increment,
795 for my $data (@data) {
796 my $tr = SQL::Translator->new;
798 my $val = parse($tr, $data);
799 my $schema = $tr->schema;
800 is( $schema->is_valid, 1, 'Schema is valid' );
801 my @tables = $schema->get_tables;
802 is( scalar @tables, 1, 'Right number of tables (1)' );
803 my $table = shift @tables;
804 is( $table->name, 'quote', 'Found "quote" table' );
806 my $tableTypeFound = 0;
807 for my $t_option_ref ( $table->options ) {
808 my($key, $value) = %{$t_option_ref};
809 if ( $key eq 'ENGINE' ) {
810 is($value, 'innodb', 'Table has right table engine option' );
815 fail('Table did not have a type option') unless $tableTypeFound;
817 my @fields = $table->get_fields;
818 my $f1 = shift @fields;
819 is( $f1->name, 'id', 'First field name is "id"' );
820 is( $f1->data_type, 'int', 'Type is "int"' );
821 is( $f1->size, 11, 'Size is "11"' );
822 is( $f1->is_nullable, 0, 'Field cannot be null' );
823 is( $f1->is_primary_key, 1, 'Field is PK' );
828 my $tr = SQL::Translator->new;
829 my $data = q|create table "sessions" (
830 id char(32) not null default '0' primary key,
831 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
832 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
833 key using btree (ssn)
836 my $val = parse($tr, $data);
837 my $schema = $tr->schema;
838 is( $schema->is_valid, 1, 'Schema is valid' );
839 my @tables = $schema->get_tables;
840 is( scalar @tables, 1, 'Right number of tables (1)' );
841 my $table = shift @tables;
842 is( $table->name, 'sessions', 'Found "sessions" table' );
844 my @fields = $table->get_fields;
845 is( scalar @fields, 3, 'Right number of fields (3)' );
846 my $f1 = shift @fields;
847 my $f2 = shift @fields;
848 my $f3 = shift @fields;
849 is( $f1->name, 'id', 'First field name is "id"' );
850 is( $f1->data_type, 'char', 'Type is "char"' );
851 is( $f1->size, 32, 'Size is "32"' );
852 is( $f1->is_nullable, 0, 'Field cannot be null' );
853 is( $f1->default_value, '0', 'Default value is "0"' );
854 is( $f1->is_primary_key, 1, 'Field is PK' );
856 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
857 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
858 is( $f2->size, 12, 'Size is "12"' );
859 is( $f2->is_nullable, 0, 'Field can not be null' );
860 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
861 is( $f2->is_primary_key, 0, 'Field is not PK' );
863 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
864 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
865 is( $f3->name, 'user', 'Second field name is "user"' );
866 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
867 is( $f3->size, 20, 'Size is "20"' );
868 is( $f3->is_nullable, 0, 'Field can not be null' );
869 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
870 is( $f3->is_primary_key, 0, 'Field is not PK' );
874 # silence PR::D from spewing on STDERR
875 local ($::RD_ERRORS, $::RD_WARN,$::RD_HINT,$::RD_TRACE);
876 my $tr = SQL::Translator->new;
877 my $data = q|create table "sessions" (
878 id char(32) not null default,
879 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
880 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
881 key using btree (ssn)
884 my $val= parse($tr,$data);
885 ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
889 # make sure empty string default value still works
890 my $tr = SQL::Translator->new;
891 my $data = q|create table "sessions" (
892 id char(32) not null DEFAULT '',
893 ssn varchar(12) NOT NULL default "",
894 key using btree (ssn)
896 my $val= parse($tr,$data);
898 my @fields = $tr->schema->get_table('sessions')->get_fields;
899 is (scalar @fields, 2, 'Both fields parsed correctly');
901 my $def = $_->default_value;
902 ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );