Fake it till you make it

dev_talk_fake_tables.md

Fake it till you make it - Extend functionality of your legacy application with fake tables

Concrete example: leihs legacy knows about roles for users. We would like to extend this to include roles for groups. But we can not change code of leihs legacy. Solution: we add new functionality but make leihs legacy belive the DB schema has not changed.

Principle Idea

1. Add new tables for new functionality, i.e. group_roles
2. Rename existing table, i.e. user_roles to direct_user_roles.
3. Create a view user_roles with custom AGGREGATEs using direct_user_roles and group_roles.
4. Optinally: make the views, i.e. `user_roles`` writable with triggers.

Technique 1: Custom AGGREGATEs

https://www.postgresql.org/docs/current/sql-createaggregate.html

CREATE OR REPLACE FUNCTION role_agg_f (role1 text, role2 text)
RETURNS text AS $$
BEGIN
  IF role1 = 'inventory_manager' OR role2 = 'inventory_manager' THEN
    RETURN 'inventory_manager';
  ELSIF role1 = 'lending_manager' OR role2 = 'lending_manager' THEN
    RETURN 'lending_manager';
  ELSIF role1 = 'group_manager' OR role2 = 'group_manager' THEN
    RETURN 'group_manager';
  ELSIF role1 = 'customer' OR role2 = 'customer' THEN
    RETURN 'customer';
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE role_agg (text)
( sfunc = role_agg_f,
  stype = text
);

CREATE VIEW access_rights AS
    SELECT
      ar_uuid_agg(id, user_id, inventory_pool_id) AS id,
      origin_table_agg(origin_table) AS origin_table,
      inventory_pool_id,
      user_id,
      role_agg(role) AS role
    FROM unified_access_rights
    GROUP BY (inventory_pool_id, user_id);

Technique 2: Make a view writable with triggers

https://www.postgresql.org/docs/current/sql-createtrigger.html

-- INSERT on view access_rights view ------------------------------------------

CREATE OR REPLACE FUNCTION access_rights_on_insert_f()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.id iS NULL then
    NEW.id = uuid_generate_v4();
  END IF;
  INSERT INTO direct_access_rights(id, user_id, inventory_pool_id, role)
    VALUES (NEW.id, NEW.user_id, NEW.inventory_pool_id, NEW.role);
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER access_rights_on_insert_t
INSTEAD OF insert ON access_rights
FOR EACH ROW EXECUTE PROCEDURE access_rights_on_insert_f();