[PATCH 4 of 5 saem_ref] [ark] Add PL/pgSQL function to generate qualified ARK identifier

Denis Laxalde denis.laxalde at logilab.fr
Wed Feb 14 17:26:11 CET 2018


# HG changeset patch
# User Denis Laxalde <denis.laxalde at logilab.fr>
# Date 1518453568 -3600
#      Mon Feb 12 17:39:28 2018 +0100
# Node ID b19f5bb5f1a93835b5522d586da88868de31c287
# Parent  f584d40d5f5a3caeded786b72d831f068a1ca00d
# Available At http://hg.logilab.org/review/cubes/saem_ref
#              hg pull http://hg.logilab.org/review/cubes/saem_ref -r b19f5bb5f1a9
# EXP-Topic ark/qualifiers
[ark] Add PL/pgSQL function to generate qualified ARK identifier

Essentially, we add a gen_qualified_ark function taking an existing ARK
record with specified "name" attribute and produces a new ARK record
with a randomly generated "qualifier" and specified "name".

Related to extranet #46880051.

diff --git a/cubicweb_saem_ref/schema/ark.postgres.sql b/cubicweb_saem_ref/schema/ark.postgres.sql
--- a/cubicweb_saem_ref/schema/ark.postgres.sql
+++ b/cubicweb_saem_ref/schema/ark.postgres.sql
@@ -4,8 +4,8 @@ CREATE TABLE ark (
     CONSTRAINT qualified_name PRIMARY KEY (name, qualifier)
 );
 
--- Return a part of an ARK identifier of `len` length with `prefix` prefix and
--- `control_char` trailing character.
+-- Return the "name" part of an ARK identifier of `len` length with `prefix`
+-- prefix and `control_char` trailing character.
 CREATE OR REPLACE
 FUNCTION gen_ark_name(len INTEGER, prefix TEXT, control_char TEXT)
 RETURNS text AS $$
@@ -41,6 +41,43 @@ BEGIN
 END;
 $$ language 'plpgsql';
 
+-- Return a "qualifier" part of an ARK identifier of `len` length.
+-- TODO: factor out common bits with gen_ark_name()
+CREATE OR REPLACE
+FUNCTION gen_ark_qualifier(len INTEGER)
+RETURNS text AS $$
+DECLARE
+    qualifier TEXT;
+    letters TEXT;
+    numbers TEXT;
+    characters TEXT;
+    next_char TEXT;
+    tail TEXT;
+BEGIN
+    ASSERT len > 0;
+    letters := 'bcdfghjkmnpqrstvwxz';
+    numbers := '0123456789';
+    characters := letters || numbers;
+    -- Prefix ARK identifier.
+    qualifier := '';
+    LOOP
+        tail := substring(qualifier, length(qualifier) - 2, 3);
+        -- No more than 3 consecutive consonants.
+        IF tail ~ '([a-z]){3}' THEN
+            next_char := random_char(numbers);
+        ELSE
+            next_char := random_char(characters);
+        END IF;
+        qualifier := qualifier || next_char;
+        IF length(qualifier) = len THEN
+            EXIT;
+        END IF;
+    END LOOP;
+    -- Add control character.
+    RETURN qualifier;
+END;
+$$ language 'plpgsql';
+
 -- Return an random character within given string
 CREATE OR REPLACE FUNCTION random_char(chars TEXT) RETURNS text AS $$
 BEGIN
@@ -71,3 +108,34 @@ BEGIN
     END LOOP;
 END;
 $$ LANGUAGE plpgsql;
+
+-- Insert a record in "ark" table from a "base" identifier using a qualifier.
+CREATE OR REPLACE
+FUNCTION gen_qualified_ark(base TEXT, len INTEGER)
+RETURNS TEXT AS $$
+DECLARE
+    ark_qualifier TEXT;
+    found INTEGER;
+BEGIN
+    BEGIN
+        SELECT 1 INTO STRICT found FROM ark WHERE ark.name = base AND ark.qualifier = '';
+    EXCEPTION
+        WHEN NO_DATA_FOUND THEN
+            RAISE 'no ark record matching name "%" found', base
+                USING ERRCODE = 'invalid_parameter_value';
+    END;
+    LOOP
+        BEGIN
+            INSERT INTO ark
+                VALUES (base, gen_ark_qualifier(len))
+                RETURNING qualifier INTO ark_qualifier;
+            RETURN ark_qualifier;
+        EXCEPTION
+            WHEN unique_violation THEN
+                -- Continue and try with another "name".
+                RAISE WARNING 'unique_violation';
+                NULL;
+        END;
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/test/test_ark.py b/test/test_ark.py
--- a/test/test_ark.py
+++ b/test/test_ark.py
@@ -85,6 +85,28 @@ class ArkServiceTC(testlib.CubicWebTC):
             str(cm.exception),
         )
 
+    def test_generate_qualifier_no_name_existing(self):
+        with self.connect() as conn:
+            with conn.cursor() as cu:
+                with self.assertRaises(psycopg2.DataError) as cm:
+                    cu.execute("SELECT * FROM gen_qualified_ark(%s, %s);",
+                               ("doesnotexist", 3))
+        self.assertIn('no ark record matching name "doesnotexist" found',
+                      str(cm.exception))
+
+    def test_generate_qualifier_name_existing(self):
+        with self.connect() as conn:
+            with conn.cursor() as cu:
+                cu.execute("INSERT INTO ark VALUES (%s, DEFAULT);",
+                           ("exists", ))
+                cu.execute("SELECT * FROM gen_qualified_ark(%s, %s);",
+                           ("exists", 3))
+                cu.execute("SELECT * FROM ark"
+                           " WHERE ark.name = 'exists'"
+                           " AND NOT ark.qualifier = '';")
+                (_, qualifier), = cu.fetchall()
+        self.assertEqual(len(qualifier), 3, qualifier)
+
 
 if __name__ == '__main__':
     import unittest



More information about the saem-devel mailing list