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

annotate artifact-database/doc/schema-pg.sql @ 269:d9a99b28a847

Added support for the 'type' parameter of artifacts and collections out() call. artifacts/trunk@2030 c6561f87-3c4e-4783-a992-168aeb5c3f6f

author	Ingo Weinzierl <ingo.weinzierl@intevation.de>
date	Tue, 31 May 2011 14:53:36 +0000
parents	328ef982d768
children	92166f7c3842

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,
121 720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	50 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	51 );
720d65bbba13 Extended db scheme to cope with collections, collection items and users. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 41 diff changeset	52
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	53 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	54 $$
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 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	56 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	57 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	58 (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	59 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	60 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	61 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	62 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	63 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	64 $$
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 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	66
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 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	69 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	70 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	71
38 93edc04f3a10 Added postgresql support. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	72 COMMIT;

Mercurial > dive4elements > framework

annotate artifact-database/doc/schema-pg.sql @ 269:d9a99b28a847