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(41,
  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 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
  68                                                                 $field2);
  69 is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL
  70 ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)],
  71  'Alter field works');
  72
  73 $field1->name('field3');
  74 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
  75
  76 is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10)', 'Add field works');
  77
  78 my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2);
  79 is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
  80
  81 my $field3 = SQL::Translator::Schema::Field->new( name      => 'time_field',
  82                                                   table => $table,
  83                                                   data_type => 'TIME',
  84                                                   default_value => undef,
  85                                                   is_auto_increment => 0,
  86                                                   is_nullable => 0,
  87                                                   is_foreign_key => 0,
  88                                                   is_unique => 0 );
  89
  90 my $field3_sql = SQL::Translator::Producer::PostgreSQL::create_field($field3);
  91
  92 is($field3_sql, 'time_field time NOT NULL', 'Create time field works');
  93
  94 my $field3_datetime_with_TZ = SQL::Translator::Schema::Field->new(
  95     name      => 'datetime_with_TZ',
  96     table     => $table,
  97     data_type => 'timestamp with time zone',
  98     size      => 7,
  99 );
 100
 101 my $field3_datetime_with_TZ_sql =
 102     SQL::Translator::Producer::PostgreSQL::create_field(
 103         $field3_datetime_with_TZ
 104     );
 105
 106 is(
 107     $field3_datetime_with_TZ_sql,
 108     'datetime_with_TZ timestamp(6) with time zone',
 109     'Create time field with time zone and size, works'
 110 );
 111
 112 my $field3_time_without_TZ = SQL::Translator::Schema::Field->new(
 113     name      => 'time_without_TZ',
 114     table     => $table,
 115     data_type => 'time without time zone',
 116     size      => 2,
 117 );
 118
 119 my $field3_time_without_TZ_sql
 120     = SQL::Translator::Producer::PostgreSQL::create_field(
 121         $field3_time_without_TZ
 122     );
 123
 124 is(
 125     $field3_time_without_TZ_sql,
 126     'time_without_TZ time(2) without time zone',
 127     'Create time field without time zone but with size, works'
 128 );
 129
 130 my $field_num = SQL::Translator::Schema::Field->new( name => 'num',
 131                                                   table => $table,
 132                                                   data_type => 'numeric',
 133                                                   size => [10,2],
 134                                                   );
 135 my $fieldnum_sql = SQL::Translator::Producer::PostgreSQL::create_field($field_num);
 136
 137 is($fieldnum_sql, 'num numeric(10,2)', 'Create numeric field works');
 138
 139
 140 my $field4 = SQL::Translator::Schema::Field->new( name      => 'bytea_field',
 141                                                   table => $table,
 142                                                   data_type => 'bytea',
 143                                                   size => '16777215',
 144                                                   default_value => undef,
 145                                                   is_auto_increment => 0,
 146                                                   is_nullable => 0,
 147                                                   is_foreign_key => 0,
 148                                                   is_unique => 0 );
 149
 150 my $field4_sql = SQL::Translator::Producer::PostgreSQL::create_field($field4);
 151
 152 is($field4_sql, 'bytea_field bytea NOT NULL', 'Create bytea field works');
 153
 154 my $field5 = SQL::Translator::Schema::Field->new( name => 'enum_field',
 155                                                    table => $table,
 156                                                    data_type => 'enum',
 157                                                    extra => { list => [ 'Foo', 'Bar' ] },
 158                                                    is_auto_increment => 0,
 159                                                    is_nullable => 0,
 160                                                    is_foreign_key => 0,
 161                                                    is_unique => 0 );
 162
 163 my $field5_sql = SQL::Translator::Producer::PostgreSQL::create_field($field5,{ postgres_version => 8.3 });
 164
 165 is($field5_sql, 'enum_field mytable_enum_field_type NOT NULL', 'Create real enum field works');
 166
 167
 168
 169
 170 my $field6 = SQL::Translator::Schema::Field->new(
 171                                                   name      => 'character',
 172                                                   table => $table,
 173                                                   data_type => 'character',
 174                                                   size => '123',
 175                                                   default_value => 'foobar',
 176                                                     is_auto_increment => 0,
 177                                                     is_nullable => 0,
 178                                                     is_foreign_key => 0,
 179                                                     is_unique => 0);
 180
 181 my $field7 = SQL::Translator::Schema::Field->new(
 182                                 name      => 'character',
 183                                 table => $table,
 184                                 data_type => 'character',
 185                                 size => '123',
 186                                 default_value => undef,
 187                                   is_auto_increment => 0,
 188                                   is_nullable => 0,
 189                                   is_foreign_key => 0,
 190                                   is_unique => 0);
 191
 192 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 193                                                                 $field7);
 194
 195 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP DEFAULT), 'DROP DEFAULT');
 196
 197 $field7->default_value(q(foo'bar'));
 198
 199 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 200                                                                 $field7);
 201
 202 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT 'foo''bar'''), 'DEFAULT with escaping');
 203
 204 $field7->default_value(\q(foobar));
 205
 206 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 207                                                                 $field7);
 208
 209 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character SET DEFAULT foobar), 'DEFAULT unescaped if scalarref');
 210
 211 $field7->is_nullable(1);
 212 $field7->default_value(q(foobar));
 213
 214 $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field6,
 215                                                                 $field7);
 216
 217 is($alter_field, q(ALTER TABLE mytable ALTER COLUMN character DROP NOT NULL), 'DROP NOT NULL');
 218
 219 my $field8 = SQL::Translator::Schema::Field->new( name => 'ts_field',
 220                                                    table => $table,
 221                                                    data_type => 'timestamp with time zone',
 222                                                    size => 6,
 223                                                    is_auto_increment => 0,
 224                                                    is_nullable => 0,
 225                                                    is_foreign_key => 0,
 226                                                    is_unique => 0 );
 227
 228 my $field8_sql = SQL::Translator::Producer::PostgreSQL::create_field($field8,{ postgres_version => 8.3 });
 229
 230 is($field8_sql, 'ts_field timestamp(6) with time zone NOT NULL', 'timestamp with precision');
 231
 232 my $field9 = SQL::Translator::Schema::Field->new( name => 'time_field',
 233                                                    table => $table,
 234                                                    data_type => 'time with time zone',
 235                                                    size => 6,
 236                                                    is_auto_increment => 0,
 237                                                    is_nullable => 0,
 238                                                    is_foreign_key => 0,
 239                                                    is_unique => 0 );
 240
 241 my $field9_sql = SQL::Translator::Producer::PostgreSQL::create_field($field9,{ postgres_version => 8.3 });
 242
 243 is($field9_sql, 'time_field time(6) with time zone NOT NULL', 'time with precision');
 244
 245 my $field10 = SQL::Translator::Schema::Field->new( name => 'interval_field',
 246                                                    table => $table,
 247                                                    data_type => 'interval',
 248                                                    size => 6,
 249                                                    is_auto_increment => 0,
 250                                                    is_nullable => 0,
 251                                                    is_foreign_key => 0,
 252                                                    is_unique => 0 );
 253
 254 my $field10_sql = SQL::Translator::Producer::PostgreSQL::create_field($field10,{ postgres_version => 8.3 });
 255
 256 is($field10_sql, 'interval_field interval(6) NOT NULL', 'time with precision');
 257
 258
 259 my $field11 = SQL::Translator::Schema::Field->new( name => 'time_field',
 260                                                    table => $table,
 261                                                    data_type => 'time without time zone',
 262                                                    size => 6,
 263                                                    is_auto_increment => 0,
 264                                                    is_nullable => 0,
 265                                                    is_foreign_key => 0,
 266                                                    is_unique => 0 );
 267
 268 my $field11_sql = SQL::Translator::Producer::PostgreSQL::create_field($field11,{ postgres_version => 8.3 });
 269
 270 is($field11_sql, 'time_field time(6) without time zone NOT NULL', 'time with precision');
 271
 272
 273
 274 my $field12 = SQL::Translator::Schema::Field->new( name => 'time_field',
 275                                                    table => $table,
 276                                                    data_type => 'timestamp',
 277                                                    is_auto_increment => 0,
 278                                                    is_nullable => 0,
 279                                                    is_foreign_key => 0,
 280                                                    is_unique => 0 );
 281
 282 my $field12_sql = SQL::Translator::Producer::PostgreSQL::create_field($field12,{ postgres_version => 8.3 });
 283
 284 is($field12_sql, 'time_field timestamp NOT NULL', 'time with precision');
 285
 286 my $field13 = SQL::Translator::Schema::Field->new( name => 'enum_field_with_type_name',
 287                                                    table => $table,
 288                                                    data_type => 'enum',
 289                                                    extra => { list => [ 'Foo', 'Bar' ],
 290                                                               custom_type_name => 'real_enum_type' },
 291                                                    is_auto_increment => 0,
 292                                                    is_nullable => 0,
 293                                                    is_foreign_key => 0,
 294                                                    is_unique => 0 );
 295
 296 my $field13_sql = SQL::Translator::Producer::PostgreSQL::create_field($field13,{ postgres_version => 8.3 });
 297
 298 is($field13_sql, 'enum_field_with_type_name real_enum_type NOT NULL', 'Create real enum field works');
 299
 300
 301 {
 302     # let's test default values! -- rjbs, 2008-09-30
 303     my %field = (
 304         table => $table,
 305         data_type => 'VARCHAR',
 306         size => 10,
 307         is_auto_increment => 0,
 308         is_nullable => 1,
 309         is_foreign_key => 0,
 310         is_unique => 0,
 311     );
 312
 313     {
 314         my $simple_default = SQL::Translator::Schema::Field->new(
 315             %field,
 316             name => 'str_default',
 317             default_value => 'foo',
 318         );
 319
 320         is(
 321             $PRODUCER->($simple_default),
 322             q{str_default character varying(10) DEFAULT 'foo'},
 323             'default str',
 324         );
 325     }
 326
 327     {
 328         my $null_default = SQL::Translator::Schema::Field->new(
 329             %field,
 330             name => 'null_default',
 331             default_value => \'NULL',
 332         );
 333
 334         is(
 335             $PRODUCER->($null_default),
 336             q{null_default character varying(10) DEFAULT NULL},
 337             'default null',
 338         );
 339     }
 340
 341     {
 342         my $null_default = SQL::Translator::Schema::Field->new(
 343             %field,
 344             name => 'null_default_2',
 345             default_value => 'NULL', # XXX: this should go away
 346         );
 347
 348         is(
 349             $PRODUCER->($null_default),
 350             q{null_default_2 character varying(10) DEFAULT NULL},
 351             'default null from special cased string',
 352         );
 353     }
 354
 355     {
 356         my $func_default = SQL::Translator::Schema::Field->new(
 357             %field,
 358             name => 'func_default',
 359             default_value => \'func(funky)',
 360         );
 361
 362         is(
 363             $PRODUCER->($func_default),
 364             q{func_default character varying(10) DEFAULT func(funky)},
 365             'unquoted default from scalar ref',
 366         );
 367     }
 368 }
 369
 370
 371 my $view1 = SQL::Translator::Schema::View->new(
 372     name   => 'view_foo',
 373     fields => [qw/id name/],
 374     sql    => 'SELECT id, name FROM thing',
 375 );
 376 my $create_opts = { add_replace_view => 1, no_comments => 1 };
 377 my $view1_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view1, $create_opts);
 378
 379 my $view_sql_replace = "CREATE VIEW view_foo ( id, name ) AS
 380     SELECT id, name FROM thing
 381 ";
 382 is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
 383
 384 my $view2 = SQL::Translator::Schema::View->new(
 385     name   => 'view_foo2',
 386     sql    => 'SELECT id, name FROM thing',
 387     extra  => {
 388       'temporary'    => '1',
 389       'check_option' => 'cascaded',
 390     },
 391 );
 392 my $create2_opts = { add_replace_view => 1, no_comments => 1 };
 393 my $view2_sql1 = SQL::Translator::Producer::PostgreSQL::create_view($view2, $create2_opts);
 394
 395 my $view2_sql_replace = "CREATE TEMPORARY VIEW view_foo2 AS
 396     SELECT id, name FROM thing
 397  WITH CASCADED CHECK OPTION";
 398 is($view2_sql1, $view2_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL 2');
 399
 400 {
 401     my $table = SQL::Translator::Schema::Table->new( name => 'foobar', fields => [qw( foo  bar )] );
 402     my $quote = { quote_table_names => '"', quote_field_names => '"' };
 403
 404     {
 405         my $index = $table->add_index(name => 'myindex', fields => ['foo']);
 406         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 407         is($def, "CREATE INDEX myindex on foobar (foo)", 'index created');
 408         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 409         is($def, 'CREATE INDEX "myindex" on "foobar" ("foo")', 'index created w/ quotes');
 410     }
 411
 412     {
 413         my $index = $table->add_index(name => 'myindex', fields => ['lower(foo)']);
 414         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 415         is($def, "CREATE INDEX myindex on foobar (lower(foo))", 'index created');
 416         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 417         is($def, 'CREATE INDEX "myindex" on "foobar" (lower(foo))', 'index created w/ quotes');
 418     }
 419
 420     {
 421         my $index = $table->add_index(name => 'myindex', fields => ['bar', 'lower(foo)']);
 422         my ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index);
 423         is($def, "CREATE INDEX myindex on foobar (bar, lower(foo))", 'index created');
 424         ($def) = SQL::Translator::Producer::PostgreSQL::create_index($index, $quote);
 425         is($def, 'CREATE INDEX "myindex" on "foobar" ("bar", lower(foo))', 'index created w/ quotes');
 426     }
 427
 428     {
 429         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['foo']);
 430         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 431         is($def->[0], 'CONSTRAINT constr UNIQUE (foo)', 'constraint created');
 432         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 433         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("foo")', 'constraint created w/ quotes');
 434     }
 435
 436     {
 437         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['lower(foo)']);
 438         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 439         is($def->[0], 'CONSTRAINT constr UNIQUE (lower(foo))', 'constraint created');
 440         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 441         is($def->[0], 'CONSTRAINT "constr" UNIQUE (lower(foo))', 'constraint created w/ quotes');
 442     }
 443
 444     {
 445         my $constr = $table->add_constraint(name => 'constr', type => UNIQUE, fields => ['bar', 'lower(foo)']);
 446         my ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr);
 447         is($def->[0], 'CONSTRAINT constr UNIQUE (bar, lower(foo))', 'constraint created');
 448         ($def) = SQL::Translator::Producer::PostgreSQL::create_constraint($constr, $quote);
 449         is($def->[0], 'CONSTRAINT "constr" UNIQUE ("bar", lower(foo))', 'constraint created w/ quotes');
 450     }
 451 }
 452
 453 my $drop_view_opts1 = { add_drop_view => 1, no_comments => 1, postgres_version => 8.001 };
 454 my $drop_view_8_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts1);
 455
 456 my $drop_view_8_1_expected = "DROP VIEW view_foo;
 457 CREATE VIEW view_foo ( id, name ) AS
 458     SELECT id, name FROM thing
 459 ";
 460
 461 is($drop_view_8_1_produced, $drop_view_8_1_expected, "My DROP VIEW statement for 8.1 is correct");
 462
 463 my $drop_view_opts2 = { add_drop_view => 1, no_comments => 1, postgres_version => 9.001 };
 464 my $drop_view_9_1_produced = SQL::Translator::Producer::PostgreSQL::create_view($view1, $drop_view_opts2);
 465
 466 my $drop_view_9_1_expected = "DROP VIEW IF EXISTS view_foo;
 467 CREATE VIEW view_foo ( id, name ) AS
 468     SELECT id, name FROM thing
 469 ";
 470
 471 is($drop_view_9_1_produced, $drop_view_9_1_expected, "My DROP VIEW statement for 9.1 is correct");