Mercurial > dive4elements > framework
annotate artifact-database/doc/schema-pg.sql @ 407:08731e7403b5
Update sql user queries to add the account column
artifacts/trunk@5243 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Bjoern Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Thu, 23 Aug 2012 13:35:16 +0000 |
parents | 10aa92f2eca0 |
children |
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, |
406
10aa92f2eca0
Add a account column to the users table
Bjoern Ricks <bjoern.ricks@intevation.de>
parents:
274
diff
changeset
|
26 name VARCHAR(256) NOT NULL, |
10aa92f2eca0
Add a account column to the users table
Bjoern Ricks <bjoern.ricks@intevation.de>
parents:
274
diff
changeset
|
27 account VARCHAR(256) NOT NULL UNIQUE, |
199
face2302387c
Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
187
diff
changeset
|
28 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
|
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 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
31 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
|
32 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
33 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
|
34 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
|
35 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
|
36 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
|
37 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
|
38 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
|
39 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
|
40 ttl bigint, -- NULL means eternal |
199
face2302387c
Insert collection attribute into database, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
187
diff
changeset
|
41 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
|
42 ); |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
43 |
187
a85d889a1b0d
Fixed some backend specific stuff (sql statements, schema).
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
156
diff
changeset
|
44 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
|
45 |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
46 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
|
47 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
|
48 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
|
49 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
|
50 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
|
51 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
|
52 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
|
53 ); |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
54 |
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
|
55 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
|
56 $$ |
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 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
|
58 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
|
59 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
|
60 (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
|
61 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
|
62 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
|
63 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
|
64 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
|
65 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
|
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 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
|
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 |
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 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
|
71 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
|
72 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
|
73 |
38
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
74 COMMIT; |