gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 5141:722b7c305319

Prevent equal axis geometries with intersecting validity Such things usually cannot happen via importing, but it is at least consistent with constraints on other tables and the functionality is needed for fairway dimensions, since they are first stored in the staging area and equal geometries with intersecting validity could be generated by a concurrent import. Set the CONSTRAINT TRIGGER as INITIALLY DEFERRED because new entries are inserted before the validity of existing entries is adapted in the same transaction.

author	Tom Gottfried <tom@intevation.de>
date	Tue, 31 Mar 2020 12:50:30 +0200
parents	882b3d2308c4
children	f11b9b50fcc9

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
5141 722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	74 -- trigger function. If additional column names are given as trigger arguments,
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	75 -- the group of given columns is tested for equality, if no operators are
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	76 -- given. Optionally, a column can be compared with a different operator
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	77 -- given after the keyword "WITH" in the trigger argument.
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	78 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	79 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	80 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	81 new_geom geometry;
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	82 tg_arg text;
5141 722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	83 col varchar;
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	84 opr varchar;
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	85 filters text;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	86 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	87 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	88 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	89 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	90 USING NEW;
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	91 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	92 -- Test each additional argument for equality
5141 722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	93 -- or with operator given after keyword "WITH"
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	94 tg_arg = lower(tg_arg);
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	95 col = split_part(tg_arg, ' with ', 1);
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	96 opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '=');
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	97 filters = format('%s AND %I %s $2.%2$I', filters, col, opr);
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	98 END LOOP;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	99 EXECUTE format(
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	100 'SELECT EXISTS(SELECT 1 FROM %I.%I '
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	101 '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	102 '%s)',
f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	103 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	104 INTO has_equal
5005 f697a398eff4 Amend trigger function Tom Gottfried <tom@intevation.de> parents: 4995 diff changeset	105 USING new_geom, NEW;
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	106 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	107 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	108 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	109 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	110 USING
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	111 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	112 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	113 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	114 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	115 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	116 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	117 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	118 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	119 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	120 END;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	121 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	122 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	123
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	124 -- 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	125 -- 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	126 -- 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	127 -- 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	128 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	129 $$
3666 db87f34805fb Align bottleneck validity at gauges Tom Gottfried <tom@intevation.de> parents: 3665 diff changeset	130 DECLARE
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	131 -- 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	132 -- 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	133 -- 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	134 referenced_gauge text;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	135 new_validity tstzrange;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	136 BEGIN
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	137 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	138 INTO referenced_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	139 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	140 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	141 INTO new_validity
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	142 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	143 IF EXISTS ( SELECT * FROM waterway.gauges
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	144 WHERE location = referenced_gauge::isrs
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	145 AND validity && new_validity )
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	146 THEN
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	147 RETURN NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	148 ELSE
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	149 RAISE EXCEPTION
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	150 '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	151 TG_TABLE_NAME, TG_NAME
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	152 USING
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	153 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	154 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	155 ERRCODE = 23503,
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	156 SCHEMA = TG_TABLE_SCHEMA,
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	157 TABLE = TG_TABLE_NAME,
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	158 COLUMN = TG_ARGV[0],
3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	159 CONSTRAINT = TG_NAME;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	160 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	161 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	162 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	163 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	164
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	165 -- 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	166 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	167 $$
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	168 DECLARE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	169 -- 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	170 -- 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	171 -- 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	172 referenced_gauge text;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	173 new_tstz timestamptz;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	174 BEGIN
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	175 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	176 INTO referenced_gauge
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	177 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	178 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	179 INTO new_tstz
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	180 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	181 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	182 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	183 AND validity @> new_tstz )
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	184 THEN
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	185 RETURN NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	186 ELSE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	187 RAISE EXCEPTION
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	188 '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	189 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	190 USING
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	191 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	192 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	193 ERRCODE = 23503,
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	194 SCHEMA = TG_TABLE_SCHEMA,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	195 TABLE = TG_TABLE_NAME,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	196 COLUMN = TG_ARGV[0],
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	197 CONSTRAINT = TG_NAME;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	198 END IF;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	199 END;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	200 $$
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	201 LANGUAGE plpgsql;
3666 db87f34805fb Align bottleneck validity at gauges Tom Gottfried <tom@intevation.de> parents: 3665 diff changeset	202
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	203 -- 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	204 -- 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	205 -- 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	206 -- 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	207 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	208 $$
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	209 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	210 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	211 new_tstz timestamptz;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	212 BEGIN
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	213 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	214 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	215 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	216 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	217 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	218 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	219 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	220 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	221 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	222 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	223 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	224 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	225 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	226 '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	227 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	228 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	229 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	230 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	231 ERRCODE = 23503,
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	232 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	233 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	234 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	235 CONSTRAINT = TG_NAME;
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	236 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	237 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	238 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	239 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	240
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 -- 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	242 -- 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	243 -- 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	244 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	245 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	246 AS $$
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	247 BEGIN
4882 566e9ee70d45 Backout changesets 5c12b7cdc58c and 8a4c98b80fbd Tom Gottfried <tom@intevation.de> parents: 4873 diff changeset	248 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	249 FROM waterway.bottlenecks
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	250 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	251 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	252 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	253 THEN
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	254 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	255 '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	256 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	257 USING
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	258 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	259 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	260 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	261 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	262 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	263 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	264 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	265 END;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	266 $$;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	267
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area. Sascha Wilde <wilde@intevation.de> parents: 3666 diff changeset	268
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	269 --
3978 7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	270 -- GEMA meta data
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	271 --
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	272 CREATE TABLE gemma_schema_version (
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	273 version int PRIMARY KEY,
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	274 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	275 );
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	276
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	277 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	278 LANGUAGE sql
3978 7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	279 AS $$
4173 d3fb2f37380b Schema qualify tables in function body Tom Gottfried <tom@intevation.de> parents: 4132 diff changeset	280 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	281 $$;
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	282
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	283
7b7c324b318f Added database schema versioning and db update script. Sascha Wilde <wilde@intevation.de> parents: 3956 diff changeset	284 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	285 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	286 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	288 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	289 -- 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	290 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	291 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	292 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	293 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	294
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	296 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	297 -- 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	298 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	299 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	300 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	301
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	302 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	303 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	304 -- 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	305 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	306 );
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	307 INSERT INTO dirimps VALUES (1), (2), (3), (4), (5);
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	308
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	309 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	310 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	311 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	312 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	313 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	314 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	315 -- 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	316 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	317 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	318 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	319
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	320 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	321 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	322 -- 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	323 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	324 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	325 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	326
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	327 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	328 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	329 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	330 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	331 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	332 -- 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	333 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	334 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	335 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	336
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	337 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	338 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	339 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	340 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	341 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	342 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	343 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	344 ) 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	345
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	346 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	347 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	348 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	349 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	350 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	351
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	352 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	353 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	354 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	355
4628 28999c7c0c18 Fill single and multi into survey type table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4623 diff changeset	356 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	357
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	358 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	359 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	360 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	361
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	362 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	363 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	364 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	365
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	366 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	367 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	368 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	369
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	370 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	371 '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	372 '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	373 '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	374 );
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	375
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	376
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	377 -- 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	378 CREATE SCHEMA internal
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	379 -- 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	380 CREATE TABLE user_profiles (
4723 baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	381 username varchar PRIMARY KEY
baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	382 CHECK(octet_length(username) <= 63)
baabc2b2f094 Avoid creating user profiles without matching role Tom Gottfried <tom@intevation.de> parents: 4722 diff changeset	383 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	384 -- keep username length compatible with role identifier
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	385 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	386 map_extent box2d NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	387 email_address varchar NOT NULL
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	388 )
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
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	392 -- Namespace to be accessed by sys_admin only
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	393 CREATE SCHEMA sys_admin
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	394 CREATE TABLE system_config (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	395 config_key varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	396 config_val varchar
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 CREATE TABLE password_reset_requests (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	400 hash varchar(32) PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	401 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	402 username varchar NOT NULL UNIQUE
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	403 REFERENCES internal.user_profiles(username)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	404 ON DELETE CASCADE ON UPDATE CASCADE
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
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	407 -- 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	408 CREATE TABLE external_services (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	409 local_name varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	410 remote_url varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	411 is_wfs boolean NOT NULL DEFAULT TRUE
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	412 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	413
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	414 CREATE TABLE published_services (
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	415 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	416 name varchar,
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	417 PRIMARY KEY (schema, name),
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	418 -- 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	419 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	420 -- 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	421 key_column varchar,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	422 -- SRID to be used with SQL view:
0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	423 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	424 -- 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	425 style bytea,
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	426 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	427 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	428 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	429 wmst_end_attribute varchar DEFAULT NULL,
4618 0f2c3cb139cc Merge default into geoserver_sql_views Tom Gottfried <tom@intevation.de> parents: 4614 4449 diff changeset	430 -- 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	431 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	432 OR view_def IS NOT NULL)
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	433 )
4930 8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	434
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	435 CREATE TABLE layer_groups (
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	436 name varchar PRIMARY KEY
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	437 )
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	438
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	439 CREATE TABLE grouped_layers (
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	440 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	441 schema varchar,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	442 name varchar,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	443 ord int NOT NULL DEFAULT 0,
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	444 PRIMARY KEY (group_name, schema, name),
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	445 UNIQUE (group_name, schema, name, ord),
8b83b18a1d49 Added database models for group layers. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4925 diff changeset	446 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	447 )
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	448 ;
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	449
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	450
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	451 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	452 CREATE SCHEMA users
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	453 CREATE TABLE stretches (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	454 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718 92640ae5be07 Backed out changeset 9279fdb7a422 Tom Gottfried <tom@intevation.de> parents: 4717 diff changeset	455 name varchar NOT NULL,
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	456 stretch isrsrange NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	457 area geography(MULTIPOLYGON, 4326) NOT NULL
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	458 CHECK(ST_IsValid(CAST(area AS geometry))),
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	459 objnam varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	460 nobjnam varchar,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	461 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	462 source_organization varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	463 staging_done boolean NOT NULL DEFAULT false,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	464 UNIQUE(name, staging_done)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	465 )
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	466 CREATE TRIGGER stretches_date_info
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	467 BEFORE UPDATE ON stretches
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	468 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	469
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	470 CREATE TABLE stretch_countries (
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	471 stretch_id int NOT NULL REFERENCES stretches(id)
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	472 ON DELETE CASCADE,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	473 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	474 PRIMARY KEY(stretch_id, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	475 )
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 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	478 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	479 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	480 country char(2) REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	481 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	482 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	483 UNIQUE (template_name, template_type, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	484 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	485 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	486 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	487
4755 dfd990a4ac64 Hide sys_admin accounts to waterway admins Tom Gottfried <tom@intevation.de> parents: 4749 diff changeset	488 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	489 SELECT
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	490 r.rolname,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	491 p.username,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	492 CAST('' AS varchar) AS pw,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	493 p.country,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	494 p.map_extent,
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	495 p.email_address
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	496 FROM internal.user_profiles p
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	497 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	498 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	499 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	500 WHERE p.username = current_user
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	501 OR pg_has_role('waterway_admin', 'MEMBER')
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	502 AND p.country = (
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	503 SELECT country FROM internal.user_profiles
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	504 WHERE username = current_user)
4755 dfd990a4ac64 Hide sys_admin accounts to waterway admins Tom Gottfried <tom@intevation.de> parents: 4749 diff changeset	505 AND r.rolname <> 'sys_admin'
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	506 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	507 ;
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	508
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	509
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	510 --
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	511 -- Functions to be used in DEFAULT expresions
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
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	514 -- Return current_user's country code
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	515 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	516 RETURNS internal.user_profiles.country%TYPE
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	517 AS $$
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	518 SELECT country FROM users.list_users
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	519 WHERE username = user_name
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	520 $$
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	521 LANGUAGE SQL
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	522 STABLE PARALLEL SAFE;
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	523
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	524
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	525 -- 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	526 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	527
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	528 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	529 -- 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	530 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	531 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	532 -- -- 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	533 -- -- 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	534 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	535 -- -- 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	536 -- -- 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	537 -- -- => 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	538 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	539 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	540 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	541 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	542
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	543 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	544 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	545 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	546 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	547 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	548 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	549 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	550 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	551 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	552 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	553
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	554
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	555 CREATE TABLE gauges (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	556 location isrs CHECK(
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	557 (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	558 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	559 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	560 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	561 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	562 applicability_to_km int8,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3647 diff changeset	563 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	564 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	565 geodref varchar,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	566 date_info timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	567 source_organization varchar,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	568 lastupdate timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	569 -- entry removed from external data source (RIS-Index)/historicised:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	570 erased boolean NOT NULL DEFAULT false,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	571 PRIMARY KEY (location, validity),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	572 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	573 DEFERRABLE INITIALLY DEFERRED
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	574 )
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	575 -- Allow only one non-erased entry per location
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	576 CREATE UNIQUE INDEX gauges_erased_unique_constraint
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	577 ON gauges (location)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	578 WHERE NOT erased
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	579
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	580 CREATE TABLE gauges_reference_water_levels (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	581 location isrs NOT NULL,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	582 validity tstzrange NOT NULL,
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3389 diff changeset	583 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	584 -- 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	585 -- names, too:
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	586 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	587 PRIMARY KEY (location, validity, depth_reference),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	588 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	589 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	590
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	591 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	592 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	593 location isrs NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	594 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	595 country_code char(2) NOT NULL REFERENCES countries,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	596 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	597 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	598 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	599 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	600 water_level double precision NOT NULL,
3387 d9eda49a52f4 Remove obsolete DEFAULT values Tom Gottfried <tom@intevation.de> parents: 3307 diff changeset	601 date_info timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	602 source_organization varchar NOT NULL, -- "originator" from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	603 staging_done boolean NOT NULL DEFAULT false,
4016 9ab7e1056360 Optimize index setup a bit Tom Gottfried <tom@intevation.de> parents: 4006 diff changeset	604 UNIQUE (measure_date, location, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	605 )
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	606 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	607 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	608 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	609
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	610 CREATE TABLE gauge_predictions (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	611 location isrs NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	612 measure_date timestamp with time zone NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	613 country_code char(2) NOT NULL REFERENCES countries,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	614 sender varchar NOT NULL, -- "from" element from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	615 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	616 date_issue timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	617 reference_code varchar(4) NOT NULL REFERENCES depth_references,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	618 water_level double precision NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	619 conf_interval numrange
232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	620 CHECK (conf_interval @> CAST(water_level AS numeric)),
3387 d9eda49a52f4 Remove obsolete DEFAULT values Tom Gottfried <tom@intevation.de> parents: 3307 diff changeset	621 date_info timestamp with time zone NOT NULL,
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	622 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	623 PRIMARY KEY (measure_date, location, date_issue)
3277 232fc90e6ee2 Disentangle gauge measurements and predictions Tom Gottfried <tom@intevation.de> parents: 3217 diff changeset	624 )
4045 12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements. Sascha Wilde <wilde@intevation.de> parents: 4044 diff changeset	625 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	626 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	627 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	628
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	629 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	630 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5009 e8b2dc771f9e Store axis as MultiLinestring Tom Gottfried <tom@intevation.de> parents: 5005 diff changeset	631 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	632 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	633 -- 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	634 objnam varchar NOT NULL,
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	635 nobjnam varchar,
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	636 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	637 CHECK (NOT isempty(validity)),
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	638 -- 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	639 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	640 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	641 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	642 AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis
5141 722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	643 DEFERRABLE INITIALLY DEFERRED
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	644 FOR EACH ROW
722b7c305319 Prevent equal axis geometries with intersecting validity Tom Gottfried <tom@intevation.de> parents: 5129 diff changeset	645 EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&')
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	646 CREATE INDEX waterway_axis_validity
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	647 ON waterway_axis USING GiST (validity)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	648
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	649 -- 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	650 -- 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	651 -- 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	652 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	653 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	654 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	655 related_enc varchar(12) NOT NULL,
1b11ff97c1d1 Add wwname to import of virtual distance marks. Sascha Wilde <wilde@intevation.de> parents: 3747 diff changeset	656 wwname varchar
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	657 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	658
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	659 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	660 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	661 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	662 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	663 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	664 -- 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	665 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	666 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	667 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	668 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	669
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	670 -- 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	671 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	672 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718 92640ae5be07 Backed out changeset 9279fdb7a422 Tom Gottfried <tom@intevation.de> parents: 4717 diff changeset	673 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	674 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	675 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	676 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	677 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	678 nobjnam varchar,
4740 2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	679 country char(2) NOT NULL REFERENCES countries
2440d2f86f4e Authorize sections based on country of creator Tom Gottfried <tom@intevation.de> parents: 4723 diff changeset	680 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	681 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	682 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	683 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	684 -- 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	685 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	686 -- 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	687 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	688 )
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	689 CREATE TRIGGER sections_date_info
4389 5e38667f740c Use stretches as areas of responsibility. Sascha Wilde <wilde@intevation.de> parents: 4358 diff changeset	690 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	691 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	692
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	693 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	694 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	695 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	696 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	697 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	698 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	699 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	700 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	701 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	702 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	703 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	704 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	705 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	706 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	707 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	708 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	709 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	710 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	711 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	712 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	713 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	714 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	715 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	716 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	717
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	718 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	719 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	720 area geography(MULTIPOLYGON, 4326) NOT NULL
5031 8c590ef35280 Improve feedback if geometry cannot be stored Tom Gottfried <tom@intevation.de> parents: 5030 diff changeset	721 CHECK(ST_IsValid(CAST(area AS geometry))
8c590ef35280 Improve feedback if geometry cannot be stored Tom Gottfried <tom@intevation.de> parents: 5030 diff changeset	722 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	723 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	724 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	725 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	726 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	727 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	728 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	729 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	730 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	731 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	732 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	733 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5095 e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5031 diff changeset	734 CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5031 diff changeset	735 AFTER INSERT OR UPDATE OF area, staging_done ON fairway_dimensions
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5031 diff changeset	736 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area', 'staging_done')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	737
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	738 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	739 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	740 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	741 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	742 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3624 diff changeset	743 bottleneck_id varchar NOT NULL,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3647 diff changeset	744 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3278 diff changeset	745 gauge_location isrs NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	746 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	747 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	748 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	749 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	750 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	751 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	752 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	753 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	754 revisiting_time smallint,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	755 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	756 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	757 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	758 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	759 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	760 -- 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	761 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	762 -- 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	763 -- XXX: Also an attribut of sounding result?
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3624 diff changeset	764 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	765 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	766 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	767 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	768 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	769 validity WITH &&,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag. Sascha Wilde <wilde@intevation.de> parents: 4106 diff changeset	770 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	771 DEFERRABLE INITIALLY DEFERRED
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	772 )
4041 3fcb95a07948 WIP: Started to remove references to gauge validity. Sascha Wilde <wilde@intevation.de> parents: 4016 diff changeset	773 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	774 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	775 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	776
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	777 CREATE TABLE bottlenecks_riverbed_materials (
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2867 diff changeset	778 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	779 ON DELETE CASCADE,
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	780 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	781 -- 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	782 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	783 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	784
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	785 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	786 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	787 bottleneck_id varchar NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	788 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	789 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	790 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	791 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	792 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	793 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	794 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	795 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	796 octree_index bytea,
4648 66fcd898efd9 Started with conversion tool. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4628 diff changeset	797 mesh_checksum varchar,
66fcd898efd9 Started with conversion tool. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4628 diff changeset	798 mesh_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	799 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	800 )
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4045 diff changeset	801 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	802 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	803 FOR EACH ROW
4068 76482935b6e5 Fixed c&p errors in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	804 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	805 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	806 AFTER INSERT OR UPDATE ON sounding_results
3747 ad67e4286d65 Fixed typo in gemma schema. Sascha Wilde <wilde@intevation.de> parents: 3746 diff changeset	807 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	808
4561 f7b57136c800 Added table to to store iso areas of sounding results. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4461 diff changeset	809 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	810 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	811 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	812 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	813 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	814 -- 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	815 -- 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	816 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	817 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	818 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	819 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	820 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	821 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	822 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	823 position varchar,
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	824 bottleneck_id varchar NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	825 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	826 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	827 -- 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	828 critical boolean,
4057 b79b60c0cc5a date_info has to be provided by the data source Tom Gottfried <tom@intevation.de> parents: 4033 diff changeset	829 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	830 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	831 )
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	832 -- 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	833 -- 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	834 -- (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	835 -- 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	836 -- 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	837 -- 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	838 -- 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	839 -- 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	840 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	841 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	842 FOR EACH ROW
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	843 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	844
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	845 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	846 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	847 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	848 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	849 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	850 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	851 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	852 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	853 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	854 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	855 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	856
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	857 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	858 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	859 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	860 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	861 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	862 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	863 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	864 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	865
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	866 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	867 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	868 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	869 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	870 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	871 measure_type),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	872 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	873 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	874 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	875 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	876 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	877 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	878 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	879 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	880 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	881 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	882 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	883 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	884 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	885 )
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	886
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	887 -- Attributes common to all fairway marks
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	888 CREATE TABLE fairway_marks (
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	889 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	890 CHECK (NOT isempty(validity)),
7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	891 -- 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	892 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	893 geom geography(POINT, 4326) NOT NULL,
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	894 -- Attributes according to IENC Feature Catalogue:
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	895 datsta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	896 datend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	897 persta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	898 perend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	899 objnam varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	900 nobjnm varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	901 inform varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	902 ninfom varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	903 scamin int,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	904 picrep varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	905 txtdsc varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	906 sordat varchar,
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	907 sorind varchar
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	908 )
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	909
6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	910 -- 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	911 CREATE TABLE fairway_marks_bcnlat (
4894 8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	912 colour varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	913 colpat varchar,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	914 condtn int,
8eb36d0d5bdf Draft implementation of fairway marks import Tom Gottfried <tom@intevation.de> parents: 4859 diff changeset	915 bcnshp int,
4912 bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	916 catlam int
4909 6f244b5eb716 Use table inheritance to define common fairway marks attributes Tom Gottfried <tom@intevation.de> parents: 4897 diff changeset	917 ) INHERITS (fairway_marks)
4962 1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	918
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	919 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	920 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	921 ) INHERITS (fairway_marks_bcnlat)
4895 9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	922 -- Prevent identical entries using composite type comparison
9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	923 -- (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	924 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
4965 be713316b47a Fixup rev. 1b309a8e7673 Tom Gottfried <tom@intevation.de> parents: 4964 diff changeset	925 ON fairway_marks_bcnlat_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	926 ((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	927 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	928 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	929 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	930 ) AS fairway_marks_bcnlat_hydro)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	931 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	932 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	933 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	934
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	935 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	936 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	937 ) INHERITS (fairway_marks_bcnlat)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	938 -- 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	939 -- (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	940 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
4965 be713316b47a Fixup rev. 1b309a8e7673 Tom Gottfried <tom@intevation.de> parents: 4964 diff changeset	941 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	942 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	943 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	944 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features Tom Gottfried <tom@intevation.de> parents: 4956 diff changeset	945 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	946 ) AS fairway_marks_bcnlat_ienc)
4895 9f799077a3e6 Prevent importing non-distinct fairway marks Tom Gottfried <tom@intevation.de> parents: 4894 diff changeset	947 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	948 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	949 ON fairway_marks_bcnlat_ienc USING GiST (validity)
4912 bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	950
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	951 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	952 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	953 dirimp smallint REFERENCES dirimps,
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	954 PRIMARY KEY (fm_bcnlat_id, dirimp)
bfd8ef836998 Fix handling of attribute dirimp Tom Gottfried <tom@intevation.de> parents: 4909 diff changeset	955 )
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	956
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	957 -- 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	958 CREATE TABLE fairway_marks_boycar (
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	959 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	960 colour varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	961 colpat varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	962 conrad int,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	963 marsys int,
4925 b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	964 boyshp int,
b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	965 catcam int
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	966 ) INHERITS (fairway_marks)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	967 -- 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	968 -- (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	969 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	970 ON fairway_marks_boycar
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	971 ((CAST((validity, last_found, geom,
4913 8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	972 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	973 scamin, picrep, txtdsc, sordat, sorind,
4925 b86ce7fc4da3 Add missing BOYCAR attribute Tom Gottfried <tom@intevation.de> parents: 4922 diff changeset	974 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	975 ) AS fairway_marks_boycar)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR Tom Gottfried <tom@intevation.de> parents: 4912 diff changeset	976 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	977 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	978 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	979
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	980 -- Additional attributes for IENC feature BOYLAT
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	981 CREATE TABLE fairway_marks_boylat (
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	982 colour varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	983 colpat varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	984 conrad int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	985 marsys int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	986 boyshp int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	987 catlam int
9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	988 ) INHERITS (fairway_marks)
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	989
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	990 CREATE TABLE fairway_marks_boylat_hydro (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	991 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	992 ) INHERITS (fairway_marks_boylat)
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	993 -- 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	994 -- (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	995 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	996 ON fairway_marks_boylat_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	997 ((CAST((validity, last_found, geom,
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	998 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	999 scamin, picrep, txtdsc, sordat, sorind,
4964 58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1000 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1001 ) AS fairway_marks_boylat_hydro)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1002 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1003 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	1004 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	1005
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1006 CREATE TABLE fairway_marks_boylat_ienc (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1007 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	1008 ) INHERITS (fairway_marks_boylat)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1009 -- Prevent identical entries using composite type comparison
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1010 -- (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	1011 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	1012 ON fairway_marks_boylat_ienc
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1013 ((CAST((validity, last_found, geom,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1014 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	1015 scamin, picrep, txtdsc, sordat, sorind,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1016 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT Tom Gottfried <tom@intevation.de> parents: 4962 diff changeset	1017 ) AS fairway_marks_boylat_ienc)
4922 9bd6a0ca63ea Add import for fairway marks of type BOYLAT Tom Gottfried <tom@intevation.de> parents: 4919 diff changeset	1018 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1019 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	1020 ON fairway_marks_boylat_ienc USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1021
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1022 -- Additional attributes for IENC feature BOYSAW
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1023 CREATE TABLE fairway_marks_boysaw (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1024 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1025 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1026 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1027 conrad int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1028 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1029 boyshp int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1030 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1031 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1032 -- (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	1033 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1034 ON fairway_marks_boysaw
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1035 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1036 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1037 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1038 0, colour, colpat, conrad, marsys, boyshp
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1039 ) AS fairway_marks_boysaw)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1040 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1041 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	1042 ON fairway_marks_boysaw USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1043
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1044 -- Additional attributes for IENC feature BOYSPP
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1045 CREATE TABLE fairway_marks_boyspp (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1046 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1047 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1048 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1049 conrad int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1050 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1051 boyshp int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1052 catspm varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1053 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1054 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1055 -- (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	1056 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1057 ON fairway_marks_boyspp
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1058 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1059 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1060 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1061 0, colour, colpat, conrad, marsys, boyshp, catspm
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1062 ) AS fairway_marks_boyspp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1063 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1064 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	1065 ON fairway_marks_boyspp USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1066
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1067 -- Additional attributes for IENC features DAYMAR/daymar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1068 CREATE TABLE fairway_marks_daymar (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1069 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1070 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1071 condtn int,
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1072 topshp int
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1073 ) INHERITS (fairway_marks)
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1074
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1075 CREATE TABLE fairway_marks_daymar_hydro (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1076 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	1077 ) INHERITS (fairway_marks_daymar)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1078 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1079 -- (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	1080 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	1081 ON fairway_marks_daymar_hydro
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1082 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1083 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1084 scamin, picrep, txtdsc, sordat, sorind,
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1085 colour, colpat, condtn, topshp, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1086 ) AS fairway_marks_daymar_hydro)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1087 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1088 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	1089 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	1090
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1091 CREATE TABLE fairway_marks_daymar_ienc (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1092 orient double precision,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1093 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	1094 ) INHERITS (fairway_marks_daymar)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1095 -- Prevent identical entries using composite type comparison
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1096 -- (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	1097 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	1098 ON fairway_marks_daymar_ienc
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1099 ((CAST((validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1100 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	1101 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1102 colour, colpat, condtn, topshp, orient, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1103 ) AS fairway_marks_daymar_ienc)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1104 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1105 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	1106 ON fairway_marks_daymar_ienc USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1107
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1108 CREATE TABLE fairway_marks_daymar_dirimps (
4967 3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR Tom Gottfried <tom@intevation.de> parents: 4965 diff changeset	1109 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	1110 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1111 PRIMARY KEY (fm_daymar_id, dirimp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1112 )
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1113
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1114 -- Additional attributes for IENC feature LIGHTS
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1115 CREATE TABLE fairway_marks_lights (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1116 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1117 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1118 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1119 orient double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1120 catlit varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1121 exclit int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1122 litchr int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1123 litvis varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1124 mltylt int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1125 sectr1 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1126 sectr2 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1127 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1128 sigper double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1129 sigseq varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1130 status varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1131 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1132 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1133 -- (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	1134 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1135 ON fairway_marks_lights
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1136 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1137 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1138 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1139 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1140 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1141 ) AS fairway_marks_lights)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1142 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1143 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	1144 ON fairway_marks_lights USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1145
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1146 -- Additional attributes for IENC feature RTPBCN
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1147 CREATE TABLE fairway_marks_rtpbcn (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1148 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1149 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1150 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1151 catrtb int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1152 radwal varchar
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1153 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1154 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1155 -- (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	1156 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1157 ON fairway_marks_rtpbcn
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1158 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1159 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1160 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1161 0, condtn, siggrp, catrtb, radwal
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1162 ) AS fairway_marks_rtpbcn)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1163 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1164 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	1165 ON fairway_marks_rtpbcn USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1166
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1167 -- Additional attributes for IENC feature TOPMAR
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1168 CREATE TABLE fairway_marks_topmar (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1169 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1170 colour varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1171 colpat varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1172 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1173 topshp int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1174 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1175 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1176 -- (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	1177 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1178 ON fairway_marks_topmar
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1179 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1180 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1181 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1182 0, colour, colpat, condtn, topshp
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1183 ) AS fairway_marks_topmar)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1184 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1185 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	1186 ON fairway_marks_topmar USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1187
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1188 -- Additional attributes for IENC feature NOTMRK
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1189 CREATE TABLE fairway_marks_notmrk (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1190 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1191 condtn int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1192 marsys int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1193 orient double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1194 status varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1195 addmrk varchar,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1196 catnmk int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1197 disipd double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1198 disipu double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1199 disbk1 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1200 disbk2 double precision,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1201 fnctnm int,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1202 bnkwtw int
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1203 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1204 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1205 -- (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	1206 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1207 ON fairway_marks_notmrk
4956 7cc79c65a9e5 Keep the history of fairway marks Tom Gottfried <tom@intevation.de> parents: 4940 diff changeset	1208 ((CAST((validity, last_found, geom,
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1209 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1210 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1211 0, condtn, marsys, orient, status, addmrk, catnmk,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1212 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1213 ) AS fairway_marks_notmrk)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1214 ))
4971 de190de05f67 Add index to speed up fairway marks imports a bit Tom Gottfried <tom@intevation.de> parents: 4967 diff changeset	1215 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	1216 ON fairway_marks_notmrk USING GiST (validity)
4940 b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1217
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1218 CREATE TABLE fairway_marks_notmrk_dirimps (
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1219 fm_notmrk_id int REFERENCES fairway_marks_notmrk,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1220 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1221 PRIMARY KEY (fm_notmrk_id, dirimp)
b3b2ba09a450 Add missing fairway mark types Tom Gottfried <tom@intevation.de> parents: 4930 diff changeset	1222 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1223 ;
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
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1226 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1227 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1228 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1229 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1230 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1231 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1232 'failed', 'unchanged', 'pending',
5096 f64ff954ee31 Added new reviewed state. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 5031 diff changeset	1233 'accepted', 'declined', 'reviewed'
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1234 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1235
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1236 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	1237
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1238 -- 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	1239 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1240
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1241 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1242 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	1243 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1244 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1245 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1246 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	1247 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	1248 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1249
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1250 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	1251 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	1252 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	1253 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1254 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	1255 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	1256 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	1257 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	1258 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1259
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1260 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1261 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	1262 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	1263 kind varchar NOT NULL,
4098 cfa0a5775d70 Reapplied timezone patch to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4094 diff changeset	1264 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	1265 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	1266 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	1267 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1268 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1269 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1270 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	1271 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1272 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1273 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1274 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1275 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1276 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1277 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1278 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1279 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	1280 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1281 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1282 )
5122 0b6b62d247e8 Prioritize review jobs on selection Tom Gottfried <tom@intevation.de> parents: 5096 diff changeset	1283 -- Mainly for listing imports in clients:
0b6b62d247e8 Prioritize review jobs on selection Tom Gottfried <tom@intevation.de> parents: 5096 diff changeset	1284 CREATE INDEX enqueued_idx ON imports(enqueued)
0b6b62d247e8 Prioritize review jobs on selection Tom Gottfried <tom@intevation.de> parents: 5096 diff changeset	1285 -- For fast retrieval of queued imports by the import queue in backend:
0b6b62d247e8 Prioritize review jobs on selection Tom Gottfried <tom@intevation.de> parents: 5096 diff changeset	1286 CREATE INDEX state_idx ON imports(state)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1287
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1288 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1289 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	1290 ON DELETE CASCADE,
4100 cc3d607b49cc Merged default into timezone branch. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4098 4099 diff changeset	1291 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	1292 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	1293 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1294 )
4187 65a5501dc13d Enable faster access to import logs Tom Gottfried <tom@intevation.de> parents: 4173 diff changeset	1295 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	1296
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1297 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1298 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	1299 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	1300 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	1301 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1302 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1303 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1304 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	1305 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	1306
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	1307 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	1308 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1309 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1310 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1311 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1312 FOR tmp IN
4126 52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1313 SELECT relation, array_agg(key) AS keys
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1314 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1315 WHERE import_id = imp_id AND NOT deletion
52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1316 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	1317 LOOP
4126 52f7264265bb Bulk-delete tracked entries per table Tom Gottfried <tom@intevation.de> parents: 4106 diff changeset	1318 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	1319 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	1320 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1321 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1322 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1323 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	1324
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1325 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1326
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1327 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1328 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	1329 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	1330 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1331 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	1332 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1333 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1334 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1335
4573 26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1336 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	1337 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	1338 ON DELETE CASCADE,
26e9846ed69f Added caching table for iso areas of sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 4561 diff changeset	1339 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	1340 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	1341 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	1342 )
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1343 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	1344
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	1345 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 5141:722b7c305319