# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1531839016 -7200
# Node ID 4e2451d561b1a1cae0a8b37fc32ae87172f74dff
# Parent  0c08260635616051e1aa284a22642d1bb5eee8af
Make schema for templates more realistic

A print template should be stored with name and timestamp and
the template itself will be any data to be interpreted as
a template file by a templating engine.
Delete relations in user_templates with their parents using CASCADE,
because user_templates is only an m:n-table.

diff -r 0c0826063561 -r 4e2451d561b1 schema/auth.sql
--- a/schema/auth.sql	Tue Jul 17 12:01:14 2018 +0200
+++ b/schema/auth.sql	Tue Jul 17 16:50:16 2018 +0200
@@ -57,7 +57,7 @@
 ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
 
 CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
-       USING (id IN(SELECT template_id FROM user_templates
+       USING (template_name IN(SELECT template_name FROM user_templates
                     WHERE username = current_user));
 ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
 
@@ -87,7 +87,7 @@
        USING (country = current_user_country());
 
 CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
-       USING (id IN(SELECT template_id FROM user_templates ut
+       USING (template_name IN(SELECT template_name FROM user_templates ut
                     JOIN user_profiles p ON ut.username = p.username
                     WHERE p.country = current_user_country()));
 
diff -r 0c0826063561 -r 4e2451d561b1 schema/gemma.sql
--- a/schema/gemma.sql	Tue Jul 17 12:01:14 2018 +0200
+++ b/schema/gemma.sql	Tue Jul 17 16:50:16 2018 +0200
@@ -76,16 +76,17 @@
        );
 
 CREATE TABLE gemma.templates (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       dummy_attrib varchar,
-       "..." varchar
-       -- TODO: template attributes tbd.
+       template_name varchar PRIMARY KEY,
+       template_data bytea NOT NULL,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
        );
+CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE gemma.user_templates (
-       username varchar NOT NULL REFERENCES user_profiles,
-       template_id int NOT NULL REFERENCES templates,
-       PRIMARY KEY (username, template_id)
+       username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
+       template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
+       PRIMARY KEY (username, template_name)
        );
 
 --
diff -r 0c0826063561 -r 4e2451d561b1 schema/tap_tests.sql
--- a/schema/tap_tests.sql	Tue Jul 17 12:01:14 2018 +0200
+++ b/schema/tap_tests.sql	Tue Jul 17 16:50:16 2018 +0200
@@ -32,8 +32,8 @@
 
 SELECT isnt_empty('SELECT * FROM templates',
                   'User should see templates associated to him');
-SELECT is_empty('SELECT * FROM templates t
-                 JOIN user_templates ut ON t.id = template_id
+SELECT is_empty('SELECT * FROM templates
+                 JOIN user_templates USING (template_name)
                  WHERE username <> current_user',
                 'User should only see templates associated to him');
 
@@ -66,8 +66,9 @@
                       4326))', 42501, NULL,
                  'Waterway admin cannot insert data outside his region');
 
-SELECT isnt_empty('SELECT * FROM templates t
-                   JOIN user_templates ut ON t.id = template_id
+-- template management
+SELECT isnt_empty('SELECT * FROM templates
+                   JOIN user_templates USING (template_name)
                    WHERE username <> current_user',
                   'Waterway admin should see templates of users in country');
 
diff -r 0c0826063561 -r 4e2451d561b1 schema/tap_tests_data.sql
--- a/schema/tap_tests_data.sql	Tue Jul 17 12:01:14 2018 +0200
+++ b/schema/tap_tests_data.sql	Tue Jul 17 16:50:16 2018 +0200
@@ -1,6 +1,6 @@
 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
-INSERT INTO countries VALUES ('AT');
+INSERT INTO countries VALUES ('AT'), ('RO');
 
 INSERT INTO responsibility_areas VALUES
        ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326));
@@ -8,6 +8,7 @@
 INSERT INTO user_profiles (username, country, email_adress)
        VALUES
        ('waterway_user', 'AT', 'xxx'),
+       ('waterway_user2', 'RO', 'xxy'),
        ('waterway_admin', 'AT', 'yyy'),
        ('sys_admin', 'AT', 'zzz');
 
@@ -42,8 +43,7 @@
         'AT', 'AT', 'AT',
         1, 'depth', 'testorganization', true);
 
-INSERT INTO templates (dummy_attrib)
-       VALUES ('usertemplate'), ('othertemplate');
+INSERT INTO templates (template_name, template_data)
+       VALUES ('AT', '\x'), ('RO', '\x');
 INSERT INTO user_templates
-       SELECT 'waterway_user', id FROM templates
-              WHERE dummy_attrib = 'usertemplate';
+       VALUES ('waterway_user', 'AT'), ('waterway_user2', 'RO');