Mercurial > lada > lada-server

annotate db_schema/stammdaten_schema.sql @ 1016:8912b6f1776d schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added inline FK constraints.
author Raimund Renkert <raimund.renkert@intevation.de>
date Mon, 04 Jul 2016 12:05:26 +0200
parents 9f903c54141b
children dd86066d7515
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,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
108 meh_id integer REFERENCES mess_einheit
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
109 );
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
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
112 CREATE TABLE staat (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
113 id serial PRIMARY KEY,
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
114 staat character varying(50) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
115 hkl_id smallint NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
116 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
117 staat_kurz character varying(5),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
118 eu character(1) DEFAULT NULL::bpchar,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
119 koord_x_extern character varying(22),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
120 koord_y_extern character varying(22),
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
121 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
122 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
123
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
124
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
125 CREATE TABLE verwaltungseinheit (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
126 id character varying(8) PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
127 bundesland character varying(8) NOT NULL,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
128 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
129 kreis character varying(8),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
130 nuts character varying(10),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
131 regbezirk character varying(8),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
132 bezeichnung character varying(80) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
133 is_bundesland character(1) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
134 is_gemeinde character(1) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
135 is_landkreis character(1) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
136 is_regbezirk character(1) NOT NULL,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
137 koord_x_extern character varying(22),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
138 koord_y_extern character varying(22),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
139 plz character varying(6),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
140 longitude double precision,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
141 latitude double precision
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
142 );
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
143
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
144
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
145 CREATE TABLE netz_betreiber (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
146 id character varying(2) PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
147 netzbetreiber character varying(50),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
148 idf_netzbetreiber character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
149 is_bmn boolean DEFAULT false,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
150 mailverteiler character varying(512),
1012
c739d08040d6 Fixed names and typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1011
diff changeset
151 aktiv boolean DEFAULT false
902
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
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
155 CREATE TABLE mess_stelle (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
156 id character varying(5) PRIMARY KEY,
968
69b954343acc Add missing FK-constraint.
Tom Gottfried <tom@intevation.de>
parents: 965
diff changeset
157 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
158 beschreibung character varying(300),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
159 mess_stelle character varying(60),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
160 mst_typ character varying(1),
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
161 amtskennung character varying(6)
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
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
165 CREATE TABLE auth_funktion (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
166 id smallint PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
167 funktion character varying(40) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168 );
869
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
169 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
170 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
171 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
172 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
173 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
175
1015
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
176 CREATE TABLE auth (
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
177 id serial PRIMARY KEY,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
178 ldap_group character varying(40) NOT NULL,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
179 netzbetreiber_id character varying(2) REFERENCES netz_betreiber,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
180 mst_id character varying(5) REFERENCES mess_stelle,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
181 labor_mst_id character varying(5) REFERENCES mess_stelle,
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
182 funktion_id smallint REFERENCES auth_funktion
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
183 );
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
184
9f903c54141b Reordered tables.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1014
diff changeset
185
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
186 CREATE TABLE auth_lst_umw (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
187 id serial PRIMARY KEY,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
188 mst_id character varying(5) REFERENCES mess_stelle,
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
189 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
190 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
193 CREATE TABLE datenbasis (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
194 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
195 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
196 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
197 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
200 CREATE TABLE datensatz_erzeuger (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
201 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
202 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
203 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
204 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
205 bezeichnung character varying(120) NOT NULL,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
206 letzte_aenderung timestamp without time zone,
1012
c739d08040d6 Fixed names and typos.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1011
diff changeset
207 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
208 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
209 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
210
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
211
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
212 CREATE TABLE de_vg (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
213 id serial PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214 use double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
215 rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
216 gf double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
217 rau_rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
218 gen character varying(50),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
219 des character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220 isn double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 bemerk character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222 nambild character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
223 ags character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224 rs_alt character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 wirksamkei date,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
226 debkg_id character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227 length numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228 shape_area numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
229 geom public.geometry(MultiPolygon,4326)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
230 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
231
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
232
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
233 CREATE TABLE deskriptor_umwelt (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
234 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
235 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
236 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
237 s02 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
238 s03 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
239 s04 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
240 s05 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
241 s06 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
242 s07 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243 s08 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244 s09 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245 s10 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246 s11 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247 s12 integer,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
248 umw_id character varying(3) NOT NULL REFERENCES umwelt
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252 CREATE TABLE deskriptoren (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
253 id serial PRIMARY KEY,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
254 vorgaenger integer REFERENCES deskriptoren,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
255 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
256 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
257 sn smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
258 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
259 bedeutung character varying(300)
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
954
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
262
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
263 CREATE TABLE lada_user (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
264 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
265 name character varying(80) NOT NULL
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
266 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
267
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
268
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
269 CREATE TABLE query (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
270 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
271 name character varying(80) NOT NULL,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
272 type character varying(30) NOT NULL,
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
273 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
274 description character varying(100)
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
275 );
379480a94c81 Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents: 926
diff changeset
276
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
277
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
278 CREATE TABLE favorite (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
279 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
280 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
281 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
282 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
283
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
284
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
285 CREATE TABLE filter (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
286 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
287 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
288 data_index character varying(50) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
289 type character varying(10) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
290 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
291 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
292 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
293
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
294
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
295 CREATE TABLE filter_value (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
296 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
297 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
298 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
299 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
300 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
301
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
302
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
303 CREATE TABLE mess_methode (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
304 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
305 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
306 messmethode character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
307 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
308
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
309
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
310 CREATE TABLE messgroesse (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
311 id serial PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
312 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
313 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
314 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
315 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
316 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
317 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
318 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
319 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
320
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
321
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
322 CREATE TABLE messgroessen_gruppe (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
323 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
324 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
325 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
326 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
327
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
328
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
329 CREATE TABLE messprogramm_kategorie (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
330 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
331 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
332 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
333 bezeichnung character varying(120) NOT NULL,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
334 letzte_aenderung timestamp without time zone,
1013
48c07fc5d2d1 Fixed column names in constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1012
diff changeset
335 UNIQUE(code, netzbetreiber_id)
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
336 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
337 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
338
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
339
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
340 CREATE TABLE mg_grp (
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
341 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
342 messgroesse_id integer NOT NULL REFERENCES messgroesse
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
346 CREATE TABLE mmt_messgroesse_grp (
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
347 messgroessengruppe_id integer NOT NULL REFERENCES messgroessen_gruppe,
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
348 mmt_id character varying(2) NOT NULL REFERENCES mess_methode
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
349 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
350
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
351
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
352 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
353 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
354 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
355 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
356 mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
357 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
358
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
359
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
360 CREATE TABLE ort_typ (
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
361 id smallint PRIMARY KEY,
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
362 ort_typ character varying(60)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
363 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
364
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
365
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
366 CREATE TABLE ort (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
367 id serial PRIMARY KEY,
912
d8c66007fa14 netzbetreiber_id is mandatory for ort.
Tom Gottfried <tom@intevation.de>
parents: 905
diff changeset
368 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
369 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
370 langtext character varying(100) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
371 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
372 gem_id character varying(8) REFERENCES verwaltungseinheit,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
373 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
374 nuts_code character varying(10),
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
375 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
376 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
377 koord_y_extern character varying(22) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
378 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
379 letzte_aenderung timestamp without time zone DEFAULT now(),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
380 latitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
381 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
382 geom public.geometry(Point,4326),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
383 shape public.geometry(MultiPolygon,4326),
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
384 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
385 kurztext character varying(15) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
386 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
387 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
388 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
389 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
390 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
391 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
392 anlage_id integer,
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
393 oz_id integer,
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
394 UNIQUE(ort_id, netzbetreiber_id)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
395 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
396 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
397
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
398 ALTER TABLE ONLY ort
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
399 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
400
902
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
401 ALTER TABLE ONLY ort
d465094946ef Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents: 891
diff changeset
402 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
403
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
404
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
405 CREATE TABLE ortszuordnung_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
406 id character(1) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
407 ortstyp character varying(60)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
408 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
409
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
410
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
411 CREATE TABLE pflicht_messgroesse (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
412 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
413 messgroesse_id integer,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
414 mmt_id character varying(2) REFERENCES mess_methode,
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
415 umw_id character varying(3) REFERENCES umwelt,
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
416 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
417 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
418
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
419
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
420 CREATE TABLE proben_zusatz (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
421 id character varying(3) PRIMARY KEY,
1016
8912b6f1776d Added inline FK constraints.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1015
diff changeset
422 meh_id integer REFERENCES mess_einheit,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
423 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
424 zusatzwert character varying(7) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
425 eudf_keyword character varying(40)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
426 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
427
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
428
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
429 CREATE TABLE probenart (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
430 id serial PRIMARY KEY,
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
431 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
432 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
433 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
434 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
436
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
437 CREATE TABLE probenehmer (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
438 id serial PRIMARY KEY,
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
439 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
440 prn_id character varying(9) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
441 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
442 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
443 betrieb character varying(80),
904
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
444 bezeichnung character varying(80) NOT NULL,
825d82b8ad6e Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents: 902
diff changeset
445 kurz_bezeichnung character varying(10) NOT NULL,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
446 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
447 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
448 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
449 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
450 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
451 typ character(1),
905
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
452 letzte_aenderung timestamp without time zone,
df2908334150 Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents: 904
diff changeset
453 UNIQUE(prn_id, netzbetreiber_id)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
454 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 912
diff changeset
455 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
456
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 CREATE TABLE result (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
459 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
460 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
462 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
463 width integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
464 flex boolean,
988
5efb32a41b24 Fix CHECK-Constraint definition.
Tom Gottfried <tom@intevation.de>
parents: 987
diff changeset
465 index integer,
5efb32a41b24 Fix CHECK-Constraint definition.
Tom Gottfried <tom@intevation.de>
parents: 987
diff changeset
466 UNIQUE (query_id, data_index)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
467 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
468
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
469
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
470 -- Status workflow
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471 CREATE TABLE status_stufe (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
472 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
473 stufe character varying(50) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
474 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
475 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
476 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
477 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
478
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
479
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
480 CREATE TABLE status_wert (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
481 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
482 wert character varying(50) UNIQUE NOT NULL
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
483 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
484 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
485 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
486 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
487 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
488 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
489 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
490 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
491
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
492
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
493 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
494 id integer PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
495 stufe_id integer REFERENCES status_stufe NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
496 wert_id integer REFERENCES status_wert NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
497 UNIQUE(stufe_id, wert_id)
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
498 );
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
499 -- '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
500 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
501 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
502 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
503 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
504 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
505 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
506 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
507 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
508 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
509 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
510 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
511 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
512 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
513
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
514
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
515 CREATE TABLE status_reihenfolge (
1014
6b204c617c1f Replaced explicid sequences with serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1013
diff changeset
516 id serial PRIMARY KEY,
872
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
517 von_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
518 zu_id integer REFERENCES status_kombi NOT NULL,
e161d8f2d978 Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents: 871
diff changeset
519 UNIQUE(von_id, zu_id)
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
520 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
521
871
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
522 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
523 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
524 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
525 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
526 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
527 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
528 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
529
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
530 BEGIN
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
531 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
532 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
533 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
534
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
535 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
536 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
537 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
538
884
fa7278bd8d73 Allow 'editing' of status 'Rückfrage'
Tom Gottfried <tom@intevation.de>
parents: 879
diff changeset
539 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
540 -- 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
541 -- 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
542 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
543 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
544
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
545 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
546 -- 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
547 -- 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
548 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
549 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
550
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
551 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
552 -- 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
553 -- '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
554 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
555 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
556
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
557 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
558 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
559 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
560 END;
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
561 $$ 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
562
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
563 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
564 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
565 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
566 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
567
53d739275c50 Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 870
diff changeset
568
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
569 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
570 SELECT r.id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
571 zu.wert_id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
572 zu.stufe_id,
6bd149c352a9 Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents: 873
diff changeset
573 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
574 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
575 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
576 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
577 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
578 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
579 ON zu.id = r.zu_id
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
580 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
581 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
582
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
583
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
584 ALTER TABLE ONLY lada_user
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
585 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
586
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
587
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
588 ALTER TABLE ONLY mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
589 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
590
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
591
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
592 ALTER TABLE ONLY mmt_messgroesse_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
593 ADD CONSTRAINT mmt_messgroesse_grp_pkey PRIMARY KEY (messgroessengruppe_id, mmt_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
594
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
595
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
596 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
597 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
598
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
599
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
600 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
601 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
602
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
603
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
604 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
605
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
606
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
607 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
608
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
609
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
610 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)