Mercurial > gemma

comparison schema/gemma.sql @ 1821:332e42a2088d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
DB schema: Made primary key of waterway.gauges an integer for staging purposes.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 16 Jan 2019 11:28:33 +0100
parents 99cf6da4c8c0
children f1351a58da35
comparison
equal deleted inserted replaced
1820:ba24a6e3e64d 1821:332e42a2088d
234 catccl smallint REFERENCES catccls, 234 catccl smallint REFERENCES catccls,
235 dirimp smallint REFERENCES dirimps 235 dirimp smallint REFERENCES dirimps
236 ) 236 )
237 237
238 CREATE TABLE gauges ( 238 CREATE TABLE gauges (
239 location isrs PRIMARY KEY CHECK( 239 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
240 location isrs UNIQUE NOT NULL KEY CHECK(
240 (location).orc SIMILAR TO 'G[[:digit:]]{4}' 241 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
241 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), 242 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
242 objname varchar NOT NULL, 243 objname varchar NOT NULL,
243 is_left boolean, -- XXX: Or reference position_codes? 244 is_left boolean, -- XXX: Or reference position_codes?
244 geom geography(POINT, 4326) NOT NULL, 245 geom geography(POINT, 4326) NOT NULL,
254 ) 255 )
255 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges 256 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
256 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 257 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
257 258
258 CREATE TABLE gauges_reference_water_levels ( 259 CREATE TABLE gauges_reference_water_levels (
259 gauge_id isrs NOT NULL REFERENCES gauges, 260 gauge_id isrs NOT NULL REFERENCES gauges(location),
260 reference_water_level varchar(20) 261 reference_water_level varchar(20)
261 NOT NULL REFERENCES reference_water_levels, 262 NOT NULL REFERENCES reference_water_levels,
262 PRIMARY KEY (gauge_id, reference_water_level), 263 PRIMARY KEY (gauge_id, reference_water_level),
263 value int NOT NULL 264 value int NOT NULL
264 ) 265 )
265 266
266 CREATE TABLE gauge_measurements ( 267 CREATE TABLE gauge_measurements (
267 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 268 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
268 fk_gauge_id isrs NOT NULL REFERENCES gauges, 269 fk_gauge_id isrs NOT NULL REFERENCES gauges(location),
269 measure_date timestamp with time zone NOT NULL, 270 measure_date timestamp with time zone NOT NULL,
270 country_code char(2) NOT NULL REFERENCES countries, 271 country_code char(2) NOT NULL REFERENCES countries,
271 -- TODO: add relations to stuff provided as enumerations 272 -- TODO: add relations to stuff provided as enumerations
272 sender varchar NOT NULL, -- "from" attribute from DRC 273 sender varchar NOT NULL, -- "from" attribute from DRC
273 language_code varchar NOT NULL REFERENCES language_codes, 274 language_code varchar NOT NULL REFERENCES language_codes,
404 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and 405 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
405 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) 406 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
406 CREATE TABLE bottlenecks ( 407 CREATE TABLE bottlenecks (
407 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 408 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
408 bottleneck_id varchar UNIQUE NOT NULL, 409 bottleneck_id varchar UNIQUE NOT NULL,
409 fk_g_fid isrs NOT NULL REFERENCES gauges, 410 fk_g_fid isrs NOT NULL REFERENCES gauges(location),
410 -- XXX: DRC references "ch. 3.1.1", which does not exist in document. 411 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
411 objnam varchar, 412 objnam varchar,
412 nobjnm varchar, 413 nobjnm varchar,
413 stretch isrsrange NOT NULL, 414 stretch isrsrange NOT NULL,
414 area geography(POLYGON, 4326) NOT NULL, 415 area geography(POLYGON, 4326) NOT NULL,