gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 3003:92818da6133d

Cleanup VIEW definition NULLS LAST is not necessary on non-nullable columns; avoid materializing unused columns in a CTE; use boolean value directly instead of comparing to auto-casted string; gauge_measurements might contain not only waterlevels; avoid unnecessary subselects.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 10 Apr 2019 18:50:20 +0200
parents	b3c3c5b5b7c1
children	f394e828a6d2

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
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 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 --
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	26 -- Trigger functions
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	27 --
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	28 -- 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	29 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	34 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	35 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	36 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	37 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	38
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	39 -- 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	40 -- of geometries in the column with its name given as an argument to the
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	41 -- trigger function
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	42 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	43 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	44 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	45 new_geom geometry;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	46 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	47 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	48 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	49 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	50 USING NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	51 EXECUTE format(
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	52 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	53 'WHERE id <> $2',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	54 TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	55 INTO has_equal
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	56 USING new_geom, NEW.id;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	57 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	58 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	59 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	60 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	61 USING
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	62 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	63 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	64 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	65 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	66 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	67 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	68 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	69 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	70 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	71 END;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	72 $$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	73 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	74
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	75 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	76 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	77 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	78
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	79 -- Namespace not to be accessed directly by any user
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	80 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	81 -- Profile data are only accessible via the view users.list_users.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	82 CREATE TABLE user_profiles (
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	83 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	84 -- keep username length compatible with role identifier
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	85 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	86 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	87 )
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	88 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	89 ;
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	90
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	91
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	92 -- Namespace to be accessed by sys_admin only
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	93 CREATE SCHEMA sys_admin
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	94 CREATE TABLE system_config (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	95 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	96 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	97 )
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	98
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	99 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	100 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	101 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	102 username varchar NOT NULL
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	103 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	104 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	105 )
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	106
624 8772979f8750 Remove test data from schema Tom Gottfried <tom@intevation.de> parents: 611 diff changeset	107 -- Tables with geo data to be published with GeoServer.
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	108 CREATE TABLE external_services (
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	109 local_name varchar PRIMARY KEY,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	110 remote_url varchar NOT NULL,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	111 is_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	112 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	113
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	114 CREATE TABLE published_services (
598 4854a1e85870 Ensure published service is based on existing table Tom Gottfried <tom@intevation.de> parents: 580 diff changeset	115 name regclass PRIMARY KEY,
1288 9f7dc950ffd2 Accept only well-formed XML documents as style in database Tom Gottfried <tom@intevation.de> parents: 1194 diff changeset	116 style xml CHECK(style IS DOCUMENT),
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	117 as_wms boolean NOT NULL DEFAULT TRUE,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	118 as_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	119 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	120 ;
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	121
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	122 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	123 -- 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	124 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	125 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	126 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	127 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	128
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	129 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	130 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 -- 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	132 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	133 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	134 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	135
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	136 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	137 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	138 -- 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	139 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	140 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	141 INSERT INTO dirimps VALUES (1), (2), (3), (4);
48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	142 -- dirimp_5 left out because it cannot be used for waterway area
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	143
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	144 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	145 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	146 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	147 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	148 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	149 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	150 -- 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	151 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	152 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	153 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	154
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	155 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	156 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	157 -- 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	158 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	159 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	160 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	161
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	162 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	163 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	164 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	165 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	166 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	167 -- 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	168 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	169 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	170 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	171
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	172 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	173 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	174 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	175 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	176 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	177 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	178 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	179 ) 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	180
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	181 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	182 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	183 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	184 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	185 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	186
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	187 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	188 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	189 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	190
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	191 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	192 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	193 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	194
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	195 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	196 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	197 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	198
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	199 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	200 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	201 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	202
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	203
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	204 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	205 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	206 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	207 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	208 area geography(MULTIPOLYGON, 4326)
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	209 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	210 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	211
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212 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	213 template_name varchar NOT NULL,
2267 37ae1bee3e4a Ajjusted RLS for user templates. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2230 diff changeset	214 country char(2) REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	215 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	216 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
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	217 UNIQUE (template_name, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	218 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	219 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	220 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	221 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	222 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	223 country char(2) NOT NULL REFERENCES users.responsibility_areas;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	225
831 fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	226 -- Namespace for system wide configuration
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	227 CREATE SCHEMA systemconf
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	228 CREATE TABLE feature_colours (
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	229 feature_name varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	230 style_attr varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	231 r int NOT NULL CHECK (r >= 0 AND r < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	232 g int NOT NULL CHECK (g >= 0 AND g < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	233 b int NOT NULL CHECK (b >= 0 AND b < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	234 a numeric NOT NULL CHECK (a >= 0 AND a <= 1),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	235 PRIMARY KEY (feature_name, style_attr)
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	236 )
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	237 ;
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	238
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	239 -- 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	240 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	241
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	242 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	243 -- 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	244 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	246 -- -- 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	247 -- -- 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	248 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	249 -- -- 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	250 -- -- 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	251 -- -- => 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	252 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	253 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	254 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	255 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	256
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	257 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	258 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	259 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	260 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	261 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	262 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	263 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	264 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	265 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	266 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	267
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	268
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	269 CREATE TABLE gauges (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	270 location isrs PRIMARY KEY CHECK(
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	271 (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	272 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	273 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	274 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	275 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	276 applicability_to_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	277 validity tstzrange,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	278 -- pasted text from a more general specification is given
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	279 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	280 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	281 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	282 geodref varchar,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	283 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
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	284 source_organization varchar
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	285 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	286 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287 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	288
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	289 CREATE TABLE gauges_reference_water_levels (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	290 gauge_id isrs NOT NULL REFERENCES gauges,
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	291 -- 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	292 -- names, too:
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	293 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	294 PRIMARY KEY (gauge_id, depth_reference),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	296 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	297
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	298 CREATE VIEW gauges_geoserver AS
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	299 SELECT
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	300 g.location,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	301 isrs_asText(g.location) AS isrs_code,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	302 g.objname,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	303 g.geom,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	304 g.applicability_from_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	305 g.applicability_to_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	306 g.validity,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	307 g.zero_point,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	308 g.geodref,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	309 g.date_info,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	310 g.source_organization,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	311 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	312 r.value))
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	313 AS reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	314 FROM gauges g LEFT JOIN LATERAL (
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	315 SELECT gauge_id, depth_reference, value
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	316 FROM gauges_reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	317 ) r ON r.gauge_id = g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	318 GROUP BY g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	319
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	320 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	321 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	322 fk_gauge_id isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	323 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	324 country_code char(2) NOT NULL REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	325 -- TODO: add relations to stuff provided as enumerations
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	326 sender varchar NOT NULL, -- "from" attribute from DRC
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	327 language_code varchar NOT NULL REFERENCES language_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	328 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	329 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	330 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	331 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	332 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	333 -- XXX: "measure_code" if really only W or Q
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	334 -- XXX: Do we need "unit" attribute or can we normalise on import?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	335 value_min double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	336 value_max double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	337 --- TODO: Add a double range type for checking?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	338 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1780 48791416bea5 (Approved) gauge measurement import: Fixed row level security. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1775 diff changeset	339 source_organization varchar NOT NULL, -- "originator"
2863 e2d362589ce6 gemma.sql removing trailing comma Thomas Junk <thomas.junk@intevation.de> parents: 2849 diff changeset	340 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	341 )
2849 b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	342 -- Constraints are conditional for gauge_measurements, as they
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	343 -- differ between predicted values and measured ones. PG does not
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	344 -- have real conditional unique constraints, but we can use unique
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	345 -- indeces for that.
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	346 --
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	347 -- So we can have a staged and a non-staged
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	348 -- fk_gauge_id/measure_date pairs in measured values.
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	349 CREATE UNIQUE INDEX gm_measured_unique_constraint
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	350 ON gauge_measurements (fk_gauge_id, measure_date, staging_done)
2864 96781e519952 Fix syntax error in schema Tom Gottfried <tom@intevation.de> parents: 2863 diff changeset	351 WHERE NOT predicted
2849 b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	352 -- And we can have multiple predictions for one point in time
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	353 -- (but they are never staged).
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	354 CREATE UNIQUE INDEX gm_predicted_unique_constraint
b8972e4671fa Preserve old predictions on raw gauge measurement import. Sascha Wilde <wilde@intevation.de> parents: 2811 diff changeset	355 ON gauge_measurements (fk_gauge_id, measure_date, date_issue)
2864 96781e519952 Fix syntax error in schema Tom Gottfried <tom@intevation.de> parents: 2863 diff changeset	356 WHERE predicted
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	357
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	358 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	359 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	360 wtwaxs geography(LINESTRING, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	361 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	362 -- 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	363 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	364 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	365 )
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	366 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	367 AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: 2770 diff changeset	368 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	369
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	370 -- 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	371 -- 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	372 -- 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	373 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	374 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	375 geom geography(POINT, 4326) NOT NULL,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	376 related_enc varchar(12) NOT NULL
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	377 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	378
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	379 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	380 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	381 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	382 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	383 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	384 -- 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	385 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	386 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	387 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	388 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	389
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	390 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	391 -- At least geoserver-2.13.2 does not serve type geography correctly
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	392 -- and does not serve the location_code as isrs type
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	393 CREATE VIEW distance_marks_geoserver AS
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	394 SELECT location_code,
3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	395 isrs_asText(location_code) AS location,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	396 geom::Geometry(POINT, 4326),
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	397 related_enc,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	398 (location_code).hectometre
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	399 FROM distance_marks_virtual
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	400
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	401 CREATE VIEW distance_marks_ashore_geoserver AS
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	402 SELECT id,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	403 country,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	404 geom::Geometry(POINT, 4326),
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	405 related_enc,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	406 hectom,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	407 catdis,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	408 position_code
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	409 FROM distance_marks
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	410
1446 1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	411 -- We need to configure primary keys for the views used by
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	412 -- geoserver for wfs, otherwise it will generate ids on the fly,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	413 -- which will change for the same feature...
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	414 -- See
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	415 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	416 -- for details.
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	417 CREATE TABLE gt_pk_metadata (
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	418 table_schema VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	419 table_name VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	420 pk_column VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	421 pk_column_idx INTEGER,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	422 pk_policy VARCHAR(32),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	423 pk_sequence VARCHAR(64),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	424 unique (table_schema, table_name, pk_column),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	425 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	426 )
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	427
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	428 CREATE TABLE stretches (
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	429 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	430 name varchar NOT NULL,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	431 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	432 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	433 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	434 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	435 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	436 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	437 source_organization varchar NOT NULL,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	438 staging_done boolean NOT NULL DEFAULT false,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	439 UNIQUE(name, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	440 )
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	441
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	442 CREATE TABLE stretch_countries (
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	443 stretches_id int NOT NULL REFERENCES stretches(id)
32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	444 ON DELETE CASCADE,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	445 country_code char(2) NOT NULL REFERENCES countries(country_code),
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	446 UNIQUE(stretches_id, country_code)
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	447 )
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	448
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	449 -- Published view for GeoServer
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	450 CREATE VIEW stretches_geoserver AS SELECT
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	451 id,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	452 name,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	453 (stretch).lower::varchar as lower,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	454 (stretch).upper::varchar as upper,
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1975 diff changeset	455 area::Geometry(MULTIPOLYGON, 4326),
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	456 objnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	457 nobjnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	458 date_info,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	459 source_organization,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	460 (SELECT string_agg(country_code, ', ')
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	461 FROM stretch_countries
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	462 WHERE stretches_id = id) AS countries,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	463 staging_done
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	464 FROM stretches
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	465
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	466
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	467 CREATE TRIGGER sections_stretches_date_info
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	468 BEFORE UPDATE ON stretches
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	469 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	470
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	471 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	472 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	473 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	474 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	475 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	476 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	477 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	478 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	479 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	480 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	481 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	482 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	483 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	484 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	485 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	486 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	487 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	488 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	489 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	490 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	491 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	492 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	493 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	494 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	495
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	496 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	497 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	498 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	499 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	500 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	501 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	502 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	503 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	504 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	505 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	506 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	507 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	508 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	509 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	510 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	511
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	512 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	513 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	514 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	515 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	516 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	517 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	518 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	519 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	520 bottleneck_id varchar UNIQUE NOT NULL,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	521 fk_g_fid isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	522 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	523 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	524 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	525 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	526 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	527 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	528 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	529 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	530 responsible_country char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	531 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	532 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	533 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	534 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	535 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	536 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	537 -- 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	538 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	539 -- 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	540 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	541 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	542 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	543 -- 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	544 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	545 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	546 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	547 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	548
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	549 CREATE TABLE bottlenecks_riverbed_materials (
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2867 diff changeset	550 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	551 ON DELETE CASCADE,
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	552 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	553 -- 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	554 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	555 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	556
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	557 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	558 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	559 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	560 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	561 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	562 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	563 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	564 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	565 coverage varchar REFERENCES coverage_types,
2444 7ca6bdb2d174 depth_reference in sounding data must be more permissive. Sascha Wilde <wilde@intevation.de> parents: 2396 diff changeset	566 depth_reference varchar(4) NOT NULL, -- REFERENCES depth_references,
1131 2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	567 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	568 octree_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	569 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	570 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	571
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	572 CREATE TABLE sounding_results_contour_lines (
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	573 sounding_result_id int NOT NULL REFERENCES sounding_results
dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	574 ON DELETE CASCADE,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	575 height numeric NOT NULL,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	576 lines 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	577 -- TODO: generate valid simple features and add constraint:
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	578 -- CHECK(ST_IsSimple(CAST(lines AS geometry))),
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	579 PRIMARY KEY (sounding_result_id, height)
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	580 )
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	581 -- A view to help geoserver serve contour lines.
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	582 -- At least geoserver-2.13.2 does not serve type geography correctly
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	583 CREATE VIEW sounding_results_contour_lines_geoserver AS
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	584 SELECT bottleneck_id,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	585 date_info,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	586 height,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	587 CAST(lines AS geometry(multilinestring, 4326)) AS lines
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	588 FROM sounding_results_contour_lines cl
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	589 JOIN sounding_results sr
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	590 ON sr.id = cl.sounding_result_id
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	591
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	592 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	593 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	594 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	595 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	596 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	597 position_code char(2) REFERENCES position_codes,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	598 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	599 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	600 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	601 -- 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	602 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	603 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	604 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	605 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	606 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	607 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	608 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	609
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	610 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	611 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	612 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	613 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	614 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	615 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	616 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	617 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	618 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	619 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	620 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	621
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	622 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	623 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	624 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	625 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	626 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	627 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	628 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	629 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	630
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	631 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	632 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	633 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	634 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	635 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	636 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	637 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	638 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	639 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	640 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	641 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	642 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	643 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	644 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	645 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	646 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	647 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	648 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	649 )
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	650
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	651 CREATE VIEW bottleneck_overview AS
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	652 SELECT
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	653 objnam AS name,
2558 802d2a62b4bc schema: fix bottleneck_overview's project Bernhard Reiter <bernhard@intevation.de> parents: 2542 diff changeset	654 ST_Centroid(area)::Geometry(POINT, 4326) AS point,
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	655 (lower(stretch)).hectometre AS from,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	656 (upper(stretch)).hectometre AS to,
2811 3b1bdbbc0225 added responsible_country to bottleneck_overview Markus Kottlaender <markus@intevation.de> parents: 2781 diff changeset	657 sr.current::text,
3b1bdbbc0225 added responsible_country to bottleneck_overview Markus Kottlaender <markus@intevation.de> parents: 2781 diff changeset	658 responsible_country
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	659 FROM bottlenecks bn LEFT JOIN (
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	660 SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	661 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	662 ORDER BY objnam
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	663
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	664 -- Published view for GeoServer
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	665 CREATE VIEW bottlenecks_geoserver AS
3003 92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	666 WITH
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	667 fairway_availability_latest AS (
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	668 SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	669 FROM fairway_availability
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	670 ORDER BY bottleneck_id, date_info DESC),
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	671 waterlevel_latest AS (
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	672 SELECT DISTINCT ON (fk_gauge_id) fk_gauge_id, water_level
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	673 FROM gauge_measurements
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	674 WHERE is_waterlevel AND NOT predicted
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	675 ORDER BY fk_gauge_id, measure_date DESC)
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	676 SELECT
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	677 b.id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	678 b.bottleneck_id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	679 b.objnam,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	680 b.nobjnm,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	681 b.stretch,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	682 b.area,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	683 b.rb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	684 b.lb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	685 b.responsible_country,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	686 b.revisiting_time,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	687 b.limiting,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	688 b.date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	689 b.source_organization,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	690 g.location AS gauge_isrs_code,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	691 g.objname AS gauge_objname,
2770 ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	692 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	693 r.value))
ab1a22052437 Handle the case of missing depth references for bottlenecks gracefully. Sascha Wilde <wilde@intevation.de> parents: 2765 diff changeset	694 AS reference_water_levels,
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	695 fal.date_info AS fa_date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	696 fal.critical AS fa_critical,
3003 92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	697 wl.water_level AS gm_waterlevel
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	698 FROM bottlenecks b
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	699 LEFT JOIN gauges g ON b.fk_g_fid = g.location
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	700 LEFT JOIN gauges_reference_water_levels r ON g.location = r.gauge_id
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	701 LEFT JOIN fairway_availability_latest fal ON b.id = fal.bottleneck_id
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	702 LEFT JOIN waterlevel_latest wl ON b.fk_g_fid = wl.fk_gauge_id
92818da6133d Cleanup VIEW definition Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	703 GROUP BY b.id, g.location, fal.date_info, fal.critical, wl.water_level
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	704 ;
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	705
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	706 -- Configure primary keys for geoserver views
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	707 INSERT INTO waterway.gt_pk_metadata VALUES
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	708 ('waterway', 'gauges_geoserver', 'location'),
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	709 ('waterway', 'distance_marks_geoserver', 'location_code'),
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	710 ('waterway', 'distance_marks_ashore_geoserver', 'id'),
2341 fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	711 ('waterway', 'bottlenecks_geoserver', 'id'),
fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	712 ('waterway', 'stretches_geoserver', 'id');
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	713
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	714 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	715 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	716 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	717 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	718 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	719 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	720 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	721 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	722 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	723
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	724 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	725
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	726 -- 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	727 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	728
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	729 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	730 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	731 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	732 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	733 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	734 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	735 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	736 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	737
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	738 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	739 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	740 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	741 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	742 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	743 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	744 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	745 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	746 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	747
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	748 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	749 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	750 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	751 kind varchar NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	752 enqueued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	753 due timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	754 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	755 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	756 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	757 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	758 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	759 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	760 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	761 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	762 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	763 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	764 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	765 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	766 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	767 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	768 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	769 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	770
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	771 CREATE INDEX enqueued_idx ON imports(enqueued, state)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	772
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	773 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	774 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	775 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	776 time timestamp NOT NULL DEFAULT now(),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	777 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	778 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	779 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	780
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	781 CREATE INDEX kind_idx ON import_logs(kind)
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	782
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	783 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	784 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	785 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	786 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	787 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	788 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	789 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	790 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	791
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	792 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	793 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	794 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	795 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	796 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	797 FOR tmp IN
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	798 SELECT * FROM import.track_imports WHERE import_id = imp_id
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	799 LOOP
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	800 EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	801 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	802 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	803 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	804 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	805
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	806 CREATE FUNCTION import.del_import() RETURNS trigger AS
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	807 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	808 BEGIN
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	809 EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key;
1354 1000e0e71d5a Added missing rights and fixed trigger function. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1338 diff changeset	810 RETURN NULL;
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	811 END;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	812 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	813 LANGUAGE plpgsql;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	814
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	815 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	816
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	817 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	818 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	819 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	820 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	821 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	822 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	823 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	824 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	825
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	826 CREATE TABLE sounding_differences_contour_lines (
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	827 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	828 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	829 height numeric NOT NULL,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	830 lines geography(multilinestring, 4326) NOT NULL,
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	831 PRIMARY KEY (sounding_differences_id, height)
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	832 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	833 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	834
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	835 CREATE VIEW waterway.sounding_differences AS SELECT
2589 f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	836 sd.id AS id,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	837 bn.objnam AS objnam,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	838 srm.date_info AS minuend,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	839 srs.date_info AS subtrahend,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	840 sdcl.height AS height,
f4c399a496cb Morphological differences: Add SRID to cast in ios-lines view. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2558 diff changeset	841 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
2542 fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	842 FROM
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	843 caching.sounding_differences sd JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	844 caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	845 waterway.sounding_results srm ON sd.minuend = srm.id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	846 waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	847 waterway.bottlenecks bn ON srm.bottleneck_id = bn.id;
fc7d828695c9 Schema: Added caching schema containing the sounding differences. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2529 diff changeset	848
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	849 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 3003:92818da6133d