Mercurial > gemma

comparison schema/gemma_tests.sql @ 3665:29ef6d41e4af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use database triggers to move referencing objects to new versions Needs fewer database round-trips and is more convenient especially if more than two levels in the object hierarchy have to be handled.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 09:24:28 +0200
parents a2127495093e
children db87f34805fb
comparison
equal deleted inserted replaced
3664:58508f50d192 3665:29ef6d41e4af
29 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')) 29 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'))
30 $$, 30 $$,
31 23505, NULL, 31 23505, NULL,
32 'No duplicate geometries can be inserted into waterway_area'); 32 'No duplicate geometries can be inserted into waterway_area');
33 33
34 START TRANSACTION;
35 CREATE TEMP TABLE new_v (v) AS
36 SELECT tstzrange(current_timestamp - '2 d'::interval,
37 current_timestamp - '12 h'::interval);
38 INSERT INTO waterway.gauges (
39 location,
40 validity,
41 objname,
42 geom,
43 zero_point,
44 date_info,
45 source_organization,
46 lastupdate,
47 erased)
48 VALUES (
49 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
50 (SELECT v FROM new_v),
51 'testgauge',
52 ST_geomfromtext('POINT(0 0)', 4326),
53 0,
54 current_timestamp,
55 'testorganization',
56 current_timestamp,
57 true);
58 -- Fix validity of old entry to match exclusion constraint
59 UPDATE waterway.gauges SET
60 validity = validity - (SELECT v FROM new_v)
61 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
62 AND validity && (SELECT v FROM new_v)
63 AND NOT erased;
64 COMMIT;
65 SELECT results_eq($$
66 SELECT DISTINCT gauge_validity FROM waterway.bottlenecks
67 $$,
68 $$
69 SELECT v FROM new_v
70 $$,
71 'Bottlenecks have been associated to new matching gauge version');