gemma: schema/updates/1100/02.remove_bottleneck_validity

annotate schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 5361:ce1fe22bda5a extented-report

Backed out changeset f845c3b7b68e

author	Sascha L. Teichmann <sascha.teichmann@intevation.de>
date	Tue, 22 Jun 2021 17:12:17 +0200
parents	30a567b1cc2e
children

rev	line source
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	2 -- without warranty, see README.md and license for details.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	3
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	6
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	7 -- Copyright (C) 2019 by via donau
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	9 -- Software engineering by Intevation GmbH
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	10
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	11 -- Author(s):
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	12 -- * Sascha Wilde <sascha.wilde@intevation.de>
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	13
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	14 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	15 -- CONSTRAINT FUNCTIONS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	16 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	17
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	18 -- We still want to ensure, that there is at least a valid bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	19 -- at any time of the referencing objects validity. To ensure this we
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	20 -- need a trigger constraint:
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	21 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	22 $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	23 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	24 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	25 new_tstz timestamptz;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	26 BEGIN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	27 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	28 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	29 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	30 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	31 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	32 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	33 IF EXISTS ( SELECT * FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	34 WHERE bottleneck_id = referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	35 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	36 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	37 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	38 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	39 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	40 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	41 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	42 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	43 DETAIL = format('No matching bottleneck %s for %s found.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	44 referenced_bottleneck_id, new_tstz),
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	45 ERRCODE = 23505,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	46 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	47 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	48 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	49 CONSTRAINT = TG_NAME;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	50 END IF;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	51 END;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	52 $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	53 LANGUAGE plpgsql;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	54
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	55 -- Redifine constraint trigger: sounding Results must intersect with
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	56 -- the area of the bottleneck they belong to. Bottleneck is
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	57 -- determined dynamically via date_info.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	58 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	59 LANGUAGE plpgsql
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	60 AS $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	61 BEGIN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	62 IF NOT st_intersects((SELECT area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	63 FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	64 WHERE bottleneck_id = NEW.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	65 AND validity @> NEW.date_info::timestamptz),
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	66 NEW.area)
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	67 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	68 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	69 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	70 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	71 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	72 DETAIL = 'Failing row area has no intersection with bottleneck.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	73 ERRCODE = 23514,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	74 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	75 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	76 CONSTRAINT = TG_NAME;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	77 END IF;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	78 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	79 END;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	80 $$;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	81
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	82
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	83 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	84 -- SOUNDING RESULTS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	85 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	86
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	87 -- Dynamic version of sounding_differences geoserver view: Instead of
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	88 -- a static reference to a specific bottleneck data set via primary
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	89 -- key (id, validity) we check for a bottleneck valid at the time of
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	90 -- the survey at executiuon time.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	91 CREATE OR REPLACE VIEW waterway.sounding_differences AS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	92 SELECT
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	93 sd.id AS id,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	94 bn.objnam AS objnam,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	95 srm.date_info AS minuend,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	96 srs.date_info AS subtrahend,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	97 sdcl.height AS height,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	98 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	99 FROM caching.sounding_differences sd
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	100 JOIN caching.sounding_differences_contour_lines sdcl
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	101 ON sd.id = sdcl.sounding_differences_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	102 JOIN waterway.sounding_results srm
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	103 ON sd.minuend = srm.id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	104 JOIN waterway.sounding_results srs
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	105 ON sd.subtrahend = srs.id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	106 JOIN waterway.bottlenecks bn
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	107 ON srm.bottleneck_id = bn.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	108 AND srm.date_info::timestamptz <@ bn.validity;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	109
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	110 -- As we resolve the correct gauge data to use on runtime, we drop the
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	111 -- hard reference to the bottlenecks vaidity:
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	112 ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	113
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	114 -- Note, we now use prefixed names, to ensure correct execution order
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	115 -- for the triggers...
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	116 DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	117 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	118 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	119 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	120 FOR EACH ROW
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	121 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	122
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	123 DROP TRIGGER IF EXISTS sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	124 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	125 DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	126 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	127 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	128 AFTER INSERT OR UPDATE ON waterway.sounding_results
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	129 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	130
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	131
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	132 --
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	133 -- FAIRWAY AVAILABILITY
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	134 --
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	135
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	136 -- The current implementation associates fairway_availability data
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	137 -- hard with the internal id of an bottleneck. Lets use the
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	138 -- bottleneck_id (official unique id) instead and to the lookup of an
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	139 -- matching bottleneck for a specific date on demand.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	140
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	141 -- Dear reader: The gemma schema update scripts are intended to be run
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	142 -- only once, which is implemented via schema verioning and the
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	143 -- update-db.sh script. None the less it is very helpful to be able
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	144 -- to run scripts more than once during development and testing,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	145 -- without harm being done. Thats the reason for the following code
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	146 -- to be, like it is:
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	147 --
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	148 -- This migration can only be done once, thanks to psql magic we are
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	149 -- able to guard it approprieatly.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	150
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	151 SELECT data_type='integer' AS old_fwa_bnid
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	152 FROM information_schema.columns
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	153 WHERE table_schema = 'waterway'
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	154 AND table_name='fairway_availability'
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	155 AND column_name='bottleneck_id';
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	156 \gset
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	157 \if :old_fwa_bnid
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	158 \qecho 'Migrating bottleneck_id column in fairway_availability to text id.'
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	159 -- We temporarily keep the old if field for the migration of existing data:
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	160 ALTER TABLE waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	161 RENAME COLUMN bottleneck_id TO old_bnid;
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	162 ALTER TABLE waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	163 ADD COLUMN bottleneck_id varchar;
4085 c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	164
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	165 -- Migrate existing data: Let's be ultra permissive and make the
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	166 -- migration _before adding the constraint triggger.
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	167 UPDATE waterway.fairway_availability AS fwa
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	168 SET bottleneck_id = b.bottleneck_id
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	169 FROM waterway.bottlenecks b
c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	170 WHERE b.id = fwa.old_bnid;
4092 30a567b1cc2e Re-added uniq constraint after waterway.fairway_availability update. Sascha Wilde <wilde@intevation.de> parents: 4085 diff changeset	171 ALTER TABLE waterway.fairway_availability
30a567b1cc2e Re-added uniq constraint after waterway.fairway_availability update. Sascha Wilde <wilde@intevation.de> parents: 4085 diff changeset	172 ALTER COLUMN bottleneck_id SET NOT NULL;
4085 c0642c1c0ff8 Be more permissive during afd data migration. Sascha Wilde <wilde@intevation.de> parents: 4067 diff changeset	173
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	174 -- Set constraint trigger to make sure a matching BN exists:
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	175 --
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	176 -- FIXME: From the DRC it is unclear what the exact semantics of
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	177 -- surdat and Date_Info ar unclear. Currently we assume that
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	178 -- (fk_bn_fid,surdat) has to be unique, but that might be false.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	179 -- Anyway, I will date_info here to check for an matching
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	180 -- reference gauge at the bottleneck. The reason for this
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	181 -- decision is purely practical (and might be semantically
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	182 -- disputable: the bottleneck data in the demo system is not old
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	183 -- enough to cover rthe surdat times...
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	184 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	185 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	186 FOR EACH ROW
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	187 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	188 -- Set NOT NULL constraint for new column
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	189 ALTER TABLE waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	190 ALTER COLUMN bottleneck_id SET NOT NULL;
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	191
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	192 -- The change also effects the geoserver bottlenecks view, which
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	193 -- joined in fairway_availability. We leave the rather fuzzy match
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	194 -- (not using any validity time match), as it is unclear what the
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	195 -- validity period of fwa data is.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	196 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	197 SELECT
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	198 b.id,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	199 b.bottleneck_id,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	200 b.objnam,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	201 b.nobjnm,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	202 b.stretch,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	203 b.area,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	204 b.rb,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	205 b.lb,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	206 b.responsible_country,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	207 b.revisiting_time,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	208 b.limiting,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	209 b.date_info,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	210 b.source_organization,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	211 g.location AS gauge_isrs_code,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	212 g.objname AS gauge_objname,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	213 g.reference_water_levels,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	214 fal.date_info AS fa_date_info,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	215 fal.critical AS fa_critical,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	216 g.gm_measuredate,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	217 g.gm_waterlevel,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	218 g.gm_n_14d,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	219 srl.date_max,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	220 g.forecast_accuracy_3d,
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	221 g.forecast_accuracy_1d
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	222 FROM waterway.bottlenecks b
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	223 LEFT JOIN waterway.gauges_base_view g
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	224 ON b.gauge_location = g.location AND g.validity @> current_timestamp
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	225 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	226 bottleneck_id, date_info, critical
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	227 FROM waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	228 ORDER BY bottleneck_id, date_info DESC) AS fal
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	229 ON b.bottleneck_id = fal.bottleneck_id
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	230 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	231 bottleneck_id, max(date_info) AS date_max
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	232 FROM waterway.sounding_results
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	233 GROUP BY bottleneck_id
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	234 ORDER BY bottleneck_id DESC) AS srl
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	235 ON b.bottleneck_id = srl.bottleneck_id
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	236 WHERE b.validity @> current_timestamp;
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	237
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	238 -- Finally dropt the old column
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	239 ALTER TABLE waterway.fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	240 DROP COLUMN old_bnid;
4092 30a567b1cc2e Re-added uniq constraint after waterway.fairway_availability update. Sascha Wilde <wilde@intevation.de> parents: 4085 diff changeset	241 ALTER TABLE waterway.fairway_availability
30a567b1cc2e Re-added uniq constraint after waterway.fairway_availability update. Sascha Wilde <wilde@intevation.de> parents: 4085 diff changeset	242 ADD CONSTRAINT fairway_availability_bottleneck_id_surdat_key
30a567b1cc2e Re-added uniq constraint after waterway.fairway_availability update. Sascha Wilde <wilde@intevation.de> parents: 4085 diff changeset	243 UNIQUE (bottleneck_id, surdat);
4067 0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	244 \else
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	245 \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.'
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	246 \endif
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	247
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	248
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema. Sascha Wilde <wilde@intevation.de> parents: 4047 diff changeset	249 --
4047 8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	250 -- DROP NO LONGER NEEDED TRIGGER FUNCTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	251 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	252
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	253 -- This used to update foreign key references. As these references no
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	254 -- longer exist we dont need this magic any more...
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	255 DROP TRIGGER IF EXISTS move_referencing ON waterway.bottlenecks;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema. Sascha Wilde <wilde@intevation.de> parents: diff changeset	256 DROP FUNCTION IF EXISTS move_bottleneck_referencing();

Mercurial > gemma

annotate schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 5361:ce1fe22bda5a extented-report