Mercurial > dive4elements > river

annotate flys-backend/doc/schema/postgresql-minfo.sql @ 5462:f2371f3aaf03

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Show top level folder icons only if node has no factory If you have an empty folder the folder icon is still shown. This makes it possible to add functional "Top Level" entries in the Datacage
author Andre Heinecke <aheinecke@intevation.de>
date Tue, 26 Mar 2013 18:29:13 +0100
parents c2bd97cd127f
children b6a6d13eec41
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,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
140 density NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
141 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
142 year int,
2823
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
143 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
144 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
145 );
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 CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ;
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 CREATE TABLE morphologic_width (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
151 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
152 river_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
153 unit_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
154 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
155 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
156 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
157 );
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 CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ;
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 CREATE TABLE morphologic_width_values (
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
163 id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
164 morphologic_width_id int NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
165 station NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
166 width NUMERIC NOT NULL,
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
167 description VARCHAR(256),
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
168 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
169 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
170 );
13b3fcaa1b34 Add MINFO schema for PostgreSQL
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
diff changeset
171
2858
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
172
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
173 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
174
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
175 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
176 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
177 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
178 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
179 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
180 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
181 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
182 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
183 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
184 );
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 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
188
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
189 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
190 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
191 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
192 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
193 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
194 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
195 );
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 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
199
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
200 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
201 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
202 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
203 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
204 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
205 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
206 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
207 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
208 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
209 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
210 );
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 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
215
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
216 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
217 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
218 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
219 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
220 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
221 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
222 );
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 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
225
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
226 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
227 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
228 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
229 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
230 datetime TIMESTAMP,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
231 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
232 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
233 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
234 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
235 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
236 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
237 );
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 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
241
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
242 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
243 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
244 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
245 lower NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
246 upper NUMERIC,
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
247 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
248 );
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 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
252
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
253 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
254 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
255 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
256 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
257 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
258 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
259 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
260 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
261 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
262 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
263 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
264 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
265 );
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 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
269
c3b2673eafbf Adjusted PostgreSQL port of MINFO schema to match Oracle one.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2823
diff changeset
270 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
271 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
272 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
273 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
274 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
275 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
276 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
277 );
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
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
280 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
281 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
282 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
283 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
284 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
285 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
286 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
287 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
288 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
289 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
290 -- 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
291 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
292 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
293 operator VARCHAR(64),
5460
c2bd97cd127f Schema Change: rename measurement_station.description to comment to fit rest of the code
Tom Gottfried <tom.gottfried@intevation.de>
parents: 5456
diff changeset
294 comment 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
295 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
296 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
297 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
298 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
299 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
300 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
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,
6875a9f02097 SCHEMA CHANGE! Renamed columns in sq_relation and sq_relation_value.
Raimund Renkert <rrenkert@intevation.de>
parents: 5406
diff changeset
326 qmax NUMERIC,
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