dive4elements/framework: artifact-database/doc/schema-pg.sql annotate

annotate artifact-database/doc/schema-pg.sql @ 320:b46c5b13ac94

Extract the collection and artifact creation times when doing the initial, too. artifacts/trunk@2482 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Wed, 10 Aug 2011 14:18:33 +0000
parents	92166f7c3842
children	10aa92f2eca0

rev	line source
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 --
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2 -- schema to store artifacts in PostgreSQL databases.
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 --
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 BEGIN;
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	7 -- not using AUTO_INCREMENT to be more compatible with
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 -- other dbms.
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11 CREATE TABLE artifacts (
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	12 id int PRIMARY KEY NOT NULL,
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	13 gid uuid NOT NULL UNIQUE,
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14 creation timestamp NOT NULL,
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 last_access timestamp NOT NULL,
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16 ttl bigint, -- NULL means eternal
41 5e4bc24ea438 Made serilization more flexible. DB update required!!! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 38 diff changeset	17 factory VARCHAR(256) NOT NULL,
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 data bytea
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 );
93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	21 CREATE SEQUENCE USERS_ID_SEQ;
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	22
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	23 CREATE TABLE users (
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	24 id int PRIMARY KEY NOT NULL,
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 121 diff changeset	25 gid uuid NOT NULL UNIQUE,
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	26 name VARCHAR(256) NOT NULL UNIQUE,
199 face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 187 diff changeset	27 role bytea
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	28 );
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	29
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	30 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	31
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	32 CREATE TABLE collections (
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	33 id int PRIMARY KEY NOT NULL,
124 caf9f456f7e3 Added SQL statements to handle users in database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 121 diff changeset	34 gid uuid NOT NULL UNIQUE,
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	35 name VARCHAR(256) NOT NULL,
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	36 owner_id int NOT NULL REFERENCES users(id),
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	37 creation timestamp NOT NULL,
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	38 last_access timestamp NOT NULL,
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	39 ttl bigint, -- NULL means eternal
199 face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 187 diff changeset	40 attribute bytea
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	41 );
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	42
187 a85d889a1b0d Fixed some backend specific stuff (sql statements, schema). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 156 diff changeset	43 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	44
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	45 CREATE TABLE collection_items (
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	46 id int PRIMARY KEY NOT NULL,
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	47 collection_id int NOT NULL REFERENCES collections(id),
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	48 artifact_id int NOT NULL REFERENCES artifacts(id),
199 face2302387c Insert collection attribute into database, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 187 diff changeset	49 attribute bytea,
274 92166f7c3842 #50 Added code on database level to recognize the oldest collection item in a collection (-> master artifact). Ingo Weinzierl <ingo.weinzierl@intevation.de> parents: 229 diff changeset	50 creation timestamp NOT NULL,
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	51 UNIQUE (collection_id, artifact_id)
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	52 );
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	53
229 328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	54 CREATE FUNCTION collections_access_update() RETURNS trigger AS
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	55 $$
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	56 BEGIN
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	57 UPDATE collections SET last_access = current_timestamp
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	58 WHERE id IN
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	59 (SELECT c.id FROM collections c
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	60 INNER JOIN collection_items ci ON c.id = ci.collection_id
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	61 INNER JOIN artifacts a ON a.id = ci.artifact_id
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	62 WHERE a.id = NEW.id);
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	63 RETURN NEW;
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	64 END;
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	65 $$
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	66 LANGUAGE 'plpgsql';
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	67
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	68
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	69 CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	70 ON artifacts FOR EACH ROW
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	71 EXECUTE PROCEDURE collections_access_update();
328ef982d768 When an artifact is touched the collections which contain the artifact are touched, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 199 diff changeset	72
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	73 COMMIT;

Mercurial > dive4elements > framework

annotate artifact-database/doc/schema-pg.sql @ 320:b46c5b13ac94