projects / dbsrgits/SQL-Translator.git / blame
? search:
summary | shortlog | log | commit | commitdiff | tree
blob | history | HEAD
patch from rt67989 applied, changes dependency from Digest::SHA1 to Digest::SHA
[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),
62a64f6c 86 UNIQUE (date, i1) USING BTREE,
251b6ff5 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),
1f5b2625 237 PRIMARY KEY (order_id) USING BTREE,
62a64f6c 238 KEY (status) USING BTREE,
239 KEY USING BTREE (billing_address_id),
251b6ff5 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' );
3ebe2ce5 619
d31c185b 620 my @views = $schema->get_views;
22b9814a 621 is( scalar @views, 3, 'Right number of views (3)' );
3ebe2ce5 622
623 my ($view1, $view2, $view3) = @views;
d31c185b 624 is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
22b9814a 625 is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
626 is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
3ebe2ce5 627 like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
628 like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
629 unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
630
d31c185b 631 my @procs = $schema->get_procedures;
632 is( scalar @procs, 2, 'Right number of procedures (2)' );
633 my $proc1 = shift @procs;
634 is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
635 like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
636 my $proc2 = shift @procs;
637 is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
638 like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
b68bfdf0 639}
640
a7f49dfb 641# Tests for collate table option
642{
643 my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
644 my $data = parse($tr,
645 q[
bb4c66d1 646 CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
a7f49dfb 647 ] );
4d438549 648
a7f49dfb 649 my $schema = $tr->schema;
650 is( $schema->is_valid, 1, 'Schema is valid' );
651 my @tables = $schema->get_tables;
652 is( scalar @tables, 1, 'Right number of tables (1)' );
653 my $table1 = shift @tables;
654 is( $table1->name, 'test', 'Found "test" table' );
655
656
657 my $collate = "Not found!";
bb4c66d1 658 my $charset = "Not found!";
a7f49dfb 659 for my $t1_option_ref ( $table1->options ) {
660 my($key, $value) = %{$t1_option_ref};
bb4c66d1 661 $collate = $value if $key eq 'COLLATE';
662 $charset = $value if $key eq 'CHARACTER SET';
a7f49dfb 663 }
664 is($collate, 'latin1_bin', "Collate found");
bb4c66d1 665 is($charset, 'latin1', "Character set found");
a7f49dfb 666}
5d666b31 667
668# Test the mysql version parser (probably needs to migrate to t/utils.t)
669my $parse_as = {
670 perl => {
671 '3.23.2' => 3.023002,
672 '4' => 4.000000,
673 '50003' => 5.000003,
674 '5.01.0' => 5.001000,
675 '5.1' => 5.001000,
676 },
677 mysql => {
678 '3.23.2' => 32302,
679 '4' => 40000,
680 '50003' => 50003,
681 '5.01.0' => 50100,
682 '5.1' => 50100,
683 },
684};
685
686for my $target (keys %$parse_as) {
687 for my $str (keys %{$parse_as->{$target}}) {
688 cmp_ok (
689 SQL::Translator::Utils::parse_mysql_version ($str, $target),
690 '==',
691 $parse_as->{$target}{$str},
692 "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
693 );
694 }
695}
696
697eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
698ok ($@, 'Exception thrown on invalid version string');
9a96648f 699
700{
701 my $tr = SQL::Translator->new;
702 my $data = q|create table merge_example (
703 id int(11) NOT NULL auto_increment,
704 shape_field geometry NOT NULL,
705 PRIMARY KEY (id),
706 SPATIAL KEY shape_field (shape_field)
707 ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
708
709 my $val = parse($tr, $data);
710 my $schema = $tr->schema;
711 is( $schema->is_valid, 1, 'Schema is valid' );
712 my @tables = $schema->get_tables;
713 is( scalar @tables, 1, 'Right number of tables (1)' );
714 my $table = shift @tables;
715 is( $table->name, 'merge_example', 'Found "merge_example" table' );
716
717 my $tableTypeFound = 0;
718 my $unionFound = 0;
719 for my $t_option_ref ( $table->options ) {
720 my($key, $value) = %{$t_option_ref};
721 if ( $key eq 'ENGINE' ) {
722 is($value, 'MRG_MyISAM', 'Table has right table engine option' );
723 $tableTypeFound = 1;
724 } elsif ( $key eq 'UNION' ) {
725 is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
726 "UNION option has correct set");
727 $unionFound = 1;
728 }
729 }
730
731 fail('Table did not have a type option') unless $tableTypeFound;
732 fail('Table did not have a union option') unless $unionFound;
733
734 my @fields = $table->get_fields;
735 is( scalar @fields, 2, 'Right number of fields (2)' );
736 my $f1 = shift @fields;
737 my $f2 = shift @fields;
738 is( $f1->name, 'id', 'First field name is "id"' );
739 is( $f1->data_type, 'int', 'Type is "int"' );
740 is( $f1->size, 11, 'Size is "11"' );
741 is( $f1->is_nullable, 0, 'Field cannot be null' );
742 is( $f1->is_primary_key, 1, 'Field is PK' );
743
744 is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
745 is( $f2->data_type, 'geometry', 'Type is "geometry"' );
746 is( $f2->is_nullable, 0, 'Field cannot be null' );
747 is( $f2->is_primary_key, 0, 'Field is not PK' );
748
749 my @indices = $table->get_indices;
750 is( scalar @indices, 1, 'Right number of indices (1)' );
751 my $i1 = shift @indices;
752 is( $i1->name, 'shape_field', 'No name on index' );
753 is( $i1->type, SPATIAL, 'Spatial index' );
754
755 my @constraints = $table->get_constraints;
756 is( scalar @constraints, 1, 'Right number of constraints (1)' );
757 my $c = shift @constraints;
758 is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
759 is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
760}
47666977 761
762{
763 my @data = (
764 q|create table quote (
765 id int(11) NOT NULL auto_increment,
766 PRIMARY KEY (id)
767 ) ENGINE="innodb";|,
768 q|create table quote (
769 id int(11) NOT NULL auto_increment,
770 PRIMARY KEY (id)
771 ) ENGINE='innodb';|,
772 q|create table quote (
773 id int(11) NOT NULL auto_increment,
774 PRIMARY KEY (id)
775 ) ENGINE=innodb;|,
776 );
777 for my $data (@data) {
778 my $tr = SQL::Translator->new;
779
780 my $val = parse($tr, $data);
781 my $schema = $tr->schema;
782 is( $schema->is_valid, 1, 'Schema is valid' );
783 my @tables = $schema->get_tables;
784 is( scalar @tables, 1, 'Right number of tables (1)' );
785 my $table = shift @tables;
786 is( $table->name, 'quote', 'Found "quote" table' );
787
788 my $tableTypeFound = 0;
789 for my $t_option_ref ( $table->options ) {
790 my($key, $value) = %{$t_option_ref};
791 if ( $key eq 'ENGINE' ) {
792 is($value, 'innodb', 'Table has right table engine option' );
793 $tableTypeFound = 1;
794 }
795 }
796
797 fail('Table did not have a type option') unless $tableTypeFound;
798
799 my @fields = $table->get_fields;
800 my $f1 = shift @fields;
801 is( $f1->name, 'id', 'First field name is "id"' );
802 is( $f1->data_type, 'int', 'Type is "int"' );
803 is( $f1->size, 11, 'Size is "11"' );
804 is( $f1->is_nullable, 0, 'Field cannot be null' );
805 is( $f1->is_primary_key, 1, 'Field is PK' );
806 }
807}
808
8b075c9f 809{
810 my $tr = SQL::Translator->new;
811 my $data = q|create table "sessions" (
812 id char(32) not null default '0' primary key,
813 ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
814 user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
97902ff6 815 key using btree (ssn)
8b075c9f 816 );|;
817
818 my $val = parse($tr, $data);
819 my $schema = $tr->schema;
820 is( $schema->is_valid, 1, 'Schema is valid' );
821 my @tables = $schema->get_tables;
822 is( scalar @tables, 1, 'Right number of tables (1)' );
823 my $table = shift @tables;
824 is( $table->name, 'sessions', 'Found "sessions" table' );
825
826 my @fields = $table->get_fields;
827 is( scalar @fields, 3, 'Right number of fields (3)' );
828 my $f1 = shift @fields;
829 my $f2 = shift @fields;
830 my $f3 = shift @fields;
831 is( $f1->name, 'id', 'First field name is "id"' );
832 is( $f1->data_type, 'char', 'Type is "char"' );
833 is( $f1->size, 32, 'Size is "32"' );
834 is( $f1->is_nullable, 0, 'Field cannot be null' );
835 is( $f1->default_value, '0', 'Default value is "0"' );
836 is( $f1->is_primary_key, 1, 'Field is PK' );
837
838 is( $f2->name, 'ssn', 'Second field name is "ssn"' );
839 is( $f2->data_type, 'varchar', 'Type is "varchar"' );
840 is( $f2->size, 12, 'Size is "12"' );
841 is( $f2->is_nullable, 0, 'Field can not be null' );
842 is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
843 is( $f2->is_primary_key, 0, 'Field is not PK' );
844
845 # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
846 # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
847 is( $f3->name, 'user', 'Second field name is "user"' );
848 is( $f3->data_type, 'varchar', 'Type is "varchar"' );
849 is( $f3->size, 20, 'Size is "20"' );
850 is( $f3->is_nullable, 0, 'Field can not be null' );
851 is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
852 is( $f3->is_primary_key, 0, 'Field is not PK' );
853}
DDL Tool (dbsrgits@git.shadowcat.co.uk:SQL-Translator.git)