Mercurial > gemma
changeset 569:ad07846b09d1
Add function to construct isrs from text
In passing moved all related functionality to its own set of files.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 05 Sep 2018 11:05:58 +0200 |
| parents | 99968a7394f7 |
| children | 7575cf0e15ff |
| files | schema/gemma.sql schema/install-db.sh schema/isrs.sql schema/isrs_tests.sql schema/run_tests.sh |
| diffstat | 5 files changed, 76 insertions(+), 17 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Sep 05 10:43:05 2018 +0200 +++ b/schema/gemma.sql Wed Sep 05 11:05:58 2018 +0200 @@ -16,22 +16,6 @@ END; $$; --- Composite type: UN/LOCODE, fairway section, object reference, hectometre. --- See RIS-Index Encoding Guide -CREATE TYPE isrs 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), - object_reference char(5), - hectometre int -- should be constrained to five digits -); - -CREATE TYPE isrsrange AS RANGE ( - subtype = isrs -); - -- -- GEMMA data --
--- a/schema/install-db.sh Wed Sep 05 10:43:05 2018 +0200 +++ b/schema/install-db.sh Wed Sep 05 11:05:58 2018 +0200 @@ -101,6 +101,7 @@ psql -q -p "$port" -f "$BASEDIR/roles.sql" -d "$db" psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \ -c "SET client_min_messages TO WARNING;" \ + -f "$BASEDIR/isrs.sql" \ -f "$BASEDIR/gemma.sql" \ -f "$BASEDIR/manage_users.sql" \ -f "$BASEDIR/auth.sql"
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/isrs.sql Wed Sep 05 11:05:58 2018 +0200 @@ -0,0 +1,56 @@ +-- +-- Types and functions for ISRS location codes +-- + +BEGIN; + +-- +-- Types +-- + +-- Composite type: UN/LOCODE, fairway section, object reference, hectometre. +-- See RIS-Index Encoding Guide +CREATE TYPE isrs 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), + object_reference char(5), + hectometre int -- should be constrained to five digits +); + +CREATE TYPE isrsrange AS RANGE ( + subtype = isrs +); + +-- +-- Functions +-- + +CREATE OR REPLACE FUNCTION isrs_fromText( + isrs_text varchar + ) + RETURNS isrs +AS $$ +DECLARE + isrs_len CONSTANT int = 20; +BEGIN + IF char_length(isrs_text) <> isrs_len + THEN + RAISE 'ISRS location code must be % characters long', isrs_len + USING ERRCODE = 'invalid_parameter_value'; + ELSE + RETURN CAST(( + substring(isrs_text from 1 for 2), + substring(isrs_text from 3 for 3), + substring(isrs_text from 6 for 5), + substring(isrs_text from 10 for 5), + CAST(substring(isrs_text from 16 for 5) AS int)) + AS isrs); + END IF; +END; +$$ + LANGUAGE plpgsql; + +COMMIT;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/isrs_tests.sql Wed Sep 05 11:05:58 2018 +0200 @@ -0,0 +1,17 @@ +-- +-- pgTAP test script for ISRS location code types and functions +-- + +SELECT results_eq($$ + SELECT isrs_fromText('DEXXX039000000005023') + $$, + $$ + SELECT CAST(('DE', 'XXX', '03900', '00000', 5023) AS isrs) + $$, + 'Correct ISRS text input gives respective ISRS location code'); + +SELECT throws_ok($$ + SELECT isrs_fromText('DEUXXX039000000005023') + $$, + 22023, NULL, + 'ISRS text input needs to have correct length');
--- a/schema/run_tests.sh Wed Sep 05 10:43:05 2018 +0200 +++ b/schema/run_tests.sh Wed Sep 05 11:05:58 2018 +0200 @@ -16,7 +16,8 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(43)' \ + -c 'SELECT plan(45)' \ + -f isrs_tests.sql \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'