Mercurial > gemma
comparison schema/tap_tests.sql @ 113:25b28fd0e256
Moved schema stuff to own subfolder.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 26 Jun 2018 16:14:59 +0200 |
| parents | tap_tests.sql@894f633e2d3f |
| children | d349db18bece |
comparison
equal
deleted
inserted
replaced
| 112:894f633e2d3f | 113:25b28fd0e256 |
|---|---|
| 1 -- | |
| 2 -- pgTAP test script | |
| 3 -- | |
| 4 BEGIN; | |
| 5 | |
| 6 CREATE EXTENSION pgtap; | |
| 7 | |
| 8 SELECT plan(10); -- Give number of tests that have to be run | |
| 9 | |
| 10 SET search_path TO public, wamos, wamos_waterway, wamos_fairway; | |
| 11 | |
| 12 \i tap_tests_data.sql | |
| 13 | |
| 14 -- | |
| 15 -- Run tests as unprivileged user | |
| 16 -- | |
| 17 SET SESSION AUTHORIZATION waterway_user; | |
| 18 | |
| 19 SELECT throws_ok('CREATE TABLE test()', 42501, NULL, | |
| 20 'No objects can be created'); | |
| 21 | |
| 22 SELECT isnt_empty('SELECT * FROM bottlenecks', | |
| 23 'Staged data should be visible'); | |
| 24 SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done', | |
| 25 'Only staged data should be visible'); | |
| 26 | |
| 27 SELECT set_eq('SELECT count(*) FROM wamos.user_profiles', ARRAY[1], | |
| 28 'User should only see his own profile'); | |
| 29 SELECT results_eq('SELECT username FROM user_profiles', | |
| 30 'SELECT CAST(current_user AS varchar)', | |
| 31 'User should only see his own profile'); | |
| 32 | |
| 33 SELECT isnt_empty('SELECT * FROM templates', | |
| 34 'User should see templates associated to him'); | |
| 35 SELECT is_empty('SELECT * FROM templates t | |
| 36 JOIN user_templates ut ON t.id = template_id | |
| 37 WHERE username <> current_user', | |
| 38 'User should only see templates associated to him'); | |
| 39 | |
| 40 -- | |
| 41 -- Run tests as waterway administrator | |
| 42 -- | |
| 43 SET SESSION AUTHORIZATION waterway_admin; | |
| 44 | |
| 45 PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS | |
| 46 INSERT INTO bottlenecks ( | |
| 47 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, | |
| 48 revisiting_time, limiting, source_organization) | |
| 49 VALUES | |
| 50 ($1, | |
| 51 ('AT', 'XXX', '00001', '00000', 1)::isrs, | |
| 52 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, | |
| 53 ('AT', 'XXX', '00001', '00000', 2)::isrs), | |
| 54 $2, 'AT', 'AT', 'AT', | |
| 55 1, 'depth', 'testorganization'); | |
| 56 -- XXX: In contrast to what table 240 in the PostgreSQL docs says | |
| 57 -- (https://www.postgresql.org/docs/10/static/sql-createpolicy.html), | |
| 58 -- the following tests seem to show that the USING expression is applied | |
| 59 -- also to an INSERT: | |
| 60 SELECT lives_ok('EXECUTE bn_insert(''test1'', | |
| 61 ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', | |
| 62 4326))', | |
| 63 'Waterway admin can insert data within his region'); | |
| 64 SELECT throws_ok('EXECUTE bn_insert(''test2'', | |
| 65 ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', | |
| 66 4326))', 42501, NULL, | |
| 67 'Waterway admin cannot insert data outside his region'); | |
| 68 | |
| 69 -- XXX: Why does this fail? POLICY manage_templates should allow to see | |
| 70 -- the template of waterway_user. | |
| 71 SELECT isnt_empty('SELECT * FROM templates t | |
| 72 JOIN user_templates ut ON t.id = template_id | |
| 73 WHERE username <> current_user', | |
| 74 'Waterway admin should see templates of users in country'); | |
| 75 | |
| 76 -- | |
| 77 -- finish tests | |
| 78 -- | |
| 79 SELECT * FROM finish(); | |
| 80 | |
| 81 ROLLBACK; |