t/47postgres-producer.t

   1 #!/usr/bin/perl
   2
   3 use strict;
   4 use warnings;
   5
   6 use Test::More;
   7 use Test::Exception;
   8 use Test::SQL::Translator qw(maybe_plan);
   9 use SQL::Translator::Schema::Constants;
  10 use Data::Dumper;
  11 use FindBin qw/$Bin/;
  12
  13 # Testing 1,2,3,4...
  14 #=============================================================================
  15
  16 BEGIN {
  17     maybe_plan(48,
  18         'SQL::Translator::Producer::PostgreSQL',
  19         'Test::Differences',
  20     )
  21 }
  22 use Test::Differences;
  23 use SQL::Translator;
  24
  25 my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
  26
  27 my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
  28
  29 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
  30                                                   table => $table,
  31                                                   data_type => 'VARCHAR',
  32                                                   size => 10,
  33                                                   default_value => undef,
  34                                                   is_auto_increment => 0,
  35                                                   is_nullable => 1,
  36                                                   is_foreign_key => 0,
  37                                                   is_unique => 0 );
  38
  39 my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
  40
  41 is($field1_sql, 'myfield character varying(10)', 'Create field works');
  42
  43 my $field_array = SQL::Translator::Schema::Field->new( name => 'myfield',
  44                                                   table => $table,
  45                                                   data_type => 'character varying[]',
  46                                                   size => 10,
  47                                                   default_value => undef,
  48                                                   is_auto_increment => 0,
  49                                                   is_nullable => 1,
  50                                                   is_foreign_key => 0,
  51                                                   is_unique => 0 );
  52
  53 my $field_array_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_array);
  54
  55 is($field_array_sql, 'myfield character varying(10)[]', 'Create field works');
  56
  57 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
  58                                                   table => $table,
  59                                                   data_type => 'VARCHAR',
  60                                                   size      => 25,
  61                                                   default_value => undef,
  62                                                   is_auto_increment => 0,
  63                                                   is_nullable => 0,
  64                                                   is_foreign_key => 0,
  65                                                   is_unique => 0 );
  66
  67 my $pk_constraint = SQL::Translator::Schema::Constraint->new(
  68     table => $table,
  69     name   => 'foo',
  70     fields => [qw(myfield)],
  71     type   => 'PRIMARY_KEY',
  72 );
  73
  74 my  ($pk_constraint_def_ref, $pk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($pk_constraint);
  75 ok(@{$pk_constraint_def_ref} == 1 && @{$pk_constraint_fk_ref} == 0,  'precheck of create_Primary Key constraint');
  76 is($pk_constraint_def_ref->[0], 'CONSTRAINT foo PRIMARY KEY (myfield)', 'Create Primary Key Constraint works');
  77
  78 my $alter_pk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($pk_constraint);
  79 is($alter_pk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT foo', 'Alter drop Primary Key constraint works');
  80
  81 my $table2 = SQL::Translator::Schema::Table->new( name => 'mytable2');
  82
  83 my $field1_2 = SQL::Translator::Schema::Field->new( name => 'myfield_2',
  84                                                   table => $table,
  85                                                   data_type => 'VARCHAR',
  86                                                   size => 10,
  87                                                   default_value => undef,
  88                                                   is_auto_increment => 0,
  89                                                   is_nullable => 1,
  90                                                   is_foreign_key => 0,
  91                                                   is_unique => 0 );
  92
  93 my $fk_constraint = SQL::Translator::Schema::Constraint->new(
  94     table => $table,
  95     name   => 'foo',
  96     fields => [qw(myfield)],
  97     type   => 'FOREIGN_KEY',
  98     reference_table => $table2,
  99     reference_fields => [qw(myfield_2)],
 100 );
 101
 102 my  ($fk_constraint_def_ref, $fk_constraint_fk_ref ) = SQL::Translator::Producer::PostgreSQL::create_constraint($fk_constraint);
 103
 104 ok(@{$fk_constraint_def_ref} == 0 && @{$fk_constraint_fk_ref} == 1,  'precheck of create_Foreign Key constraint');
 105 is($fk_constraint_fk_ref->[0], 'ALTER TABLE mytable ADD FOREIGN KEY (myfield)
 106   REFERENCES mytable2 (myfield_2) DEFERRABLE', 'Create Foreign Key Constraint works');
 107
 108 my $alter_fk_constraint = SQL::Translator::Producer::PostgreSQL::alter_drop_constraint($fk_constraint);
 109 is($alter_fk_constraint, 'ALTER TABLE mytable DROP CONSTRAINT mytable_myfield_fkey', 'Alter drop Foreign Key constraint works');
 110
 111 my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
 112                                                                 $field2);
 113 is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
 114 ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)],
 115  'Alter field works');
 116
 117 my $field1_complex = SQL::Translator::Schema::Field->new(
 118     name => 'my_complex_field',
 119     table => $table,
 120     data_type => 'VARCHAR',
 121     size => 10,
 122     default_value => undef,
 123                                                   is_auto_increment => 0,
 124     is_nullable => 1,
 125     is_foreign_key => 0,
 126     is_unique => 0
 127 );
 128
 129 my $field2_complex = SQL::Translator::Schema::Field->new(
 130     name      => 'my_altered_field',
 131     table => $table,
 132     data_type => 'VARCHAR',
 133     size      => 60,
 134     default_value => 'whatever',
 135     is_auto_increment => 0,
 136     is_nullable => 1,
 137     is_foreign_key => 0,
 138     is_unique => 0
 139 );
 140
 141 my $alter_field_complex = SQL::Translator::Producer::PostgreSQL::alter_field($field1_complex, $field2_complex);
 142 is(
 143     $alter_field_complex,
 144     q{ALTER TABLE mytable RENAME COLUMN my_complex_field TO my_altered_field;
 145 ALTER TABLE mytable ALTER COLUMN my_altered_field TYPE character varying(60);
 146 ALTER TABLE mytable ALTER COLUMN my_altered_field SET DEFAULT 'whatever'},
 147     'Complex Alter field works'
 148 );
 149
 150 $field1->name('field3');
 151 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
 152
 153 is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works');
 154
 155 my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2);
 156 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
 157
 158 my $field3 = SQL::Translator::Schema::Field->new( name      => 'time_field',
 159                                                   table => $table,
 160                                                   data_type => 'TIME',
 161                                                   default_value => undef,
 162                                                   is_auto_increment => 0,
 163                                                   is_nullable => 0,
 164                                                   is_foreign_key => 0,
 165                                                   is_unique => 0 );
 166
 167 my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3);
 168
 169 is($field3_sql, 'time_field time NOT NULL', 'Create time field works');
 170
 171 my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new(
 172     name      => 'datetime_with_TZ',
 173     table     => $table,
 174     data_type => 'timestamp with time zone',
 175     size      => 7,
 176 );
 177
 178 my $field3_datetime_with_TZ_sql =
 179     SQL::Translator::Producer::PostgreSQL::create_field(
 180         $field3_datetime_with_TZ
 181     );
 182
 183 is(
 184     $field3_datetime_with_TZ_sql,
 185     'datetime_with_TZ timestamp(6) with time zone',
 186     'Create time field with time zone and size, works'
 187 );
 188
 189 my $field3_time_without_TZ = SQL::Translator::Schema::Field->new(
 190     name      => 'time_without_TZ',
 191     table     => $table,
 192     data_type => 'time without time zone',
 193     size      => 2,
 194 );
 195
 196 my $field3_time_without_TZ_sql
 197     = SQL::Translator::Producer::PostgreSQL::create_field(
 198         $field3_time_without_TZ
 199     );
 200
 201 is(
 202     $field3_time_without_TZ_sql,
 203     'time_without_TZ time(2) without time zone',
 204     'Create time field without time zone but with size, works'
 205 );
 206
 207 my $field_num = SQL::Translator::Schema::Field->new( name => 'num',
 208                                                   table => $table,
 209                                                   data_type => 'numeric',
 210                                                   size => [10,2],
 211                                                   );
 212 my $fieldnum_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_num);
 213
 214 is($fieldnum_sql, 'num numeric(10,2)', 'Create numeric field works');
 215
 216
 217 my $field4 = SQL::Translator::Schema::Field->new( name      => 'bytea_field',
 218                                                   table => $table,
 219                                                   data_type => 'bytea',
 220                                                   size => '16777215',
 221                                                   default_value => undef,
 222                                                   is_auto_increment => 0,
 223                                                   is_nullable => 0,
 224                                                   is_foreign_key => 0,
 225                                                   is_unique => 0 );
 226
 227 my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4);
 228
 229 is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
 230
 231 my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field',
 232                                                    table => $table,
 233                                                    data_type => 'enum',
 234                                                    extra => { list => [ 'Foo', 'Bar' ] },
 235                                                    is_auto_increment => 0,
 236                                                    is_nullable => 0,
 237                                                    is_foreign_key => 0,
 238                                                    is_unique => 0 );
 239
 240 my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
 241
 242 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
 243
 244
 245
 246
 247 my $field6 = SQL::Translator::Schema::Field->new(
 248                                                   name      => 'character',
 249                                                   table => $table,
 250                                                   data_type => 'character',
 251                                                   size => '123',
 252                                                   default_value => 'foobar',
 253                                                     is_auto_increment => 0,
 254                                                     is_nullable => 0,
 255                                                     is_foreign_key => 0,
 256                                                     is_unique => 0);
 257
 258 my $field7 = SQL::Translator::Schema::Field->new(
 259                                 name      => 'character',
 260                                 table => $table,
 261                                 data_type => 'character',
 262                                 size => '123',
 263                                 default_value => undef,
 264                                   is_auto_increment => 0,
 265                                   is_nullable => 0,
 266                                   is_foreign_key => 0,
 267                                   is_unique => 0);
 268
 269 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 270                                                                 $field7);
 271
 272 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT');
 273
 274 $field7->default_value(q(foo'bar'));
 275
 276 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 277                                                                 $field7);
 278
 279 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping');
 280
 281 $field7->default_value(\q(foobar));
 282
 283 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 284                                                                 $field7);
 285
 286 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref');
 287
 288 $field7->is_nullable(1);
 289 $field7->default_value(q(foobar));
 290
 291 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 292                                                                 $field7);
 293
 294 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL');
 295
 296 my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field',
 297                                                    table => $table,
 298                                                    data_type => 'timestamp with time zone',
 299                                                    size => 6,
 300                                                    is_auto_increment => 0,
 301                                                    is_nullable => 0,
 302                                                    is_foreign_key => 0,
 303                                                    is_unique => 0 );
 304
 305 my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 });
 306
 307 is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision');
 308
 309 my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field',
 310                                                    table => $table,
 311                                                    data_type => 'time with time zone',
 312                                                    size => 6,
 313                                                    is_auto_increment => 0,
 314                                                    is_nullable => 0,
 315                                                    is_foreign_key => 0,
 316                                                    is_unique => 0 );
 317
 318 my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 });
 319
 320 is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision');
 321
 322 my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field',
 323                                                    table => $table,
 324                                                    data_type => 'interval',
 325                                                    size => 6,
 326                                                    is_auto_increment => 0,
 327                                                    is_nullable => 0,
 328                                                    is_foreign_key => 0,
 329                                                    is_unique => 0 );
 330
 331 my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 });
 332
 333 is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision');
 334
 335
 336 my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field',
 337                                                    table => $table,
 338                                                    data_type => 'time without time zone',
 339                                                    size => 6,
 340                                                    is_auto_increment => 0,
 341                                                    is_nullable => 0,
 342                                                    is_foreign_key => 0,
 343                                                    is_unique => 0 );
 344
 345 my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 });
 346
 347 is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision');
 348
 349
 350
 351 my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field',
 352                                                    table => $table,
 353                                                    data_type => 'timestamp',
 354                                                    is_auto_increment => 0,
 355                                                    is_nullable => 0,
 356                                                    is_foreign_key => 0,
 357                                                    is_unique => 0 );
 358
 359 my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 });
 360
 361 is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision');
 362
 363 my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name',
 364                                                    table => $table,
 365                                                    data_type => 'enum',
 366                                                    extra => { list => [ 'Foo', 'Bar' ],
 367                                                               custom_type_name => 'real_enum_type' },
 368                                                    is_auto_increment => 0,
 369                                                    is_nullable => 0,
 370                                                    is_foreign_key => 0,
 371                                                    is_unique => 0 );
 372
 373 my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field($field13,{ postgres_version => 8.3 });
 374
 375 is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works');
 376
 377
 378 {
 379     # let's test default values! -- rjbs, 2008-09-30
 380     my %field = (
 381         table => $table,
 382         data_type => 'VARCHAR',
 383         size => 10,
 384         is_auto_increment => 0,
 385         is_nullable => 1,
 386         is_foreign_key => 0,
 387         is_unique => 0,
 388     );
 389
 390     {
 391         my $simple_default = SQL::Translator::Schema::Field->new(
 392             %field,
 393             name => 'str_default',
 394             default_value => 'foo',
 395         );
 396
 397         is(
 398             $PRODUCER->($simple_default),
 399             q{str_default character varying(10) DEFAULT 'foo'},
 400             'default str',
 401         );
 402     }
 403
 404     {
 405         my $null_default = SQL::Translator::Schema::Field->new(
 406             %field,
 407             name => 'null_default',
 408             default_value => \'NULL',
 409         );
 410
 411         is(
 412             $PRODUCER->($null_default),
 413             q{null_default character varying(10) DEFAULT NULL},
 414             'default null',
 415         );
 416     }
 417
 418     {
 419         my $null_default = SQL::Translator::Schema::Field->new(
 420             %field,
 421             name => 'null_default_2',
 422             default_value => 'NULL', # XXX: this should go away
 423         );
 424
 425         is(
 426             $PRODUCER->($null_default),
 427             q{null_default_2 character varying(10) DEFAULT NULL},
 428             'default null from special cased string',
 429         );
 430     }
 431
 432     {
 433         my $func_default = SQL::Translator::Schema::Field->new(
 434             %field,
 435             name => 'func_default',
 436             default_value => \'func(funky)',
 437         );
 438
 439         is(
 440             $PRODUCER->($func_default),
 441             q{func_default character varying(10) DEFAULT func(funky)},
 442             'unquoted default from scalar ref',
 443         );
 444     }
 445 }
 446
 447
 448 my $view1 = SQL::Translator::Schema::View->new(
 449     name   => 'view_foo',
 450     fields => [qw/id name/],
 451     sql    => 'SELECT id, name FROM thing',
 452 );
 453 my $create_opts = { add_replace_view => 1, no_comments => 1 };
 454 my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts);
 455
 456 my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS
 457     SELECT id, name FROM thing
 458 ";
 459 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
 460
 461 my $view2 = SQL::Translator::Schema::View->new(
 462     name   => 'view_foo2',
 463     sql    => 'SELECT id, name FROM thing',
 464     extra  => {
 465       'temporary'    => '1',
 466       'check_option' => 'cascaded',
 467     },
 468 );
 469 my $create2_opts = { add_replace_view => 1, no_comments => 1 };
 470 my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);
 471
 472 my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS
 473     SELECT id, name FROM thing
 474  WITH CASCADED CHECK OPTION";
 475 is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');
 476
 477 {
 478     my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => [qw( foo  bar )] );
 479     my $quote = { quote_table_names => '"', quote_field_names => '"' };
 480
 481     {
 482         my $index = $table->add_index(name => 'myindex', fields => ['foo']);
 483         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 484         is($def, "CREATE INDEX myindex on foobar (foo)", 'index created');
 485         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 486         is($def, 'CREATE INDEX "myindex" on "foobar" ("foo")', 'index created w/ quotes');
 487     }
 488
 489     {
 490         my $index = $table->add_index(name => 'myindex', fields => ['lower(foo)']);
 491         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 492         is($def, "CREATE INDEX myindex on foobar (lower(foo))", 'index created');
 493         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 494         is($def, 'CREATE INDEX "myindex" on "foobar" (lower(foo))', 'index created w/ quotes');
 495     }
 496
 497     {
 498         my $index = $table->add_index(name => 'myindex', fields => ['bar', 'lower(foo)']);
 499         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 500         is($def, "CREATE INDEX myindex on foobar (bar, lower(foo))", 'index created');
 501         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 502         is($def, 'CREATE INDEX "myindex" on "foobar" ("bar", lower(foo))', 'index created w/ quotes');
 503     }
 504
 505     {
 506         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['foo']);
 507         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 508         is($def->[0], 'CONSTRAINT constr UNIQUE (foo)', 'constraint created');
 509         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 510         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("foo")', 'constraint created w/ quotes');
 511     }
 512
 513     {
 514         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['lower(foo)']);
 515         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 516         is($def->[0], 'CONSTRAINT constr UNIQUE (lower(foo))', 'constraint created');
 517         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 518         is($def->[0], 'CONSTRAINT "constr" UNIQUE (lower(foo))', 'constraint created w/ quotes');
 519     }
 520
 521     {
 522         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['bar', 'lower(foo)']);
 523         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 524         is($def->[0], 'CONSTRAINT constr UNIQUE (bar, lower(foo))', 'constraint created');
 525         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 526         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("bar", lower(foo))', 'constraint created w/ quotes');
 527     }
 528 }
 529
 530 my $drop_view_opts1 = { add_drop_view => 1, no_comments => 1, postgres_version => 8.001 };
 531 my $drop_view_8_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts1);
 532
 533 my $drop_view_8_1_expected = "DROP VIEW view_foo;
 534 CREATE VIEW view_foo ( id, name ) AS
 535     SELECT id, name FROM thing
 536 ";
 537
 538 is($drop_view_8_1_produced, $drop_view_8_1_expected, "My DROP VIEW statement for 8.1 is correct");
 539
 540 my $drop_view_opts2 = { add_drop_view => 1, no_comments => 1, postgres_version => 9.001 };
 541 my $drop_view_9_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts2);
 542
 543 my $drop_view_9_1_expected = "DROP VIEW IF EXISTS view_foo;
 544 CREATE VIEW view_foo ( id, name ) AS
 545     SELECT id, name FROM thing
 546 ";
 547
 548 is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct");