Mercurial > dive4elements > river

annotate contrib/make_flys_release/h2/artifacts-h2.sql @ 4638:6122ee2ab67f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Put start and end km into the context for average calculation.
author Raimund Renkert <rrenkert@intevation.de>
date Tue, 04 Dec 2012 15:39:50 +0100
parents 9d404069f361
children
rev   line source
4065
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
1 --
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
2 -- schema to store artifacts in H2 databases.
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
3 --
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
4
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
5 BEGIN;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
6
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
7 -- not using AUTO_INCREMENT to be more compatible with
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
8 -- other dbms.
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
9 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
10
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
11 CREATE TABLE artifacts (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
12 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
13 gid UUID NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
14 creation TIMESTAMP NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
15 last_access TIMESTAMP NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
16 ttl BIGINT, -- NULL means eternal
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
17 factory VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
18 data BINARY
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
19 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
20
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
21 CREATE SEQUENCE USERS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
22
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
23 CREATE TABLE users (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
24 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
25 gid UUID NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
26 name VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
27 account VARCHAR(256) NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
28 role BINARY
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
29 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
30
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
31 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
32
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
33 CREATE TABLE collections (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
34 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
35 gid UUID NOT NULL UNIQUE,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
36 name VARCHAR(256) NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
37 owner_id INT NOT NULL REFERENCES users(id),
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
38 creation TIMESTAMP NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
39 last_access TIMESTAMP NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
40 ttl BIGINT, -- NULL means eternal
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
41 attribute BINARY
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
42 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
43
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
44 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
45
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
46 CREATE TABLE collection_items (
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
47 id INT PRIMARY KEY NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
48 collection_id INT NOT NULL REFERENCES collections(id),
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
49 artifact_id INT NOT NULL REFERENCES artifacts(id),
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
50 attribute BINARY,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
51 creation TIMESTAMP NOT NULL,
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
52 UNIQUE (collection_id, artifact_id)
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
53 );
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
54
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
55 CREATE TRIGGER collections_access_update_trigger AFTER UPDATE
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
56 ON artifacts FOR EACH ROW
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
57 CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger";
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
58
9d404069f361 Added build script for FLYS release.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
diff changeset
59 COMMIT;
http://dive4elements.wald.intevation.org