projects / dbsrgits/SQL-Translator.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
Add giftnuss to contributors, awesome triage work
[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 {
bc9932bf 14 maybe_plan(329, "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',
bc9932bf 80 e1 enum('a','b','c') default "c",
251b6ff5 81 name varchar(30) default NULL,
82 foo_type enum('vk','ck') NOT NULL default 'vk',
83 date timestamp,
84 time_stamp2 timestamp,
bc9932bf 85 foo_enabled bit(1) default b'0',
86 bar_enabled bit(1) default b"1",
251b6ff5 87 KEY (i1),
62a64f6c 88 UNIQUE (date, i1) USING BTREE,
251b6ff5 89 KEY date_idx (date),
90 KEY name_idx (name(10))
91 ) TYPE=MyISAM PACK_KEYS=1;
92 ]
93 );
94
2ab84c11 95 my $schema = $tr->schema;
e3252059 96 is( $schema->is_valid, 1, 'Schema is valid' );
2ab84c11 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' );
101
102 my @fields = $table->get_fields;
bc9932bf 103 is( scalar @fields, 12, 'Right number of fields (12)' );
2ab84c11 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' );
115
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' );
123
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' );
131
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' );
3a0b6f0a 139
2ab84c11 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"' );
3a0b6f0a 149
2ab84c11 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"' );
3a0b6f0a 159
2ab84c11 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' );
3a0b6f0a 167
2ab84c11 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"' );
177
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' );
185
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' );
193
bc9932bf 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' );
201
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' );
209
2ab84c11 210 my @indices = $table->get_indices;
211 is( scalar @indices, 3, 'Right number of indices (3)' );
212
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"' );
217
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"' );
222
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)"' );
227
228 my @constraints = $table->get_constraints;
229 is( scalar @constraints, 2, 'Right number of constraints (2)' );
230
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"' );
234
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"' );
251b6ff5 238}
239
240{
241 my $tr = SQL::Translator->new;
242 my $data = parse($tr,
243 q[
244 CREATE TABLE orders (
3a0b6f0a 245 order_id integer NOT NULL auto_increment,
85a6c976 246 member_id varchar(255) comment 'fk to member',
251b6ff5 247 billing_address_id int,
248 shipping_address_id int,
249 credit_card_id int,
250 status smallint NOT NULL,
251 store_id varchar(255) NOT NULL REFERENCES store,
252 tax decimal(8,2),
253 shipping_charge decimal(8,2),
254 price_paid decimal(8,2),
1f5b2625 255 PRIMARY KEY (order_id) USING BTREE,
62a64f6c 256 KEY (status) USING BTREE,
257 KEY USING BTREE (billing_address_id),
251b6ff5 258 KEY (shipping_address_id),
259 KEY (member_id, store_id),
556d0509 260 FOREIGN KEY (status) REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
251b6ff5 261 FOREIGN KEY (billing_address_id) REFERENCES address(address_id),
262 FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
85a6c976 263 ) TYPE=INNODB COMMENT = 'orders table comment';
2ab84c11 264
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,
273 phone varchar(255),
274 PRIMARY KEY (address_id)
275 ) TYPE=INNODB;
251b6ff5 276 ]
277 ) or die $tr->error;
278
2ab84c11 279 my $schema = $tr->schema;
e3252059 280 is( $schema->is_valid, 1, 'Schema is valid' );
2ab84c11 281 my @tables = $schema->get_tables;
282 is( scalar @tables, 2, 'Right number of tables (2)' );
283
284 my $t1 = shift @tables;
285 is( $t1->name, 'orders', 'Found "orders" table' );
85a6c976 286 is( $t1->comments, 'orders table comment', 'Table comment OK' );
2ab84c11 287
288 my @fields = $t1->get_fields;
289 is( scalar @fields, 10, 'Right number of fields (10)' );
290
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' );
299
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' );
85a6c976 305 is( $f2->comments, 'fk to member', 'Field comment OK' );
2ab84c11 306 is( $f2->default_value, undef, 'Default value is undefined' );
307
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"' );
313
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"' );
319
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"' );
324
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' );
330
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' );
340
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"' );
345
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"' );
350
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"' );
355
356 my @indices = $t1->get_indices;
357 is( scalar @indices, 4, 'Right number of indices (4)' );
358
359 my $i1 = shift @indices;
360 is( $i1->type, NORMAL, 'First index is normal' );
361 is( join(',', $i1->fields), 'status', 'Index is on "status"' );
362
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"' );
367
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"' );
372
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"' );
377
378 my @constraints = $t1->get_constraints;
379 is( scalar @constraints, 5, 'Right number of constraints (5)' );
380
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"' );
384
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"' );
390
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"' );
398
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"' );
406
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"' );
c9ca0061 411 is( join(',', map { $_ || '' } $c5->reference_fields), '',
412 'No reference fields defined' );
2ab84c11 413
414 my $t2 = shift @tables;
415 is( $t2->name, 'address', 'Found "address" table' );
416
417 my @t2_fields = $t2->get_fields;
418 is( scalar @t2_fields, 8, 'Right number of fields (8)' );
251b6ff5 419}
13aec984 420
421# djh Tests for:
422# USE database ;
423# ALTER TABLE ADD FOREIGN KEY
424# trailing comma on last create definition
425# Ignoring INSERT statements
426#
427{
428 my $tr = SQL::Translator->new;
429 my $data = parse($tr,
430 q[
431 USE database_name;
432
433 CREATE TABLE one (
434 id integer NOT NULL auto_increment,
435 two_id integer NOT NULL auto_increment,
436 some_data text,
437 PRIMARY KEY (id),
438 INDEX (two_id),
439 ) TYPE=INNODB;
440
441 CREATE TABLE two (
442 id int NOT NULL auto_increment,
443 one_id int NOT NULL auto_increment,
444 some_data text,
445 PRIMARY KEY (id),
446 INDEX (one_id),
447 FOREIGN KEY (one_id) REFERENCES one (id),
448 ) TYPE=INNODB;
449
450 ALTER TABLE one ADD FOREIGN KEY (two_id) REFERENCES two (id);
451
306c3c97 452 INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
13aec984 453 ]
454 ) or die $tr->error;
455
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' );
466
467 my @constraints = $table1->get_constraints;
468 is(scalar @constraints, 2, 'Right number of constraints (2) on table one');
469
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"' );
473
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"' );
479
480 @constraints = $table2->get_constraints;
481 is(scalar @constraints, 2, 'Right number of constraints (2) on table two');
482
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"' );
486
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"' );
492}
493
b68bfdf0 494# cch Tests for:
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
499#
500{
d31c185b 501 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
b68bfdf0 502 my $data = parse($tr,
503 q[
3e5e5622 504 DELIMITER ;;
505 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
da9f2af8 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') */;;
b68bfdf0 510
3e5e5622 511 DELIMITER ;
b68bfdf0 512 CREATE TABLE one (
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;
d31c185b 516
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`))) */;
530 DELIMITER ;;
22b9814a 531 /*!50001 CREATE */
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`))) */;
543 DELIMITER ;;
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`))) */;
556 DELIMITER ;;
d31c185b 557 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
558 DETERMINISTIC
559 BEGIN
560 DECLARE rval TIMESTAMP;
561 IF ( millis_since_1970 = 0 )
562 THEN
563 SET rval = NULL;
564 ELSE
565 SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
566 END IF;
567 RETURN rval;
568 END */;;
569 /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
570 BEGIN
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) ;
576
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;
580
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;
585
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;
589
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 ;
593 END */;;
b68bfdf0 594 ]
595 ) or die $tr->error;
596
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' );
603
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' );
612 $tableTypeFound = 1;
613 } elsif ( $key eq 'CHARACTER SET' ) {
614 is($value, 'latin1', 'Table has right character set option' );
615 $charsetFound = 1;
616 }
617 }
618 fail('Table did not have a type option') unless $tableTypeFound;
619 fail('Table did not have a character set option') unless $charsetFound;
620
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' );
627
628 my $t1f2 = shift @fields;
629 is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
630 ok( !$t1f2->is_nullable, 'Field is not nullable' );
6d80a120 631 is_deeply(
632 $t1f2->default_value,
633 \'CURRENT_TIMESTAMP',
634 'Field has right default value'
635 );
b68bfdf0 636 is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
3ebe2ce5 637
d31c185b 638 my @views = $schema->get_views;
22b9814a 639 is( scalar @views, 3, 'Right number of views (3)' );
3ebe2ce5 640
641 my ($view1, $view2, $view3) = @views;
d31c185b 642 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
22b9814a 643 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
644 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
3ebe2ce5 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");
648
d31c185b 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");
b68bfdf0 657}
658
a7f49dfb 659# Tests for collate table option
660{
661 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
662 my $data = parse($tr,
663 q[
bb4c66d1 664 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
a7f49dfb 665 ] );
4d438549 666
a7f49dfb 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' );
673
674
675 my $collate = "Not found!";
bb4c66d1 676 my $charset = "Not found!";
a7f49dfb 677 for my $t1_option_ref ( $table1->options ) {
678 my($key, $value) = %{$t1_option_ref};
bb4c66d1 679 $collate = $value if $key eq 'COLLATE';
680 $charset = $value if $key eq 'CHARACTER SET';
a7f49dfb 681 }
682 is($collate, 'latin1_bin', "Collate found");
bb4c66d1 683 is($charset, 'latin1', "Character set found");
a7f49dfb 684}
5d666b31 685
686# Test the mysql version parser (probably needs to migrate to t/utils.t)
687my $parse_as = {
688 perl => {
689 '3.23.2' => 3.023002,
690 '4' => 4.000000,
691 '50003' => 5.000003,
692 '5.01.0' => 5.001000,
693 '5.1' => 5.001000,
694 },
695 mysql => {
696 '3.23.2' => 32302,
697 '4' => 40000,
698 '50003' => 50003,
699 '5.01.0' => 50100,
700 '5.1' => 50100,
701 },
702};
703
704for my $target (keys %$parse_as) {
705 for my $str (keys %{$parse_as->{$target}}) {
706 cmp_ok (
707 SQL::Translator::Utils::parse_mysql_version ($str, $target),
708 '==',
709 $parse_as->{$target}{$str},
710 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
711 );
712 }
713}
714
715eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
716ok ($@, 'Exception thrown on invalid version string');
9a96648f 717
718{
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,
723 PRIMARY KEY (id),
724 SPATIAL KEY shape_field (shape_field)
725 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
726
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' );
734
735 my $tableTypeFound = 0;
736 my $unionFound = 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' );
741 $tableTypeFound = 1;
742 } elsif ( $key eq 'UNION' ) {
743 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
744 "UNION option has correct set");
745 $unionFound = 1;
746 }
747 }
748
749 fail('Table did not have a type option') unless $tableTypeFound;
750 fail('Table did not have a union option') unless $unionFound;
751
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' );
761
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' );
766
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' );
772
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"' );
778}
47666977 779
780{
781 my @data = (
782 q|create table quote (
783 id int(11) NOT NULL auto_increment,
784 PRIMARY KEY (id)
785 ) ENGINE="innodb";|,
786 q|create table quote (
787 id int(11) NOT NULL auto_increment,
788 PRIMARY KEY (id)
789 ) ENGINE='innodb';|,
790 q|create table quote (
791 id int(11) NOT NULL auto_increment,
792 PRIMARY KEY (id)
793 ) ENGINE=innodb;|,
794 );
795 for my $data (@data) {
796 my $tr = SQL::Translator->new;
797
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' );
805
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' );
811 $tableTypeFound = 1;
812 }
813 }
814
815 fail('Table did not have a type option') unless $tableTypeFound;
816
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' );
824 }
825}
826
8b075c9f 827{
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',
97902ff6 833 key using btree (ssn)
8b075c9f 834 );|;
835
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' );
843
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' );
855
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' );
862
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' );
871}
DDL Tool (dbsrgits@git.shadowcat.co.uk:SQL-Translator.git)