Mercurial > dive4elements > river

comparison flys-artifacts/doc/conf/datacage.sql @ 980:f9a6a9cd918e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage: Added ON DELETE CASCADE constraints in schema. flys-artifacts/trunk@2407 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 27 Jul 2011 08:20:55 +0000
parents 2306340d7540
children 799c7108ea6d
comparison
equal deleted inserted replaced
979:2306340d7540 980:f9a6a9cd918e
6 ); 6 );
7 7
8 CREATE TABLE collections ( 8 CREATE TABLE collections (
9 id IDENTITY PRIMARY KEY NOT NULL, 9 id IDENTITY PRIMARY KEY NOT NULL,
10 gid UUID NOT NULL UNIQUE, 10 gid UUID NOT NULL UNIQUE,
11 user_id INT NOT NULL REFERENCES users(id), 11 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
12 name VARCHAR(256) NOT NULL 12 name VARCHAR(256) NOT NULL
13 ); 13 );
14 14
15 CREATE TABLE artifacts ( 15 CREATE TABLE artifacts (
16 id IDENTITY PRIMARY KEY NOT NULL, 16 id IDENTITY PRIMARY KEY NOT NULL,
18 state VARCHAR(256) NOT NULL 18 state VARCHAR(256) NOT NULL
19 ); 19 );
20 20
21 CREATE TABLE collection_items ( 21 CREATE TABLE collection_items (
22 id IDENTITY PRIMARY KEY NOT NULL, 22 id IDENTITY PRIMARY KEY NOT NULL,
23 collection_id INT NOT NULL REFERENCES collections(id), 23 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
24 artifact_id INT NOT NULL REFERENCES artifacts(id) 24 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE
25 ); 25 );
26 26
27 CREATE TABLE artifact_data ( 27 CREATE TABLE artifact_data (
28 id IDENTITY PRIMARY KEY NOT NULL, 28 id IDENTITY PRIMARY KEY NOT NULL,
29 artifact_id INT NOT NULL REFERENCES artifacts(id), 29 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
30 k VARCHAR(256) NOT NULL, 30 k VARCHAR(256) NOT NULL,
31 v VARCHAR(256), -- Maybe too short 31 v VARCHAR(256), -- Maybe too short
32 UNIQUE (artifact_id, k) 32 UNIQUE (artifact_id, k)
33 ); 33 );
34 34
35 CREATE TABLE facets ( 35 CREATE TABLE facets (
36 id IDENTITY PRIMARY KEY NOT NULL, 36 id IDENTITY PRIMARY KEY NOT NULL,
37 artifact_id INT NOT NULL REFERENCES artifacts(id), 37 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
38 name VARCHAR(256) NOT NULL, 38 name VARCHAR(256) NOT NULL,
39 num INT NOT NULL, 39 num INT NOT NULL,
40 state VARCHAR(256) NOT NULL,
40 output VARCHAR(256) NOT NULL, 41 output VARCHAR(256) NOT NULL,
41 description VARCHAR(256), 42 description VARCHAR(256),
42 UNIQUE (artifact_id, output, num, name) 43 UNIQUE (artifact_id, output, num, name)
43 ); 44 );
44 45
46 -- DROP TABLE facets;
47 -- DROP TABLE artifact_data;
48 -- DROP TABLE collection_items;
49 -- DROP TABLE collections;
50 -- DROP TABLE artifacts;
51 -- DROP TABLE users;
52
45 COMMIT; 53 COMMIT;
http://dive4elements.wald.intevation.org