# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1527877521 -7200
# Node ID 765906789840d91467a386cdf296af1323814b5d
# Parent  f11366b419ae74d5742ce0d7fdc60709f23f2bcc
Add gauge attributes.

diff -r f11366b419ae -r 765906789840 wamos.sql
--- a/wamos.sql	Fri Jun 01 20:08:47 2018 +0200
+++ b/wamos.sql	Fri Jun 01 20:25:21 2018 +0200
@@ -118,16 +118,39 @@
        dirimp smallint REFERENCES dirimps
        );
 
+CREATE TABLE wamos_fairway.depth_references (
+       depth_reference varchar(4) PRIMARY KEY
+       -- See col. AB and AI RIS-Index Encoding Guide
+       -- XXX: We need a way to distinguish between geodetic (eg. col. AP
+       -- RIS-Index) and other references (e.g. col. AB and AI):
+       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
+       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
+       -- _ Do not mixup things with different meanings in one table at all
+       --   (which would mean a model differing a bit from RIS-Index ideas)
+       );
+
 CREATE TABLE wamos_fairway.reference_water_levels (
        reference_water_level varchar(20) PRIMARY KEY
        );
 
 CREATE TABLE wamos_fairway.gauges (
        location isrs PRIMARY KEY,
-       dummy_attrib varchar,
-       "..." varchar
-       -- TODO: add real gauge attributes (DRC 2.1.4)
+       function_code varchar(10) NOT NULL, -- XXX: What is this really for?
+       objname varchar NOT NULL,
+       is_left boolean, -- XXX: Or reference position_codes?
+       geom geometry(POINT, 4326) NOT NULL,
+       applicability isrsrange,
+       validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
+       -- pasted text from a more general specification is given
+       -- (a gauge is not a berth!)
+       -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
+       zero_point double precision NOT NULL,
+       geodref varchar(4) REFERENCES depth_references,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
        );
+CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE wamos_fairway.gauges_reference_water_levels (
        gauge_id isrs NOT NULL REFERENCES gauges,
@@ -250,17 +273,6 @@
        limiting_factor varchar PRIMARY KEY
        );
 
-CREATE TABLE wamos_fairway.depth_references (
-       depth_reference varchar(4) PRIMARY KEY
-       -- See col. AB and AI RIS-Index Encoding Guide
-       -- XXX: We need a way to distinguish between geodetic (eg. col. AP
-       -- RIS-Index) and other references (e.g. col. AB and AI):
-       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
-       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
-       -- _ Do not mixup things with different meanings in one table at all
-       --   (which would mean a model differing a bit from RIS-Index ideas)
-       );
-
 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)