8 use Test::SQL::Translator qw(maybe_plan);
14 #=============================================================================
18 'SQL::Translator::Producer::PostgreSQL',
22 use Test::Differences;
25 my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
27 my $schema = SQL::Translator::Schema->new( name => 'myschema' );
29 my $table = SQL::Translator::Schema::Table->new( name => 'mytable', schema => $schema );
31 my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
33 data_type => 'geometry',
36 geometry_type => 'POINT',
39 default_value => undef,
40 is_auto_increment => 0,
45 my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
47 is($field1_sql, 'myfield geometry', 'Create geometry field works');
49 my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1);
51 is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT')", 'Add geometry column works');
53 my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1);
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');
60 my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
62 data_type => 'VARCHAR',
64 default_value => undef,
65 is_auto_increment => 0,
70 my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
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');
80 my $alter_field2 = SQL::Translator::Producer::PostgreSQL::alter_field($field2,
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');
90 $field1->name('field3');
91 my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
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');
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');
104 $table->add_field($field1);
105 my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
106 is($create_table,qq[--
109 CREATE TABLE mytable (
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))
115 INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.');
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.');
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.');