Mercurial > dive4elements > river

annotate contrib/make_flys_release/h2/artifacts-h2.sql @ 4993:71e1b96f4794 dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add srid field
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 14 Feb 2013 17:45:13 +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