gemma: wamos.sql annotate

annotate wamos.sql @ 55:ecb9bb0591c9

Note on spurious nullability.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 23 May 2018 17:37:19 +0200
parents	285146e8c9c5
children	f378959820be

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
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	3 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	4
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	5 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	6 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	7 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	8 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	9 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	10 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	11 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	12 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	13
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	14 -- 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	15 -- See RIS-Index Encoding Guide
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 CREATE TYPE isrs AS (
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	17 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	18 -- 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	19 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	20 -- 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	21 fairway_section char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	22 object_reference char(5),
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	23 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	24 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	25
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	26 CREATE TYPE isrsrange AS RANGE (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	27 subtype = isrs
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	28 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	29
53 6e8d571c720e Refer to issue tracker. Tom Gottfried <tom@intevation.de> parents: 52 diff changeset	30 -- Eventually obsolete.
6e8d571c720e Refer to issue tracker. Tom Gottfried <tom@intevation.de> parents: 52 diff changeset	31 -- See https://roundup-intern.intevation.de/wamos/issue5
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 CREATE TABLE rwdrs (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 stretch isrsrange PRIMARY KEY,
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	34 -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	35 -- Only B-tree supports UNIQUE indexes, but we need the GIST index
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	36 -- below anyhow.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	37 -- Is it a good idea to build B-tree indexes on relatively large
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	38 -- composites of string values or should we use inter PKs?
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	39 -- => In case the index is used and cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	40 -- factor, this might be an issue.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	41 rwdr double precision NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	42 EXCLUDE USING GIST (stretch WITH &&)
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	43 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	44
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	45 CREATE TABLE gauges (
38 529319bc3c5b Mark gauges table as placeholder. Tom Gottfried <tom@intevation.de> parents: 37 diff changeset	46 location isrs PRIMARY KEY,
43 e84ff54eb8e9 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 42 diff changeset	47 dummy_attrib varchar,
e84ff54eb8e9 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 42 diff changeset	48 "..." varchar
38 529319bc3c5b Mark gauges table as placeholder. Tom Gottfried <tom@intevation.de> parents: 37 diff changeset	49 -- TODO: add real gauge attributes (DRC 2.1.4)
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	50 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	51
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	52 CREATE TABLE countries (
37 6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	53 country_code char(2) PRIMARY KEY -- ISO 3166 country code
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	54 -- A smallint PK would require even less disk space i.e. on the FK side.
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	55 -- This might be an issue in case cache space becomes a limiting
6f273a649f08 Be more specific with ISRS and update some comments. Tom Gottfried <tom@intevation.de> parents: 35 diff changeset	56 -- factor when there are many FKs pointing here.
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	57 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	58
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	59 CREATE TABLE riverbed_materials (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	60 material varchar PRIMARY KEY
39 830287983657 Comment: details to clarify riverbed material. Tom Gottfried <tom@intevation.de> parents: 38 diff changeset	61 -- 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	62 -- 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	63 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	64
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	65 CREATE TABLE survey_types (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	66 survey_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	67 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	68
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	69 CREATE TABLE coverage_types (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	70 coverage_type varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	71 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	72
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	73 CREATE TABLE limiting_factors (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	74 limiting_factor varchar PRIMARY KEY
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	75 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	76
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	77 CREATE TABLE depth_references (
40 535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	78 depth_reference varchar(4) PRIMARY KEY
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	79 -- See col. AB and AI RIS-Index Encoding Guide
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	80 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	81 -- RIS-Index) and other references (e.g. col. AB and AI):
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	82 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	83 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	84 -- _ Do not mixup things with different meanings in one table at all
535e57373a93 Fix and comments on depth references. Tom Gottfried <tom@intevation.de> parents: 39 diff changeset	85 -- (which would mean a model differing a bit from RIS-Index ideas)
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	86 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	87
55 ecb9bb0591c9 Note on spurious nullability. Tom Gottfried <tom@intevation.de> parents: 54 diff changeset	88 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
ecb9bb0591c9 Note on spurious nullability. Tom Gottfried <tom@intevation.de> parents: 54 diff changeset	89 -- (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	90 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	91 CREATE TABLE bottlenecks (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	92 bottleneck_id varchar PRIMARY KEY,
54 285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	93 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	94 -- 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	95 objnam varchar,
285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	96 nobjnm varchar,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	97 stretch isrsrange NOT NULL,
50 a7cb0c2c3dd7 An area will be processed on upload for bottlenecks (see SUC8). Tom Gottfried <tom@intevation.de> parents: 49 diff changeset	98 area geometry(POLYGON, 3146) NOT NULL,
42 47f1ffdc6ef4 Cleanup rb_lb. Tom Gottfried <tom@intevation.de> parents: 41 diff changeset	99 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	100 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	101 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	102 revisiting_time smallint NOT NULL,
46 9106e1510244 Tweak ER diagramm. Tom Gottfried <tom@intevation.de> parents: 45 diff changeset	103 limiting varchar NOT NULL REFERENCES limiting_factors,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	104 surtyp varchar NOT NULL REFERENCES survey_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	105 -- XXX: Also an attribut of sounding result?
54 285146e8c9c5 Align constraints with WSDL (NOT NULL vs. minOccurs). Tom Gottfried <tom@intevation.de> parents: 53 diff changeset	106 coverage varchar REFERENCES coverage_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	107 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	108 -- CHECK allowed combinations of surtyp and coverage or
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	109 -- different model approach?
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	110 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	111 -- XXX: Also an attribut of sounding result?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	112 date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 6185e4780017 Fix syntax error. Tom Gottfried <tom@intevation.de> parents: 47 diff changeset	113 source_organization varchar NOT NULL
47 a03ff9076969 Delivered by interface for other uses than WAMOS. Tom Gottfried <tom@intevation.de> parents: 46 diff changeset	114 -- additional_data xml -- Currently not relevant for WAMOS
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	115 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	116 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	117 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	118
44 dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	119 CREATE TABLE bottlenecks_riverbed_materials (
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	120 bottleneck_id varchar REFERENCES bottlenecks,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	121 riverbed varchar REFERENCES riverbed_materials,
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	122 -- 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	123 PRIMARY KEY (bottleneck_id, riverbed)
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	124 );
dcc3b1134b24 Multiple riverbed materials per bottleneck possible. Tom Gottfried <tom@intevation.de> parents: 43 diff changeset	125
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	126 CREATE TABLE sounding_results (
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	127 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	128 date_info date NOT NULL,
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	129 PRIMARY KEY (bottleneck_id, date_info),
51 bb81404e8ad1 An area will be processed or given for clipping for surveys (see SUC10). Tom Gottfried <tom@intevation.de> parents: 50 diff changeset	130 area geometry(POLYGON, 3146) NOT NULL,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	131 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	132 coverage varchar REFERENCES coverage_types,
45 cb25ddf16cbf Probably attributes of sounding results Tom Gottfried <tom@intevation.de> parents: 44 diff changeset	133 depth_reference char(3) NOT NULL REFERENCES depth_references,
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	134 sounding_data raster NOT NULL
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	135 );
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	136
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	137 COMMIT;

Mercurial > gemma

annotate wamos.sql @ 55:ecb9bb0591c9