Debian Codegraph @

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

raw
1 #############################################################################################
2 # #
3 # The aim of this test is to set up replication between a master #
4 # and slave and check how it works with GEOMETRY data. #
5 # #
6 # The following scenarios are tested : #
7 # #
8 # 1. Creating a table and inserting different types of spatial data into it. #
9 # 2. Selecting the values from the table using a few geometric functions. #
10 # 3. Creating a view on the table and checking whether it was replicated on slave #
11 # and then dropping the view. #
12 # 4. Altering the table by adding columns, adding index, dropping columns and #
13 # dropping index. #
14 # 5. Updating values in the table. #
15 # 6. Deleting rows from the table. #
16 # 7. Dropping the table. #
17 # #
18 # Creation Date : 2015-10-12 #
19 # Author : Deepa Dixit #
20 # #
21 #############################################################################################
22
23 --source include/set_privilege_checks_user_as_system_user.inc
24 --source include/master-slave.inc
25
26 #############################################################################################
27 # Create tables and insert different GEOMETRY data #
28 #############################################################################################
29
30 --connection master
31
32 --echo # Create tables
33
34 CREATE TABLE point_geom(id INT PRIMARY KEY, pt POINT NOT NULL SRID 0, SPATIAL INDEX(pt));
35 CREATE TABLE linestring_geom(id INT PRIMARY KEY, lnstr LINESTRING NOT NULL SRID 0, SPATIAL INDEX(lnstr));
36 CREATE TABLE polygon_geom(id INT PRIMARY KEY, poly POLYGON NOT NULL SRID 0, SPATIAL INDEX(poly));
37 CREATE TABLE multipoint_geom(id INT PRIMARY KEY, mpt MULTIPOINT NOT NULL SRID 0, SPATIAL INDEX(mpt));
38 CREATE TABLE multilinestring_geom(id INT PRIMARY KEY, mlnstr MULTILINESTRING NOT NULL SRID 0, SPATIAL INDEX(mlnstr));
39 CREATE TABLE multipolygon_geom(id INT PRIMARY KEY, mpoly MULTIPOLYGON NOT NULL SRID 0, SPATIAL INDEX(mpoly));
40 CREATE TABLE geometrycollection_geom(id INT PRIMARY KEY, geomcoll GEOMETRYCOLLECTION NOT NULL SRID 0, SPATIAL INDEX(geomcoll));
41 CREATE TABLE geom_data(id INT PRIMARY KEY , geom GEOMETRY NOT NULL SRID 0, SPATIAL INDEX(geom));
42
43 --echo # Insert different GEOMETRY data
44
45 INSERT INTO point_geom VALUES
46 (10,ST_GEOMFROMTEXT('POINT(0 0)')),
47 (11,ST_GEOMFROMTEXT('POINT(6 4)')),
48 (12,ST_GEOMFROMTEXT('POINT(3 4)')),
49 (13,ST_GEOMFROMTEXT('POINT(5 5)')),
50 (14,ST_GEOMFROMTEXT('POINT(2 1)'));
51
52 INSERT INTO linestring_geom VALUES
53 (20,ST_GEOMFROMTEXT('LINESTRING(0 0,5 5,6 6)')),
54 (21,ST_GEOMFROMTEXT('LINESTRING(2 3,4 5)')),
55 (22,ST_GEOMFROMTEXT('LINESTRING(3 0,7 5,6 3,2 0)')),
56 (23,ST_GEOMFROMTEXT('LINESTRING(5 0,9 5,2 6)')),
57 (24,ST_GEOMFROMTEXT('LINESTRING(0 0,5 3,3 4,7 7,9 0)'));
58
59 INSERT INTO polygon_geom VALUES
60 (30,ST_GEOMFROMTEXT('POLYGON((0 0,6 7,8 8,3 9,0 0),(3 6,4 6,4 7,3 6))')),
61 (31,ST_GEOMFROMTEXT('POLYGON((1 2,5 4,9 9,1 9,1 2))')),
62 (32,ST_GEOMFROMTEXT('POLYGON((2 3,3 5,8 1,2 6,2 3))')),
63 (33,ST_GEOMFROMTEXT('POLYGON((8 0,2 7,5 6,9 5,8 0))')),
64 (34,ST_GEOMFROMTEXT('POLYGON((5 2,5 10,10 10,10 2,5 2),(7 5,9 5,8 8,6 9,7 5))'));
65
66 INSERT INTO multipoint_geom VALUES
67 (40,ST_GEOMFROMTEXT('MULTIPOINT(2 2,3 3,4 4)')),
68 (41,ST_GEOMFROMTEXT('MULTIPOINT(1 2,3 0,7 4,6 6)')),
69 (42,ST_GEOMFROMTEXT('MULTIPOINT(2 9,3 4,6 4,5 5,3 3)')),
70 (43,ST_GEOMFROMTEXT('MULTIPOINT(8 7,2 1,5 4,3 4,2 8,4 5)')),
71 (44,ST_GEOMFROMTEXT('MULTIPOINT(3 6,2 3,7 3)'));
72
73 INSERT INTO multilinestring_geom VALUES
74 (50,ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 3,4 5),(6 6,8 8,9 9,10 10))')),
75 (51,ST_GEOMFROMTEXT('MULTILINESTRING((2 3,5 6,4 4,1 1),(2 6,5 8,7 9,9 10))')),
76 (52,ST_GEOMFROMTEXT('MULTILINESTRING((0 1,6 3,7 5),(6 2,5 8,6 9))')),
77 (53,ST_GEOMFROMTEXT('MULTILINESTRING((5 5,3 7,7 8),(2 2,6 9,10 9),(1 2,6 7,9 9))')),
78 (54,ST_GEOMFROMTEXT('MULTILINESTRING((0 1,5 3,8 5),(7 6,9 8,10 9,10 11))'));
79
80 INSERT INTO multipolygon_geom VALUES
81 (60,ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((2 2,4 5,6 2,2 2)))')),
82 (61,ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 6,6 6,6 0,0 0)),((2 1,2 3,5 3,5 1,2 1)))')),
83 (62,ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 7,7 7,7 0,0 0)),((3 3,5 6,7 3,3 3)),((2 6,5 8,8 6,2 6)))')),
84 (63,ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 8,8 8,8 0,0 0)),((0 0,0 2,2 2,2 0,0 0)))')),
85 (64,ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 9,9 9,9 0,0 0)),((1 1,1 3,3 3,3 1,1 1)))'));
86
87 INSERT INTO geometrycollection_geom VALUES
88 (70,ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
89 'POINT(0 0),'
90 'LINESTRING(0 0,10 10),'
91 'POLYGON((0 0,0 10,10 10,10 0, 0 0)),'
92 'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
93 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
94 'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))')),
95 (71,ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
96 'POINT(4 5),'
97 'LINESTRING(0 0,10 10,11 11),'
98 'POLYGON((0 0,0 8,8 8,8 0, 0 0)),'
99 'MULTIPOINT(0 0,4 4,6 4,8 6,9 9,12 12),'
100 'MULTILINESTRING((0 0,11 11),(0 8,1 0)),'
101 'MULTIPOLYGON(((0 0,0 9,9 9,9 0,0 0)),((1 1,4 4,4 1,1 1))))')),
102 (72,ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
103 'POINT(0 0),'
104 'LINESTRING(0 0,2 2,3 3,4 4),'
105 'POLYGON((0 0,0 5,5 5,5 0, 0 0)),'
106 'MULTIPOINT(0 5,1 6),'
107 'MULTILINESTRING((0 0,9 9,10 10),(0 1,1 0),(1 0,1 1,1 2,1 3,1 4)),'
108 'MULTIPOLYGON(((0 0,0 6,6 6,6 0,0 0)),((1 1,7 10,9 8,7 4,1 1))))'));
109
110 INSERT INTO geom_data SELECT * FROM point_geom;
111 INSERT INTO geom_data SELECT * FROM linestring_geom;
112 INSERT INTO geom_data SELECT * FROM polygon_geom;
113 INSERT INTO geom_data SELECT * FROM multipoint_geom;
114 INSERT INTO geom_data SELECT * FROM multilinestring_geom;
115 INSERT INTO geom_data SELECT * FROM multipolygon_geom;
116 INSERT INTO geom_data SELECT * FROM geometrycollection_geom;
117
118 --source include/sync_slave_sql_with_master.inc
119 --echo [Connection Slave]
120
121 --echo # Check whether data was inserted on the slave
122 SELECT id, ST_ASTEXT(geom) FROM geom_data;
123 SELECT COUNT(*) FROM geom_data;
124 SELECT ST_AREA(geom) FROM geom_data WHERE ST_GEOMETRYTYPE(geom) = 'POLYGON';
125
126 --let $diff_tables = master:geom_data, slave:geom_data
127 --source include/diff_tables.inc
128
129 #############################################################################################
130 # Create views on the LINESTRING and GEOMETRYCOLLECTION types #
131 #############################################################################################
132
133 --echo [Connection Master]
134 --connection master
135
136 --echo # Create a view on LINESTRING type
137 CREATE VIEW linestring_view
138 AS SELECT ST_NUMPOINTS(geom) AS numpoints, ST_LENGTH(geom) AS length
139 FROM geom_data WHERE ST_GEOMETRYTYPE(geom) = 'LINESTRING';
140
141 --echo # Create a view on GEOMETRYCOLLECTION type
142 CREATE VIEW geomcollection_view
143 AS SELECT ST_NUMGEOMETRIES(geom) as numgeometries, ST_GEOMETRYN(geom,3) as nthgeom
144 FROM geom_data WHERE ST_GEOMETRYTYPE(geom) = 'GEOMETRYCOLLECTION';
145
146 --source include/sync_slave_sql_with_master.inc
147 --echo [Connection Slave]
148
149 --echo # Check whether views were created on slave
150 SELECT * FROM linestring_view;
151 SELECT numgeometries, ST_ASTEXT(nthgeom) FROM geomcollection_view;
152
153 --let $diff_tables = master:geom_data, slave:geom_data
154 --source include/diff_tables.inc
155
156 #############################################################################################
157 # Drop the views #
158 #############################################################################################
159
160 --echo [Connection Master]
161 --connection master
162
163 --echo # Drop the views
164 DROP VIEW linestring_view;
165 DROP VIEW geomcollection_view;
166
167 --source include/sync_slave_sql_with_master.inc
168 --echo [Connection Slave]
169
170 --echo # Check whether views are dropped on slave
171 --error ER_NO_SUCH_TABLE
172 SELECT * FROM linestring_view;
173
174 --error ER_NO_SUCH_TABLE
175 SELECT * FROM geomcollection_view;
176
177 #############################################################################################
178 # Update a row in the table #
179 #############################################################################################
180
181 --echo [Connection Master]
182 --connection master
183
184 --echo # Update a row in the table
185 UPDATE geom_data SET geom = ST_GEOMFROMTEXT('POINT(4 5)') WHERE id = 10;
186
187 --source include/sync_slave_sql_with_master.inc
188 --echo [Connection Slave]
189
190 --echo # Check whether row is updated on slave
191 SELECT id, ST_ASTEXT(geom) FROM geom_data WHERE id = 10;
192
193 --let $diff_tables = master:geom_data, slave:geom_data
194 --source include/diff_tables.inc
195
196 #############################################################################################
197 # Delete a row from the table #
198 #############################################################################################
199
200 --echo [Connection Master]
201 --connection master
202
203 --echo # Delete a row from the table
204 DELETE FROM geom_data WHERE id = 13;
205
206 --source include/sync_slave_sql_with_master.inc
207 --echo [Connection Slave]
208
209 --echo # Check whether row is deleted on slave
210 SELECT COUNT(geom) FROM geom_data;
211
212 --let $diff_tables = master:geom_data, slave:geom_data
213 --source include/diff_tables.inc
214
215 #############################################################################################
216 # Drop the spatial index from the table #
217 #############################################################################################
218
219 --echo [Connection Master]
220 --connection master
221
222 --echo # Drop the spatial index from the table
223 ALTER TABLE geom_data DROP INDEX geom;
224
225 --source include/sync_slave_sql_with_master.inc
226 --echo [Connection Slave]
227
228 --echo # Check whether spatial index is dropped on slave
229 SHOW CREATE TABLE geom_data;
230
231 #############################################################################################
232 # Add spatial index to the column of GEOMETRY data type #
233 #############################################################################################
234
235 --echo [Connection Master]
236 --connection master
237
238 --echo # Add spatial index to the column of GEOMETRY data type
239 ALTER TABLE geom_data ADD SPATIAL INDEX(geom);
240
241 --source include/sync_slave_sql_with_master.inc
242 --echo [Connection Slave]
243
244 --echo # Check whether spatial index is added on slave
245 SHOW CREATE TABLE geom_data;
246
247 #############################################################################################
248 # Add a column of GEOMETRY data type to the table and populate the column #
249 #############################################################################################
250
251 --echo [Connection Master]
252 --connection master
253
254 --echo # Add a column of GEOMETRY data type
255 ALTER TABLE geom_data ADD COLUMN geom1 GEOMETRY;
256
257 --echo # Insert values into the column
258 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POINT(2 3)') WHERE id = 10;
259 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POINT(0 0)') WHERE id = 11;
260 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POINT(9 9)') WHERE id = 12;
261 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POINT(8 7)') WHERE id = 14;
262
263 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('LINESTRING(0 0,1 1,2 2,3 3)') WHERE id = 20;
264 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('LINESTRING(5 5,6 7,2 3,9 9)') WHERE id = 21;
265 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('LINESTRING(6 6,8 9,6 7,10 10)') WHERE id = 22;
266 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('LINESTRING(7 7,5 5,7 8,4 5)') WHERE id = 23;
267 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('LINESTRING(9 9,1 6,0 0,2 3)') WHERE id = 24;
268
269 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POLYGON((0 0,0 6,6 6,6 0,0 0))') WHERE id = 30;
270 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POLYGON((0 0,6 6,6 0,0 0))') WHERE id = 31;
271 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POLYGON((0 0,0 7,7 7,7 0,0 0))') WHERE id = 32;
272 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POLYGON((1 1,1 6,6 6,6 1,1 1))') WHERE id = 33;
273 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('POLYGON((2 2,2 8,8 8,8 2,2 2))') WHERE id = 34;
274
275 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOINT(3 4,5 6,10 10,4 4)') WHERE id = 40;
276 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOINT(0 0,2 2,3 3)') WHERE id = 41;
277 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOINT(5 5,5 6,5 7,5 8)') WHERE id = 42;
278 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOINT(7 8,2 3,5 6)') WHERE id = 43;
279 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOINT(6 7,3 4,2 5,7 8,9 9)') WHERE id = 44;
280
281 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTILINESTRING((5 6,8 8,9 9),(0 0,3 3,6 7))') WHERE id = 50;
282 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTILINESTRING((5 6,8 8,9 9),(0 0,3 3,6 7),(1 1,5 5))') WHERE id = 51;
283 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTILINESTRING((6 7,9 9,10 10),(0 0,3 3,6 7))') WHERE id = 52;
284 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTILINESTRING((7 6,7 8,7 9),(1 1,4 4,7 7))') WHERE id = 53;
285 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTILINESTRING((4 6,9 8,7 9),(3 0,3 3,3 7))') WHERE id = 54;
286
287 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 3,3 3,3 0,0 0)),((3 7,6 9,9 7,3 7)))') WHERE id = 60;
288 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1,1 4,4 4,4 1,1 1)),((4 7,7 9,10 7,4 7)))') WHERE id = 61;
289 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOLYGON(((2 2,2 5,5 5,5 2,2 2)),((5 6,7 10,9 7,5 6)),((0 0,5 5,5 0,0 0)))') WHERE id = 62;
290 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOLYGON(((3 3,3 6,6 6,6 3,3 3)),((0 0,0 2,2 2,2 0,0 0)))') WHERE id = 63;
291 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('MULTIPOLYGON(((4 4,4 7,7 7,7 4,4 4)),((5 7,6 9,9 7,5 7)))') WHERE id = 64;
292
293 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
294 'POINT(6 7),'
295 'LINESTRING(4 4,5 5,8 8,10 10),'
296 'POLYGON((3 3,3 6,6 6,6 3,3 3)),'
297 'MULTIPOINT(5 5,7 8,9 10,10 10),'
298 'MULTILINESTRING((2 2,3 4,5 5),(5 6,7 7,7 8,9 9)),'
299 'MULTIPOLYGON(((4 4,4 7,7 7,7 4,4 4)),((3 3,5 6,7 3,3 3))))')
300 WHERE id = 70;
301 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
302 'POINT(7 8),'
303 'LINESTRING(5 5,6 6,9 9,10 10),'
304 'POLYGON((0 0,0 8,8 8,8 0,0 0)),'
305 'MULTIPOINT(7 5,7 8,7 10,7 11),'
306 'MULTILINESTRING((2 1,3 6,9 5),(9 6,8 7,7 6,5 9)),'
307 'MULTIPOLYGON(((0 0,0 4,4 4,4 0,0 0)),((6 3,5 6,7 3,6 3))))')
308 WHERE id = 71;
309 UPDATE geom_data SET geom1 = ST_GEOMFROMTEXT('GEOMETRYCOLLECTION('
310 'POINT(9 6),'
311 'LINESTRING(7 8,7 9,7 10),'
312 'POLYGON((4 5,6 7,9 5,4 5)),'
313 'MULTIPOINT(5 0,6 0),'
314 'MULTILINESTRING((4 3,4 7,4 8),(4 4,5 6,7 6,8 8,10 10)),'
315 'MULTIPOLYGON(((7 7,7 10,10 10,10 7,7 7)),((3 4,5 7,7 4,3 4))))')
316 WHERE id = 72;
317
318 --source include/sync_slave_sql_with_master.inc
319 --echo [Connection Slave]
320
321 --echo # Check whether the column is added and populated on slave
322 SHOW CREATE TABLE geom_data;
323 SELECT id, ST_ASTEXT(geom1), ST_GEOMETRYTYPE(geom1), ST_DIMENSION(geom1) FROM geom_data;
324
325 --let $diff_tables = master:geom_data, slave:geom_data
326 --source include/diff_tables.inc
327
328 #############################################################################################
329 # Drop a column from the table #
330 #############################################################################################
331
332 --echo [Connection Master]
333 --connection master
334
335 --echo # Drop a column from the table
336
337 ALTER TABLE geom_data DROP COLUMN geom1;
338
339 --source include/sync_slave_sql_with_master.inc
340 --echo [Connection Slave]
341
342 --echo # Check whether column is dropped on slave
343 SHOW CREATE TABLE geom_data;
344
345 #############################################################################################
346 # Drop all the tables #
347 #############################################################################################
348
349 --echo [Connection Master]
350 --connection master
351
352 --echo # Drop all the tables
353
354 DROP TABLE point_geom;
355 DROP TABLE linestring_geom;
356 DROP TABLE polygon_geom;
357 DROP TABLE multipoint_geom;
358 DROP TABLE multilinestring_geom;
359 DROP TABLE multipolygon_geom;
360 DROP TABLE geometrycollection_geom;
361 DROP TABLE geom_data;
362
363 --source include/sync_slave_sql_with_master.inc
364 --echo [Connection Slave]
365
366 --echo # Check whether table is dropped on slave
367 --error ER_NO_SUCH_TABLE
368 SELECT COUNT(id) FROM geom_data;
369
370 --source include/rpl_end.inc