Mercurial > gemma
view schema/updates/1400/01.add_fairway_marks.sql @ 5420:851c14d57680 marking-single-beam
Merged default into marking-single-beam branch.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Thu, 08 Jul 2021 00:14:58 +0200 |
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;