gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 143:abfac07bd82a vue-gettext

closing branch vue-gettext

author	Thomas Junk <thomas.junk@intevation.de>
date	Mon, 02 Jul 2018 09:37:53 +0200
parents	731fb359b29c
children	4e2451d561b1

rev	line source
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	1 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	2
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	3 --
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	4 -- Infrastructure
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	5 --
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	6 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	7
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	8 -- TODO: will there ever be UPDATEs or can we drop that function due to
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	9 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	10 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	11 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	12 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	13 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	14 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	15 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	17 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	18
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	19 -- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	20 -- See RIS-Index Encoding Guide
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 CREATE TYPE isrs AS (
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	22 country_code char(2), -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	23 -- could be validated against countries table.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	24 locode char(3), -- without the country code:
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	25 -- http://www.unece.org/cefact/locode/welcome.html
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	26 fairway_section char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	27 object_reference char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	28 hectometre int -- should be constrained to five digits
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	29 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 CREATE TYPE isrsrange AS RANGE (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 subtype = isrs
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	34
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	35 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	36 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	37 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	38 CREATE SCHEMA gemma;
d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	39 CREATE SCHEMA gemma_waterway;
d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	40 CREATE SCHEMA gemma_fairway;
d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	41 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	42 -- TODO: consolidate schemas. The current distribution of tables is mainly
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	43 -- for diagram generation. A privilege based distribution might ease
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	44 -- privilege management.
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	45
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	46 --
83 d9d01dc58f60 Update README. Tom Gottfried <tom@intevation.de> parents: 82 diff changeset	47 -- Auxiliary tables
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	48 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	49 CREATE TABLE gemma.system_config (
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	50 config_key varchar PRIMARY KEY,
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	51 config_val varchar
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	52 );
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	53
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	54 CREATE TABLE gemma.countries (
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	55 country_code char(2) PRIMARY KEY -- ISO 3166 country code
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	56 -- A smallint PK would require even less disk space i.e. on the FK side.
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	57 -- This might be an issue in case cache space becomes a limiting
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	58 -- factor when there are many FKs pointing here.
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	59 );
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	60
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	61 CREATE TABLE gemma.responsibility_areas (
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	62 country char(2) PRIMARY KEY REFERENCES countries,
135 731fb359b29c Schema: Make responsibility_areas multi polygons. Sascha Wilde <wilde@intevation.de> parents: 115 diff changeset	63 area geometry(MULTIPOLYGON, 4326) --XXX: Should be geography (elsewhere too)
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	64 );
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	65
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	66 CREATE TABLE gemma.language_codes (
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	67 language_code varchar PRIMARY KEY
d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	68 );
d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	69
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	70 CREATE TABLE gemma.user_profiles (
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	71 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	72 country char(2) NOT NULL REFERENCES countries,
94 611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	73 language_code varchar REFERENCES language_codes,
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	74 map_extent box2d,
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	75 email_adress varchar NOT NULL UNIQUE
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	76 );
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	77
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	78 CREATE TABLE gemma.templates (
102 3780a1afdc98 Auto-generate template IDs. Tom Gottfried <tom@intevation.de> parents: 99 diff changeset	79 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	80 dummy_attrib varchar,
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	81 "..." varchar
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	82 -- TODO: template attributes tbd.
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	83 );
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	84
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	85 CREATE TABLE gemma.user_templates (
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	86 username varchar NOT NULL REFERENCES user_profiles,
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	87 template_id int NOT NULL REFERENCES templates,
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	88 PRIMARY KEY (username, template_id)
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	89 );
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	90
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	91 --
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	92 -- General river information
c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	93 --
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	94
53 6e8d571c720e Refer to issue tracker. Tom Gottfried <tom@intevation.de> parents: 52 diff changeset	95 -- Eventually obsolete.
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	96 -- See https://roundup-intern.intevation.de/gemma/issue5
62 39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	97 -- CREATE TABLE rwdrs (
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	98 -- stretch isrsrange PRIMARY KEY,
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	99 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	100 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	101 -- -- below anyhow.
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	102 -- -- Is it a good idea to build B-tree indexes on relatively large
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	103 -- -- composites of string values or should we use inter PKs?
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	104 -- -- => In case the index is used and cache space becomes a limiting
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	105 -- -- factor, this might be an issue.
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	106 -- rwdr double precision NOT NULL,
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	107 -- EXCLUDE USING GIST (stretch WITH &&)
39e0e3d8dd1b Comment potentially obsolete table out. Tom Gottfried <tom@intevation.de> parents: 60 diff changeset	108 -- );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	109
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	110 CREATE TABLE gemma_waterway.catccls (
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	111 catccl smallint PRIMARY KEY
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	112 -- TODO: Do we need name and/or definition from IENC feature catalogue?
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	113 );
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	114
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	115 CREATE TABLE gemma_waterway.dirimps (
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	116 dirimp smallint PRIMARY KEY
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	117 -- TODO: Do we need name and/or definition from IENC feature catalogue?
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	118 );
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	119
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	120 CREATE TABLE gemma_waterway.waterway_area (
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	121 area geometry(POLYGON, 4326) PRIMARY KEY,
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	122 catccl smallint REFERENCES catccls,
1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	123 dirimp smallint REFERENCES dirimps
69 beb1863eefaf Add waterway area. Tom Gottfried <tom@intevation.de> parents: 67 diff changeset	124 );
beb1863eefaf Add waterway area. Tom Gottfried <tom@intevation.de> parents: 67 diff changeset	125
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	126 CREATE TABLE gemma_fairway.depth_references (
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	127 depth_reference varchar(4) PRIMARY KEY
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	128 -- See col. AB and AI RIS-Index Encoding Guide
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	129 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	130 -- RIS-Index) and other references (e.g. col. AB and AI):
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	131 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	132 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	133 -- _ Do not mixup things with different meanings in one table at all
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	134 -- (which would mean a model differing a bit from RIS-Index ideas)
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	135 );
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	136
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	137 CREATE TABLE gemma_fairway.reference_water_levels (
70 28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	138 reference_water_level varchar(20) PRIMARY KEY
28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	139 );
28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	140
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	141 CREATE TABLE gemma_fairway.gauges (
38 529319bc3c5b Mark gauges table as placeholder. Tom Gottfried <tom@intevation.de> parents: 37 diff changeset	142 location isrs PRIMARY KEY,
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	143 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	144 objname varchar NOT NULL,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	145 is_left boolean, -- XXX: Or reference position_codes?
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	146 geom geometry(POINT, 4326) NOT NULL,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	147 applicability isrsrange,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	148 validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	149 -- pasted text from a more general specification is given
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	150 -- (a gauge is not a berth!)
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	151 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	152 zero_point double precision NOT NULL,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	153 geodref varchar(4) REFERENCES depth_references,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	154 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	155 source_organization varchar NOT NULL
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	156 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	157 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	158 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	159
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	160 CREATE TABLE gemma_fairway.gauges_reference_water_levels (
71 e9bc0d93bef9 Add missing NOT NULL constraints. Tom Gottfried <tom@intevation.de> parents: 70 diff changeset	161 gauge_id isrs NOT NULL REFERENCES gauges,
e9bc0d93bef9 Add missing NOT NULL constraints. Tom Gottfried <tom@intevation.de> parents: 70 diff changeset	162 reference_water_level varchar(20)
e9bc0d93bef9 Add missing NOT NULL constraints. Tom Gottfried <tom@intevation.de> parents: 70 diff changeset	163 NOT NULL REFERENCES reference_water_levels,
70 28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	164 PRIMARY KEY (gauge_id, reference_water_level),
28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	165 value int NOT NULL
28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	166 );
28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	167
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	168 CREATE TABLE gemma_fairway.gauge_measurements (
72 158f776732c7 Add gauge measurements stub. Tom Gottfried <tom@intevation.de> parents: 71 diff changeset	169 fk_gauge_id isrs NOT NULL REFERENCES gauges,
94 611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	170 measure_date timestamp with time zone NOT NULL,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	171 PRIMARY KEY (fk_gauge_id, measure_date),
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	172 -- XXX: Is country_code really relevant for GEMMA or just NtS?
72 158f776732c7 Add gauge measurements stub. Tom Gottfried <tom@intevation.de> parents: 71 diff changeset	173 -- country_code char(2) NOT NULL REFERENCES countries,
158f776732c7 Add gauge measurements stub. Tom Gottfried <tom@intevation.de> parents: 71 diff changeset	174 -- TODO: add relations to stuff provided as enumerations
94 611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	175 sender varchar NOT NULL, -- "from" attribute from DRC
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	176 language_code varchar NOT NULL REFERENCES language_codes,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	177 date_issue timestamp with time zone NOT NULL,
95 c79fd71ebe06 Comment out some rather spurious attributes. Tom Gottfried <tom@intevation.de> parents: 94 diff changeset	178 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
94 611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	179 -- XXX: Always ZPG?
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	180 water_level double precision NOT NULL,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	181 predicted boolean NOT NULL,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	182 is_waterlevel boolean NOT NULL,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	183 -- XXX: "measure_code" if really only W or Q
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	184 -- XXX: Do we need "unit" attribute or can we normalise on import?
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	185 value_min double precision, -- XXX: NOT NULL if predicted?
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	186 value_max double precision, -- XXX: NOT NULL if predicted?
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	187 --- TODO: Add a double range type for checking?
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	188 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	189 source_organization varchar NOT NULL -- "originator"
72 158f776732c7 Add gauge measurements stub. Tom Gottfried <tom@intevation.de> parents: 71 diff changeset	190 );
94 611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	191 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
611dc09dbcd8 Add gauge measurement attributes. Tom Gottfried <tom@intevation.de> parents: 93 diff changeset	192 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
72 158f776732c7 Add gauge measurements stub. Tom Gottfried <tom@intevation.de> parents: 71 diff changeset	193
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	194 CREATE TABLE gemma_waterway.waterway_axis (
86 2477cba141e9 Add axis attributes. Tom Gottfried <tom@intevation.de> parents: 85 diff changeset	195 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
2477cba141e9 Add axis attributes. Tom Gottfried <tom@intevation.de> parents: 85 diff changeset	196 -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
2477cba141e9 Add axis attributes. Tom Gottfried <tom@intevation.de> parents: 85 diff changeset	197 objnam varchar NOT NULL,
2477cba141e9 Add axis attributes. Tom Gottfried <tom@intevation.de> parents: 85 diff changeset	198 nobjnam varchar
73 65a8aeb9e2b3 Add waterway axis stub. Tom Gottfried <tom@intevation.de> parents: 72 diff changeset	199 );
65a8aeb9e2b3 Add waterway axis stub. Tom Gottfried <tom@intevation.de> parents: 72 diff changeset	200
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	201 CREATE TABLE gemma_waterway.distance_mark_functions (
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	202 -- XXX: Redundant information to object code in isrs code of dist. mark
012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	203 distance_mark_function varchar(8) PRIMARY KEY
012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	204 );
012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	205
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	206 CREATE TABLE gemma_waterway.position_codes (
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	207 position_code char(2) PRIMARY KEY
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	208 -- Use smallint because of fairway availability provided on daily basis?
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	209 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	210 -- sheet "Position_code" or RIS-Index encoding guide?
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	211 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	212 -- Clarify!
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	213 -- TODO: Do we need an attribute "meaning" or so?
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	214 );
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	215
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	216 -- This table allows linkage between the 1D ISRS location codes and 2D space
012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	217 -- e.g. for cutting bottleneck area out of waterway area based on virtual
012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	218 -- distance marks along waterway axis (see SUC7).
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	219 CREATE TABLE gemma_waterway.distance_marks (
74 8c0af903a764 Add distance marks stub. Tom Gottfried <tom@intevation.de> parents: 73 diff changeset	220 location_code isrs PRIMARY KEY,
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	221 geom geometry(POINT, 4326) NOT NULL,
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	222 distance_mark_function varchar(8)
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	223 NOT NULL REFERENCES distance_mark_functions,
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	224 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	225 -- to encode the same thing as the object code in ISRS location code.
c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	226 position_code char(2) NOT NULL REFERENCES position_codes
75 ed7a50cfbc19 Add sections and stretches stub and fix comment. Tom Gottfried <tom@intevation.de> parents: 74 diff changeset	227 );
ed7a50cfbc19 Add sections and stretches stub and fix comment. Tom Gottfried <tom@intevation.de> parents: 74 diff changeset	228
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	229 CREATE TABLE gemma_waterway.sections_stretches (
75 ed7a50cfbc19 Add sections and stretches stub and fix comment. Tom Gottfried <tom@intevation.de> parents: 74 diff changeset	230 id varchar PRIMARY KEY,
ed7a50cfbc19 Add sections and stretches stub and fix comment. Tom Gottfried <tom@intevation.de> parents: 74 diff changeset	231 is_section boolean NOT NULL, -- maps 'function' from interface
ed7a50cfbc19 Add sections and stretches stub and fix comment. Tom Gottfried <tom@intevation.de> parents: 74 diff changeset	232 stretch isrsrange,
88 a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	233 objnam varchar NOT NULL,
a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	234 nobjnam varchar,
a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	235 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	236 source_organization varchar NOT NULL
74 8c0af903a764 Add distance marks stub. Tom Gottfried <tom@intevation.de> parents: 73 diff changeset	237 );
88 a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	238 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
a96fee9654db Add sections and stretches attributes. Tom Gottfried <tom@intevation.de> parents: 87 diff changeset	239 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
74 8c0af903a764 Add distance marks stub. Tom Gottfried <tom@intevation.de> parents: 73 diff changeset	240
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	241 CREATE TABLE gemma_waterway.waterway_profiles (
91 c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	242 location isrs NOT NULL,
92 f11366b419ae Fix profile constraints. Tom Gottfried <tom@intevation.de> parents: 91 diff changeset	243 validity tstzrange,
91 c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	244 EXCLUDE USING GIST (validity WITH &&),
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	245 PRIMARY KEY (location, validity),
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	246 lnwl smallint,
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	247 mwl smallint,
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	248 hnwl smallint,
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	249 fe30 smallint,
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	250 fe100 smallint,
92 f11366b419ae Fix profile constraints. Tom Gottfried <tom@intevation.de> parents: 91 diff changeset	251 -- XXX: further normalise using reference_water_levels?
f11366b419ae Fix profile constraints. Tom Gottfried <tom@intevation.de> parents: 91 diff changeset	252 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
f11366b419ae Fix profile constraints. Tom Gottfried <tom@intevation.de> parents: 91 diff changeset	253 OR validity IS NOT NULL),
91 c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	254 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	255 source_organization varchar NOT NULL
79 c22ddffc2071 Add waterway profiles stub. Tom Gottfried <tom@intevation.de> parents: 78 diff changeset	256 );
91 c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	257 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
c38edd879e2e Add profile attributes. Tom Gottfried <tom@intevation.de> parents: 90 diff changeset	258 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
79 c22ddffc2071 Add waterway profiles stub. Tom Gottfried <tom@intevation.de> parents: 78 diff changeset	259
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	260 CREATE TABLE gemma_waterway.levels_of_service (
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	261 level_of_service smallint PRIMARY KEY
a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	262 );
a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	263
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	264 CREATE TABLE gemma_waterway.fairway_dimensions (
90 a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	265 area geometry(POLYGON, 4326) PRIMARY KEY,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	266 level_of_service smallint NOT NULL REFERENCES levels_of_service,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	267 min_width smallint NOT NULL,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	268 max_width smallint NOT NULL,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	269 min_depth smallint NOT NULL,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	270 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	271 source_organization varchar NOT NULL
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	272 );
90 a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	273 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
a8878834adfb Add fairway attributes. Tom Gottfried <tom@intevation.de> parents: 89 diff changeset	274 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	275
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	276 --
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	277 -- Bottlenecks
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	278 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	279 CREATE TABLE gemma_fairway.riverbed_materials (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	280 material varchar PRIMARY KEY
39 830287983657 Comment: details to clarify riverbed material. Tom Gottfried <tom@intevation.de> parents: 38 diff changeset	281 -- XXX: Should this table contain choices from DRC 2.2.3 or
830287983657 Comment: details to clarify riverbed material. Tom Gottfried <tom@intevation.de> parents: 38 diff changeset	282 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	283 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	284
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	285 CREATE TABLE gemma_fairway.survey_types (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	286 survey_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	287 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	288
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	289 CREATE TABLE gemma_fairway.coverage_types (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	290 coverage_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	291 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	292
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	293 CREATE TABLE gemma_fairway.limiting_factors (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	294 limiting_factor varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	295 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	296
55 ecb9bb0591c9 Note on spurious nullability. Tom Gottfried <tom@intevation.de> parents: 54 diff changeset	297 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
ecb9bb0591c9 Note on spurious nullability. Tom Gottfried <tom@intevation.de> parents: 54 diff changeset	298 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
ecb9bb0591c9 Note on spurious nullability. Tom Gottfried <tom@intevation.de> parents: 54 diff changeset	299 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	300 CREATE TABLE gemma_fairway.bottlenecks (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	301 bottleneck_id varchar PRIMARY KEY,
54 285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	302 fk_g_fid isrs NOT NULL REFERENCES gauges,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	303 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
54 285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	304 objnam varchar,
285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	305 nobjnm varchar,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	306 stretch isrsrange NOT NULL,
80 004198a3cbc0 Set correct EPSG code. Tom Gottfried <tom@intevation.de> parents: 79 diff changeset	307 area geometry(POLYGON, 4326) NOT NULL,
42 47f1ffdc6ef4 Cleanup rb_lb. Tom Gottfried <tom@intevation.de> parents: 41 diff changeset	308 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
47f1ffdc6ef4 Cleanup rb_lb. Tom Gottfried <tom@intevation.de> parents: 41 diff changeset	309 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
41 dd858a74b4e9 Set columns referencing countries next to each other. Tom Gottfried <tom@intevation.de> parents: 40 diff changeset	310 responsible_country char(2) NOT NULL REFERENCES countries,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	311 revisiting_time smallint NOT NULL,
46 9106e1510244 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 45 diff changeset	312 limiting varchar NOT NULL REFERENCES limiting_factors,
95 c79fd71ebe06 Comment out some rather spurious attributes. Tom Gottfried <tom@intevation.de> parents: 94 diff changeset	313 -- surtyp varchar NOT NULL REFERENCES survey_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	314 -- XXX: Also an attribut of sounding result?
95 c79fd71ebe06 Comment out some rather spurious attributes. Tom Gottfried <tom@intevation.de> parents: 94 diff changeset	315 -- coverage varchar REFERENCES coverage_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	316 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	317 -- CHECK allowed combinations of surtyp and coverage or
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	318 -- different model approach?
95 c79fd71ebe06 Comment out some rather spurious attributes. Tom Gottfried <tom@intevation.de> parents: 94 diff changeset	319 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	320 -- XXX: Also an attribut of sounding result?
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	321 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
60 f0ca64da9446 Add attribute to mark data sets as checked in staging area. Tom Gottfried <tom@intevation.de> parents: 59 diff changeset	322 source_organization varchar NOT NULL,
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	323 -- additional_data xml -- Currently not relevant for GEMMA
60 f0ca64da9446 Add attribute to mark data sets as checked in staging area. Tom Gottfried <tom@intevation.de> parents: 59 diff changeset	324 staging_done boolean NOT NULL DEFAULT false
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	325 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	326 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	327 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	328
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	329 CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials (
44 dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	330 bottleneck_id varchar REFERENCES bottlenecks,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	331 riverbed varchar REFERENCES riverbed_materials,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	332 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	333 PRIMARY KEY (bottleneck_id, riverbed)
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	334 );
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	335
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	336 CREATE TABLE gemma_fairway.sounding_results (
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	337 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	338 date_info date NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	339 PRIMARY KEY (bottleneck_id, date_info),
80 004198a3cbc0 Set correct EPSG code. Tom Gottfried <tom@intevation.de> parents: 79 diff changeset	340 area geometry(POLYGON, 4326) NOT NULL,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	341 surtyp varchar NOT NULL REFERENCES survey_types,
54 285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	342 coverage varchar REFERENCES coverage_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	343 depth_reference char(3) NOT NULL REFERENCES depth_references,
60 f0ca64da9446 Add attribute to mark data sets as checked in staging area. Tom Gottfried <tom@intevation.de> parents: 59 diff changeset	344 sounding_data raster NOT NULL,
f0ca64da9446 Add attribute to mark data sets as checked in staging area. Tom Gottfried <tom@intevation.de> parents: 59 diff changeset	345 staging_done boolean NOT NULL DEFAULT false
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	346 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	347
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	348 --
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	349 -- Fairway availability
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	350 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	351 CREATE TABLE gemma_fairway.measure_types (
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	352 measure_type varchar PRIMARY KEY
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	353 );
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	354
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	355 CREATE TABLE gemma_fairway.fairway_availability (
66 21df0e95db4a Fix thinkos and syntax errors in previous commit. Tom Gottfried <tom@intevation.de> parents: 65 diff changeset	356 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	357 position_code char(2) REFERENCES position_codes,
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	358 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	359 surdat date NOT NULL,
66 21df0e95db4a Fix thinkos and syntax errors in previous commit. Tom Gottfried <tom@intevation.de> parents: 65 diff changeset	360 UNIQUE (bottleneck_id, surdat),
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	361 -- additional_data xml -- Currently not relevant for GEMMA
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	362 critical boolean,
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	363 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	364 source_organization varchar NOT NULL
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	365 );
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	366 CREATE TRIGGER fairway_availability_date_info
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	367 BEFORE UPDATE ON fairway_availability
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	368 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	369
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	370 CREATE TABLE gemma_fairway.fa_reference_values (
66 21df0e95db4a Fix thinkos and syntax errors in previous commit. Tom Gottfried <tom@intevation.de> parents: 65 diff changeset	371 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	372 level_of_service smallint NOT NULL REFERENCES levels_of_service,
65 cad19acc53b3 Avoid duplicate direct relations in composite primary keys. Tom Gottfried <tom@intevation.de> parents: 64 diff changeset	373 PRIMARY KEY (fairway_availability_id, level_of_service),
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	374 fairway_depth smallint,
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	375 fairway_width smallint,
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	376 fairway_radius int,
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	377 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
80 004198a3cbc0 Set correct EPSG code. Tom Gottfried <tom@intevation.de> parents: 79 diff changeset	378 shallowest_spot geometry(POINT, 4326)
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	379 );
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	380
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	381 CREATE TABLE gemma_fairway.bottleneck_pdfs (
66 21df0e95db4a Fix thinkos and syntax errors in previous commit. Tom Gottfried <tom@intevation.de> parents: 65 diff changeset	382 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	383 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	384 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
65 cad19acc53b3 Avoid duplicate direct relations in composite primary keys. Tom Gottfried <tom@intevation.de> parents: 64 diff changeset	385 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	386 pdf_generation_date timestamp with time zone NOT NULL,
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	387 source_organization varchar NOT NULL
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	388 );
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	389
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	390 CREATE TABLE gemma_fairway.effective_fairway_availability (
66 21df0e95db4a Fix thinkos and syntax errors in previous commit. Tom Gottfried <tom@intevation.de> parents: 65 diff changeset	391 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	392 measure_date timestamp with time zone NOT NULL,
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	393 level_of_service smallint NOT NULL REFERENCES levels_of_service,
65 cad19acc53b3 Avoid duplicate direct relations in composite primary keys. Tom Gottfried <tom@intevation.de> parents: 64 diff changeset	394 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	395 available_depth_value smallint,
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	396 available_width_value smallint,
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	397 water_level_value smallint,
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	398 CHECK(COALESCE(available_depth_value, available_width_value,
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	399 water_level_value) IS NOT NULL),
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	400 measure_type varchar NOT NULL REFERENCES measure_types,
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	401 source_organization varchar NOT NULL,
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	402 forecast_generation_time timestamp with time zone,
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	403 CHECK(measure_type <> 'forecasted'
30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	404 OR forecast_generation_time IS NOT NULL),
59 37faa7359ea6 Interfaces may provide timestamps with time zone. Tom Gottfried <tom@intevation.de> parents: 58 diff changeset	405 value_lifetime timestamp with time zone,
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	406 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	407 );
f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	408
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	409 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 143:abfac07bd82a vue-gettext