Mercurial > gemma

annotate schema/tap_tests_data.sql @ 195:5dc8e734487a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce database schemas as privilege-based namespaces Some privileges changed (e.g. for responsibility_areas), but additional privileges were not left off intentionally before. Search path settings have been replaced by schema-qualifying names in statements to prevent object definitions from being dependend on search path settings.
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Jul 2018 17:28:16 +0200
parents 4df4e4bf480e
children 88d21c29cf04
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
1 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
2 -- Test data used in *_tests.sql scripts
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
3 --
109
0e486e6ee60d Set search path in test data script for standalone use.
Tom Gottfried <tom@intevation.de>
parents: 108
diff changeset
4
177
4e2451d561b1 Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents: 136
diff changeset
5 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
6
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
7 INSERT INTO users.responsibility_areas VALUES
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
8 ('AT', 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
9
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
10 INSERT INTO users.user_profiles (username, country, email_adress)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
11 VALUES
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
12 ('waterway_user', 'AT', 'xxx'),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
13 ('waterway_user2', 'RO', 'xxy'),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
14 ('waterway_admin', 'AT', 'yyy'),
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
15 ('sys_admin', 'AT', 'zzz');
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 INSERT INTO limiting_factors VALUES ('depth'), ('width');
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
19 INSERT INTO waterway.gauges (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
20 location, function_code, objname, geom, zero_point, source_organization)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
21 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
22 ('AT', 'XXX', '00001', '00000', 1)::isrs,
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 'xxx',
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 'testgauge',
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 ST_geomfromtext('POINT(0 0)', 4326),
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 0,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
27 'testorganization'
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
28 );
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
30 INSERT INTO waterway.bottlenecks (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
31 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
32 revisiting_time, limiting, source_organization, staging_done)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
33 VALUES (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
34 'testbottleneck1',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 ('AT', 'XXX', '00001', '00000', 1)::isrs,
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
37 ('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
38 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
39 'AT', 'AT', 'AT',
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
40 1, 'depth', 'testorganization', false
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
41 ), (
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
42 'testbottleneck2',
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 ('AT', 'XXX', '00001', '00000', 1)::isrs,
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
45 ('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
46 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
47 'AT', 'AT', 'AT',
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
48 1, 'depth', 'testorganization', true
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
49 );
108
f6aba3ee3a75 Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
51 INSERT INTO users.templates (template_name, template_data)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
52 VALUES ('AT', '\x'), ('RO', '\x');
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 182
diff changeset
53 INSERT INTO users.user_templates
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
54 VALUES ('waterway_user', 'AT'), ('waterway_user2', 'RO');