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),
  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),
 238               KEY (status),
 239               KEY (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     my ($view3, $view1, $view2) = @views;
 623     is( $view1->name, 'vs_asset', 'Found "vs_asset" view' );
 624     is( $view2->name, 'vs_asset2', 'Found "vs_asset2" view' );
 625     is( $view3->name, 'vs_asset3', 'Found "vs_asset3" view' );
 626         like($view1->sql, qr/ALGORITHM=UNDEFINED/, "Detected algorithm");
 627         like($view1->sql, qr/vs_asset/, "Detected view vs_asset");
 628         unlike($view1->sql, qr/cfgmgmt_mig/, "Did not detect cfgmgmt_mig");
 629
 630     my @procs = $schema->get_procedures;
 631     is( scalar @procs, 2, 'Right number of procedures (2)' );
 632     my $proc1 = shift @procs;
 633     is( $proc1->name, 'ap_from_millitime_nullable', 'Found "ap_from_millitime_nullable" procedure' );
 634         like($proc1->sql, qr/CREATE FUNCTION ap_from_millitime_nullable/, "Detected procedure ap_from_millitime_nullable");
 635     my $proc2 = shift @procs;
 636     is( $proc2->name, 'sp_update_security_acl', 'Found "sp_update_security_acl" procedure' );
 637         like($proc2->sql, qr/CREATE PROCEDURE sp_update_security_acl/, "Detected procedure sp_update_security_acl");
 638 }
 639
 640 # Tests for collate table option
 641 {
 642     my $tr = SQL::Translator->new(parser_args => {mysql_parser_version => 50003});
 643     my $data = parse($tr,
 644         q[
 645           CREATE TABLE test ( id int ) DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
 646          ] );
 647
 648     my $schema = $tr->schema;
 649     is( $schema->is_valid, 1, 'Schema is valid' );
 650     my @tables = $schema->get_tables;
 651     is( scalar @tables, 1, 'Right number of tables (1)' );
 652     my $table1 = shift @tables;
 653     is( $table1->name, 'test', 'Found "test" table' );
 654
 655
 656     my $collate = "Not found!";
 657     my $charset = "Not found!";
 658     for my $t1_option_ref ( $table1->options ) {
 659       my($key, $value) = %{$t1_option_ref};
 660       $collate = $value if $key eq 'COLLATE';
 661       $charset = $value if $key eq 'CHARACTER SET';
 662     }
 663     is($collate, 'latin1_bin', "Collate found");
 664     is($charset, 'latin1', "Character set found");
 665 }
 666
 667 # Test the mysql version parser (probably needs to migrate to t/utils.t)
 668 my $parse_as = {
 669     perl => {
 670         '3.23.2'    => 3.023002,
 671         '4'         => 4.000000,
 672         '50003'     => 5.000003,
 673         '5.01.0'    => 5.001000,
 674         '5.1'       => 5.001000,
 675     },
 676     mysql => {
 677         '3.23.2'    => 32302,
 678         '4'         => 40000,
 679         '50003'     => 50003,
 680         '5.01.0'    => 50100,
 681         '5.1'       => 50100,
 682     },
 683 };
 684
 685 for my $target (keys %$parse_as) {
 686     for my $str (keys %{$parse_as->{$target}}) {
 687         cmp_ok (
 688             SQL::Translator::Utils::parse_mysql_version ($str, $target),
 689             '==',
 690             $parse_as->{$target}{$str},
 691             "'$str' parsed as $target version '$parse_as->{$target}{$str}'",
 692         );
 693     }
 694 }
 695
 696 eval { SQL::Translator::Utils::parse_mysql_version ('bogus5.1') };
 697 ok ($@, 'Exception thrown on invalid version string');
 698
 699 {
 700     my $tr = SQL::Translator->new;
 701     my $data = q|create table merge_example (
 702        id int(11) NOT NULL auto_increment,
 703        shape_field geometry NOT NULL,
 704        PRIMARY KEY (id),
 705        SPATIAL KEY shape_field (shape_field)
 706     ) ENGINE=MRG_MyISAM UNION=(`sometable_0`,`sometable_1`,`sometable_2`);|;
 707
 708     my $val = parse($tr, $data);
 709     my $schema = $tr->schema;
 710     is( $schema->is_valid, 1, 'Schema is valid' );
 711     my @tables = $schema->get_tables;
 712     is( scalar @tables, 1, 'Right number of tables (1)' );
 713     my $table  = shift @tables;
 714     is( $table->name, 'merge_example', 'Found "merge_example" table' );
 715
 716     my $tableTypeFound = 0;
 717     my $unionFound = 0;
 718     for my $t_option_ref ( $table->options ) {
 719       my($key, $value) = %{$t_option_ref};
 720       if ( $key eq 'ENGINE' ) {
 721         is($value, 'MRG_MyISAM', 'Table has right table engine option' );
 722         $tableTypeFound = 1;
 723       } elsif ( $key eq 'UNION' ) {
 724         is_deeply($value, [ 'sometable_0','sometable_1','sometable_2' ],
 725           "UNION option has correct set");
 726         $unionFound = 1;
 727       }
 728     }
 729
 730     fail('Table did not have a type option') unless $tableTypeFound;
 731     fail('Table did not have a union option') unless $unionFound;
 732
 733     my @fields = $table->get_fields;
 734     is( scalar @fields, 2, 'Right number of fields (2)' );
 735     my $f1 = shift @fields;
 736     my $f2 = shift @fields;
 737     is( $f1->name, 'id', 'First field name is "id"' );
 738     is( $f1->data_type, 'int', 'Type is "int"' );
 739     is( $f1->size, 11, 'Size is "11"' );
 740     is( $f1->is_nullable, 0, 'Field cannot be null' );
 741     is( $f1->is_primary_key, 1, 'Field is PK' );
 742
 743     is( $f2->name, 'shape_field', 'Second field name is "shape_field"' );
 744     is( $f2->data_type, 'geometry', 'Type is "geometry"' );
 745     is( $f2->is_nullable, 0, 'Field cannot be null' );
 746     is( $f2->is_primary_key, 0, 'Field is not PK' );
 747
 748     my @indices = $table->get_indices;
 749     is( scalar @indices, 1, 'Right number of indices (1)' );
 750     my $i1 = shift @indices;
 751     is( $i1->name, 'shape_field', 'No name on index' );
 752     is( $i1->type, SPATIAL, 'Spatial index' );
 753
 754     my @constraints = $table->get_constraints;
 755     is( scalar @constraints, 1, 'Right number of constraints (1)' );
 756     my $c = shift @constraints;
 757     is( $c->type, PRIMARY_KEY, 'Constraint is a PK' );
 758     is( join(',', $c->fields), 'id', 'Constraint is on "id"' );
 759 }
 760
 761 {
 762     my @data = (
 763         q|create table quote (
 764             id int(11) NOT NULL auto_increment,
 765             PRIMARY KEY (id)
 766         ) ENGINE="innodb";|,
 767         q|create table quote (
 768             id int(11) NOT NULL auto_increment,
 769             PRIMARY KEY (id)
 770         ) ENGINE='innodb';|,
 771         q|create table quote (
 772             id int(11) NOT NULL auto_increment,
 773             PRIMARY KEY (id)
 774         ) ENGINE=innodb;|,
 775     );
 776     for my $data (@data) {
 777         my $tr = SQL::Translator->new;
 778
 779         my $val = parse($tr, $data);
 780         my $schema = $tr->schema;
 781         is( $schema->is_valid, 1, 'Schema is valid' );
 782         my @tables = $schema->get_tables;
 783         is( scalar @tables, 1, 'Right number of tables (1)' );
 784         my $table  = shift @tables;
 785         is( $table->name, 'quote', 'Found "quote" table' );
 786
 787         my $tableTypeFound = 0;
 788         for my $t_option_ref ( $table->options ) {
 789         my($key, $value) = %{$t_option_ref};
 790         if ( $key eq 'ENGINE' ) {
 791             is($value, 'innodb', 'Table has right table engine option' );
 792             $tableTypeFound = 1;
 793         }
 794         }
 795
 796         fail('Table did not have a type option') unless $tableTypeFound;
 797
 798         my @fields = $table->get_fields;
 799         my $f1 = shift @fields;
 800         is( $f1->name, 'id', 'First field name is "id"' );
 801         is( $f1->data_type, 'int', 'Type is "int"' );
 802         is( $f1->size, 11, 'Size is "11"' );
 803         is( $f1->is_nullable, 0, 'Field cannot be null' );
 804         is( $f1->is_primary_key, 1, 'Field is PK' );
 805     }
 806 }
 807
 808 {
 809     my $tr = SQL::Translator->new;
 810     my $data = q|create table "sessions" (
 811         id char(32) not null default '0' primary key,
 812         ssn varchar(12) NOT NULL default 'test single quotes like in you''re',
 813         user varchar(20) NOT NULL default 'test single quotes escaped like you\'re',
 814         key using btree (ssn)
 815     );|;
 816
 817     my $val = parse($tr, $data);
 818     my $schema = $tr->schema;
 819     is( $schema->is_valid, 1, 'Schema is valid' );
 820     my @tables = $schema->get_tables;
 821     is( scalar @tables, 1, 'Right number of tables (1)' );
 822     my $table  = shift @tables;
 823     is( $table->name, 'sessions', 'Found "sessions" table' );
 824
 825     my @fields = $table->get_fields;
 826     is( scalar @fields, 3, 'Right number of fields (3)' );
 827     my $f1 = shift @fields;
 828     my $f2 = shift @fields;
 829     my $f3 = shift @fields;
 830     is( $f1->name, 'id', 'First field name is "id"' );
 831     is( $f1->data_type, 'char', 'Type is "char"' );
 832     is( $f1->size, 32, 'Size is "32"' );
 833     is( $f1->is_nullable, 0, 'Field cannot be null' );
 834     is( $f1->default_value, '0', 'Default value is "0"' );
 835     is( $f1->is_primary_key, 1, 'Field is PK' );
 836
 837     is( $f2->name, 'ssn', 'Second field name is "ssn"' );
 838     is( $f2->data_type, 'varchar', 'Type is "varchar"' );
 839     is( $f2->size, 12, 'Size is "12"' );
 840     is( $f2->is_nullable, 0, 'Field can not be null' );
 841     is( $f2->default_value, "test single quotes like in you''re", "Single quote in default value is escaped properly" );
 842     is( $f2->is_primary_key, 0, 'Field is not PK' );
 843
 844     # this is more of a sanity test because the original sqlt regex for default looked for an escaped quote represented as \'
 845     # however in mysql 5.x (and probably other previous versions) still actually outputs that as ''
 846     is( $f3->name, 'user', 'Second field name is "user"' );
 847     is( $f3->data_type, 'varchar', 'Type is "varchar"' );
 848     is( $f3->size, 20, 'Size is "20"' );
 849     is( $f3->is_nullable, 0, 'Field can not be null' );
 850     is( $f3->default_value, "test single quotes escaped like you\\'re", "Single quote in default value is escaped properly" );
 851     is( $f3->is_primary_key, 0, 'Field is not PK' );
 852 }