1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
#!/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 $options = { quote_identifiers => 1 };
my $schema = SQL::Translator::Schema->new( name => 'myschema' );
my $table = SQL::Translator::Schema::Table->new( name => 'my\'table', 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, $options);
is($field1_sql, '"myfield" geometry', 'Create geometry field works');
my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1, $options);
is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT')", 'Add geometry column works');
my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1, $options);
is($field1_geocon, qq[ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
ALTER TABLE "my'table" 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, $options);
is($alter_field, qq[DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' AND f_geometry_column = 'myfield';
ALTER TABLE "my'table" DROP CONSTRAINT "enforce_dims_myfield";
ALTER TABLE "my'table" DROP CONSTRAINT "enforce_srid_myfield";
ALTER TABLE "my'table" DROP CONSTRAINT "enforce_geotype_myfield";
ALTER TABLE "my'table" ALTER COLUMN "myfield" SET NOT NULL;
ALTER TABLE "my'table" 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, $options);
is($alter_field2, qq[ALTER TABLE "my'table" ALTER COLUMN "myfield" DROP NOT NULL;
ALTER TABLE "my'table" ALTER COLUMN "myfield" TYPE geometry;
INSERT INTO geometry_columns VALUES ('','myschema','my''table','myfield','2','-1','POINT');
ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_myfield" CHECK ((ST_NDims("myfield") = 2));
ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_myfield" CHECK ((ST_SRID("myfield") = -1));
ALTER TABLE "my'table" 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, $options);
is($add_field, qq[ALTER TABLE "my'table" ADD COLUMN "field3" geometry;
INSERT INTO geometry_columns VALUES ('','myschema','my''table','field3','2','-1','POINT');
ALTER TABLE "my'table" ADD CONSTRAINT "enforce_dims_field3" CHECK ((ST_NDims("field3") = 2));
ALTER TABLE "my'table" ADD CONSTRAINT "enforce_srid_field3" CHECK ((ST_SRID("field3") = -1));
ALTER TABLE "my'table" 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, $options);
is($drop_field, qq[ALTER TABLE "my'table" DROP COLUMN "field3";
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' 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, $options);
is($create_table,qq[--
-- Table: my'table
--
CREATE TABLE "my'table" (
"field3" geometry,
"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','my''table','field3','2','-1','POINT')],
'Create table with geometry works.');
my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2", $options);
is($rename_table,qq[ALTER TABLE "my'table" RENAME TO "table2";
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'my''table' 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, $options);
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.');
|
