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