Debian Codegraph @

mysql-test/suite/rpl/t/rpl_gis_ddl.test

raw
1 #############################################################################
2 #
3 # The aim of this test case is to test the new column property/constraint
4 # on geometry columns which allows to specify the SRID.
5 #
6 # E.g: CREATE TABLE t1(c1 GEOMETRY SRID 4326, c2 POINT SRID 0 NOT NULL);
7 #
8 # The following scenarios are tested :
9 #
10 # 1. Test table having columns with and without SRID property must
11 # replicate properly.
12 # 2. Test removing of the SRID property from a geometry column must work
13 # fine and replicates properly.
14 # 3. Test the SRID property on a generated column must work fine and
15 # replicate properly.
16 #
17 # Creation date: 2017-08-03
18 # Author: Pavan Naik
19 #
20 #############################################################################
21
22 # Skip the test case on group replication runs since it uses
23 # MyISAM engine tables.
24 --source include/not_group_replication_plugin.inc
25
26 --source include/master-slave.inc
27
28 --connection master
29
30 --echo # Create a table with three columns:
31 --echo # 1) Normal POINT column without SRID specification
32 --echo # 2) Normal POINT column with SRID 0
33 --echo # 3) Normal POINT column with SRID 4326
34 CREATE TABLE t1 (no_srid POINT DEFAULT NULL,
35 srid_0 POINT SRID 0 DEFAULT NULL,
36 srid_4326 POINT SRID 4326 DEFAULT NULL);
37
38 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
39
40 --echo # Insert some data with correct SRID into these columns
41 INSERT INTO t1 (no_srid, srid_0, srid_4326)
42 VALUES (ST_GeomFromText('POINT(0 0)', 0),
43 ST_GeomFromText('POINT(0 0)', 0),
44 ST_GeomFromText('POINT(0 0)', 4326));
45
46 INSERT INTO t1 (no_srid, srid_0, srid_4326)
47 VALUES (ST_GeomFromText('POINT(1 1)', 4326),
48 ST_GeomFromText('POINT(1 1)', 0),
49 ST_GeomFromText('POINT(1 1)', 4326));
50
51 --source include/sync_slave_sql_with_master.inc
52 --echo [Connection Slave]
53
54 --let $diff_tables = master:t1, slave:t1
55 --source include/diff_tables.inc
56
57 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
58
59 SELECT ST_SRID(no_srid) AS no_srid,
60 ST_SRID(srid_0) AS srid_0,
61 ST_SRID(srid_4326) AS srid_4326 FROM t1;
62
63 --echo [Connection Master]
64 --connection master
65
66 --echo # Removing the SRID specification from a column should work just fine.
67 ALTER TABLE t1 CHANGE COLUMN srid_4326 no_srid_2 POINT DEFAULT NULL;
68
69 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
70
71 --source include/sync_slave_sql_with_master.inc
72 --echo [Connection Slave]
73
74 --let $diff_tables = master:t1, slave:t1
75 --source include/diff_tables.inc
76
77 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
78
79 SELECT ST_SRID(no_srid) AS no_srid,
80 ST_SRID(no_srid_2) AS no_srid_2,
81 ST_SRID(srid_0) AS srid_0 FROM t1;
82
83 --echo [Connection Master]
84 --connection master
85
86 DELETE FROM t1;
87
88 --source include/sync_slave_sql_with_master.inc
89 --echo [Connection Slave]
90
91 --let $diff_tables = master:t1, slave:t1
92 --source include/diff_tables.inc
93
94 --echo [Connection Master]
95 --connection master
96
97 ALTER TABLE t1 CHANGE COLUMN srid_0 srid_4326 POINT SRID 4326 DEFAULT NULL;
98
99 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
100
101 INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
102 VALUES (ST_GeomFromText('POINT(0 0)', 2000),
103 ST_GeomFromText('POINT(0 0)', 0),
104 ST_GeomFromText('POINT(0 0)', 4326));
105
106 INSERT INTO t1 (no_srid, no_srid_2, srid_4326)
107 VALUES (ST_GeomFromText('POINT(1 1)', 4326),
108 ST_GeomFromText('POINT(1 1)', 2000),
109 ST_GeomFromText('POINT(1 1)', 4326));
110
111 --source include/sync_slave_sql_with_master.inc
112 --echo [Connection Slave]
113
114 --let $diff_tables = master:t1, slave:t1
115 --source include/diff_tables.inc
116
117 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS ORDER BY COLUMN_NAME;
118
119 SELECT ST_SRID(no_srid) AS no_srid,
120 ST_SRID(no_srid_2) AS no_srid_2,
121 ST_SRID(srid_4326) AS srid_4326 FROM t1;
122
123 --echo [Connection Master]
124 --connection master
125
126 --echo # Cleanup
127 DROP TABLE t1;
128
129 --source include/sync_slave_sql_with_master.inc
130
131 --echo # The SRID property on a generated column should work
132
133 --echo [Connection Master]
134 --connection master
135
136 CREATE TABLE t1 (col1 POINT SRID 4326,
137 col2 POINT AS (ST_SRID(col1, 0)) SRID 0);
138 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 4326));
139 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 4326));
140
141 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
142
143 --source include/sync_slave_sql_with_master.inc
144 --echo [Connection Slave]
145
146 --let $diff_tables = master:t1, slave:t1
147 --source include/diff_tables.inc
148
149 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
150
151 SELECT ST_SRID(col1) AS srid_4326, ST_SRID(col2) AS srid_0 FROM t1;
152
153 --echo [Connection Master]
154 --connection master
155
156 DELETE FROM t1;
157
158 ALTER TABLE t1 CHANGE COLUMN col1 col1 POINT SRID 2000;
159 ALTER TABLE t1 CHANGE COLUMN col2 col2 POINT AS (ST_SRID(col1, 4326)) SRID 4326;
160
161 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(0 0)', 2000));
162 INSERT INTO t1 (col1) VALUES (ST_GeomFromText('POINT(1 1)', 2000));
163
164 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
165
166 --source include/sync_slave_sql_with_master.inc
167 --echo [Connection Slave]
168
169 --let $diff_tables = master:t1, slave:t1
170 --source include/diff_tables.inc
171
172 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS;
173
174 SELECT ST_SRID(col1) AS srid_2000, ST_SRID(col2) AS srid_4326 FROM t1;
175
176 --echo [Connection Master]
177 --connection master
178
179 --echo # Cleanup
180 DROP TABLE t1;
181
182 --source include/sync_slave_sql_with_master.inc
183
184 --source include/rpl_end.inc