annotate db_schema/lada_schema.sql @ 1065:7d87ddba5520

Validate against possible maxima. Actual maxima being less are handled by the generation of Probe objects.
author Tom Gottfried <tom@intevation.de>
date Thu, 29 Sep 2016 16:23:04 +0200
parents 7788a805a98d
children daca5c62356f
rev   line source
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
1 \set ON_ERROR_STOP on
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
2
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
3 BEGIN;
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
4
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 SET statement_timeout = 0;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SET lock_timeout = 0;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 SET client_encoding = 'UTF8';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 SET standard_conforming_strings = on;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 SET check_function_bodies = false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 SET client_min_messages = warning;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 -- Name: bund; Type: SCHEMA; Schema: -; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 CREATE SCHEMA bund;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 CREATE SCHEMA land;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
30 -- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
31 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
32
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
33 CREATE FUNCTION update_time_status() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 BEGIN
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
37 NEW.tree_modified = now();
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
38 RETURN NEW;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
42 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
43 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
44 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
45 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
46 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
47 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
48 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
49 $$;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
53 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
54 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
55 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
56 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
57 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
58 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
59 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
60 $$;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
61
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 LANGUAGE plpgsql STABLE SECURITY DEFINER
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 AS $_$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 DECLARE result BOOLEAN;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 SELECT (count(id) > 0) INTO result from land.messung where probe_id = $1 and fertig = TRUE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 RETURN result;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 $_$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 CREATE FUNCTION update_time_messung() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 RAISE NOTICE 'messung is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
88 UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 -- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 CREATE FUNCTION update_time_messwert() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 -- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 CREATE FUNCTION update_time_ort() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
118 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 CREATE FUNCTION update_time_probe() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
131 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 UPDATE land.messung SET tree_modified = now() WHERE probe_id = NEW.id;
824
6e5a0edba363 Fixed database function.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 808
diff changeset
134 UPDATE land.ortszuordnung SET tree_modified = now() WHERE probe_id = NEW.id;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 UPDATE land.zusatz_wert SET tree_modified = now() WHERE probe_id = NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
141 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
142 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
145 CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
148 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
152 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
154
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
156
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
157 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
158 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 CREATE SEQUENCE kommentar_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
162 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
163 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
166 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
167
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
169 SET default_tablespace = '';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
170
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
171 SET default_with_oids = false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
172
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
173 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
174 -- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
175 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
176
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
177 CREATE TABLE kommentar (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
178 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 erzeuger character varying(5) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
180 datum timestamp without time zone DEFAULT now(),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
181 text character varying(1024)
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
182 );
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
183
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
184
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
185 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
186 -- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
187 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
188
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
189 CREATE TABLE kommentar_m (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190 messungs_id integer NOT NULL
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 INHERITS (kommentar);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
193
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
196 -- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
197 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
198
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
199 CREATE TABLE kommentar_p (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
200 probe_id integer NOT NULL
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
201 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
202 INHERITS (kommentar);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
203
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
204
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
205 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
206 -- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
208
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209 CREATE TABLE list (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
210 id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
211 typ character(1) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
212 bezeichnung character varying(20) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
213 beschreibuing character varying(512),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214 letzte_aenderung timestamp with time zone NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
215 gueltig_bis timestamp with time zone
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
216 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
217 CREATE TRIGGER letzte_aenderung_list BEFORE UPDATE ON list FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
218
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
219
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
220 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
223
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224 CREATE SEQUENCE list_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
226 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
227 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
228 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
229 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
230
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
231
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
232 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
233 -- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
234 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
235
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
236 ALTER SEQUENCE list_id_seq OWNED BY list.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
237
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
238
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
239 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
240 -- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
241 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
242
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
243 CREATE TABLE list_zuordnung (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
244 list_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245 probe_id integer
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246 );
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
247
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
248
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
249 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
250 -- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
251 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
252
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
253 CREATE SEQUENCE messung_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
254 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
255 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
256 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
257 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
258 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
259
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
260
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
261 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
262 -- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
263 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
264
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
265 CREATE TABLE messung (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
266 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
267 probe_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
268 nebenproben_nr character varying(10),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
269 mmt_id character varying(2) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
270 messdauer integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
271 messzeitpunkt timestamp with time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
272 fertig boolean DEFAULT false NOT NULL,
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
273 status integer,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
274 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
275 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
276 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
277
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
278
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
279 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
280 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
281 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
282
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
283 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
284 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
285 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
286 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
287 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
288 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
289
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
290
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
291 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
292 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
293 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
294
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
295 CREATE SEQUENCE messung_messungs_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
296 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
297 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
298 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
299 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
300 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
301
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
302
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
303 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
304 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
305 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
306
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
307 CREATE SEQUENCE messwert_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
308 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
309 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
310 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
311 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
312 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
313
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
314
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
315 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
316 -- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
317 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
318
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
319 CREATE TABLE messwert (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
320 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
321 messungs_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
322 messgroesse_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
323 messwert_nwg character varying(1),
914
54e83373bfae We need a wider range to allow arbitrary input formated +-9.99e+-99
Tom Gottfried <tom@intevation.de>
parents: 901
diff changeset
324 messwert double precision NOT NULL,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
325 messfehler real,
914
54e83373bfae We need a wider range to allow arbitrary input formated +-9.99e+-99
Tom Gottfried <tom@intevation.de>
parents: 901
diff changeset
326 nwg_zu_messwert double precision,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
327 meh_id smallint NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
328 grenzwertueberschreitung boolean DEFAULT false,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
329 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
330 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
331 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
332
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
333
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
334 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
335 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
336 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
337
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
338 CREATE SEQUENCE ort_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
339 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
340 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
341 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
342 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
346 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
347 -- Name: ortszuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
348 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
349
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
350 CREATE TABLE ortszuordnung (
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
351 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
352 probe_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
353 ort_id bigint NOT NULL,
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
354 ortszuordnung_typ character varying(1),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
355 ortszusatztext character varying(100),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
356 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
357 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
358 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
359
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
360
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
361 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
362 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
363 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
364
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
365 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
366
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
367
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
368 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
369 -- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
370 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
371
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
372 CREATE SEQUENCE probe_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
373 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
374 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
375 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
376 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
377 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
378
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
379
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
380 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
381 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
382 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
383
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
384 CREATE TABLE probe (
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
385 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
386 test boolean DEFAULT false NOT NULL,
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
387 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
901
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
388 mst_id character varying(5) NOT NULL
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
389 REFERENCES stammdaten.mess_stelle,
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
390 labor_mst_id character varying(5) NOT NULL
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
391 REFERENCES stammdaten.mess_stelle,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
392 hauptproben_nr character varying(20),
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
393 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
991
ce1ec3cbde54 Make ID for Messregime an integer.
Tom Gottfried <tom@intevation.de>
parents: 989
diff changeset
394 ba_id integer,
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
395 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
396 media_desk character varying(100),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
397 media character varying(100),
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
398 umw_id character varying(3) REFERENCES stammdaten.umwelt,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
399 probeentnahme_beginn timestamp with time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
400 probeentnahme_ende timestamp with time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
401 mittelungsdauer bigint,
924
34c04b0557ac Added missing UNIQUE-Constraint.
Tom Gottfried <tom@intevation.de>
parents: 914
diff changeset
402 letzte_aenderung timestamp without time zone DEFAULT now(),
34c04b0557ac Added missing UNIQUE-Constraint.
Tom Gottfried <tom@intevation.de>
parents: 914
diff changeset
403 UNIQUE (mst_id, hauptproben_nr)
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
404 );
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
405
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
406 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
407
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
408 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
409 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
410 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
411
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
412 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
413
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
414
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
415 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
416 -- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
417 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
418
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
419 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
420
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
421
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
422 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
423 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
424 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
425
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
426 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
427
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
428
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
429 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
430 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
431 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
432
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
433 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
434
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
436 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
437 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
438 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
439
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
440 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
441
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
442
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
443 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
444 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
445 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
446
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
447 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
448
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
449
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
450 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
451 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
452 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
453
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
454 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
455
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
456
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
457 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
458 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
462
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
463
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
464 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
465 -- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
466 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
467
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
468 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
469
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
470
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
472 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
473 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
474
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
475 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
476
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
477
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
478 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
479 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
480 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
481
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
482 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
483 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
484 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
485 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
486 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
487 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
488
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
489
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
490 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
491 -- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace:
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
492 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
493
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
494 CREATE TABLE status_protokoll (
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
495 status_stufe integer NOT NULL,
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
496 status_wert integer NOT NULL,
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
497 tree_modified timestamp without time zone DEFAULT now()
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
498 )
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
499 INHERITS (kommentar_m);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
500
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
501
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
502 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
503 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
504 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
505
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
506 CREATE SEQUENCE zusatz_wert_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
507 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
508 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
509 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
510 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
511 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
512
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
513
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
514 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
515 -- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
516 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
517
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
518 CREATE TABLE zusatz_wert (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
519 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
520 probe_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
521 pzs_id character varying(3) NOT NULL,
914
54e83373bfae We need a wider range to allow arbitrary input formated +-9.99e+-99
Tom Gottfried <tom@intevation.de>
parents: 901
diff changeset
522 messwert_pzs double precision,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
523 messfehler real,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
524 letzte_aenderung timestamp without time zone DEFAULT now(),
914
54e83373bfae We need a wider range to allow arbitrary input formated +-9.99e+-99
Tom Gottfried <tom@intevation.de>
parents: 901
diff changeset
525 nwg_zu_messwert double precision
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
526 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
527 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
528
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
529
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
530 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
531
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
532 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
533 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
534 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
535
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
536 CREATE TABLE kommentar_m (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
537 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
538 INHERITS (bund.kommentar_m);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
539
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
540
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
541 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
542 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
543 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
544
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
545 CREATE TABLE kommentar_p (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
546 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
547 INHERITS (bund.kommentar_p);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
548
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
549
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
550 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
551 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
552 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
553
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
554 CREATE SEQUENCE messprogramm_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
555 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
556 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
557 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
558 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
559 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
560
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
561
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
562 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
563 -- Name: messprogramm; Type: TABLE; Schema: land; Owner: -; Tablespace:
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
564 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
565
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
566 CREATE TABLE messprogramm (
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
567 id integer PRIMARY KEY DEFAULT nextval('messprogramm_id_seq'::regclass),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
568 name character varying(256),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
569 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
570 netzbetreiber_id character varying(2) NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
571 REFERENCES stammdaten.netz_betreiber,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
572 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
901
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
573 labor_mst_id character varying(5) NOT NULL
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
574 REFERENCES stammdaten.mess_stelle,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
575 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
1019
e192feaf916f Introduce default value vor Messregime in Messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 991
diff changeset
576 ba_id integer DEFAULT 1,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
577 gem_id character varying(8),
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
578 ort_id integer NOT NULL REFERENCES stammdaten.ort,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
579 media_desk character varying(100),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
580 umw_id character varying(3) REFERENCES stammdaten.umwelt,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
581 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
582 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
583 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
584 teilintervall_bis integer NOT NULL,
1059
a435b9fd1d85 Set default in data model instead of on generation of Probe objects.
Tom Gottfried <tom@intevation.de>
parents: 1054
diff changeset
585 intervall_offset integer NOT NULL DEFAULT 0,
1054
3c9616e5439f Remove wrong period check.
Tom Gottfried <tom@intevation.de>
parents: 1045
diff changeset
586 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 0 AND 364),
3c9616e5439f Remove wrong period check.
Tom Gottfried <tom@intevation.de>
parents: 1045
diff changeset
587 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 0 AND 364),
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
588 probe_nehmer_id integer,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
589 probe_kommentar character varying(80),
1044
a796e68da1cf A period with end before start is not meaningful.
Tom Gottfried <tom@intevation.de>
parents: 1019
diff changeset
590 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL,
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
591 CHECK (probenintervall = 'J'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
592 AND teilintervall_von BETWEEN 1 AND 365
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
593 AND teilintervall_bis BETWEEN 1 AND 365
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
594 AND intervall_offset BETWEEN 0 AND 364
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
595 OR probenintervall = 'H'
1065
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
596 AND teilintervall_von BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
597 AND teilintervall_bis BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
598 AND intervall_offset BETWEEN 0 AND 183
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
599 OR probenintervall = 'Q'
1065
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
600 AND teilintervall_von BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
601 AND teilintervall_bis BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
602 AND intervall_offset BETWEEN 0 AND 91
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
603 OR probenintervall = 'M'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
604 AND teilintervall_von BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
605 AND teilintervall_bis BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
606 AND intervall_offset BETWEEN 0 AND 30
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
607 OR probenintervall = 'W4'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
608 AND teilintervall_von BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
609 AND teilintervall_bis BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
610 AND intervall_offset BETWEEN 0 AND 27
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
611 OR probenintervall = 'W2'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
612 AND teilintervall_von BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
613 AND teilintervall_bis BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
614 AND intervall_offset BETWEEN 0 AND 13
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
615 OR probenintervall = 'W'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
616 AND teilintervall_von BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
617 AND teilintervall_bis BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
618 AND intervall_offset BETWEEN 0 AND 6
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
619 OR probenintervall = 'T'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
620 AND teilintervall_von = 1
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
621 AND teilintervall_bis = 1
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
622 AND intervall_offset = 0
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
623 ),
1054
3c9616e5439f Remove wrong period check.
Tom Gottfried <tom@intevation.de>
parents: 1045
diff changeset
624 CHECK (teilintervall_von <= teilintervall_bis)
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
625 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
626 CREATE TRIGGER letzte_aenderung_messprogramm BEFORE UPDATE ON messprogramm FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
627
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
628 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
629 -- Name: messprogramm_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
630 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
631
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
632 ALTER SEQUENCE messprogramm_id_seq OWNED BY messprogramm.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
633
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
634
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
635 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
636 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
637 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
638
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
639 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
640
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
641
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
642 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
643 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
644 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
645
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
646 CREATE TABLE messprogramm_mmt (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
647 id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
648 messprogramm_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
649 mmt_id character varying(2) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
650 messgroessen integer[],
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
651 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
652 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
653 CREATE TRIGGER letzte_aenderung_messprogramm_mmt BEFORE UPDATE ON messprogramm_mmt FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
654
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
655
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
656 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
657 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
658 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
659
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
660 CREATE SEQUENCE messprogramm_mmt_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
661 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
662 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
663 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
664 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
665 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
666
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
667
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
668 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
669 -- Name: messprogramm_mmt_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
670 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
671
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
672 ALTER SEQUENCE messprogramm_mmt_id_seq OWNED BY messprogramm_mmt.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
673
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
674
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
675 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
676 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
677 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
678
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
679 CREATE TABLE messung (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
680 geplant boolean DEFAULT false NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
681 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
682 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
683 INHERITS (bund.messung);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
684
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
685
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
686 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
687 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
688 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
689
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
690 CREATE TABLE messung_translation (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
691 id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
692 messungs_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
693 messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
694 );
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
695
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
696
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
697 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
698 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
699 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
700
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
701 CREATE SEQUENCE messung_translation_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
702 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
703 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
704 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
705 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
706 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
707
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
708
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
709 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
710 -- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
711 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
712
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
713 ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
714
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
715
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
716 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
717 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
718 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
719
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
720 CREATE TABLE messwert (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
721 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
722 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
723 INHERITS (bund.messwert);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
724
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
725
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
726 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
727 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
728 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
729
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
730 CREATE TABLE ortszuordnung (
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
731 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
732 )
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
733 INHERITS (bund.ortszuordnung);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
734
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
735
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
736 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
737 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
738 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
739
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
740 CREATE TABLE probe (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
741 erzeuger_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
742 probe_nehmer_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
743 mp_kat character(1),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
744 mpl_id character varying(3),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
745 mpr_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
746 solldatum_beginn timestamp without time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
747 solldatum_ende timestamp without time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
748 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
749 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
750 INHERITS (bund.probe);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
751
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
752 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
753 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
754
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
755 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
756 ADD CONSTRAINT probe_datenbasis_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
757 FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
758
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
759 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
760 ADD CONSTRAINT probe_mst_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
761 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
762
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
763 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
764 ADD CONSTRAINT probe_labor_mst_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
765 FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
766
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
767 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
768 ADD CONSTRAINT probe_netzbetreiber_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
769 FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
770
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
771 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
772 ADD CONSTRAINT probe_probenart_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
773 FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
774
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
775 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
776 ADD CONSTRAINT probe_umw_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
777 FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
778
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
779
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
780 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
781 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
782 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
783
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
784 CREATE TABLE probe_translation (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
785 id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
786 probe_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
787 probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('bund.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
788 );
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
789
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
790
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
791 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
792 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
793 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
794
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
795 CREATE SEQUENCE probe_translation_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
796 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
797 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
798 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
799 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
800 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
801
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
802
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
803 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
804 -- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
805 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
806
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
807 ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
808
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
809
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
810 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
811 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
812 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
813
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
814 CREATE TABLE status_protokoll (
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
815 )
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
816 INHERITS (bund.status_protokoll);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
817
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
818
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
819 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
820 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
821 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
822
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
823 CREATE TABLE zusatz_wert (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
824 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
825 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
826 INHERITS (bund.zusatz_wert);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
827
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
828
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
829 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
830
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
831 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
832 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
833 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
834
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
835 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
836
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
837
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
838 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
839 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
840 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
841
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
842 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
843
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
844
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
845 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
846 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
847 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
848
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
849 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
850
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
851
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
852 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
853 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
854 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
855
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
856 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
857
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
858
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
859 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
860 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
861 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
862
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
863 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
864
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
865
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
866 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
867 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
868 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
869
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
870 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass);
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
871
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
872
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
873 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
874 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
875 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
876
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
877 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
878
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
879
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
880 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
881
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
882 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
883 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
884 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
885
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
886 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
887
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
888
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
889 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
890 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
891 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
892
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
893 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
894
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
895
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
896 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
897 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
898 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
899
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
900 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
901
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
902
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
903 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
904 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
905 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
906
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
907 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
908
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
909
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
910 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
911 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
912 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
913
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
914 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
915
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
916
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
917 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
918 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
919 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
920
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
921 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
922
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
923
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
924 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
925 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
926 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
927
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
928 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
929
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
930
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
931 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
932 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
933 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
934
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
935 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
936
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
937
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
938 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
939 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
940 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
941
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
942 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
943
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
944
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
945 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
946 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
947 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
948
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
949 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
950
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
951
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
952 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
953 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
954 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
955
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
956 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
957
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
958
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
959 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
960 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
961 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
962
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
963 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
964
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
965
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
966 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
967 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
968 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
969
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
970 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
971
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
972
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
973 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
974 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
975 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
976
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
977 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
978
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
979
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
980 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
981 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
982 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
983
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
984 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
985
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
986
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
987 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
988 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
989 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
990
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
991 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass);
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
992
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
993
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
994 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
995 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
996 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
997
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
998 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now();
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
999
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1000
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1001 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1002 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1003 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1004
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1005 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1006
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1007
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1008 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1009 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1010 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1011
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1012 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1013
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1014
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1015 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1016 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1017 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1018
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1019 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1020
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1021
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1022 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1023
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1024 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1025 -- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1026 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1027
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1028 ALTER TABLE ONLY kommentar
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1029 ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1030
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1031
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1032 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1033 -- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1034 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1035
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1036 ALTER TABLE ONLY list
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1037 ADD CONSTRAINT list_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1038
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1039
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1040 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1041 -- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1042 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1043
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1044 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1045 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1046
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1047
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1048 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1049 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1050 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1051
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1052 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1053 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1054
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1055
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1056 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1057 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1058 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1059
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1060 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1061 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1062
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1063
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1064 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1065 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1066 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1067
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1068 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1069 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1070
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1071
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1072 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1073 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1074 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1075
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1076 ALTER TABLE ONLY status_protokoll
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1077 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1078
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1079
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1080 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1081 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1082 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1083
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1084 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1085 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1086
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1087
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1088 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1089 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1090 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1091
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1092 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1093 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1094
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1095
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1096 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1097
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1098 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1099 -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1100 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1101
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1102 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1103 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1104
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1105
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1106 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1107 -- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1108 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1109
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1110 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1111 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1112
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1113
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1114 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1115 -- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1116 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1117
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1118 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1119 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1120
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1121
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1122 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1123 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1124 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1125
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1126 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1127 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1128
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1129
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1130 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1131 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1132 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1133
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1134 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1135 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1136
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1137
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1138 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1139 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1140 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1141
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1142 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1143 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1144
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1145
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1146 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1147 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1148 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1149
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1150 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1151 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1152
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1153
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1154 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1155 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1156 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1157
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1158 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1159 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1160
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1161
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1162 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1163 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1164 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1165
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1166 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1167 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1168
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1169
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1170 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1171 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1172 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1173
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1174 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1175 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1176
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1177
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1178 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1179 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1180 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1181
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1182 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1183 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1184
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1185
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1186 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1187 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1188 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1189
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1190 ALTER TABLE ONLY status_protokoll
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1191 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1192
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1193
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1194 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1195 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1196 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1197
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1198 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1199 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1200
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1201
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1202 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1203 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1204 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1205
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1206 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1207 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1208
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1209
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1210 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1211
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1212 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1213 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1214 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1215
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1216 CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1217
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1218
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1219 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1220 -- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1221 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1222
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1223 CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1224
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1225
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1226 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1227
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1228 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1229 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1230 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1231
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1232 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1233
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1234
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1235 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1236 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1237 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1238
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1239 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1240
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1241
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1242 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1243 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1244 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1245
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1246 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1247
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1248
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1249 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1250 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1251 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1252
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1253 CREATE INDEX probe_id_idx ON probe USING btree (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1254
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1255
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1256 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1257 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1258 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1259
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1260 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1261
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1262
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1263 SET search_path = bund, pg_catalog;
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1264
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1265 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1266 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1267 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1268
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1269 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status();
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1270
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1271
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1272 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1273
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1274 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1275 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1276 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1277
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1278 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1279
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1280
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1281 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1282 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1283 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1284
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1285 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1286
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1287
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1288 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1289 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1290 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1291
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1292 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1293
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1294
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1295 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1296 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1297 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1298
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1299 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1300
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1301
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1302 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1303 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1304 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1305
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1306 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1307
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1308
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1309 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1310
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1311 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1312 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1313 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1314
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1315 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1316 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1317
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1318
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1319 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1320 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1321 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1322
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1323 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1324 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1325
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1326
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1327 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1328 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1329 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1330
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1331 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1332 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1333
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1334
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1335 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1336 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1337 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1338
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1339 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1340 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1341
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1342
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1343 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1344 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1345 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1346
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1347 ALTER TABLE ONLY messung
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1348 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1349
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1350
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1351 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1352 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1353 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1354
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1355 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1356 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1357
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1358
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1359 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1360 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1361 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1362
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1363 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1364 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1365
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1366
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1367 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1368 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1369 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1370
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1371 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1372 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1373
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1374
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1375 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1376 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1377 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1378
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1379 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1380 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1381
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1382
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1383 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1384 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1385 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1386
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1387 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1388 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id);
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1389
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1390
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1391 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1392 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1393 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1394
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1395 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1396 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1397
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1398
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1399 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1400 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1401 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1402
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1403 ALTER TABLE ONLY status_protokoll
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1404 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id);
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1405
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1406
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1407 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1408 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1409 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1410
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1411 ALTER TABLE ONLY status_protokoll
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1412 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1413
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1414
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1415 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1416 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1417 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1418
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1419 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1420 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1421
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1422
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1423 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1424 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1425 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1426
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1427 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1428 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1429
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1430
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1431 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1432
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1433 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1434 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1435 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1436
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1437 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1438 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1439
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1440
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1441 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1442 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1443 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1444
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1445 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1446 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1447
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1448
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1449 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1450 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1451 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1452
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1453 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1454 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1455
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1456
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1457 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1458 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1459 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1460
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1461 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1462 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1463
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1464
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1465 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1466 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1467 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1468
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1469 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1470 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1471
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1472
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1473 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1474 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1475 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1476
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1477 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1478 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1479
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1480
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1481 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1482 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1483 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1484
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1485 ALTER TABLE ONLY messung
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1486 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1487
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1488
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1489 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1490 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1491 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1492
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1493 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1494 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1495
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1496
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1497 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1498 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1499 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1500
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1501 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1502 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1503
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1504
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1505 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1506 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1507 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1508
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1509 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1510 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1511
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1512
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1513 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1514 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1515 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1516
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1517 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1518 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1519
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1520
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1521 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1522 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1523 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1524
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1525 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1526 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id);
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1527
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1528
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1529 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1530 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1531 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1532
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1533 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1534 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id);
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1535
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1536
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1537 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1538 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1539 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1540
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1541 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1542 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1543
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1544
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1545 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1546 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1547 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1548
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1549 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1550 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1551
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1552
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1553 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1554 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1555 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1556
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1557 ALTER TABLE ONLY status_protokoll
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1558 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1559
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1560
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1561 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1562 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1563 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1564
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1565 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1566 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1567
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1568
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1569 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1570 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1571 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1572
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1573 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1574 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1575
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1576
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
1577 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)