Mercurial > gemma
view schema/updates/1403/02.add_fairway_mark_types.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 | b3b2ba09a450 |
children |
line wrap: on
line source
CREATE TABLE waterway.fairway_marks_boysaw ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, conrad int, marsys int, boyshp int ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows ON waterway.fairway_marks_boysaw ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp ) AS waterway.fairway_marks_boysaw) )); CREATE TABLE waterway.fairway_marks_boyspp ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, conrad int, marsys int, boyshp int, catspm varchar ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows ON waterway.fairway_marks_boyspp ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, conrad, marsys, boyshp, catspm ) AS waterway.fairway_marks_boyspp) )); CREATE TABLE waterway.fairway_marks_daymar ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, condtn int, topshp int, orient double precision ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_daymar_distinct_rows ON waterway.fairway_marks_daymar ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, topshp, orient ) AS waterway.fairway_marks_daymar) )); CREATE TABLE waterway.fairway_marks_daymar_dirimps ( fm_daymar_id int REFERENCES waterway.fairway_marks_daymar, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_daymar_id, dirimp) ); CREATE TABLE waterway.fairway_marks_lights ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, condtn int, orient double precision, catlit varchar, exclit int, litchr int, litvis varchar, mltylt int, sectr1 double precision, sectr2 double precision, siggrp varchar, sigper double precision, sigseq varchar, status varchar ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows ON waterway.fairway_marks_lights ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, condtn, orient, catlit, exclit, litchr, litvis, mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status ) AS waterway.fairway_marks_lights) )); CREATE TABLE waterway.fairway_marks_rtpbcn ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, condtn int, siggrp varchar, catrtb int, radwal varchar ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows ON waterway.fairway_marks_rtpbcn ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, siggrp, catrtb, radwal ) AS waterway.fairway_marks_rtpbcn) )); CREATE TABLE waterway.fairway_marks_topmar ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, colour varchar, colpat varchar, condtn int, topshp int ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows ON waterway.fairway_marks_topmar ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, colour, colpat, condtn, topshp ) AS waterway.fairway_marks_topmar) )); CREATE TABLE waterway.fairway_marks_notmrk ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, condtn int, marsys int, orient double precision, status varchar, addmrk varchar, catnmk int, disipd double precision, disipu double precision, disbk1 double precision, disbk2 double precision, fnctnm int, bnkwtw int ) INHERITS (waterway.fairway_marks); CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows ON waterway.fairway_marks_notmrk ((CAST((geom, datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, scamin, picrep, txtdsc, sordat, sorind, 0, condtn, marsys, orient, status, addmrk, catnmk, disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw ) AS waterway.fairway_marks_notmrk) )); CREATE TABLE waterway.fairway_marks_notmrk_dirimps ( fm_notmrk_id int REFERENCES waterway.fairway_marks_notmrk, dirimp smallint REFERENCES dirimps, PRIMARY KEY (fm_notmrk_id, dirimp) ); GRANT SELECT on ALL tables in schema waterway TO waterway_user ; GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway TO waterway_admin;