[dbsrgits/SQL-Translator.git] / t / 63-spacial-pgsql.t

#!/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.');
Commit	Line	Data
e83ad71c	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 => {
aee4b66e	35	dimensions => 2,
	36	geometry_type => 'POINT',
	37	srid => -1
	38	},
e83ad71c	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
42bab2bc	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))],
e83ad71c	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);
c50d1a0a	72	is($alter_field, qq[DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'myfield';
e83ad71c	73	ALTER TABLE mytable DROP CONSTRAINT enforce_dims_myfield
e83ad71c	74	ALTER TABLE mytable DROP CONSTRAINT enforce_srid_myfield
c50d1a0a	75	ALTER TABLE mytable DROP CONSTRAINT enforce_geotype_myfield;
c50d1a0a	76	ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
e83ad71c	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);
c50d1a0a	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');
42bab2bc	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))],
e83ad71c	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')
42bab2bc	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))],
e83ad71c	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);
140a1dad	105
	106	my $field4 = SQL::Translator::Schema::Field->new( name => 'field4',
	107	table => $table,
	108	data_type => 'geography',
	109	extra => {
aee4b66e	110	geography_type => 'POINT',
	111	srid => -1
	112	},
140a1dad	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
e83ad71c	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,
140a1dad	126	field4 geography(POINT,-1),
42bab2bc	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))
e83ad71c	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.');
140a1dad	142