Mercurial > gemma

comparison wamos.sql @ 56:f378959820be

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Started to draft schema for fairway availability.
author Tom Gottfried <tom@intevation.de>
date Wed, 23 May 2018 18:24:02 +0200
parents ecb9bb0591c9
children 30cb2f87c268
comparison
equal deleted inserted replaced
55:ecb9bb0591c9 56:f378959820be
54 -- A smallint PK would require even less disk space i.e. on the FK side. 54 -- A smallint PK would require even less disk space i.e. on the FK side.
55 -- This might be an issue in case cache space becomes a limiting 55 -- This might be an issue in case cache space becomes a limiting
56 -- factor when there are many FKs pointing here. 56 -- factor when there are many FKs pointing here.
57 ); 57 );
58 58
59 --
60 -- Bottlenecks
61 --
59 CREATE TABLE riverbed_materials ( 62 CREATE TABLE riverbed_materials (
60 material varchar PRIMARY KEY 63 material varchar PRIMARY KEY
61 -- XXX: Should this table contain choices from DRC 2.2.3 or 64 -- XXX: Should this table contain choices from DRC 2.2.3 or
62 -- from IENC Encoding Guide M.4.3, attribute NATSUR? 65 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
63 ); 66 );
132 coverage varchar REFERENCES coverage_types, 135 coverage varchar REFERENCES coverage_types,
133 depth_reference char(3) NOT NULL REFERENCES depth_references, 136 depth_reference char(3) NOT NULL REFERENCES depth_references,
134 sounding_data raster NOT NULL 137 sounding_data raster NOT NULL
135 ); 138 );
136 139
140 --
141 -- Fairway availability
142 --
143 CREATE TABLE spot_marks (
144 mark_name varchar PRIMARY KEY
145 -- Use smallint because of fairway availability provided on daily basis?
146 );
147
148 CREATE TABLE levels_of_service (
149 level_of_service smallint PRIMARY KEY
150 );
151
152 CREATE TABLE fairway_availability (
153 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
154 surdat date NOT NULL,
155 PRIMARY KEY (bottleneck_id, surdat),
156 position varchar REFERENCES spot_marks,
157 -- additional_data xml -- Currently not relevant for WAMOS
158 critical boolean,
159 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
160 source_organization varchar NOT NULL
161 );
162 CREATE TRIGGER fairway_availability_date_info
163 BEFORE UPDATE ON fairway_availability
164 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
165
166 CREATE TABLE fa_reference_values (
167 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
168 surdat date NOT NULL,
169 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
170 level_of_service smallint NOT NULL REFERENCES levels_of_service,
171 PRIMARY KEY (bottleneck_id, surdat, level_of_service),
172 fairway_depth smallint,
173 fairway_width smallint,
174 fairway_radius int,
175 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
176 shallowest_spot geometry(POINT, 3146)
177 );
178
179 CREATE TABLE bottleneck_pdfs (
180 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
181 surdat date NOT NULL,
182 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
183 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
184 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
185 PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url),
186 pdf_generation_date timestamp NOT NULL,
187 source_organization varchar NOT NULL
188 );
189
190 CREATE TABLE effective_fairway_availability (
191 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
192 surdat date NOT NULL,
193 FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability
194 -- TODO
195 );
196
137 COMMIT; 197 COMMIT;