X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F63-spacial-pgsql.t;h=652893fee74e8c9c0e3bdf22138662eeea7e3db7;hb=d1ca5a5cb007ddb2f361d314ae4c26ddd4308aa4;hp=3ff3de3b5d94ae6c0d7666e74fbe1ed5697714b3;hpb=e83ad71c8681208b81dacee01240c2b78752b83e;p=dbsrgits%2FSQL-Translator.git diff --git a/t/63-spacial-pgsql.t b/t/63-spacial-pgsql.t index 3ff3de3..652893f 100644 --- a/t/63-spacial-pgsql.t +++ b/t/63-spacial-pgsql.t @@ -32,10 +32,10 @@ my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', table => $table, data_type => 'geometry', extra => { - dimensions => 2, - geometry_type => 'POINT', - srid => -1 - }, + dimensions => 2, + geometry_type => 'POINT', + srid => -1 + }, default_value => undef, is_auto_increment => 0, is_nullable => 1, @@ -52,9 +52,9 @@ is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable' 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))], +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', @@ -69,22 +69,22 @@ my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', 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' +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 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))], +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'); @@ -92,9 +92,9 @@ 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))], +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); @@ -102,15 +102,31 @@ 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, - 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)) + 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.'); @@ -123,3 +139,4 @@ $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.'); +