[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 ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
is($create_table,qq[--
-- Table: mytable
--
CREATE TABLE mytable (
  field3 geometry,
  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 => {
	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	my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
106	is($create_table,qq[--
107	-- Table: mytable
108	--
109	CREATE TABLE mytable (
110	field3 geometry,
111	CONSTRAINT "enforce_dims_field3" CHECK ((st_ndims(field3) = 2)),
112	CONSTRAINT "enforce_srid_field3" CHECK ((st_srid(field3) = -1)),
113	CONSTRAINT "enforce_geotype_field3" CHECK ((geometrytype(field3) = 'POINT'::text OR field3 IS NULL))
114	);
115	INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.');
116
117	my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2");
118	is($rename_table,qq[ALTER TABLE mytable RENAME TO table2
119	DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' AND f_geometry_column = 'field3'
120	INSERT INTO geometry_columns VALUES ('','myschema','table2','field3','2','-1','POINT')],'Rename table with geometry works.');
121
122	$table->name("table2");
123	my $drop_table = SQL::Translator::Producer::PostgreSQL::drop_table($table);
124	is($drop_table, qq[DROP TABLE table2 CASCADE
125	DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'table2' AND f_geometry_column = 'field3'], 'Drop table with geometry works.');