Mercurial > gemma
annotate schema/tap_tests_data.sql @ 2053:c06a2e5397fc unify_imports
Scheduler: Adjusted booting of scheduled imports to new database schema.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 27 Jan 2019 23:19:30 +0100 |
parents | 59055c8301df |
children | b66cfcde8ff7 |
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 | 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 | 47 'testorganization' |
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 | 51 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
52 revisiting_time, limiting, source_organization, staging_done) | |
53 VALUES ( | |
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 | 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 | 60 1, 'depth', 'testorganization', false |
61 ), ( | |
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 | 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 | 68 1, 'depth', 'testorganization', true |
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 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
91 INSERT INTO users.templates (template_name, template_data) |
182 | 92 VALUES ('AT', '\x'), ('RO', '\x'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
93 INSERT INTO users.user_templates |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
211
diff
changeset
|
94 VALUES ('test_user_at', 'AT'), ('test_user_ro', 'RO'); |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
95 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
96 WITH |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
97 job AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
98 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
|
99 'test', 'test_admin_ro', 'test') RETURNING id), |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
100 log AS ( |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
101 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
|
102 SELECT id, 'test' FROM job) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
103 INSERT INTO import.track_imports |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
104 SELECT id, 'waterway.bottlenecks', 1 FROM job; |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
105 |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
106 WITH |
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
107 config AS ( |
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 (kind, username) VALUES ( |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
109 '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
|
110 INSERT INTO import.import_configuration_attributes |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
111 SELECT id, 'test key', 'test value' FROM config; |