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