Prefix does not allow underscores

[sjakos]

Looks like the spec allows for underscores in the prefix, however the typeid_parse function only splits on the first _

[loreto]

Hi @sjakos: yes, you're right. It's because we haven't updated this sql implementation to the 0.3 spec (which introduced the _ support in the prefix). It's still following version 0.2.

Let me see if I can find some time to update it this week.

[sjakos]

I missed that detail, thanks for the clarification. Are there other changes that would need to happen for the move from 0.2 to 0.3?

[sakopov]

Needed to get this working for my own needs. Here's my updated 03_typeid.sql script which handles new spec updates around prefixes.

-- Implementation of typeids in SQL (Postgres).
-- This file:
-- + Defines a `typeid` type: a composite type consisting of a type prefix,
--   and a UUID
-- + Defines functions to generate and validate typeids in SQL.

-- Create a `typeid` type.
create type "typeid" as ("type" varchar(63), "uuid" uuid);

-- Function that generates a random typeid of the given type.
-- This depends on the `uuid_generate_v7` function defined in `uuid_v7.sql`.
create or replace function typeid_generate(prefix text)
returns typeid
as $$
begin
  if (prefix is null) or (prefix = '') then
    return ('', uuid_generate_v7())::typeid;
  end if;
  if not prefix ~ '^([a-z]([a-z_]{0,61}[a-z])?)?$' then
    raise exception 'typeid prefix must match the regular expression ([a-z]([a-z_]{0,61}[a-z])?)?';
  end if;
  return (prefix, uuid_generate_v7())::typeid;
end
$$
language plpgsql
volatile;

-- Function that generates a type_id of given type, and returns the parsed typeid as text.
create or replace function typeid_generate_text(prefix text)
returns text
as $$
begin
  if (prefix is null) or (prefix = '') then
    return typeid_print(('', uuid_generate_v7())::typeid);
  end if;
  if not prefix ~ '^([a-z]([a-z_]{0,61}[a-z])?)?$' then
    raise exception 'typeid prefix must match the regular expression ([a-z]([a-z_]{0,61}[a-z])?)?';
  end if;
  return typeid_print((prefix, uuid_generate_v7())::typeid);
end
$$
language plpgsql
volatile;

-- Function that checks if a typeid is valid, for the given type prefix.
create or replace function typeid_check(tid typeid, expected_type text)
returns boolean
as $$
declare
  prefix text;
begin
  prefix = (tid).type;
  return prefix = expected_type;
end
$$
language plpgsql
immutable;

-- Function that checks if a typeid is valid, for the given type_id in text format and type prefix, returns boolean.
create or replace function typeid_check_text(typeid_str text, expected_type text)
returns boolean
as $$
declare
  prefix text;
  tid typeid;
begin
  tid = typeid_parse(typeid_str);
  prefix = (tid).type;
  return prefix = expected_type;
end
$$
language plpgsql
immutable;

-- Function that parses a string into a typeid.
create or replace function typeid_parse(typeid_str text)
returns typeid
as $$
declare
  prefix text;
  suffix text;
begin
  if (typeid_str is null) then
    return null;
  end if;
  if position('_' in typeid_str) = 0 then
    return ('', base32_decode(typeid_str))::typeid;
  end if;
  prefix = substring(typeid_str from 1 for (length(typeid_str) - position('_' in reverse(typeid_str))));
  suffix = substring(typeid_str from (length(prefix) + 2));
  if prefix is null or prefix = '' then
    raise exception 'typeid prefix cannot be empty with a delimiter';
  end if;
  -- prefix must match the regular expression ([a-z]([a-z_]{0,61}[a-z])?)?
  if not prefix ~ '^([a-z]([a-z_]{0,61}[a-z])?)?$' then
    raise exception 'typeid prefix must match the regular expression ([a-z]([a-z_]{0,61}[a-z])?)?';
  end if;

  return (prefix, base32_decode(suffix))::typeid;
end
$$
language plpgsql
immutable;

-- Function that serializes a typeid into a string.
create or replace function typeid_print(tid typeid)
returns text
as $$
declare
  prefix text;
  suffix text;
begin
  if (tid is null) then
    return null;
  end if;
  prefix = (tid).type;
  suffix = base32_encode((tid).uuid);
  if not prefix ~ '^([a-z]([a-z_]{0,61}[a-z])?)?$' then
    raise exception 'typeid prefix must match the regular expression ([a-z]([a-z_]{0,61}[a-z])?)?';
  end if;
  if prefix = '' then
    return suffix;
  end if;
  return (prefix || '_' || suffix);
end
$$
language plpgsql
immutable;

[loreto]

@sakopov Thanks for sharing! Are you open to contributing the above code back to the project under the Apache license? (If yes, I'll put together a PR based on it)

[sakopov]

@loreto Absolutely, feel free.

[loreto]

Thank you @sjakos for sending https://github.com/jetify-com/opensource/pull/363 Just tagging it here so that the issue has a link to the PR discussion.