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