Mercurial > gemma
changeset 1894:d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 18 Jan 2019 12:00:59 +0100 |
parents | 9beff970b107 |
children | 048580ec843d |
files | schema/gemma.sql |
diffstat | 1 files changed, 18 insertions(+), 6 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Fri Jan 18 11:26:55 2019 +0100 +++ b/schema/gemma.sql Fri Jan 18 12:00:59 2019 +0100 @@ -347,18 +347,30 @@ check (pk_policy in ('sequence', 'assigned', 'autogenerated')) ) - CREATE TABLE sections_stretches ( - id varchar PRIMARY KEY, - is_section boolean NOT NULL, -- maps 'function' from interface - stretch isrsrange, + CREATE TABLE stretches ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + name varchar NOT NULL, + stretch isrsrange NOT NULL, + -- TODO: make it a (MULTI)-LINESTRING. + -- POLYGON is chosen for the convinience + -- of re-using ISRSrange_area. + geom geography(POLYGON, 4326) NOT NULL, objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, - staging_done boolean NOT NULL DEFAULT false + staging_done boolean NOT NULL DEFAULT false, + UNIQUE(name, staging_done) ) + + CREATE TABLE stretch_countries ( + stretches_id int NOT NULL REFERENCES stretches(id), + country_code char(2) NOT NULL REFERENCES countries(country_code), + UNIQUE(stretches_id, country_code) + ) + CREATE TRIGGER sections_stretches_date_info - BEFORE UPDATE ON sections_stretches + BEFORE UPDATE ON stretches FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE waterway_profiles (