8 use Test::SQL::Translator qw(maybe_plan);
14 #=============================================================================
18 'SQL::Translator::Producer::PostgreSQL',
22 use Test::Differences;
25 my $options = { quote_identifiers => 1 };
27 my $schema = SQL::Translator::Schema->new( name => 'myschema' );
29 my $table = SQL::Translator::Schema::Table->new( name => 'my\'table', 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, $options);
47 is($field1_sql, '"myfield" geometry', 'Create geometry field works');
49 my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1, $options);
51 is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT')", 'Add geometry column works');
53 my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1, $options);
55 is($field1_geocon, qq[ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
56 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
57 ALTER TABLE "my'table" 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 = 'my''table' AND f_geometry_column = 'myfield';
73 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_dims_myfield";
74 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_srid_myfield";
75 ALTER TABLE "my'table" DROP CONSTRAINT "enforce_geotype_myfield";
76 ALTER TABLE "my'table" ALTER COLUMN "myfield" SET NOT NULL;
77 ALTER TABLE "my'table" 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 "my'table" ALTER COLUMN "myfield" DROP NOT NULL;
83 ALTER TABLE "my'table" ALTER COLUMN "myfield" TYPE geometry;
84 INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT');
85 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
86 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
87 ALTER TABLE "my'table" 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, $options);
93 is($add_field, qq[ALTER TABLE "my'table" ADD COLUMN "field3" geometry;
94 INSERT INTO geometry_columns VALUES ('','myschema','my''table','field3','2','-1','POINT');
95 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_field3" CHECK ((ST_NDims("field3") = 2));
96 ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_field3" CHECK ((ST_SRID("field3") = -1));
97 ALTER TABLE "my'table" 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, $options);
101 is($drop_field, qq[ALTER TABLE "my'table" DROP COLUMN "field3";
102 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'field3'],
103 'Drop geometry field works');
105 $table->add_field($field1);
107 my $field4 = SQL::Translator::Schema::Field->new( name => 'field4',
109 data_type => 'geography',
111 geography_type => 'POINT',
114 default_value => undef,
115 is_auto_increment => 0,
119 $table->add_field($field4);
121 my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table, $options);
122 is($create_table,qq[--
125 CREATE TABLE "my'table" (
127 "field4" geography(POINT,-1),
128 CONSTRAINT "enforce_dims_field3" CHECK ((ST_NDims("field3") = 2)),
129 CONSTRAINT "enforce_srid_field3" CHECK ((ST_SRID("field3") = -1)),
130 CONSTRAINT "enforce_geotype_field3" CHECK ((GeometryType("field3") = 'POINT'::text OR "field3" IS NULL))
132 INSERT INTO geometry_columns VALUES ('','myschema','my''table','field3','2','-1','POINT')],
133 'Create table with geometry works.');
135 my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2", $options);
136 is($rename_table,qq[ALTER TABLE "my'table" RENAME TO "table2";
137 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'field3';
138 INSERT INTO geometry_columns VALUES ('','myschema','table2','field3','2','-1','POINT')],
139 'Rename table with geometry works.');
141 $table->name("table2");
142 my $drop_table = SQL::Translator::Producer::PostgreSQL::drop_table($table, $options);
143 is($drop_table, qq[DROP TABLE "table2" CASCADE;
144 DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'table2' AND f_geometry_column = 'field3'],
145 'Drop table with geometry works.');