sascha@38: -- sascha@38: -- schema to store artifacts in PostgreSQL databases. sascha@38: -- sascha@38: sascha@38: BEGIN; sascha@38: sascha@38: -- not using AUTO_INCREMENT to be more compatible with sascha@38: -- other dbms. sascha@38: CREATE SEQUENCE ARTIFACTS_ID_SEQ; sascha@38: sascha@38: CREATE TABLE artifacts ( sascha@38: id int PRIMARY KEY NOT NULL, sascha@38: gid uuid NOT NULL UNIQUE, sascha@38: creation timestamp NOT NULL, sascha@38: last_access timestamp NOT NULL, sascha@38: ttl bigint, -- NULL means eternal sascha@41: factory VARCHAR(256) NOT NULL, sascha@38: data bytea sascha@38: ); sascha@38: sascha@121: CREATE SEQUENCE USERS_ID_SEQ; sascha@121: sascha@121: CREATE TABLE users ( sascha@121: id int PRIMARY KEY NOT NULL, sascha@124: gid uuid NOT NULL UNIQUE, sascha@121: name VARCHAR(256) NOT NULL UNIQUE, sascha@199: role bytea sascha@121: ); sascha@121: sascha@121: CREATE SEQUENCE COLLECTIONS_ID_SEQ; sascha@121: sascha@121: CREATE TABLE collections ( sascha@121: id int PRIMARY KEY NOT NULL, sascha@124: gid uuid NOT NULL UNIQUE, sascha@121: name VARCHAR(256) NOT NULL, sascha@121: owner_id int NOT NULL REFERENCES users(id), sascha@121: creation timestamp NOT NULL, sascha@121: last_access timestamp NOT NULL, sascha@121: ttl bigint, -- NULL means eternal sascha@199: attribute bytea sascha@121: ); sascha@121: ingo@187: CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ; sascha@121: sascha@121: CREATE TABLE collection_items ( sascha@121: id int PRIMARY KEY NOT NULL, sascha@121: collection_id int NOT NULL REFERENCES collections(id), sascha@121: artifact_id int NOT NULL REFERENCES artifacts(id), sascha@199: attribute bytea, ingo@274: creation timestamp NOT NULL, sascha@121: UNIQUE (collection_id, artifact_id) sascha@121: ); sascha@121: sascha@229: CREATE FUNCTION collections_access_update() RETURNS trigger AS sascha@229: $$ sascha@229: BEGIN sascha@229: UPDATE collections SET last_access = current_timestamp sascha@229: WHERE id IN sascha@229: (SELECT c.id FROM collections c sascha@229: INNER JOIN collection_items ci ON c.id = ci.collection_id sascha@229: INNER JOIN artifacts a ON a.id = ci.artifact_id sascha@229: WHERE a.id = NEW.id); sascha@229: RETURN NEW; sascha@229: END; sascha@229: $$ sascha@229: LANGUAGE 'plpgsql'; sascha@229: sascha@229: sascha@229: CREATE TRIGGER collections_access_update_trigger AFTER UPDATE sascha@229: ON artifacts FOR EACH ROW sascha@229: EXECUTE PROCEDURE collections_access_update(); sascha@229: sascha@38: COMMIT;