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','c''k') 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, 3, '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,c\'k', 'List is "vk,c\'k"' );
 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 => 50013});
 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             /*!50014 DEFINER=`BOGUS` */
 531             /*! VIEW `vs_asset` AS
 532                 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
 533                 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
 534                 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
 535                 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
 536                 `a`.`foreign_asset_id` AS `foreign_asset_id`,
 537                 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
 538                 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
 539                 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
 540                 `m`.`user_id` AS `user_access`
 541                 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
 542             DELIMITER ;;
 543             /*!50001 CREATE */
 544             /*! VIEW `vs_asset2` AS
 545                 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
 546                 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
 547                 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
 548                 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
 549                 `a`.`foreign_asset_id` AS `foreign_asset_id`,
 550                 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
 551                 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
 552                 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
 553                 `m`.`user_id` AS `user_access`
 554                 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
 555             DELIMITER ;;
 556             /*!50001 CREATE OR REPLACE */
 557             /*! VIEW `vs_asset3` AS
 558                 select `a`.`asset_id` AS `asset_id`,`a`.`fq_name` AS `fq_name`,
 559                 `cfgmgmt_mig`.`ap_extract_folder`(`a`.`fq_name`) AS `folder_name`,
 560                 `cfgmgmt_mig`.`ap_extract_asset`(`a`.`fq_name`) AS `asset_name`,
 561                 `a`.`annotation` AS `annotation`,`a`.`asset_type` AS `asset_type`,
 562                 `a`.`foreign_asset_id` AS `foreign_asset_id`,
 563                 `a`.`foreign_asset_id2` AS `foreign_asset_id2`,`a`.`dateCreated` AS `date_created`,
 564                 `a`.`dateModified` AS `date_modified`,`a`.`container_id` AS `container_id`,
 565                 `a`.`creator_id` AS `creator_id`,`a`.`modifier_id` AS `modifier_id`,
 566                 `m`.`user_id` AS `user_access`
 567                 from (`asset` `a` join `M_ACCESS_CONTROL` `m` on((`a`.`acl_id` = `m`.`acl_id`))) */;
 568             DELIMITER ;;
 569             /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 FUNCTION `ap_from_millitime_nullable`( millis_since_1970 BIGINT ) RETURNS timestamp
 570                 DETERMINISTIC
 571                 BEGIN
 572                     DECLARE rval TIMESTAMP;
 573                     IF ( millis_since_1970 = 0 )
 574                     THEN
 575                         SET rval = NULL;
 576                     ELSE
 577                         SET rval = FROM_UNIXTIME( millis_since_1970 / 1000 );
 578                     END IF;
 579                     RETURN rval;
 580                 END */;;
 581             /*!50003 CREATE*/ /*!50020 DEFINER=`cmdomain`@`localhost`*/ /*!50003 PROCEDURE `sp_update_security_acl`(IN t_acl_id INTEGER)
 582                 BEGIN
 583                     DECLARE hasMoreRows BOOL DEFAULT TRUE;
 584                     DECLARE t_group_id INT;
 585                     DECLARE t_user_id INT ;
 586                     DECLARE t_user_name VARCHAR (512) ;
 587                     DECLARE t_message VARCHAR (512) ;
 588
 589                     DROP TABLE IF EXISTS group_acl;
 590                     DROP TABLE IF EXISTS user_group;
 591                     DELETE FROM M_ACCESS_CONTROL WHERE acl_id = t_acl_id;
 592
 593                     CREATE TEMPORARY TABLE group_acl SELECT DISTINCT p.id group_id, d.acl_id acl_id
 594                         FROM  asset d, acl_entry e, alterpoint_principal p
 595                         WHERE d.acl_id = e.acl
 596                         AND p.id = e.principal AND d.acl_id = t_acl_id;
 597
 598                     CREATE TEMPORARY TABLE user_group  SELECT a.id user_id, a.name user_name, c.id group_id
 599                         FROM alterpoint_principal a, groups_for_user b, alterpoint_principal c
 600                         WHERE a.id = b.user_ref AND b.elt = c.id;
 601
 602                     INSERT INTO M_ACCESS_CONTROL SELECT DISTINCT group_acl.group_id, group_acl.acl_id, user_group.user_id, user_group.user_name
 603                         FROM group_acl, user_group
 604                         WHERE group_acl.group_id = user_group.group_id ;
 605                 END */;;
 606         ]
 607     ) or die $tr->error;
 608
 609     my $schema = $tr->schema;
 610     is( $schema->is_valid, 1, 'Schema is valid' );
 611     my @tables = $schema->get_tables;
 612     is( scalar @tables, 1, 'Right number of tables (1)' );
 613     my $table1 = shift @tables;
 614     is( $table1->name, 'one', 'Found "one" table' );
 615
 616     my @fields = $table1->get_fields;
 617     is(scalar @fields, 3, 'Right number of fields (3) on table one');
 618     my $tableTypeFound = 0;
 619     my $charsetFound = 0;
 620     for my $t1_option_ref ( $table1->options ) {
 621         my($key, $value) = %{$t1_option_ref};
 622         if ( $key eq 'TYPE' ) {
 623             is($value, 'INNODB', 'Table has right table type option' );
 624             $tableTypeFound = 1;
 625         } elsif ( $key eq 'CHARACTER SET' ) {
 626             is($value, 'latin1', 'Table has right character set option' );
 627             $charsetFound = 1;
 628         }
 629     }
 630     fail('Table did not have a type option') unless $tableTypeFound;
 631     fail('Table did not have a character set option') unless $charsetFound;
 632
 633     my $t1f1 = shift @fields;
 634     is( $t1f1->data_type, 'varchar', 'Field is a varchar' );
 635     is( $t1f1->size, 255, 'Field is right size' );
 636     is( $t1f1->extra('character set'), 'latin1', 'Field has right character set qualifier' );
 637     is( $t1f1->extra('collate'), 'latin1_bin', 'Field has right collate qualifier' );
 638     is( $t1f1->default_value, 'NULL', 'Field has right default value' );
 639
 640     my $t1f2 = shift @fields;
 641     is( $t1f2->data_type, 'timestamp', 'Field is a timestamp' );
 642     ok( !$t1f2->is_nullable, 'Field is not nullable' );
 643     is_deeply(
 644       $t1f2->default_value,
 645       \'CURRENT_TIMESTAMP',
 646       'Field has right default value'
 647     );
 648     is_deeply( $t1f2->extra('on update'), \'CURRENT_TIMESTAMP', 'Field has right on update qualifier' );
 649
 650     my $t1f3 = shift @fields;
 651     is( $t1f3->data_type, 'datetime', 'Field is a datetime' );
 652     ok( !$t1f3->is_nullable, 'Field is not nullable' );
 653     is_deeply(
 654       $t1f3->default_value,
 655       \'CURRENT_TIMESTAMP',
 656       'Field has right default value'
 657     );
 658
 659     my @views = $schema->get_views;
 660     is( scalar @views, 3, 'Right number of views (3)' );
 661
 662     my ($view1, $view2, $view3) = @views;
 663     is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
 664     is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
 665     is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
 666     like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
 667
 668     # KYC - commenting this out as I don't understand why this string
 669     # should /not/ be detected when it is in the SQL - 2/28/12
 670     # like($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
 671
 672     is( join(',', $view1->fields),
 673         join(',', qw[ asset_id fq_name folder_name asset_name annotation
 674             asset_type foreign_asset_id foreign_asset_id2 date_created
 675             date_modified container_id creator_id modifier_id user_access
 676         ] ),
 677         'First view has correct fields'
 678     );
 679
 680     my @options = $view1->options;
 681
 682     is_deeply(
 683       \@options,
 684       [
 685         'ALGORITHM=UNDEFINED',
 686         'DEFINER=`cmdomain`@`localhost`',
 687         'SQL SECURITY DEFINER',
 688       ],
 689       'Only version 50013 options parsed',
 690     );
 691
 692     my @procs = $schema->get_procedures;
 693     is( scalar @procs, 2, 'Right number of procedures (2)' );
 694     my $proc1 = shift @procs;
 695     is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
 696     like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
 697     my $proc2 = shift @procs;
 698     is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
 699     like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
 700 }
 701
 702 # Tests for collate table option
 703 {
 704     my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
 705     my $data = parse($tr,
 706         q[
 707           CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
 708          ] );
 709
 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 $table1 = shift @tables;
 715     is( $table1->name, 'test', 'Found "test" table' );
 716
 717
 718     my $collate = "Not found!";
 719     my $charset = "Not found!";
 720     for my $t1_option_ref ( $table1->options ) {
 721       my($key, $value) = %{$t1_option_ref};
 722       $collate = $value if $key eq 'COLLATE';
 723       $charset = $value if $key eq 'CHARACTER SET';
 724     }
 725     is($collate, 'latin1_bin', "Collate found");
 726     is($charset, 'latin1', "Character set found");
 727 }
 728
 729 # Test the mysql version parser (probably needs to migrate to t/utils.t)
 730 my $parse_as = {
 731     perl => {
 732         '3.23.2'    => 3.023002,
 733         '4'         => 4.000000,
 734         '50003'     => 5.000003,
 735         '5.01.0'    => 5.001000,
 736         '5.1'       => 5.001000,
 737     },
 738     mysql => {
 739         '3.23.2'    => 32302,
 740         '4'         => 40000,
 741         '50003'     => 50003,
 742         '5.01.0'    => 50100,
 743         '5.1'       => 50100,
 744     },
 745 };
 746
 747 for my $target (keys %$parse_as) {
 748     for my $str (keys %{$parse_as->{$target}}) {
 749         cmp_ok (
 750             SQL::Translator::Utils::parse_mysql_version ($str, $target),
 751             '==',
 752             $parse_as->{$target}{$str},
 753             "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
 754         );
 755     }
 756 }
 757
 758 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
 759 ok ($@, 'Exception thrown on invalid version string');
 760
 761 {
 762     my $tr = SQL::Translator->new;
 763     my $data = q|create table merge_example (
 764        id int(11) NOT NULL auto_increment,
 765        shape_field geometry NOT NULL,
 766        PRIMARY KEY (id),
 767        SPATIAL KEY shape_field (shape_field)
 768     ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
 769
 770     my $val = parse($tr, $data);
 771     my $schema = $tr->schema;
 772     is( $schema->is_valid, 1, 'Schema is valid' );
 773     my @tables = $schema->get_tables;
 774     is( scalar @tables, 1, 'Right number of tables (1)' );
 775     my $table  = shift @tables;
 776     is( $table->name, 'merge_example', 'Found "merge_example" table' );
 777
 778     my $tableTypeFound = 0;
 779     my $unionFound = 0;
 780     for my $t_option_ref ( $table->options ) {
 781       my($key, $value) = %{$t_option_ref};
 782       if ( $key eq 'ENGINE' ) {
 783         is($value, 'MRG_MyISAM', 'Table has right table engine option' );
 784         $tableTypeFound = 1;
 785       } elsif ( $key eq 'UNION' ) {
 786         is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
 787           "UNION option has correct set");
 788         $unionFound = 1;
 789       }
 790     }
 791
 792     fail('Table did not have a type option') unless $tableTypeFound;
 793     fail('Table did not have a union option') unless $unionFound;
 794
 795     my @fields = $table->get_fields;
 796     is( scalar @fields, 2, 'Right number of fields (2)' );
 797     my $f1 = shift @fields;
 798     my $f2 = shift @fields;
 799     is( $f1->name, 'id', 'First field name is "id"' );
 800     is( $f1->data_type, 'int', 'Type is "int"' );
 801     is( $f1->size, 11, 'Size is "11"' );
 802     is( $f1->is_nullable, 0, 'Field cannot be null' );
 803     is( $f1->is_primary_key, 1, 'Field is PK' );
 804
 805     is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
 806     is( $f2->data_type, 'geometry', 'Type is "geometry"' );
 807     is( $f2->is_nullable, 0, 'Field cannot be null' );
 808     is( $f2->is_primary_key, 0, 'Field is not PK' );
 809
 810     my @indices = $table->get_indices;
 811     is( scalar @indices, 1, 'Right number of indices (1)' );
 812     my $i1 = shift @indices;
 813     is( $i1->name, 'shape_field', 'No name on index' );
 814     is( $i1->type, SPATIAL, 'Spatial index' );
 815
 816     my @constraints = $table->get_constraints;
 817     is( scalar @constraints, 1, 'Right number of constraints (1)' );
 818     my $c = shift @constraints;
 819     is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
 820     is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
 821 }
 822
 823 {
 824     my @data = (
 825         q|create table quote (
 826             id int(11) NOT NULL auto_increment,
 827             PRIMARY KEY (id)
 828         ) ENGINE="innodb";|,
 829         q|create table quote (
 830             id int(11) NOT NULL auto_increment,
 831             PRIMARY KEY (id)
 832         ) ENGINE='innodb';|,
 833         q|create table quote (
 834             id int(11) NOT NULL auto_increment,
 835             PRIMARY KEY (id)
 836         ) ENGINE=innodb;|,
 837     );
 838     for my $data (@data) {
 839         my $tr = SQL::Translator->new;
 840
 841         my $val = parse($tr, $data);
 842         my $schema = $tr->schema;
 843         is( $schema->is_valid, 1, 'Schema is valid' );
 844         my @tables = $schema->get_tables;
 845         is( scalar @tables, 1, 'Right number of tables (1)' );
 846         my $table  = shift @tables;
 847         is( $table->name, 'quote', 'Found "quote" table' );
 848
 849         my $tableTypeFound = 0;
 850         for my $t_option_ref ( $table->options ) {
 851         my($key, $value) = %{$t_option_ref};
 852         if ( $key eq 'ENGINE' ) {
 853             is($value, 'innodb', 'Table has right table engine option' );
 854             $tableTypeFound = 1;
 855         }
 856         }
 857
 858         fail('Table did not have a type option') unless $tableTypeFound;
 859
 860         my @fields = $table->get_fields;
 861         my $f1 = shift @fields;
 862         is( $f1->name, 'id', 'First field name is "id"' );
 863         is( $f1->data_type, 'int', 'Type is "int"' );
 864         is( $f1->size, 11, 'Size is "11"' );
 865         is( $f1->is_nullable, 0, 'Field cannot be null' );
 866         is( $f1->is_primary_key, 1, 'Field is PK' );
 867     }
 868 }
 869
 870 {
 871     my $tr = SQL::Translator->new;
 872     my $data = q|create table "sessions" (
 873         id char(32) not null default '0' primary key,
 874         ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
 875         user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
 876         key using btree (ssn)
 877     );|;
 878
 879     my $val = parse($tr, $data);
 880     my $schema = $tr->schema;
 881     is( $schema->is_valid, 1, 'Schema is valid' );
 882     my @tables = $schema->get_tables;
 883     is( scalar @tables, 1, 'Right number of tables (1)' );
 884     my $table  = shift @tables;
 885     is( $table->name, 'sessions', 'Found "sessions" table' );
 886
 887     my @fields = $table->get_fields;
 888     is( scalar @fields, 3, 'Right number of fields (3)' );
 889     my $f1 = shift @fields;
 890     my $f2 = shift @fields;
 891     my $f3 = shift @fields;
 892     is( $f1->name, 'id', 'First field name is "id"' );
 893     is( $f1->data_type, 'char', 'Type is "char"' );
 894     is( $f1->size, 32, 'Size is "32"' );
 895     is( $f1->is_nullable, 0, 'Field cannot be null' );
 896     is( $f1->default_value, '0', 'Default value is "0"' );
 897     is( $f1->is_primary_key, 1, 'Field is PK' );
 898
 899     is( $f2->name, 'ssn', 'Second field name is "ssn"' );
 900     is( $f2->data_type, 'varchar', 'Type is "varchar"' );
 901     is( $f2->size, 12, 'Size is "12"' );
 902     is( $f2->is_nullable, 0, 'Field can not be null' );
 903     is( $f2->default_value, "test single quotes like in you're", "Single quote in default value is unescaped properly" );
 904     is( $f2->is_primary_key, 0, 'Field is not PK' );
 905
 906     # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
 907     # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
 908     is( $f3->name, 'user', 'Second field name is "user"' );
 909     is( $f3->data_type, 'varchar', 'Type is "varchar"' );
 910     is( $f3->size, 20, 'Size is "20"' );
 911     is( $f3->is_nullable, 0, 'Field can not be null' );
 912     is( $f3->default_value, "test single quotes escaped like you're", "Single quote in default value is unescaped properly" );
 913     is( $f3->is_primary_key, 0, 'Field is not PK' );
 914 }
 915
 916 {
 917     # silence PR::D from spewing on STDERR
 918     local $::RD_ERRORS = 0;
 919     local $::RD_WARN = 0;
 920     local $::RD_HINT = 0;
 921     my $tr = SQL::Translator->new;
 922     my $data = q|create table "sessions" (
 923         id char(32) not null default,
 924         ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
 925         user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
 926         key using btree (ssn)
 927     );|;
 928
 929     my $val= parse($tr,$data);
 930     ok ($tr->error =~ /Parse failed\./, 'Parse failed error without default value');
 931 }
 932
 933 {
 934     # make sure empty string default value still works
 935     my $tr = SQL::Translator->new;
 936     my $data = q|create table "sessions" (
 937         id char(32) not null DEFAULT '',
 938         ssn varchar(12) NOT NULL default "",
 939         key using btree (ssn)
 940     );|;
 941     my $val= parse($tr,$data);
 942
 943     my @fields = $tr->schema->get_table('sessions')->get_fields;
 944     is (scalar @fields, 2, 'Both fields parsed correctly');
 945     for (@fields) {
 946       my $def = $_->default_value;
 947       ok( (defined $def and $def eq ''), "Defaults on field $_ correct" );
 948     }
 949 }
 950
 951 {
 952     # test rt70437 and rt71468
 953     my $file = "$Bin/data/mysql/cashmusic_db.sql";
 954     ok (-f $file,"File exists");
 955     my $tr = SQL::Translator->new( parser => 'MySQL');
 956     ok ($tr->translate($file),'File translated');
 957     ok (!$tr->error, 'no error');
 958     ok (my $schema = $tr->schema, 'got schema');
 959 }