WL#9059: CREATE/DROP SPATIAL REFERENCE SYSTEM

Affects: Server-8.0 — Status: Complete

Description
Requirements
Dependent Tasks
High Level Architecture
Low Level Design

This WL adds "CREATE/DROP SPATIAL REFERENCE SYSTEM" statements to manipulate the spatial reference systems in the data dictionary.

Examples:

CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS 1234
  NAME 'foo'
  DEFINITION 'PROJCS[...]';

DROP SPATIAL REFERENCE SYSTEM IF EXISTS 1234;

CREATE SPATIAL REFERENCE SYSTEM 12345
  NAME 'My SRS name'
  DEFINITION 'GEOGCS[...]'
  ORGANIZATION 'My organization name'
    IDENTIFIED BY 12345678
  DESCRIPTION 'This is an example of an SRS';

DROP SPATIAL REFERENCE SYSTEM 12345;

The user needs SUPER privileges to use these operations.

This WL also disables SELECT/INSERT/UPDATE/DELETE access to the mysql.st_spatial_reference_systems dictionary table.

The WL causes a dictionary upgrade to add an index to a DD table, rename an existing index, and add a default row to a dictionary table.

ALTER SPATIAL REFERENCE SYSTEM, originally part of this WL, has been moved out.

CREATE

F-1.1: If the CREATE statement syntax is incorrect, an error (i.e., ER_SYNTAX_ERROR or a more specific error) MUST be raised.
F-1.2: If an attribute is repeated (possibly with different parameters), ER_SRS_MULTIPLE_ATTRIBUTE_DEFINITIONS MUST be raised.
F-1.3: If the SRID is larger than 2^32-1 (max 32 bit uint), ER_DATA_OUT_OF_RANGE MUST be raised.
F-1.4: If the SRID is 0, ER_CANT_MODIFY_SRID_0 MUST be raised.
F-1.5: If the NAME or DEFINITION attribute is missing, ER_SRS_MISSING_MANDATORY_ATTRIBUTE MUST be raised.
F-1.6: If the NAME attribute is an empty string or starts or ends with whitespace, ER_SRS_NAME_CANT_BE_EMPTY_OR_WHITESPACE MUST be raised.
F-1.7: If the ORGANIZATION attribute is specified and is an empty string or starts or ends with whitespace, ER_SRS_ORGANIZATION_CANT_BE_EMPTY_OR_WHITESPACE MUST be raised.
F-1.8: If the organization SRS ID (organization_coordsys_id) is specified and is larger than 2^32-1 (max 32 bit uint), ER_DATA_OUT_OF_RANGE MUST be raised.
F-1.9: If the user does not have the SUPER privilege, ER_CMD_NEED_SUPER MUST be raised.
F-1.10: If the SRID already exists and IF NOT EXISTS is specified, the statement MUST raise ER_WARN_SRS_ID_ALREADY_EXISTS and finish successfully without any effect on the dictionary.
F-1.11: If the SRID already exists and OR REPLACE is specified, the existing SRS is overwritten with the new information.
F-1.12: If the SRID already exists and neither IF NOT EXISTS nor REPLACE is specified, ER_SRS_ID_ALREADY_EXISTS MUST be raised.
F-1.13: If the DEFINITION attribute does not contain a syntactically valid SRS definition, ER_SRS_PARSE_ERROR MUST be raised.
F-1.14: If the DEFINITION attribute is syntactically valid projected SRS definition, but is missing a necessary projection parameter, ER_SRS_PROJ_PARAMETER_MISSING MUST be raised.
F-1.15: If the SRID is in one of the ranges [0, 32767], [60 000 000, 69 999 999] or [2 000 000 000, 2 147 483 647], ER_WARN_RESERVED_SRID_RANGE MUST be raised.¹
F-1.16: If the NAME attribute is the name of an already existing SRS, ER_DUP_ENTRY MUST be raised.
F-1.17: If the combination of ORGANIZATION attribute and organization SRS ID (organization_coordsys_id) is used by an already existing SRS, ER_DUP_ENTRY MUST be raised.
F-1.18: If any of the optional parameters (ORGANIZATION, organization SRS ID (organization_coordsys_id) and DESCRIPTION) are not specified, their values in the dictionary are set to NULL.
F-1.19: A CREATE statement causes an implicit transaction commit, similar to other types of CREATE statements.
F-1.20: If any of the parameters are longer than the allowed length in the data dictionary (80 characters for NAME, 4096 characters for DEFINITION, 256 characters for ORGANIZATION, and 2048 characters for DESCRIPTION), ER_SRS_ATTRIBUTE_STRING_TOO_LONG MUST be raised.
F-1.21: If trying to CREATE OR REPLACE an SRS with an SRID that is currently used by a column (i.e., the column has a SRID restriction referencing the SRID), ER_CANT_MODIFY_SRS_USED_BY_COLUMN MUST be raised.
F-1.22: If any string parameter (NAME, DEFINITION, ORGANIZATION, DESCRIPTION) contains a control character, ER_SRS_INVALID_CHARACTER_IN_ATTRIBUTE MUST be raised.

DROP

F-2.1: If the DROP statement syntax is incorrect, an error (i.e., ER_SYNTAX_ERROR or a more specific error) MUST be raised.
F-2.2: If the SRID is larger than 2^32-1 (max 32 bit uint), ER_DATA_OUT_OF_RANGE MUST be raised.
F-2.3: If the SRID is 0, ER_CANT_MODIFY_SRID_0 MUST be raised.
F-2.4: If the user does not have the SUPER privilege, ER_CMD_NEED_SUPER MUST be raised.
F-2.5: If the SRID does not exists and IF EXISTS is specified, ER_WARN_SRS_NOT_FOUND MUST be raised.
F-2.6: If the SRID does not exist and IF EXISTS is not specified, ER_SRS_NOT_FOUND MUST be raised.
F-2.7: If the SRID is in one of the ranges [0, 32767], [60 000 000, 69 999 999] or [2 000 000 000, 2 147 483 647], ER_WARN_RESERVED_SRID_RANGE MUST be raised.¹
F-2.8: A DROP statement causes an implicit transaction commit, similar to other types of DROP statements.
F-2.9: If trying to drop an SRS with an SRID that is currently used by a column (i.e., the column has a SRID restriction referencing the SRID), ER_CANT_MODIFY_SRS_USED_BY_COLUMN MUST be raised.

DEFINITION_SCHEMA and INFORMATION_SCHEMA

F-3.1 a): It MUST NOT be possible to do INSERT/UPDATE/DELETE on the mysql.st_spatial_reference_systems table or the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS view. It MUST NOT be possible to do SELECT on the mysql.st_spatial_reference-systems table.

¹ The two first ranges are reserved by EPSG (cf. OGP Publication 373-7-1 Geomatics Guidance Note number 7, part 1 - August 2012, Sect. 5.9), the last one by MySQL (matching a similar reservation by SQL Anywhere, cf. CREATE SRS documentation in SQL Anywhere 12).

WL#8579: Spatial Reference Systems
WL#8592: SRID type modifier for geometric types

I-1: No new files.
I-2: Interface SQL01 is extended with new DDL statements to create, alter and drop spatial reference systems.
I-3: No new commands.
I-4: No new tools.
I-5: Changes to existing functionality: SELECT/INSERT/UPDATE/DELETE on table mysql.st_spatial_reference_systems becomes illegal.
I-6: Interface ERR01 is extended with 9 new error messages:
ER_SRS_MULTIPLE_ATTRIBUTE_DEFINITIONS, SQLSTATE SR006, "Multiple definitions of attribute %s."
ER_CANT_MODIFY_SRID_0, SQLSTATE SR000, "SRID 0 is not modifiable."
ER_SRS_MISSING_MANDATORY_ATTRIBUTE, SQLSTATE SR006, "Missing mandatory attribute %s."
ER_SRS_NAME_CANT_BE_EMPTY_OR_WHITESPACE, SQLSTATE SR006, "The spatial reference system name can't be an empty string or start or end with whitespace."
ER_SRS_ORGANIZATION_CANT_BE_EMPTY_OR_WHITESPACE, SQLSTATE SR006, "The organization name can't be an empty string or start or end with whitespace."
ER_SRS_ID_ALREADY_EXISTS, SQLSTATE SR004, "There is already a spatial reference system with SRID %u."
ER_CANT_MODIFY_SRS_USED_BY_COLUMN<, SQLSTATE SR005, "Can't modify SRID %u. There is at least one column depending on it."
ER_SRS_ATTRIBUTE_STRING_TOO_LONG, SQLSTATE SR006, "Attribute %s is too long. The maximum length is %u characters."
ER_SRS_INVALID_CHARACTER_IN_ATTRIBUTE, SQLSTATE SR006, "Invalid character in attribute %s."

Interface ERR01 is extended with 2 new warnings:
ER_WARN_RESERVED_SRID_RANGE, SQLSTATE 01S01, "The SRID range [%u, %u] has been reserved for system use. SRSs in this range may be added, modified or removed without warning during upgrade."
ER_WARN_SRS_ID_ALREADY_EXISTS, SQLSTATE 01S00, "There is already a spatial reference system with SRID %u."

Interface ERR01 is modified by changing the text of warning ER_WARN_SRS_NOT_FOUND to "There's no spatial reference system with SRID %u."

Behavior
- SRID 0
- Removing/replacing SRSs that are in use
Grammar
DD table mysql.st_spatial_reference_systems
- mysqld --initialize and mysql_upgrade

Behavior

SRID 0

SRID 0 is special. It has no valid definition, and the entry in the DD will never be read, other than to verify foreign key constraints in other DD tables.

Currently, an entry for SRID 0 is inserted (through an INSERT statement) on initialization and upgrade. This is changed to be done in code during data dictionary initialization. The entry can't be replaced or removed by CREATE OR REPLACE or DROP statements.

Removing/replacing SRSs that are in use

The user is not allowed to change the definition of, or drop, an SRS that is in use by a column, i.e., the column type has an SRID type modifier referencing the SRS.

SRIDs on columns are defined as foreign keys into the SRS table. However, the data dictionary currently doesn't enforce foreign key constraints. Therefore, a query is run through Ed_connection to check if there are any I_S.ST_GEOMETRY_COLUMNS entries for the SRID.

Grammar

create_statement = CREATE OR REPLACE SPATIAL REFERENCE SYSTEM <srid> <srs_attributes>*
                 | CREATE SPATIAL REFERENCE SYSTEM [ IF NOT EXISTS ] <srid> <srs_attributes>*

srid = unsigned 32 bit integer

srs_attributes = NAME <srs_name>
               | DEFINITION <definition>
               | ORGANIZATION <org_name> IDENTIFIED BY <srid>
               | DESCRIPTION <description>

srs_name = TEXT_STRING_sys_nonewline

definition = TEXT_STRING_sys_nonewline

org_name = TEXT_STRING_sys_nonewline

description = TEXT_STRING_sys_nonewline

drop_statement = DROP SPATIAL REFERENCE SYSTEM [ IF EXISTS ] <srid>

DD table mysql.st_spatial_reference_systems

The table mysql.st_spatial_reference_systems can currently be modified by the superuser. This WL removes that possibility, and all modifications have to be done through CREATE/DROP SPATIAL REFERENCE SYSTEM. The contents can be inspected through the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS view.

A new index is added to the table: UNIQUE KEY ORGANIZATION_AND_ID (catalog_id, organization, organization_coordsys_id). This will enforce one of the check constraints defined in SQL/MM Part 3, Sect. 20.3.

The existing index on the name column will enforce the unique NAME attribute constraint. The index is renamed to give a more understandable error message when the unique constraint is violated.

SRID 0 is inserted by code instead of through INSERT or CREATE statements.

mysqld --initialize and mysql_upgrade

Since INSERT is forbidden, mysqld --initialize and mysql_upgrade must use CREATE instead of REPLACE INTO. The statements in scripts/mysql_system_tables_data.sql and scripts/mysql_system_tables_data_fix.sql are updated to address this.

SRID 0 is inserted programmatically on dictionary initialization.