Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 170:88c14d5d45be

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added missing sequences. Deleted unsupported sqlite schema. flys-backend/trunk@1460 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 14 Mar 2011 11:31:00 +0000
parents 86a1bd9cc50e
children 4a83e14f40f9
comparison
equal deleted inserted replaced
169:7929f4144d2f 170:88c14d5d45be
7 id int PRIMARY KEY NOT NULL, 7 id int PRIMARY KEY NOT NULL,
8 name VARCHAR(256) NOT NULL UNIQUE 8 name VARCHAR(256) NOT NULL UNIQUE
9 ); 9 );
10 10
11 -- Bruecke, Haefen, etc. 11 -- Bruecke, Haefen, etc.
12 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
13
12 CREATE TABLE attributes ( 14 CREATE TABLE attributes (
13 id int PRIMARY KEY NOT NULL, 15 id int PRIMARY KEY NOT NULL,
14 value VARCHAR(256) NOT NULL UNIQUE 16 value VARCHAR(256) NOT NULL UNIQUE
15 ); 17 );
16 18
17 -- segments from/to at a river 19 -- segments from/to at a river
20 CREATE SEQUENCE RANGES_ID_SEQ;
21
18 CREATE TABLE ranges ( 22 CREATE TABLE ranges (
19 id int PRIMARY KEY NOT NULL, 23 id int PRIMARY KEY NOT NULL,
20 river_id int NOT NULL REFERENCES rivers(id), 24 river_id int NOT NULL REFERENCES rivers(id),
21 a NUMERIC NOT NULL, 25 a NUMERIC NOT NULL,
22 b NUMERIC, 26 b NUMERIC,
23 UNIQUE (river_id, a, b) 27 UNIQUE (river_id, a, b)
24 ); 28 );
25 29
26 -- Lage 'links', 'rechts', etc. 30 -- Lage 'links', 'rechts', etc.
31 CREATE SEQUENCE POSITIONS_ID_SEQ;
32
27 CREATE TABLE positions ( 33 CREATE TABLE positions (
28 id int PRIMARY KEY NOT NULL, 34 id int PRIMARY KEY NOT NULL,
29 value VARCHAR(256) NOT NULL UNIQUE 35 value VARCHAR(256) NOT NULL UNIQUE
30 ); 36 );
31 37
32 -- Some object (eg. Hafen) at a segment of river 38 -- Some object (eg. Hafen) at a segment of river
33 -- plus its position. 39 -- plus its position.
40 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
41
34 CREATE TABLE annotations ( 42 CREATE TABLE annotations (
35 id int PRIMARY KEY NOT NULL, 43 id int PRIMARY KEY NOT NULL,
36 range_id int NOT NULL REFERENCES ranges(id), 44 range_id int NOT NULL REFERENCES ranges(id),
37 attribute_id int NOT NULL REFERENCES attributes(id), 45 attribute_id int NOT NULL REFERENCES attributes(id),
38 position_id int REFERENCES positions(id) 46 position_id int REFERENCES positions(id)
39 ); 47 );
40 48
41 -- Pegel 49 -- Pegel
50 CREATE SEQUENCE GAUGES_ID_SEQ;
51
42 CREATE TABLE gauges ( 52 CREATE TABLE gauges (
43 id int PRIMARY KEY NOT NULL, 53 id int PRIMARY KEY NOT NULL,
44 name VARCHAR(256) NOT NULL, 54 name VARCHAR(256) NOT NULL,
45 river_id int NOT NULL REFERENCES rivers(id), 55 river_id int NOT NULL REFERENCES rivers(id),
46 station NUMERIC NOT NULL UNIQUE, 56 station NUMERIC NOT NULL UNIQUE,
54 UNIQUE (name, river_id), 64 UNIQUE (name, river_id),
55 UNIQUE (river_id, datum) 65 UNIQUE (river_id, datum)
56 ); 66 );
57 67
58 -- Type of a Hauptwert 'W', 'Q', 'D', etc. 68 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
69 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
70
59 CREATE TABLE main_value_types ( 71 CREATE TABLE main_value_types (
60 id int PRIMARY KEY NOT NULL, 72 id int PRIMARY KEY NOT NULL,
61 name VARCHAR(256) NOT NULL UNIQUE 73 name VARCHAR(256) NOT NULL UNIQUE
62 ); 74 );
63 75
64 -- Named type of a Hauptwert (eg. HQ100) 76 -- Named type of a Hauptwert (eg. HQ100)
77 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
78
65 CREATE TABLE named_main_values ( 79 CREATE TABLE named_main_values (
66 id int PRIMARY KEY NOT NULL, 80 id int PRIMARY KEY NOT NULL,
67 name VARCHAR(256) NOT NULL UNIQUE, 81 name VARCHAR(256) NOT NULL UNIQUE,
68 type_id int NOT NULL REFERENCES main_value_types(id), 82 type_id int NOT NULL REFERENCES main_value_types(id),
69 UNIQUE (name, type_id) 83 UNIQUE (name, type_id)
70 ); 84 );
71 85
72 -- Table for time intervals 86 -- Table for time intervals
87 CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
88
73 CREATE TABLE time_intervals ( 89 CREATE TABLE time_intervals (
74 id int PRIMARY KEY NOT NULL, 90 id int PRIMARY KEY NOT NULL,
75 start_time TIMESTAMP NOT NULL, 91 start_time TIMESTAMP NOT NULL,
76 stop_time TIMESTAMP, 92 stop_time TIMESTAMP,
77 CHECK (start_time <= stop_time) 93 CHECK (start_time <= stop_time)
78 ); 94 );
79 95
80 -- Stammdaten 96 -- Stammdaten
97 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
98
81 CREATE TABLE main_values ( 99 CREATE TABLE main_values (
82 id int PRIMARY KEY NOT NULL, 100 id int PRIMARY KEY NOT NULL,
83 gauge_id int NOT NULL REFERENCES gauges(id), 101 gauge_id int NOT NULL REFERENCES gauges(id),
84 named_value_id int NOT NULL REFERENCES named_main_values(id), 102 named_value_id int NOT NULL REFERENCES named_main_values(id),
85 value NUMERIC NOT NULL, 103 value NUMERIC NOT NULL,
89 -- TODO: better checks 107 -- TODO: better checks
90 UNIQUE (gauge_id, named_value_id, time_interval_id) 108 UNIQUE (gauge_id, named_value_id, time_interval_id)
91 ); 109 );
92 110
93 -- Abflusstafeln 111 -- Abflusstafeln
112 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
113
94 CREATE TABLE discharge_tables ( 114 CREATE TABLE discharge_tables (
95 id int PRIMARY KEY NOT NULL, 115 id int PRIMARY KEY NOT NULL,
96 gauge_id int NOT NULL REFERENCES gauges(id), 116 gauge_id int NOT NULL REFERENCES gauges(id),
97 117
98 time_interval_id int REFERENCES time_intervals(id), 118 time_interval_id int REFERENCES time_intervals(id),
100 -- TODO: better checks 120 -- TODO: better checks
101 UNIQUE (gauge_id, time_interval_id) 121 UNIQUE (gauge_id, time_interval_id)
102 ); 122 );
103 123
104 -- Values of the Abflusstafeln 124 -- Values of the Abflusstafeln
125 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
126
105 CREATE TABLE discharge_table_values ( 127 CREATE TABLE discharge_table_values (
106 id int PRIMARY KEY NOT NULL, 128 id int PRIMARY KEY NOT NULL,
107 table_id int NOT NULL REFERENCES discharge_tables(id), 129 table_id int NOT NULL REFERENCES discharge_tables(id),
108 q NUMERIC NOT NULL, 130 q NUMERIC NOT NULL,
109 w NUMERIC NOT NULL, 131 w NUMERIC NOT NULL,
110 132
111 UNIQUE (table_id, q, w) 133 UNIQUE (table_id, q, w)
112 ); 134 );
113 135
114 -- WST files 136 -- WST files
137 CREATE SEQUENCE WST_ID_SEQ;
138
115 CREATE TABLE wst ( 139 CREATE TABLE wst (
116 id int PRIMARY KEY NOT NULL, 140 id int PRIMARY KEY NOT NULL,
117 river_id int NOT NULL REFERENCES rivers(id), 141 river_id int NOT NULL REFERENCES rivers(id),
118 description VARCHAR(256) NOT NULL, 142 description VARCHAR(256) NOT NULL,
119 -- TODO: more meta infos 143 -- TODO: more meta infos
120 UNIQUE (river_id, description) 144 UNIQUE (river_id, description)
121 ); 145 );
122 146
123 -- columns of WST files 147 -- columns of WST files
148 CREATE SEQUENCE WST_COLUMN_ID_SEQ;
149
124 CREATE TABLE wst_column ( 150 CREATE TABLE wst_column (
125 id int PRIMARY KEY NOT NULL, 151 id int PRIMARY KEY NOT NULL,
126 wst_id int NOT NULL REFERENCES wst(id), 152 wst_id int NOT NULL REFERENCES wst(id),
127 name VARCHAR(256) NOT NULL, 153 name VARCHAR(256) NOT NULL,
128 description VARCHAR, 154 description VARCHAR,
131 157
132 UNIQUE (wst_id, name) 158 UNIQUE (wst_id, name)
133 ); 159 );
134 160
135 -- w values in WST file column 161 -- w values in WST file column
162 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
163
136 CREATE TABLE wst_column_values ( 164 CREATE TABLE wst_column_values (
137 id int PRIMARY KEY NOT NULL, 165 id int PRIMARY KEY NOT NULL,
138 wst_column_id int NOT NULL REFERENCES wst_column(id), 166 wst_column_id int NOT NULL REFERENCES wst_column(id),
139 position NUMERIC NOT NULL, 167 position NUMERIC NOT NULL,
140 w NUMERIC NOT NULL, 168 w NUMERIC NOT NULL,
142 UNIQUE (position, wst_column_id), 170 UNIQUE (position, wst_column_id),
143 UNIQUE (position, wst_column_id, w) 171 UNIQUE (position, wst_column_id, w)
144 ); 172 );
145 173
146 -- bind q values to range 174 -- bind q values to range
175 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
176
147 CREATE TABLE wst_q_ranges ( 177 CREATE TABLE wst_q_ranges (
148 id int PRIMARY KEY NOT NULL, 178 id int PRIMARY KEY NOT NULL,
149 range_id int NOT NULL REFERENCES ranges(id), 179 range_id int NOT NULL REFERENCES ranges(id),
150 q NUMERIC NOT NULL 180 q NUMERIC NOT NULL
151 ); 181 );
152 182
153 -- bind q ranges to wst columns 183 -- bind q ranges to wst columns
184 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
185
154 CREATE TABLE wst_column_q_ranges ( 186 CREATE TABLE wst_column_q_ranges (
155 id int PRIMARY KEY NOT NULL, 187 id int PRIMARY KEY NOT NULL,
156 wst_column_id int NOT NULL REFERENCES wst_column(id), 188 wst_column_id int NOT NULL REFERENCES wst_column(id),
157 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), 189 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id),
158 190
http://dive4elements.wald.intevation.org