171 lines
7.6 KiB
Plaintext
171 lines
7.6 KiB
Plaintext
#############################################################################
|
|
#
|
|
# 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
|
|
|
|
--connection master
|
|
--source suite/xengine/include/check_xengine_log_error.inc
|