From a9ef3a229c6fef5ef8a05ba521a0237f2ffa6aa6 Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Thu, 6 Dec 2018 16:23:07 +0100 Subject: Coordinate operation search: add a authority_to_authority_preference table to restrict and prioritize searches --- data/sql/proj_db_table_defs.sql | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index 553dab38..c63637cf 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -1048,3 +1048,12 @@ CREATE VIEW authority_list AS UNION SELECT DISTINCT auth_name FROM coordinate_operation_view ; + +-- Define the allowed authorities, and their precedence, when researching a +-- coordinate operation +CREATE TABLE authority_to_authority_preference( + source_auth_name TEXT NOT NULL, -- 'any' for any source + target_auth_name TEXT NOT NULL, -- 'any' for any target + allowed_authorities TEXT NOT NULL, -- for example 'PROJ,EPSG,any' + CONSTRAINT unique_authority_to_authority_preference UNIQUE (source_auth_name, target_auth_name) +); -- cgit v1.2.3 From f06045c2f0145ec2290913fa144cd690e70736fd Mon Sep 17 00:00:00 2001 From: Even Rouault Date: Thu, 6 Dec 2018 21:28:16 +0100 Subject: Add API to retrieve non-deprecated equivalent of an object --- data/sql/proj_db_table_defs.sql | 24 ++++++++++++++++++++++++ 1 file changed, 24 insertions(+) (limited to 'data/sql/proj_db_table_defs.sql') diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index c63637cf..d403f0eb 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -946,6 +946,30 @@ FOR EACH ROW BEGIN END; +CREATE TABLE deprecation( + table_name TEXT NOT NULL CHECK (table_name IN ( + 'unit_of_measure', 'celestial_body', 'ellipsoid', + 'area', 'prime_meridian', 'geodetic_datum', 'vertical_datum', 'geodetic_crs', + 'projected_crs', 'vertical_crs', 'compound_crs', 'conversion', 'grid_transformation', + 'helmert_transformation', 'other_transformation', 'concatenated_operation')), + deprecated_auth_name TEXT NOT NULL, + deprecated_code TEXT NOT NULL, + replacement_auth_name TEXT NOT NULL, + replacement_code TEXT NOT NULL, + source TEXT +); + +CREATE TRIGGER deprecation_insert_trigger +BEFORE INSERT ON deprecation +FOR EACH ROW BEGIN + SELECT RAISE(ABORT, 'insert on deprecation violates constraint: deprecated entry refers to unexisting code') + WHERE NOT EXISTS (SELECT 1 FROM object_view o WHERE o.table_name = NEW.table_name AND o.auth_name = NEW.deprecated_auth_name AND o.code = NEW.deprecated_code); + + SELECT RAISE(ABORT, 'insert on deprecation violates constraint: replacement entry refers to unexisting code') + WHERE NOT EXISTS (SELECT 1 FROM object_view o WHERE o.table_name = NEW.table_name AND o.auth_name = NEW.replacement_auth_name AND o.code = NEW.replacement_code); +END; + + CREATE VIEW coordinate_operation_view AS SELECT 'grid_transformation' AS table_name, auth_name, code, name, -- cgit v1.2.3