Mercurial > dive4elements > river

comparison flys-artifacts/doc/conf/datacage.sql @ 985:8094ba4ab873

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage SQL schema: Using sequences for id generation now to make schema more compatible. flys-artifacts/trunk@2417 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Thu, 28 Jul 2011 14:38:51 +0000
parents 799c7108ea6d
children dbe39e1fb5e7
comparison
equal deleted inserted replaced
984:2b05c4a0c6fd 985:8094ba4ab873
1 BEGIN; 1 BEGIN;
2 2
3 CREATE SEQUENCE USERS_ID_SEQ;
4
3 CREATE TABLE users ( 5 CREATE TABLE users (
4 id IDENTITY PRIMARY KEY NOT NULL, 6 id INT PRIMARY KEY NOT NULL,
5 gid UUID NOT NULL UNIQUE 7 gid UUID NOT NULL UNIQUE
6 ); 8 );
7 9
10 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
11
8 CREATE TABLE collections ( 12 CREATE TABLE collections (
9 id IDENTITY PRIMARY KEY NOT NULL, 13 id INT PRIMARY KEY NOT NULL,
10 gid UUID NOT NULL UNIQUE, 14 gid UUID NOT NULL UNIQUE,
11 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE, 15 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
12 name VARCHAR(256) NOT NULL 16 name VARCHAR(256) NOT NULL
13 ); 17 );
14 18
19 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
20
15 CREATE TABLE artifacts ( 21 CREATE TABLE artifacts (
16 id IDENTITY PRIMARY KEY NOT NULL, 22 id INT PRIMARY KEY NOT NULL,
17 gid UUID NOT NULL UNIQUE, 23 gid UUID NOT NULL UNIQUE,
18 state VARCHAR(256) NOT NULL 24 state VARCHAR(256) NOT NULL
19 ); 25 );
20 26
27 CREATE SEQUENCE COLLECTIONS_ITEMS_ID_SEQ;
28
21 CREATE TABLE collection_items ( 29 CREATE TABLE collection_items (
22 id IDENTITY PRIMARY KEY NOT NULL, 30 id INT PRIMARY KEY NOT NULL,
23 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE, 31 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
24 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE 32 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE
25 ); 33 );
26 34
35 CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
36
27 CREATE TABLE artifact_data ( 37 CREATE TABLE artifact_data (
28 id IDENTITY PRIMARY KEY NOT NULL, 38 id INT PRIMARY KEY NOT NULL,
29 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, 39 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
30 k VARCHAR(256) NOT NULL, 40 k VARCHAR(256) NOT NULL,
31 v VARCHAR(256), -- Maybe too short 41 v VARCHAR(256), -- Maybe too short
32 UNIQUE (artifact_id, k) 42 UNIQUE (artifact_id, k)
33 ); 43 );
34 44
45 CREATE SEQUENCE OUTS_ID_SEQ;
46
35 CREATE TABLE outs ( 47 CREATE TABLE outs (
36 id IDENTITY PRIMARY KEY NOT NULL, 48 id INT PRIMARY KEY NOT NULL,
37 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, 49 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
38 name VARCHAR(256) NOT NULL, 50 name VARCHAR(256) NOT NULL,
39 description VARCHAR(256), 51 description VARCHAR(256),
40 out_type VARCHAR(256) 52 out_type VARCHAR(256)
41 ); 53 );
42 54
55 CREATE SEQUENCE FACETS_ID_SEQ;
56
43 CREATE TABLE facets ( 57 CREATE TABLE facets (
44 id IDENTITY PRIMARY KEY NOT NULL, 58 id INT PRIMARY KEY NOT NULL,
45 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE, 59 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
46 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE, 60 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
47 name VARCHAR(256) NOT NULL, 61 name VARCHAR(256) NOT NULL,
48 num INT NOT NULL, 62 num INT NOT NULL,
49 state VARCHAR(256) NOT NULL, 63 state VARCHAR(256) NOT NULL,
50 description VARCHAR(256), 64 description VARCHAR(256),
51 UNIQUE (artifact_id, output, num, name) 65 UNIQUE (artifact_id, out_id, num, name)
52 ); 66 );
53 67
68 -- DROP SEQUENCE USERS_ID_SEQ;
69 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
70 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
71 -- DROP SEQUENCE COLLECTIONS_ITEMS_ID_SEQ;
72 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
73 -- DROP SEQUENCE OUTS_ID_SEQ;
74 -- DROP SEQUENCE FACETS_ID_SEQ;
54 -- DROP TABLE facets; 75 -- DROP TABLE facets;
55 -- DROP TABLE outs; 76 -- DROP TABLE outs;
56 -- DROP TABLE artifact_data; 77 -- DROP TABLE artifact_data;
57 -- DROP TABLE collection_items; 78 -- DROP TABLE collection_items;
58 -- DROP TABLE collections; 79 -- DROP TABLE collections;
http://dive4elements.wald.intevation.org