Mercurial > dive4elements > river
comparison contrib/make_flys_release/h2/artifacts-h2.sql @ 4065:9d404069f361
Added build script for FLYS release.
| author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
|---|---|
| date | Tue, 09 Oct 2012 12:30:13 +0200 |
| parents | |
| children |
comparison
equal
deleted
inserted
replaced
| 4064:61020a61ed38 | 4065:9d404069f361 |
|---|---|
| 1 -- | |
| 2 -- schema to store artifacts in H2 databases. | |
| 3 -- | |
| 4 | |
| 5 BEGIN; | |
| 6 | |
| 7 -- not using AUTO_INCREMENT to be more compatible with | |
| 8 -- other dbms. | |
| 9 CREATE SEQUENCE ARTIFACTS_ID_SEQ; | |
| 10 | |
| 11 CREATE TABLE artifacts ( | |
| 12 id INT PRIMARY KEY NOT NULL, | |
| 13 gid UUID NOT NULL UNIQUE, | |
| 14 creation TIMESTAMP NOT NULL, | |
| 15 last_access TIMESTAMP NOT NULL, | |
| 16 ttl BIGINT, -- NULL means eternal | |
| 17 factory VARCHAR(256) NOT NULL, | |
| 18 data BINARY | |
| 19 ); | |
| 20 | |
| 21 CREATE SEQUENCE USERS_ID_SEQ; | |
| 22 | |
| 23 CREATE TABLE users ( | |
| 24 id INT PRIMARY KEY NOT NULL, | |
| 25 gid UUID NOT NULL UNIQUE, | |
| 26 name VARCHAR(256) NOT NULL, | |
| 27 account VARCHAR(256) NOT NULL UNIQUE, | |
| 28 role BINARY | |
| 29 ); | |
| 30 | |
| 31 CREATE SEQUENCE COLLECTIONS_ID_SEQ; | |
| 32 | |
| 33 CREATE TABLE collections ( | |
| 34 id INT PRIMARY KEY NOT NULL, | |
| 35 gid UUID NOT NULL UNIQUE, | |
| 36 name VARCHAR(256) NOT NULL, | |
| 37 owner_id INT NOT NULL REFERENCES users(id), | |
| 38 creation TIMESTAMP NOT NULL, | |
| 39 last_access TIMESTAMP NOT NULL, | |
| 40 ttl BIGINT, -- NULL means eternal | |
| 41 attribute BINARY | |
| 42 ); | |
| 43 | |
| 44 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ; | |
| 45 | |
| 46 CREATE TABLE collection_items ( | |
| 47 id INT PRIMARY KEY NOT NULL, | |
| 48 collection_id INT NOT NULL REFERENCES collections(id), | |
| 49 artifact_id INT NOT NULL REFERENCES artifacts(id), | |
| 50 attribute BINARY, | |
| 51 creation TIMESTAMP NOT NULL, | |
| 52 UNIQUE (collection_id, artifact_id) | |
| 53 ); | |
| 54 | |
| 55 CREATE TRIGGER collections_access_update_trigger AFTER UPDATE | |
| 56 ON artifacts FOR EACH ROW | |
| 57 CALL "de.intevation.artifactdatabase.h2.CollectionAccessUpdateTrigger"; | |
| 58 | |
| 59 COMMIT; |