projects / dbsrgits/SQL-Translator-2.0-ish.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
remove some unnecessary code
[dbsrgits/SQL-Translator-2.0-ish.git] / t / 02mysql-parser.t
CommitLineData
dbca887e 1use strict;
2e718597 2use warnings;
dbca887e 3use Test::More;
4use SQL::Translator;
dbca887e 5use SQL::Translator::Constants qw(:sqlt_types :sqlt_constants);
dbca887e 6
7{
8 my $tr = SQL::Translator->new({ from => 'MySQL' });
9 my $data = q|create table "sessions" (
10 id char(32) not null default '0' primary key,
11 a_session text,
12 ssn varchar(12) unique key,
13 age int key,
14 fulltext key `session_fulltext` (a_session)
15 );|;
16
7dcf6bc2 17 my $val = $tr->parse($data);
18 my $schema = $tr->schema;
dbca887e 19 is( $schema->is_valid, 1, 'Schema is valid' );
20 my @tables = $schema->get_tables;
21 is( scalar @tables, 1, 'Right number of tables (1)' );
22 my $table = shift @tables;
23 is( $table->name, 'sessions', 'Found "sessions" table' );
24
25 my @fields = $table->get_fields;
26 is( scalar @fields, 4, 'Right number of fields (4)' );
27
28 my $f1 = shift @fields;
29 my $f2 = shift @fields;
30 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 31 is( $f1->data_type, 'char', 'Type is "char"' );
dbca887e 32 is( $f1->size, 32, 'Size is "32"' );
33 is( $f1->is_nullable, 0, 'Field cannot be null' );
34 is( $f1->default_value, '0', 'Default value is "0"' );
35 is( $f1->is_primary_key, 1, 'Field is PK' );
36
37 is( $f2->name, 'a_session', 'Second field name is "a_session"' );
92a61e09 38 is( $f2->data_type, 'text', 'Type is "text"' );
f35e598e 39 is( $f2->size, 0, 'Size is "0"' );
dbca887e 40 is( $f2->is_nullable, 1, 'Field can be null' );
41 is( $f2->default_value, undef, 'Default value is undefined' );
42 is( $f2->is_primary_key, 0, 'Field is not PK' );
43
44 my @indices = $table->get_indices;
45 is( scalar @indices, 2, 'Right number of indices (2)' );
46
47 my $i = pop @indices;
48 is( $i->type, 'FULLTEXT', 'Found fulltext' );
49
50 my @constraints = $table->get_constraints;
51 is( scalar @constraints, 2, 'Right number of constraints (2)' );
92a61e09 52 my $c1 = shift @constraints;
53 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
54 is( join(',', $c1->fields), 'id', 'Constraint is on "id"' );
dbca887e 55 my $c2 = shift @constraints;
56 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
57 is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
58}
59
60{
61 my $tr = SQL::Translator->new({ from => 'MySQL' });
7dcf6bc2 62 my $val = $tr->parse(
dbca887e 63 q[
64 CREATE TABLE `check` (
65 check_id int(7) unsigned zerofill NOT NULL default '0000000'
66 auto_increment primary key,
67 successful date NOT NULL default '0000-00-00',
68 unsuccessful date default '0000-00-00',
69 i1 int(11) default '0' not null,
70 s1 set('a','b','c') default 'b',
71 e1 enum('a','b','c') default 'c',
72 name varchar(30) default NULL,
73 foo_type enum('vk','ck') NOT NULL default 'vk',
74 date timestamp,
75 time_stamp2 timestamp,
76 KEY (i1),
77 UNIQUE (date, i1),
78 KEY date_idx (date),
7dcf6bc2 79 KEY name_idx (name)
dbca887e 80 ) TYPE=MyISAM PACK_KEYS=1;
81 ]
7dcf6bc2 82 ); ## KEY name_idx (name(10))
dbca887e 83
7dcf6bc2 84 my $schema = $tr->schema;
dbca887e 85 is( $schema->is_valid, 1, 'Schema is valid' );
86 my @tables = $schema->get_tables;
87 is( scalar @tables, 1, 'Right number of tables (1)' );
88 my $table = shift @tables;
89 is( $table->name, 'check', 'Found "check" table' );
90
91 my @fields = $table->get_fields;
92 is( scalar @fields, 10, 'Right number of fields (10)' );
93 my $f1 = shift @fields;
94 is( $f1->name, 'check_id', 'First field name is "check_id"' );
92a61e09 95 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 96 is( $f1->size, 7, 'Size is "7"' );
97 is( $f1->is_nullable, 0, 'Field cannot be null' );
98 is( $f1->default_value, '0000000', 'Default value is "0000000"' );
99 is( $f1->is_primary_key, 1, 'Field is PK' );
100 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
101 my %extra = $f1->extra;
102 ok( defined $extra{'unsigned'}, 'Field is unsigned' );
103 ok( defined $extra{'zerofill'}, 'Field is zerofill' );
104
105 my $f2 = shift @fields;
106 is( $f2->name, 'successful', 'Second field name is "successful"' );
107 is( $f2->data_type, 'date', 'Type is "date"' );
108 is( $f2->size, 0, 'Size is "0"' );
109 is( $f2->is_nullable, 0, 'Field cannot be null' );
110 is( $f2->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
111 is( $f2->is_primary_key, 0, 'Field is not PK' );
112
113 my $f3 = shift @fields;
114 is( $f3->name, 'unsuccessful', 'Third field name is "unsuccessful"' );
115 is( $f3->data_type, 'date', 'Type is "date"' );
116 is( $f3->size, 0, 'Size is "0"' );
117 is( $f3->is_nullable, 1, 'Field can be null' );
118 is( $f3->default_value, '0000-00-00', 'Default value is "0000-00-00"' );
119 is( $f3->is_primary_key, 0, 'Field is not PK' );
120
121 my $f4 = shift @fields;
122 is( $f4->name, 'i1', 'Fourth field name is "i1"' );
92a61e09 123 is( $f4->data_type, 'int', 'Type is "int"' );
dbca887e 124 is( $f4->size, 11, 'Size is "11"' );
125 is( $f4->is_nullable, 0, 'Field cannot be null' );
126 is( $f4->default_value, '0', 'Default value is "0"' );
127 is( $f4->is_primary_key, 0, 'Field is not PK' );
128
129 my $f5 = shift @fields;
130 is( $f5->name, 's1', 'Fifth field name is "s1"' );
131 is( $f5->data_type, 'set', 'Type is "set"' );
f35e598e 132 is( $f5->size, 0, 'Size is "0"' );
dbca887e 133 is( $f5->is_nullable, 1, 'Field can be null' );
134 is( $f5->default_value, 'b', 'Default value is "b"' );
135 is( $f5->is_primary_key, 0, 'Field is not PK' );
136 my %f5extra = $f5->extra;
137 is( join(',', @{ $f5extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
138
139 my $f6 = shift @fields;
140 is( $f6->name, 'e1', 'Sixth field name is "e1"' );
141 is( $f6->data_type, 'enum', 'Type is "enum"' );
f35e598e 142 is( $f6->size, 0, 'Size is "0"' );
dbca887e 143 is( $f6->is_nullable, 1, 'Field can be null' );
144 is( $f6->default_value, 'c', 'Default value is "c"' );
145 is( $f6->is_primary_key, 0, 'Field is not PK' );
146 my %f6extra = $f6->extra;
147 is( join(',', @{ $f6extra{'list'} || [] }), 'a,b,c', 'List is "a,b,c"' );
148
149 my $f7 = shift @fields;
150 is( $f7->name, 'name', 'Seventh field name is "name"' );
151 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
152 is( $f7->size, 30, 'Size is "30"' );
153 is( $f7->is_nullable, 1, 'Field can be null' );
154 is( $f7->default_value, 'NULL', 'Default value is "NULL"' );
155 is( $f7->is_primary_key, 0, 'Field is not PK' );
156
157 my $f8 = shift @fields;
158 is( $f8->name, 'foo_type', 'Eighth field name is "foo_type"' );
159 is( $f8->data_type, 'enum', 'Type is "enum"' );
f35e598e 160 is( $f8->size, 0, 'Size is "0"' );
dbca887e 161 is( $f8->is_nullable, 0, 'Field cannot be null' );
162 is( $f8->default_value, 'vk', 'Default value is "vk"' );
163 is( $f8->is_primary_key, 0, 'Field is not PK' );
164 my %f8extra = $f8->extra;
165 is( join(',', @{ $f8extra{'list'} || [] }), 'vk,ck', 'List is "vk,ck"' );
166
167 my $f9 = shift @fields;
168 is( $f9->name, 'date', 'Ninth field name is "date"' );
169 is( $f9->data_type, 'timestamp', 'Type is "timestamp"' );
170 is( $f9->size, 0, 'Size is "0"' );
171 is( $f9->is_nullable, 1, 'Field can be null' );
172 is( $f9->default_value, undef, 'Default value is undefined' );
173 is( $f9->is_primary_key, 0, 'Field is not PK' );
174
175 my $f10 = shift @fields;
176 is( $f10->name, 'time_stamp2', 'Tenth field name is "time_stamp2"' );
177 is( $f10->data_type, 'timestamp', 'Type is "timestamp"' );
178 is( $f10->size, 0, 'Size is "0"' );
179 is( $f10->is_nullable, 1, 'Field can be null' );
180 is( $f10->default_value, undef, 'Default value is undefined' );
181 is( $f10->is_primary_key, 0, 'Field is not PK' );
182
183 my @indices = $table->get_indices;
184 is( scalar @indices, 3, 'Right number of indices (3)' );
185
186 my $i1 = shift @indices;
187 is( $i1->name, '', 'No name on index' );
188 is( $i1->type, NORMAL, 'Normal index' );
189 is( join(',', $i1->fields ), 'i1', 'Index is on field "i1"' );
190
191 my $i2 = shift @indices;
192 is( $i2->name, 'date_idx', 'Name is "date_idx"' );
193 is( $i2->type, NORMAL, 'Normal index' );
194 is( join(',', $i2->fields ), 'date', 'Index is on field "date"' );
195
196 my $i3 = shift @indices;
197 is( $i3->name, 'name_idx', 'Name is "name_idx"' );
198 is( $i3->type, NORMAL, 'Normal index' );
8149a03a 199 is( join(',', $i3->fields ), 'name', 'Index is on field "name(10)"' ); ## FIX ME
dbca887e 200
201 my @constraints = $table->get_constraints;
202 is( scalar @constraints, 2, 'Right number of constraints (2)' );
203
204 my $c1 = shift @constraints;
205 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
206 is( join(',', $c1->fields), 'check_id', 'Constraint is on "check_id"' );
207
208 my $c2 = shift @constraints;
209 is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
210 is( join(',', $c2->fields), 'date,i1', 'Constraint is on "date, i1"' );
211}
212
213{
7dcf6bc2 214 my $tr = SQL::Translator->new({ from => 'MySQL' });
215 my $val = $tr->parse(
dbca887e 216 q[
217 CREATE TABLE orders (
218 order_id integer NOT NULL auto_increment,
219 member_id varchar(255) comment 'fk to member',
220 billing_address_id int,
221 shipping_address_id int,
222 credit_card_id int,
223 status smallint NOT NULL,
224 store_id varchar(255) NOT NULL REFERENCES store,
225 tax decimal(8,2),
226 shipping_charge decimal(8,2),
227 price_paid decimal(8,2),
228 PRIMARY KEY (order_id),
229 KEY (status),
230 KEY (billing_address_id),
231 KEY (shipping_address_id),
232 KEY (member_id, store_id),
233 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
234 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
235 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
236 ) TYPE=INNODB COMMENT = 'orders table comment';
237
238 CREATE TABLE address (
239 address_id int NOT NULL auto_increment,
240 recipient varchar(255) NOT NULL,
241 address1 varchar(255) NOT NULL,
242 address2 varchar(255),
243 city varchar(255) NOT NULL,
244 state varchar(255) NOT NULL,
245 postal_code varchar(255) NOT NULL,
246 phone varchar(255),
247 PRIMARY KEY (address_id)
248 ) TYPE=INNODB;
249 ]
250 ) or die $tr->error;
251
252 my $schema = $tr->schema;
253 is( $schema->is_valid, 1, 'Schema is valid' );
254 my @tables = $schema->get_tables;
255 is( scalar @tables, 2, 'Right number of tables (2)' );
256
257 my $t1 = shift @tables;
258 is( $t1->name, 'orders', 'Found "orders" table' );
259 is( $t1->comments, 'orders table comment', 'Table comment OK' );
260
261 my @fields = $t1->get_fields;
262 is( scalar @fields, 10, 'Right number of fields (10)' );
263
264 my $f1 = shift @fields;
265 is( $f1->name, 'order_id', 'First field name is "order_id"' );
f35e598e 266 is( $f1->data_type, 'integer', 'Type is "integer"' );
267 is( $f1->size, 0, 'Size is "0"' );
dbca887e 268 is( $f1->is_nullable, 0, 'Field cannot be null' );
269 is( $f1->default_value, undef, 'Default value is undefined' );
270 is( $f1->is_primary_key, 1, 'Field is PK' );
271 is( $f1->is_auto_increment, 1, 'Field is auto inc' );
272
273 my $f2 = shift @fields;
274 is( $f2->name, 'member_id', 'Second field name is "member_id"' );
275 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
276 is( $f2->size, 255, 'Size is "255"' );
277 is( $f2->is_nullable, 1, 'Field can be null' );
278 is( $f2->comments, 'fk to member', 'Field comment OK' );
279 is( $f2->default_value, undef, 'Default value is undefined' );
280
281 my $f3 = shift @fields;
282 is( $f3->name, 'billing_address_id',
283 'Third field name is "billing_address_id"' );
92a61e09 284 is( $f3->data_type, 'int', 'Type is "int"' );
f35e598e 285 is( $f3->size, 0, 'Size is "0"' );
dbca887e 286
287 my $f4 = shift @fields;
288 is( $f4->name, 'shipping_address_id',
289 'Fourth field name is "shipping_address_id"' );
92a61e09 290 is( $f4->data_type, 'int', 'Type is "int"' );
f35e598e 291 is( $f4->size, 0, 'Size is "0"' );
dbca887e 292
293 my $f5 = shift @fields;
294 is( $f5->name, 'credit_card_id', 'Fifth field name is "credit_card_id"' );
92a61e09 295 is( $f5->data_type, 'int', 'Type is "int"' );
f35e598e 296 is( $f5->size, 0, 'Size is "0"' );
dbca887e 297
298 my $f6 = shift @fields;
299 is( $f6->name, 'status', 'Sixth field name is "status"' );
300 is( $f6->data_type, 'smallint', 'Type is "smallint"' );
f35e598e 301 is( $f6->size, 0, 'Size is "0"' );
dbca887e 302 is( $f6->is_nullable, 0, 'Field cannot be null' );
303
304 my $f7 = shift @fields;
305 is( $f7->name, 'store_id', 'Seventh field name is "store_id"' );
306 is( $f7->data_type, 'varchar', 'Type is "varchar"' );
307 is( $f7->size, 255, 'Size is "255"' );
308 is( $f7->is_nullable, 0, 'Field cannot be null' );
309 is( $f7->is_foreign_key, 1, 'Field is a FK' );
310 my $fk_ref = $f7->foreign_key_reference;
1d8914e7 311 isa_ok( $fk_ref, 'SQL::Translator::Object::Constraint', 'FK' );
dbca887e 312 is( $fk_ref->reference_table, 'store', 'FK is to "store" table' );
313
314 my $f8 = shift @fields;
315 is( $f8->name, 'tax', 'Eighth field name is "tax"' );
316 is( $f8->data_type, 'decimal', 'Type is "decimal"' );
317 is( $f8->size, '8,2', 'Size is "8,2"' );
318
319 my $f9 = shift @fields;
320 is( $f9->name, 'shipping_charge', 'Ninth field name is "shipping_charge"' );
321 is( $f9->data_type, 'decimal', 'Type is "decimal"' );
322 is( $f9->size, '8,2', 'Size is "8,2"' );
323
324 my $f10 = shift @fields;
325 is( $f10->name, 'price_paid', 'Tenth field name is "price_paid"' );
326 is( $f10->data_type, 'decimal', 'Type is "decimal"' );
327 is( $f10->size, '8,2', 'Size is "8,2"' );
328
329 my @indices = $t1->get_indices;
330 is( scalar @indices, 4, 'Right number of indices (4)' );
331
332 my $i1 = shift @indices;
333 is( $i1->type, NORMAL, 'First index is normal' );
334 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
335
336 my $i2 = shift @indices;
337 is( $i2->type, NORMAL, 'Second index is normal' );
338 is( join(',', $i2->fields), 'billing_address_id',
339 'Index is on "billing_address_id"' );
340
341 my $i3 = shift @indices;
342 is( $i3->type, NORMAL, 'Third index is normal' );
343 is( join(',', $i3->fields), 'shipping_address_id',
344 'Index is on "shipping_address_id"' );
345
346 my $i4 = shift @indices;
347 is( $i4->type, NORMAL, 'Third index is normal' );
348 is( join(',', $i4->fields), 'member_id,store_id',
349 'Index is on "member_id,store_id"' );
350
351 my @constraints = $t1->get_constraints;
352 is( scalar @constraints, 5, 'Right number of constraints (5)' );
353
354 my $c1 = shift @constraints;
355 is( $c1->type, PRIMARY_KEY, 'Constraint is a PK' );
356 is( join(',', $c1->fields), 'order_id', 'Constraint is on "order_id"' );
357
358 my $c2 = shift @constraints;
359 is( $c2->type, FOREIGN_KEY, 'Constraint is a FK' );
360 is( join(',', $c2->fields), 'status', 'Constraint is on "status"' );
361 is( $c2->reference_table, 'order_status', 'To table "order_status"' );
362 is( join(',', $c2->reference_fields), 'id', 'To field "id"' );
363
364 my $c3 = shift @constraints;
365 is( $c3->type, FOREIGN_KEY, 'Constraint is a FK' );
366 is( join(',', $c3->fields), 'billing_address_id',
367 'Constraint is on "billing_address_id"' );
368 is( $c3->reference_table, 'address', 'To table "address"' );
369 is( join(',', $c3->reference_fields), 'address_id',
370 'To field "address_id"' );
371
372 my $c4 = shift @constraints;
373 is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
374 is( join(',', $c4->fields), 'shipping_address_id',
375 'Constraint is on "shipping_address_id"' );
376 is( $c4->reference_table, 'address', 'To table "address"' );
377 is( join(',', $c4->reference_fields), 'address_id',
378 'To field "address_id"' );
379
380 my $c5 = shift @constraints;
381 is( $c5->type, FOREIGN_KEY, 'Constraint is a FK' );
382 is( join(',', $c5->fields), 'store_id', 'Constraint is on "store_id"' );
383 is( $c5->reference_table, 'store', 'To table "store"' );
384 is( join(',', map { $_ || '' } $c5->reference_fields), '',
385 'No reference fields defined' );
386
387 my $t2 = shift @tables;
388 is( $t2->name, 'address', 'Found "address" table' );
389
390 my @t2_fields = $t2->get_fields;
391 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
392}
393
394# djh Tests for:
395# USE database ;
396# ALTER TABLE ADD FOREIGN KEY
397# trailing comma on last create definition
398# Ignoring INSERT statements
399#
400{
7dcf6bc2 401 my $tr = SQL::Translator->new({ from => 'MySQL' });
402 my $data = $tr->parse(
dbca887e 403 q[
404 USE database_name;
405
406 CREATE TABLE one (
407 id integer NOT NULL auto_increment,
408 two_id integer NOT NULL auto_increment,
409 some_data text,
410 PRIMARY KEY (id),
411 INDEX (two_id),
412 ) TYPE=INNODB;
413
414 CREATE TABLE two (
415 id int NOT NULL auto_increment,
416 one_id int NOT NULL auto_increment,
417 some_data text,
418 PRIMARY KEY (id),
419 INDEX (one_id),
420 FOREIGN KEY (one_id) REFERENCES one (id),
421 ) TYPE=INNODB;
422
423 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
424
425 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
426 ]
427 ) or die $tr->error;
428
429 my $schema = $tr->schema;
430 is( $schema->is_valid, 1, 'Schema is valid' );
431 my $db_name = $schema->name;
432 is( $db_name, 'database_name', 'Database name extracted from USE' );
433 my @tables = $schema->get_tables;
434 is( scalar @tables, 2, 'Right number of tables (2)' );
435 my $table1 = shift @tables;
436 is( $table1->name, 'one', 'Found "one" table' );
437 my $table2 = shift @tables;
438 is( $table2->name, 'two', 'Found "two" table' );
439
440 my @constraints = $table1->get_constraints;
441 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
442
443 my $t1c1 = shift @constraints;
444 is( $t1c1->type, PRIMARY_KEY, 'Constraint is a PK' );
445 is( join(',', $t1c1->fields), 'id', 'Constraint is on "id"' );
446
447 my $t1c2 = shift @constraints;
448 is( $t1c2->type, FOREIGN_KEY, 'Constraint is a FK' );
449 is( join(',', $t1c2->fields), 'two_id', 'Constraint is on "two_id"' );
450 is( $t1c2->reference_table, 'two', 'To table "two"' );
451 is( join(',', $t1c2->reference_fields), 'id', 'To field "id"' );
452
453 @constraints = $table2->get_constraints;
454 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
455
456 my $t2c1 = shift @constraints;
457 is( $t2c1->type, PRIMARY_KEY, 'Constraint is a PK' );
458 is( join(',', $t2c1->fields), 'id', 'Constraint is on "id"' );
459
460 my $t2c2 = shift @constraints;
461 is( $t2c2->type, FOREIGN_KEY, 'Constraint is a FK' );
462 is( join(',', $t2c2->fields), 'one_id', 'Constraint is on "one_id"' );
463 is( $t2c2->reference_table, 'one', 'To table "one"' );
464 is( join(',', $t2c2->reference_fields), 'id', 'To field "id"' );
465}
466
467# cch Tests for:
468# comments like: /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
469# char fields with character set and collate qualifiers
470# timestamp fields with on update qualifier
471# charset table option
472#
473{
7dcf6bc2 474 my $tr = SQL::Translator->new({ from => 'MySQL', parser_args => { mysql_parser_version => 50003 } });
475 my $data = $tr->parse(
476 q[
dbca887e 477 DELIMITER ;;
478 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
479 /*!50003 CREATE */ /*!50017 DEFINER=`cmdomain`@`localhost` */
480 /*!50003 TRIGGER `acl_entry_insert` BEFORE INSERT ON `acl_entry`
481 FOR EACH ROW SET NEW.dateCreated = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00'),
482 NEW.dateModified = CONVERT_TZ(SYSDATE(),'SYSTEM','+0:00') */;;
483
484 DELIMITER ;
485 CREATE TABLE one (
486 `op` varchar(255) character set latin1 collate latin1_bin default NULL,
487 `last_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
488 ) TYPE=INNODB DEFAULT CHARSET=latin1;
489
490 /*!50001 CREATE ALGORITHM=UNDEFINED */
491 /*!50013 DEFINER=`cmdomain`@`localhost` SQL SECURITY DEFINER */
492 /*! VIEW `vs_asset` AS
493 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
494 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
495 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
496 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
497 `a`.`foreign_asset_id` AS `foreign_asset_id`,
498 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
499 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
500 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
501 `m`.`user_id` AS `user_access`
502 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
503 DELIMITER ;;
504 /*!50001 CREATE */
505 /*! VIEW `vs_asset2` AS
506 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
507 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
508 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
509 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
510 `a`.`foreign_asset_id` AS `foreign_asset_id`,
511 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
512 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
513 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
514 `m`.`user_id` AS `user_access`
515 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
516 DELIMITER ;;
517 /*!50001 CREATE OR REPLACE */
518 /*! VIEW `vs_asset3` AS
519 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
520 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
521 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
522 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
523 `a`.`foreign_asset_id` AS `foreign_asset_id`,
524 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
525 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
526 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
527 `m`.`user_id` AS `user_access`
528 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
529 DELIMITER ;;
530 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
531 DETERMINISTIC
532 BEGIN
533 DECLARE rval TIMESTAMP;
534 IF ( millis_since_1970 = 0 )
535 THEN
536 SET rval = NULL;
537 ELSE
538 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
539 END IF;
540 RETURN rval;
541 END */;;
542 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
543 BEGIN
544 DECLARE hasMoreRows BOOL DEFAULT TRUE;
545 DECLARE t_group_id INT;
546 DECLARE t_user_id INT ;
547 DECLARE t_user_name VARCHAR (512) ;
548 DECLARE t_message VARCHAR (512) ;
549
550 DROP TABLE IF EXISTS group_acl;
551 DROP TABLE IF EXISTS user_group;
552 DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
553
554 CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
555 FROM asset d, acl_entry e, alterpoint_principal p
556 WHERE d.acl_id = e.acl
557 AND p.id = e.principal AND d.acl_id = t_acl_id;
558
559 CREATE TEMPORARY TABLE user_group SELECT a.id user_id, a.name user_name, c.id group_id
560 FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
561 WHERE a.id = b.user_ref AND b.elt = c.id;
562
563 INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
564 FROM group_acl, user_group
565 WHERE group_acl.group_id = user_group.group_id ;
566 END */;;
567 ]
568 ) or die $tr->error;
569
570 my $schema = $tr->schema;
571 is( $schema->is_valid, 1, 'Schema is valid' );
572 my @tables = $schema->get_tables;
573 is( scalar @tables, 1, 'Right number of tables (1)' );
574 my $table1 = shift @tables;
575 is( $table1->name, 'one', 'Found "one" table' );
576
577 my @fields = $table1->get_fields;
578 is(scalar @fields, 2, 'Right number of fields (2) on table one');
579 my $tableTypeFound = 0;
580 my $charsetFound = 0;
581 for my $t1_option_ref ( $table1->options ) {
582 my($key, $value) = %{$t1_option_ref};
583 if ( $key eq 'TYPE' ) {
584 is($value, 'INNODB', 'Table has right table type option' );
585 $tableTypeFound = 1;
586 } elsif ( $key eq 'CHARACTER SET' ) {
587 is($value, 'latin1', 'Table has right character set option' );
588 $charsetFound = 1;
589 }
590 }
591 fail('Table did not have a type option') unless $tableTypeFound;
592 fail('Table did not have a character set option') unless $charsetFound;
593
594 my $t1f1 = shift @fields;
595 is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
596 is( $t1f1->size, 255, 'Field is right size' );
597 is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
598 is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
599 is( $t1f1->default_value, 'NULL', 'Field has right default value' );
600
601 my $t1f2 = shift @fields;
602 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
603 ok( !$t1f2->is_nullable, 'Field is not nullable' );
604 is_deeply(
605 $t1f2->default_value,
606 \'CURRENT_TIMESTAMP',
607 'Field has right default value'
608 );
609 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
610
611 my @views = $schema->get_views;
612 is( scalar @views, 3, 'Right number of views (3)' );
613 my ($view3, $view1, $view2) = @views;
614 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
615 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
616 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
617 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
618 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
619 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
620
621 my @procs = $schema->get_procedures;
622 is( scalar @procs, 2, 'Right number of procedures (2)' );
623 my $proc1 = shift @procs;
624 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
625 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
626 my $proc2 = shift @procs;
627 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
628 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
629}
630
631# Tests for collate table option
632{
7dcf6bc2 633 my $tr = SQL::Translator->new({ from => 'MySQL', parser_args => { mysql_parser_version => 50003 } });
634 my $data = $tr->parse(
dbca887e 635 q[
636 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
637 ] );
638
639 my $schema = $tr->schema;
640 is( $schema->is_valid, 1, 'Schema is valid' );
641 my @tables = $schema->get_tables;
642 is( scalar @tables, 1, 'Right number of tables (1)' );
643 my $table1 = shift @tables;
644 is( $table1->name, 'test', 'Found "test" table' );
645
646
647 my $collate = "Not found!";
648 my $charset = "Not found!";
649 for my $t1_option_ref ( $table1->options ) {
650 my($key, $value) = %{$t1_option_ref};
651 $collate = $value if $key eq 'COLLATE';
652 $charset = $value if $key eq 'CHARACTER SET';
653 }
654 is($collate, 'latin1_bin', "Collate found");
655 is($charset, 'latin1', "Character set found");
656}
657
658# Test the mysql version parser (probably needs to migrate to t/utils.t)
659my $parse_as = {
660 perl => {
661 '3.23.2' => 3.023002,
662 '4' => 4.000000,
663 '50003' => 5.000003,
664 '5.01.0' => 5.001000,
665 '5.1' => 5.001000,
666 },
667 mysql => {
668 '3.23.2' => 32302,
669 '4' => 40000,
670 '50003' => 50003,
671 '5.01.0' => 50100,
672 '5.1' => 50100,
673 },
674};
675
deb9f57f 676my $tr = SQL::Translator->new;
dbca887e 677for my $target (keys %$parse_as) {
678 for my $str (keys %{$parse_as->{$target}}) {
679 cmp_ok (
deb9f57f 680 $tr->engine_version($str, $target),
dbca887e 681 '==',
682 $parse_as->{$target}{$str},
683 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
684 );
685 }
686}
687
deb9f57f 688eval { $tr->engine_version ('bogus5.1') };
dbca887e 689ok ($@, 'Exception thrown on invalid version string');
690
691{
deb9f57f 692 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 693 my $data = q|create table merge_example (
694 id int(11) NOT NULL auto_increment,
695 shape_field geometry NOT NULL,
696 PRIMARY KEY (id),
697 SPATIAL KEY shape_field (shape_field)
698 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
699
7dcf6bc2 700 my $val = $tr->parse($data);
dbca887e 701 my $schema = $tr->schema;
702 is( $schema->is_valid, 1, 'Schema is valid' );
703 my @tables = $schema->get_tables;
704 is( scalar @tables, 1, 'Right number of tables (1)' );
705 my $table = shift @tables;
706 is( $table->name, 'merge_example', 'Found "merge_example" table' );
707
708 my $tableTypeFound = 0;
709 my $unionFound = 0;
710 for my $t_option_ref ( $table->options ) {
711 my($key, $value) = %{$t_option_ref};
712 if ( $key eq 'ENGINE' ) {
713 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
714 $tableTypeFound = 1;
715 } elsif ( $key eq 'UNION' ) {
716 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
717 "UNION option has correct set");
718 $unionFound = 1;
719 }
720 }
721
722 fail('Table did not have a type option') unless $tableTypeFound;
723 fail('Table did not have a union option') unless $unionFound;
724
725 my @fields = $table->get_fields;
726 is( scalar @fields, 2, 'Right number of fields (2)' );
727 my $f1 = shift @fields;
728 my $f2 = shift @fields;
729 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 730 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 731 is( $f1->size, 11, 'Size is "11"' );
732 is( $f1->is_nullable, 0, 'Field cannot be null' );
733 is( $f1->is_primary_key, 1, 'Field is PK' );
734
735 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
736 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
737 is( $f2->is_nullable, 0, 'Field cannot be null' );
738 is( $f2->is_primary_key, 0, 'Field is not PK' );
739
740 my @indices = $table->get_indices;
741 is( scalar @indices, 1, 'Right number of indices (1)' );
742 my $i1 = shift @indices;
743 is( $i1->name, 'shape_field', 'No name on index' );
744 is( $i1->type, SPATIAL, 'Spatial index' );
745
746 my @constraints = $table->get_constraints;
747 is( scalar @constraints, 1, 'Right number of constraints (1)' );
748 my $c = shift @constraints;
749 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
750 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
751}
752
753{
754 my @data = (
755 q|create table quote (
756 id int(11) NOT NULL auto_increment,
757 PRIMARY KEY (id)
758 ) ENGINE="innodb";|,
759 q|create table quote (
760 id int(11) NOT NULL auto_increment,
761 PRIMARY KEY (id)
762 ) ENGINE='innodb';|,
763 q|create table quote (
764 id int(11) NOT NULL auto_increment,
765 PRIMARY KEY (id)
766 ) ENGINE=innodb;|,
767 );
768 for my $data (@data) {
7dcf6bc2 769 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 770
7dcf6bc2 771 my $val = $tr->parse($data);
dbca887e 772 my $schema = $tr->schema;
773 is( $schema->is_valid, 1, 'Schema is valid' );
774 my @tables = $schema->get_tables;
775 is( scalar @tables, 1, 'Right number of tables (1)' );
776 my $table = shift @tables;
777 is( $table->name, 'quote', 'Found "quote" table' );
778
779 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, 'innodb', 'Table has right table engine option' );
784 $tableTypeFound = 1;
785 }
786 }
787
788 fail('Table did not have a type option') unless $tableTypeFound;
789
790 my @fields = $table->get_fields;
791 my $f1 = shift @fields;
792 is( $f1->name, 'id', 'First field name is "id"' );
92a61e09 793 is( $f1->data_type, 'int', 'Type is "int"' );
dbca887e 794 is( $f1->size, 11, 'Size is "11"' );
795 is( $f1->is_nullable, 0, 'Field cannot be null' );
796 is( $f1->is_primary_key, 1, 'Field is PK' );
797 }
798}
799
800{
7dcf6bc2 801 my $tr = SQL::Translator->new({ from => 'MySQL' });
dbca887e 802 my $data = q|create table "sessions" (
803 id char(32) not null default '0' primary key,
804 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
805 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
806 );|;
807
7dcf6bc2 808 my $val = $tr->parse($data);
dbca887e 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, 'sessions', 'Found "sessions" table' );
815
816 my @fields = $table->get_fields;
817 is( scalar @fields, 3, 'Right number of fields (3)' );
818 my $f1 = shift @fields;
819 my $f2 = shift @fields;
820 my $f3 = shift @fields;
821 is( $f1->name, 'id', 'First field name is "id"' );
822 is( $f1->data_type, 'char', 'Type is "char"' );
823 is( $f1->size, 32, 'Size is "32"' );
824 is( $f1->is_nullable, 0, 'Field cannot be null' );
825 is( $f1->default_value, '0', 'Default value is "0"' );
826 is( $f1->is_primary_key, 1, 'Field is PK' );
827
828 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
829 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
830 is( $f2->size, 12, 'Size is "12"' );
831 is( $f2->is_nullable, 0, 'Field can not be null' );
832 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
833 is( $f2->is_primary_key, 0, 'Field is not PK' );
834
835 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
836 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
837 is( $f3->name, 'user', 'Second field name is "user"' );
838 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
839 is( $f3->size, 20, 'Size is "20"' );
840 is( $f3->is_nullable, 0, 'Field can not be null' );
841 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
842 is( $f3->is_primary_key, 0, 'Field is not PK' );
843}
4177b4ea 844
845done_testing;
SQL::Translator (GSOC work)