Mercurial > gemma

changeset 66:21df0e95db4a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix thinkos and syntax errors in previous commit.
author Tom Gottfried <tom@intevation.de>
date Fri, 25 May 2018 21:54:27 +0200
parents cad19acc53b3
children d218aa96170f
files wamos.sql
diffstat 1 files changed, 5 insertions(+), 7 deletions(-) [+]
line wrap: on
line diff
--- a/wamos.sql	Fri May 25 21:45:08 2018 +0200
+++ b/wamos.sql	Fri May 25 21:54:27 2018 +0200
@@ -159,9 +159,10 @@
        );
 
 CREATE TABLE wamos.fairway_availability (
+       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
        surdat date NOT NULL,
-       PRIMARY KEY (bottleneck_id, surdat),
+       UNIQUE (bottleneck_id, surdat),
        position varchar REFERENCES spot_marks,
        -- additional_data xml -- Currently not relevant for WAMOS
        critical boolean,
@@ -173,8 +174,7 @@
        FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE wamos.fa_reference_values (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       fairway_availability_id NOT NULL REFERENCES fairway_availability,
+       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        PRIMARY KEY (fairway_availability_id, level_of_service),
        fairway_depth smallint,
@@ -185,8 +185,7 @@
        );
 
 CREATE TABLE wamos.bottleneck_pdfs (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       fairway_availability_id NOT NULL REFERENCES fairway_availability,
+       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
        profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
        profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
        PRIMARY KEY (fairway_availability_id, profile_pdf_url),
@@ -195,8 +194,7 @@
        );
 
 CREATE TABLE wamos.effective_fairway_availability (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       fairway_availability_id NOT NULL REFERENCES fairway_availability,
+       fairway_availability_id int NOT NULL REFERENCES fairway_availability,
        measure_date timestamp with time zone NOT NULL,
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),