Mercurial > dive4elements > framework
annotate artifact-database/doc/schema-h2.sql @ 308:a077bb098eb4
Fixed broken SQL statement. Added debug output.
artifacts/trunk@2427 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 31 Jul 2011 17:28:07 +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 H2 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 BINARY |
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, |
720d65bbba13
Extended db scheme to cope with collections, collection items and users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
41
diff
changeset
|
27 role BINARY |
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 BINARY |
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), |
156
a76de72ad6d1
Added stubs for the collection handling in db backend.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
124
diff
changeset
|
49 attribute BINARY, |
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:
271
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 TRIGGER collections_access_update_trigger AFTER UPDATE |
271
aa9f2f5ff032
flys/issue75: fixed doc and schema to make H2 work again.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
229
diff
changeset
|
55 ON artifacts FOR EACH ROW |
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
|
56 CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger"; |
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 |
38
93edc04f3a10
Added postgresql support.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
58 COMMIT; |