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

Philippe Pepiot philippe.pepiot at logilab.fr
Thu Feb 15 13:30:05 CET 2018


On 14/02/2018, Denis Laxalde wrote:
> # 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()

Did you tried with gen_ark_name(len, '', ''); ?

> +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