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
|
#############################################################################
#
# The aim of this test case is to test the
# "CREATE/DROP SPATIAL REFERENCE SYSTEM" statements to manipulate the
# spatial reference systems in the data dictionary table.
#
# E.g:
# CREATE SPATIAL REFERENCE SYSTEM XXXX ...
# CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS XXXX
# CREATE OR REPLACE REFERENCE SYSTEM XXXX ...
#
# DROP SPATIAL REFERENCE SYSTEM XXXX
# DROP SPATIAL REFERENCE SYSTEM IF EXISTS XXXX
#
# The following scenarios are tested :
#
# 1. SRS created in master must replicate properly and should be
# available for slave server.
#
# 2. SRS dropped in master shouldn't be available for slave
#
# Creation date: 2017-12-05
# Author: Pavan Naik
#
#############################################################################
--source include/master-slave.inc
--connection master
# Store the number of SRSs. Compare at the end to detect if we create an SRS and
# forget to drop it afterwards.
SET @number_of_srss =
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS);
# Create SRS's to drop.
CREATE SPATIAL REFERENCE SYSTEM 1000000000
NAME 'foo_1000000000'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000001
NAME 'foo_1000000001'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1000000002
NAME 'foo_1000000002'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check whether SRS created in master is available on slave server(should exist)
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
--echo [Connection Master]
--connection master
# Drop the created SRS
DROP SPATIAL REFERENCE SYSTEM 1000000000;
DROP SPATIAL REFERENCE SYSTEM 1000000001;
# Replace an exsisting SRS
CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000002
NAME 'foo_1000000000'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 1000000002;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check whether SRS modified in master is available on slave server(should exist)
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 1000000002;
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
--echo [Connection Master]
--connection master
# Drop the created SRS
DROP SPATIAL REFERENCE SYSTEM IF EXISTS 1000000002;
# Result should be empty
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check whether the SRS dropped on master is available on slave server(shouldn't exist)
SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID = 1000000000;
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
--echo [Connection Master]
--connection master
# Both CREATE and DROP SPATIAL REFERENCE SYSTEM causes an implicit
# transaction commit.
# CREATE SPATIAL REFERENCE SYSTEM inside a transaction on master.
BEGIN;
CREATE SPATIAL REFERENCE SYSTEM 1000000000
NAME 'foo_1000000000'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000001
NAME 'foo_1000000001'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1000000002
NAME 'foo_1000000002'
DEFINITION 'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],AUTHORITY["EPSG","4326"]]';
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
# Check whether SRS created in master is available on slave server(should exist)
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
--echo [Connection Master]
--connection master
COMMIT;
# DROP SPATIAL REFERENCE SYSTEM inside a transaction on master.
BEGIN;
DROP SPATIAL REFERENCE SYSTEM 1000000000;
DROP SPATIAL REFERENCE SYSTEM 1000000001;
DROP SPATIAL REFERENCE SYSTEM IF EXISTS 1000000002;
--source include/sync_slave_sql_with_master.inc
--echo [Connection Slave]
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
# Check whether SRS created in master is available on slave server(shouldn't exist)
SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
--error ER_SRS_NOT_FOUND
SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
--echo [Connection Master]
--connection master
COMMIT;
# Compare the number of SRSs with the number recorded at the top of the test to
# detect if we created an SRS and forgot to drop it afterwards.
SELECT @number_of_srss = COUNT(*) AS should_be_true
FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;
--source include/sync_slave_sql_with_master.inc
--source include/rpl_end.inc
|
