Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-minfo.sql @ 5655:53a2ceeae9a3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Create floodmap colorrange dynamically from artifact parameters. Theme setting for colorrange classes is currently not functioning (wasn't before either).
author Christian Lins <christian.lins@intevation.de>
date Thu, 11 Apr 2013 11:25:41 +0200
parents db6c7268b08e
children 88cbe798cbab
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,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
43 year int,
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
44 sounding_width int,
2823
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,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
49 range_id int,
2823
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,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
72 range_id int,
2823
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,
5441
7c1dd9c3f6bd remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5426
diff changeset
117 PRIMARY KEY(id)
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
118 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
119
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 CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;
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 TABLE sediment_density (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
124 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
125 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
126 depth_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
127 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
128 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
129 CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE,
5441
7c1dd9c3f6bd remove unit from sediment density and depths (always t/m3 respectively cm, otherwise a typo)
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5426
diff changeset
130 CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id)
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
131 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
132
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
133
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
134 CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ;
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 CREATE TABLE sediment_density_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
137 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
138 sediment_density_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
139 station NUMERIC NOT NULL,
5507
db6c7268b08e Schema change: add shore offset to sediment density values and be more rigid with input files in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5498
diff changeset
140 shore_offset NUMERIC,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
141 density NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
142 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
143 year int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
144 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
145 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
146 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
147
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
148
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
149 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
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 CREATE TABLE morphologic_width (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
152 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
153 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
154 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
155 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
156 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
157 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
158 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
159
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
160
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
161 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
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 CREATE TABLE morphologic_width_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
164 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
165 morphologic_width_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
166 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
167 width NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
168 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
169 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
170 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
171 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
172
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
173
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
174 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
175
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
176 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
177 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
178 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
179 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
180 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
181 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
182 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
183 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
184 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
185 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
186
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
187
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
188 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
189
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
190 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
191 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
192 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
193 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
194 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
195 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
196 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
197
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
198
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
199 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
200
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
201 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
202 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
203 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
204 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
205 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
206 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
207 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
208 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
209 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
210 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
211 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
212
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
213
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 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
216
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
217 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
218 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
219 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
220 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
221 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
222 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
223 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
224
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
225 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
226
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
227 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
228 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
229 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
230 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
231 datetime TIMESTAMP,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
232 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
233 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
234 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
235 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
236 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
237 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
238 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
239
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
240
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
241 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
242
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
243 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
244 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
245 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
246 lower NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
247 upper NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
248 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
249 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
250
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
251
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
252 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
253
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
254 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
255 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
256 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
257 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
258 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
259 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
260 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
261 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
262 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
263 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
264 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
265 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
266 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
267
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
268
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
269 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
270
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
271 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
272 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
273 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
274 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
275 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
276 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
277 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
278 );
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
279
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
280
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
281 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
282 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
283 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
284 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
285 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
286 station NUMERIC NOT NULL,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
287 range_id int,
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
288 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
289 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
290 reference_gauge_id int,
5456
e47c4e9b050e Schema corrected: measurement_station.reference_gauge_station is not needed
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5455
diff changeset
291 -- store name of reference gauges here too, as not all are in gauges
5453
303701ab73c6 store name and km of reference gauges in measurement_stations too, as not all are in gauges
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5448
diff changeset
292 reference_gauge_name VARCHAR(64),
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
293 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
294 operator VARCHAR(64),
5475
b6a6d13eec41 backout r5460 to be compatible with r5471 and Oracle database
Tom Gottfried <tom@intevation.de>
parents: 5460
diff changeset
295 description VARCHAR(512),
5206
8667f629d238 SCHEME CHANGE: Add measurement_station to oracle table and rename comment column
Andre Heinecke <aheinecke@intevation.de>
parents: 5202
diff changeset
296 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
297 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
298 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
299 CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE,
5478
5bb3610b94f7 Schema Change: removed constraint that did not allow to have more than one measurement_type per station
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5475
diff changeset
300 CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id)
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
301 );
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
302
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
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
304 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
305
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 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
307 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
308 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
309 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
310 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
311 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
312 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
313 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
314 );
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
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 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
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 TABLE sq_relation_value (
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
320 id int NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
321 sq_relation_id int NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
322 measurement_station_id int NOT NULL,
5455
45c5b8b08ffd Schema Change: use parameter instead of fraction in sq_relation_value
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5453
diff changeset
323 parameter VARCHAR(1) NOT NULL,
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
324 a NUMERIC NOT NULL,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
325 b NUMERIC NOT NULL,
5498
d459a885c51f Schema change: make sq_relation_value.qmax NOT NULL and more informative messages in parser
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5478
diff changeset
326 qmax NUMERIC NOT NULL,
5426
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
327 rsq NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
328 ntot int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
329 noutl int,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
330 cferguson NUMERIC,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
331 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
332 PRIMARY KEY (id),
5406
e88d55d01cf5 SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
Tom Gottfried <tom@intevation.de>
parents: 5321
diff changeset
333 CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
5448
b279f2d4bc78 SCHEMA CHANGE: fixed constraints in MINFO and removed obsolete column
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5441
diff changeset
334 CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_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
335 );
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
336 COMMIT;
http://dive4elements.wald.intevation.org