Mercurial > gemma
annotate schema/gemma_tests.sql @ 3701:519c6e01607f
client: available fairway depth diagram: show date labels below bar charts like in other diagram
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Wed, 19 Jun 2019 11:34:43 +0200 |
parents | db87f34805fb |
children | b785b6bef578 |
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 |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 SELECT throws_ok($$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test'), |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test') |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 $$, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 23505, NULL, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 '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
|
25 |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 SELECT throws_ok($$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 INSERT INTO waterway.waterway_area (area) VALUES |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 (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
|
29 (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
|
30 $$, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 23505, NULL, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 'No duplicate geometries can be inserted into waterway_area'); |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
34 START TRANSACTION; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
35 CREATE TEMP TABLE new_v (v) AS |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
36 SELECT tstzrange(current_timestamp - '2 d'::interval, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
37 current_timestamp - '12 h'::interval); |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
38 INSERT INTO waterway.gauges ( |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
39 location, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
40 validity, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
41 objname, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
42 geom, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
43 zero_point, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
44 date_info, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
45 source_organization, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
46 lastupdate, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
47 erased) |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
48 VALUES ( |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
49 ('AT', 'XXX', '00001', 'G0001', 1)::isrs, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
50 (SELECT v FROM new_v), |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
51 'testgauge', |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
52 ST_geomfromtext('POINT(0 0)', 4326), |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
53 0, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
54 current_timestamp, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
55 'testorganization', |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
56 current_timestamp, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
57 true); |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
58 -- Fix validity of old entry to match exclusion constraint |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
59 UPDATE waterway.gauges SET |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
60 validity = validity - (SELECT v FROM new_v) |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
61 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
62 AND validity && (SELECT v FROM new_v) |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
63 AND NOT erased; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
64 COMMIT; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
65 SELECT results_eq($$ |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
66 SELECT count(*) FROM waterway.bottlenecks GROUP BY bottleneck_id; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
2781
diff
changeset
|
67 $$, |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
68 CAST(ARRAY[2,2] AS bigint[]), |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
69 'Bottlenecks have been split to two new matching gauge versions'); |