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