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