# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1581691770 -3600
# Node ID 6081cbe71b81f011b22f0f2e405bde068a4b8192
# Parent  271616eff8e356401782d1044054d9c672d750cc
Add initial schema update script for fairway marks

diff -r 271616eff8e3 -r 6081cbe71b81 schema/updates/1400/01.add_fairway_marks.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1400/01.add_fairway_marks.sql	Fri Feb 14 15:49:30 2020 +0100
@@ -0,0 +1,81 @@
+CREATE TABLE waterway.fairway_marks (
+    geom geography(POINT, 4326) NOT NULL,
+    datsta varchar,
+    datend varchar,
+    persta varchar,
+    perend varchar,
+    objnam varchar,
+    nobjnm varchar,
+    inform varchar,
+    ninfom varchar,
+    scamin int,
+    picrep varchar,
+    txtdsc varchar,
+    sordat varchar,
+    sorind varchar
+);
+
+CREATE TABLE waterway.fairway_marks_bcnlat (
+    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+    colour varchar,
+    colpat varchar,
+    condtn int,
+    bcnshp int,
+    catlam int
+) INHERITS (waterway.fairway_marks);
+CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows
+    ON waterway.fairway_marks_bcnlat
+    ((CAST((geom,
+            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+            scamin, picrep, txtdsc, sordat, sorind,
+            0, colour, colpat, condtn, bcnshp, catlam
+        ) AS waterway.fairway_marks_bcnlat)
+    ));
+
+CREATE TABLE waterway.fairway_marks_bcnlat_dirimps (
+    fm_bcnlat_id int REFERENCES waterway.fairway_marks_bcnlat,
+    dirimp smallint REFERENCES dirimps,
+    PRIMARY KEY (fm_bcnlat_id, dirimp)
+);
+
+
+CREATE TABLE waterway.fairway_marks_boycar (
+    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+    colour varchar,
+    colpat varchar,
+    conrad int,
+    marsys int,
+    boyshp int,
+    catcam int
+) INHERITS (waterway.fairway_marks);
+CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows
+    ON waterway.fairway_marks_boycar
+    ((CAST((geom,
+            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+            scamin, picrep, txtdsc, sordat, sorind,
+            0, colour, colpat, conrad, marsys, boyshp, catcam
+        ) AS waterway.fairway_marks_boycar)
+    ));
+
+CREATE TABLE waterway.fairway_marks_boylat (
+    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+    colour varchar,
+    colpat varchar,
+    conrad int,
+    marsys int,
+    boyshp int,
+    catlam int
+) INHERITS (waterway.fairway_marks);
+CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows
+    ON waterway.fairway_marks_boylat
+    ((CAST((geom,
+            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
+            scamin, picrep, txtdsc, sordat, sorind,
+            0, colour, colpat, conrad, marsys, boyshp, catlam
+        ) AS waterway.fairway_marks_boylat)
+    ));
+
+
+GRANT SELECT on ALL tables in schema waterway TO waterway_user ;
+GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
+    TO waterway_admin;
diff -r 271616eff8e3 -r 6081cbe71b81 schema/version.sql
--- a/schema/version.sql	Fri Feb 14 14:33:42 2020 +0100
+++ b/schema/version.sql	Fri Feb 14 15:49:30 2020 +0100
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1317);
+INSERT INTO gemma_schema_version(version) VALUES (1400);