universal-uuid.sql

/*
Experimental PostgreSQL schema that keeps a universal_id table as a materialized
view of all UUIDs in the system.

Simply inserts each new UUID inserted into the tables with the trigger into the
universal ID table.

Rational is that a REST API could provide an endpoint like /object/:uuid which
returns whatever that object happens to be, or a redirect to its canonical URL
perhaps, no matter the type. In the unlikely event of a UUID collision between
the many types, this will prevent that.
*/

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE OR REPLACE FUNCTION trigger_universal_id () RETURNS trigger
LANGUAGE plpgsql
VOLATILE
AS $BODY$
DECLARE
    column_name text := TG_ARGV[0];
    type_name text := TG_ARGV[1];
BEGIN
  CASE TG_OP
  WHEN 'INSERT' THEN
      EXECUTE FORMAT('
          INSERT INTO universal_id
          VALUES (
            ($1)."' || column_name || '",
            ($2))')
        USING NEW, type_name;
      RETURN NEW;
  WHEN 'DELETE' THEN
      EXECUTE FORMAT('
          DELETE FROM universal_id
          WHERE uuid = ($1)."' || column_name || '"')
        USING OLD;
      RETURN OLD;
  END CASE;
END
$BODY$;


CREATE TABLE factory (
    factory_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    factory_name text UNIQUE NOT NULL,
    city text
);

CREATE TABLE widget (
    widget_id uuid DEFAULT uuid_generate_v4() PRIMARY KEY,
    widget_name text UNIQUE NOT NULL,
    width integer,
    height integer
);

CREATE TABLE universal_id (
    uuid uuid PRIMARY KEY,
    type text NOT NULL
);

CREATE TABLE factory_widget (
    factory_id uuid REFERENCES factory (factory_id),
    widget_id uuid REFERENCES widget (widget_id),
    PRIMARY KEY (factory_id, widget_id)
);


CREATE TRIGGER factory_universal_id
    AFTER INSERT OR DELETE ON factory
    FOR EACH ROW EXECUTE PROCEDURE trigger_universal_id('factory_id', 'factory');

CREATE TRIGGER widget_universal_id
    AFTER INSERT OR DELETE ON widget
    FOR EACH ROW EXECUTE PROCEDURE trigger_universal_id('widget_id', 'widget');