Mercurial > dive4elements > river

comparison artifacts/doc/conf/datacage.sql @ 5838:5aa05a7a34b7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Rename modules to more fitting names.
author Sascha L. Teichmann <teichmann@intevation.de>
date Thu, 25 Apr 2013 15:23:37 +0200
parents flys-artifacts/doc/conf/datacage.sql@1071aacd042c
children 6d55614d6f87
comparison
equal deleted inserted replaced
5837:d9901a08d0a6 5838:5aa05a7a34b7
1 BEGIN;
2
3 CREATE SEQUENCE USERS_ID_SEQ;
4
5 CREATE TABLE users (
6 id INT PRIMARY KEY NOT NULL,
7 gid UUID NOT NULL UNIQUE
8 );
9
10 CREATE SEQUENCE COLLECTIONS_ID_SEQ;
11
12 CREATE TABLE collections (
13 id INT PRIMARY KEY NOT NULL,
14 gid UUID NOT NULL UNIQUE,
15 user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16 name VARCHAR(256) NOT NULL,
17 creation TIMESTAMP NOT NULL
18 );
19
20 CREATE SEQUENCE ARTIFACTS_ID_SEQ;
21
22 CREATE TABLE artifacts (
23 id INT PRIMARY KEY NOT NULL,
24 gid UUID NOT NULL UNIQUE,
25 state VARCHAR(256) NOT NULL,
26 creation TIMESTAMP NOT NULL
27 );
28
29 CREATE SEQUENCE COLLECTION_ITEMS_ID_SEQ;
30
31 CREATE TABLE collection_items (
32 id INT PRIMARY KEY NOT NULL,
33 collection_id INT NOT NULL REFERENCES collections(id) ON DELETE CASCADE,
34 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE
35 );
36
37 CREATE SEQUENCE ARTIFACT_DATA_ID_SEQ;
38
39 CREATE TABLE artifact_data (
40 id INT PRIMARY KEY NOT NULL,
41 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
42 kind VARCHAR(256) NOT NULL,
43 k VARCHAR(256) NOT NULL,
44 v VARCHAR(256), -- Maybe too short
45 UNIQUE (artifact_id, k)
46 );
47
48 CREATE SEQUENCE OUTS_ID_SEQ;
49
50 CREATE TABLE outs (
51 id INT PRIMARY KEY NOT NULL,
52 artifact_id INT NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
53 name VARCHAR(256) NOT NULL,
54 description VARCHAR(256),
55 out_type VARCHAR(256)
56 );
57
58 CREATE SEQUENCE FACETS_ID_SEQ;
59
60 CREATE TABLE facets (
61 id INT PRIMARY KEY NOT NULL,
62 out_id INT NOT NULL REFERENCES outs(id) ON DELETE CASCADE,
63 name VARCHAR(256) NOT NULL,
64 num INT NOT NULL,
65 state VARCHAR(256) NOT NULL,
66 description VARCHAR(256),
67 UNIQUE (out_id, num, name)
68 );
69
70 CREATE VIEW master_artifacts AS
71 SELECT a2.id AS id,
72 a2.gid AS gid,
73 a2.state AS state,
74 a2.creation AS creation,
75 ci2.collection_id AS collection_id
76 FROM collection_items ci2
77 JOIN artifacts a2
78 ON ci2.artifact_id = a2.id
79 JOIN (SELECT ci.collection_id AS c_id,
80 MIN(a.creation) AS oldest_a
81 FROM collection_items ci
82 JOIN artifacts a
83 ON ci.artifact_id = a.id
84 GROUP BY ci.collection_id) o
85 ON o.c_id = ci2.collection_id
86 WHERE a2.creation = o.oldest_a;
87
88 CREATE VIEW master_artifacts_range AS
89 SELECT ma.id AS id,
90 ma.gid AS gid,
91 ma.state AS state,
92 ma.creation AS creation,
93 ma.collection_id AS collection_id,
94 mam.ld_mode AS ld_mode,
95 mal.ld_locations AS ld_locations,
96 maf.ld_from AS ld_from,
97 mat.ld_to AS ld_to
98 FROM master_artifacts ma
99 LEFT JOIN (SELECT ad.v AS ld_mode,
100 ad.artifact_id AS artifact_id
101 FROM artifact_data ad
102 WHERE ad.k = 'ld_mode') mam
103 ON mam.artifact_id = ma.id
104 LEFT JOIN (SELECT ad.v AS ld_locations,
105 ad.artifact_id AS artifact_id
106 FROM artifact_data ad
107 WHERE ad.k = 'ld_locations') mal
108 ON mal.artifact_id = ma.id
109 LEFT JOIN (SELECT ad.v AS ld_from,
110 ad.artifact_id AS artifact_id
111 FROM artifact_data ad
112 WHERE ad.k = 'ld_from') maf
113 ON maf.artifact_id = ma.id
114 LEFT JOIN (SELECT ad.v AS ld_to,
115 ad.artifact_id AS artifact_id
116 FROM artifact_data ad
117 WHERE ad.k = 'ld_to') mat
118 ON mat.artifact_id = ma.id;
119
120 -- DROP VIEW master_artifacts;
121 -- DROP VIEW master_artifacts_range;
122 -- DROP SEQUENCE USERS_ID_SEQ;
123 -- DROP SEQUENCE COLLECTIONS_ID_SEQ;
124 -- DROP SEQUENCE ARTIFACTS_ID_SEQ;
125 -- DROP SEQUENCE COLLECTION_ITEMS_ID_SEQ;
126 -- DROP SEQUENCE ARTIFACT_DATA_ID_SEQ;
127 -- DROP SEQUENCE OUTS_ID_SEQ;
128 -- DROP SEQUENCE FACETS_ID_SEQ;
129 -- DROP TABLE facets;
130 -- DROP TABLE outs;
131 -- DROP TABLE artifact_data;
132 -- DROP TABLE collection_items;
133 -- DROP TABLE collections;
134 -- DROP TABLE artifacts;
135 -- DROP TABLE users;
136
137 COMMIT;
http://dive4elements.wald.intevation.org