Mercurial > lada > lada-server
annotate db_schema/stammdaten_schema.sql @ 1028:1c41c7b8f7c2 schema-update
Updated server application to new database model. THIS IS STILL WIP!!!
author | Raimund Renkert <raimund.renkert@intevation.de> |
---|---|
date | Fri, 08 Jul 2016 15:32:36 +0200 |
parents | 8e5dcf7156c5 |
children | b29098d201c2 |
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 |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
17 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
|
18 LANGUAGE plpgsql |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
19 AS $$ |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
20 BEGIN |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
21 NEW.letzte_aenderung = now(); |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
22 RETURN NEW; |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
23 END; |
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
24 $$; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 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
|
27 LANGUAGE plpgsql |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 AS $$ |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 declare |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 result character varying(100); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 d00 smallint; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 d01 smallint; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 d02 smallint; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 d03 smallint; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 begin |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 if media_desk like 'D: %' then |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 d00 := substring(media_desk,4,2); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 d01 := substring(media_desk,7,2); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 d02 := substring(media_desk,10,2); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 d03 := substring(media_desk,13,2); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 if d00 = '00' then |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 result := null; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 else |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 if d01 = '00' then |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 select s00.beschreibung into result FROM stammdaten.deskriptoren s00 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 where s00.ebene = 0 and s00.sn = d00::smallint; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 else |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 if d02 = '00' or d00 <> '01' then |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 select s01.beschreibung into result FROM stammdaten.deskriptoren s01 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 where s01.ebene = 1 and s01.sn = d01::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 and s01.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 (select s00.id FROM stammdaten.deskriptoren s00 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 where s00.ebene = 0 and s00.sn = d00::smallint); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 else |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 if d03 = '00' then |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 select s02.beschreibung into result FROM stammdaten.deskriptoren s02 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 where s02.ebene = 2 and s02.sn = d02::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 and s02.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
59 (select s01.id FROM stammdaten.deskriptoren s01 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
60 where s01.ebene = 1 and s01.sn = d01::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
61 and s01.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 (select s00.id FROM stammdaten.deskriptoren s00 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 where s00.ebene = 0 and s00.sn = d00::smallint)); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
64 else |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
65 select s03.beschreibung into result FROM stammdaten.deskriptoren s03 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 where s03.ebene = 3 and s03.sn = d03::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 and s03.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 (select s02.id FROM stammdaten.deskriptoren s02 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
69 where s02.ebene = 2 and s02.sn = d02::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 and s02.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
71 (select s01.id FROM stammdaten.deskriptoren s01 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 where s01.ebene = 1 and s01.sn = d01::smallint |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
73 and s01.vorgaenger = |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 (select s00.id FROM stammdaten.deskriptoren s00 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 where s00.ebene = 0 and s00.sn = d00::smallint))); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 end if; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
77 end if; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 end if; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 end if; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 else |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 result := null; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
82 end if; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 return (result); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
84 end; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
85 $$; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
86 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 |
1015
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
88 CREATE TABLE koordinaten_art ( |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
89 id serial PRIMARY KEY, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
90 koordinatenart character varying(50), |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
91 idf_geo_key character varying(1) |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
92 ); |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
93 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
94 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
95 CREATE TABLE mess_einheit ( |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
96 id serial PRIMARY KEY, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
97 beschreibung character varying(50), |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
98 einheit character varying(12), |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
99 eudf_messeinheit_id character varying(8), |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
100 umrechnungs_faktor_eudf bigint |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
101 ); |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
102 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
103 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
104 CREATE TABLE umwelt ( |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
105 id character varying(3) PRIMARY KEY, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
106 beschreibung character varying(300), |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
107 umwelt_bereich character varying(80) NOT NULL, |
1018
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
108 meh_id integer REFERENCES mess_einheit, |
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
109 UNIQUE (umwelt_bereich) |
1015
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
110 ); |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
111 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
112 |
1025
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
113 CREATE TABLE betriebsart ( |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
114 id serial PRIMARY KEY, |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
115 name character varying(30) NOT NULL |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
116 ); |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
117 INSERT INTO betriebsart VALUES(0, 'Normal-/Routinebetrieb'); |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
118 INSERT INTO betriebsart VALUES(1, 'Störfall-/Intensivbetrieb'); |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
119 |
8e5dcf7156c5
Added table betriebsart
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1023
diff
changeset
|
120 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
121 CREATE TABLE staat ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
122 id serial PRIMARY KEY, |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
123 staat character varying(50) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
124 hkl_id smallint NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
125 staat_iso character varying(2) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
126 staat_kurz character varying(5), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
127 eu character(1) DEFAULT NULL::bpchar, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
128 koord_x_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
129 koord_y_extern character varying(22), |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
130 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
|
131 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
132 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
133 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
134 CREATE TABLE verwaltungseinheit ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
135 id character varying(8) PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
136 bundesland character varying(8) NOT NULL, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
137 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
|
138 kreis character varying(8), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
139 nuts character varying(10), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
140 regbezirk character varying(8), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
141 bezeichnung character varying(80) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
142 is_bundesland character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
143 is_gemeinde character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
144 is_landkreis character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
145 is_regbezirk character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
146 koord_x_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
147 koord_y_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
148 plz character varying(6), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
149 longitude double precision, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
150 latitude double precision |
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 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
153 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
154 CREATE TABLE netz_betreiber ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
155 id character varying(2) PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
156 netzbetreiber character varying(50), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
157 idf_netzbetreiber character varying(1), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
158 is_bmn boolean DEFAULT false, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
159 mailverteiler character varying(512), |
1012
c739d08040d6
Fixed names and typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1011
diff
changeset
|
160 aktiv boolean DEFAULT false |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
161 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
162 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
163 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
164 CREATE TABLE mess_stelle ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
165 id character varying(5) PRIMARY KEY, |
968
69b954343acc
Add missing FK-constraint.
Tom Gottfried <tom@intevation.de>
parents:
965
diff
changeset
|
166 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
|
167 beschreibung character varying(300), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
168 mess_stelle character varying(60), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
169 mst_typ character varying(1), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
170 amtskennung character varying(6) |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
171 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
172 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
173 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
174 CREATE TABLE auth_funktion ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
175 id smallint PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
176 funktion character varying(40) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
177 ); |
869
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
178 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
|
179 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
|
180 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
|
181 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
|
182 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
184 |
1015
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
185 CREATE TABLE auth ( |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
186 id serial PRIMARY KEY, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
187 ldap_group character varying(40) NOT NULL, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
188 netzbetreiber_id character varying(2) REFERENCES netz_betreiber, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
189 mst_id character varying(5) REFERENCES mess_stelle, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
190 labor_mst_id character varying(5) REFERENCES mess_stelle, |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
191 funktion_id smallint REFERENCES auth_funktion |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
192 ); |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
193 |
9f903c54141b
Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1014
diff
changeset
|
194 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
195 CREATE TABLE auth_lst_umw ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
196 id serial PRIMARY KEY, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
197 mst_id character varying(5) REFERENCES mess_stelle, |
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
198 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
|
199 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
201 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
202 CREATE TABLE datenbasis ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
203 id serial PRIMARY KEY, |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
204 beschreibung character varying(30), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
205 datenbasis character varying(6) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
206 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
207 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
208 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
209 CREATE TABLE datensatz_erzeuger ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
210 id serial PRIMARY KEY, |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
211 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, |
1011
302f800ba588
Renamed/Removed some columns.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
988
diff
changeset
|
212 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
|
213 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
|
214 bezeichnung character varying(120) NOT NULL, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
215 letzte_aenderung timestamp without time zone, |
1012
c739d08040d6
Fixed names and typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1011
diff
changeset
|
216 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
|
217 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
218 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
|
219 |
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 CREATE TABLE de_vg ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
222 id serial PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
223 use double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
224 rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
225 gf double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
226 rau_rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
227 gen character varying(50), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
228 des character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
229 isn double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
230 bemerk character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
231 nambild character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
232 ags character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
233 rs_alt character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
234 wirksamkei date, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
235 debkg_id character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
236 length numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
237 shape_area numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
238 geom public.geometry(MultiPolygon,4326) |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
242 CREATE TABLE deskriptor_umwelt ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
243 id integer PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
244 s00 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
245 s01 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
246 s02 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
247 s03 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
248 s04 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
249 s05 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
250 s06 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
251 s07 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
252 s08 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
253 s09 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
254 s10 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
255 s11 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
256 s12 integer, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
257 umw_id character varying(3) NOT NULL REFERENCES umwelt |
866
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
261 CREATE TABLE deskriptoren ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
262 id serial PRIMARY KEY, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
263 vorgaenger integer REFERENCES deskriptoren, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
264 ebene smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
265 s_xx integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
266 sn smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
267 beschreibung character varying(100), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
268 bedeutung character varying(300) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
269 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
270 |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
271 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
272 CREATE TABLE lada_user ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
273 id serial PRIMARY KEY, |
1018
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
274 name character varying(80) NOT NULL, |
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
275 UNIQUE (name) |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
276 ); |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
277 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
278 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
279 CREATE TABLE query ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
280 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
|
281 name character varying(80) NOT NULL, |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
282 type integer NOT NULL REFERENCES query_type, |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
283 sql character varying(1500) NOT NULL, |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
284 description character varying(100) |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
285 ); |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
286 |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
287 CREATE TABLE query_type ( |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
288 id serial PRIMARY KEY, |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
289 type character varying(30) NOT NULL |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
290 ): |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
291 INSERT INTO query_type VALUES(0, 'probe'); |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
292 INSERT INTO query_type VALUES(1, 'messung'); |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
293 INSERT INTO query_type VALUES(2, 'messprogramm'); |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
294 INSERT INTO query_type VALUES(3, 'ort'); |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
295 INSERT INTO query_type VALUES(4, 'probenehmer'); |
1023
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
296 INSERT INTO query_type VALUES(5, 'datensatzerzeuger'); |
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
297 INSERT INTO query_type VALUES(6, 'messprogrammkategorie'); |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
298 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
299 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
300 CREATE TABLE favorite ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
301 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
|
302 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
|
303 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
|
304 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
305 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
306 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
307 CREATE TABLE filter ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
308 id serial PRIMARY KEY, |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
309 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
|
310 data_index character varying(50) NOT NULL, |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
311 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
|
312 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
|
313 multiselect boolean |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
314 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
315 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
316 |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
317 CREATE TABLE filter_type ( |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
318 id serial PRIMARY KEY, |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
319 type character varying(10) NOT NULL |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
320 ) |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
321 INSERT INTO filter_type VALUES(0, 'text'); |
1023
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
322 INSERT INTO filter_type VALUES(1, 'listmst'); |
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
323 INSERT INTO filter_type VALUES(2, 'listnetz'); |
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
324 INSERT INTO filter_type VALUES(3, 'listumw'); |
5fdc6feabedb
Updated values.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1022
diff
changeset
|
325 INSERT INTO filter_type VALUES(4, 'liststatus'); |
1022
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
326 |
042437ce8f51
Added filter and query type table.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1018
diff
changeset
|
327 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
328 CREATE TABLE filter_value ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
329 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
|
330 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
|
331 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
|
332 value text |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
333 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
334 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
335 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
336 CREATE TABLE mess_methode ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
337 id character varying(2) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
338 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
339 messmethode character varying(50) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
340 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
341 |
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 CREATE TABLE messgroesse ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
344 id serial PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
345 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
346 messgroesse character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
347 default_farbe character varying(9), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
348 idf_nuklid_key character varying(6), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
349 ist_leitnuklid boolean DEFAULT false, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
350 eudf_nuklid_id bigint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
351 kennung_bvl character varying(7) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
352 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
353 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
354 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
355 CREATE TABLE messgroessen_gruppe ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
356 id serial PRIMARY KEY, |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
357 bezeichnung character varying(80), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
358 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
|
359 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
360 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
361 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
362 CREATE TABLE messprogramm_kategorie ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
363 id serial PRIMARY KEY, |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
364 netzbetreiber_id character varying(2) NOT NULL REFERENCES netz_betreiber, |
1011
302f800ba588
Renamed/Removed some columns.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
988
diff
changeset
|
365 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
|
366 bezeichnung character varying(120) NOT NULL, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
367 letzte_aenderung timestamp without time zone, |
1013
48c07fc5d2d1
Fixed column names in constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1012
diff
changeset
|
368 UNIQUE(code, netzbetreiber_id) |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
369 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
370 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
|
371 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
372 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
373 CREATE TABLE mg_grp ( |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
374 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
375 messgroesse_id integer NOT NULL REFERENCES messgroesse |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
376 ); |
1017
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
377 ALTER TABLE ONLY mg_grp |
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
378 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
|
379 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
380 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
381 CREATE TABLE mmt_messgroesse_grp ( |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
382 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe, |
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
383 mmt_id character varying(2) NOT NULL REFERENCES mess_methode |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
384 ); |
1017
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
385 ALTER TABLE ONLY mmt_messgroesse_grp |
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
386 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id); |
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
387 |
dd86066d7515
Made most constraints inline.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1016
diff
changeset
|
388 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
389 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
390 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
391 CREATE VIEW mmt_messgroesse AS |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
392 SELECT mmt_messgroesse_grp.mmt_id, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
393 mg_grp.messgroesse_id |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
394 FROM mmt_messgroesse_grp, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
395 mg_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
396 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
397 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
398 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
399 CREATE TABLE ort_typ ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
400 id smallint PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
401 ort_typ character varying(60) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
402 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
403 |
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 CREATE TABLE ort ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
406 id serial PRIMARY KEY, |
912
d8c66007fa14
netzbetreiber_id is mandatory for ort.
Tom Gottfried <tom@intevation.de>
parents:
905
diff
changeset
|
407 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
|
408 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
|
409 langtext character varying(100) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
410 staat_id smallint NOT NULL REFERENCES staat, |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
411 gem_id character varying(8) REFERENCES verwaltungseinheit, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
412 unscharf character(1) DEFAULT NULL::bpchar, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
413 nuts_code character varying(10), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
414 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
|
415 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
|
416 koord_y_extern character varying(22) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
417 hoehe_land real, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
418 letzte_aenderung timestamp without time zone DEFAULT now(), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
419 latitude double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
420 longitude double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
421 geom public.geometry(Point,4326), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
422 shape public.geometry(MultiPolygon,4326), |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
423 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
|
424 kurztext character varying(15) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
425 berichtstext character varying(70), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
426 zone character varying(1), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
427 sektor character varying(2), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
428 zustaendigkeit character varying(10), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
429 mp_art character varying(10), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
430 aktiv character(1), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
431 anlage_id integer, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
432 oz_id integer, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
433 UNIQUE(ort_id, netzbetreiber_id) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
434 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
435 CREATE TRIGGER letzte_aenderung_ort BEFORE UPDATE ON ort FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
436 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
437 ALTER TABLE ONLY ort |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
438 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
439 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
440 ALTER TABLE ONLY ort |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
441 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
442 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
443 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
444 CREATE TABLE ortszuordnung_typ ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
445 id character(1) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
446 ortstyp character varying(60) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
447 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
448 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
449 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
450 CREATE TABLE pflicht_messgroesse ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
451 id serial PRIMARY KEY, |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
452 messgroesse_id integer, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
453 mmt_id character varying(2) REFERENCES mess_methode, |
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
454 umw_id character varying(3) REFERENCES umwelt, |
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
455 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
|
456 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
457 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
458 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
459 CREATE TABLE proben_zusatz ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
460 id character varying(3) PRIMARY KEY, |
1016
8912b6f1776d
Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1015
diff
changeset
|
461 meh_id integer REFERENCES mess_einheit, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
462 beschreibung character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
463 zusatzwert character varying(7) NOT NULL, |
1018
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
464 eudf_keyword character varying(40), |
178ac8ee17c0
Fixed typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1017
diff
changeset
|
465 UNIQUE (eudf_keyword) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
466 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
467 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
468 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
469 CREATE TABLE probenart ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
470 id serial PRIMARY KEY, |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
471 beschreibung character varying(30), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
472 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
|
473 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
|
474 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
475 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
476 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
477 CREATE TABLE probenehmer ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
478 id serial PRIMARY KEY, |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
479 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
|
480 prn_id character varying(9) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
481 bearbeiter character varying(25), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
482 bemerkung character varying(60), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
483 betrieb character varying(80), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
484 bezeichnung character varying(80) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
485 kurz_bezeichnung character varying(10) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
486 ort character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
487 plz character varying(5), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
488 strasse character varying(30), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
489 telefon character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
490 tp character varying(3), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
491 typ character(1), |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
492 letzte_aenderung timestamp without time zone, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
493 UNIQUE(prn_id, netzbetreiber_id) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
494 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
495 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
|
496 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
497 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
498 CREATE TABLE result ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
499 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
|
500 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
501 data_index character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
502 header character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
503 width integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
504 flex boolean, |
988
5efb32a41b24
Fix CHECK-Constraint definition.
Tom Gottfried <tom@intevation.de>
parents:
987
diff
changeset
|
505 index integer, |
5efb32a41b24
Fix CHECK-Constraint definition.
Tom Gottfried <tom@intevation.de>
parents:
987
diff
changeset
|
506 UNIQUE (query_id, data_index) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
507 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
508 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
509 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
510 -- Status workflow |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
511 CREATE TABLE status_stufe ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
512 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
513 stufe character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
514 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
515 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
|
516 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
|
517 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
|
518 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
519 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
520 CREATE TABLE status_wert ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
521 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
522 wert character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
523 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
524 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
|
525 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
|
526 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
|
527 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
|
528 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
|
529 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
|
530 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
|
531 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
532 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
533 CREATE TABLE status_kombi ( |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
534 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
535 stufe_id integer REFERENCES status_stufe NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
536 wert_id integer REFERENCES status_wert NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
537 UNIQUE(stufe_id, wert_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
538 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
539 -- 'zurückgesetzt' is left out here deliberately! |
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_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
|
541 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
|
542 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
|
543 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
|
544 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
|
545 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
|
546 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
|
547 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
|
548 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
|
549 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
|
550 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
|
551 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
|
552 INSERT INTO status_kombi VALUES (13, 3, 4); |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
553 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
554 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
555 CREATE TABLE status_reihenfolge ( |
1014
6b204c617c1f
Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1013
diff
changeset
|
556 id serial PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
557 von_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
558 zu_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
559 UNIQUE(von_id, zu_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
560 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
561 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
562 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
|
563 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
|
564 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
|
565 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
|
566 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
|
567 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
|
568 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
|
569 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
570 BEGIN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
571 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
|
572 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
|
573 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
|
574 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
575 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
|
576 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
|
577 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
|
578 |
884
fa7278bd8d73
Allow 'editing' of status 'Rückfrage'
Tom Gottfried <tom@intevation.de>
parents:
879
diff
changeset
|
579 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
|
580 -- 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
|
581 -- 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
|
582 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
|
583 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
|
584 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
585 ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
586 -- Going to the next 'stufe' all available status_kombi are allowed |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
587 -- in case current wert is not 'nicht vergeben' or '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
|
588 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
|
589 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
|
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 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
|
592 -- 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
|
593 -- '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
|
594 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
|
595 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
|
596 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
597 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
|
598 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
|
599 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
|
600 END; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
601 $$ 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
|
602 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
603 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
|
604 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
|
605 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
|
606 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
|
607 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
608 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
609 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
|
610 SELECT r.id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
611 zu.wert_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
612 zu.stufe_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
613 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
|
614 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
|
615 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
|
616 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
|
617 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
|
618 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
|
619 ON zu.id = r.zu_id |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
620 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
621 -- Status workflow |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
622 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
623 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
624 CREATE INDEX de_vg_geom_gist ON de_vg USING gist (geom); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
625 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
626 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
627 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
628 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
629 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
630 COMMIT; |