gemma: schema/tap_tests_data.sql annotate

annotate schema/tap_tests_data.sql @ 1873:9f8f7d3fd655

Fix policies interfering badly with integrity checks The 'parent_allowed' policies did not allow concurrent inserts into parent and child table in one statement (like in the tests introduced here) nor would they have allowed deferring foreign keys in transactions.

author	Tom Gottfried <tom@intevation.de>
date	Thu, 17 Jan 2019 19:22:49 +0100
parents	9d51f022b8ee
children	931b15be6d7f

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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: 580 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: 580 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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: 580 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: 580 diff changeset	6
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	7 -- Copyright (C) 2018 by via donau
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 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>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 580 diff changeset	14
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	15 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	16 -- Test data used in *_tests.sql scripts
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	17 --
109 0e486e6ee60d Set search path in test data script for standalone use. Tom Gottfried <tom@intevation.de> parents: 108 diff changeset	18
177 4e2451d561b1 Make schema for templates more realistic Tom Gottfried <tom@intevation.de> parents: 136 diff changeset	19 INSERT INTO countries VALUES ('AT'), ('RO');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	20
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	21 INSERT INTO users.responsibility_areas VALUES
211 e7826710d9c4 Make responsibility area mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	22 ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
e7826710d9c4 Make responsibility area mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	23 ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	24
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	25 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	26 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	27 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	28 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	29 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	30 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	31 INSERT INTO users.list_users VALUES (
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1629 diff changeset	32 'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx');
9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1629 diff changeset	33 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	34 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	35
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 567 diff changeset	36 INSERT INTO limiting_factors VALUES ('depth'), ('width');
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	37
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	38 INSERT INTO waterway.gauges (
580 e78bdbb6cac8 Remove worthless attribute Tom Gottfried <tom@intevation.de> parents: 579 diff changeset	39 location, objname, geom, zero_point, source_organization)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	40 VALUES (
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	41 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	42 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	43 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	44 0,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	45 'testorganization'
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	46 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	47
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	48 INSERT INTO waterway.bottlenecks (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	49 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	50 revisiting_time, limiting, source_organization, staging_done)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	51 VALUES (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	52 'testbottleneck1',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	53 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	54 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	55 ('AT', 'XXX', '00001', '00000', 2)::isrs),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	56 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	58 1, 'depth', 'testorganization', false
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	59 ), (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	60 'testbottleneck2',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	61 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	62 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	63 ('AT', 'XXX', '00001', '00000', 2)::isrs),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	64 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	66 1, 'depth', 'testorganization', true
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	67 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	68
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	69 INSERT INTO waterway.distance_marks_virtual VALUES (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	70 ('AT', 'XXX', '00001', '00000', 0)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	71 ST_SetSRID('POINT(0 0)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	72 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	73 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	74 ('AT', 'XXX', '00001', '00000', 1)::isrs,
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	75 ST_SetSRID('POINT(1 0)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	76 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	77 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	78
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	79 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	80 ST_SetSRID(ST_CurveToLine(
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	81 'CIRCULARSTRING(0 0, 0.5 0.5, 1 0, 1.5 -0.2, 2 0)'::geometry),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	82 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	83 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	84 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	85 ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	86 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	87 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	88
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	89 INSERT INTO users.templates (template_name, template_data)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	90 VALUES ('AT', '\x'), ('RO', '\x');
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	91 INSERT INTO users.user_templates
224 57dfab80973c By convention, prefix all test users with 'test' Tom Gottfried <tom@intevation.de> parents: 211 diff changeset	92 VALUES ('test_user_at', 'AT'), ('test_user_ro', 'RO');

Mercurial > gemma

annotate schema/tap_tests_data.sql @ 1873:9f8f7d3fd655