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;