gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 5031:8c590ef35280

Improve feedback if geometry cannot be stored This is also a backout of rev. 36a3dce20232

author	Tom Gottfried <tom@intevation.de>
date	Wed, 18 Mar 2020 19:19:09 +0100
parents	737d7859dd86
children	e21cbb9768a2 f64ff954ee31

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	6
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	7 -- Copyright (C) 2018, 2019 by via donau
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	11 -- Author(s):
1301 2304778c4432 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1298 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
1336 f65d1767452c add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1301 diff changeset	13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 diff changeset	14 -- * Sascha Wilde <sascha.wilde@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	15
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	17
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	18 --
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	19 -- Extensions
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	20 --
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 CREATE EXTENSION postgis;
2083 6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	22 -- needed for multi-column GiST indexes with otherwise unsupported types:
6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	23 CREATE EXTENSION btree_gist;
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	24
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	25 --
4602 9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	26 -- Functions to be used in CHECK constraints
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	27 --
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	28
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	29 -- Check if a given string can be used as a FROM item in an SQL statement
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	30 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	31 AS $$
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	32 BEGIN
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	33 EXECUTE format('SELECT * FROM (%s) AS test', stmt);
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	34 RETURN true;
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	35 EXCEPTION
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	36 WHEN OTHERS THEN
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	37 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	38 RETURN false;
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	39 END
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	40 $$
4603 d24e951206ca Do not try to execute NULL as a subquery Tom Gottfried <tom@intevation.de> parents: 4602 diff changeset	41 LANGUAGE plpgsql
d24e951206ca Do not try to execute NULL as a subquery Tom Gottfried <tom@intevation.de> parents: 4602 diff changeset	42 STRICT;
4602 9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	43
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	44
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4236 diff changeset	45 --
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	46 -- Trigger functions
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	47 --
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	48 -- TODO: will there ever be UPDATEs or can we drop that function due to
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	49 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	50 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	51 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	52 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	53 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	54 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	55 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	58
4846 c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	59 --
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	60 -- Trigger functions to be used in constraint triggers
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	61 --
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	62 /* The constraint triggers are subject to a possible race condition
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	63 due to PostgreSQL MVCC: Trigger functions do not see uncommited changes
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	64 of other transactions running in parallel.
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	65
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	66 Since we serialize imports at application level, the race condition
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	67 should not happen, though. In case serialization at application level
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	68 should be removed, database side locking/serialization would have
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	69 to be applied.
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	70 */
c69e35ec6adf Explaining comment on constraint triggers Tom Gottfried <tom@intevation.de> parents: 4755 diff changeset	71
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	72 -- Trigger function to be used as a constraint trigger to enforce uniqueness
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	73 -- of geometries in the column with its name given as an argument to the
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	74 -- trigger function. If additional column names are given,
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	75 -- the group of given columns is tested for equality.
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	76 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	77 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	78 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	79 new_geom geometry;
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	80 tg_arg text;
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	81 filters text;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	82 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	83 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	84 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	85 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	86 USING NEW;
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	87 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	88 -- Test each additional argument for equality
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	89 filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	90 END LOOP;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	91 EXECUTE format(
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	92 'SELECT EXISTS(SELECT 1 FROM %I.%I '
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	93 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	94 '%s)',
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	95 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	96 INTO has_equal
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	97 USING new_geom, NEW;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	98 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	99 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	100 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	101 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	102 USING
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	103 DETAIL = format('Failing row contains geometry in %s',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	104 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	105 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	106 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	107 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	108 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	109 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	110 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	111 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	112 END;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	113 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	114 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	115
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	116 -- Trigger function to be used as a constraint trigger to enforce
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	117 -- existance of a referenced gauge with intersecting validity. The
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	118 -- columns with the referenced gauge isrs code an the validity are
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	119 -- given as arguments to the trigger function.
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	120 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	121 $$
3666 db87f34805fb Align bottleneck validity at gauges Tom Gottfried <tom@intevation.de> parents: 3665 diff changeset	122 DECLARE
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	123 -- FIXME: I'm using text for the isrs code and cast it on demand.
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	124 -- If someone is able to get it to work with isrs or isrs_base as
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	125 -- type, feel free to show me how its done... ;-) [sw]
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	126 referenced_gauge text;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	127 new_validity tstzrange;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	128 BEGIN
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	129 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	130 INTO referenced_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	131 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	132 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	133 INTO new_validity
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	134 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	135 IF EXISTS ( SELECT * FROM waterway.gauges
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	136 WHERE location = referenced_gauge::isrs
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	137 AND validity && new_validity )
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	138 THEN
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	139 RETURN NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	140 ELSE
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	141 RAISE EXCEPTION
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	142 'new row for relation "%" violates constraint trigger "%"',
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	143 TG_TABLE_NAME, TG_NAME
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	144 USING
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	145 DETAIL = format('No matching gauge %s found.',
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	146 isrs_AsText(referenced_gauge::isrs)),
4132 ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints. Sascha Wilde <wilde@intevation.de> parents: 4127 diff changeset	147 ERRCODE = 23503,
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	148 SCHEMA = TG_TABLE_SCHEMA,
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	149 TABLE = TG_TABLE_NAME,
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	150 COLUMN = TG_ARGV[0],
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	151 CONSTRAINT = TG_NAME;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	152 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	153 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	154 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	155 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	156
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	157 -- The same for objects with a timestamp instead of a validity range.
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	158 CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	159 $$
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	160 DECLARE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	161 -- FIXME: I'm using text for the isrs code and cast it on demand.
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	162 -- If someone is able to get it to work with isrs or isrs_base as
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	163 -- type, feel free to show me how its done... ;-) [sw]
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	164 referenced_gauge text;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	165 new_tstz timestamptz;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	166 BEGIN
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	167 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	168 INTO referenced_gauge
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	169 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	170 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	171 INTO new_tstz
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	172 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	173 IF EXISTS ( SELECT * FROM waterway.gauges
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	174 WHERE location = referenced_gauge::isrs
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	175 AND validity @> new_tstz )
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	176 THEN
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	177 RETURN NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	178 ELSE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	179 RAISE EXCEPTION
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	180 'new row for relation "%" violates constraint trigger "%"',
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	181 TG_TABLE_NAME, TG_NAME
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	182 USING
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	183 DETAIL = format('No matching gauge %s found.',
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	184 isrs_AsText(referenced_gauge::isrs)),
4132 ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints. Sascha Wilde <wilde@intevation.de> parents: 4127 diff changeset	185 ERRCODE = 23503,
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	186 SCHEMA = TG_TABLE_SCHEMA,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	187 TABLE = TG_TABLE_NAME,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	188 COLUMN = TG_ARGV[0],
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	189 CONSTRAINT = TG_NAME;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	190 END IF;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	191 END;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	192 $$
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	193 LANGUAGE plpgsql;
3666 db87f34805fb Align bottleneck validity at gauges Tom Gottfried <tom@intevation.de> parents: 3665 diff changeset	194
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	195 -- Trigger function to be used as a constraint trigger to enforce
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	196 -- existance of a referenced bottleneck with validity at a given time.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	197 -- The columns with the referenced bottleneck id and the timestamp are
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	198 -- given as arguments to the trigger function.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	199 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	200 $$
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	201 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	202 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	203 new_tstz timestamptz;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	204 BEGIN
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	205 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	206 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	207 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	208 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	209 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	210 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	211 IF EXISTS ( SELECT * FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	212 WHERE bottleneck_id = referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	213 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	214 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	215 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	216 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	217 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	218 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	219 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	220 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	221 DETAIL = format('No matching bottleneck %s for %s found.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	222 referenced_bottleneck_id, new_tstz),
4132 ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints. Sascha Wilde <wilde@intevation.de> parents: 4127 diff changeset	223 ERRCODE = 23503,
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	224 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	225 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	226 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	227 CONSTRAINT = TG_NAME;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	228 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	229 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	230 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	231 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	232
3746 433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	233 -- Constraint trigger: sounding Results must intersect with the area
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	234 -- of the bottleneck they belong to. The "xx" at the beginning of the
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	235 -- name is to ensure, it is fired last after other triggers.
3746 433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	236 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	237 LANGUAGE plpgsql
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	238 AS $$
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	239 BEGIN
4882 566e9ee70d45 Backout changesets 5c12b7cdc58c and 8a4c98b80fbd Tom Gottfried <tom@intevation.de> parents: 4873 diff changeset	240 IF NOT st_intersects((SELECT area
3746 433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	241 FROM waterway.bottlenecks
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	242 WHERE bottleneck_id = NEW.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	243 AND validity @> NEW.date_info::timestamptz),
3746 433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	244 NEW.area)
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	245 THEN
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	246 RAISE EXCEPTION
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	247 'new row for relation "%" violates constraint trigger "%"',
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	248 TG_TABLE_NAME, TG_NAME
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	249 USING
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	250 DETAIL = 'Failing row area has no intersection with bottleneck.',
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	251 ERRCODE = 23514,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	252 SCHEMA = TG_TABLE_SCHEMA,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	253 TABLE = TG_TABLE_NAME,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	254 CONSTRAINT = TG_NAME;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	255 END IF;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	256 RETURN NEW;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	257 END;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	258 $$;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	259
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	260
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	261 --
3978 7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	262 -- GEMA meta data
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	263 --
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	264 CREATE TABLE gemma_schema_version (
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	265 version int PRIMARY KEY,
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	266 update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	267 );
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	268
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	269 CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int
3996 a2921151b193 Schema: Use SQL instead of PLPGSQL for get_schema_version(). Sascha Wilde <wilde@intevation.de> parents: 3978 diff changeset	270 LANGUAGE sql
3978 7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	271 AS $$
4173 d3fb2f37380b Schema qualify tables in function body Tom Gottfried <tom@intevation.de> parents: 4132 diff changeset	272 SELECT max(version) FROM public.gemma_schema_version;
3978 7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	273 $$;
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	274
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	275
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	276 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	277 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	278 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	279
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	280 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	281 -- Look-up tables with data that are static in a running system
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	282 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	283 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	284 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	285 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	286
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	288 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	289 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	290 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	291 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	292 INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	293
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	294 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	295 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	296 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	297 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	298 );
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	299 INSERT INTO dirimps VALUES (1), (2), (3), (4), (5);
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	300
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	301 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	302 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	303 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	304 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	305 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	306 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	307 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	308 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	309 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	310 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	311
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	312 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	313 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	314 -- TODO: Do we need name and/or definition from IENC feature catalogue?
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	315 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	316 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	317 INSERT INTO catdis VALUES (1), (2), (3), (4);
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	318
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	319 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	320 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	321 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	322 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	323 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	324 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	325 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	326 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	327 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	328
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	329 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	330 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	331 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	332 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	333 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	334 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	335 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	336 ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	337
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	338 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	339 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	340 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	341 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	342 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	343
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	344 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	345 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	346 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	347
4628 28999c7c0c18 Fill single and multi into survey type table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4623 diff changeset	348 INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi');
28999c7c0c18 Fill single and multi into survey type table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4623 diff changeset	349
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	350 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	351 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	352 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	353
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	354 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	355 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	356 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	357
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	358 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	359 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	360 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	361
3189 6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	362 CREATE TYPE template_types AS ENUM (
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	363 'map',
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	364 'diagram',
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	365 'report'
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	366 );
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	367
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	368
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	369 -- Namespace not to be accessed directly by any user
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	370 CREATE SCHEMA internal
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	371 -- Profile data are only accessible via the view users.list_users.
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	372 CREATE TABLE user_profiles (
4723 baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	373 username varchar PRIMARY KEY
baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	374 CHECK(octet_length(username) <= 63)
baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	375 CHECK(to_regrole(quote_ident(username)) IS NOT NULL),
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	376 -- keep username length compatible with role identifier
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	377 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	378 map_extent box2d NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	379 email_address varchar NOT NULL
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	380 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	381 ;
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	382
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	383
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	384 -- Namespace to be accessed by sys_admin only
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	385 CREATE SCHEMA sys_admin
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	386 CREATE TABLE system_config (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	387 config_key varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	388 config_val varchar
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	389 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	390
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	391 CREATE TABLE password_reset_requests (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	392 hash varchar(32) PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	393 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	394 username varchar NOT NULL UNIQUE
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	395 REFERENCES internal.user_profiles(username)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	396 ON DELETE CASCADE ON UPDATE CASCADE
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	397 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	398
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	399 -- Tables with geo data to be published with GeoServer.
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	400 CREATE TABLE external_services (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	401 local_name varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	402 remote_url varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	403 is_wfs boolean NOT NULL DEFAULT TRUE
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	404 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	405
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	406 CREATE TABLE published_services (
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	407 schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	408 name varchar,
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	409 PRIMARY KEY (schema, name),
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	410 -- SQL statement used for an SQL view in GeoServer:
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	411 view_def text CHECK (is_valid_from_item(view_def)),
4673 443867b548b5 Fix identifiers in layers generated from SQL views Tom Gottfried <tom@intevation.de> parents: 4648 diff changeset	412 -- Column in output of SQL statement to be used as primary key:
443867b548b5 Fix identifiers in layers generated from SQL views Tom Gottfried <tom@intevation.de> parents: 4648 diff changeset	413 key_column varchar,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	414 -- SRID to be used with SQL view:
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	415 srid int REFERENCES spatial_ref_sys,
4919 399458e60a9b Geo styles as ZIP: Forgot to change the schema itself. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4913 diff changeset	416 -- SLD style document or ZIP blob:
399458e60a9b Geo styles as ZIP: Forgot to change the schema itself. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4913 diff changeset	417 style bytea,
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	418 as_wms boolean NOT NULL DEFAULT TRUE,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	419 as_wfs boolean NOT NULL DEFAULT TRUE,
4995 4a816ecf70de Added support for WMS-Time in configuring the GeoServer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4971 diff changeset	420 wmst_attribute varchar DEFAULT NULL,
4a816ecf70de Added support for WMS-Time in configuring the GeoServer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4971 diff changeset	421 wmst_end_attribute varchar DEFAULT NULL,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	422 -- Either give a valid relation or a SQL statement:
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	423 CHECK (to_regclass(schema \|\| '.' \|\| name) IS NOT NULL
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	424 OR view_def IS NOT NULL)
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	425 )
4930 8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	426
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	427 CREATE TABLE layer_groups (
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	428 name varchar PRIMARY KEY
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	429 )
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	430
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	431 CREATE TABLE grouped_layers (
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	432 group_name varchar REFERENCES layer_groups ON DELETE CASCADE,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	433 schema varchar,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	434 name varchar,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	435 ord int NOT NULL DEFAULT 0,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	436 PRIMARY KEY (group_name, schema, name),
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	437 UNIQUE (group_name, schema, name, ord),
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	438 FOREIGN KEY(schema, name) REFERENCES published_services
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	439 )
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	440 ;
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	441
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	442
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	443 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	444 CREATE SCHEMA users
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	445 CREATE TABLE stretches (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	446 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718 92640ae5be07 Backed out changeset 9279fdb7a422 Tom Gottfried <tom@intevation.de> parents: 4717 diff changeset	447 name varchar NOT NULL,
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	448 stretch isrsrange NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	449 area geography(MULTIPOLYGON, 4326) NOT NULL
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	450 CHECK(ST_IsValid(CAST(area AS geometry))),
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	451 objnam varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	452 nobjnam varchar,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	453 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	454 source_organization varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	455 staging_done boolean NOT NULL DEFAULT false,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	456 UNIQUE(name, staging_done)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	457 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	458 CREATE TRIGGER stretches_date_info
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	459 BEFORE UPDATE ON stretches
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	460 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	461
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	462 CREATE TABLE stretch_countries (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	463 stretch_id int NOT NULL REFERENCES stretches(id)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	464 ON DELETE CASCADE,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	465 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	466 PRIMARY KEY(stretch_id, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	467 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	468
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	469 CREATE TABLE templates (
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	470 template_name varchar NOT NULL,
3189 6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3176 diff changeset	471 template_type template_types NOT NULL DEFAULT 'map'::template_types,
2267 37ae1bee3e4a Ajjusted RLS for user templates. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2230 diff changeset	472 country char(2) REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	473 template_data bytea NOT NULL,
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	474 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
3217 4c254651d80b Added template types "map", "diagram", "report". Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3189 diff changeset	475 UNIQUE (template_name, template_type, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	476 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	477 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	478 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	479
4755 dfd990a4ac64 Hide sys_admin accounts to waterway admins Tom Gottfried <tom@intevation.de> parents: 4749 diff changeset	480 CREATE VIEW list_users WITH (security_barrier) AS
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	481 SELECT
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	482 r.rolname,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	483 p.username,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	484 CAST('' AS varchar) AS pw,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	485 p.country,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	486 p.map_extent,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	487 p.email_address
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	488 FROM internal.user_profiles p
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	489 JOIN pg_roles u ON p.username = u.rolname
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	490 JOIN pg_auth_members a ON u.oid = a.member
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	491 JOIN pg_roles r ON a.roleid = r.oid
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	492 WHERE p.username = current_user
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	493 OR pg_has_role('waterway_admin', 'MEMBER')
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	494 AND p.country = (
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	495 SELECT country FROM internal.user_profiles
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	496 WHERE username = current_user)
4755 dfd990a4ac64 Hide sys_admin accounts to waterway admins Tom Gottfried <tom@intevation.de> parents: 4749 diff changeset	497 AND r.rolname <> 'sys_admin'
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	498 OR pg_has_role('sys_admin', 'MEMBER')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	499 ;
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	500
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	501
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	502 --
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	503 -- Functions to be used in DEFAULT expresions
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	504 --
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	505
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	506 -- Return current_user's country code
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	507 CREATE FUNCTION users.user_country(user_name name DEFAULT current_user)
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	508 RETURNS internal.user_profiles.country%TYPE
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	509 AS $$
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	510 SELECT country FROM users.list_users
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	511 WHERE username = user_name
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	512 $$
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	513 LANGUAGE SQL
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	514 STABLE PARALLEL SAFE;
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	515
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	516
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	517 -- Namespace for waterway data that can change in a running system
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	518 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	519
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	520 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	521 -- See https://roundup-intern.intevation.de/gemma/issue5
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	522 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	523 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	524 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	525 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	526 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	527 -- -- Is it a good idea to build B-tree indexes on relatively large
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	528 -- -- composites of string values or should we use inter PKs?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	529 -- -- => In case the index is used and cache space becomes a limiting
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	530 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	531 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	532 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	533 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	534
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	535 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	536 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	537 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	538 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	539 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	540 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	541 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	542 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	543 AFTER INSERT OR UPDATE OF area ON waterway_area
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	544 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	545
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	546
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	547 CREATE TABLE gauges (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	548 location isrs CHECK(
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	549 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	550 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	551 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	552 geom geography(POINT, 4326) NOT NULL,
1826 d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	553 applicability_from_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	554 applicability_to_km int8,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3647 diff changeset	555 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	556 zero_point double precision NOT NULL,
2369 89a3096e1988 Schema: Dropped wrong constraints in gauges.geodref. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2367 diff changeset	557 geodref varchar,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	558 date_info timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	559 source_organization varchar,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	560 lastupdate timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	561 -- entry removed from external data source (RIS-Index)/historicised:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	562 erased boolean NOT NULL DEFAULT false,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	563 PRIMARY KEY (location, validity),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	564 EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3389 diff changeset	565 DEFERRABLE INITIALLY DEFERRED
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	566 )
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	567 -- Allow only one non-erased entry per location
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	568 CREATE UNIQUE INDEX gauges_erased_unique_constraint
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	569 ON gauges (location)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	570 WHERE NOT erased
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	571
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	572 CREATE TABLE gauges_reference_water_levels (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	573 location isrs NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	574 validity tstzrange NOT NULL,
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3389 diff changeset	575 FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE,
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	576 -- Omit foreign key constraint to be able to store not NtS-compliant
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	577 -- names, too:
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	578 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	579 PRIMARY KEY (location, validity, depth_reference),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	580 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	581 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	582
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	583 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	584 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	585 location isrs NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	586 measure_date timestamp with time zone NOT NULL,
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	587 country_code char(2) NOT NULL REFERENCES countries,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	588 sender varchar NOT NULL, -- "from" element from NtS response
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	589 language_code varchar NOT NULL REFERENCES language_codes,
3389 45a629a3a8b8 Fix constraints on relationship between gauges and measurements/predictions Tom Gottfried <tom@intevation.de> parents: 3387 diff changeset	590 date_issue timestamp with time zone NOT NULL,
1775 fcb0106ec510 Gauge measurement import: Added reference_code column. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1759 diff changeset	591 reference_code varchar(4) NOT NULL REFERENCES depth_references,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	592 water_level double precision NOT NULL,
3387 d9eda49a52f4 Remove obsolete DEFAULT values Tom Gottfried <tom@intevation.de> parents: 3307 diff changeset	593 date_info timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	594 source_organization varchar NOT NULL, -- "originator" from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	595 staging_done boolean NOT NULL DEFAULT false,
4016 9ab7e1056360 Optimize index setup a bit Tom Gottfried <tom@intevation.de> parents: 4006 diff changeset	596 UNIQUE (measure_date, location, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	597 )
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	598 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
4068 76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	599 AFTER INSERT OR UPDATE OF location ON gauge_measurements
76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	600 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	601
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	602 CREATE TABLE gauge_predictions (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	603 location isrs NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	604 measure_date timestamp with time zone NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	605 country_code char(2) NOT NULL REFERENCES countries,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	606 sender varchar NOT NULL, -- "from" element from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	607 language_code varchar NOT NULL REFERENCES language_codes,
3389 45a629a3a8b8 Fix constraints on relationship between gauges and measurements/predictions Tom Gottfried <tom@intevation.de> parents: 3387 diff changeset	608 date_issue timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	609 reference_code varchar(4) NOT NULL REFERENCES depth_references,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	610 water_level double precision NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	611 conf_interval numrange
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	612 CHECK (conf_interval @> CAST(water_level AS numeric)),
3387 d9eda49a52f4 Remove obsolete DEFAULT values Tom Gottfried <tom@intevation.de> parents: 3307 diff changeset	613 date_info timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	614 source_organization varchar NOT NULL, -- "originator" from NtS response
4016 9ab7e1056360 Optimize index setup a bit Tom Gottfried <tom@intevation.de> parents: 4006 diff changeset	615 PRIMARY KEY (measure_date, location, date_issue)
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	616 )
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	617 CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
4068 76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	618 AFTER INSERT OR UPDATE OF location ON gauge_predictions
76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	619 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	620
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	621 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	622 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5009 e8b2dc771f9e Store axis as MultiLinestring Tom Gottfried <tom@intevation.de> parents: 5005 diff changeset	623 wtwaxs geography(MULTILINESTRING, 4326) NOT NULL
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	624 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	625 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	626 objnam varchar NOT NULL,
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	627 nobjnam varchar,
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	628 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	629 CHECK (NOT isempty(validity)),
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	630 -- Last time an import job found this entry in a data source:
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	631 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	632 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	633 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	634 AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	635 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity')
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	636 CREATE INDEX waterway_axis_validity
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	637 ON waterway_axis USING GiST (validity)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	638
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	639 -- This table allows linkage between 1D ISRS location codes and 2D space
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	640 -- e.g. for cutting bottleneck area out of waterway area based on virtual
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	641 -- distance marks along waterway axis (see SUC7).
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	642 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	643 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	644 geom geography(POINT, 4326) NOT NULL,
3778 1b11ff97c1d1 Add wwname to import of virtual distance marks. Sascha Wilde <wilde@intevation.de> parents: 3747 diff changeset	645 related_enc varchar(12) NOT NULL,
1b11ff97c1d1 Add wwname to import of virtual distance marks. Sascha Wilde <wilde@intevation.de> parents: 3747 diff changeset	646 wwname varchar
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	647 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	648
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	649 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	650 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	651 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	652 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	653 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	654 -- include location in primary key, because we have no fairway code:
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	655 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	656 position_code char(2) REFERENCES position_codes,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	657 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	658 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	659
3171 c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	660 -- Like stretches without the countries
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	661 CREATE TABLE sections (
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	662 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718 92640ae5be07 Backed out changeset 9279fdb7a422 Tom Gottfried <tom@intevation.de> parents: 4717 diff changeset	663 name varchar NOT NULL,
3307 b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema Markus Kottlaender <markus@intevation.de> parents: 3302 diff changeset	664 section isrsrange NOT NULL,
3171 c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	665 area geography(MULTIPOLYGON, 4326) NOT NULL
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	666 CHECK(ST_IsValid(CAST(area AS geometry))),
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	667 objnam varchar NOT NULL,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	668 nobjnam varchar,
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	669 country char(2) NOT NULL REFERENCES countries
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	670 DEFAULT users.user_country(),
3171 c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	671 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	672 source_organization varchar NOT NULL,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	673 staging_done boolean NOT NULL DEFAULT false,
4749 fd9f171b87e4 Generally disallow the same section name for different countries Tom Gottfried <tom@intevation.de> parents: 4748 diff changeset	674 -- Disallow the same name for different countries
fd9f171b87e4 Generally disallow the same section name for different countries Tom Gottfried <tom@intevation.de> parents: 4748 diff changeset	675 EXCLUDE USING GiST (name WITH =, country WITH <>),
fd9f171b87e4 Generally disallow the same section name for different countries Tom Gottfried <tom@intevation.de> parents: 4748 diff changeset	676 -- Allow the same name one time in and outside staging area, each
3171 c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	677 UNIQUE(name, staging_done)
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	678 )
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	679 CREATE TRIGGER sections_date_info
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	680 BEFORE UPDATE ON sections
3171 c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	681 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3008 diff changeset	682
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	683 CREATE TABLE waterway_profiles (
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	684 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
2141 3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	685 location isrs NOT NULL REFERENCES distance_marks_virtual,
3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	686 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	687 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	688 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	689 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	690 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	691 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	692 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	693 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	694 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	695 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	696 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	697 source_organization varchar NOT NULL,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	698 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	699 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	700 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	701 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	702 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	703 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	704 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	705 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	706 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	707
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	708 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	709 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5030 737d7859dd86 Store fairway dimensions as MultiPolygon Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	710 area geography(MULTIPOLYGON, 4326) NOT NULL
5031 8c590ef35280 Improve feedback if geometry cannot be stored Tom Gottfried <tom@intevation.de> parents: 5030 diff changeset	711 CHECK(ST_IsValid(CAST(area AS geometry))
8c590ef35280 Improve feedback if geometry cannot be stored Tom Gottfried <tom@intevation.de> parents: 5030 diff changeset	712 AND NOT ST_IsEmpty(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	713 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	714 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	715 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	716 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	717 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	718 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	719 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	720 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	721 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	722 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	723 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	724
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	725 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	726 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	727 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	728 CREATE TABLE bottlenecks (
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	729 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3624 diff changeset	730 bottleneck_id varchar NOT NULL,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3647 diff changeset	731 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	732 gauge_location isrs NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	733 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	734 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	735 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	736 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	737 CHECK(ST_IsValid(CAST(area AS geometry))),
2765 41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2627 diff changeset	738 rb char(2) REFERENCES countries, -- from rb_lb in interface
41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2627 diff changeset	739 lb char(2) REFERENCES countries, -- from rb_lb in interface
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	740 responsible_country char(2) NOT NULL REFERENCES countries,
3423 6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	741 revisiting_time smallint,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	742 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	743 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	744 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	745 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	746 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	747 -- CHECK allowed combinations of surtyp and coverage or
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	748 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	749 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	750 -- XXX: Also an attribut of sounding result?
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3624 diff changeset	751 date_info timestamp with time zone NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	752 source_organization varchar NOT NULL,
4111 692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	753 staging_done boolean NOT NULL DEFAULT false,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	754 UNIQUE (bottleneck_id, validity, staging_done),
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	755 EXCLUDE USING GiST (bottleneck_id WITH =,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	756 validity WITH &&,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	757 CAST(staging_done AS int) WITH =)
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	758 DEFERRABLE INITIALLY DEFERRED
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	759 )
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	760 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	761 AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	762 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	763
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	764 CREATE TABLE bottlenecks_riverbed_materials (
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2867 diff changeset	765 bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2867 diff changeset	766 ON DELETE CASCADE,
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	767 riverbed varchar NOT NULL REFERENCES riverbed_materials,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	768 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	769 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	770 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	771
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	772 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	773 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3656 2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version Tom Gottfried <tom@intevation.de> parents: 3648 diff changeset	774 bottleneck_id varchar NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	775 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	776 UNIQUE (bottleneck_id, date_info),
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	777 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	778 CHECK(ST_IsValid(CAST(area AS geometry))),
611 effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	779 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	780 coverage varchar REFERENCES coverage_types,
3624 3012d0b3badc Allow same values for depth_reference in SR as in gauges Sascha Wilde <wilde@intevation.de> parents: 3535 diff changeset	781 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
1131 2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	782 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	783 octree_index bytea,
4648 66fcd898efd9 Started with conversion tool. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4628 diff changeset	784 mesh_checksum varchar,
66fcd898efd9 Started with conversion tool. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4628 diff changeset	785 mesh_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	786 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	787 )
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	788 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
4068 76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	789 AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	790 FOR EACH ROW
4068 76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	791 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	792 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
3746 433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	793 AFTER INSERT OR UPDATE ON sounding_results
3747 ad67e4286d65 Fixed typo in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 3746 diff changeset	794 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area()
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	795
4561 f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	796 CREATE TABLE sounding_results_iso_areas (
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	797 sounding_result_id int NOT NULL REFERENCES sounding_results
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	798 ON DELETE CASCADE,
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	799 height numeric NOT NULL,
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	800 areas geography(MULTIPOLYGON, 4326) NOT NULL,
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	801 -- TODO: generate valid simple features and add constraint:
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	802 -- CHECK(ST_IsSimple(CAST(areas AS geometry))),
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	803 PRIMARY KEY (sounding_result_id, height)
f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	804 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	805 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	806 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	807 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	808 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	809 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4461 7128741faeb9 Changed fa data to accept "position" as strings, not a 2char "position_code" Sascha Wilde <wilde@intevation.de> parents: 4449 diff changeset	810 position varchar,
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	811 bottleneck_id varchar NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	812 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	813 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	814 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	815 critical boolean,
4057 b79b60c0cc5a date_info has to be provided by the data source Tom Gottfried <tom@intevation.de> parents: 4033 diff changeset	816 date_info timestamp with time zone NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	817 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	818 )
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	819 -- FIXME: From the DRC it is unclear what the exact semantics of
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	820 -- surdat and Date_Info ar unclear. Currently we assume that
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	821 -- (fk_bn_fid,surdat) has to be unique, but that might be false.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	822 -- Anyway, I will date_info here to check for an matching
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	823 -- reference gauge at the bottleneck. The reason for this
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	824 -- decision is purely practical (and might be semantically
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	825 -- disputable: the bottleneck data in the demo system is not old
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	826 -- enough to cover rthe surdat times...
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	827 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	828 AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	829 FOR EACH ROW
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	830 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	831
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	832 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	833 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	834 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	835 PRIMARY KEY (fairway_availability_id, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	836 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	837 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	838 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	839 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	840 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	841 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	842 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	843
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	844 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	845 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	846 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	847 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	848 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	849 pdf_generation_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	850 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	851 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	852
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	853 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	854 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	855 measure_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	856 level_of_service smallint NOT NULL REFERENCES levels_of_service,
4859 082027fb2d58 Allow "Measured" and "Forecast" values to coexist for the same time and gauge. Sascha Wilde <wilde@intevation.de> parents: 4846 diff changeset	857 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service,
082027fb2d58 Allow "Measured" and "Forecast" values to coexist for the same time and gauge. Sascha Wilde <wilde@intevation.de> parents: 4846 diff changeset	858 measure_type),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	859 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	860 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	861 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	862 CHECK(COALESCE(available_depth_value, available_width_value,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	863 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	864 measure_type varchar NOT NULL REFERENCES measure_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	865 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	866 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	867 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	868 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	869 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	870 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	871 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	872 )
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	873
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	874 -- Attributes common to all fairway marks
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	875 CREATE TABLE fairway_marks (
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	876 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	877 CHECK (NOT isempty(validity)),
7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	878 -- Last time an import job found this entry in a data source:
7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	879 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp,
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	880 geom geography(POINT, 4326) NOT NULL,
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	881 -- Attributes according to IENC Feature Catalogue:
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	882 datsta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	883 datend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	884 persta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	885 perend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	886 objnam varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	887 nobjnm varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	888 inform varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	889 ninfom varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	890 scamin int,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	891 picrep varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	892 txtdsc varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	893 sordat varchar,
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	894 sorind varchar
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	895 )
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	896
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	897 -- Additional attributes for IENC features BCNLAT/bcnlat
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	898 CREATE TABLE fairway_marks_bcnlat (
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	899 colour varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	900 colpat varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	901 condtn int,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	902 bcnshp int,
4912 bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	903 catlam int
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	904 ) INHERITS (fairway_marks)
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	905
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	906 CREATE TABLE fairway_marks_bcnlat_hydro (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	907 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	908 ) INHERITS (fairway_marks_bcnlat)
4895 9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	909 -- Prevent identical entries using composite type comparison
9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	910 -- (i.e. considering two NULL values in a field equal):
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	911 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
4965 be713316b47a Fixup rev. 1b309a8e7673 Tom Gottfried <tom@intevation.de> parents: 4964 diff changeset	912 ON fairway_marks_bcnlat_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	913 ((CAST((validity, last_found, geom,
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	914 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	915 scamin, picrep, txtdsc, sordat, sorind,
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	916 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	917 ) AS fairway_marks_bcnlat_hydro)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	918 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	919 CREATE INDEX fairway_marks_bcnlat_hydro_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	920 ON fairway_marks_bcnlat_hydro USING GiST (validity)
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	921
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	922 CREATE TABLE fairway_marks_bcnlat_ienc (
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	923 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	924 ) INHERITS (fairway_marks_bcnlat)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	925 -- Prevent identical entries using composite type comparison
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	926 -- (i.e. considering two NULL values in a field equal):
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	927 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
4965 be713316b47a Fixup rev. 1b309a8e7673 Tom Gottfried <tom@intevation.de> parents: 4964 diff changeset	928 ON fairway_marks_bcnlat_ienc
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	929 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	930 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	931 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	932 colour, colpat, condtn, bcnshp, catlam, 0
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	933 ) AS fairway_marks_bcnlat_ienc)
4895 9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	934 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	935 CREATE INDEX fairway_marks_bcnlat_ienc_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	936 ON fairway_marks_bcnlat_ienc USING GiST (validity)
4912 bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	937
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	938 CREATE TABLE fairway_marks_bcnlat_dirimps (
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	939 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
4912 bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	940 dirimp smallint REFERENCES dirimps,
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	941 PRIMARY KEY (fm_bcnlat_id, dirimp)
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	942 )
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	943
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	944 -- Additional attributes for IENC feature BOYCAR
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	945 CREATE TABLE fairway_marks_boycar (
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	946 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	947 colour varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	948 colpat varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	949 conrad int,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	950 marsys int,
4925 b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	951 boyshp int,
b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	952 catcam int
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	953 ) INHERITS (fairway_marks)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	954 -- Prevent identical entries using composite type comparison
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	955 -- (i.e. considering two NULL values in a field equal):
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	956 CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	957 ON fairway_marks_boycar
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	958 ((CAST((validity, last_found, geom,
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	959 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	960 scamin, picrep, txtdsc, sordat, sorind,
4925 b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	961 0, colour, colpat, conrad, marsys, boyshp, catcam
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	962 ) AS fairway_marks_boycar)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	963 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	964 CREATE INDEX fairway_marks_boycar_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	965 ON fairway_marks_boycar USING GiST (validity)
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	966
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	967 -- Additional attributes for IENC feature BOYLAT
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	968 CREATE TABLE fairway_marks_boylat (
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	969 colour varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	970 colpat varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	971 conrad int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	972 marsys int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	973 boyshp int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	974 catlam int
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	975 ) INHERITS (fairway_marks)
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	976
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	977 CREATE TABLE fairway_marks_boylat_hydro (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	978 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	979 ) INHERITS (fairway_marks_boylat)
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	980 -- Prevent identical entries using composite type comparison
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	981 -- (i.e. considering two NULL values in a field equal):
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	982 CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	983 ON fairway_marks_boylat_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	984 ((CAST((validity, last_found, geom,
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	985 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	986 scamin, picrep, txtdsc, sordat, sorind,
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	987 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	988 ) AS fairway_marks_boylat_hydro)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	989 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	990 CREATE INDEX fairway_marks_boylat_hydro_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	991 ON fairway_marks_boylat_hydro USING GiST (validity)
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	992
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	993 CREATE TABLE fairway_marks_boylat_ienc (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	994 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	995 ) INHERITS (fairway_marks_boylat)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	996 -- Prevent identical entries using composite type comparison
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	997 -- (i.e. considering two NULL values in a field equal):
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	998 CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	999 ON fairway_marks_boylat_ienc
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1000 ((CAST((validity, last_found, geom,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1001 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1002 scamin, picrep, txtdsc, sordat, sorind,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1003 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1004 ) AS fairway_marks_boylat_ienc)
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	1005 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1006 CREATE INDEX fairway_marks_boylat_ienc_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1007 ON fairway_marks_boylat_ienc USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1008
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1009 -- Additional attributes for IENC feature BOYSAW
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1010 CREATE TABLE fairway_marks_boysaw (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1011 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1012 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1013 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1014 conrad int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1015 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1016 boyshp int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1017 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1018 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1019 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1020 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1021 ON fairway_marks_boysaw
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1022 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1023 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1024 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1025 0, colour, colpat, conrad, marsys, boyshp
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1026 ) AS fairway_marks_boysaw)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1027 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1028 CREATE INDEX fairway_marks_boysaw_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1029 ON fairway_marks_boysaw USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1030
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1031 -- Additional attributes for IENC feature BOYSPP
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1032 CREATE TABLE fairway_marks_boyspp (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1033 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1034 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1035 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1036 conrad int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1037 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1038 boyshp int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1039 catspm varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1040 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1041 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1042 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1043 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1044 ON fairway_marks_boyspp
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1045 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1046 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1047 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1048 0, colour, colpat, conrad, marsys, boyshp, catspm
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1049 ) AS fairway_marks_boyspp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1050 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1051 CREATE INDEX fairway_marks_boyspp_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1052 ON fairway_marks_boyspp USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1053
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1054 -- Additional attributes for IENC features DAYMAR/daymar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1055 CREATE TABLE fairway_marks_daymar (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1056 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1057 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1058 condtn int,
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1059 topshp int
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1060 ) INHERITS (fairway_marks)
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1061
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1062 CREATE TABLE fairway_marks_daymar_hydro (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1063 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1064 ) INHERITS (fairway_marks_daymar)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1065 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1066 -- (i.e. considering two NULL values in a field equal):
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1067 CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1068 ON fairway_marks_daymar_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1069 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1070 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1071 scamin, picrep, txtdsc, sordat, sorind,
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1072 colour, colpat, condtn, topshp, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1073 ) AS fairway_marks_daymar_hydro)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1074 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1075 CREATE INDEX fairway_marks_daymar_hydro_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1076 ON fairway_marks_daymar_hydro USING GiST (validity)
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1077
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1078 CREATE TABLE fairway_marks_daymar_ienc (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1079 orient double precision,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1080 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1081 ) INHERITS (fairway_marks_daymar)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1082 -- Prevent identical entries using composite type comparison
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1083 -- (i.e. considering two NULL values in a field equal):
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1084 CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1085 ON fairway_marks_daymar_ienc
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1086 ((CAST((validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1087 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1088 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1089 colour, colpat, condtn, topshp, orient, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1090 ) AS fairway_marks_daymar_ienc)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1091 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1092 CREATE INDEX fairway_marks_daymar_ienc_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1093 ON fairway_marks_daymar_ienc USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1094
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1095 CREATE TABLE fairway_marks_daymar_dirimps (
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1096 fm_daymar_id int REFERENCES fairway_marks_daymar_ienc,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1097 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1098 PRIMARY KEY (fm_daymar_id, dirimp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1099 )
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1100
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1101 -- Additional attributes for IENC feature LIGHTS
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1102 CREATE TABLE fairway_marks_lights (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1103 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1104 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1105 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1106 orient double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1107 catlit varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1108 exclit int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1109 litchr int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1110 litvis varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1111 mltylt int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1112 sectr1 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1113 sectr2 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1114 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1115 sigper double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1116 sigseq varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1117 status varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1118 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1119 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1120 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1121 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1122 ON fairway_marks_lights
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1123 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1124 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1125 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1126 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1127 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1128 ) AS fairway_marks_lights)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1129 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1130 CREATE INDEX fairway_marks_lights_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1131 ON fairway_marks_lights USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1132
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1133 -- Additional attributes for IENC feature RTPBCN
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1134 CREATE TABLE fairway_marks_rtpbcn (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1135 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1136 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1137 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1138 catrtb int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1139 radwal varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1140 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1141 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1142 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1143 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1144 ON fairway_marks_rtpbcn
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1145 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1146 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1147 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1148 0, condtn, siggrp, catrtb, radwal
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1149 ) AS fairway_marks_rtpbcn)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1150 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1151 CREATE INDEX fairway_marks_rtpbcn_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1152 ON fairway_marks_rtpbcn USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1153
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1154 -- Additional attributes for IENC feature TOPMAR
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1155 CREATE TABLE fairway_marks_topmar (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1156 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1157 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1158 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1159 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1160 topshp int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1161 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1162 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1163 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1164 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1165 ON fairway_marks_topmar
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1166 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1167 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1168 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1169 0, colour, colpat, condtn, topshp
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1170 ) AS fairway_marks_topmar)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1171 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1172 CREATE INDEX fairway_marks_topmar_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1173 ON fairway_marks_topmar USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1174
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1175 -- Additional attributes for IENC feature NOTMRK
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1176 CREATE TABLE fairway_marks_notmrk (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1177 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1178 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1179 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1180 orient double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1181 status varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1182 addmrk varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1183 catnmk int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1184 disipd double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1185 disipu double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1186 disbk1 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1187 disbk2 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1188 fnctnm int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1189 bnkwtw int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1190 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1191 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1192 -- (i.e. considering two NULL values in a field equal):
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1193 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1194 ON fairway_marks_notmrk
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1195 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1196 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1197 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1198 0, condtn, marsys, orient, status, addmrk, catnmk,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1199 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1200 ) AS fairway_marks_notmrk)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1201 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1202 CREATE INDEX fairway_marks_notmrk_validity
de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1203 ON fairway_marks_notmrk USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1204
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1205 CREATE TABLE fairway_marks_notmrk_dirimps (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1206 fm_notmrk_id int REFERENCES fairway_marks_notmrk,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1207 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1208 PRIMARY KEY (fm_notmrk_id, dirimp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1209 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1210 ;
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1211
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1212
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1213 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1214 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1215 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1216 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1217 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1218 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1219 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1220 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1221 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1222
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1223 CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1224
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1225 -- Namespace for import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1226 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1227
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1228 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1229 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1230 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1231 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1232 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1233 ON UPDATE CASCADE,
2042 d29ac997eb34 This breaks this branch!!!! Starting to remove the old persistent layer for configured imports. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1995 diff changeset	1234 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1235 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1236
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1237 CREATE TABLE import_configuration_attributes (
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1238 import_configuration_id int NOT NULL
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1239 REFERENCES import_configuration(id)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1240 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1241 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1242 k VARCHAR NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1243 v TEXT NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1244 UNIQUE (import_configuration_id, k)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1245 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1246
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1247 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1248 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1249 state import_state NOT NULL DEFAULT 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1250 kind varchar NOT NULL,
4098 cfa0a5775d70 Reapplied timezone patch to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4094 diff changeset	1251 enqueued timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
4748 47922c1a088d Added a 'changed' column to the import.imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4740 diff changeset	1252 changed timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
4098 cfa0a5775d70 Reapplied timezone patch to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4094 diff changeset	1253 due timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1254 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1255 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1256 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1257 trys_left int, -- if NULL and retry_wait NOT NULL, endless
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1258 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1259 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1260 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1261 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1262 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1263 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1264 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1265 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1266 send_email boolean NOT NULL DEFAULT false,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1267 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1268 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1269 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1270
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1271 CREATE INDEX enqueued_idx ON imports(enqueued, state)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1272
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1273 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1274 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1275 ON DELETE CASCADE,
4100 cc3d607b49cc Merged default into timezone branch. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4098 4099 diff changeset	1276 time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1277 kind log_type NOT NULL DEFAULT 'info',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1278 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1279 )
4187 65a5501dc13d Enable faster access to import logs Tom Gottfried <tom@intevation.de> parents: 4173 diff changeset	1280 CREATE INDEX import_logs_import_id ON import.import_logs (import_id)
2627 3a242e6aa56d Import log: Add filter for log entries with warnings only: GET /api/imports?warnings=true Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2589 diff changeset	1281
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1282 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1283 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1284 ON DELETE CASCADE,
4031 4bf1c8d91bac Import queue: Added a column to the tracking table to delete entries on stage done. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4016 diff changeset	1285 deletion bool NOT NULL DEFAULT false,
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1286 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1287 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1288 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1289 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	1290 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	1291
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1292 CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1293 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1294 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1295 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1296 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1297 FOR tmp IN
4126 52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1298 SELECT relation, array_agg(key) AS keys
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1299 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1300 WHERE import_id = imp_id AND NOT deletion
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1301 GROUP BY relation
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1302 LOOP
4126 52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1303 EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation)
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1304 USING tmp.keys;
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1305 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1306 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1307 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1308 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1309
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1310 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1311
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1312 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1313 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1314 minuend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1315 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1316 subtrahend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1317 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1318 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1319 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1320
4573 26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1321 CREATE TABLE sounding_differences_iso_areas (
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1322 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1323 ON DELETE CASCADE,
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1324 height numeric NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1325 areas geography(MULTIPOLYGON, 4326) NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1326 PRIMARY KEY (sounding_differences_id, height)
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1327 )
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1328 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1329
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	1330 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 5031:8c590ef35280