Mercurial > gemma

changeset 1636:37ee25bc2bbe

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated database schema for gauge meaurement imports.
author Raimund Renkert <raimund.renkert@intevation.de>
date Thu, 20 Dec 2018 12:05:30 +0100
parents ca48145dba9c
children dd31be75ce6d
files schema/auth.sql schema/gemma.sql
diffstat 2 files changed, 10 insertions(+), 6 deletions(-) [+]
line wrap: on
line diff
--- a/schema/auth.sql	Thu Dec 20 11:38:49 2018 +0100
+++ b/schema/auth.sql	Thu Dec 20 12:05:30 2018 +0100
@@ -77,7 +77,8 @@
 DECLARE the_table varchar;
 BEGIN
     FOREACH the_table IN ARRAY ARRAY[
-        'gauge_measurements',
+       -- 'gauge_measurements', XXX Removed since this table has currently no
+    -- staging
         'sections_stretches',
         'waterway_profiles',
         'fairway_dimensions',
--- a/schema/gemma.sql	Thu Dec 20 11:38:49 2018 +0100
+++ b/schema/gemma.sql	Thu Dec 20 12:05:30 2018 +0100
@@ -261,11 +261,11 @@
     )
 
     CREATE TABLE gauge_measurements (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         fk_gauge_id isrs NOT NULL REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
-        PRIMARY KEY (fk_gauge_id, measure_date),
-        -- XXX: Is country_code really relevant for GEMMA or just NtS?
-        -- country_code char(2) NOT NULL REFERENCES countries,
+        -- PRIMARY KEY (fk_gauge_id, measure_date),
+        country_code char(2) NOT NULL REFERENCES countries,
         -- TODO: add relations to stuff provided as enumerations
         sender varchar NOT NULL, -- "from" attribute from DRC
         language_code varchar NOT NULL REFERENCES language_codes,
@@ -281,8 +281,11 @@
         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"
-        staging_done boolean NOT NULL DEFAULT false
+        source_organization varchar NOT NULL -- "originator"
+        -- XXX removed staging done temporarily. Currently imported raw data is
+        -- not staged. When importing approved gauge measurements uncomment this
+        -- and add policy to allow select on this table for waterway_admin
+        -- staging_done boolean NOT NULL DEFAULT false
     )
     CREATE TRIGGER gauge_measurements_date_info
         BEFORE UPDATE ON gauge_measurements