Activity

1. loreto commented on Aug 7, 2024

   

   loreto

   on Aug 7, 2024

   Contributor

   More actions

   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.

   👍React with 👍3Reacted by Sam Jakos, Luke Ramsden and zda
2. sjakos commented on Aug 7, 2024

   

   sjakos

   on Aug 7, 2024

   Author

   More actions

   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?
3. sakopov commented on Aug 26, 2024

   

   sakopov

   on Aug 26, 2024

   More actions

   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;

   👍React with 👍2Reacted by zda and AntQwanlity
4. loreto commented on Aug 27, 2024

   

   loreto

   on Aug 27, 2024

   Contributor

   More actions

   @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)

   👍React with 👍2Reacted by Sergey Akopov and zda
5. sakopov commented on Aug 28, 2024

   

   sakopov

   on Aug 28, 2024

   More actions

   @loreto Absolutely, feel free.
6. loreto commented on Sep 6, 2024

   

   loreto

   on Sep 6, 2024

   Contributor

   More actions

   Thank you @sjakos for sending jetify-com/opensource#363
   Just tagging it here so that the issue has a link to the PR discussion.
7. c-nv-s commented on Mar 5, 2025

   

   c-nv-s

   on Mar 5, 2025

   More actions

   any update on getting a corrected patch incorporated? this would be very useful for me.

   👍React with 👍1Reacted by Jay Patel