li7y/migrations/20240721221728_add_item_state.up.sql

156 lines
4.1 KiB
MySQL
Raw Permalink Normal View History

2024-07-22 23:31:11 +02:00
-- SPDX-FileCopyrightText: 2024 Simon Bruder <simon@sbruder.de>
--
-- SPDX-License-Identifier: AGPL-3.0-or-later
-- This datbase design is inspired by the following two blog posts by Felix Geisendörfer and Raphael Medaer:
--
-- https://felixge.de/2017/07/27/implementing-state-machines-in-postgresql/
-- https://raphael.medaer.me/2019/06/12/pgfsm.html
--
-- I chose a design that makes a compromise between both designs.
-- To simplify modifying transitions, it uses a table for storing the transitions,
-- but it does not version it, as the design should not radically change.
CREATE TYPE item_state AS ENUM (
'borrowed',
'inactive',
'loaned',
'owned'
);
CREATE TYPE item_event AS ENUM (
'acquire',
'borrow',
'buy',
'dispose',
'gift',
'loan',
'lose',
'recieve_gift',
'return_borrowed',
'return_loaned',
'sell',
'use'
);
CREATE TABLE item_events_transitions (
state item_state,
event item_event,
next item_state,
PRIMARY KEY (state, event, next)
);
INSERT INTO item_events_transitions VALUES
('inactive', 'buy', 'owned'),
('inactive', 'recieve_gift', 'owned'),
('inactive', 'acquire', 'owned'),
('inactive', 'borrow', 'borrowed'),
('owned', 'sell', 'inactive'),
('owned', 'gift', 'inactive'),
('owned', 'lose', 'inactive'),
('owned', 'use', 'inactive'),
('owned', 'dispose', 'inactive'),
('owned', 'loan', 'loaned'),
('loaned', 'return_loaned', 'owned'),
('borrowed', 'return_borrowed', 'inactive');
CREATE FUNCTION item_events_transition(_state item_state, _event item_event)
RETURNS item_state AS $$
SELECT next
FROM item_events_transitions
WHERE state = _state AND event = _event;
$$ LANGUAGE sql STRICT;
CREATE AGGREGATE item_events_fsm(item_event) (
SFUNC = item_events_transition,
STYPE = item_state,
INITCOND = 'inactive'
);
CREATE TABLE item_events (
id SERIAL PRIMARY KEY,
item UUID NOT NULL REFERENCES items(id),
date DATE NOT NULL DEFAULT now(),
event item_event NOT NULL,
description VARCHAR NOT NULL DEFAULT ''
);
CREATE FUNCTION check_item_events_fsm()
RETURNS trigger AS $$
BEGIN
IF (SELECT item_events_fsm(event ORDER BY id) FROM (
SELECT id, event FROM item_events WHERE item = NEW.item
UNION ALL
SELECT NEW.id, NEW.event
) events) IS NULL THEN
RAISE EXCEPTION 'Event not possible from current state';
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_item_events_fsm
BEFORE INSERT ON item_events
FOR EACH ROW
EXECUTE PROCEDURE check_item_events_fsm();
CREATE FUNCTION check_item_events_delete()
RETURNS trigger AS $$
BEGIN
IF (SELECT OLD.id <> max(id) FROM item_events WHERE item = OLD.item) THEN
RAISE EXCEPTION 'Only the last event of an item can be deleted';
END IF;
RETURN OLD;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER check_item_events_delete
BEFORE DELETE ON item_events
FOR EACH ROW
EXECUTE PROCEDURE check_item_events_delete();
CREATE VIEW item_states AS
-- probably not the best query, but it works
SELECT
items.id AS "item",
state,
event AS "last_event",
date AS "last_event_date",
item_events.description AS "last_event_description"
FROM item_events
-- items without eny event must be included
RIGHT JOIN items
ON item_events.item = items.id
JOIN (
SELECT
item_events_fsm(event ORDER BY item_events.id) AS "state",
max(item_events.id) AS "id",
items.id AS "item"
FROM item_events
-- see above
RIGHT JOIN items
ON item_events.item = items.id
GROUP BY item_events.item, items.id
) last_event
ON items.id = last_event.item AND (item_events.id = last_event.id OR last_event.id IS NULL);
CREATE FUNCTION add_default_item_event()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO item_events (item, event, description)
VALUES (NEW.id, 'acquire', 'automatically added on item insert');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER add_default_item_event
AFTER INSERT ON items
FOR EACH ROW
EXECUTE FUNCTION add_default_item_event();
INSERT INTO item_events (item, event, description)
SELECT id, 'acquire', 'automatically added on migration' FROM items;