Mercurial > gemma
changeset 2381:99274fed6f3d
Validate country and hectometre in ISRS code
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 22 Feb 2019 18:02:23 +0100 |
parents | 5f300bd7316f |
children | c28971cd70ce |
files | schema/gemma.sql schema/isrs.sql schema/tap_tests_data.sql |
diffstat | 3 files changed, 18 insertions(+), 11 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Feb 22 15:15:23 2019 +0100 +++ b/schema/gemma.sql Fri Feb 22 18:02:23 2019 +0100 @@ -83,13 +83,6 @@ -- -- Look-up tables with data that are static in a running system -- -CREATE TABLE countries ( - country_code char(2) PRIMARY KEY -- ISO 3166 country code - -- A smallint PK would require even less disk space i.e. on the FK side. - -- This might be an issue in case cache space becomes a limiting - -- factor when there are many FKs pointing here. -); - CREATE TABLE language_codes ( language_code varchar PRIMARY KEY );
--- a/schema/isrs.sql Fri Feb 22 15:15:23 2019 +0100 +++ b/schema/isrs.sql Fri Feb 22 18:02:23 2019 +0100 @@ -17,22 +17,36 @@ BEGIN; +CREATE TABLE countries ( + country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code +); + +CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean +AS $$ + SELECT EXISTS(SELECT 1 FROM countries WHERE country_code = code) +$$ + LANGUAGE sql + STABLE PARALLEL SAFE; + -- -- Types -- -- Composite type: UN/LOCODE, fairway section, object reference, hectometre. -- See RIS-Index Encoding Guide -CREATE TYPE isrs AS ( +CREATE TYPE isrs_base AS ( country_code char(2), -- ISO 3166 country code - -- could be validated against countries table. locode char(3), -- without the country code: -- http://www.unece.org/cefact/locode/welcome.html fairway_section char(5), orc char(5), -- Object Reference Code - hectometre int -- TODO: should be constrained to five digits + hectometre int ); +CREATE DOMAIN isrs AS isrs_base + CHECK (is_country((VALUE).country_code)) + CHECK ((VALUE).hectometre BETWEEN 0 AND 99999); + CREATE TYPE isrsrange AS RANGE ( subtype = isrs );
--- a/schema/tap_tests_data.sql Fri Feb 22 15:15:23 2019 +0100 +++ b/schema/tap_tests_data.sql Fri Feb 22 18:02:23 2019 +0100 @@ -16,7 +16,7 @@ -- Test data used in *_tests.sql scripts -- -INSERT INTO countries VALUES ('AT'), ('RO'); +INSERT INTO countries VALUES ('AT'), ('RO'), ('DE'); INSERT INTO users.responsibility_areas VALUES ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),