t/02mysql-parser.t

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