Mercurial > lada > lada-server
annotate db_schema/stammdaten_schema.sql @ 975:5ec11036b4e2
Fix statusFilter query.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 08 Jun 2016 12:16:40 +0200 |
parents | 69b954343acc |
children | 35878d9b4321 |
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 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
88 CREATE SEQUENCE staat_id_seq |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
89 START WITH 1 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
90 INCREMENT BY 1 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
91 NO MINVALUE |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
92 NO MAXVALUE |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
93 CACHE 1; |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
94 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
95 CREATE TABLE staat ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
96 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
97 staat character varying(50) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
98 hkl_id smallint NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
99 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
|
100 staat_kurz character varying(5), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
101 eu character(1) DEFAULT NULL::bpchar, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
102 koord_x_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
103 koord_y_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
104 kda_id integer |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
105 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
106 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
107 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
108 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
109 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
110 CREATE TABLE verwaltungseinheit ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
111 id character varying(8) PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
112 bundesland character varying(8) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
113 kda_id integer, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
114 kreis character varying(8), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
115 nuts character varying(10), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
116 regbezirk character varying(8), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
117 bezeichnung character varying(80) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
118 is_bundesland character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
119 is_gemeinde character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
120 is_landkreis character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
121 is_regbezirk character(1) NOT NULL, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
122 koord_x_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
123 koord_y_extern character varying(22), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
124 plz character varying(6), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
125 longitude double precision, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
126 latitude double precision |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
127 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
128 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
129 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
130 CREATE TABLE netz_betreiber ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
131 id character varying(2) PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
132 netzbetreiber character varying(50), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
133 idf_netzbetreiber character varying(1), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
134 is_bmn boolean DEFAULT false, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
135 mailverteiler character varying(512), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
136 aktiv boolean DEFAULT false, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
137 zust_mst_id character varying(5) |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
138 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
139 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
140 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
141 CREATE TABLE mess_stelle ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
142 id character varying(5) PRIMARY KEY, |
968
69b954343acc
Add missing FK-constraint.
Tom Gottfried <tom@intevation.de>
parents:
965
diff
changeset
|
143 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
|
144 beschreibung character varying(300), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
145 mess_stelle character varying(60), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
146 mst_typ character varying(1), |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
147 amtskennung character varying(6) |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
148 ); |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
149 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
150 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
151 CREATE SEQUENCE auth_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
152 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
153 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
154 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
155 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
156 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
157 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
158 CREATE TABLE auth ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
159 id integer PRIMARY KEY DEFAULT nextval('auth_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
160 ldap_group character varying(40) NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
161 netzbetreiber_id character varying(2), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
162 mst_id character varying(5), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
163 labor_mst_id character varying(5), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
164 funktion_id smallint |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
165 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
166 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
167 ALTER SEQUENCE auth_id_seq OWNED BY auth.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
168 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
169 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
170 CREATE TABLE auth_funktion ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
171 id smallint PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
172 funktion character varying(40) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
173 ); |
869
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
174 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
|
175 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
|
176 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
|
177 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
|
178 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
179 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
180 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
181 CREATE SEQUENCE auth_lst_umw_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
182 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
184 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
186 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
187 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
188 CREATE TABLE auth_lst_umw ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
189 id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
190 lst_id character varying(5), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
191 umw_id character varying(3) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
192 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
193 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
194 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
195 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
197 CREATE SEQUENCE datenbasis_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
198 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
199 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
201 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
202 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
203 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
204 CREATE TABLE datenbasis ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
205 id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
206 beschreibung character varying(30), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
207 datenbasis character varying(6) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
208 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
209 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id; |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
213 CREATE SEQUENCE datensatz_erzeuger_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
214 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
215 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
216 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
217 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
218 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
219 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
220 CREATE TABLE datensatz_erzeuger ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
221 id integer PRIMARY KEY |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
222 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
223 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
|
224 da_erzeuger_id character varying(2) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
225 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
|
226 bezeichnung character varying(120) NOT NULL, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
227 letzte_aenderung timestamp without time zone, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
228 UNIQUE(da_erzeuger_id, netzbetreiber_id) |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
229 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
230 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
|
231 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
232 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
233 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
234 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
235 CREATE SEQUENCE de_vg_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
236 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
237 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
238 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
239 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
240 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
241 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
242 CREATE TABLE de_vg ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
243 id integer PRIMARY KEY DEFAULT nextval('de_vg_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
244 use double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
245 rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
246 gf double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
247 rau_rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
248 gen character varying(50), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
249 des character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
250 isn double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
251 bemerk character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
252 nambild character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
253 ags character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
254 rs_alt character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
255 wirksamkei date, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
256 debkg_id character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
257 length numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
258 shape_area numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
259 geom public.geometry(MultiPolygon,4326) |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
262 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
263 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
264 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
265 CREATE TABLE deskriptor_umwelt ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
266 id integer PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
267 s00 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
268 s01 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
269 s02 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
270 s03 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
271 s04 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
272 s05 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
273 s06 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
274 s07 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
275 s08 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
276 s09 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
277 s10 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
278 s11 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
279 s12 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
280 umw_id character varying(3) NOT NULL |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
281 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
282 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
283 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
284 CREATE SEQUENCE deskriptoren_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
285 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
286 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
287 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
288 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
289 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
290 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
291 CREATE TABLE deskriptoren ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
292 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
293 vorgaenger integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
294 ebene smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
295 s_xx integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
296 sn smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
297 beschreibung character varying(100), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
298 bedeutung character varying(300) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
299 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
300 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
301 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; |
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 |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
304 CREATE SEQUENCE lada_user_id_seq |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
305 START WITH 1 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
306 INCREMENT BY 1 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
307 NO MINVALUE |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
308 NO MAXVALUE |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
309 CACHE 1; |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
310 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
311 CREATE TABLE lada_user ( |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
312 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass), |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
313 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
|
314 ); |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
315 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
316 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id; |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
317 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
318 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
319 CREATE SEQUENCE query_id_seq |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
320 START WITH 1 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
321 INCREMENT BY 1 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
322 NO MINVALUE |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
323 NO MAXVALUE |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
324 CACHE 1; |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
325 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
326 CREATE TABLE query ( |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
327 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass), |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
328 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
|
329 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
|
330 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
|
331 description character varying(100) |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
332 ); |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
333 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
334 ALTER SEQUENCE query_id_seq OWNED BY query.id; |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
335 |
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
336 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
337 CREATE SEQUENCE favorite_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
338 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
339 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
340 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
341 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
342 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
343 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
344 CREATE TABLE favorite ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
345 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass), |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
346 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
|
347 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
|
348 ); |
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 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
353 CREATE SEQUENCE filter_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
354 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
355 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
356 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
357 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
358 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
359 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
360 CREATE TABLE filter ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
361 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass), |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
362 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
|
363 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
|
364 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
|
365 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
|
366 multiselect boolean |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
367 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
368 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
369 ALTER SEQUENCE filter_id_seq OWNED BY filter.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
370 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
371 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
372 CREATE SEQUENCE filter_value_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
373 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
374 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
375 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
376 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
377 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
378 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
379 CREATE TABLE filter_value ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
380 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass), |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
381 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
|
382 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
|
383 value text |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
384 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
385 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
386 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
387 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
388 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
389 CREATE SEQUENCE koordinaten_art_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
390 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
391 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
392 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
393 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
394 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
395 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
396 CREATE TABLE koordinaten_art ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
397 id integer PRIMARY KEY DEFAULT nextval('koordinaten_art_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
398 koordinatenart character varying(50), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
399 idf_geo_key character varying(1) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
400 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
401 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
402 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
403 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
404 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
405 CREATE SEQUENCE mess_einheit_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
406 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
407 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
408 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
409 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
410 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
411 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
412 CREATE TABLE mess_einheit ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
413 id integer PRIMARY KEY DEFAULT nextval('mess_einheit_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
414 beschreibung character varying(50), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
415 einheit character varying(12), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
416 eudf_messeinheit_id character varying(8), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
417 umrechnungs_faktor_eudf bigint |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
418 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
419 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
420 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
421 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
422 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
423 CREATE TABLE mess_methode ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
424 id character varying(2) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
425 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
426 messmethode character varying(50) |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
429 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
430 CREATE SEQUENCE messgroesse_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
431 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
432 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
433 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
434 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
435 CACHE 1; |
866
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 messgroesse ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
438 id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
439 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
440 messgroesse character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
441 default_farbe character varying(9), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
442 idf_nuklid_key character varying(6), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
443 ist_leitnuklid boolean DEFAULT false, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
444 eudf_nuklid_id bigint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
445 kennung_bvl character varying(7) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
446 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
447 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
448 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
449 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
450 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
451 CREATE SEQUENCE messgroessen_gruppe_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
452 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
453 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
454 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
455 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
456 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
457 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
458 CREATE TABLE messgroessen_gruppe ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
459 id integer PRIMARY KEY |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
460 DEFAULT nextval('messgroessen_gruppe_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
461 bezeichnung character varying(80), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
462 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
|
463 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
464 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
465 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
466 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
467 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
468 CREATE SEQUENCE messprogramm_kategorie_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
469 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
470 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
471 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
472 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
473 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
474 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
475 CREATE TABLE messprogramm_kategorie ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
476 id integer PRIMARY KEY |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
477 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
478 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
|
479 mpl_id character varying(3) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
480 bezeichnung character varying(120) NOT NULL, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
481 letzte_aenderung timestamp without time zone, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
482 UNIQUE(mpl_id, netzbetreiber_id) |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
483 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
484 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
|
485 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
486 ALTER SEQUENCE messprogramm_kategorie_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
487 OWNED BY messprogramm_kategorie.id; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
488 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
489 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
490 CREATE TABLE mg_grp ( |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
491 messgroessengruppe_id integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
492 messgroesse_id integer NOT NULL |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
493 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
494 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
495 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
496 CREATE TABLE mmt_messgroesse_grp ( |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
497 messgroessengruppe_id integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
498 mmt_id character varying(2) NOT NULL |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
499 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
500 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
501 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
502 CREATE VIEW mmt_messgroesse AS |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
503 SELECT mmt_messgroesse_grp.mmt_id, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
504 mg_grp.messgroesse_id |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
505 FROM mmt_messgroesse_grp, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
506 mg_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
507 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
508 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
509 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
510 CREATE TABLE ort_typ ( |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
511 id smallint PRIMARY KEY, |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
512 ort_typ character varying(60) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
513 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
514 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
515 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
516 CREATE SEQUENCE ort_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
517 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
518 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
519 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
520 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
521 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
522 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
523 CREATE TABLE ort ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
524 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass), |
912
d8c66007fa14
netzbetreiber_id is mandatory for ort.
Tom Gottfried <tom@intevation.de>
parents:
905
diff
changeset
|
525 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
|
526 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
|
527 langtext character varying(100) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
528 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
|
529 gem_id character varying(8) REFERENCES verwaltungseinheit, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
530 unscharf character(1) DEFAULT NULL::bpchar, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
531 nuts_code character varying(10), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
532 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
|
533 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
|
534 koord_y_extern character varying(22) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
535 hoehe_land real, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
536 letzte_aenderung timestamp without time zone DEFAULT now(), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
537 latitude double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
538 longitude double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
539 geom public.geometry(Point,4326), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
540 shape public.geometry(MultiPolygon,4326), |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
541 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
|
542 kurztext character varying(15) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
543 berichtstext character varying(70), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
544 zone character varying(1), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
545 sektor character varying(2), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
546 zustaendigkeit character varying(10), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
547 mp_art character varying(10), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
548 aktiv character(1), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
549 anlage_id integer, |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
550 oz_id integer, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
551 UNIQUE(ort_id, netzbetreiber_id) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
552 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
553 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
|
554 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
555 ALTER TABLE ONLY ort |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
556 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
|
557 |
902
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
558 ALTER TABLE ONLY ort |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
559 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
|
560 |
d465094946ef
Improve readability of DDL for editable stammdaten tables.
Tom Gottfried <tom@intevation.de>
parents:
891
diff
changeset
|
561 ALTER SEQUENCE ort_id_seq OWNED BY ort.id; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
562 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
563 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
564 CREATE TABLE ortszuordnung_typ ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
565 id character(1) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
566 ortstyp character varying(60) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
567 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
568 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
569 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
570 CREATE SEQUENCE pflicht_messgroesse_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
571 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
572 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
573 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
574 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
575 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
576 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
577 CREATE TABLE pflicht_messgroesse ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
578 id integer PRIMARY KEY |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
579 DEFAULT nextval('pflicht_messgroesse_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
580 messgroesse_id integer, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
581 mmt_id character varying(2), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
582 umw_id character varying(3), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
583 datenbasis_id smallint NOT NULL |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
584 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
585 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
586 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id; |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
589 CREATE TABLE proben_zusatz ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
590 id character varying(3) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
591 meh_id integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
592 beschreibung character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
593 zusatzwert character varying(7) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
594 eudf_keyword character varying(40) |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
597 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
598 CREATE SEQUENCE probenart_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
599 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
600 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
601 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
602 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
603 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
604 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
605 CREATE TABLE probenart ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
606 id integer PRIMARY KEY DEFAULT nextval('probenart_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
607 beschreibung character varying(30), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
608 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
|
609 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
|
610 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
611 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
612 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
613 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
614 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
615 CREATE SEQUENCE probenehmer_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
616 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
617 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
618 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
619 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
620 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
621 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
622 CREATE TABLE probenehmer ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
623 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
624 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
|
625 prn_id character varying(9) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
626 bearbeiter character varying(25), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
627 bemerkung character varying(60), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
628 betrieb character varying(80), |
904
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
629 bezeichnung character varying(80) NOT NULL, |
825d82b8ad6e
Set known mandatory fields in stammdaten NOT NULL.
Tom Gottfried <tom@intevation.de>
parents:
902
diff
changeset
|
630 kurz_bezeichnung character varying(10) NOT NULL, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
631 ort character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
632 plz character varying(5), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
633 strasse character varying(30), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
634 telefon character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
635 tp character varying(3), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
636 typ character(1), |
905
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
637 letzte_aenderung timestamp without time zone, |
df2908334150
Set netzbetreiber-specific IDs UNIQUE per netzbetreiber.
Tom Gottfried <tom@intevation.de>
parents:
904
diff
changeset
|
638 UNIQUE(prn_id, netzbetreiber_id) |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
639 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
912
diff
changeset
|
640 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
|
641 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
642 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
643 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
644 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
645 CREATE SEQUENCE result_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
646 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
647 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
648 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
649 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
650 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
651 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
652 CREATE TABLE result ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
653 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass), |
954
379480a94c81
Use ON DELETE CASCADE to ease removing queries from database.
Tom Gottfried <tom@intevation.de>
parents:
926
diff
changeset
|
654 query_id integer NOT NULL REFERENCES query ON DELETE CASCADE, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
655 data_index character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
656 header character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
657 width integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
658 flex boolean, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
659 index integer |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
660 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
661 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
662 ALTER SEQUENCE result_id_seq OWNED BY result.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
663 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
664 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
665 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
666 -- Status workflow |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
667 CREATE TABLE status_stufe ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
668 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
669 stufe character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
670 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
671 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
|
672 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
|
673 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
|
674 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
675 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
676 CREATE TABLE status_wert ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
677 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
678 wert character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
679 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
680 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
|
681 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
|
682 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
|
683 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
|
684 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
|
685 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
|
686 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
|
687 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
688 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
689 CREATE TABLE status_kombi ( |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
690 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
691 stufe_id integer REFERENCES status_stufe NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
692 wert_id integer REFERENCES status_wert NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
693 UNIQUE(stufe_id, wert_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
694 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
695 -- '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
|
696 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
|
697 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
|
698 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
|
699 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
|
700 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
|
701 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
|
702 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
|
703 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
|
704 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
|
705 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
|
706 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
|
707 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
|
708 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
|
709 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
710 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
711 CREATE SEQUENCE status_reihenfolge_id_seq; |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
712 CREATE TABLE status_reihenfolge ( |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
713 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
714 von_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
715 zu_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
716 UNIQUE(von_id, zu_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
717 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
718 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
719 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
|
720 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
|
721 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
|
722 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
|
723 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
|
724 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
|
725 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
|
726 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
727 BEGIN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
728 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
|
729 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
|
730 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
|
731 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
732 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
|
733 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
|
734 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
|
735 |
884
fa7278bd8d73
Allow 'editing' of status 'Rückfrage'
Tom Gottfried <tom@intevation.de>
parents:
879
diff
changeset
|
736 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
|
737 -- 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
|
738 -- 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
|
739 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
|
740 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
|
741 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
742 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
|
743 -- 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
|
744 -- 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
|
745 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
|
746 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
|
747 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
748 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
|
749 -- 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
|
750 -- '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
|
751 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
|
752 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
|
753 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
754 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
|
755 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
|
756 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
|
757 END; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
758 $$ 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
|
759 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
760 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
|
761 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
|
762 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
|
763 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
|
764 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
765 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
766 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
|
767 SELECT r.id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
768 zu.wert_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
769 zu.stufe_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
770 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
|
771 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
|
772 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
|
773 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
|
774 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
|
775 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
|
776 ON zu.id = r.zu_id |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
777 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
778 -- Status workflow |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
779 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
780 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
781 CREATE TABLE umwelt ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
782 id character varying(3) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
783 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
784 umwelt_bereich character varying(80) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
785 meh_id integer |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
786 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
787 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
788 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
789 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
790 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
791 ALTER TABLE ONLY lada_user |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
792 ADD CONSTRAINT lada_user_name_key UNIQUE (name); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
793 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
794 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
795 ALTER TABLE ONLY mg_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
796 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
797 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
798 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
799 ALTER TABLE ONLY mmt_messgroesse_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
800 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
|
801 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
802 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
803 ALTER TABLE ONLY proben_zusatz |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
804 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
805 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
806 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
807 ALTER TABLE ONLY umwelt |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
808 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
809 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
810 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
811 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
|
812 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
813 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
814 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
815 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
816 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
817 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
818 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
819 ADD CONSTRAINT auth_funktion_fkey FOREIGN KEY (funktion_id) REFERENCES auth_funktion(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
820 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
821 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
822 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
823 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
824 ADD CONSTRAINT auth_labor_fkey FOREIGN KEY (labor_mst_id) REFERENCES mess_stelle(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
825 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
826 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
827 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
828 ALTER TABLE ONLY auth_lst_umw |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
829 ADD CONSTRAINT auth_lst_umw_lst_fkey FOREIGN KEY (lst_id) REFERENCES mess_stelle(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
830 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
831 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
832 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
833 ALTER TABLE ONLY auth_lst_umw |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
834 ADD CONSTRAINT auth_lst_umw_umw_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
835 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
836 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
837 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
838 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
839 ADD CONSTRAINT auth_mst_id_fkey FOREIGN KEY (mst_id) REFERENCES mess_stelle(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
840 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
841 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
842 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
843 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
844 ADD CONSTRAINT auth_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
845 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
846 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
847 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
848 ALTER TABLE ONLY deskriptoren |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
849 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
850 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
851 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
852 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
853 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
854 ADD CONSTRAINT pflicht_messgroesse_datenbasis_id_fkey FOREIGN KEY (datenbasis_id) REFERENCES datenbasis(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
855 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
856 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
857 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
858 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
859 ADD CONSTRAINT pflicht_messgroesse_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES mess_methode(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
860 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
861 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
862 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
863 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
864 ADD CONSTRAINT pflicht_messgroesse_umw_id_fkey FOREIGN KEY (umw_id) REFERENCES umwelt(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
865 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
866 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
867 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
868 ALTER TABLE ONLY proben_zusatz |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
869 ADD CONSTRAINT proben_zusatz_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
870 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
871 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
872 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
873 ALTER TABLE ONLY staat |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
874 ADD CONSTRAINT staat_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
875 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
876 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
877 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
878 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
879 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
880 ALTER TABLE ONLY umwelt |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
881 ADD CONSTRAINT umwelt_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES mess_einheit(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
882 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
883 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
884 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
885 ALTER TABLE ONLY verwaltungseinheit |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
886 ADD CONSTRAINT verwaltungseinheit_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
887 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
888 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
889 COMMIT; |