gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 793:073394629ec6

client: add measurement and improve identify * Only display features that have an Id in the identify box, because during the drawmode, after the first click, there is an additional feature that has no Id. Probably this is the point. * Add setting an Id to the feature that is drawn, which makes it show up in the identify box. * Add code to add the length of the drawn line to the store and a rounded value to the feature property. This creates a simple measurement tool, if the feature box is shown.

author	Bernhard Reiter <bernhard@intevation.de>
date	Thu, 27 Sep 2018 10:02:39 +0200
parents	34ea9d5df653
children	d32516051d08

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
657 40d3b697ea15 Add PostgreSQL extension for 3D processing Tom Gottfried <tom@intevation.de> parents: 656 diff changeset	8 -- Needed for 3D processing e.g. for cross section profiles
659 ef658c66cfca schema: disable sfcgal Bernhard Reiter <bernhard@intevation.de> parents: 657 diff changeset	9 -- FIXME disabled for now, because not available on Ubuntu/Debian
ef658c66cfca schema: disable sfcgal Bernhard Reiter <bernhard@intevation.de> parents: 657 diff changeset	10 --CREATE EXTENSION postgis_sfcgal;
657 40d3b697ea15 Add PostgreSQL extension for 3D processing Tom Gottfried <tom@intevation.de> parents: 656 diff changeset	11
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	12 -- 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	13 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	14 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	15 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	17 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	18 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	19 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	20 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	22
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	23 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	24 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	25 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	26
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	27 -- Namespace not to be accessed directly by any user
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	28 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	29 -- Profile data are only accessible via the view users.list_users.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	30 CREATE TABLE user_profiles (
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	31 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	32 -- keep username length compatible with role identifier
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	33 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	34 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	35 )
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	36 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	37 ;
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	38
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	39
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	40 -- Namespace to be accessed by sys_admin only
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	41 CREATE SCHEMA sys_admin
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	42 CREATE TABLE system_config (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	43 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	44 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	45 )
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	46
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	47 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	48 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	49 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	50 username varchar NOT NULL
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	51 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	52 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	53 )
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	54
624 8772979f8750 Remove test data from schema Tom Gottfried <tom@intevation.de> parents: 611 diff changeset	55 -- Tables with geo data to be published with GeoServer.
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	56 CREATE TABLE external_services (
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	57 local_name varchar PRIMARY KEY,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	58 remote_url varchar NOT NULL,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	59 is_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	60 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	61
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	62 CREATE TABLE published_services (
598 4854a1e85870 Ensure published service is based on existing table Tom Gottfried <tom@intevation.de> parents: 580 diff changeset	63 name regclass PRIMARY KEY,
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	64 style bytea,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	65 as_wms boolean NOT NULL DEFAULT TRUE,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	66 as_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	67 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	68 ;
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	69
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	70 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	71 -- Look-up tables with data that are static in a running system
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	72 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	73 CREATE TABLE countries (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	74 country_code char(2) PRIMARY KEY -- ISO 3166 country code
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	75 -- A smallint PK would require even less disk space i.e. on the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	76 -- This might be an issue in case cache space becomes a limiting
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	77 -- factor when there are many FKs pointing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	78 );
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	79
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	80 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	81 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	82 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	83
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	84 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	85 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	86 -- TODO: Do we need name and/or definition from IENC feature catalogue?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	87 );
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	88
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	89 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	90 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	91 -- TODO: Do we need name and/or definition from IENC feature catalogue?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	92 );
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	93
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	94 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	95 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	96 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	97 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	98 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	99 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	100 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	101 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	102 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	103 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	104
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	105 CREATE TABLE reference_water_levels (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	106 reference_water_level varchar(20) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	107 );
70 28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	108
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	109 CREATE TABLE distance_mark_functions (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	110 -- XXX: Redundant information to object code in isrs code of dist. mark
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	111 distance_mark_function varchar(8) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	112 );
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	113
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	114 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	115 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	116 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	117 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	118 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	119 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	120 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	121 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	122 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	123
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	124 CREATE TABLE levels_of_service (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	125 level_of_service smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	126 );
368 78588014ad0a Populate levels_of_service Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	127 INSERT INTO levels_of_service VALUES (1), (2), (3);
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	128
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	129 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	130 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	132 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	133 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	134
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	135 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	136 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	137 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	138
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	139 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	140 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	141 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	142
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	143 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	144 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	145 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	146
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	147 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	148 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	149 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	150
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	151
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	152 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	153 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	154 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	155 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	156 area geography(MULTIPOLYGON, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	157 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	158
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	159 CREATE TABLE templates (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	160 template_name varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	161 template_data bytea NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	162 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	163 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	164 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	165 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	166
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	167 CREATE TABLE user_templates (
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	168 username varchar NOT NULL
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	169 REFERENCES internal.user_profiles
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	170 ON DELETE CASCADE ON UPDATE CASCADE,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	171 template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	172 PRIMARY KEY (username, template_name)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	173 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	174 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	175 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	176 country char(2) NOT NULL REFERENCES users.responsibility_areas;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	177
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	178
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	179 -- Namespace for waterway data that can change in a running system
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	180 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	181
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	182 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	183 -- See https://roundup-intern.intevation.de/gemma/issue5
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	184 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	185 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	186 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	187 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	188 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	189 -- -- Is it a good idea to build B-tree indexes on relatively large
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	190 -- -- composites of string values or should we use inter PKs?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	191 -- -- => In case the index is used and cache space becomes a limiting
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	192 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	193 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	194 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	195 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	196
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	197 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	198 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	199 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	200 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	201 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	202 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	203 CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	204
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	205 CREATE TABLE gauges (
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	206 location isrs PRIMARY KEY CHECK(
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	207 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	208 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	209 objname varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	210 is_left boolean, -- XXX: Or reference position_codes?
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	211 geom geography(POINT, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212 applicability isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	213 validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	214 -- pasted text from a more general specification is given
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	215 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	216 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	217 zero_point double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	218 geodref varchar(4) REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	219 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	220 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	221 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	222 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	223 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	225 CREATE TABLE gauges_reference_water_levels (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	226 gauge_id isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	227 reference_water_level varchar(20)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	228 NOT NULL REFERENCES reference_water_levels,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	229 PRIMARY KEY (gauge_id, reference_water_level),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	230 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	231 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	232
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	233 CREATE TABLE gauge_measurements (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	234 fk_gauge_id isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	235 measure_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	236 PRIMARY KEY (fk_gauge_id, measure_date),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	237 -- XXX: Is country_code really relevant for GEMMA or just NtS?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	238 -- country_code char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	239 -- TODO: add relations to stuff provided as enumerations
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	240 sender varchar NOT NULL, -- "from" attribute from DRC
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	241 language_code varchar NOT NULL REFERENCES language_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	242 date_issue timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	243 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	244 -- XXX: Always ZPG?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	246 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	247 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	248 -- XXX: "measure_code" if really only W or Q
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	249 -- XXX: Do we need "unit" attribute or can we normalise on import?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	250 value_min double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	251 value_max double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	252 --- TODO: Add a double range type for checking?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	253 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	254 source_organization varchar NOT NULL, -- "originator"
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	255 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	256 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	257 CREATE TRIGGER gauge_measurements_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	258 BEFORE UPDATE ON gauge_measurements
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	259 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	260
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	261 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	262 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	263 wtwaxs geography(LINESTRING, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	264 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	265 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	266 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	267 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	268 CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	269
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	270 -- This table allows linkage between 1D ISRS location codes and 2D space
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	271 -- e.g. for cutting bottleneck area out of waterway area based on virtual
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	272 -- distance marks along waterway axis (see SUC7).
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	273 CREATE TABLE distance_marks (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	274 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	275 geom geography(POINT, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	276 distance_mark_function varchar(8)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	277 NOT NULL REFERENCES distance_mark_functions,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	278 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	279 -- to encode the same thing as the object code in ISRS location code.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	280 position_code char(2) NOT NULL REFERENCES position_codes
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	281 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	282
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	283 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	284 -- At least geoserver-2.13.2 does not serve type geography correctly
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	285 -- and does not serve the location_code as isrs type
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	286 CREATE VIEW waterway.distance_marks_geoserver AS
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	287 SELECT location_code::VARCHAR,
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	288 geom::Geometry(POINT, 4326),
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	289 distance_mark_function,
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	290 position_code,
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	291 (location_code).hectometre
697 77928a3497f1 schema: fix view distance_marks_geoserver Bernhard Reiter <bernhard@intevation.de> parents: 690 diff changeset	292 FROM waterway.distance_marks
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	293
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	294 CREATE TABLE sections_stretches (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	296 is_section boolean NOT NULL, -- maps 'function' from interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	297 stretch isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	298 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	299 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	300 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	301 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	302 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	303 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	304 CREATE TRIGGER sections_stretches_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	305 BEFORE UPDATE ON sections_stretches
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	306 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	307
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	308 CREATE TABLE waterway_profiles (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	309 location isrs NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	310 validity tstzrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	311 EXCLUDE USING GIST (validity WITH &&),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	312 PRIMARY KEY (location, validity),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	313 lnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	314 mwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	315 hnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	316 fe30 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	317 fe100 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	318 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	319 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	320 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	321 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	322 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	323 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	324 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	325 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	326 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	327 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	328
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	329 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	330 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	331 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	332 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	333 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	334 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	335 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	336 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	337 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	338 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	339 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	340 CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	341 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	342 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	343 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	344
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	345 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	346 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	347 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	348 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	349 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	350 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	351 CREATE TABLE bottlenecks (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	352 bottleneck_id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	353 fk_g_fid isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	354 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	355 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	356 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	357 stretch isrsrange NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	358 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	359 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	360 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	361 responsible_country char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	362 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	363 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	364 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	365 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	366 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	367 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	368 -- CHECK allowed combinations of surtyp and coverage or
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	369 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	370 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	371 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	372 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	373 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	374 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	375 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	376 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	377 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	378 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	379
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	380 CREATE TABLE bottlenecks_riverbed_materials (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	381 bottleneck_id varchar REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	382 riverbed varchar REFERENCES riverbed_materials,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	383 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	384 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	385 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	386
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	387 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	388 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	389 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	390 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	391 UNIQUE (bottleneck_id, date_info),
611 effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	392 area geography(POLYGON, 4326),
effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	393 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	394 coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	395 depth_reference char(3) NOT NULL REFERENCES depth_references,
609 6dc1b586328e Sounding results: C&P error ... too much brackets. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 605 diff changeset	396 point_cloud geography(MULTIPOINTZ, 4326),
605 255f9a4e747f Sounding results: We store the raw data as point clouds. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 598 diff changeset	397 -- XXX: We may raster the data later.
255f9a4e747f Sounding results: We store the raw data as point clouds. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 598 diff changeset	398 -- sounding_data raster NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	399 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	400 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	401
654 baa31814717c Cross sections: Commit data model for fast cross section calculation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 624 diff changeset	402 CREATE TABLE meshes (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	403 sounding_result_id int NOT NULL REFERENCES sounding_results,
654 baa31814717c Cross sections: Commit data model for fast cross section calculation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 624 diff changeset	404 geom geometry(polygonz) NOT NULL
baa31814717c Cross sections: Commit data model for fast cross section calculation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 624 diff changeset	405 )
baa31814717c Cross sections: Commit data model for fast cross section calculation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 624 diff changeset	406
655 014ee3beb329 Fixed syntax error in schema definition. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 654 diff changeset	407 CREATE INDEX meshes_gix ON meshes USING gist(geom)
654 baa31814717c Cross sections: Commit data model for fast cross section calculation. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 624 diff changeset	408
720 aeaa2adf5a8b octree: write SQL insert statements for octree indices. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 697 diff changeset	409 CREATE TABLE octrees (
aeaa2adf5a8b octree: write SQL insert statements for octree indices. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 697 diff changeset	410 sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results,
aeaa2adf5a8b octree: write SQL insert statements for octree indices. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 697 diff changeset	411 checksum varchar NOT NULL,
aeaa2adf5a8b octree: write SQL insert statements for octree indices. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 697 diff changeset	412 octree_index bytea NOT NULL
aeaa2adf5a8b octree: write SQL insert statements for octree indices. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 697 diff changeset	413 )
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	414
4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	415 CREATE TABLE sounding_results_contour_lines (
736 34ea9d5df653 Fixed problem with unique constraint for countour lines. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 731 diff changeset	416 sounding_result_id int NOT NULL REFERENCES sounding_results,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	417 height numeric NOT NULL,
4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	418 lines geography(multilinestringz, 4326) NOT NULL,
4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	419 UNIQUE (sounding_result_id, height)
4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	420 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	421 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	422 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	423 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	424 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	425 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	426 position_code char(2) REFERENCES position_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	427 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	428 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	429 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	430 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	431 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	432 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	433 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	434 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	435 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	436 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	437 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	438
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	439 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	440 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	441 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	442 PRIMARY KEY (fairway_availability_id, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	443 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	444 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	445 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	446 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	447 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	448 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	449 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	450
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	451 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	452 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	453 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	454 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	455 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	456 pdf_generation_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	457 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	458 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	459
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	460 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	461 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	462 measure_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	463 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	464 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	465 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	466 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	467 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	468 CHECK(COALESCE(available_depth_value, available_width_value,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	469 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	470 measure_type varchar NOT NULL REFERENCES measure_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	471 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	472 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	473 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	474 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	475 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	476 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	477 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	478 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	479 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	480
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	481 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 793:073394629ec6