Mercurial > lada > lada-server
annotate db_schema/stammdaten_schema.sql @ 893:9258cce7bcbd
Replaced replaceable character for 'SIMILAR TO' clause in queries
author | Raimund Renkert <raimund.renkert@intevation.de> |
---|---|
date | Wed, 09 Mar 2016 17:00:55 +0100 |
parents | d4d768295068 |
children | d465094946ef |
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, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
101 funktion character varying(40) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 ); |
869
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
103 INSERT INTO auth_funktion VALUES (0, 'Erfasser'); |
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
104 INSERT INTO auth_funktion VALUES (1, 'Status-Erfasser'); |
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
105 INSERT INTO auth_funktion VALUES (2, 'Status-Land'); |
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
106 INSERT INTO auth_funktion VALUES (3, 'Status-Leitstelle'); |
fc8349057de1
Functions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
868
diff
changeset
|
107 INSERT INTO auth_funktion VALUES (4, 'Stammdatenpflege-Land'); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
108 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
109 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 CREATE SEQUENCE auth_lst_umw_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
111 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
112 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
113 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
114 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
115 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
116 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
117 CREATE TABLE auth_lst_umw ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
118 id integer PRIMARY KEY DEFAULT nextval('auth_lst_umw_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
119 lst_id character varying(5), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
120 umw_id character varying(3) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
121 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
122 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
123 ALTER SEQUENCE auth_lst_umw_id_seq OWNED BY auth_lst_umw.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
124 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
125 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
126 CREATE SEQUENCE datenbasis_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
127 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
128 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
129 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
130 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
131 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
132 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
133 CREATE TABLE datenbasis ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
134 id integer PRIMARY KEY DEFAULT nextval('datenbasis_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
135 beschreibung character varying(30), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
136 datenbasis character varying(6) |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
137 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
138 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
139 ALTER SEQUENCE datenbasis_id_seq OWNED BY datenbasis.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
140 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
141 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
142 CREATE SEQUENCE datensatz_erzeuger_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
143 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
144 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
145 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
146 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
147 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
148 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
149 CREATE TABLE datensatz_erzeuger ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
150 id integer PRIMARY KEY |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
151 DEFAULT nextval('datensatz_erzeuger_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
152 netzbetreiber_id character varying(2), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
153 da_erzeuger_id character varying(2), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
154 mst_id character varying(5), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
155 bezeichnung character varying(120), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
156 letzte_aenderung timestamp without time zone |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
157 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
158 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
159 ALTER SEQUENCE datensatz_erzeuger_id_seq OWNED BY datensatz_erzeuger.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
160 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
161 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
162 CREATE SEQUENCE de_vg_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
163 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
164 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
165 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
166 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
167 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
168 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
169 CREATE TABLE de_vg ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
170 id integer PRIMARY KEY DEFAULT nextval('de_vg_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
171 use double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
172 rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
173 gf double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
174 rau_rs character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
175 gen character varying(50), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
176 des character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
177 isn double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
178 bemerk character varying(75), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
179 nambild character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
180 ags character varying(12), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
181 rs_alt character varying(20), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
182 wirksamkei date, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
183 debkg_id character varying(16), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
184 length numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
185 shape_area numeric, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
186 geom public.geometry(MultiPolygon,4326) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
187 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
188 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
189 ALTER SEQUENCE de_vg_id_seq OWNED BY de_vg.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
190 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
191 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
192 CREATE TABLE deskriptor_umwelt ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
193 id integer PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
194 s00 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
195 s01 integer NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
196 s02 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
197 s03 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
198 s04 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
199 s05 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
200 s06 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
201 s07 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
202 s08 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
203 s09 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
204 s10 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
205 s11 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
206 s12 integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
207 umw_id character varying(3) NOT NULL |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
208 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
209 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
211 CREATE SEQUENCE deskriptoren_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
212 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
213 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
214 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
215 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
216 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
217 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
218 CREATE TABLE deskriptoren ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
219 id integer PRIMARY KEY DEFAULT nextval('deskriptoren_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
220 vorgaenger integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
221 ebene smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
222 s_xx integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
223 sn smallint, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
224 beschreibung character varying(100), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
225 bedeutung character varying(300) |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
226 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
227 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
228 ALTER SEQUENCE deskriptoren_id_seq OWNED BY deskriptoren.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
229 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
230 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
231 CREATE SEQUENCE favorite_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
232 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
233 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
234 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
235 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
236 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
237 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
238 CREATE TABLE favorite ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
239 id integer PRIMARY KEY DEFAULT nextval('favorite_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
240 user_id integer NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
241 query_id integer NOT NULL |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
242 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
243 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
244 ALTER SEQUENCE favorite_id_seq OWNED BY favorite.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
245 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
246 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
247 CREATE SEQUENCE filter_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
248 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
249 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
250 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
251 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
252 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
253 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
254 CREATE TABLE filter ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
255 id integer PRIMARY KEY DEFAULT nextval('filter_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
256 query_id integer NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
257 data_index character varying(50) NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
258 type character varying(10) NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
259 label character varying(50) NOT NULL, |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
260 multiselect boolean |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
261 ); |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
262 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
263 ALTER SEQUENCE filter_id_seq OWNED BY filter.id; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
264 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
265 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
266 CREATE SEQUENCE filter_value_id_seq |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
267 START WITH 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
268 INCREMENT BY 1 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
269 NO MINVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
270 NO MAXVALUE |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
271 CACHE 1; |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
272 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
273 CREATE TABLE filter_value ( |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
274 id integer PRIMARY KEY DEFAULT nextval('filter_value_id_seq'::regclass), |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
275 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 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
603 CREATE SEQUENCE staat_id_seq |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
604 START WITH 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
605 INCREMENT BY 1 |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
606 NO MINVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
607 NO MAXVALUE |
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
608 CACHE 1; |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
609 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
610 CREATE TABLE staat ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
611 id integer PRIMARY KEY DEFAULT nextval('staat_id_seq'::regclass), |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
612 staat character varying(50) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
613 hkl_id smallint NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
614 staat_iso character varying(2) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
615 staat_kurz character varying(5), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
616 eu character(1) DEFAULT NULL::bpchar, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
617 koord_x_extern character varying(22), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
618 koord_y_extern character varying(22), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
619 kda_id integer |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
620 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
621 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
622 ALTER SEQUENCE staat_id_seq OWNED BY staat.id; |
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 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
625 -- Status workflow |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
626 CREATE TABLE status_stufe ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
627 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
628 stufe character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
629 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
630 INSERT INTO status_stufe VALUES (1, 'MST'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
631 INSERT INTO status_stufe VALUES (2, 'LAND'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
632 INSERT INTO status_stufe VALUES (3, 'LST'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
633 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
634 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
635 CREATE TABLE status_wert ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
636 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
637 wert character varying(50) UNIQUE NOT NULL |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
638 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
639 INSERT INTO status_wert VALUES (0, 'nicht vergeben'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
640 INSERT INTO status_wert VALUES (1, 'plausibel'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
641 INSERT INTO status_wert VALUES (2, 'nicht repräsentativ'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
642 INSERT INTO status_wert VALUES (3, 'nicht plausibel'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
643 INSERT INTO status_wert VALUES (4, 'Rückfrage'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
644 INSERT INTO status_wert VALUES (7, 'nicht lieferbar'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
645 INSERT INTO status_wert VALUES (8, 'zurückgesetzt'); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
646 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
647 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
648 CREATE TABLE status_kombi ( |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
649 id integer PRIMARY KEY, |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
650 stufe_id integer REFERENCES status_stufe NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
651 wert_id integer REFERENCES status_wert NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
652 UNIQUE(stufe_id, wert_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
653 ); |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
654 -- 'zurückgesetzt' is left out here deliberately! |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
655 INSERT INTO status_kombi VALUES (1, 1, 0); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
656 INSERT INTO status_kombi VALUES (2, 1, 1); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
657 INSERT INTO status_kombi VALUES (3, 1, 2); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
658 INSERT INTO status_kombi VALUES (4, 1, 3); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
659 INSERT INTO status_kombi VALUES (5, 1, 7); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
660 INSERT INTO status_kombi VALUES (6, 2, 1); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
661 INSERT INTO status_kombi VALUES (7, 2, 2); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
662 INSERT INTO status_kombi VALUES (8, 2, 3); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
663 INSERT INTO status_kombi VALUES (9, 2, 4); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
664 INSERT INTO status_kombi VALUES (10, 3, 1); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
665 INSERT INTO status_kombi VALUES (11, 3, 2); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
666 INSERT INTO status_kombi VALUES (12, 3, 3); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
667 INSERT INTO status_kombi VALUES (13, 3, 4); |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
668 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
669 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
670 CREATE SEQUENCE status_reihenfolge_id_seq; |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
671 CREATE TABLE status_reihenfolge ( |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
672 id integer PRIMARY KEY DEFAULT nextval('status_reihenfolge_id_seq'), |
872
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
673 von_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
674 zu_id integer REFERENCES status_kombi NOT NULL, |
e161d8f2d978
Add necessary constraints for status workflow.
Tom Gottfried <tom@intevation.de>
parents:
871
diff
changeset
|
675 UNIQUE(von_id, zu_id) |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
676 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
677 |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
678 CREATE FUNCTION populate_status_reihenfolge() RETURNS void AS $$ |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
679 DECLARE kombi_from RECORD; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
680 DECLARE s_from integer; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
681 DECLARE w_from integer; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
682 DECLARE kombi_to RECORD; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
683 DECLARE s_to integer; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
684 DECLARE w_to integer; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
685 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
686 BEGIN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
687 FOR kombi_from IN SELECT * FROM status_kombi LOOP |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
688 s_from := kombi_from.stufe_id; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
689 w_from := kombi_from.wert_id; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
690 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
691 FOR kombi_to IN SELECT * FROM status_kombi LOOP |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
692 s_to := kombi_to.stufe_id; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
693 w_to := kombi_to.wert_id; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
694 |
884
fa7278bd8d73
Allow 'editing' of status 'Rückfrage'
Tom Gottfried <tom@intevation.de>
parents:
879
diff
changeset
|
695 IF s_from = s_to AND w_to <> 0 THEN |
871
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
696 -- At the same 'stufe', all permutations occur, |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
697 -- but 'nicht vergeben' is only allowed for von_id |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
698 INSERT INTO status_reihenfolge (von_id, zu_id) |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
699 VALUES (kombi_from.id, kombi_to.id); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
700 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
701 ELSEIF s_to = s_from + 1 AND w_from <> 0 AND w_from <> 4 THEN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
702 -- Going to the next 'stufe' all available status_kombi are allowed |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
703 -- in case current wert is not 'nicht vergeben' or 'Rückfrage' |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
704 INSERT INTO status_reihenfolge (von_id, zu_id) |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
705 VALUES (kombi_from.id, kombi_to.id); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
706 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
707 ELSEIF w_from = 4 AND s_to = 1 AND w_to >= 1 AND w_to <= 3 THEN |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
708 -- After 'Rückfrage' follows 'MST' with |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
709 -- 'plausibel', 'nicht plausibel' or 'nicht repräsentativ' |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
710 INSERT INTO status_reihenfolge (von_id, zu_id) |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
711 VALUES (kombi_from.id, kombi_to.id); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
712 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
713 END IF; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
714 END LOOP; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
715 END LOOP; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
716 END; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
717 $$ LANGUAGE plpgsql; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
718 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
719 SELECT populate_status_reihenfolge(); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
720 DROP FUNCTION populate_status_reihenfolge(); |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
721 ALTER TABLE status_reihenfolge ALTER COLUMN id DROP DEFAULT; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
722 DROP SEQUENCE status_reihenfolge_id_seq; |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
723 |
53d739275c50
Status workflow definitions are needed and assumed to be given by the server.
Tom Gottfried <tom@intevation.de>
parents:
870
diff
changeset
|
724 |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
725 CREATE VIEW status_erreichbar AS ( |
879
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
726 SELECT r.id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
727 zu.wert_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
728 zu.stufe_id, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
729 von.wert_id AS cur_wert, |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
730 von.stufe_id AS cur_stufe |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
731 FROM stammdaten.status_reihenfolge r |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
732 JOIN stammdaten.status_kombi von |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
733 ON von.id = r.von_id |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
734 JOIN stammdaten.status_kombi zu |
6bd149c352a9
Available status values need to be filtered by the users functions.
Tom Gottfried <tom@intevation.de>
parents:
873
diff
changeset
|
735 ON zu.id = r.zu_id |
870
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
736 ); |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
737 -- Status workflow |
82a51cafa0fe
Put status workflow definitions in one place.
Tom Gottfried <tom@intevation.de>
parents:
869
diff
changeset
|
738 |
866
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 TABLE umwelt ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
741 id character varying(3) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
742 beschreibung character varying(300), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
743 umwelt_bereich character varying(80) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
744 meh_id integer |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
745 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
746 |
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 CREATE TABLE verwaltungseinheit ( |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
749 id character varying(8) PRIMARY KEY, |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
750 bundesland character varying(8) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
751 kda_id integer, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
752 kreis character varying(8), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
753 nuts character varying(10), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
754 regbezirk character varying(8), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
755 bezeichnung character varying(80) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
756 is_bundesland character(1) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
757 is_gemeinde character(1) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
758 is_landkreis character(1) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
759 is_regbezirk character(1) NOT NULL, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
760 koord_x_extern character varying(22), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
761 koord_y_extern character varying(22), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
762 plz character varying(6), |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
763 longitude double precision, |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
764 latitude double precision |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
765 ); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
766 |
868
6cefa1468832
Use inline primary key definitions and remove autogenerated comments.
Tom Gottfried <tom@intevation.de>
parents:
867
diff
changeset
|
767 |
866
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
768 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
769 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
770 ALTER TABLE ONLY lada_user |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
771 ADD CONSTRAINT lada_user_name_key UNIQUE (name); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
772 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
773 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
774 ALTER TABLE ONLY mg_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
775 ADD CONSTRAINT mg_grp_pkey PRIMARY KEY (messgroessengruppe_id, messgroesse_id); |
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 ALTER TABLE ONLY mmt_messgroesse_grp |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
779 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
|
780 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
781 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
782 ALTER TABLE ONLY proben_zusatz |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
783 ADD CONSTRAINT proben_zusatz_eudf_keyword_key UNIQUE (eudf_keyword); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
784 |
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 ALTER TABLE ONLY umwelt |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
787 ADD CONSTRAINT umwelt_umwelt_bereich_key UNIQUE (umwelt_bereich); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
788 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
789 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
790 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
|
791 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
792 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
793 CREATE INDEX fts_stauts_kooin10001 ON staat USING btree (kda_id); |
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 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
798 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
|
799 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
800 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
801 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
802 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
803 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
|
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 ALTER TABLE ONLY auth_lst_umw |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
808 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
|
809 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
810 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
811 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
812 ALTER TABLE ONLY auth_lst_umw |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
813 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
|
814 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
815 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
816 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
817 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
818 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
|
819 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
820 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
821 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
822 ALTER TABLE ONLY auth |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
823 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
|
824 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
825 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
826 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
827 ALTER TABLE ONLY datensatz_erzeuger |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
828 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
|
829 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
830 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
831 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
832 ALTER TABLE ONLY datensatz_erzeuger |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
833 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
|
834 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
835 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
836 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
837 ALTER TABLE ONLY favorite |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
838 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
|
839 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
840 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
841 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
842 ALTER TABLE ONLY favorite |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
843 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
|
844 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
845 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
846 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
847 ALTER TABLE ONLY filter |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
848 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
|
849 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
850 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
851 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
852 ALTER TABLE ONLY filter_value |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
853 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
|
854 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
855 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
856 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
857 ALTER TABLE ONLY filter_value |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
858 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
|
859 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
860 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
861 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
862 ALTER TABLE ONLY deskriptoren |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
863 ADD CONSTRAINT fk_deskriptoren_vorgaenger FOREIGN KEY (vorgaenger) REFERENCES deskriptoren(id); |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
864 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
865 |
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 ALTER TABLE ONLY messprogramm_kategorie |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
868 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
|
869 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
870 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
871 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
872 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
873 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
|
874 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
875 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
876 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
877 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
878 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
|
879 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
880 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
881 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
882 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
883 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
|
884 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
885 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
886 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
887 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
888 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
|
889 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
890 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
891 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
892 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
893 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
|
894 |
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 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
898 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
|
899 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
900 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
901 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
902 ALTER TABLE ONLY ort |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
903 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
|
904 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
905 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
906 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
907 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
908 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
|
909 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
910 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
911 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
912 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
913 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
|
914 |
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 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
917 ALTER TABLE ONLY pflicht_messgroesse |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
918 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
|
919 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
920 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
921 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
922 ALTER TABLE ONLY proben_zusatz |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
923 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
|
924 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
925 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
926 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
927 ALTER TABLE ONLY probenehmer |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
928 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
|
929 |
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 ALTER TABLE ONLY result |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
933 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
|
934 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
935 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
936 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
937 ALTER TABLE ONLY staat |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
938 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
|
939 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
940 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
941 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
942 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
943 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
944 ALTER TABLE ONLY umwelt |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
945 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
|
946 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
947 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
948 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
949 ALTER TABLE ONLY verwaltungseinheit |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
950 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
|
951 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
952 |
d47e6b8f3897
Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
953 COMMIT; |