Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1275:a356b818389a

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