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 $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
  26
  27 my $schema = SQL::Translator::Schema->new( name => 'myschema' );
  28
  29 my $table = SQL::Translator::Schema::Table->new( name => 'mytable', 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);
  46
  47 is($field1_sql, 'myfield geometry', 'Create geometry field works');
  48
  49 my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1);
  50
  51 is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT')", 'Add geometry column works');
  52
  53 my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1);
  54
  55 is($field1_geocon, qq[ALTER TABLE mytable ADD CONSTRAINT enforce_dims_myfield CHECK ((ST_NDims(myfield) = 2))
  56 ALTER TABLE mytable ADD CONSTRAINT enforce_srid_myfield CHECK ((ST_SRID(myfield) = -1))
  57 ALTER TABLE mytable 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);
  72 is($alter_field, qq[DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'myfield';
  73 ALTER TABLE mytable DROP CONSTRAINT enforce_dims_myfield
  74 ALTER TABLE mytable DROP CONSTRAINT enforce_srid_myfield
  75 ALTER TABLE mytable DROP CONSTRAINT enforce_geotype_myfield;
  76 ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
  77 ALTER TABLE mytable 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);
  82 is($alter_field2, qq[ALTER TABLE mytable ALTER COLUMN myfield DROP NOT NULL;
  83 ALTER TABLE mytable ALTER COLUMN myfield TYPE geometry;
  84 INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT');
  85 ALTER TABLE mytable ADD CONSTRAINT enforce_dims_myfield CHECK ((ST_NDims(myfield) = 2))
  86 ALTER TABLE mytable ADD CONSTRAINT enforce_srid_myfield CHECK ((ST_SRID(myfield) = -1))
  87 ALTER TABLE mytable 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);
  92
  93 is($add_field, qq[ALTER TABLE mytable ADD COLUMN field3 geometry
  94 INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')
  95 ALTER TABLE mytable ADD CONSTRAINT enforce_dims_field3 CHECK ((ST_NDims(field3) = 2))
  96 ALTER TABLE mytable ADD CONSTRAINT enforce_srid_field3 CHECK ((ST_SRID(field3) = -1))
  97 ALTER TABLE mytable 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);
 101 is($drop_field, qq[ALTER TABLE mytable DROP COLUMN field3
 102 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'field3'], 'Drop geometry field works');
 103
 104 $table->add_field($field1);
 105
 106 my $field4 = SQL::Translator::Schema::Field->new( name      => 'field4',
 107                                                   table     => $table,
 108                                                   data_type => 'geography',
 109                                                   extra     => {
 110                                                       geography_type => 'POINT',
 111                                                       srid           => -1
 112                                                   },
 113                                                   default_value     => undef,
 114                                                   is_auto_increment => 0,
 115                                                   is_nullable       => 1,
 116                                                   is_foreign_key    => 0,
 117                                                   is_unique         => 0 );
 118 $table->add_field($field4);
 119
 120 my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
 121 is($create_table,qq[--
 122 -- Table: mytable
 123 --
 124 CREATE TABLE mytable (
 125   field3 geometry,
 126   field4 geography(POINT,-1),
 127   CONSTRAINT enforce_dims_field3 CHECK ((ST_NDims(field3) = 2)),
 128   CONSTRAINT enforce_srid_field3 CHECK ((ST_SRID(field3) = -1)),
 129   CONSTRAINT enforce_geotype_field3 CHECK ((GeometryType(field3) = 'POINT'::text OR field3 IS NULL))
 130 );
 131 INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.');
 132
 133 my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2");
 134 is($rename_table,qq[ALTER TABLE mytable RENAME TO table2
 135 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'field3'
 136 INSERT INTO geometry_columns VALUES ('','myschema','table2','field3','2','-1','POINT')],'Rename table with geometry works.');
 137
 138 $table->name("table2");
 139 my $drop_table = SQL::Translator::Producer::PostgreSQL::drop_table($table);
 140 is($drop_table, qq[DROP TABLE table2 CASCADE
 141 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'table2' AND f_geometry_column = 'field3'], 'Drop table with geometry works.');
 142