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