Debian Codegraph @

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

raw
1 #############################################################################
2 #
3 # The aim of this test case is to test the
4 # "CREATE/DROP SPATIAL REFERENCE SYSTEM" statements to manipulate the
5 # spatial reference systems in the data dictionary table.
6 #
7 # E.g:
8 # CREATE SPATIAL REFERENCE SYSTEM XXXX ...
9 # CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS XXXX
10 # CREATE OR REPLACE REFERENCE SYSTEM XXXX ...
11 #
12 # DROP SPATIAL REFERENCE SYSTEM XXXX
13 # DROP SPATIAL REFERENCE SYSTEM IF EXISTS XXXX
14 #
15 # The following scenarios are tested :
16 #
17 # 1. SRS created in master must replicate properly and should be
18 # available for slave server.
19 #
20 # 2. SRS dropped in master shouldn't be available for slave
21 #
22 # Creation date: 2017-12-05
23 # Author: Pavan Naik
24 #
25 #############################################################################
26
27 --source include/master-slave.inc
28
29 --connection master
30
31 # Store the number of SRSs. Compare at the end to detect if we create an SRS and
32 # forget to drop it afterwards.
33 SET @number_of_srss =
34 (SELECT COUNT(*) FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS);
35
36 # Create SRS's to drop.
37 CREATE SPATIAL REFERENCE SYSTEM 1000000000
38 NAME 'foo_1000000000'
39 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"]]';
40
41 CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000001
42 NAME 'foo_1000000001'
43 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"]]';
44
45 CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1000000002
46 NAME 'foo_1000000002'
47 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"]]';
48
49 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
50
51 --source include/sync_slave_sql_with_master.inc
52 --echo [Connection Slave]
53
54 # Check whether SRS created in master is available on slave server(should exist)
55 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
56 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
57 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
58 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
59
60 --echo [Connection Master]
61 --connection master
62
63 # Drop the created SRS
64 DROP SPATIAL REFERENCE SYSTEM 1000000000;
65 DROP SPATIAL REFERENCE SYSTEM 1000000001;
66
67 # Replace an exsisting SRS
68 CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000002
69 NAME 'foo_1000000000'
70 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"]]';
71
72 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 1000000002;
73
74 --source include/sync_slave_sql_with_master.inc
75 --echo [Connection Slave]
76
77 # Check whether SRS modified in master is available on slave server(should exist)
78 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID = 1000000002;
79 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
80
81 --echo [Connection Master]
82 --connection master
83
84 # Drop the created SRS
85 DROP SPATIAL REFERENCE SYSTEM IF EXISTS 1000000002;
86
87 # Result should be empty
88 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
89
90 --source include/sync_slave_sql_with_master.inc
91 --echo [Connection Slave]
92
93 # Check whether the SRS dropped on master is available on slave server(shouldn't exist)
94 SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID = 1000000000;
95 --error ER_SRS_NOT_FOUND
96 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
97
98 --echo [Connection Master]
99 --connection master
100
101 # Both CREATE and DROP SPATIAL REFERENCE SYSTEM causes an implicit
102 # transaction commit.
103 # CREATE SPATIAL REFERENCE SYSTEM inside a transaction on master.
104 BEGIN;
105 CREATE SPATIAL REFERENCE SYSTEM 1000000000
106 NAME 'foo_1000000000'
107 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"]]';
108
109 CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 1000000001
110 NAME 'foo_1000000001'
111 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"]]';
112
113 CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1000000002
114 NAME 'foo_1000000002'
115 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"]]';
116
117 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
118
119 --source include/sync_slave_sql_with_master.inc
120 --echo [Connection Slave]
121
122 # Check whether SRS created in master is available on slave server(should exist)
123 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
124 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
125 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
126 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
127
128 --echo [Connection Master]
129 --connection master
130
131 COMMIT;
132
133 # DROP SPATIAL REFERENCE SYSTEM inside a transaction on master.
134 BEGIN;
135 DROP SPATIAL REFERENCE SYSTEM 1000000000;
136 DROP SPATIAL REFERENCE SYSTEM 1000000001;
137 DROP SPATIAL REFERENCE SYSTEM IF EXISTS 1000000002;
138
139 --source include/sync_slave_sql_with_master.inc
140 --echo [Connection Slave]
141
142 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
143
144 # Check whether SRS created in master is available on slave server(shouldn't exist)
145 SELECT * FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS WHERE SRS_ID >= 1000000000 AND SRS_ID <= 1000000002;
146
147 --error ER_SRS_NOT_FOUND
148 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000000));
149
150 --error ER_SRS_NOT_FOUND
151 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000001));
152
153 --error ER_SRS_NOT_FOUND
154 SELECT ST_SRID(ST_GeomFromText('POINT(0 0)', 1000000002));
155
156 --echo [Connection Master]
157 --connection master
158
159 COMMIT;
160
161 # Compare the number of SRSs with the number recorded at the top of the test to
162 # detect if we created an SRS and forgot to drop it afterwards.
163 SELECT @number_of_srss = COUNT(*) AS should_be_true
164 FROM INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS;
165
166 --source include/sync_slave_sql_with_master.inc
167 --source include/rpl_end.inc