Mercurial > gemma

comparison schema/gemma.sql @ 1879:9a2fbeaabd52 dev-pdf-generation

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merging in from branch default
author Bernhard Reiter <bernhard@intevation.de>
date Tue, 15 Jan 2019 10:07:10 +0100
parents 8fd132b9cdbd
children 99cf6da4c8c0
comparison
equal deleted inserted replaced
1878:f030182f82f1 1879:9a2fbeaabd52
137 -- Clarify! 137 -- Clarify!
138 -- TODO: Do we need an attribute "meaning" or so? 138 -- TODO: Do we need an attribute "meaning" or so?
139 ); 139 );
140 140
141 CREATE TABLE levels_of_service ( 141 CREATE TABLE levels_of_service (
142 level_of_service smallint PRIMARY KEY 142 level_of_service smallint PRIMARY KEY,
143 ); 143 name varchar(4)
144 INSERT INTO levels_of_service VALUES (1), (2), (3); 144 );
145 INSERT INTO levels_of_service (
146 level_of_service,
147 name
148 ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');
145 149
146 CREATE TABLE riverbed_materials ( 150 CREATE TABLE riverbed_materials (
147 material varchar PRIMARY KEY 151 material varchar PRIMARY KEY
148 -- XXX: Should this table contain choices from DRC 2.2.3 or 152 -- XXX: Should this table contain choices from DRC 2.2.3 or
149 -- from IENC Encoding Guide M.4.3, attribute NATSUR? 153 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
262 266
263 CREATE TABLE gauge_measurements ( 267 CREATE TABLE gauge_measurements (
264 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 268 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
265 fk_gauge_id isrs NOT NULL REFERENCES gauges, 269 fk_gauge_id isrs NOT NULL REFERENCES gauges,
266 measure_date timestamp with time zone NOT NULL, 270 measure_date timestamp with time zone NOT NULL,
267 -- PRIMARY KEY (fk_gauge_id, measure_date),
268 country_code char(2) NOT NULL REFERENCES countries, 271 country_code char(2) NOT NULL REFERENCES countries,
269 -- TODO: add relations to stuff provided as enumerations 272 -- TODO: add relations to stuff provided as enumerations
270 sender varchar NOT NULL, -- "from" attribute from DRC 273 sender varchar NOT NULL, -- "from" attribute from DRC
271 language_code varchar NOT NULL REFERENCES language_codes, 274 language_code varchar NOT NULL REFERENCES language_codes,
272 date_issue timestamp with time zone NOT NULL, 275 date_issue timestamp with time zone NOT NULL,
273 -- reference_code varchar(4) NOT NULL REFERENCES depth_references, 276 reference_code varchar(4) NOT NULL REFERENCES depth_references,
274 -- XXX: Always ZPG?
275 water_level double precision NOT NULL, 277 water_level double precision NOT NULL,
276 predicted boolean NOT NULL, 278 predicted boolean NOT NULL,
277 is_waterlevel boolean NOT NULL, 279 is_waterlevel boolean NOT NULL,
278 -- XXX: "measure_code" if really only W or Q 280 -- XXX: "measure_code" if really only W or Q
279 -- XXX: Do we need "unit" attribute or can we normalise on import? 281 -- XXX: Do we need "unit" attribute or can we normalise on import?
280 value_min double precision, -- XXX: NOT NULL if predicted? 282 value_min double precision, -- XXX: NOT NULL if predicted?
281 value_max double precision, -- XXX: NOT NULL if predicted? 283 value_max double precision, -- XXX: NOT NULL if predicted?
282 --- TODO: Add a double range type for checking? 284 --- TODO: Add a double range type for checking?
283 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 285 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
284 source_organization varchar NOT NULL -- "originator" 286 source_organization varchar NOT NULL, -- "originator"
285 -- XXX removed staging done temporarily. Currently imported raw data is 287 staging_done boolean NOT NULL DEFAULT false,
286 -- not staged. When importing approved gauge measurements uncomment this 288 -- So we can have a staged and
287 -- and add policy to allow select on this table for waterway_admin 289 -- a non-staged fk_gauge_id/measure_date pair.
288 -- staging_done boolean NOT NULL DEFAULT false 290 UNIQUE (fk_gauge_id, measure_date, staging_done)
289 ) 291 )
290 CREATE TRIGGER gauge_measurements_date_info 292 CREATE TRIGGER gauge_measurements_date_info
291 BEFORE UPDATE ON gauge_measurements 293 BEFORE UPDATE ON gauge_measurements
292 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 294 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
293 295
296 wtwaxs geography(LINESTRING, 4326) NOT NULL, 298 wtwaxs geography(LINESTRING, 4326) NOT NULL,
297 -- TODO: Do we need to check data set quality (DRC 2.1.6)? 299 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
298 objnam varchar NOT NULL, 300 objnam varchar NOT NULL,
299 nobjnam varchar 301 nobjnam varchar
300 ) 302 )
301 CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23))) 303 -- TODO: @tom: Why did you choose this index kind?
304 -- CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
302 305
303 -- This table allows linkage between 1D ISRS location codes and 2D space 306 -- This table allows linkage between 1D ISRS location codes and 2D space
304 -- e.g. for cutting bottleneck area out of waterway area based on virtual 307 -- e.g. for cutting bottleneck area out of waterway area based on virtual
305 -- distance marks along waterway axis (see SUC7). 308 -- distance marks along waterway axis (see SUC7).
306 CREATE TABLE distance_marks_virtual ( 309 CREATE TABLE distance_marks_virtual (
552 REFERENCES internal.user_profiles(username) 555 REFERENCES internal.user_profiles(username)
553 ON DELETE CASCADE 556 ON DELETE CASCADE
554 ON UPDATE CASCADE, 557 ON UPDATE CASCADE,
555 kind varchar NOT NULL, 558 kind varchar NOT NULL,
556 send_email boolean NOT NULL DEFAULT false, 559 send_email boolean NOT NULL DEFAULT false,
557 auto_accept boolean NOT NULL DEFAULT false,
558 cron varchar, 560 cron varchar,
559 url varchar 561 url varchar
562 )
563
564 CREATE TABLE import_configuration_attributes (
565 import_configuration_id int NOT NULL
566 REFERENCES import_configuration(id)
567 ON DELETE CASCADE
568 ON UPDATE CASCADE,
569 k VARCHAR NOT NULL,
570 v TEXT NOT NULL,
571 UNIQUE (import_configuration_id, k)
560 ) 572 )
561 ; 573 ;
562 574
563 -- Configure primary keys for geoserver views 575 -- Configure primary keys for geoserver views
564 INSERT INTO waterway.gt_pk_metadata VALUES ('waterway', 576 INSERT INTO waterway.gt_pk_metadata VALUES ('waterway',
573 'queued', 'running', 'failed', 585 'queued', 'running', 'failed',
574 'pending', 'accepted', 'declined' 586 'pending', 'accepted', 'declined'
575 ); 587 );
576 588
577 CREATE TABLE waterway.imports ( 589 CREATE TABLE waterway.imports (
578 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 590 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
579 state waterway.import_state NOT NULL DEFAULT 'queued', 591 state waterway.import_state NOT NULL DEFAULT 'queued',
580 enqueued timestamp NOT NULL DEFAULT now(), 592 kind varchar NOT NULL,
581 kind varchar NOT NULL, 593 enqueued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
582 username varchar NOT NULL 594 due timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
595 trys_left int,
596 username varchar NOT NULL
583 REFERENCES internal.user_profiles(username) 597 REFERENCES internal.user_profiles(username)
584 ON DELETE CASCADE 598 ON DELETE CASCADE
585 ON UPDATE CASCADE, 599 ON UPDATE CASCADE,
586 signer varchar 600 signer varchar
587 REFERENCES internal.user_profiles(username) 601 REFERENCES internal.user_profiles(username)
588 ON DELETE SET NULL 602 ON DELETE SET NULL
589 ON UPDATE CASCADE, 603 ON UPDATE CASCADE,
590 send_email boolean NOT NULL DEFAULT false, 604 send_email boolean NOT NULL DEFAULT false,
591 auto_accept boolean NOT NULL DEFAULT false, 605 data TEXT,
592 data TEXT, 606 summary TEXT
593 summary TEXT
594 ); 607 );
595 608
596 CREATE INDEX enqueued_idx ON waterway.imports(enqueued, state); 609 CREATE INDEX enqueued_idx ON waterway.imports(enqueued, state);
597 610
598 CREATE TYPE waterway.log_type AS ENUM ('info', 'warn', 'error'); 611 CREATE TYPE waterway.log_type AS ENUM ('info', 'warn', 'error');