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
|
#!/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 $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field;
my $schema = SQL::Translator::Schema->new( name => 'myschema' );
my $table = SQL::Translator::Schema::Table->new( name => 'mytable', 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);
is($field1_sql, 'myfield geometry', 'Create geometry field works');
my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1);
is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT')", 'Add geometry column works');
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))],
'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);
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 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))],
'Alter field non geometry to geometry works');
$field1->name('field3');
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))],
'Add geometry field works');
my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field1);
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,
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.');
my $rename_table = SQL::Translator::Producer::PostgreSQL::rename_table($table, "table2");
is($rename_table,qq[ALTER TABLE mytable RENAME TO table2
DELETE FROM geometry_columns WHERE f_table_schema = 'myschema' AND f_table_name = 'mytable' 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);
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.');
|
