gemma: schema/gemma_tests.sql annotate

annotate schema/gemma_tests.sql @ 5095:e21cbb9768a2

Prevent duplicate fairway areas In principal, there can be only one or no fairway area at each point on the map. Since polygons from real data will often be topologically inexact, just disallow equal geometries. This will also help to avoid importing duplicates with concurrent imports, once the history of fairway dimensions will be preserved.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 25 Mar 2020 18:10:02 +0100
parents	cf25b23e3eec
children	722b7c305319

rev	line source
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	2 -- without warranty, see README.md and license for details.
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	3
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	6
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	7 -- Copyright (C) 2019 by via donau
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	9 -- Software engineering by Intevation GmbH
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	10
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	11 -- Author(s):
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	13
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	14 --
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	15 -- pgTAP test script for gemma schema definition
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	16 --
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	17
4602 9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	18 SELECT ok(is_valid_from_item('SELECT * FROM sys_admin.published_services'),
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	19 'Valid statement passes check');
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	20
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	21 SELECT ok(NOT is_valid_from_item('This is not SQL'),
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	22 'Arbitrary text does not pass check');
9fef9930aa8a Add field for GeoServer SQL view definition Tom Gottfried <tom@intevation.de> parents: 4118 diff changeset	23
4603 d24e951206ca Do not try to execute NULL as a subquery Tom Gottfried <tom@intevation.de> parents: 4602 diff changeset	24 SELECT ok(is_valid_from_item(NULL) IS NULL,
d24e951206ca Do not try to execute NULL as a subquery Tom Gottfried <tom@intevation.de> parents: 4602 diff changeset	25 'NULL value is not checked');
d24e951206ca Do not try to execute NULL as a subquery Tom Gottfried <tom@intevation.de> parents: 4602 diff changeset	26
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	27 SELECT throws_ok($$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	28 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES
5009 e8b2dc771f9e Store axis as MultiLinestring Tom Gottfried <tom@intevation.de> parents: 4603 diff changeset	29 (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'),
e8b2dc771f9e Store axis as MultiLinestring Tom Gottfried <tom@intevation.de> parents: 4603 diff changeset	30 (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test')
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	31 $$,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	32 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	33 'No duplicate geometries can be inserted into waterway_axis');
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	34
5016 cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	35 SELECT lives_ok($$
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	36 INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES (
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	37 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	38 'test',
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	39 tstzrange(NULL, current_timestamp)
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	40 ), (
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	41 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	42 'test',
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	43 tstzrange(current_timestamp, NULL)
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	44 )
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	45 $$,
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	46 'Duplicate axis geometries can be inserted if validity differs');
cf25b23e3eec Keep historic data of waterway axis Tom Gottfried <tom@intevation.de> parents: 5009 diff changeset	47
2781 a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	48 SELECT throws_ok($$
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	49 INSERT INTO waterway.waterway_area (area) VALUES
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	50 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')),
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	51 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'))
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	52 $$,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	53 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries Tom Gottfried <tom@intevation.de> parents: diff changeset	54 'No duplicate geometries can be inserted into waterway_area');
5095 e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	55
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	56 SELECT throws_ok($$
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	57 INSERT INTO waterway.fairway_dimensions (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	58 area, level_of_service,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	59 min_width, max_width, min_depth, source_organization
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	60 ) VALUES (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	61 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	62 100, 200, 2, 'test'
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	63 ), (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	64 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	65 100, 200, 2, 'test'
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	66 )
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	67 $$,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	68 23505, NULL,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	69 'No duplicate geometries can be inserted into fairway_dimensions');
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	70
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	71 SELECT lives_ok($$
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	72 INSERT INTO waterway.fairway_dimensions (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	73 area, level_of_service,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	74 min_width, max_width, min_depth, source_organization, staging_done
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	75 ) VALUES (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	76 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	77 100, 200, 2, 'test', false
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	78 ), (
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	79 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	80 100, 200, 2, 'test', true
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	81 )
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	82 $$,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	83 'Duplicate fairway area can be inserted if stage_done differs');
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	84
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	85 SELECT throws_ok($$
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	86 UPDATE waterway.fairway_dimensions SET staging_done = true
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	87 $$,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	88 23505, NULL,
e21cbb9768a2 Prevent duplicate fairway areas Tom Gottfried <tom@intevation.de> parents: 5016 diff changeset	89 'No duplicate fairway area can be released from staging area');

Mercurial > gemma

annotate schema/gemma_tests.sql @ 5095:e21cbb9768a2