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