Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1256:1d9552e05b76

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initialize map for ort attributes once and reset attribute indicator for entnahmeOrt.
author Raimund Renkert <raimund.renkert@intevation.de>
date Tue, 17 Jan 2017 17:28:21 +0100
parents f094aca05cf2
children cb88ab44c2be
rev   line source
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 \set ON_ERROR_STOP on
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 BEGIN;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 SET statement_timeout = 0;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 SET lock_timeout = 0;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SET client_encoding = 'UTF8';
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 SET standard_conforming_strings = on;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 SET check_function_bodies = false;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 SET client_min_messages = warning;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 CREATE SCHEMA stammdaten;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 SET search_path = stammdaten, pg_catalog;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16
1175
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
17 CREATE FUNCTION set_ort_id() RETURNS trigger
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
18 LANGUAGE plpgsql
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
19 AS $$
1179
612107caf961 Declared variable in db function set_ort_id.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1176
diff changeset
20 DECLARE value text;
1175
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
21 BEGIN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
22 value = '#'::text || lpad((NEW.id::character varying)::text, 9, '0'::text);
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
23 IF NEW.ort_id IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
24 NEW.ort_id = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
25 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
26 IF NEW.langtext IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
27 NEW.langtext = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
28 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
29 IF NEW.kurztext IS NULL THEN
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
30 NEW.kurztext = value;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
31 END IF;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
32 RETURN NEW;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
33 END;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
34 $$;
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
35
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
36 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
37 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
38 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
39 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
40 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
41 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
42 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
43 $$;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 CREATE FUNCTION get_media_from_media_desk(media_desk character varying) RETURNS character varying
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 LANGUAGE plpgsql
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 AS $$
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 declare
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 result character varying(100);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 d00 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 d01 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 d02 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 d03 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 begin
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 if media_desk like 'D: %' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 d00 := substring(media_desk,4,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 d01 := substring(media_desk,7,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 d02 := substring(media_desk,10,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 d03 := substring(media_desk,13,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 if d00 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 result := null;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 if d01 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 where s00.ebene = 0 and s00.sn = d00::smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 if d02 = '00' or d00 <> '01' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 where s00.ebene = 0 and s00.sn = d00::smallint);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 if d03 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 where s00.ebene = 0 and s00.sn = d00::smallint));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 where s03.ebene = 3 and s03.sn = d03::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 and s03.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 (select s02.id FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 where s00.ebene = 0 and s00.sn = d00::smallint)));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 result := null;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 return (result);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 end;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 $$;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
107 CREATE TABLE koordinaten_art (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
108 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
109 koordinatenart character varying(50),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
110 idf_geo_key character varying(1)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
111 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
112
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
113
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
114 CREATE TABLE mess_einheit (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
115 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
116 beschreibung character varying(50),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
117 einheit character varying(12),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
118 eudf_messeinheit_id character varying(8),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
119 umrechnungs_faktor_eudf bigint
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
120 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
121
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
122
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
123 CREATE TABLE umwelt (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
124 id character varying(3) PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
125 beschreibung character varying(300),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
126 umwelt_bereich character varying(80) NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
127 meh_id integer REFERENCES mess_einheit,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
128 UNIQUE (umwelt_bereich)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
129 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
130
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
131
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
132 CREATE TABLE betriebsart (
1119
d1d7d684800c Switch back to 1-based id of betriebsart.
Tom Gottfried <tom@intevation.de>
parents: 1115
diff changeset
133 id smallint PRIMARY KEY,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
134 name character varying(30) NOT NULL
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
135 );
1119
d1d7d684800c Switch back to 1-based id of betriebsart.
Tom Gottfried <tom@intevation.de>
parents: 1115
diff changeset
136 INSERT INTO betriebsart VALUES(1, 'Normal-/Routinebetrieb');
d1d7d684800c Switch back to 1-based id of betriebsart.
Tom Gottfried <tom@intevation.de>
parents: 1115
diff changeset
137 INSERT INTO betriebsart VALUES(2, 'Störfall-/Intensivbetrieb');
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
138
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
139
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
140 CREATE TABLE staat (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
141 id serial PRIMARY KEY,
1223
9b8b18c00804 Make all state identifiers unique.
Tom Gottfried <tom@intevation.de>
parents: 1222
diff changeset
142 staat character varying(50) NOT NULL UNIQUE,
9b8b18c00804 Make all state identifiers unique.
Tom Gottfried <tom@intevation.de>
parents: 1222
diff changeset
143 hkl_id smallint NOT NULL UNIQUE,
9b8b18c00804 Make all state identifiers unique.
Tom Gottfried <tom@intevation.de>
parents: 1222
diff changeset
144 staat_iso character varying(2) UNIQUE,
9b8b18c00804 Make all state identifiers unique.
Tom Gottfried <tom@intevation.de>
parents: 1222
diff changeset
145 staat_kurz character varying(5) UNIQUE,
1222
f8df0eae09e4 In memoriam Oracle.
Tom Gottfried <tom@intevation.de>
parents: 1221
diff changeset
146 eu boolean,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
147 koord_x_extern character varying(22),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
148 koord_y_extern character varying(22),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
149 kda_id integer REFERENCES koordinaten_art
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
150 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
151
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
152 CREATE TABLE verwaltungseinheit (
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
153 id character varying(8) NOT NULL PRIMARY KEY,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
154 bezeichnung character varying(80) NOT NULL,
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
155 regbezirk character varying(8),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
156 kreis character varying(8),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
157 bundesland character varying(8) NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
158 is_gemeinde boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
159 is_landkreis boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
160 is_regbezirk boolean DEFAULT false NOT NULL,
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
161 is_bundesland boolean DEFAULT false NOT NULL,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
162 plz character varying(6),
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
163 nuts character varying(10),
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
164 mittelpunkt public.geometry(Point)
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
165 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
166
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
167 CREATE TABLE verwaltungsgrenze (
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
168 id serial PRIMARY KEY,
1208
9dda1237c756 Add missing FK constraint.
Tom Gottfried <tom@intevation.de>
parents: 1194
diff changeset
169 gem_id character varying(8) NOT NULL REFERENCES verwaltungseinheit,
1249
f094aca05cf2 Avoid having to handle missing shapes.
Tom Gottfried <tom@intevation.de>
parents: 1223
diff changeset
170 shape public.geometry(MultiPolygon, 4326) NOT NULL
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1179
diff changeset
171 );
1209
df75a467d55a Add spatial index to speed up OrtFactory.findVerwaltungseinheit().
Tom Gottfried <tom@intevation.de>
parents: 1208
diff changeset
172 CREATE INDEX verwaltungsgrenze_sp_idx ON verwaltungsgrenze USING gist (shape);
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
173
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
174 CREATE TABLE netz_betreiber (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
175 id character varying(2) PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
176 netzbetreiber character varying(50),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
177 idf_netzbetreiber character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
178 is_bmn boolean DEFAULT false,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
179 mailverteiler character varying(512),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
180 aktiv boolean DEFAULT false
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
181 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
182
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
183
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
184 CREATE TABLE mess_stelle (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
185 id character varying(5) PRIMARY KEY,
968
69b954343acc Add missing FK-constraint.
Tom Gottfried <tom@intevation.de>
parents: 965
diff changeset
186 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
187 beschreibung character varying(300),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
188 mess_stelle character varying(60),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
189 mst_typ character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
190 amtskennung character varying(6)
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
191 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
192
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
193
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
194 CREATE TABLE auth_funktion (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
195 id smallint PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
196 funktion character varying(40) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 );
869
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
198 INSERT INTO auth_funktion VALUES (0, 'Erfasser');
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
199 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser');
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
200 INSERT INTO auth_funktion VALUES (2, 'Status-Land');
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
201 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle');
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
202 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
203
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
204
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
205 CREATE TABLE auth (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
206 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
207 ldap_group character varying(40) NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
208 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
209 mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
210 labor_mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
211 funktion_id smallint REFERENCES auth_funktion
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
212 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
213
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
215 CREATE TABLE auth_lst_umw (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
216 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
217 mst_id character varying(5) REFERENCES mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
218 umw_id character varying(3) REFERENCES umwelt
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
219 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
222 CREATE TABLE datenbasis (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
223 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
224 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
225 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
226 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
229 CREATE TABLE datensatz_erzeuger (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
230 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
231 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
232 datensatz_erzeuger_id character varying(2) NOT NULL,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
233 mst_id character varying(5) NOT NULL REFERENCES mess_stelle,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
234 bezeichnung character varying(120) NOT NULL,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
235 letzte_aenderung timestamp without time zone,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
236 UNIQUE(datensatz_erzeuger_id, netzbetreiber_id)
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
237 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
238 CREATE TRIGGER letzte_aenderung_datensatz_erzeuger BEFORE UPDATE ON datensatz_erzeuger FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
239
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
240
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
241 CREATE TABLE deskriptor_umwelt (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
242 id serial PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245 s02 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246 s03 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247 s04 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
248 s05 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 s06 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250 s07 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251 s08 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252 s09 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
253 s10 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
254 s11 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
255 s12 integer,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
256 umw_id character varying(3) NOT NULL REFERENCES umwelt
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
257 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
258
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
259
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
260 CREATE TABLE deskriptoren (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
261 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
262 vorgaenger integer REFERENCES deskriptoren,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
263 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
264 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
265 sn smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
266 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
267 bedeutung character varying(300)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
268 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
269
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
270
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
271 CREATE TABLE lada_user (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
272 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
273 name character varying(80) NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
274 UNIQUE (name)
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
275 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
276
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
277
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
278 CREATE TABLE query_type (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
279 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
280 type character varying(30) NOT NULL
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
281 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
282 INSERT INTO query_type VALUES(0, 'probe');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
283 INSERT INTO query_type VALUES(1, 'messung');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
284 INSERT INTO query_type VALUES(2, 'messprogramm');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
285 INSERT INTO query_type VALUES(3, 'ort');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
286 INSERT INTO query_type VALUES(4, 'probenehmer');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
287 INSERT INTO query_type VALUES(5, 'datensatzerzeuger');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
288 INSERT INTO query_type VALUES(6, 'messprogrammkategorie');
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
289
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
290
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
291 CREATE TABLE query (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
292 id serial PRIMARY KEY,
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
293 name character varying(80) NOT NULL,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
294 type integer NOT NULL REFERENCES query_type,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
295 sql character varying(2500) NOT NULL,
1093
effef57f3f7e Complement and correct data model and docs for queries.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
296 description character varying(100),
effef57f3f7e Complement and correct data model and docs for queries.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
297 UNIQUE (name, type)
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
298 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
299
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
300
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
301 CREATE TABLE favorite (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
302 id serial PRIMARY KEY,
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
303 user_id integer NOT NULL REFERENCES lada_user,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
304 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
305 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
306
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
307
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
308 CREATE TABLE filter_type (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
309 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
310 type character varying(10) NOT NULL
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
311 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
312 INSERT INTO filter_type VALUES(0, 'text');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
313 INSERT INTO filter_type VALUES(1, 'listmst');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
314 INSERT INTO filter_type VALUES(2, 'listnetz');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
315 INSERT INTO filter_type VALUES(3, 'listumw');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
316 INSERT INTO filter_type VALUES(4, 'liststatus');
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
317 INSERT INTO filter_type VALUES(5, 'number');
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
318
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
319
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
320 CREATE TABLE filter (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
321 id serial PRIMARY KEY,
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
322 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
323 data_index character varying(50) NOT NULL,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
324 type integer NOT NULL REFERENCES filter_type,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
325 label character varying(50) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
326 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
327 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
328
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
329
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
330 CREATE TABLE filter_value (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
331 id serial PRIMARY KEY,
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
332 user_id integer NOT NULL REFERENCES lada_user,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
333 filter_id integer NOT NULL REFERENCES filter ON DELETE CASCADE,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
334 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
335 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
336
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
337
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
338 CREATE TABLE mess_methode (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
339 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
340 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
341 messmethode character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
342 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345 CREATE TABLE messgroesse (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
346 id serial PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
347 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
348 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
349 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
350 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
351 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
352 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
353 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
354 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
355
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
356
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
357 CREATE TABLE messgroessen_gruppe (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
358 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
359 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
360 ist_leitnuklidgruppe character(1) DEFAULT NULL::bpchar
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
361 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
362
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
363
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
364 CREATE TABLE messprogramm_kategorie (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
365 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
366 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
367 code character varying(3) NOT NULL,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
368 bezeichnung character varying(120) NOT NULL,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
369 letzte_aenderung timestamp without time zone,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
370 UNIQUE(code, netzbetreiber_id)
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
371 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
372 CREATE TRIGGER letzte_aenderung_messprogramm_kategorie BEFORE UPDATE ON messprogramm_kategorie FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
373
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
374
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
375 CREATE TABLE mg_grp (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
376 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
377 messgroesse_id integer NOT NULL REFERENCES messgroesse
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
378 );
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
379 ALTER TABLE ONLY mg_grp
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
380 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
381
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
382
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
383 CREATE TABLE mmt_messgroesse_grp (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
384 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
385 mmt_id character varying(2) NOT NULL REFERENCES mess_methode
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
386 );
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
387 ALTER TABLE ONLY mmt_messgroesse_grp
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
388 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
389
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
390
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
391
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
392
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
393 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
394 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
395 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
396 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
397 mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
398 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
399
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
400
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
401 CREATE TABLE ort_typ (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
402 id smallint PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
403 ort_typ character varying(60)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
404 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
405
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
406 CREATE TABLE kta (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
407 id serial NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
408 code character varying(7),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
409 bezeichnung character varying(80),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
410 CONSTRAINT kta_pkey PRIMARY KEY (id)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
411 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
412 COMMENT ON TABLE kta
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
413 IS 'kernteschnische Anlagen';
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
414
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
415 CREATE TABLE ortszusatz (
1143
b49c1fb84040 Updated stammdaten schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1119
diff changeset
416 ozs_id character varying(7) PRIMARY KEY,
b49c1fb84040 Updated stammdaten schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1119
diff changeset
417 ortszusatz character varying(80) NOT NULL
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
418 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
419
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
420 CREATE TABLE ort (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
421 id serial PRIMARY KEY,
912
d8c66007fa14 netzbetreiber_id is mandatory for ort.
Tom Gottfried <tom@intevation.de>
parents: 905
diff changeset
422 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
423 ort_id character varying(10) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
424 langtext character varying(100) NOT NULL,
1176
33dc7dc6b7bc Removed NOT NULL constraint from stammdaten.ort.staat_id.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1175
diff changeset
425 staat_id smallint REFERENCES staat,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
426 gem_id character varying(8) REFERENCES verwaltungseinheit,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
427 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
428 nuts_code character varying(10),
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
429 kda_id integer NOT NULL REFERENCES koordinaten_art,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
430 koord_x_extern character varying(22) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
431 koord_y_extern character varying(22) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
432 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
433 letzte_aenderung timestamp without time zone DEFAULT now(),
1213
aed3332cfacc An Ort always has a location which is created from respective input.
Tom Gottfried <tom@intevation.de>
parents: 1212
diff changeset
434 geom public.geometry(Point,4326) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435 shape public.geometry(MultiPolygon,4326),
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
436 ort_typ smallint REFERENCES ort_typ,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
437 kurztext character varying(15) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
438 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
439 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
440 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
441 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
442 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
443 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
444 anlage_id integer,
1143
b49c1fb84040 Updated stammdaten schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1119
diff changeset
445 oz_id character varying(7) REFERENCES ortszusatz(ozs_id),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
446 hoehe_ueber_nn real,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
447 UNIQUE(ort_id, netzbetreiber_id)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
448 );
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
449
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
450 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1175
60b91dbb98cc Added trigger to fill stammdaten.ort attributes.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1143
diff changeset
451 CREATE TRIGGER set_ort_id_ort BEFORE INSERT ON ort FOR EACH ROW EXECUTE PROCEDURE set_ort_id();
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
452
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
453 ALTER TABLE ONLY ort
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
454 ADD CONSTRAINT ort_kta_fkey FOREIGN KEY (anlage_id) REFERENCES kta(id);
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
455
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
456
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
457 CREATE TABLE ortszuordnung_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
458 id character(1) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459 ortstyp character varying(60)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
462
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
463 CREATE TABLE pflicht_messgroesse (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
464 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
465 messgroesse_id integer,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
466 mmt_id character varying(2) REFERENCES mess_methode,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
467 umw_id character varying(3) REFERENCES umwelt,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
468 datenbasis_id smallint NOT NULL REFERENCES datenbasis
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
469 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
470
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
472 CREATE TABLE proben_zusatz (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
473 id character varying(3) PRIMARY KEY,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
474 meh_id integer REFERENCES mess_einheit,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
475 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
476 zusatzwert character varying(7) NOT NULL,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
477 eudf_keyword character varying(40),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
478 UNIQUE (eudf_keyword)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
479 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
480
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
481
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
482 CREATE TABLE probenart (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
483 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
484 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
485 probenart character varying(5) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
486 probenart_eudf_id character varying(1) NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
487 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
488
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
489
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
490 CREATE TABLE probenehmer (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
491 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
492 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
493 prn_id character varying(9) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
494 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
495 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
496 betrieb character varying(80),
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
497 bezeichnung character varying(80) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
498 kurz_bezeichnung character varying(10) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
499 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
500 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
501 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
502 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
503 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
504 typ character(1),
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
505 letzte_aenderung timestamp without time zone,
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
506 UNIQUE(prn_id, netzbetreiber_id)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
507 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
508 CREATE TRIGGER letzte_aenderung_probenehmer BEFORE UPDATE ON probenehmer FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
509
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
510
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
511 CREATE TABLE result (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
512 id serial PRIMARY KEY,
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
513 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
514 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
515 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
516 width integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
517 flex boolean,
1093
effef57f3f7e Complement and correct data model and docs for queries.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
518 index integer NOT NULL,
effef57f3f7e Complement and correct data model and docs for queries.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
519 UNIQUE (query_id, index),
988
5efb32a41b24 Fix CHECK-Constraint definition.
Tom Gottfried <tom@intevation.de>
parents: 987
diff changeset
520 UNIQUE (query_id, data_index)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
521 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
522
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
523
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
524 -- Status workflow
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
525 CREATE TABLE status_stufe (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
526 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
527 stufe character varying(50) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
528 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
529 INSERT INTO status_stufe VALUES (1, 'MST');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
530 INSERT INTO status_stufe VALUES (2, 'LAND');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
531 INSERT INTO status_stufe VALUES (3, 'LST');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
532
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
533
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
534 CREATE TABLE status_wert (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
535 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
536 wert character varying(50) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
537 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
538 INSERT INTO status_wert VALUES (0, 'nicht vergeben');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
539 INSERT INTO status_wert VALUES (1, 'plausibel');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
540 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
541 INSERT INTO status_wert VALUES (3, 'nicht plausibel');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
542 INSERT INTO status_wert VALUES (4, 'Rückfrage');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
543 INSERT INTO status_wert VALUES (7, 'nicht lieferbar');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
544 INSERT INTO status_wert VALUES (8, 'zurückgesetzt');
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
545
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
546
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
547 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
548 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
549 stufe_id integer REFERENCES status_stufe NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
550 wert_id integer REFERENCES status_wert NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
551 UNIQUE(stufe_id, wert_id)
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
552 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
553 INSERT INTO status_kombi VALUES (1, 1, 0);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
554 INSERT INTO status_kombi VALUES (2, 1, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
555 INSERT INTO status_kombi VALUES (3, 1, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
556 INSERT INTO status_kombi VALUES (4, 1, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
557 INSERT INTO status_kombi VALUES (5, 1, 7);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
558 INSERT INTO status_kombi VALUES (6, 2, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
559 INSERT INTO status_kombi VALUES (7, 2, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
560 INSERT INTO status_kombi VALUES (8, 2, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
561 INSERT INTO status_kombi VALUES (9, 2, 4);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
562 INSERT INTO status_kombi VALUES (10, 3, 1);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
563 INSERT INTO status_kombi VALUES (11, 3, 2);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
564 INSERT INTO status_kombi VALUES (12, 3, 3);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
565 INSERT INTO status_kombi VALUES (13, 3, 4);
1115
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
566 INSERT INTO status_kombi VALUES (14, 1, 8);
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
567 INSERT INTO status_kombi VALUES (15, 2, 8);
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
568 INSERT INTO status_kombi VALUES (16, 3, 8);
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
569
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
570
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
571 CREATE TABLE status_reihenfolge (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
572 id serial PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
573 von_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
574 zu_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
575 UNIQUE(von_id, zu_id)
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
576 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
577
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
578 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
579 DECLARE kombi_from RECORD;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
580 DECLARE s_from integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
581 DECLARE w_from integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
582 DECLARE kombi_to RECORD;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
583 DECLARE s_to integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
584 DECLARE w_to integer;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
585
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
586 BEGIN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
587 FOR kombi_from IN SELECT * FROM status_kombi LOOP
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
588 s_from := kombi_from.stufe_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
589 w_from := kombi_from.wert_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
590
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
591 FOR kombi_to IN SELECT * FROM status_kombi LOOP
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
592 s_to := kombi_to.stufe_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
593 w_to := kombi_to.wert_id;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
594
884
fa7278bd8d73 Allow 'editing' of status 'Rückfrage'
Tom Gottfried <tom@intevation.de>
parents: 879
diff changeset
595 IF s_from = s_to AND w_to <> 0 THEN
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
596 -- At the same 'stufe', all permutations occur,
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
597 -- but 'nicht vergeben' is only allowed for von_id
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
598 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
599 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
600
1115
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
601 ELSEIF s_to = s_from + 1
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
602 AND w_from <> 0 AND w_from <> 4
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
603 AND w_from <> 8 AND w_to <> 8 THEN
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
604 -- Going to the next 'stufe' all available status_kombi are allowed
1115
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
605 -- in case current wert is not 'nicht vergeben', 'Rückfrage' or
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
606 -- 'zurückgesetzt' and we are not trying to set 'zurückgesetzt'
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
607 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
608 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
609
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
610 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
611 -- After 'Rückfrage' follows 'MST' with
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
612 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ'
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
613 INSERT INTO status_reihenfolge (von_id, zu_id)
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
614 VALUES (kombi_from.id, kombi_to.id);
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
615
1115
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
616 ELSEIF w_to = 8 AND s_from = s_to THEN
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
617 -- 'zurückgesetzt' can only be set on the same 'stufe'
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
618 INSERT INTO status_reihenfolge (von_id, zu_id)
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
619 VALUES (kombi_from.id, kombi_to.id);
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
620
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
621 ELSEIF w_from = 8 AND s_to = s_from - 1 THEN
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
622 -- after 'zurückgesetzt' always follows the next lower 'stufe'
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
623 INSERT INTO status_reihenfolge (von_id, zu_id)
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
624 VALUES (kombi_from.id, kombi_to.id);
2f42a7607bbd Client and server now expect these combinations to be modelled, too.
Tom Gottfried <tom@intevation.de>
parents: 1099
diff changeset
625
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
626 END IF;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
627 END LOOP;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
628 END LOOP;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
629 END;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
630 $$ LANGUAGE plpgsql;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
631
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
632 SELECT populate_status_reihenfolge();
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
633 DROP FUNCTION populate_status_reihenfolge();
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
634 ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
635 DROP SEQUENCE status_reihenfolge_id_seq;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
636
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
637
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
638 CREATE VIEW status_erreichbar AS (
879
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
639 SELECT r.id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
640 zu.wert_id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
641 zu.stufe_id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
642 von.wert_id AS cur_wert,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
643 von.stufe_id AS cur_stufe
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
644 FROM stammdaten.status_reihenfolge r
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
645 JOIN stammdaten.status_kombi von
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
646 ON von.id = r.von_id
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
647 JOIN stammdaten.status_kombi zu
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
648 ON zu.id = r.zu_id
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
649 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
650 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
651
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
652 -- Mappings for import
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
653
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
654 CREATE TABLE messprogramm_transfer (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
655 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
656 messprogramm_s character varying(1) NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
657 messprogramm_c character varying(100) NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
658 ba_id integer NOT NULL REFERENCES betriebsart,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
659 UNIQUE (messprogramm_s)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
660 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
661
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1093
diff changeset
662 -- Mappings for import
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
663
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
664 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
665
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
666
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
667 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)