Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1290:14876c62f692

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Push down refreshing of persisted objects deeper into the stack. There are more places besides creation of Probe objects where it is useful to return within the response what has been really written to the database (including modifications by the database itself) instead of merely the request data, e.g. creation of Ort objects, which includes database generated ort_ids.
author Tom Gottfried <tom@intevation.de>
date Wed, 08 Feb 2017 18:02:05 +0100
parents a356b818389a
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)