t/63-spacial-pgsql.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
  10 use Data::Dumper;
  11 use FindBin qw/$Bin/;
  12
  13 # Testing 1,2,3,4...
  14 #=============================================================================
  15
  16 BEGIN {
  17     maybe_plan(10,
  18         'SQL::Translator::Producer::PostgreSQL',
  19         'Test::Differences',
  20     )
  21 }
  22 use Test::Differences;
  23 use SQL::Translator;
  24
  25 my $options = { quote_identifiers => 1 };
  26
  27 my $schema = SQL::Translator::Schema->new( name => 'myschema' );
  28
  29 my $table = SQL::Translator::Schema::Table->new( name => 'my\'table', schema => $schema );
  30
  31 my $field1 = SQL::Translator::Schema::Field->new( name      => 'myfield',
  32                                                   table     => $table,
  33                                                   data_type => 'geometry',
  34                                                   extra     => {
  35                                                       dimensions    => 2,
  36                                                       geometry_type => 'POINT',
  37                                                       srid          => -1
  38                                                   },
  39                                                   default_value     => undef,
  40                                                   is_auto_increment => 0,
  41                                                   is_nullable       => 1,
  42                                                   is_foreign_key    => 0,
  43                                                   is_unique         => 0 );
  44
  45 my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1, $options);
  46
  47 is($field1_sql, '"myfield" geometry', 'Create geometry field works');
  48
  49 my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1, $options);
  50
  51 is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT')", 'Add geometry column works');
  52
  53 my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1, $options);
  54
  55 is($field1_geocon, qq[ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
  56 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
  57 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_geotype_myfield" CHECK ((GeometryType("myfield") = 'POINT'::text OR "myfield" IS NULL))],
  58  'Add geometry constraints works');
  59
  60 my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
  61                                                   table => $table,
  62                                                   data_type => 'VARCHAR',
  63                                                   size      => 25,
  64                                                   default_value => undef,
  65                                                   is_auto_increment => 0,
  66                                                   is_nullable => 0,
  67                                                   is_foreign_key => 0,
  68                                                   is_unique => 0 );
  69
  70 my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
  71                                                                 $field2, $options);
  72 is($alter_field, qq[DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'myfield';
  73 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_dims_myfield";
  74 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_srid_myfield";
  75 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_geotype_myfield";
  76 ALTER TABLE "my'table" ALTER COLUMN "myfield" SET NOT NULL;
  77 ALTER TABLE "my'table" ALTER COLUMN "myfield" TYPE character varying(25)],
  78  'Alter field geometry to non geometry works');
  79
  80 my $alter_field2 = SQL::Translator::Producer::PostgreSQL::alter_field($field2,
  81                                                                 $field1, $options);
  82 is($alter_field2, qq[ALTER TABLE "my'table" ALTER COLUMN "myfield" DROP NOT NULL;
  83 ALTER TABLE "my'table" ALTER COLUMN "myfield" TYPE geometry;
  84 INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT');
  85 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
  86 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
  87 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_geotype_myfield" CHECK ((GeometryType("myfield") = 'POINT'::text OR "myfield" IS NULL))],
  88  'Alter field non geometry to geometry works');
  89
  90 $field1->name('field3');
  91 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1, $options);
  92
  93 is($add_field, qq[ALTER TABLE "my'table" ADD COLUMN "field3" geometry;
  94 INSERT INTO geometry_columns VALUES ('','myschema','my''table','field3','2','-1','POINT');
  95 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_field3" CHECK ((ST_NDims("field3") = 2));
  96 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_field3" CHECK ((ST_SRID("field3") = -1));
  97 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_geotype_field3" CHECK ((GeometryType("field3") = 'POINT'::text OR "field3" IS NULL))],
  98  'Add geometry field works');
  99
 100 my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field1, $options);
 101 is($drop_field, qq[ALTER TABLE "my'table" DROP COLUMN "field3";
 102 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'field3'],
 103  'Drop geometry field works');
 104
 105 $table->add_field($field1);
 106
 107 my $field4 = SQL::Translator::Schema::Field->new( name      => 'field4',
 108                                                   table     => $table,
 109                                                   data_type => 'geography',
 110                                                   extra     => {
 111                                                       geography_type => 'POINT',
 112                                                       srid           => -1
 113                                                   },
 114                                                   default_value     => undef,
 115                                                   is_auto_increment => 0,
 116                                                   is_nullable       => 1,
 117                                                   is_foreign_key    => 0,
 118                                                   is_unique         => 0 );
 119 $table->add_field($field4);
 120
 121 my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table, $options);
 122 is($create_table,qq[--
 123 -- Table: my'table
 124 --
 125 CREATE TABLE "my'table" (
 126   "field3" geometry,
 127   "field4" geography(POINT,-1),
 128   CONSTRAINT "enforce_dims_field3" CHECK ((ST_NDims("field3") = 2)),
 129   CONSTRAINT "enforce_srid_field3" CHECK ((ST_SRID("field3") = -1)),
 130   CONSTRAINT "enforce_geotype_field3" CHECK ((GeometryType("field3") = 'POINT'::text OR "field3" IS NULL))
 131 );
 132 INSERT INTO geometry_columns VALUES ('','myschema','my''table','field3','2','-1','POINT')],
 133  'Create table with geometry works.');
 134
 135 my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2", $options);
 136 is($rename_table,qq[ALTER TABLE "my'table" RENAME TO "table2";
 137 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'field3';
 138 INSERT INTO geometry_columns VALUES ('','myschema','table2','field3','2','-1','POINT')],
 139  'Rename table with geometry works.');
 140
 141 $table->name("table2");
 142 my $drop_table = SQL::Translator::Producer::PostgreSQL::drop_table($table, $options);
 143 is($drop_table, qq[DROP TABLE "table2" CASCADE;
 144 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'table2' AND f_geometry_column = 'field3'],
 145  'Drop table with geometry works.');
 146