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