Mercurial > dive4elements > river

comparison flys-backend/doc/schema/oracle.sql @ 5783:153456f84602

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
add missing constraints to Oracle Schema (issue426) and some cosmetics
author Tom Gottfried <tom@intevation.de>
date Mon, 22 Apr 2013 13:00:13 +0200
parents 88cbe798cbab
children
comparison
equal deleted inserted replaced
5782:5ad389bc20f0 5783:153456f84602
1 -- ANNOTATION_TYPES 1 -- ANNOTATION_TYPES
2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ; 2 CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
3 3
4 CREATE TABLE annotation_types ( 4 CREATE TABLE annotation_types (
5 id NUMBER(38,0) NOT NULL, 5 id NUMBER(38,0) NOT NULL,
6 name VARCHAR2(255), 6 name VARCHAR2(255) NOT NULL UNIQUE,
7 PRIMARY KEY (id) 7 PRIMARY KEY (id)
8 ); 8 );
9 9
10 10
11 -- ANNOTATIONS 11 -- ANNOTATIONS
12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; 12 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
13 13
14 CREATE TABLE annotations ( 14 CREATE TABLE annotations (
15 id NUMBER(38,0) NOT NULL, 15 id NUMBER(38,0) NOT NULL,
16 attribute_id NUMBER(38,0), 16 attribute_id NUMBER(38,0) NOT NULL,
17 edge_id NUMBER(38,0), 17 edge_id NUMBER(38,0),
18 position_id NUMBER(38,0), 18 position_id NUMBER(38,0),
19 range_id NUMBER(38,0), 19 range_id NUMBER(38,0),
20 type_id NUMBER(38,0), 20 type_id NUMBER(38,0),
21 PRIMARY KEY (id) 21 PRIMARY KEY (id)
25 -- ATTRIBUTES 25 -- ATTRIBUTES
26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ; 26 CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
27 27
28 CREATE TABLE attributes ( 28 CREATE TABLE attributes (
29 id NUMBER(38,0) NOT NULL, 29 id NUMBER(38,0) NOT NULL,
30 value VARCHAR2(255), 30 value VARCHAR2(255) NOT NULL UNIQUE,
31 primary key (id) 31 primary key (id)
32 ); 32 );
33 33
34 34
35 -- CROSS_SECTION_LINES 35 -- CROSS_SECTION_LINES
76 -- DISCHARGE_TABLE_VALUES 76 -- DISCHARGE_TABLE_VALUES
77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; 77 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
78 78
79 CREATE TABLE discharge_table_values ( 79 CREATE TABLE discharge_table_values (
80 id NUMBER(38,0) NOT NULL, 80 id NUMBER(38,0) NOT NULL,
81 q NUMBER(38,2), 81 q NUMBER(38,2) NOT NULL,
82 w NUMBER(38,2), 82 w NUMBER(38,2) NOT NULL,
83 table_id NUMBER(38,0), 83 table_id NUMBER(38,0) NOT NULL,
84 UNIQUE (table_id, q, w),
84 PRIMARY KEY (id) 85 PRIMARY KEY (id)
85 ); 86 );
86 87
87 88
88 -- DISCHARGE_TABLES 89 -- DISCHARGE_TABLES
89 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; 90 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
90 91
91 CREATE TABLE discharge_tables ( 92 CREATE TABLE discharge_tables (
92 id NUMBER(38,0) NOT NULL, 93 id NUMBER(38,0) NOT NULL,
93 description VARCHAR2(255), 94 description VARCHAR2(255) NOT NULL,
94 bfg_id VARCHAR2(50), 95 bfg_id VARCHAR2(50),
95 kind NUMBER(38,0), 96 kind NUMBER(38,0) NOT NULL DEFAULT 0,
96 gauge_id NUMBER(38,0), 97 gauge_id NUMBER(38,0) NOT NULL,
97 time_interval_id NUMBER(38,0), 98 time_interval_id NUMBER(38,0),
98 PRIMARY KEY (id) 99 PRIMARY KEY (id)
99 ); 100 );
100 101
101 102
120 name VARCHAR2(255) NOT NULL, 121 name VARCHAR2(255) NOT NULL,
121 station NUMBER(38,2) NOT NULL, 122 station NUMBER(38,2) NOT NULL,
122 official_number NUMBER(38,0) UNIQUE, 123 official_number NUMBER(38,0) UNIQUE,
123 range_id NUMBER(38,0) NOT NULL, 124 range_id NUMBER(38,0) NOT NULL,
124 -- remove river id here because range_id references river already 125 -- remove river id here because range_id references river already
125 river_id NUMBER(38,0), 126 river_id NUMBER(38,0) NOT NULL,
126 PRIMARY KEY (id), 127 PRIMARY KEY (id),
127 UNIQUE (name, river_id), 128 UNIQUE (name, river_id),
128 UNIQUE (river_id, station) 129 UNIQUE (river_id, station)
129 ); 130 );
130 131
195 -- MAIN_VALUE_TYPES 196 -- MAIN_VALUE_TYPES
196 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ; 197 CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
197 198
198 CREATE TABLE main_value_types ( 199 CREATE TABLE main_value_types (
199 id NUMBER(38,0) NOT NULL, 200 id NUMBER(38,0) NOT NULL,
200 name VARCHAR2(255), 201 name VARCHAR2(255) NOT NULL UNIQUE,
201 PRIMARY KEY (id) 202 PRIMARY KEY (id)
202 ); 203 );
203 204
204 205
205 -- MAIN_VALUES 206 -- MAIN_VALUES
206 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; 207 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
207 208
208 CREATE TABLE main_values ( 209 CREATE TABLE main_values (
209 id NUMBER(38,0) NOT NULL, 210 id NUMBER(38,0) NOT NULL,
210 value NUMBER(38,2), 211 value NUMBER(38,2) NOT NULL,
211 gauge_id NUMBER(38,0), 212 gauge_id NUMBER(38,0) NOT NULL,
212 named_value_id NUMBER(38,0), 213 named_value_id NUMBER(38,0) NOT NULL,
213 time_interval_id NUMBER(38,0), 214 time_interval_id NUMBER(38,0),
215
216 -- TODO: better checks
217 UNIQUE (gauge_id, named_value_id, time_interval_id),
214 PRIMARY KEY (id) 218 PRIMARY KEY (id)
215 ); 219 );
216 220
217 221
218 -- NAMED_MAIN_VALUES 222 -- NAMED_MAIN_VALUES
219 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ; 223 CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
220 224
221 CREATE TABLE named_main_values ( 225 CREATE TABLE named_main_values (
222 id NUMBER(38,0) NOT NULL, 226 id NUMBER(38,0) NOT NULL,
223 name VARCHAR2(256) NOT NULL UNIQUE, 227 name VARCHAR2(256) NOT NULL UNIQUE,
224 type_id NUMBER(38,0), 228 type_id NUMBER(38,0) NOT NULL,
225 PRIMARY KEY (id) 229 PRIMARY KEY (id)
226 ); 230 );
227 231
228 232
229 -- POSITIONS 233 -- POSITIONS
230 CREATE SEQUENCE POSITIONS_ID_SEQ; 234 CREATE SEQUENCE POSITIONS_ID_SEQ;
231 235
232 CREATE TABLE positions ( 236 CREATE TABLE positions (
233 id NUMBER(38,0) NOT NULL, 237 id NUMBER(38,0) NOT NULL,
234 value VARCHAR2(255 char), 238 value VARCHAR2(255 char) NOT NULL UNIQUE,
235 PRIMARY KEY (id) 239 PRIMARY KEY (id)
236 ); 240 );
237 241
238 242
239 --- RANGES 243 --- RANGES
242 CREATE TABLE ranges ( 246 CREATE TABLE ranges (
243 id NUMBER(38,0) NOT NULL, 247 id NUMBER(38,0) NOT NULL,
244 a NUMBER(38,10) NOT NULL, 248 a NUMBER(38,10) NOT NULL,
245 b NUMBER(38,10), 249 b NUMBER(38,10),
246 river_id NUMBER(38,0), 250 river_id NUMBER(38,0),
247 PRIMARY KEY (id) 251 UNIQUE (river_id, a, b),
252 PRIMARY KEY (id)
248 ); 253 );
249 254
250 255
251 256
252 -- RIVERS 257 -- RIVERS
253 CREATE SEQUENCE RIVERS_ID_SEQ; 258 CREATE SEQUENCE RIVERS_ID_SEQ;
254 259
255 CREATE TABLE rivers ( 260 CREATE TABLE rivers (
256 id NUMBER(38,0) NOT NULL, 261 id NUMBER(38,0) NOT NULL,
257 official_number NUMBER(38,0), 262 official_number NUMBER(38,0) UNIQUE,
258 km_up NUMBER(38,0), 263 km_up NUMBER(38,0) NOT NULL DEFAULT 0,
259 name VARCHAR2(255), 264 name VARCHAR2(255) NOT NULL UNIQUE,
260 wst_unit_id NUMBER(38,0), 265 wst_unit_id NUMBER(38,0) NOT NULL,
261 PRIMARY KEY (id) 266 PRIMARY KEY (id)
262 ); 267 );
263 268
264 269
265 -- TIME_INTERVALS 270 -- TIME_INTERVALS
277 --- UNITS 282 --- UNITS
278 CREATE SEQUENCE UNITS_ID_SEQ; 283 CREATE SEQUENCE UNITS_ID_SEQ;
279 284
280 CREATE TABLE units ( 285 CREATE TABLE units (
281 id NUMBER(38,0) NOT NULL, 286 id NUMBER(38,0) NOT NULL,
282 name VARCHAR2(255), 287 name VARCHAR2(255) NOT NULL UNIQUE,
283 PRIMARY KEY (id) 288 PRIMARY KEY (id)
284 ); 289 );
285 290
286 291
287 -- WST_COLUMN_Q_RANGES 292 -- WST_COLUMN_Q_RANGES
288 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; 293 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
289 294
290 CREATE TABLE wst_column_q_ranges ( 295 CREATE TABLE wst_column_q_ranges (
291 id NUMBER(38,0) NOT NULL, 296 id NUMBER(38,0) NOT NULL,
292 wst_column_id NUMBER(38,0), 297 wst_column_id NUMBER(38,0) NOT NULL,
293 wst_q_range_id NUMBER(38,0), 298 wst_q_range_id NUMBER(38,0) NOT NULL,
299 UNIQUE (wst_column_id, wst_q_range_id),
294 PRIMARY KEY (id) 300 PRIMARY KEY (id)
295 ); 301 );
296 302
297 303
298 -- WST_COLUMN_VALUES 304 -- WST_COLUMN_VALUES
299 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; 305 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
300 306
301 CREATE TABLE wst_column_values ( 307 CREATE TABLE wst_column_values (
302 id NUMBER(38,0) NOT NULL, 308 id NUMBER(38,0) NOT NULL,
303 position NUMBER(38,5), 309 position NUMBER(38,5) NOT NULL,
304 w NUMBER(38,5), 310 w NUMBER(38,5) NOT NULL,
305 wst_column_id NUMBER(38,0), 311 wst_column_id NUMBER(38,0) NOT NULL,
312 UNIQUE (position, wst_column_id),
313 UNIQUE (position, wst_column_id, w),
306 PRIMARY KEY (id) 314 PRIMARY KEY (id)
307 ); 315 );
308 316
309 317
310 -- WST_COLUMNS 318 -- WST_COLUMNS
311 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; 319 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
312 320
313 CREATE TABLE wst_columns ( 321 CREATE TABLE wst_columns (
314 id NUMBER(38,0) NOT NULL, 322 id NUMBER(38,0) NOT NULL,
315 description VARCHAR2(255), 323 description VARCHAR2(255),
316 name VARCHAR2(255), 324 name VARCHAR2(255) NOT NULL,
317 position NUMBER(38,0), 325 position NUMBER(38,0) NOT NULL DEFAULT 0,
318 time_interval_id NUMBER(38,0), 326 time_interval_id NUMBER(38,0),
319 wst_id NUMBER(38,0), 327 wst_id NUMBER(38,0) NOT NULL,
328 UNIQUE (wst_id, name),
329 UNIQUE (wst_id, position),
320 PRIMARY KEY (id) 330 PRIMARY KEY (id)
321 ); 331 );
322 332
323 333
324 -- WST_Q_RANGES 334 -- WST_Q_RANGES
325 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; 335 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
326 336
327 CREATE TABLE wst_q_ranges ( 337 CREATE TABLE wst_q_ranges (
328 id NUMBER(38,0) NOT NULL, 338 id NUMBER(38,0) NOT NULL,
329 q NUMBER(38,5), 339 q NUMBER(38,5) NOT NULL,
330 range_id NUMBER(38,0), 340 range_id NUMBER(38,0) NOT NULL,
331 PRIMARY KEY (id) 341 PRIMARY KEY (id)
332 ); 342 );
333 343
334 344
335 -- WSTS 345 -- WSTS
350 360
351 CREATE SEQUENCE WSTS_ID_SEQ; 361 CREATE SEQUENCE WSTS_ID_SEQ;
352 362
353 CREATE TABLE wsts ( 363 CREATE TABLE wsts (
354 id NUMBER(38,0) NOT NULL, 364 id NUMBER(38,0) NOT NULL,
355 description VARCHAR2(255), 365 description VARCHAR2(255) NOT NULL,
356 kind NUMBER(38,0), 366 kind NUMBER(38,0) NOT NULL,
357 river_id NUMBER(38,0), 367 river_id NUMBER(38,0) NOT NULL,
368 UNIQUE (river_id, description),
358 PRIMARY KEY (id) 369 PRIMARY KEY (id)
359 ); 370 );
360 371
361 372
362 -- ADD CONSTRAINTs 373 -- ADD CONSTRAINTs
http://dive4elements.wald.intevation.org