Mercurial > gemma

changeset 345:b97b3172c61a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add staging feature to more tables Added tables currently only have limited visibility for waterway_user but not yet policies for write access.
author Tom Gottfried <tom@intevation.de>
date Mon, 06 Aug 2018 15:19:05 +0200
parents e98033e3683a
children ad0e47c1fedf
files schema/auth.sql schema/gemma.sql
diffstat 2 files changed, 17 insertions(+), 13 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Mon Aug 06 14:52:04 2018 +0200
+++ b/schema/auth.sql	Mon Aug 06 15:19:05 2018 +0200
@@ -54,14 +54,17 @@
 --
 
 -- Staging area
-CREATE FUNCTION create_hide_staging_policy()
-    RETURNS void
-AS $$
+DO LANGUAGE plpgsql
+$$
 DECLARE the_table varchar;
 BEGIN
     FOREACH the_table IN ARRAY ARRAY[
-        'bottlenecks', 'sounding_results']
-    -- TODO: add all relevant tables here
+        'gauge_measurements',
+        'sections_stretches',
+        'waterway_profiles',
+        'fairway_dimensions',
+        'bottlenecks',
+        'sounding_results']
     LOOP
         EXECUTE format('CREATE POLICY hide_staging ON waterway.%I '
             'FOR SELECT TO waterway_user USING (staging_done)', the_table);
@@ -69,10 +72,7 @@
             the_table);
     END LOOP;
 END;
-$$
-LANGUAGE plpgsql;
-SELECT create_hide_staging_policy();
-DROP FUNCTION create_hide_staging_policy;
+$$;
 
 CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
     USING (username IN(SELECT username FROM users.list_users));
--- a/schema/gemma.sql	Mon Aug 06 14:52:04 2018 +0200
+++ b/schema/gemma.sql	Mon Aug 06 15:19:05 2018 +0200
@@ -249,7 +249,8 @@
         value_max double precision, -- XXX: NOT NULL if predicted?
         --- TODO: Add a double range type for checking?
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL -- "originator"
+        source_organization varchar NOT NULL, -- "originator"
+        staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER gauge_measurements_date_info
         BEFORE UPDATE ON gauge_measurements
@@ -282,7 +283,8 @@
         objnam varchar NOT NULL,
         nobjnam varchar,
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER sections_stretches_date_info
         BEFORE UPDATE ON sections_stretches
@@ -302,7 +304,8 @@
         CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
             OR validity IS NOT NULL),
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER waterway_profiles_date_info
         BEFORE UPDATE ON waterway_profiles
@@ -315,7 +318,8 @@
         max_width smallint NOT NULL,
         min_depth smallint NOT NULL,
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER fairway_dimensions_date_info
         BEFORE UPDATE ON fairway_dimensions