gemma: schema/tap_tests_data.sql annotate

annotate schema/tap_tests_data.sql @ 2230:4374d942b23d

Remove debris and associate templates to country While the original idea was to associate templates to users, managed by a waterway admin per country, the new intention was to share templates between users per country. Just removing any constraints on readability and management of the templates did not fulfill this idea.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 13 Feb 2019 10:52:14 +0100
parents	b66cfcde8ff7
children	99274fed6f3d

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 (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	32 'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy');
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	33 INSERT INTO users.list_users VALUES (
1873 9f8f7d3fd655 Fix policies interfering badly with integrity checks Tom Gottfried <tom@intevation.de> parents: 1629 diff changeset	34 '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	35 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	36 '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	37
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 567 diff changeset	38 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	39
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	40 INSERT INTO waterway.gauges (
580 e78bdbb6cac8 Remove worthless attribute Tom Gottfried <tom@intevation.de> parents: 579 diff changeset	41 location, objname, geom, zero_point, source_organization)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	42 VALUES (
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	43 ('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	44 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	45 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	46 0,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	47 'testorganization'
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	48 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	49
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 182 diff changeset	50 INSERT INTO waterway.bottlenecks (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	51 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	52 revisiting_time, limiting, source_organization, staging_done)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	53 VALUES (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	54 'testbottleneck1',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	55 ('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	56 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	57 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	58 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	60 1, 'depth', 'testorganization', false
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	61 ), (
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	62 'testbottleneck2',
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	63 ('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	64 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	65 ('AT', 'XXX', '00001', '00000', 2)::isrs),
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	66 ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 'AT', 'AT', 'AT',
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	68 1, 'depth', 'testorganization', true
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	69 );
108 f6aba3ee3a75 Put test data in own file for easier debugging. Tom Gottfried <tom@intevation.de> parents: diff changeset	70
1629 9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	71 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	72 ('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	73 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	74 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	75 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	76 ('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	77 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	78 'someENC'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	79 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	80
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	81 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	82 ST_SetSRID(ST_CurveToLine(
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	83 '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	84 4326),
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	85 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	86 ), (
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	87 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	88 'testriver'
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	89 );
9d51f022b8ee Introduce SQL function to clip an area to a stretch Tom Gottfried <tom@intevation.de> parents: 1336 diff changeset	90
2230 4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	91 INSERT INTO users.templates (template_name, country, template_data)
4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2147 diff changeset	92 VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	93
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	94 WITH
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	95 job AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	96 INSERT INTO import.imports (kind, username, data) VALUES (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	97 'test', 'test_admin_ro', 'test') RETURNING id),
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	98 log AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	99 INSERT INTO import.import_logs (import_id, msg)
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	100 SELECT id, 'test' FROM job)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	101 INSERT INTO import.track_imports
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	102 SELECT id, 'waterway.bottlenecks', 1 FROM job;
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	103
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	104 WITH
931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	105 config AS (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	106 INSERT INTO import.import_configuration (kind, username) VALUES (
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	107 'test', 'test_admin_ro') RETURNING id)
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	108 INSERT INTO import.import_configuration_attributes
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	109 SELECT id, 'test key', 'test value' FROM config;

Mercurial > gemma

annotate schema/tap_tests_data.sql @ 2230:4374d942b23d