Mercurial > dive4elements > river
annotate flys-backend/doc/schema/postgresql-minfo.sql @ 5408:91f5908c472a
Add building_kinds to drop script and fix syntax of buildings table
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Mon, 25 Mar 2013 17:01:07 +0100 |
parents | e88d55d01cf5 |
children | 6875a9f02097 |
rev | line source |
---|---|
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
1 BEGIN; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
2 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
3 CREATE SEQUENCE LOCATION_SYSTEM_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
4 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
5 CREATE TABLE location_system ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
6 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
7 name VARCHAR(32) NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
8 description VARCHAR(255), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
9 PRIMARY KEY(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
10 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
11 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
12 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
13 CREATE SEQUENCE ELEVATION_MODEL_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
14 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
15 CREATE TABLE elevation_model ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
16 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
17 name VARCHAR(32) NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
18 unit_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
19 PRIMARY KEY(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
21 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
22 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
23 |
5202
646c154477fe
SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
Tom Gottfried <tom@intevation.de>
parents:
4991
diff
changeset
|
24 -- lookup table for bedheight types |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
25 CREATE TABLE bed_height_type ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
26 id int NOT NULL, |
5273
db196abd16ca
Use lookup table from bedd_height from database in importer.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5206
diff
changeset
|
27 name VARCHAR(64) NOT NULL, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
28 PRIMARY KEY(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
29 ); |
5276
170f514a4f29
SCHEMA CHANGE: lookup table for floodmaps and source-field added, Importer adapted to new directory names
Tom Gottfried <tom@intevation.de>
parents:
5275
diff
changeset
|
30 INSERT INTO bed_height_type VALUES (1, 'Querprofile'); |
5275
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
33 INSERT INTO bed_height_type VALUES (4, 'DGM'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
34 INSERT INTO bed_height_type VALUES (5, 'TIN'); |
911f62917753
additional values in lookup table for bed height types
Tom Gottfried <tom@intevation.de>
parents:
5273
diff
changeset
|
35 INSERT INTO bed_height_type VALUES (6, 'Modell'); |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
36 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
37 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
38 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
39 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
40 CREATE TABLE bed_height_single ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
41 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
42 river_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
43 year int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
44 sounding_width int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
45 type_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
46 location_system_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
47 cur_elevation_model_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
48 old_elevation_model_id int, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
49 range_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
50 evaluation_by VARCHAR(255), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
51 description VARCHAR(255), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
52 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
53 CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
54 CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
55 CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
56 CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
57 CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
58 CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
59 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
60 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
61 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
62 CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
63 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
64 CREATE TABLE bed_height_epoch ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
65 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
66 river_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
67 time_interval_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
68 -- sounding_with int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
69 -- type_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
70 cur_elevation_model_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
71 old_elevation_model_id int, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
72 range_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
73 evaluation_by VARCHAR(255), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
74 description VARCHAR(255), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
75 PRIMARY KEY(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
76 CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
77 CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
78 CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
79 CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
80 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
81 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
82 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
83 CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
84 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
85 CREATE TABLE bed_height_single_values ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
86 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
87 bed_height_single_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
88 station NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
89 height NUMERIC, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
90 uncertainty NUMERIC, |
3953
fc32a4955087
Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3793
diff
changeset
|
91 data_gap NUMERIC, |
fc32a4955087
Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3793
diff
changeset
|
92 sounding_width NUMERIC, |
fc32a4955087
Towards fix for issue863 (gaps in bedheight data).
Felix Wolfsteller <felix.wolfsteller@intevation.de>
parents:
3793
diff
changeset
|
93 width NUMERIC, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
94 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
95 CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
96 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
97 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
98 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
99 CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
100 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
101 CREATE TABLE bed_height_epoch_values ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
102 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
103 bed_height_epoch_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
104 station NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
105 height NUMERIC, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
106 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
107 CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
108 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
109 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
110 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
111 CREATE SEQUENCE DEPTHS_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
112 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
113 CREATE TABLE depths ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
114 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
115 lower NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
116 upper NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
117 unit_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
118 PRIMARY KEY(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
119 CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
120 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
121 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
122 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
123 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
124 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
125 CREATE TABLE sediment_density ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
126 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
127 river_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
128 depth_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
129 unit_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
130 description VARCHAR(256), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
131 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
132 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
133 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
134 CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
135 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
136 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
137 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
138 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
139 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
140 CREATE TABLE sediment_density_values ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
141 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
142 sediment_density_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
143 station NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
144 density NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
145 description VARCHAR(256), |
4523
504cd5801785
Added new column 'year' to sediment density values in postgresql and oracle
Raimund Renkert <rrenkert@intevation.de>
parents:
4193
diff
changeset
|
146 year int, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
147 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
148 CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
149 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
150 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
151 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
152 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
153 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
154 CREATE TABLE morphologic_width ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
155 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
156 river_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
157 unit_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
158 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
159 CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
160 CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
161 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
162 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
163 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
164 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
165 |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
166 CREATE TABLE morphologic_width_values ( |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
167 id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
168 morphologic_width_id int NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
169 station NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
170 width NUMERIC NOT NULL, |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
171 description VARCHAR(256), |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
172 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
173 CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
174 ); |
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
175 |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
176 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
177 CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
178 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
179 CREATE TABLE discharge_zone ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
180 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
181 river_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
182 gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
183 value NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
184 lower_discharge VARCHAR(16) NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
185 upper_discharge VARCHAR(16), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
186 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
187 CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
188 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
189 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
190 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
191 CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
192 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
193 CREATE TABLE flow_velocity_model ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
194 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
195 discharge_zone_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
196 description VARCHAR(256), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
197 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
198 CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
199 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
200 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
201 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
202 CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
203 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
204 CREATE TABLE flow_velocity_model_values ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
205 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
206 flow_velocity_model_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
207 station NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
208 q NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
209 total_channel NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
210 main_channel NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
211 shear_stress NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
212 PRIMARY KEY(id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
213 CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
214 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
215 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
216 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
217 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
218 CREATE SEQUENCE FV_MEASURE_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
219 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
220 CREATE TABLE flow_velocity_measurements ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
221 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
222 river_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
223 description VARCHAR(256), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
224 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
225 CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
226 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
227 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
228 CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
229 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
230 CREATE TABLE flow_velocity_measure_values ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
231 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
232 measurements_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
233 station NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
234 datetime TIMESTAMP, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
235 w NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
236 q NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
237 v NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
238 description VARCHAR(256), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
239 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
240 CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
241 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
242 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
243 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
244 CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
245 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
246 CREATE TABLE grain_fraction ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
247 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
248 name VARCHAR(64) NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
249 lower NUMERIC, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
250 upper NUMERIC, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
251 PRIMARY KEY (id), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
252 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
253 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
254 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
255 CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
256 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
257 CREATE TABLE sediment_yield ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
258 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
259 river_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
260 grain_fraction_id int, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
261 unit_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
262 time_interval_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
263 description VARCHAR(256), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
264 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
265 CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
266 CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
267 CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
268 CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
269 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
270 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
271 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
272 CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
273 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
274 CREATE TABLE sediment_yield_values ( |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
275 id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
276 sediment_yield_id int NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
277 station NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
278 value NUMERIC NOT NULL, |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
279 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE |
2858
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
281 ); |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
282 |
c3b2673eafbf
Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2823
diff
changeset
|
283 |
4193
f63b39799d2d
Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3953
diff
changeset
|
284 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; |
f63b39799d2d
Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3953
diff
changeset
|
285 CREATE TABLE measurement_station ( |
5206
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
286 id int NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
287 name VARCHAR(256) NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
288 river_id int NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
289 station NUMERIC NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
290 range_id int NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
291 measurement_type VARCHAR(64) NOT NULL, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
292 riverside VARCHAR(16), |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
293 reference_gauge_id int, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
294 observation_timerange_id int, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
295 operator VARCHAR(64), |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
296 description VARCHAR(512), |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
297 PRIMARY KEY (id), |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
298 CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
299 CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
300 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
301 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id), |
8667f629d238
SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents:
5202
diff
changeset
|
302 UNIQUE (river_id, station) |
4193
f63b39799d2d
Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3953
diff
changeset
|
303 ); |
f63b39799d2d
Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3953
diff
changeset
|
304 |
f63b39799d2d
Adapted DB schema (added relation measurement_station); improved importer to read files from filesystem with measurement stations.
Ingo Weinzierl <ingo.weinzierl@intevation.de>
parents:
3953
diff
changeset
|
305 |
3793
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
306 CREATE SEQUENCE SQ_RELATION_ID_SEQ; |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
307 |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
308 CREATE TABLE sq_relation ( |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
309 id int NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
310 river_id int NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
311 time_interval_id int NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
312 description VARCHAR(256), |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
313 PRIMARY KEY (id), |
4991
b79eb203032d
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
Tom Gottfried <tom.gottfried@intevation.de>
parents:
4523
diff
changeset
|
314 CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, |
3793
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
315 CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
316 ); |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
317 |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
318 |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
319 CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
320 |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
321 CREATE TABLE sq_relation_value ( |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
322 id int NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
323 sq_relation_id int NOT NULL, |
5406
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
324 m_station_id int NOT NULL, |
3793
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
325 parameter VARCHAR(16) NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
326 fraction VARCHAR(32) NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
327 a NUMERIC NOT NULL, |
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
328 b NUMERIC NOT NULL, |
5406
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
329 q_max NUMERIC, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
330 r_sq NUMERIC, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
331 n_tot int, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
332 n_outl int, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
333 cferguson NUMERIC, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
334 cduan NUMERIC, |
3793
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
335 PRIMARY KEY (id), |
5406
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
336 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, |
e88d55d01cf5
SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents:
5321
diff
changeset
|
337 CONSTRAINT fk_mstation_id FOREIGN KEY (m_station_id) REFERENCES measurement_station(id) |
3793
8f232119011e
FYLS-Backend: Bring MINFO schemes of Oracle and PostgreSQL back in sync.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2858
diff
changeset
|
338 ); |
2823
13b3fcaa1b34
Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff
changeset
|
339 COMMIT; |