Commit | Line | Data |
e83ad71c |
1 | #!/usr/bin/perl |
2 | |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
7 | use Test::Exception; |
8 | use Test::SQL::Translator qw(maybe_plan); |
9 | |
10 | use Data::Dumper; |
11 | use FindBin qw/$Bin/; |
12 | |
13 | # Testing 1,2,3,4... |
14 | #============================================================================= |
15 | |
16 | BEGIN { |
17 | maybe_plan(10, |
18 | 'SQL::Translator::Producer::PostgreSQL', |
19 | 'Test::Differences', |
20 | ) |
21 | } |
22 | use Test::Differences; |
23 | use SQL::Translator; |
24 | |
25 | my $PRODUCER = \&SQL::Translator::Producer::PostgreSQL::create_field; |
26 | |
27 | my $schema = SQL::Translator::Schema->new( name => 'myschema' ); |
28 | |
29 | my $table = SQL::Translator::Schema::Table->new( name => 'mytable', schema => $schema ); |
30 | |
31 | my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', |
32 | table => $table, |
33 | data_type => 'geometry', |
34 | extra => { |
35 | dimensions => 2, |
36 | geometry_type => 'POINT', |
37 | srid => -1 |
38 | }, |
39 | default_value => undef, |
40 | is_auto_increment => 0, |
41 | is_nullable => 1, |
42 | is_foreign_key => 0, |
43 | is_unique => 0 ); |
44 | |
45 | my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1); |
46 | |
47 | is($field1_sql, 'myfield geometry', 'Create geometry field works'); |
48 | |
49 | my $field1_geocol = SQL::Translator::Producer::PostgreSQL::add_geometry_column($field1); |
50 | |
51 | is($field1_geocol, "INSERT INTO geometry_columns VALUES ('','myschema','mytable','myfield','2','-1','POINT')", 'Add geometry column works'); |
52 | |
53 | my $field1_geocon = SQL::Translator::Producer::PostgreSQL::add_geometry_constraints($field1); |
54 | |
42bab2bc |
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))], |
e83ad71c |
58 | 'Add geometry constraints works'); |
59 | |
60 | my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', |
61 | table => $table, |
62 | data_type => 'VARCHAR', |
63 | size => 25, |
64 | default_value => undef, |
65 | is_auto_increment => 0, |
66 | is_nullable => 0, |
67 | is_foreign_key => 0, |
68 | is_unique => 0 ); |
69 | |
70 | my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1, |
71 | $field2); |
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'); |
79 | |
80 | my $alter_field2 = SQL::Translator::Producer::PostgreSQL::alter_field($field2, |
81 | $field1); |
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') |
42bab2bc |
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))], |
e83ad71c |
88 | 'Alter field non geometry to geometry works'); |
89 | |
90 | $field1->name('field3'); |
91 | my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1); |
92 | |
93 | is($add_field, qq[ALTER TABLE mytable ADD COLUMN field3 geometry |
94 | INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT') |
42bab2bc |
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))], |
e83ad71c |
98 | 'Add geometry field works'); |
99 | |
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'); |
103 | |
104 | $table->add_field($field1); |
140a1dad |
105 | |
106 | my $field4 = SQL::Translator::Schema::Field->new( name => 'field4', |
107 | table => $table, |
108 | data_type => 'geography', |
109 | extra => { |
110 | geography_type => 'POINT', |
111 | srid => -1 |
112 | }, |
113 | default_value => undef, |
114 | is_auto_increment => 0, |
115 | is_nullable => 1, |
116 | is_foreign_key => 0, |
117 | is_unique => 0 ); |
118 | $table->add_field($field4); |
119 | |
e83ad71c |
120 | my ($create_table,$fks) = SQL::Translator::Producer::PostgreSQL::create_table($table); |
121 | is($create_table,qq[-- |
122 | -- Table: mytable |
123 | -- |
124 | CREATE TABLE mytable ( |
125 | field3 geometry, |
140a1dad |
126 | field4 geography(POINT,-1), |
42bab2bc |
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)) |
e83ad71c |
130 | ); |
131 | INSERT INTO geometry_columns VALUES ('','myschema','mytable','field3','2','-1','POINT')],'Create table with geometry works.'); |
132 | |
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.'); |
137 | |
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.'); |
140a1dad |
142 | |