Mercurial > dive4elements > framework
comparison artifact-database/doc/schema-h2.sql @ 121:720d65bbba13
Extended db scheme to cope with collections, collection items and users.
artifacts/trunk@1344 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 01 Mar 2011 16:26:27 +0000 |
| parents | 5e4bc24ea438 |
| children | caf9f456f7e3 |
comparison
equal
deleted
inserted
replaced
| 120:c030895edfcb | 121:720d65bbba13 |
|---|---|
| 16 ttl BIGINT, -- NULL means eternal | 16 ttl BIGINT, -- NULL means eternal |
| 17 factory VARCHAR(256) NOT NULL, | 17 factory VARCHAR(256) NOT NULL, |
| 18 data BINARY | 18 data BINARY |
| 19 ); | 19 ); |
| 20 | 20 |
| 21 CREATE SEQUENCE USERS_ID_SEQ; | |
| 22 | |
| 23 CREATE TABLE users ( | |
| 24 id INT PRIMARY KEY NOT NULL, | |
| 25 gid UUID NOT NULL, | |
| 26 name VARCHAR(256) NOT NULL UNIQUE, | |
| 27 role BINARY | |
| 28 ); | |
| 29 | |
| 30 CREATE SEQUENCE COLLECTIONS_ID_SEQ; | |
| 31 | |
| 32 CREATE TABLE collections ( | |
| 33 id INT PRIMARY KEY NOT NULL, | |
| 34 gid UUID NOT NULL, | |
| 35 name VARCHAR(256) NOT NULL, | |
| 36 owner_id INT NOT NULL REFERENCES users(id), | |
| 37 creation TIMESTAMP NOT NULL, | |
| 38 last_access TIMESTAMP NOT NULL, | |
| 39 ttl BIGINT, -- NULL means eternal | |
| 40 ); | |
| 41 | |
| 42 CREATE SEQUENCE COLLECTION_ITEMS_SEQ; | |
| 43 | |
| 44 CREATE TABLE collection_items ( | |
| 45 id INT PRIMARY KEY NOT NULL, | |
| 46 collection_id INT NOT NULL REFERENCES collections(id), | |
| 47 artifact_id INT NOT NULL REFERENCES artifacts(id), | |
| 48 UNIQUE (collection_id, artifact_id) | |
| 49 ); | |
| 50 | |
| 21 COMMIT; | 51 COMMIT; |