# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1536138358 -7200
# Node ID ad07846b09d15a6421b3e7c33c50be6a076a7666
# Parent  99968a7394f77bad34b3f6320ba54660f1c864e3
Add function to construct isrs from text

In passing moved all related functionality to its own set of files.

diff -r 99968a7394f7 -r ad07846b09d1 schema/gemma.sql
--- 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
 --
diff -r 99968a7394f7 -r ad07846b09d1 schema/install-db.sh
--- 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"
diff -r 99968a7394f7 -r ad07846b09d1 schema/isrs.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;
diff -r 99968a7394f7 -r ad07846b09d1 schema/isrs_tests.sql
--- /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');
diff -r 99968a7394f7 -r ad07846b09d1 schema/run_tests.sh
--- 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()'