Mercurial > dive4elements > river
annotate backend/doc/schema/postgresql-sinfo-uinfo.sql @ 9641:f96be528ee35
Removed AS for table aliases in infrastructure queries to avoid Oracle problem
| author | mschaefer |
|---|---|
| date | Mon, 02 Dec 2019 09:24:38 +0100 |
| parents | d889ffe2fb05 |
| children | 9b8ba3b83a15 |
| rev | line source |
|---|---|
| 8968 | 1 BEGIN; |
| 2 | |
| 3 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
4 --Add new column wsts.sinfo_selection |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
5 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
6 ALTER TABLE wsts ADD COLUMN sinfo_selection VARCHAR(1) CHECK((sinfo_selection IS NULL) OR (sinfo_selection IN ('W','Q'))); |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
7 COMMENT ON COLUMN wsts.sinfo_selection IS 'Whether and how the WST series is selected within S-INFO calculation types ("W"=without-Q-group, "Q"=with-Q-group, NULL=not-selectable)' ; |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
8 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
9 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
10 --Add new columns bed_height.sounding_width_info and bed_height.comment |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
11 |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
12 ALTER TABLE bed_height |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
13 ADD sounding_width_info VARCHAR(256), |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
14 ADD notes VARCHAR(256); |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
15 COMMENT ON COLUMN bed_height.sounding_width_info IS 'File header line info "ausgewertete Peilbreite"' ; |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
16 COMMENT ON COLUMN bed_height.notes IS 'File header line info "weitere Bemerkungen"' ; |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
17 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
18 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
19 --Add new columns bed_height_values.min_height (Talweg) and bed_height_values.max_height (Kammlage) (same type as existing height column) |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
20 |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
21 ALTER TABLE bed_height_values |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
22 ADD min_height NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
23 ADD max_height NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
24 ADD height01 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
25 ADD height02 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
26 ADD height03 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
27 ADD height04 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
28 ADD height05 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
29 ADD height06 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
30 ADD height07 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
31 ADD height08 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
32 ADD height09 NUMERIC, |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
33 ADD height10 NUMERIC; |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
34 COMMENT ON COLUMN bed_height_values.min_height IS 'Minimum bed height (Talweg) in m' ; |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
35 COMMENT ON COLUMN bed_height_values.max_height IS 'Maximum bed height (Kammlage) in m' ; |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
36 COMMENT ON COLUMN bed_height_values.height01 IS 'Bed height of section 1 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
37 COMMENT ON COLUMN bed_height_values.height02 IS 'Bed height of section 2 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
38 COMMENT ON COLUMN bed_height_values.height03 IS 'Bed height of section 3 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
39 COMMENT ON COLUMN bed_height_values.height04 IS 'Bed height of section 4 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
40 COMMENT ON COLUMN bed_height_values.height05 IS 'Bed height of section 5 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
41 COMMENT ON COLUMN bed_height_values.height06 IS 'Bed height of section 6 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
42 COMMENT ON COLUMN bed_height_values.height07 IS 'Bed height of section 7 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
43 COMMENT ON COLUMN bed_height_values.height08 IS 'Bed height of section 8 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
44 COMMENT ON COLUMN bed_height_values.height09 IS 'Bed height of section 9 in m' ; |
|
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
45 COMMENT ON COLUMN bed_height_values.height10 IS 'Bed height of section 10 in m' ; |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
46 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
47 |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
48 --Bed mobility |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
49 |
| 8968 | 50 CREATE TABLE bed_mobility ( |
| 51 id NUMERIC(9,0) PRIMARY KEY, | |
| 52 river_id integer NOT NULL CONSTRAINT cBedMobilityRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 53 kmrange_info VARCHAR(32), | |
| 54 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
55 notes VARCHAR(256) |
| 8968 | 56 ); |
| 57 COMMENT ON TABLE bed_mobility IS 'Longitudinal section of the bed mobility of a river' ; | |
| 58 COMMENT ON COLUMN bed_mobility.kmrange_info IS 'File header line info "Strecke"' ; | |
| 59 COMMENT ON COLUMN bed_mobility.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
60 COMMENT ON COLUMN bed_mobility.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 61 |
| 62 CREATE SEQUENCE BED_MOBILITY_ID_SEQ ; | |
| 63 | |
| 64 | |
| 65 CREATE TABLE bed_mobility_values ( | |
| 66 id NUMERIC(9,0) PRIMARY KEY, | |
| 67 bed_mobility_id NUMERIC(9,0) NOT NULL CONSTRAINT cBedMobilityValuesBedMobility REFERENCES bed_mobility(id) ON DELETE CASCADE, | |
| 68 station NUMERIC(7,3) NOT NULL, | |
| 69 moving NUMERIC(1,0) DEFAULT 0 NOT NULL CHECK(moving IN (0,1)) | |
| 70 ); | |
| 71 COMMENT ON TABLE bed_mobility_values IS 'Bed mobility of a km' ; | |
| 72 COMMENT ON COLUMN bed_mobility_values.station IS 'River km' ; | |
| 73 COMMENT ON COLUMN bed_mobility_values.moving IS 'Whether the river bed at the station is moving' ; | |
| 74 | |
| 75 CREATE SEQUENCE BED_MOBILITY_VALUES_ID_SEQ ; | |
| 76 | |
| 77 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
78 --Infrastructure |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
79 |
| 8968 | 80 CREATE TABLE infrastructure ( |
| 81 id NUMERIC(9,0) PRIMARY KEY, | |
| 82 river_id integer NOT NULL CONSTRAINT cInfrastructureRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 83 annotation_type_id integer NOT NULL CONSTRAINT cInfrastructureAnnotationType REFERENCES annotation_types(id), | |
|
9614
d889ffe2fb05
Nachtrag Pos. 20: rename type/part to group/type, group added in Infrastructure class
mschaefer
parents:
9508
diff
changeset
|
84 group_id integer NOT NULL CONSTRAINT cInfrastructureGroupAnnoType REFERENCES annotation_types(id), |
| 8968 | 85 year NUMERIC(4,0) CHECK((year >= 1700) AND (year <= 2199)), |
| 86 dataprovider VARCHAR(256), | |
| 87 evaluation_by VARCHAR(256), | |
| 88 kmrange_info VARCHAR(32), | |
| 89 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
90 notes VARCHAR(256) |
| 8968 | 91 ); |
| 92 COMMENT ON TABLE infrastructure IS 'Longitudinal section of infrastructures of a river and a type' ; | |
|
9614
d889ffe2fb05
Nachtrag Pos. 20: rename type/part to group/type, group added in Infrastructure class
mschaefer
parents:
9508
diff
changeset
|
93 COMMENT ON COLUMN infrastructure.group_id IS 'Reference to the infrastructure type group'; |
| 8968 | 94 COMMENT ON COLUMN infrastructure.year IS 'File header line info "Stand"' ; |
| 95 COMMENT ON COLUMN infrastructure.dataprovider IS 'File header line info "Datenherkunft"' ; | |
| 96 COMMENT ON COLUMN infrastructure.evaluation_by IS 'File header line info "Auswerter"' ; | |
| 97 COMMENT ON COLUMN infrastructure.kmrange_info IS 'File header line info "Strecke"' ; | |
| 98 COMMENT ON COLUMN infrastructure.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
99 COMMENT ON COLUMN infrastructure.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 100 |
| 101 CREATE SEQUENCE INFRASTRUCTURE_ID_SEQ ; | |
| 102 | |
| 103 | |
| 104 CREATE TABLE infrastructure_values ( | |
| 105 id NUMERIC(9,0) PRIMARY KEY, | |
| 106 infrastructure_id NUMERIC(9,0) CONSTRAINT cInfrastructureValuesInfrastructure REFERENCES infrastructure(id) ON DELETE CASCADE, | |
| 107 station NUMERIC(7,3) NOT NULL, | |
| 108 attribute_id integer CONSTRAINT cInfrastructureValuesAttributes REFERENCES attributes(id), | |
| 109 height NUMERIC(6,2) | |
| 110 ); | |
| 111 COMMENT ON TABLE infrastructure_values IS 'Infrastructure at a river station' ; | |
| 112 COMMENT ON COLUMN infrastructure_values.station IS 'River km' ; | |
| 113 COMMENT ON COLUMN infrastructure_values.height IS 'Geodetic height of the top of the infrastructure in m' ; | |
| 114 | |
| 115 CREATE SEQUENCE INFRASTRUCTURE_VALUES_ID_SEQ ; | |
| 116 | |
| 117 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
118 --Channel |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
119 |
| 8968 | 120 CREATE TABLE channel ( |
| 121 id NUMERIC(9,0) PRIMARY KEY, | |
| 122 river_id integer NOT NULL CONSTRAINT cChannelRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 123 kmrange_info VARCHAR(32), | |
| 124 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
125 notes VARCHAR(256), |
| 8968 | 126 year_from NUMERIC(4,0), |
| 127 year_to NUMERIC(4,0) | |
| 128 ); | |
| 129 COMMENT ON TABLE channel IS 'Longitudinal section of the navigable channel of a river' ; | |
| 130 COMMENT ON COLUMN channel.kmrange_info IS 'File header line info "Strecke"' ; | |
| 131 COMMENT ON COLUMN channel.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
132 COMMENT ON COLUMN channel.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 133 COMMENT ON COLUMN channel.year_from IS 'Start year of the period for which the channel values are valid, NULL when valid for the whole past'; |
| 134 COMMENT ON COLUMN channel.year_to IS 'End year of the period for which the channel values are valid, NULL when unlimited'; | |
| 135 | |
| 136 | |
| 137 CREATE SEQUENCE CHANNEL_ID_SEQ ; | |
| 138 | |
| 139 | |
| 140 CREATE TABLE channel_values ( | |
| 141 id NUMERIC(9,0) PRIMARY KEY, | |
| 142 channel_id NUMERIC(9,0) CONSTRAINT cChannelValuesChannel REFERENCES channel(id) ON DELETE CASCADE, | |
| 143 station NUMERIC(7,3) NOT NULL, | |
| 144 width NUMERIC(6,2), | |
| 145 depth NUMERIC(6,2) | |
| 146 ); | |
| 147 COMMENT ON TABLE channel_values IS 'Nominal size of the navigable channel at a river station' ; | |
| 148 COMMENT ON COLUMN channel_values.station IS 'River km' ; | |
| 149 COMMENT ON COLUMN channel_values.width IS 'Nominal width of the channel in m' ; | |
| 150 COMMENT ON COLUMN channel_values.depth IS 'Nominal depth of the channel in m' ; | |
| 151 | |
| 152 CREATE SEQUENCE CHANNEL_VALUES_ID_SEQ ; | |
| 153 | |
| 154 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
155 --Collision |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
156 |
| 8968 | 157 CREATE TABLE collision_type ( |
| 158 id NUMERIC(9,0) PRIMARY KEY, | |
| 159 name VARCHAR(64) NOT NULL UNIQUE | |
| 160 ); | |
| 161 COMMENT ON TABLE collision_type IS 'Type of a ship collision' ; | |
| 162 COMMENT ON COLUMN collision_type.name IS 'Name of the collision type' ; | |
| 163 | |
| 164 CREATE SEQUENCE COLLISION_TYPE_ID_SEQ ; | |
| 165 | |
| 166 | |
| 167 CREATE TABLE collision ( | |
| 168 id NUMERIC(9,0) PRIMARY KEY, | |
| 169 river_id integer NOT NULL CONSTRAINT cCollisionRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 170 year NUMERIC(4,0) NOT NULL, | |
| 171 kmrange_info VARCHAR(32), | |
| 172 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
173 notes VARCHAR(256) |
| 8968 | 174 ); |
| 175 COMMENT ON TABLE collision IS 'Longitudinal section of ship collisions with the river bed' ; | |
| 176 COMMENT ON COLUMN collision.year IS 'File header line info "Jahr"' ; | |
| 177 COMMENT ON COLUMN collision.kmrange_info IS 'File header line info "Strecke"' ; | |
| 178 COMMENT ON COLUMN collision.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
179 COMMENT ON COLUMN collision.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 180 |
| 181 CREATE SEQUENCE COLLISION_ID_SEQ ; | |
| 182 | |
| 183 | |
| 184 CREATE TABLE collision_values ( | |
| 185 id NUMERIC(9,0) PRIMARY KEY, | |
| 186 collision_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValueCollision REFERENCES collision(id) ON DELETE CASCADE, | |
| 187 station NUMERIC(7,3) NOT NULL, | |
| 188 event_date DATE NOT NULL, | |
| 189 gauge_w NUMERIC(6,2), | |
| 190 gauge_name VARCHAR(64), | |
| 191 collision_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cCollisionValuesCollisionType REFERENCES collision_type(id) | |
| 192 ); | |
| 193 COMMENT ON TABLE collision_values IS 'Collision event' ; | |
| 194 COMMENT ON COLUMN collision_values.station IS 'River km' ; | |
| 195 COMMENT ON COLUMN collision_values.event_date IS 'Date of the collision' ; | |
| 196 COMMENT ON COLUMN collision_values.gauge_w IS 'Waterlevel during the collision' ; | |
| 197 COMMENT ON COLUMN collision_values.gauge_name IS 'Name of the gauge' ; | |
| 198 | |
| 199 CREATE SEQUENCE COLLISION_VALUES_ID_SEQ ; | |
| 200 | |
| 201 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
202 --Tkh |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
203 |
| 8968 | 204 CREATE TABLE tkh ( |
| 205 id NUMERIC(9,0) PRIMARY KEY, | |
| 206 river_id integer NOT NULL CONSTRAINT cTkhRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
207 day DATE, |
| 8968 | 208 kmrange_info VARCHAR(32), |
| 209 filename VARCHAR(256) NOT NULL, | |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
210 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('messung','berechnung')), |
| 8968 | 211 sounding_info VARCHAR(64), |
| 212 evaluation_by VARCHAR(256), | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
213 notes VARCHAR(256) |
| 8968 | 214 ); |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
215 COMMENT ON TABLE tkh IS 'Longitudinal section of computed Transportkörperhöhe of a river' ; |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
216 COMMENT ON COLUMN tkh.day IS 'File header line info "Datum"' ; |
| 8968 | 217 COMMENT ON COLUMN tkh.kmrange_info IS 'File header line info "Strecke"' ; |
| 218 COMMENT ON COLUMN tkh.filename IS 'Name without type extension of the imported file' ; | |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
219 COMMENT ON COLUMN tkh.groupname IS 'Group the file belongs to' ; |
| 8968 | 220 COMMENT ON COLUMN tkh.sounding_info IS 'File header line info "Peilung"' ; |
| 221 COMMENT ON COLUMN tkh.evaluation_by IS 'File header line info "Auswerter"' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
222 COMMENT ON COLUMN tkh.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 223 |
| 224 CREATE SEQUENCE TKH_ID_SEQ ; | |
| 225 | |
| 226 | |
| 227 CREATE TABLE tkh_column ( | |
| 228 id NUMERIC(9,0) PRIMARY KEY, | |
| 229 tkh_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhColumnTkh REFERENCES tkh(id) ON DELETE CASCADE, | |
| 230 name VARCHAR(64) NOT NULL | |
| 231 ); | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
232 COMMENT ON TABLE tkh_column IS 'Longitudinal section of computed Transportkörperhöhe for a waterlevel series' ; |
| 8968 | 233 COMMENT ON COLUMN tkh_column.name IS 'Name of the tkh computation column' ; |
| 234 | |
| 235 CREATE SEQUENCE TKH_COLUMN_ID_SEQ ; | |
| 236 | |
| 237 | |
| 238 CREATE TABLE tkh_values ( | |
| 239 id NUMERIC(9,0) PRIMARY KEY, | |
| 240 tkh_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cTkhValuesTkhColumn REFERENCES tkh_column(id) ON DELETE CASCADE, | |
| 241 station NUMERIC(7,3) NOT NULL, | |
| 242 tkheight NUMERIC(7,3) | |
| 243 ); | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
244 COMMENT ON TABLE tkh_values IS 'Transportkörperhöhe of a river station and referenced file column' ; |
| 8968 | 245 COMMENT ON COLUMN tkh_values.station IS 'River km' ; |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
246 COMMENT ON COLUMN tkh_values.tkheight IS 'Transportkörperhöhe of a river station computed for a waterlevel in m' ; |
| 8968 | 247 |
| 248 CREATE SEQUENCE TKH_VALUES_ID_SEQ ; | |
| 249 | |
| 250 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
251 --Flow depth |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
252 |
| 8968 | 253 CREATE TABLE flow_depth ( |
| 254 id NUMERIC(9,0) PRIMARY KEY, | |
| 255 river_id integer NOT NULL CONSTRAINT cFlowDepthRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 256 year NUMERIC(4,0) NOT NULL, | |
| 257 kmrange_info VARCHAR(32), | |
| 258 filename VARCHAR(256) NOT NULL, | |
| 259 sounding_info VARCHAR(64), | |
| 260 evaluation_by VARCHAR(255), | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
261 notes VARCHAR(256) |
| 8968 | 262 ); |
| 263 | |
| 264 COMMENT ON TABLE flow_depth IS 'Longitudinal section of computed flow depth of a river' ; | |
| 265 COMMENT ON COLUMN flow_depth.year IS 'File header line info "Bezugsjahr"' ; | |
| 266 COMMENT ON COLUMN flow_depth.kmrange_info IS 'File header line info "Strecke"' ; | |
| 267 COMMENT ON COLUMN flow_depth.filename IS 'Name without type extension of the imported file' ; | |
| 268 COMMENT ON COLUMN flow_depth.sounding_info IS 'File header line info "Peilung"' ; | |
| 269 COMMENT ON COLUMN flow_depth.evaluation_by IS 'File header line info "Auswerter"' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
270 COMMENT ON COLUMN flow_depth.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 271 |
| 272 CREATE SEQUENCE FLOW_DEPTH_ID_SEQ ; | |
| 273 | |
| 274 | |
| 275 CREATE TABLE flow_depth_column ( | |
| 276 id NUMERIC(9,0) PRIMARY KEY, | |
| 277 flow_depth_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthColumnFlowDepth REFERENCES flow_depth(id) ON DELETE CASCADE, | |
| 278 name VARCHAR(64) NOT NULL | |
| 279 ); | |
| 280 | |
| 281 COMMENT ON TABLE flow_depth_column IS 'Longitudinal section of computed flow depth for a waterlevel series' ; | |
| 282 COMMENT ON COLUMN flow_depth_column.name IS 'Name of the flow depth computation column' ; | |
| 283 | |
| 284 CREATE SEQUENCE FLOW_DEPTH_COLUMN_ID_SEQ ; | |
| 285 | |
| 286 | |
| 287 CREATE TABLE flow_depth_values ( | |
| 288 id NUMERIC(9,0) PRIMARY KEY, | |
| 289 flow_depth_column_id NUMERIC(9,0) NOT NULL CONSTRAINT cFlowDepthValuesFlowDepthColumn REFERENCES flow_depth_column(id) ON DELETE CASCADE, | |
| 290 station NUMERIC(7,3) NOT NULL, | |
| 291 depth NUMERIC(7,3) | |
| 292 ); | |
| 293 | |
| 294 COMMENT ON TABLE flow_depth_values IS 'Flow depth of a river station and referenced file column' ; | |
| 295 COMMENT ON COLUMN flow_depth_values.station IS 'River km' ; | |
| 296 COMMENT ON COLUMN flow_depth_values.depth IS 'Flow depth of a river station computed for a waterlevel in m' ; | |
| 297 | |
| 298 CREATE SEQUENCE FLOW_DEPTH_VALUES_ID_SEQ ; | |
| 299 | |
| 300 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
301 --Depth evolution |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
302 |
| 8968 | 303 CREATE TABLE depth_evolution ( |
| 304 id NUMERIC(9,0) PRIMARY KEY, | |
| 305 river_id integer NOT NULL CONSTRAINT cDepthEvolutionRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 306 reference_year NUMERIC(4,0) NOT NULL, | |
| 307 start_year NUMERIC(4,0) NOT NULL, | |
| 308 curr_sounding VARCHAR(64) NOT NULL, | |
| 309 old_sounding VARCHAR(64) NOT NULL, | |
| 310 kmrange_info VARCHAR(32), | |
| 311 curr_glw VARCHAR(64) NOT NULL, | |
| 312 old_glw VARCHAR(64) NOT NULL, | |
| 313 filename VARCHAR(256) NOT NULL, | |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
314 groupname VARCHAR(32) NOT NULL CHECK(groupname IN ('aktuell','etappe')), |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
315 notes VARCHAR(256) |
| 8968 | 316 ); |
| 317 COMMENT ON TABLE depth_evolution IS 'Longitudinal section of the evolution of the flow depth of a river' ; | |
| 318 COMMENT ON COLUMN depth_evolution.reference_year IS 'File header line info "Bezugsjahr" (GlW)' ; | |
| 319 COMMENT ON COLUMN depth_evolution.start_year IS 'File header line info "Ausgangsjahr" (GlW)' ; | |
| 320 COMMENT ON COLUMN depth_evolution.curr_sounding IS 'File header line info "Aktuelle Peilung / Epoche"' ; | |
| 321 COMMENT ON COLUMN depth_evolution.old_sounding IS 'File header line info "Historische Peilung / Epoche"' ; | |
| 322 COMMENT ON COLUMN depth_evolution.kmrange_info IS 'File header line info "Strecke"' ; | |
| 323 COMMENT ON COLUMN depth_evolution.curr_glw IS 'File header line info "Aktuelle Wasserspiegellage"' ; | |
| 324 COMMENT ON COLUMN depth_evolution.old_glw IS 'File header line info "Historische Wasserspiegellage"' ; | |
| 325 COMMENT ON COLUMN depth_evolution.filename IS 'Name without type extension of the imported file' ; | |
|
9032
1f63e9d3b0ec
New columns for bed_height, tkh and depth_evolution, a few syntactic corrections for Oracle
mschaefer
parents:
9008
diff
changeset
|
326 COMMENT ON COLUMN depth_evolution.groupname IS 'Group the file belongs to' ; |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
327 COMMENT ON COLUMN depth_evolution.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 328 |
| 329 CREATE SEQUENCE DEPTH_EVOLUTION_ID_SEQ ; | |
| 330 | |
| 331 | |
| 332 CREATE TABLE depth_evolution_values ( | |
| 333 id NUMERIC(9,0) PRIMARY KEY, | |
| 334 depth_evolution_id NUMERIC(9,0) NOT NULL CONSTRAINT cDepthEvolutionValuesDepthEvolution REFERENCES depth_evolution(id) ON DELETE CASCADE, | |
| 335 station NUMERIC(7,3) NOT NULL, | |
| 336 total_change NUMERIC(8,4), | |
| 337 change_per_year NUMERIC(8,4) | |
| 338 ); | |
| 339 COMMENT ON TABLE depth_evolution_values IS 'Evolution of the flow depth of a river station in a referenced period of time' ; | |
| 340 COMMENT ON COLUMN depth_evolution_values.station IS 'River km' ; | |
| 341 COMMENT ON COLUMN depth_evolution_values.total_change IS 'Flow depth change of the whole period of time in m' ; | |
| 342 COMMENT ON COLUMN depth_evolution_values.change_per_year IS 'Average yearly flow depth change in the referenced period of time in m' ; | |
| 343 | |
| 344 CREATE SEQUENCE DEPTH_EVOLUTION_VALUES_ID_SEQ ; | |
| 345 | |
| 346 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
347 --Daily discharge |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
348 |
| 8968 | 349 CREATE TABLE daily_discharge ( |
| 350 id NUMERIC(9,0) PRIMARY KEY, | |
| 351 gauge_id integer NOT NULL CONSTRAINT cDailyDischargeGauge REFERENCES gauges(id) ON DELETE CASCADE, | |
| 352 filename VARCHAR(256) NOT NULL | |
| 353 ); | |
| 354 | |
| 355 COMMENT ON TABLE daily_discharge IS 'Daily discharge value series' ; | |
| 356 COMMENT ON COLUMN daily_discharge.filename IS 'Name without type extension of the imported file' ; | |
| 357 | |
| 358 CREATE SEQUENCE DAILY_DISCHARGE_ID_SEQ ; | |
| 359 | |
| 360 | |
| 361 CREATE TABLE daily_discharge_values ( | |
| 362 id NUMERIC(9,0) PRIMARY KEY, | |
| 363 daily_discharge_id NUMERIC(9,0) NOT NULL CONSTRAINT cDailyDischargeValueDailyDischarge REFERENCES daily_discharge(id) ON DELETE CASCADE, | |
| 364 day DATE NOT NULL, | |
| 365 discharge NUMERIC(8,3) NOT NULL | |
| 366 ); | |
| 367 | |
| 368 COMMENT ON TABLE daily_discharge_values IS 'Mean daily discharge of a date and a gauge' ; | |
| 369 COMMENT ON COLUMN daily_discharge_values.day IS 'Date of the discharge value' ; | |
| 370 COMMENT ON COLUMN daily_discharge_values.discharge IS 'Mean daily discharge in m^3/s' ; | |
| 371 | |
| 372 CREATE SEQUENCE DAILY_DISCHARGE_VALUES_ID_SEQ ; | |
| 373 | |
| 374 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
375 --Salix line |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
376 |
| 8968 | 377 CREATE TABLE salix ( |
| 378 id NUMERIC(9,0) PRIMARY KEY, | |
| 379 river_id integer NOT NULL CONSTRAINT cSalixRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 380 evaluation_by VARCHAR(255), | |
| 381 kmrange_info VARCHAR(32), | |
| 382 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
383 notes VARCHAR(256) |
| 8968 | 384 ); |
| 385 | |
| 386 COMMENT ON TABLE salix IS 'Longitudinal section of the salix line parameters of a river' ; | |
| 387 COMMENT ON COLUMN salix.evaluation_by IS 'File header line info "Auswerter"' ; | |
| 388 COMMENT ON COLUMN salix.kmrange_info IS 'File header line info "Strecke"' ; | |
| 389 COMMENT ON COLUMN salix.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
390 COMMENT ON COLUMN salix.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 391 |
| 392 CREATE SEQUENCE SALIX_ID_SEQ ; | |
| 393 | |
| 394 | |
| 395 CREATE TABLE salix_values ( | |
| 396 id NUMERIC(9,0) PRIMARY KEY, | |
|
8990
07dcedddf839
Oracle schema extension added, a few changes in the postgresql schema
mschaefer
parents:
8968
diff
changeset
|
397 salix_id NUMERIC(9,0) NOT NULL CONSTRAINT cSalixValuesSalix REFERENCES salix(id) ON DELETE CASCADE, |
| 8968 | 398 station NUMERIC(7,3) NOT NULL, |
| 399 factor NUMERIC(6,2) NOT NULL, | |
| 400 mnw_mw_diff NUMERIC(6,2) | |
| 401 ); | |
| 402 | |
| 403 COMMENT ON COLUMN salix_values.station IS 'River km' ; | |
| 404 COMMENT ON COLUMN salix_values.factor IS 'Salix "factor" of the station in m' ; | |
| 405 COMMENT ON COLUMN salix_values.mnw_mw_diff IS 'Difference between MNW and MW in m (less than zero)' ; | |
| 406 | |
| 407 CREATE SEQUENCE SALIX_VALUES_ID_SEQ ; | |
| 408 | |
| 409 | |
| 410 CREATE TABLE salix_rank ( | |
| 411 id NUMERIC(9,0) PRIMARY KEY, | |
| 412 min_value NUMERIC(6,2), | |
| 413 max_value NUMERIC(6,2), | |
| 414 name VARCHAR(16) NOT NULL | |
| 415 ); | |
| 416 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (1, NULL, -0.3, 'invalid'); | |
| 417 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (2, -0.3, 0.3, 'very good'); | |
| 418 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (3, 0.3, 0.5, 'good'); | |
| 419 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (4, 0.5, 1.0, 'moderate'); | |
| 420 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (5, 1.0, 1.5, 'bad'); | |
| 421 INSERT INTO salix_rank (id, min_value, max_value, name) VALUES (6, 1.5, NULL, 'very bad'); | |
| 422 | |
| 423 | |
|
9008
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
424 --Vegetation zone |
|
ce99d3db9477
Column tkh.year replaced by tkh.day, alter bed_height and wsts integrated into sinfo-uinfo script
mschaefer
parents:
8990
diff
changeset
|
425 |
| 8968 | 426 CREATE TABLE vegetation_type ( |
| 427 id NUMERIC(9,0) PRIMARY KEY, | |
| 428 name VARCHAR(256) NOT NULL | |
| 429 ); | |
| 430 INSERT INTO vegetation_type (id, name) VALUES (1, 'zonal forest'); | |
| 431 INSERT INTO vegetation_type (id, name) VALUES (2, 'dry hartwood forest floodplain'); | |
| 432 INSERT INTO vegetation_type (id, name) VALUES (3, 'wet hartwood forest floodplain'); | |
| 433 INSERT INTO vegetation_type (id, name) VALUES (4, 'salix alba forest'); | |
| 434 INSERT INTO vegetation_type (id, name) VALUES (5, 'salix shrubs'); | |
| 435 INSERT INTO vegetation_type (id, name) VALUES (6, 'reed bed'); | |
| 436 INSERT INTO vegetation_type (id, name) VALUES (7, 'bank pioneers'); | |
| 437 INSERT INTO vegetation_type (id, name) VALUES (8, 'no vegetation'); | |
| 438 INSERT INTO vegetation_type (id, name) VALUES (9, 'water'); | |
| 439 | |
| 440 | |
| 441 CREATE TABLE vegetation ( | |
| 442 id NUMERIC(9,0) PRIMARY KEY, | |
| 443 river_id integer NOT NULL CONSTRAINT cVegetationRivers REFERENCES rivers(id) ON DELETE CASCADE, | |
| 444 name VARCHAR(256), | |
| 445 filename VARCHAR(256) NOT NULL, | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
446 notes VARCHAR(256) |
| 8968 | 447 ); |
| 448 | |
| 449 COMMENT ON TABLE vegetation IS 'Classification of the vegetation of a river' ; | |
| 450 COMMENT ON COLUMN vegetation.name IS 'File header line info "Einteilung"' ; | |
| 451 COMMENT ON COLUMN vegetation.filename IS 'Name without type extension of the imported file' ; | |
|
9038
4c5eeaff554c
Database column "comment" renamed to "notes" (restrictions in Oracle)
mschaefer
parents:
9032
diff
changeset
|
452 COMMENT ON COLUMN vegetation.notes IS 'File header line info "weitere Bemerkungen"' ; |
| 8968 | 453 |
| 454 CREATE SEQUENCE VEGETATION_ID_SEQ ; | |
| 455 | |
| 456 | |
| 457 CREATE TABLE vegetation_zone ( | |
| 458 id NUMERIC(9,0) PRIMARY KEY, | |
| 459 vegetation_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetation REFERENCES vegetation(id) ON DELETE CASCADE, | |
| 460 vegetation_type_id NUMERIC(9,0) NOT NULL CONSTRAINT cVegetationZoneVegetationType REFERENCES vegetation_type(id) ON DELETE CASCADE, | |
| 461 min_overflow_days NUMERIC(3,0) NOT NULL, | |
|
9401
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
462 max_overflow_days NUMERIC(3,0) NOT NULL, |
|
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
463 color_r NUMERIC(3,0) NOT NULL, |
|
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
464 color_g NUMERIC(3,0) NOT NULL, |
|
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
465 color_b NUMERIC(3,0) NOT NULL |
| 8968 | 466 ); |
| 467 | |
| 468 COMMENT ON TABLE vegetation_zone IS 'Vegetation zone of a river station' ; | |
|
9508
056483975a9c
Comments changed for vegetation_zone.min/max..days
mschaefer
parents:
9502
diff
changeset
|
469 COMMENT ON COLUMN vegetation_zone.min_overflow_days IS 'Minimum number (exclusive) of overflow days in a year for the zone type' ; |
|
056483975a9c
Comments changed for vegetation_zone.min/max..days
mschaefer
parents:
9502
diff
changeset
|
470 COMMENT ON COLUMN vegetation_zone.max_overflow_days IS 'Maximum number (inclusive) of overflow days in a year for the zone type' ; |
|
9401
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
471 COMMENT ON COLUMN vegetation_zone.color_r IS 'Red value (0-255) of the zone color' ; |
|
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
472 COMMENT ON COLUMN vegetation_zone.color_g IS 'Green value (0-255) of the zone color' ; |
|
361de818f76e
Added color fields to the vegetation zone database table and importer, max days field changed from exclusive to inclusive
mschaefer
parents:
9038
diff
changeset
|
473 COMMENT ON COLUMN vegetation_zone.color_b IS 'Blue value (0-255) of the zone color' ; |
| 8968 | 474 |
| 475 CREATE SEQUENCE VEGETATION_ZONE_ID_SEQ ; | |
| 476 | |
| 477 | |
| 478 COMMIT; |