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