Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
4961:67d78b74fe43 4962:1b309a8e7673
877 sorind varchar 877 sorind varchar
878 ) 878 )
879 879
880 -- Additional attributes for IENC features BCNLAT/bcnlat 880 -- Additional attributes for IENC features BCNLAT/bcnlat
881 CREATE TABLE fairway_marks_bcnlat ( 881 CREATE TABLE fairway_marks_bcnlat (
882 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
883 colour varchar, 882 colour varchar,
884 colpat varchar, 883 colpat varchar,
885 condtn int, 884 condtn int,
886 bcnshp int, 885 bcnshp int,
887 catlam int 886 catlam int
888 ) INHERITS (fairway_marks) 887 ) INHERITS (fairway_marks)
888
889 CREATE TABLE fairway_marks_bcnlat_hydro (
890 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
891 ) INHERITS (fairway_marks_bcnlat)
889 -- Prevent identical entries using composite type comparison 892 -- Prevent identical entries using composite type comparison
890 -- (i.e. considering two NULL values in a field equal): 893 -- (i.e. considering two NULL values in a field equal):
891 CREATE UNIQUE INDEX fairway_marks_bcnlat_distinct_rows 894 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
892 ON fairway_marks_bcnlat 895 ON fairway_marks_bcnlat
893 ((CAST((validity, last_found, geom, 896 ((CAST((validity, last_found, geom,
894 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, 897 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
895 scamin, picrep, txtdsc, sordat, sorind, 898 scamin, picrep, txtdsc, sordat, sorind,
896 0, colour, colpat, condtn, bcnshp, catlam 899 colour, colpat, condtn, bcnshp, catlam, 0
897 ) AS fairway_marks_bcnlat) 900 ) AS fairway_marks_bcnlat_hydro)
898 )) 901 ))
899 902
903 CREATE TABLE fairway_marks_bcnlat_ienc (
904 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
905 ) INHERITS (fairway_marks_bcnlat)
906 -- Prevent identical entries using composite type comparison
907 -- (i.e. considering two NULL values in a field equal):
908 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
909 ON fairway_marks_bcnlat
910 ((CAST((validity, last_found, geom,
911 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
912 scamin, picrep, txtdsc, sordat, sorind,
913 colour, colpat, condtn, bcnshp, catlam, 0
914 ) AS fairway_marks_bcnlat_ienc)
915 ))
916
900 CREATE TABLE fairway_marks_bcnlat_dirimps ( 917 CREATE TABLE fairway_marks_bcnlat_dirimps (
901 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat, 918 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
902 dirimp smallint REFERENCES dirimps, 919 dirimp smallint REFERENCES dirimps,
903 PRIMARY KEY (fm_bcnlat_id, dirimp) 920 PRIMARY KEY (fm_bcnlat_id, dirimp)
904 ) 921 )
905 922
906 -- Additional attributes for IENC feature BOYCAR 923 -- Additional attributes for IENC feature BOYCAR