Mercurial > dive4elements > river
annotate contrib/make_flys_release/h2/artifacts-h2.sql @ 4219:b7535b66490e
Show the 'DischargeTablesChart' when choosing the calculation timerange in historical discharge calculation.
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Tue, 23 Oct 2012 12:24:23 +0200 |
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; |