Mercurial > dive4elements > framework
view artifact-database/doc/schema-pg.sql @ 550:68f01f10624e
Introduced helper for finding the user name for a given artifact
author | gernotbelger |
---|---|
date | Fri, 09 Feb 2018 13:26:19 +0100 |
parents | 10aa92f2eca0 |
children |
line wrap: on
line source
-- -- schema to store artifacts in PostgreSQL databases. -- BEGIN; -- not using AUTO_INCREMENT to be more compatible with -- other dbms. CREATE SEQUENCE ARTIFACTS_ID_SEQ; CREATE TABLE artifacts ( id int PRIMARY KEY NOT NULL, gid uuid NOT NULL UNIQUE, creation timestamp NOT NULL, last_access timestamp NOT NULL, ttl bigint, -- NULL means eternal factory VARCHAR(256) NOT NULL, data bytea ); CREATE SEQUENCE USERS_ID_SEQ; CREATE TABLE users ( id int PRIMARY KEY NOT NULL, gid uuid NOT NULL UNIQUE, name VARCHAR(256) NOT NULL, account VARCHAR(256) NOT NULL UNIQUE, role bytea ); CREATE SEQUENCE COLLECTIONS_ID_SEQ; CREATE TABLE collections ( id int PRIMARY KEY NOT NULL, gid uuid NOT NULL UNIQUE, name VARCHAR(256) NOT NULL, owner_id int NOT NULL REFERENCES users(id), creation timestamp NOT NULL, last_access timestamp NOT NULL, ttl bigint, -- NULL means eternal attribute bytea ); CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ; CREATE TABLE collection_items ( id int PRIMARY KEY NOT NULL, collection_id int NOT NULL REFERENCES collections(id), artifact_id int NOT NULL REFERENCES artifacts(id), attribute bytea, creation timestamp NOT NULL, UNIQUE (collection_id, artifact_id) ); CREATE FUNCTION collections_access_update() RETURNS trigger AS $$ BEGIN UPDATE collections SET last_access = current_timestamp WHERE id IN (SELECT c.id FROM collections c INNER JOIN collection_items ci ON c.id = ci.collection_id INNER JOIN artifacts a ON a.id = ci.artifact_id WHERE a.id = NEW.id); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER collections_access_update_trigger AFTER UPDATE ON artifacts FOR EACH ROW EXECUTE PROCEDURE collections_access_update(); COMMIT;