Mercurial > gemma

diff schema/gemma.sql @ 4962:1b309a8e7673 fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Distinguish more clearly between BCNLAT HYDRO and IENC features This allows importing them from different sources while keeping the history of data intact. Additionally, storing them in different tables also allows to have different attributes (here only dirimp via an m:n-table) and different constraints (currently not implemented) according to the IENC feature catalogue. Since both new tables inherit from a table with the same name as the old table, all entries still can be accessed via a table of the same name. Thus, no changes to GeoServer layers are necessary. ToDo: solve layout problems in the client SPA.
author Tom Gottfried <tom@intevation.de>
date Thu, 27 Feb 2020 21:05:09 +0100
parents 7cc79c65a9e5
children 58dc06e91c39
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Feb 26 19:28:21 2020 +0100
+++ b/schema/gemma.sql	Thu Feb 27 21:05:09 2020 +0100
@@ -879,26 +879,43 @@
 
     -- Additional attributes for IENC features BCNLAT/bcnlat
     CREATE TABLE fairway_marks_bcnlat (
-        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         colour varchar,
         colpat varchar,
         condtn int,
         bcnshp int,
         catlam int
     ) INHERITS (fairway_marks)
+
+    CREATE TABLE fairway_marks_bcnlat_hydro (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
+    ) INHERITS (fairway_marks_bcnlat)
     -- Prevent identical entries using composite type comparison
     -- (i.e. considering two NULL values in a field equal):
-    CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows
+    CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
         ON fairway_marks_bcnlat
         ((CAST((validity, last_found, geom,
                 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                 scamin, picrep, txtdsc, sordat, sorind,
-                0, colour, colpat, condtn, bcnshp, catlam
-            ) AS fairway_marks_bcnlat)
+                colour, colpat, condtn, bcnshp, catlam, 0
+            ) AS fairway_marks_bcnlat_hydro)
+        ))
+
+    CREATE TABLE fairway_marks_bcnlat_ienc (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
+    ) INHERITS (fairway_marks_bcnlat)
+    -- Prevent identical entries using composite type comparison
+    -- (i.e. considering two NULL values in a field equal):
+    CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
+        ON fairway_marks_bcnlat
+        ((CAST((validity, last_found, geom,
+                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+                scamin, picrep, txtdsc, sordat, sorind,
+                colour, colpat, condtn, bcnshp, catlam, 0
+            ) AS fairway_marks_bcnlat_ienc)
         ))
 
     CREATE TABLE fairway_marks_bcnlat_dirimps (
-        fm_bcnlat_id int REFERENCES fairway_marks_bcnlat,
+        fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
         dirimp smallint REFERENCES dirimps,
         PRIMARY KEY (fm_bcnlat_id, dirimp)
     )