annotate db_schema/stammdaten_schema.sql @ 869:fc8349057de1

Functions are needed and assumed to be given by the server. These are not data to be changed by any user, thus part of the schema.
author Tom Gottfried <tom@intevation.de>
date Mon, 08 Feb 2016 12:45:26 +0100
parents 6cefa1468832
children 82a51cafa0fe
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 CREATE SEQUENCE auth_lst_umw_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
116 CREATE TABLE auth_lst_umw (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
117 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
118 lst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
119 umw_id character varying(3)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
120 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 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
123
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 CREATE SEQUENCE datenbasis_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
132 CREATE TABLE datenbasis (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
133 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
134 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
135 datenbasis character varying(6)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
136 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139
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 CREATE SEQUENCE datensatz_erzeuger_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
142 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
145 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
148 CREATE TABLE datensatz_erzeuger (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
149 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
150 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
151 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
152 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
153 mst_id character varying(5),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
154 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
155 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
156 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
157
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
161 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
162 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
163 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
164 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
165 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
166 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
167
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168 CREATE TABLE de_vg (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
169 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
170 use double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172 gf double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 rau_rs character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
174 gen character varying(50),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175 des character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
176 isn double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
177 bemerk character varying(75),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
178 nambild character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 ags character varying(12),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
180 rs_alt character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
181 wirksamkei date,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
182 debkg_id character varying(16),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
183 length numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
184 shape_area numeric,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
185 geom public.geometry(MultiPolygon,4326)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
186 );
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 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
189
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 CREATE TABLE deskriptor_umwelt (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
192 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
193 s00 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 s01 integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 s02 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
196 s03 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 s04 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198 s05 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 s06 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 s07 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
201 s08 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
202 s09 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
203 s10 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
204 s11 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
205 s12 integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
206 umw_id character varying(3) NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
208
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
210 CREATE SEQUENCE deskriptoren_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
211 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
212 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
213 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
214 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
215 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
216
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
217 CREATE TABLE deskriptoren (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
218 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
219 vorgaenger integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220 ebene smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 s_xx integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222 sn smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
223 beschreibung character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224 bedeutung character varying(300)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 );
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 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228
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 CREATE SEQUENCE favorite_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
231 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
232 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
233 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
234 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
235 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
236
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
237 CREATE TABLE favorite (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
238 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
239 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
240 query_id integer NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
241 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
242
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244
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 CREATE SEQUENCE filter_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
248 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
253 CREATE TABLE filter (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
254 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
255 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
256 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
257 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
258 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
259 multiselect boolean
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
260 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
261
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
262 ALTER SEQUENCE filter_id_seq OWNED BY filter.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
263
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
264
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
265 CREATE SEQUENCE filter_value_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
266 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
267 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
268 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
269 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
270 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
271
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
272 CREATE TABLE filter_value (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
273 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
274 query_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
275 user_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
276 filter_id integer NOT NULL,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
277 value text
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
278 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
279
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
280 ALTER SEQUENCE filter_value_id_seq OWNED BY filter_value.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
281
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
282
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
283 CREATE SEQUENCE koordinaten_art_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
284 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
285 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
286 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
287 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
288 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
289
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
290 CREATE TABLE koordinaten_art (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
291 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
292 koordinatenart character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
293 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
294 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
295
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
296 ALTER SEQUENCE koordinaten_art_id_seq OWNED BY koordinaten_art.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
297
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 CREATE SEQUENCE lada_user_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
300 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
301 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
302 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
303 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
304 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
305
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
306 CREATE TABLE lada_user (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
307 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
308 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
309 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
310
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
311 ALTER SEQUENCE lada_user_id_seq OWNED BY lada_user.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
312
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 CREATE SEQUENCE mess_einheit_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
315 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
316 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
317 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
318 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
319 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
320
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
321 CREATE TABLE mess_einheit (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
322 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
323 beschreibung character varying(50),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
324 einheit character varying(12),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
325 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
326 umrechnungs_faktor_eudf bigint
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
327 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
328
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
329 ALTER SEQUENCE mess_einheit_id_seq OWNED BY mess_einheit.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
330
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 CREATE TABLE mess_methode (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
333 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
334 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
335 messmethode character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
336 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
337
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
338
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
339 CREATE TABLE mess_stelle (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
340 id character varying(5) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
341 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
342 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343 mess_stelle character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344 mst_typ character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345 amtskennung character varying(6)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
346 );
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
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
349
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
350 CREATE SEQUENCE messgroesse_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
351 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
352 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
353 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
354 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
355 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
356
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
357 CREATE TABLE messgroesse (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
358 id integer PRIMARY KEY DEFAULT nextval('messgroesse_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
359 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
360 messgroesse character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
361 default_farbe character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
362 idf_nuklid_key character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
363 ist_leitnuklid boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
364 eudf_nuklid_id bigint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
365 kennung_bvl character varying(7)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
366 );
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 ALTER SEQUENCE messgroesse_id_seq OWNED BY messgroesse.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
369
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 CREATE SEQUENCE messgroessen_gruppe_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
372 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
373 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
374 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
375 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
376 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
377
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
378 CREATE TABLE messgroessen_gruppe (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
379 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
380 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
381 bezeichnung character varying(80),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
382 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
383 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
384
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
385 ALTER SEQUENCE messgroessen_gruppe_id_seq OWNED BY messgroessen_gruppe.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
386
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 CREATE SEQUENCE messprogramm_kategorie_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
389 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
390 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
391 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
392 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
393 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
394
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
395 CREATE TABLE messprogramm_kategorie (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
396 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
397 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
398 netzbetreiber_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
399 mpl_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
400 bezeichnung character varying(120),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
401 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
402 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
403
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
404 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
405 OWNED BY messprogramm_kategorie.id;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
406
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 CREATE TABLE mg_grp (
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
409 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
410 messgroesse_id integer NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
411 );
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 CREATE TABLE mmt_messgroesse_grp (
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
415 messgroessengruppe_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
416 mmt_id character varying(2) NOT NULL
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
417 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
418
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
419
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
420 CREATE VIEW mmt_messgroesse AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
421 SELECT mmt_messgroesse_grp.mmt_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
422 mg_grp.messgroesse_id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
423 FROM mmt_messgroesse_grp,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
424 mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
425 WHERE (mg_grp.messgroessengruppe_id = mmt_messgroesse_grp.messgroessengruppe_id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
426
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
427
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
428 CREATE TABLE netz_betreiber (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
429 id character varying(2) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
430 netzbetreiber character varying(50),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
431 idf_netzbetreiber character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
432 is_bmn boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
433 mailverteiler character varying(512),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
434 aktiv boolean DEFAULT false,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435 zust_mst_id character varying(5)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
436 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
437
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
438
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
439 CREATE SEQUENCE ort_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
440 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
441 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
442 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
443 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
444 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
445
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
446 CREATE TABLE ort (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
447 id integer PRIMARY KEY DEFAULT nextval('ort_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
448 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
449 ort_id character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
450 langtext character varying(100),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
451 staat_id smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
452 gem_id character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
453 unscharf character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
454 nuts_code character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
455 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
456 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
457 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
458 hoehe_land real,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459 letzte_aenderung timestamp without time zone DEFAULT now(),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460 latitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
462 geom public.geometry(Point,4326),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
463 shape public.geometry(MultiPolygon,4326),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
464 ort_typ smallint,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
465 kurztext character varying(15),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
466 berichtstext character varying(70),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
467 zone character varying(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
468 sektor character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
469 zustaendigkeit character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
470 mp_art character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471 aktiv character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
472 anlage_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
473 oz_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
474 );
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 ALTER SEQUENCE ort_id_seq OWNED BY ort.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
477
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 CREATE TABLE ort_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
480 id smallint PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
481 ort_typ character varying(60)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
482 );
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 CREATE TABLE ortszuordnung_typ (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
486 id character(1) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
487 ortstyp character varying(60)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
488 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
489
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
490
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
491 CREATE SEQUENCE pflicht_messgroesse_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
492 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
493 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
494 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
495 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
496 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
497
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
498 CREATE TABLE pflicht_messgroesse (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
499 id integer PRIMARY KEY
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
500 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
501 messgroesse_id integer,
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
502 mmt_id character varying(2),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
503 umw_id character varying(3),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
504 datenbasis_id smallint NOT NULL
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
505 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
506
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
507 ALTER SEQUENCE pflicht_messgroesse_id_seq OWNED BY pflicht_messgroesse.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
508
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
509
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
510 CREATE TABLE proben_zusatz (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
511 id character varying(3) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
512 meh_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
513 beschreibung character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
514 zusatzwert character varying(7) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
515 eudf_keyword character varying(40)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
516 );
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 CREATE SEQUENCE probenart_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
520 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
521 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
522 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
523 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
524 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
525
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
526 CREATE TABLE probenart (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
527 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
528 beschreibung character varying(30),
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
529 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
530 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
531 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
532
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
533 ALTER SEQUENCE probenart_id_seq OWNED BY probenart.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
534
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
535
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
536 CREATE SEQUENCE probenehmer_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
537 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
538 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
539 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
540 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
541 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
542
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
543 CREATE TABLE probenehmer (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
544 id integer PRIMARY KEY DEFAULT nextval('probenehmer_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
545 netzbetreiber_id character varying(2),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
546 prn_id character varying(9),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
547 bearbeiter character varying(25),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
548 bemerkung character varying(60),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
549 betrieb character varying(80),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
550 bezeichnung character varying(80),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
551 kurz_bezeichnung character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
552 ort character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
553 plz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
554 strasse character varying(30),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
555 telefon character varying(20),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
556 tp character varying(3),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
557 typ character(1),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
558 letzte_aenderung timestamp without time zone
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
559 );
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 ALTER SEQUENCE probenehmer_id_seq OWNED BY probenehmer.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
562
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
563
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
564 CREATE SEQUENCE query_id_seq
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
565 START WITH 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
566 INCREMENT BY 1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
567 NO MINVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
568 NO MAXVALUE
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
569 CACHE 1;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
570
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
571 CREATE TABLE query (
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
572 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
573 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
574 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
575 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
576 description character varying(100)
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
577 );
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
578
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
579 ALTER SEQUENCE query_id_seq OWNED BY query.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
580
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
581
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
582 CREATE SEQUENCE result_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
583 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
584 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
585 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
586 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
587 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
588
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
589 CREATE TABLE result (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
590 id integer PRIMARY KEY DEFAULT nextval('result_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
591 query_id integer NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
592 data_index character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
593 header character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
594 width integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
595 flex boolean,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
596 index integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
597 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
598
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
599 ALTER SEQUENCE result_id_seq OWNED BY result.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
600
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 CREATE VIEW s_00_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
604 SELECT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
605 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
606 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
607 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
608 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
609 WHERE (deskriptoren.ebene = 0);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
610
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 CREATE VIEW s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
614 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
615 d2.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
616 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
617 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
618 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
619 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
620 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
621 WHERE (d1.ebene = 1);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
622
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 CREATE VIEW s_02_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
626 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
627 d2.s_xx AS s02
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
628 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
629 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
630 WHERE (d2.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
631
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 CREATE VIEW s_02_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
635 SELECT DISTINCT deskriptoren.s_xx AS s00,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
636 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
637 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
638 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
639 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
640 WHERE (deskriptoren.ebene = 2);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
641
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
642
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 CREATE VIEW s_03_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
645 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
646 d2.s_xx AS s03
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
647 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
648 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
649 WHERE ((d2.ebene = 3) AND (d1.ebene = 1));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
650
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 CREATE VIEW s_03_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
654 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
655 d2.s_xx AS s02,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
656 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
657 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
658 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
659 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
660 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
661 WHERE ((d1.ebene = 3) AND (d2.ebene = 2))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
662 UNION
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
663 SELECT d1.s_xx AS s03,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
664 NULL::integer AS s02,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
665 d1.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
666 d1.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
667 d1.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
668 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
669 JOIN deskriptoren d2 ON ((d1.vorgaenger = d2.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
670 WHERE ((d1.ebene = 3) AND (d2.ebene = 1));
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
671
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 CREATE VIEW s_04_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
675 SELECT DISTINCT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
676 d2.s_xx AS s04
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
677 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
678 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
679 WHERE (d2.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
680
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 CREATE VIEW s_04_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
684 SELECT DISTINCT deskriptoren.s_xx AS s04,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
685 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
686 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
687 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
688 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
689 WHERE (deskriptoren.ebene = 4);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
690
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 CREATE VIEW s_05_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
694 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
695 d2.s_xx AS s05
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
696 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
697 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
698 WHERE (d2.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
699
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 CREATE VIEW s_05_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
703 SELECT DISTINCT deskriptoren.s_xx AS s05,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
704 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
705 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
706 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
707 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
708 WHERE (deskriptoren.ebene = 5);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
709
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 CREATE VIEW s_06_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
713 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
714 d2.s_xx AS s06
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
715 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
716 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
717 WHERE (d2.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
718
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 CREATE VIEW s_06_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
722 SELECT DISTINCT deskriptoren.s_xx AS s06,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
723 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
724 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
725 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
726 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
727 WHERE (deskriptoren.ebene = 6);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
728
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 CREATE VIEW s_07_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
732 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
733 d2.s_xx AS s07
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
734 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
735 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
736 WHERE (d2.ebene = 7);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
737
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 CREATE VIEW s_07_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
741 SELECT DISTINCT deskriptoren.s_xx AS s07,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
742 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
743 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
744 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
745 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
746 WHERE (deskriptoren.ebene = 7);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
747
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 CREATE VIEW s_08_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
751 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
752 d2.s_xx AS s08
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
753 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
754 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
755 WHERE (d2.ebene = 8);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
756
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 CREATE VIEW s_08_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
760 SELECT DISTINCT deskriptoren.s_xx AS s08,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
761 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
762 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
763 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
764 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
765 WHERE (deskriptoren.ebene = 8);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
766
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 CREATE VIEW s_09_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
770 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
771 d2.s_xx AS s09
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
772 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
773 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
774 WHERE (d2.ebene = 9);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
775
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 CREATE VIEW s_09_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
779 SELECT DISTINCT deskriptoren.s_xx AS s09,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
780 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
781 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
782 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
783 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
784 WHERE (deskriptoren.ebene = 9);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
785
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 CREATE VIEW s_10_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
789 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
790 d2.s_xx AS s10
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
791 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
792 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
793 WHERE (d2.ebene = 10);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
794
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
795
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 CREATE VIEW s_10_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
798 SELECT DISTINCT deskriptoren.s_xx AS s10,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
799 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
800 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
801 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
802 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
803 WHERE (deskriptoren.ebene = 10);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
804
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 CREATE VIEW s_11_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
808 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
809 d2.s_xx AS s11
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
810 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
811 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
812 WHERE (d2.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
813
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
814
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 CREATE VIEW s_11_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
817 SELECT DISTINCT deskriptoren.s_xx AS s11,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
818 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
819 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
820 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
821 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
822 WHERE (deskriptoren.ebene = 11);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
823
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 CREATE VIEW s_12_s_01_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
827 SELECT d1.s_xx AS s01,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
828 d2.s_xx AS s12
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
829 FROM (deskriptoren d1
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
830 JOIN deskriptoren d2 ON ((d2.vorgaenger = d1.id)))
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
831 WHERE (d2.ebene = 12);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
832
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
833
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 CREATE VIEW s_12_view AS
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
836 SELECT DISTINCT deskriptoren.s_xx AS s12,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
837 deskriptoren.bedeutung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
838 deskriptoren.beschreibung,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
839 deskriptoren.sn
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
840 FROM deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
841 WHERE (deskriptoren.ebene = 12);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
842
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
843
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
844
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
845 CREATE SEQUENCE staat_id_seq
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
846 START WITH 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
847 INCREMENT BY 1
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
848 NO MINVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
849 NO MAXVALUE
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
850 CACHE 1;
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
851
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
852 CREATE TABLE staat (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
853 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass),
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
854 staat character varying(50) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
855 hkl_id smallint NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
856 staat_iso character varying(2) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
857 staat_kurz character varying(5),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
858 eu character(1) DEFAULT NULL::bpchar,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
859 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
860 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
861 kda_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
862 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
863
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
864 ALTER SEQUENCE staat_id_seq OWNED BY staat.id;
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
865
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
867 CREATE TABLE status_kombi (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
868 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
869 stufe_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
870 wert_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
871 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
872
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
873 CREATE TABLE status_reihenfolge (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
874 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
875 von_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
876 zu_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
877 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
878
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
879 CREATE TABLE status_stufe (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
880 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
881 stufe character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
882 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
883
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
884 CREATE TABLE status_wert (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
885 id integer PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
886 wert character varying(50)
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
887 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
888
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
889
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
890 CREATE TABLE umwelt (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
891 id character varying(3) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
892 beschreibung character varying(300),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
893 umwelt_bereich character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
894 meh_id integer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
895 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
896
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
897
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
898 CREATE TABLE verwaltungseinheit (
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
899 id character varying(8) PRIMARY KEY,
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
900 bundesland character varying(8) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
901 kda_id integer,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
902 kreis character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
903 nuts character varying(10),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
904 regbezirk character varying(8),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
905 bezeichnung character varying(80) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
906 is_bundesland character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
907 is_gemeinde character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
908 is_landkreis character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
909 is_regbezirk character(1) NOT NULL,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
910 koord_x_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
911 koord_y_extern character varying(22),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
912 plz character varying(6),
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
913 longitude double precision,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
914 latitude double precision
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
915 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
916
868
6cefa1468832 Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents: 867
diff changeset
917
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
918 CREATE VIEW status_erreichbar AS (
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
919 SELECT DISTINCT k.wert_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
920 j.wert_id AS cur_wert,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
921 j.stufe_id AS cur_stufe
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
922 FROM stammdaten.status_kombi k
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
923 JOIN (SELECT r.zu_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
924 kom.wert_id,
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
925 kom.stufe_id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
926 FROM stammdaten.status_reihenfolge r
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
927 JOIN stammdaten.status_kombi kom
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
928 ON kom.id = r.von_id) j
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
929 ON j.zu_id = k.id
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
930 );
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
931
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
934 ALTER TABLE ONLY lada_user
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
935 ADD CONSTRAINT lada_user_name_key UNIQUE (name);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
938 ALTER TABLE ONLY mg_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
939 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
942 ALTER TABLE ONLY mmt_messgroesse_grp
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
943 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
944
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
945
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
946 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
947 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
950 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
951 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
954 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
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 fts_stauts_kooin10001 ON staat USING btree (kda_id);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
961 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
962 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
963
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
964
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
965
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
966 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
967 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
968
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
969
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
970
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
971 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
972 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
973
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
974
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
975
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
976 ALTER TABLE ONLY auth_lst_umw
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
977 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
978
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
979
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
980
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
981 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
982 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
983
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
984
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
985
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
986 ALTER TABLE ONLY auth
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
987 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
988
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
989
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
990
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
991 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
992 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
993
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
994
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
995
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
996 ALTER TABLE ONLY datensatz_erzeuger
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
997 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
998
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
999
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1000
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1001 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1002 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
1003
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1004
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1005
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1006 ALTER TABLE ONLY favorite
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1007 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
1008
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1009
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1010
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1011 ALTER TABLE ONLY filter
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1012 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
1013
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1014
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1015
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1016 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1017 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
1018
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1019
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1020
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1021 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1022 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
1023
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1024
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1025
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1026 ALTER TABLE ONLY filter_value
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1027 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
1028
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1029
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1030
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1031 ALTER TABLE ONLY deskriptoren
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1032 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id);
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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1035
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1036 ALTER TABLE ONLY messprogramm_kategorie
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1037 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
1038
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1039
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1040
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1041 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1042 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
1043
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1044
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1045
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1046 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1047 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
1048
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1049
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1050
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1051 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1052 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
1053
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1054
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1055
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1056 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1057 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
1058
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1059
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1060
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1061 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1062 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
1063
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1064
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1065
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1066 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1067 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
1068
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1069
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1070
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1071 ALTER TABLE ONLY ort
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1072 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
1073
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1074
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1075
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1076 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1077 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
1078
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1079
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1080
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1081 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1082 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
1083
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1084
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1085
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1086 ALTER TABLE ONLY pflicht_messgroesse
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1087 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
1088
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1089
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1090
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1091 ALTER TABLE ONLY proben_zusatz
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1092 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
1093
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1094
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1095
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1096 ALTER TABLE ONLY probenehmer
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1097 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
1098
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1099
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1100
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1101 ALTER TABLE ONLY result
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1102 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
1103
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1104
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1105
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1106 ALTER TABLE ONLY staat
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1107 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
1108
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1109
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1110
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1111 ALTER TABLE ONLY status_kombi
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1112 ADD CONSTRAINT status_kombi_stufe_id_fkey FOREIGN KEY (stufe_id) REFERENCES status_stufe(id);
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 status_kombi
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1117 ADD CONSTRAINT status_kombi_wert_id_fkey FOREIGN KEY (wert_id) REFERENCES status_wert(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 status_reihenfolge
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1122 ADD CONSTRAINT status_reihenfolge_von_id_fkey FOREIGN KEY (von_id) REFERENCES status_kombi(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
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1126 ALTER TABLE ONLY status_reihenfolge
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1127 ADD CONSTRAINT status_reihenfolge_zu_id_fkey FOREIGN KEY (zu_id) REFERENCES status_kombi(id);
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1128
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1129
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1130
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1131 ALTER TABLE ONLY umwelt
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1132 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
1133
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1134
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1135
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1136 ALTER TABLE ONLY verwaltungseinheit
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1137 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
1138
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1139
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1140 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)