#!/usr/bin/perl
use strict;
use warnings;
use Test::More;
use Test::Exception;
use Test::SQL::Translator qw(maybe_plan);
use Data::Dumper;
use FindBin qw/$Bin/;
# Testing 1,2,3,4...
#=============================================================================
BEGIN {
maybe_plan(10,
'SQL::Translator::Producer::PostgreSQL',
'Test::Differences',
)
}
use Test::Differences;
use SQL::Translator;
my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
my $schema = SQL::Translator::Schema->new( name => 'myschema' );
my $table = SQL::Translator::Schema::Table->new( name => 'mytable', schema => $schema );
my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
table => $table,
data_type => 'geometry',
extra => {
dimensions => 2,
geometry_type => 'POINT',
srid => -1
},
default_value => undef,
is_auto_increment => 0,
is_nullable => 1,
is_foreign_key => 0,
is_unique => 0 );
my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
is($field1_sql, 'myfield geometry', 'Create geometry field works');
my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1);
is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT')", 'Add geometry column works');
my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1);
is($field1_geocon, qq[ALTER TABLE mytable ADD CONSTRAINT enforce_dims_myfield CHECK ((ST_NDims(myfield) = 2))
ALTER TABLE mytable ADD CONSTRAINT enforce_srid_myfield CHECK ((ST_SRID(myfield) = -1))
ALTER TABLE mytable ADD CONSTRAINT enforce_geotype_myfield CHECK ((GeometryType(myfield) = 'POINT'::text OR myfield IS NULL))],
'Add geometry constraints works');
my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
table => $table,
data_type => 'VARCHAR',
size => 25,
default_value => undef,
is_auto_increment => 0,
is_nullable => 0,
is_foreign_key => 0,
is_unique => 0 );
my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
$field2);
is($alter_field, qq[DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'myfield';
ALTER TABLE mytable DROP CONSTRAINT enforce_dims_myfield
ALTER TABLE mytable DROP CONSTRAINT enforce_srid_myfield
ALTER TABLE mytable DROP CONSTRAINT enforce_geotype_myfield;
ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25)],
'Alter field geometry to non geometry works');
my $alter_field2 = SQL::Translator::Producer::PostgreSQL::alter_field($field2,
$field1);
is($alter_field2, qq[ALTER TABLE mytable ALTER COLUMN myfield DROP NOT NULL;
ALTER TABLE mytable ALTER COLUMN myfield TYPE geometry;
INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT');
ALTER TABLE mytable ADD CONSTRAINT enforce_dims_myfield CHECK ((ST_NDims(myfield) = 2))
ALTER TABLE mytable ADD CONSTRAINT enforce_srid_myfield CHECK ((ST_SRID(myfield) = -1))
ALTER TABLE mytable ADD CONSTRAINT enforce_geotype_myfield CHECK ((GeometryType(myfield) = 'POINT'::text OR myfield IS NULL))],
'Alter field non geometry to geometry works');
$field1->name('field3');
my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
is($add_field, qq[ALTER TABLE mytable ADD COLUMN field3 geometry
INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')
ALTER TABLE mytable ADD CONSTRAINT enforce_dims_field3 CHECK ((ST_NDims(field3) = 2))
ALTER TABLE mytable ADD CONSTRAINT enforce_srid_field3 CHECK ((ST_SRID(field3) = -1))
ALTER TABLE mytable ADD CONSTRAINT enforce_geotype_field3 CHECK ((GeometryType(field3) = 'POINT'::text OR field3 IS NULL))],
'Add geometry field works');
my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field1);
is($drop_field, qq[ALTER TABLE mytable DROP COLUMN field3
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'field3'], 'Drop geometry field works');
$table->add_field($field1);
my $field4 = SQL::Translator::Schema::Field->new( name => 'field4',
table => $table,
data_type => 'geography',
extra => {
geography_type => 'POINT',
srid => -1
},
default_value => undef,
is_auto_increment => 0,
is_nullable => 1,
is_foreign_key => 0,
is_unique => 0 );
$table->add_field($field4);
my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
is($create_table,qq[--
-- Table: mytable
--
CREATE TABLE mytable (
field3 geometry,
field4 geography(POINT,-1),
CONSTRAINT enforce_dims_field3 CHECK ((ST_NDims(field3) = 2)),
CONSTRAINT enforce_srid_field3 CHECK ((ST_SRID(field3) = -1)),
CONSTRAINT enforce_geotype_field3 CHECK ((GeometryType(field3) = 'POINT'::text OR field3 IS NULL))
);
INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.');
my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2");
is($rename_table,qq[ALTER TABLE mytable RENAME TO table2
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'field3'
INSERT INTO geometry_columns VALUES ('','myschema','table2','field3','2','-1','POINT')],'Rename table with geometry works.');
$table->name("table2");
my $drop_table = SQL::Translator::Producer::PostgreSQL::drop_table($table);
is($drop_table, qq[DROP TABLE table2 CASCADE
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'table2' AND f_geometry_column = 'field3'], 'Drop table with geometry works.');