Mercurial > dive4elements > framework

annotate artifact-database/doc/schema-h2.sql @ 393:b7831cefbb62

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added some more debug output during the process of removing Artifacts and Collections by the DatabaseCleaner. artifacts/trunk@4585 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Ingo Weinzierl <ingo.weinzierl@intevation.de>
date Tue, 05 Jun 2012 13:32:14 +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;
http://dive4elements.wald.intevation.org