Mercurial > gemma

annotate schema/gemma.sql @ 569:ad07846b09d1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add function to construct isrs from text In passing moved all related functionality to its own set of files.
author Tom Gottfried <tom@intevation.de>
date Wed, 05 Sep 2018 11:05:58 +0200
parents f3452ce5c056
children e6c9d2da2e20
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
3 --
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
4 -- Infrastructure
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
5 --
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents: 95
diff changeset
8 -- TODO: will there ever be UPDATEs or can we drop that function due to
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents: 95
diff changeset
9 -- historicisation?
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 AS $$
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 END;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 $$;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
19 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
20 -- GEMMA data
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
21 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
22
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
23 -- 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
24 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
25 -- 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
26 CREATE TABLE user_profiles (
361
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 345
diff changeset
27 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
28 -- 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
29 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
30 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
31 )
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
32 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
33 ;
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
34
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
35
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
36 -- 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
37 CREATE SCHEMA sys_admin
457
62ffb6c8a42e Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents: 449
diff changeset
38 CREATE TABLE system_config (
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
39 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
40 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
41 )
437
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 383
diff changeset
42
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
43 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
44 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
45 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
46 username varchar NOT NULL
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
47 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
48 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
49 )
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 457
diff changeset
50
457
62ffb6c8a42e Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents: 449
diff changeset
51 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
52 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
53 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
54 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
55 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 383
diff changeset
56
457
62ffb6c8a42e Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents: 449
diff changeset
57 CREATE TABLE published_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
58 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
59 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
60 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
61 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
62 )
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
63 ;
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
64
449
a74b8c2a4e75 Removed the command line option to publish tables with GeoServer.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 437
diff changeset
65 -- Tables with geo data to be published with GeoServer.
a74b8c2a4e75 Removed the command line option to publish tables with GeoServer.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 437
diff changeset
66 INSERT INTO sys_admin.published_services (name) VALUES ('fairway_dimensions');
a74b8c2a4e75 Removed the command line option to publish tables with GeoServer.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 437
diff changeset
67
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
68 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
69 -- 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
70 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
71 CREATE TABLE countries (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
72 country_code char(2) PRIMARY KEY -- ISO 3166 country code
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
73 -- 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
74 -- This might be an issue in case cache space becomes a limiting
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
75 -- factor when there are many FKs pointing here.
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
76 );
82
c71e43f88ae0 Add auxilliary tables.
Tom Gottfried <tom@intevation.de>
parents: 81
diff changeset
77
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
78 CREATE TABLE language_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
79 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
80 );
84
d905022a48e9 More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents: 83
diff changeset
81
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
82 CREATE TABLE catccls (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
83 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
84 -- TODO: Do we need name and/or definition from IENC feature catalogue?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
85 );
85
1a640da943b6 Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents: 84
diff changeset
86
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
87 CREATE TABLE dirimps (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
88 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
89 -- TODO: Do we need name and/or definition from IENC feature catalogue?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
90 );
85
1a640da943b6 Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents: 84
diff changeset
91
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
92 CREATE TABLE depth_references (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
93 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
94 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
95 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
96 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
97 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
98 -- 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
99 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
100 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
101 );
93
765906789840 Add gauge attributes.
Tom Gottfried <tom@intevation.de>
parents: 92
diff changeset
102
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
103 CREATE TABLE reference_water_levels (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
104 reference_water_level varchar(20) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
105 );
70
28108455ad35 Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents: 69
diff changeset
106
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
107 CREATE TABLE distance_mark_functions (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
108 -- XXX: Redundant information to object code in isrs code of dist. mark
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
109 distance_mark_function varchar(8) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
110 );
78
012948f18faa Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents: 77
diff changeset
111
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
112 CREATE TABLE position_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
113 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
114 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
115 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
116 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
117 -- 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
118 -- Clarify!
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
119 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
120 );
87
c46fb3f1faeb Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents: 86
diff changeset
121
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
122 CREATE TABLE levels_of_service (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
123 level_of_service smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
124 );
368
78588014ad0a Populate levels_of_service
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
125 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
126
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
127 CREATE TABLE riverbed_materials (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
128 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
129 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
130 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
131 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
133 CREATE TABLE survey_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
134 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
135 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
137 CREATE TABLE coverage_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
138 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
139 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
141 CREATE TABLE measure_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
142 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
143 );
58
30cb2f87c268 Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 56
diff changeset
144
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
145
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
146 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
147 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
148 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
149 country char(2) PRIMARY KEY REFERENCES countries,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
150 area geography(MULTIPOLYGON, 4326)
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
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
153 CREATE TABLE templates (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
154 template_name varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
155 template_data bytea NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
156 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
157 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
158 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
159 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
160
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
161 CREATE TABLE user_templates (
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 212
diff changeset
162 username varchar NOT NULL
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
163 REFERENCES internal.user_profiles
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
164 ON DELETE CASCADE ON UPDATE CASCADE,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
165 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
166 PRIMARY KEY (username, template_name)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
167 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
168 ;
268
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
169 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
170 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
171
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
172
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
173 -- 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
174 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
175
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
176 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
177 -- 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
178 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
179 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
180 -- -- 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
181 -- -- 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
182 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
183 -- -- 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
184 -- -- 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
185 -- -- => 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
186 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
187 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
188 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
189 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
190
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
191 CREATE TABLE waterway_area (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
192 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
193 area geography(POLYGON, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
194 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
195 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
196 )
371
d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
197 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
198
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
199 CREATE TABLE gauges (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
200 location isrs PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
201 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
202 objname varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
203 is_left boolean, -- XXX: Or reference position_codes?
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
204 geom geography(POINT, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
205 applicability isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
206 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
207 -- 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
208 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
209 -- 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
210 zero_point double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
211 geodref varchar(4) REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
212 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
213 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
214 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
215 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
216 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
217
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
218 CREATE TABLE gauges_reference_water_levels (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
219 gauge_id isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
220 reference_water_level varchar(20)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
221 NOT NULL REFERENCES reference_water_levels,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
222 PRIMARY KEY (gauge_id, reference_water_level),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
223 value int NOT NULL
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
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
226 CREATE TABLE gauge_measurements (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
227 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
228 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
229 PRIMARY KEY (fk_gauge_id, measure_date),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
230 -- 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
231 -- 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
232 -- 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
233 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
234 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
235 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
236 -- 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
237 -- XXX: Always ZPG?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
238 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
239 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
240 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
241 -- 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
242 -- 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
243 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
244 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
245 --- 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
246 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
247 source_organization varchar NOT NULL, -- "originator"
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
248 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
249 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
250 CREATE TRIGGER gauge_measurements_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
251 BEFORE UPDATE ON gauge_measurements
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
252 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
253
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
254 CREATE TABLE waterway_axis (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
255 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
256 wtwaxs geography(LINESTRING, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
257 -- 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
258 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
259 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
260 )
371
d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
261 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
262
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
263 -- 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
264 -- 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
265 -- distance marks along waterway axis (see SUC7).
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
266 CREATE TABLE distance_marks (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
267 location_code isrs PRIMARY KEY,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
268 geom geography(POINT, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
269 distance_mark_function varchar(8)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
270 NOT NULL REFERENCES distance_mark_functions,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
271 -- 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
272 -- 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
273 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
274 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
275
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
276 CREATE TABLE sections_stretches (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
277 id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
278 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
279 stretch isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
280 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
281 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
282 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
283 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
284 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
285 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
286 CREATE TRIGGER sections_stretches_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
287 BEFORE UPDATE ON sections_stretches
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
288 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
289
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
290 CREATE TABLE waterway_profiles (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
291 location isrs NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
292 validity tstzrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
293 EXCLUDE USING GIST (validity WITH &&),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
294 PRIMARY KEY (location, validity),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
295 lnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
296 mwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
297 hnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
298 fe30 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
299 fe100 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
300 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
301 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
302 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
303 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
304 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
305 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
306 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
307 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
308 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
309 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
310
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
311 CREATE TABLE fairway_dimensions (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
312 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
313 area geography(POLYGON, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
314 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
315 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
316 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
317 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
318 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
319 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
320 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
321 )
371
d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents: 370
diff changeset
322 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
323 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
324 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
325 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
326
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
327 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
328 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
329 --
567
f3452ce5c056 Make table writable for data import
Tom Gottfried <tom@intevation.de>
parents: 478
diff changeset
330 CREATE TABLE limiting_factors (
f3452ce5c056 Make table writable for data import
Tom Gottfried <tom@intevation.de>
parents: 478
diff changeset
331 limiting_factor varchar PRIMARY KEY
f3452ce5c056 Make table writable for data import
Tom Gottfried <tom@intevation.de>
parents: 478
diff changeset
332 )
f3452ce5c056 Make table writable for data import
Tom Gottfried <tom@intevation.de>
parents: 478
diff changeset
333
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
334 -- 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
335 -- (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
336 -- 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
337 CREATE TABLE bottlenecks (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
338 bottleneck_id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
339 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
340 -- 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
341 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
342 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
343 stretch isrsrange NOT NULL,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
344 area geography(POLYGON, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
345 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
346 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
347 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
348 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
349 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
350 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
351 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
352 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
353 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
354 -- 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
355 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
356 -- 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
357 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
358 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
359 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
360 -- 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
361 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
362 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
363 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
364 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
365
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
366 CREATE TABLE bottlenecks_riverbed_materials (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
367 bottleneck_id varchar REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
368 riverbed varchar REFERENCES riverbed_materials,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
369 -- 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
370 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
371 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
372
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
373 CREATE TABLE sounding_results (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
374 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
375 date_info date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
376 PRIMARY KEY (bottleneck_id, date_info),
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
377 area geography(POLYGON, 4326) NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
378 surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
379 coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
380 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
381 sounding_data raster NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
382 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
383 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
384
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 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
387 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
388 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
389 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
390 position_code char(2) REFERENCES position_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
391 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
392 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
393 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
394 -- 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
395 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
396 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
397 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
398 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
399 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
400 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
401 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
402
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
403 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
404 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
405 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
406 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
407 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
408 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
409 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
410 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
411 IS NOT NULL),
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
412 shallowest_spot geography(POINT, 4326)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
413 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
414
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
415 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
416 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
417 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
418 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
419 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
420 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
421 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
422 )
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 effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
425 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
426 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
427 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
428 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
429 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
430 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
431 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
432 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
433 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
434 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
435 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
436 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
437 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
438 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
439 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
440 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
441 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
442 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
443 ;
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
444
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
445 COMMIT;