t/02mysql-parser.t

   1 #!/usr/bin/perl
   2 # vim: set ft=perl:
   3 #
   4
   5 use strict;
   6
   7 use Test::More;
   8 use SQL::Translator;
   9 use SQL::Translator::Schema::Constants;
  10 use SQL::Translator::Utils qw//;
  11 use Test::SQL::Translator qw(maybe_plan);
  12 use FindBin qw/$Bin/;
  13
  14 BEGIN {
  15     maybe_plan(337, "SQL::Translator::Parser::MySQL");
  16     SQL::Translator::Parser::MySQL->import('parse');
  17 }
  18
  19 {
  20     my $tr = SQL::Translator->new;
  21     my $data = q|create table "sessions" (
  22         id char(32) not null default '0' primary key,
  23         a_session text,
  24         ssn varchar(12) unique key,
  25         age int key,
  26         fulltext key `session_fulltext` (a_session)
  27     );|;
  28
  29     my $val = parse($tr, $data);
  30     my $schema = $tr->schema;
  31     is( $schema->is_valid, 1, 'Schema is valid' );
  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' );
  36
  37     my @fields = $table->get_fields;
  38     is( scalar @fields, 4, 'Right number of fields (4)' );
  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' );
  47
  48     is( $f2->name, 'a_session', 'Second field name is "a_session"' );
  49     is( $f2->data_type, 'text', 'Type is "text"' );
  50     is( $f2->size, 65_535, 'Size is "65,535"' );
  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' );
  54
  55     my @indices = $table->get_indices;
  56     is( scalar @indices, 2, 'Right number of indices (2)' );
  57     my $i = pop @indices;
  58     is( $i->type, 'FULLTEXT', 'Found fulltext' );
  59
  60     my @constraints = $table->get_constraints;
  61     is( scalar @constraints, 2, 'Right number of constraints (2)' );
  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"' );
  65     my $c2 = shift @constraints;
  66     is( $c2->type, UNIQUE, 'Constraint is UNIQUE' );
  67     is( join(',', $c2->fields), 'ssn', 'Constraint is on "ssn"' );
  68 }
  69
  70 {
  71     my $tr = SQL::Translator->new;
  72     my $data = parse($tr,
  73         q[
  74             CREATE TABLE `check` (
  75               check_id int(7) unsigned zerofill NOT NULL default '0000000'
  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',
  81               e1 enum('a','b','c') default "c",
  82               name varchar(30) default NULL,
  83               foo_type enum('vk','ck') NOT NULL default 'vk',
  84               date timestamp,
  85               time_stamp2 timestamp,
  86               foo_enabled bit(1) default b'0',
  87               bar_enabled bit(1) default b"1",
  88               KEY (i1),
  89               UNIQUE (date, i1) USING BTREE,
  90               KEY date_idx (date),
  91               KEY name_idx (name(10))
  92             ) TYPE=MyISAM PACK_KEYS=1;
  93         ]
  94     );
  95
  96     my $schema = $tr->schema;
  97     is( $schema->is_valid, 1, 'Schema is valid' );
  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;
 104     is( scalar @fields, 12, 'Right number of fields (12)' );
 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' );
 140
 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"' );
 150
 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"' );
 160
 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' );
 168
 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
 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
 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"' );
 239 }
 240
 241 {
 242     my $tr = SQL::Translator->new;
 243     my $data = parse($tr,
 244         q[
 245             CREATE TABLE orders (
 246               order_id                  integer NOT NULL auto_increment,
 247               member_id                 varchar(255) comment 'fk to member',
 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),
 256               PRIMARY KEY (order_id) USING BTREE,
 257               KEY (status) USING BTREE,
 258               KEY USING BTREE (billing_address_id),
 259               KEY (shipping_address_id),
 260               KEY (member_id, store_id),
 261               FOREIGN KEY (status)              REFERENCES order_status(id) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE,
 262               FOREIGN KEY (billing_address_id)  REFERENCES address(address_id),
 263               FOREIGN KEY (shipping_address_id) REFERENCES address(address_id)
 264             ) TYPE=INNODB COMMENT = 'orders table comment';
 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;
 277         ]
 278     ) or die $tr->error;
 279
 280     my $schema = $tr->schema;
 281     is( $schema->is_valid, 1, 'Schema is valid' );
 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' );
 287     is( $t1->comments, 'orders table comment', 'Table comment OK' );
 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' );
 306     is( $f2->comments, 'fk to member', 'Field comment OK' );
 307     is( $f2->default_value, undef, 'Default value is undefined' );
 308
 309     my $f3 = shift @fields;
 310     is( $f3->name, 'billing_address_id',
 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;
 316     is( $f4->name, 'shipping_address_id',
 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' );
 366     is( join(',', $i2->fields), 'billing_address_id',
 367         'Index is on "billing_address_id"' );
 368
 369     my $i3 = shift @indices;
 370     is( $i3->type, NORMAL, 'Third index is normal' );
 371     is( join(',', $i3->fields), 'shipping_address_id',
 372         'Index is on "shipping_address_id"' );
 373
 374     my $i4 = shift @indices;
 375     is( $i4->type, NORMAL, 'Third index is normal' );
 376     is( join(',', $i4->fields), 'member_id,store_id',
 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' );
 394     is( join(',', $c3->fields), 'billing_address_id',
 395         'Constraint is on "billing_address_id"' );
 396     is( $c3->reference_table, 'address', 'To table "address"' );
 397     is( join(',', $c3->reference_fields), 'address_id',
 398         'To field "address_id"' );
 399
 400     my $c4 = shift @constraints;
 401     is( $c4->type, FOREIGN_KEY, 'Constraint is a FK' );
 402     is( join(',', $c4->fields), 'shipping_address_id',
 403         'Constraint is on "shipping_address_id"' );
 404     is( $c4->reference_table, 'address', 'To table "address"' );
 405     is( join(',', $c4->reference_fields), 'address_id',
 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"' );
 412     is( join(',', map { $_ || '' } $c5->reference_fields), '',
 413         'No reference fields defined' );
 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)' );
 420 }
 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;
 430     my $data = parse($tr,
 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
 453             INSERT absolutely *#! any old $£ ? rubbish, even "quoted; semi-what""sits";
 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
 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 {
 502     my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
 503     my $data = parse($tr,
 504         q[
 505                 DELIMITER ;;
 506             /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;;
 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') */;;
 511
 512                         DELIMITER ;
 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;
 517
 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 */;;
 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;
 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;
 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' );
 632     is_deeply(
 633       $t1f2->default_value,
 634       \'CURRENT_TIMESTAMP',
 635       'Field has right default value'
 636     );
 637     is( $t1f2->extra('on update'), 'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
 638
 639     my @views = $schema->get_views;
 640     is( scalar @views, 3, 'Right number of views (3)' );
 641
 642     my ($view1, $view2, $view3) = @views;
 643     is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
 644     is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
 645     is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
 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
 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' );
 654         like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
 655     my $proc2 = shift @procs;
 656     is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
 657         like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
 658 }
 659
 660 # Tests for collate table option
 661 {
 662     my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
 663     my $data = parse($tr,
 664         q[
 665           CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
 666          ] );
 667
 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!";
 677     my $charset = "Not found!";
 678     for my $t1_option_ref ( $table1->options ) {
 679       my($key, $value) = %{$t1_option_ref};
 680       $collate = $value if $key eq 'COLLATE';
 681       $charset = $value if $key eq 'CHARACTER SET';
 682     }
 683     is($collate, 'latin1_bin', "Collate found");
 684     is($charset, 'latin1', "Character set found");
 685 }
 686
 687 # Test the mysql version parser (probably needs to migrate to t/utils.t)
 688 my $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
 705 for 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
 716 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
 717 ok ($@, 'Exception thrown on invalid version string');
 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 }
 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
 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',
 834         key using btree (ssn)
 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 \'
 865     # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
 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 }
 873
 874 {
 875     # silence PR::D from spewing on STDERR
 876     local ($::RD_ERRORS, $::RD_WARN,$::RD_HINT,$::RD_TRACE);
 877     my $tr = SQL::Translator->new;
 878     my $data = q|create table "sessions" (
 879         id char(32) not null default,
 880         ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
 881         user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
 882         key using btree (ssn)
 883     );|;
 884
 885     my $val= parse($tr,$data);
 886     ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
 887 }
 888
 889 {
 890     # make sure empty string default value still works
 891     my $tr = SQL::Translator->new;
 892     my $data = q|create table "sessions" (
 893         id char(32) not null DEFAULT '',
 894         ssn varchar(12) NOT NULL default "",
 895         key using btree (ssn)
 896     );|;
 897     my $val= parse($tr,$data);
 898
 899     my @fields = $tr->schema->get_table('sessions')->get_fields;
 900     is (scalar @fields, 2, 'Both fields parsed correctly');
 901     for (@fields) {
 902       my $def = $_->default_value;
 903       ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
 904     }
 905 }
 906
 907 {
 908     # test rt70437 and rt71468
 909     my $file = "$Bin/data/mysql/cashmusic_db.sql";
 910     ok (-f $file,"File exists");
 911     my $tr = SQL::Translator->new( parser => 'MySQL');
 912     ok ($tr->translate($file),'File translated');
 913     ok (!$tr->error, 'no error');
 914     ok (my $schema = $tr->schema, 'got schema');
 915 }