Mercurial > gemma
view schema/updates/1400/01.add_fairway_marks.sql @ 5677:a57be8bfb6ea sr-v2
More suited file names.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Sun, 11 Feb 2024 09:51:21 +0100 |
parents | 6081cbe71b81 |
children |
line wrap: on
line source
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;