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);
106 my $field4 = SQL::Translator::Schema::Field->new( name => 'field4',
108 data_type => 'geography',
110 geography_type => 'POINT',
113 default_value => undef,
114 is_auto_increment => 0,
118 $table->add_field($field4);
120 my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table);
121 is($create_table,qq[--
124 CREATE TABLE mytable (
126 field4 geography(POINT,-1),
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))
131 INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.');
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.');
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.');