annotate db_schema/stammdaten_schema.sql @ 870:82a51cafa0fe

Put status workflow definitions in one place.
author Tom Gottfried <tom@intevation.de>
date Mon, 08 Feb 2016 13:21:42 +0100
parents fc8349057de1
children 53d739275c50
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 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
19 LANGUAGE plpgsql
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 AS $$
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 declare
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 result character varying(100);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 d00 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 d01 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 d02 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 d03 smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 begin
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 if media_desk like 'D: %' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 d00 := substring(media_desk,4,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 d01 := substring(media_desk,7,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 d02 := substring(media_desk,10,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 d03 := substring(media_desk,13,2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 if d00 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 result := null;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 if d01 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 select s00.beschreibung into result FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 where s00.ebene = 0 and s00.sn = d00::smallint;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 if d02 = '00' or d00 <> '01' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 select s01.beschreibung into result FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 where s00.ebene = 0 and s00.sn = d00::smallint);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 if d03 = '00' then
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 select s02.beschreibung into result FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 where s00.ebene = 0 and s00.sn = d00::smallint));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 select s03.beschreibung into result FROM stammdaten.deskriptoren s03
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 where s03.ebene = 3 and s03.sn = d03::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 and s03.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 (select s02.id FROM stammdaten.deskriptoren s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 where s02.ebene = 2 and s02.sn = d02::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 and s02.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 (select s01.id FROM stammdaten.deskriptoren s01
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 where s01.ebene = 1 and s01.sn = d01::smallint
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 and s01.vorgaenger =
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 (select s00.id FROM stammdaten.deskriptoren s00
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 where s00.ebene = 0 and s00.sn = d00::smallint)));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 else
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 result := null;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 end if;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 return (result);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 end;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 $$;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 CREATE SEQUENCE auth_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
87 CREATE TABLE auth (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
88 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
89 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
90 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
91 mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
92 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
93 funktion_id smallint
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
94 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 ALTER SEQUENCE auth_id_seq OWNED BY auth.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
99 CREATE TABLE auth_funktion (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
100 id smallint PRIMARY KEY,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
101 funktion character varying(40)
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 );
869
fc8349057de1 Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents: 868
diff changeset
103 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
104 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
105 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
106 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
107 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land');
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
109
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 CREATE SEQUENCE auth_lst_umw_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
117 CREATE TABLE auth_lst_umw (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
118 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
119 lst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
120 umw_id character varying(3)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
121 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 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
124
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 CREATE SEQUENCE datenbasis_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
133 CREATE TABLE datenbasis (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
134 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
135 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
136 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
137 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
141
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
142 CREATE SEQUENCE datensatz_erzeuger_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
145 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
148
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
149 CREATE TABLE datensatz_erzeuger (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
150 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
151 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
152 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
153 da_erzeuger_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
154 mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
155 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
156 letzte_aenderung timestamp without time zone
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
157 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
162 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
163 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
164 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
165 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
166 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
167 CACHE 1;
866
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 CREATE TABLE de_vg (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
170 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
171 use double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 gf double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174 rau_rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175 gen character varying(50),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
176 des character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
177 isn double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
178 bemerk character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 nambild character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
180 ags character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
181 rs_alt character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
182 wirksamkei date,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
183 debkg_id character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
184 length numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
185 shape_area numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
186 geom public.geometry(MultiPolygon,4326)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
187 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
188
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
189 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 CREATE TABLE deskriptor_umwelt (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
193 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
196 s02 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 s03 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198 s04 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 s05 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 s06 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
201 s07 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
202 s08 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
203 s09 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
204 s10 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
205 s11 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
206 s12 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 umw_id character varying(3) NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
208 );
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
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
211 CREATE SEQUENCE deskriptoren_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
212 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
213 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
214 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
215 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
216 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
217
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
218 CREATE TABLE deskriptoren (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
219 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220 vorgaenger integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
223 sn smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 bedeutung character varying(300)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
226 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
229
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
230
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
231 CREATE SEQUENCE favorite_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
232 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
233 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
234 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
235 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
236 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
237
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
238 CREATE TABLE favorite (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
239 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
240 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
241 query_id integer NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
242 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247 CREATE SEQUENCE filter_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
248 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
253
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
254 CREATE TABLE filter (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
255 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
256 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
257 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
258 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
259 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
260 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
261 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
262
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
263 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
264
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
265
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
266 CREATE SEQUENCE filter_value_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
267 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
268 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
269 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
270 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
271 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
272
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
273 CREATE TABLE filter_value (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
274 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
275 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
276 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
277 filter_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
278 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
279 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
280
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
281 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
284 CREATE SEQUENCE koordinaten_art_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
285 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
286 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
287 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
288 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
289 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
290
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
291 CREATE TABLE koordinaten_art (
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('koordinaten_art_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
293 koordinatenart character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
294 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
295 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
296
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
297 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
298
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 CREATE SEQUENCE lada_user_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
301 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
302 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
303 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
304 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
305 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
306
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
307 CREATE TABLE lada_user (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
308 id integer PRIMARY KEY DEFAULT nextval('lada_user_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
309 name character varying(80) NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
310 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
311
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
312 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
313
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
314
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
315 CREATE SEQUENCE mess_einheit_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
316 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
317 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
318 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
319 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
320 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
321
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
322 CREATE TABLE mess_einheit (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
323 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
324 beschreibung character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
325 einheit character varying(12),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
326 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
327 umrechnungs_faktor_eudf bigint
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
328 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
329
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
330 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
331
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
332
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
333 CREATE TABLE mess_methode (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
334 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
335 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
336 messmethode character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
337 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
338
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
339
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
340 CREATE TABLE mess_stelle (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
341 id character varying(5) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
342 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344 mess_stelle character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345 mst_typ character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
346 amtskennung character varying(6)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
347 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
348
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
349
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
350
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
351 CREATE SEQUENCE messgroesse_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
352 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
353 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
354 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
355 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
356 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
357
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
358 CREATE TABLE messgroesse (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
359 id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
360 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
361 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
362 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
363 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
364 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
365 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
366 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
367 );
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 messgroesse_id_seq OWNED BY messgroesse.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 messgroessen_gruppe_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 messgroessen_gruppe (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
380 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
381 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
382 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
383 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
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 messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.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 messprogramm_kategorie_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 messprogramm_kategorie (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
397 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
398 DEFAULT nextval('messprogramm_kategorie_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
399 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
400 mpl_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
401 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
402 letzte_aenderung timestamp without time zone
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
403 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
404
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
405 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
406 OWNED BY messprogramm_kategorie.id;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
407
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
408
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
409 CREATE TABLE mg_grp (
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
410 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
411 messgroesse_id integer NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
412 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
413
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
414
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
415 CREATE TABLE mmt_messgroesse_grp (
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
416 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
417 mmt_id character varying(2) NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
418 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
419
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
420
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
421 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
422 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
423 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
424 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
425 mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
426 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
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 CREATE TABLE netz_betreiber (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
430 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
431 netzbetreiber character varying(50),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
432 idf_netzbetreiber character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
433 is_bmn boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
434 mailverteiler character varying(512),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435 aktiv boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
436 zust_mst_id character varying(5)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
437 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
438
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
439
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
440 CREATE SEQUENCE ort_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
441 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
442 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
443 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
444 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
445 CACHE 1;
866
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 CREATE TABLE ort (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
448 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
449 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
450 ort_id character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
451 langtext character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
452 staat_id smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
453 gem_id character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
454 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
455 nuts_code character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
456 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
457 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
458 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460 letzte_aenderung timestamp without time zone DEFAULT now(),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461 latitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
462 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
463 geom public.geometry(Point,4326),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
464 shape public.geometry(MultiPolygon,4326),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
465 ort_typ smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
466 kurztext character varying(15),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
467 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
468 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
469 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
470 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
472 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
473 anlage_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
474 oz_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
475 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
476
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
477 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
478
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
479
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
480 CREATE TABLE ort_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
481 id smallint PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
482 ort_typ character varying(60)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
483 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
484
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
485
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
486 CREATE TABLE ortszuordnung_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
487 id character(1) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
488 ortstyp character varying(60)
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
491
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
492 CREATE SEQUENCE pflicht_messgroesse_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
493 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
494 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
495 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
496 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
497 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
498
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
499 CREATE TABLE pflicht_messgroesse (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
500 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
501 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
502 messgroesse_id integer,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
503 mmt_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
504 umw_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
505 datenbasis_id smallint NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
506 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
507
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
508 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
509
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
510
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
511 CREATE TABLE proben_zusatz (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
512 id character varying(3) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
513 meh_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
514 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
515 zusatzwert character varying(7) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
516 eudf_keyword character varying(40)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
517 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
518
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
519
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
520 CREATE SEQUENCE probenart_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
521 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
522 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
523 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
524 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
525 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
526
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
527 CREATE TABLE probenart (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
528 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
529 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
530 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
531 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
532 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
533
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
534 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
535
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
536
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
537 CREATE SEQUENCE probenehmer_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
538 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
539 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
540 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
541 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
542 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
543
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
544 CREATE TABLE probenehmer (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
545 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
546 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
547 prn_id character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
548 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
549 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
550 betrieb character varying(80),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
551 bezeichnung character varying(80),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
552 kurz_bezeichnung character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
553 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
554 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
555 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
556 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
557 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
558 typ character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
559 letzte_aenderung timestamp without time zone
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
560 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
561
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
562 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
565 CREATE SEQUENCE query_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
566 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
567 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
568 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
569 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
570 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
571
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
572 CREATE TABLE query (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
573 id integer PRIMARY KEY DEFAULT nextval('query_id_seq'::regclass),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
574 name character varying(80) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
575 type character varying(30) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
576 sql character varying(1500) NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
577 description character varying(100)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
578 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
579
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
580 ALTER SEQUENCE query_id_seq OWNED BY query.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
581
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
582
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
583 CREATE SEQUENCE result_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
584 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
585 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
586 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
587 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
588 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
589
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
590 CREATE TABLE result (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
591 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
592 query_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
593 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
594 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
595 width integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
596 flex boolean,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
597 index integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
598 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
599
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
600 ALTER SEQUENCE result_id_seq OWNED BY result.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
601
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
602
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
603
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
604 CREATE VIEW s_00_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
605 SELECT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
606 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
607 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
608 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
609 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
610 WHERE (deskriptoren.ebene = 0);
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
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 CREATE VIEW s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
615 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
616 d2.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
617 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
618 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
619 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
620 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
621 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
622 WHERE (d1.ebene = 1);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
623
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
624
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
625
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
626 CREATE VIEW s_02_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
627 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
628 d2.s_xx AS s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
629 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
630 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
631 WHERE (d2.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
632
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
633
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
634
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
635 CREATE VIEW s_02_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
636 SELECT DISTINCT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
637 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
638 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
639 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
640 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
641 WHERE (deskriptoren.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
642
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
645 CREATE VIEW s_03_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
646 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
647 d2.s_xx AS s03
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
648 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
649 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
650 WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
653
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
654 CREATE VIEW s_03_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
655 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
656 d2.s_xx AS s02,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
657 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
658 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
659 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
660 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
661 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
662 WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
663 UNION
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
664 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
665 NULL::integer AS s02,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
666 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
667 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
668 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
669 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
670 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
671 WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
672
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
673
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
674
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
675 CREATE VIEW s_04_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
676 SELECT DISTINCT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
677 d2.s_xx AS s04
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
678 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
679 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
680 WHERE (d2.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
681
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
682
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
683
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
684 CREATE VIEW s_04_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
685 SELECT DISTINCT deskriptoren.s_xx AS s04,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
686 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
687 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
688 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
689 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
690 WHERE (deskriptoren.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
691
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
692
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
693
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
694 CREATE VIEW s_05_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
695 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
696 d2.s_xx AS s05
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
697 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
698 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
699 WHERE (d2.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
700
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
701
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
702
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
703 CREATE VIEW s_05_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
704 SELECT DISTINCT deskriptoren.s_xx AS s05,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
705 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
706 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
707 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
708 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
709 WHERE (deskriptoren.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
710
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
711
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
712
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
713 CREATE VIEW s_06_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
714 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
715 d2.s_xx AS s06
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
716 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
717 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
718 WHERE (d2.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
719
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
720
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
721
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
722 CREATE VIEW s_06_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
723 SELECT DISTINCT deskriptoren.s_xx AS s06,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
724 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
725 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
726 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
727 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
728 WHERE (deskriptoren.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
729
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
730
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
731
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
732 CREATE VIEW s_07_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
733 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
734 d2.s_xx AS s07
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
735 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
736 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
737 WHERE (d2.ebene = 7);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
738
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
739
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
740
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
741 CREATE VIEW s_07_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
742 SELECT DISTINCT deskriptoren.s_xx AS s07,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
743 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
744 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
745 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
746 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
747 WHERE (deskriptoren.ebene = 7);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
748
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
749
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
750
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
751 CREATE VIEW s_08_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
752 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
753 d2.s_xx AS s08
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
754 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
755 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
756 WHERE (d2.ebene = 8);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
757
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
758
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
759
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
760 CREATE VIEW s_08_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
761 SELECT DISTINCT deskriptoren.s_xx AS s08,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
762 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
763 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
764 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
765 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
766 WHERE (deskriptoren.ebene = 8);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
767
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
768
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
769
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
770 CREATE VIEW s_09_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
771 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
772 d2.s_xx AS s09
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
773 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
774 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
775 WHERE (d2.ebene = 9);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
776
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
777
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
778
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
779 CREATE VIEW s_09_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
780 SELECT DISTINCT deskriptoren.s_xx AS s09,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
781 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
782 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
783 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
784 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
785 WHERE (deskriptoren.ebene = 9);
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 CREATE VIEW s_10_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
790 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
791 d2.s_xx AS s10
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
792 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
793 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
794 WHERE (d2.ebene = 10);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
795
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
796
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 CREATE VIEW s_10_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
799 SELECT DISTINCT deskriptoren.s_xx AS s10,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
800 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
801 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
802 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
803 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
804 WHERE (deskriptoren.ebene = 10);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
808 CREATE VIEW s_11_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
809 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
810 d2.s_xx AS s11
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
811 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
812 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
813 WHERE (d2.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
814
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 CREATE VIEW s_11_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
818 SELECT DISTINCT deskriptoren.s_xx AS s11,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
819 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
820 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
821 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
822 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
823 WHERE (deskriptoren.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
824
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 CREATE VIEW s_12_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
828 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
829 d2.s_xx AS s12
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
830 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
831 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
832 WHERE (d2.ebene = 12);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
833
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
834
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 CREATE VIEW s_12_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
837 SELECT DISTINCT deskriptoren.s_xx AS s12,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
838 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
839 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
840 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
841 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
842 WHERE (deskriptoren.ebene = 12);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
843
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
844
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
845
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
846 CREATE SEQUENCE staat_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
847 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
848 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
849 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
850 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
851 CACHE 1;
866
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 CREATE TABLE staat (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
854 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
855 staat character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
856 hkl_id smallint NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
857 staat_iso character varying(2) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
858 staat_kurz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
859 eu character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
860 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
861 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
862 kda_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
863 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
864
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
865 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
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
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
868 -- Status workflow
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
869 CREATE TABLE status_stufe (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
870 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
871 stufe character varying(50)
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
874 CREATE TABLE status_wert (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
875 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
876 wert character varying(50)
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
870
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
879 CREATE TABLE status_kombi (
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
880 id integer PRIMARY KEY,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
881 stufe_id integer REFERENCES status_stufe,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
882 wert_id integer REFERENCES status_wert
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
883 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
884
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
885 CREATE TABLE status_reihenfolge (
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
886 id integer PRIMARY KEY,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
887 von_id integer REFERENCES status_kombi,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
888 zu_id integer REFERENCES status_kombi
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
889 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
890
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
891 CREATE VIEW status_erreichbar AS (
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
892 SELECT DISTINCT k.wert_id,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
893 j.wert_id AS cur_wert,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
894 j.stufe_id AS cur_stufe
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
895 FROM stammdaten.status_kombi k
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
896 JOIN (SELECT r.zu_id,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
897 kom.wert_id,
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
898 kom.stufe_id
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
899 FROM stammdaten.status_reihenfolge r
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
900 JOIN stammdaten.status_kombi kom
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
901 ON kom.id = r.von_id) j
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
902 ON j.zu_id = k.id
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
903 );
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
904 -- Status workflow
82a51cafa0fe Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents: 869
diff changeset
905
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
906
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
907 CREATE TABLE umwelt (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
908 id character varying(3) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
909 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
910 umwelt_bereich character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
911 meh_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
912 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
913
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
914
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
915 CREATE TABLE verwaltungseinheit (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
916 id character varying(8) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
917 bundesland character varying(8) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
918 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
919 kreis character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
920 nuts character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
921 regbezirk character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
922 bezeichnung character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
923 is_bundesland character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
924 is_gemeinde character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
925 is_landkreis character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
926 is_regbezirk character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
927 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
928 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
929 plz character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
930 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
931 latitude double precision
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
932 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
933
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
934
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
935
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
936
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
937 ALTER TABLE ONLY lada_user
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
938 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
939
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
940
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
941 ALTER TABLE ONLY mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
942 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
943
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
944
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
945 ALTER TABLE ONLY mmt_messgroesse_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
946 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
947
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
948
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
949 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
950 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
951
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
952
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
953 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
954 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
955
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
956
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
957 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
958
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
959
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
960 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
961
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
962
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
963
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
964 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
965 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
966
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
967
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
968
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
969 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
970 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
971
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
972
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
973
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
974 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
975 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
976
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
977
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
978
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
979 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
980 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
981
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
982
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
983
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
984 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
985 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
986
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
987
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
988
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
989 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
990 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
991
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
992
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
993
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
994 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
995 ADD CONSTRAINT datensatz_erzeuger_mst_id_fkey1 FOREIGN KEY (mst_id) REFERENCES mess_stelle(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
996
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
997
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
998
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
999 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1000 ADD CONSTRAINT datensatz_erzeuger_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1001
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1002
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1003
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1004 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1005 ADD CONSTRAINT favorite_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1006
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1007
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1008
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1009 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1010 ADD CONSTRAINT favorite_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1011
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1012
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1013
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1014 ALTER TABLE ONLY filter
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1015 ADD CONSTRAINT filter_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1016
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1017
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1018
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1019 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1020 ADD CONSTRAINT filter_value_filter_id_fkey FOREIGN KEY (filter_id) REFERENCES filter(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1021
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1022
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1023
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1024 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1025 ADD CONSTRAINT filter_value_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1026
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1027
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1028
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1029 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1030 ADD CONSTRAINT filter_value_user_id_fkey FOREIGN KEY (user_id) REFERENCES lada_user(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1031
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1032
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1033
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1034 ALTER TABLE ONLY deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1035 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1036
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1037
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1038
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1039 ALTER TABLE ONLY messprogramm_kategorie
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1040 ADD CONSTRAINT messprogramm_kategorie_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1041
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1042
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1043
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1044 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1045 ADD CONSTRAINT ort_anlage_fkey FOREIGN KEY (anlage_id) REFERENCES ort(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1046
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1047
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1048
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1049 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1050 ADD CONSTRAINT ort_gem_id_fkey FOREIGN KEY (gem_id) REFERENCES verwaltungseinheit(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1051
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1052
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1053
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1054 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1055 ADD CONSTRAINT ort_kda_id_fkey FOREIGN KEY (kda_id) REFERENCES koordinaten_art(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1056
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1057
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1058
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1059 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1060 ADD CONSTRAINT ort_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1061
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1062
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1063
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1064 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1065 ADD CONSTRAINT ort_ort_typ_fkey FOREIGN KEY (ort_typ) REFERENCES ort_typ(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1066
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1067
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1068
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1069 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1070 ADD CONSTRAINT ort_oz_fkey FOREIGN KEY (oz_id) REFERENCES ort(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1071
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1072
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1073
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1074 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1075 ADD CONSTRAINT ort_staat_id_fkey FOREIGN KEY (staat_id) REFERENCES staat(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1076
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1077
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1078
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1079 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1080 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
1081
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1082
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1083
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1084 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1085 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
1086
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1087
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1088
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1089 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1090 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
1091
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1092
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1093
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1094 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1095 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
1096
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1097
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1098
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1099 ALTER TABLE ONLY probenehmer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1100 ADD CONSTRAINT probenehmer_netzbetreiber_id_fkey FOREIGN KEY (netzbetreiber_id) REFERENCES netz_betreiber(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1101
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1102
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1103
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1104 ALTER TABLE ONLY result
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1105 ADD CONSTRAINT result_query_id_fkey FOREIGN KEY (query_id) REFERENCES query(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1106
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1107
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1108
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1109 ALTER TABLE ONLY staat
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1110 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
1111
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1112
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1113
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1114
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1115
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1116 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1117 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
1118
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1119
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1120
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1121 ALTER TABLE ONLY verwaltungseinheit
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1122 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
1123
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1124
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1125 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)