Mercurial > dive4elements > river
annotate contrib/make_flys_release/h2/artifacts-h2.sql @ 4324:4980659b2f22
Add gauge name to the measurement station xml response
The reference gauge should be displayed in the measurement station info panel.
Therefore the artifact service must return the name of the gauge.
author | Björn Ricks <bjoern.ricks@intevation.de> |
---|---|
date | Tue, 30 Oct 2012 12:49:17 +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; |