Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1091:8f2b1a8f2a73

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Apply database seeding only once and run all tests in sequence. Before many tests were run before the database was filled and thus failed on first run.
author Tom Gottfried <tom@intevation.de>
date Thu, 13 Oct 2016 21:25:51 +0200
parents 6499f2410c42
children 186d602e031a
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,
1077
7143cd96d3de Calendar.DAY_OF_YEAR is 1-based.
Tom Gottfried <tom@intevation.de>
parents: 1066
diff changeset
586 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365),
7143cd96d3de Calendar.DAY_OF_YEAR is 1-based.
Tom Gottfried <tom@intevation.de>
parents: 1066
diff changeset
587 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365),
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'
1082
6499f2410c42 Fix validation of subintervall for yearly samples.
Tom Gottfried <tom@intevation.de>
parents: 1077
diff changeset
592 AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis
6499f2410c42 Fix validation of subintervall for yearly samples.
Tom Gottfried <tom@intevation.de>
parents: 1077
diff changeset
593 AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis
1063
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,
1066
daca5c62356f A period with end before start is meaningless.
Tom Gottfried <tom@intevation.de>
parents: 1065
diff changeset
748 tree_modified timestamp without time zone DEFAULT now(),
daca5c62356f A period with end before start is meaningless.
Tom Gottfried <tom@intevation.de>
parents: 1065
diff changeset
749 CHECK(solldatum_beginn <= solldatum_ende)
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
750 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
751 INHERITS (bund.probe);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
752
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
753 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
754 ADD CONSTRAINT probe_pkey PRIMARY KEY (id);
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
755
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
756 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
757 ADD CONSTRAINT probe_datenbasis_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
758 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
759
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
760 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
761 ADD CONSTRAINT probe_mst_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
762 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
763
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
764 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
765 ADD CONSTRAINT probe_labor_mst_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
766 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
767
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
768 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
769 ADD CONSTRAINT probe_netzbetreiber_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
770 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
771
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
772 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
773 ADD CONSTRAINT probe_probenart_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
774 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
775
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
776 ALTER TABLE ONLY probe
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
777 ADD CONSTRAINT probe_umw_id_fkey
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
778 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
779
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
780
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
781 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
782 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
785 CREATE TABLE probe_translation (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
786 id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
787 probe_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
788 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
793 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
796 CREATE SEQUENCE probe_translation_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
797 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
798 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
799 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
800 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
801 CACHE 1;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
805 -- 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
806 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
807
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
808 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
809
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
810
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
811 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
812 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
815 CREATE TABLE status_protokoll (
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
816 )
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
817 INHERITS (bund.status_protokoll);
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
820 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
821 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
824 CREATE TABLE zusatz_wert (
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
825 tree_modified timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
826 )
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
827 INHERITS (bund.zusatz_wert);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
830 SET search_path = bund, pg_catalog;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
833 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
836 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
840 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
843 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
847 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
850 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
854 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
857 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
861 -- Name: id; Type: DEFAULT; Schema: bund; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
864 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
865
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
866
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
867 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
868 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
871 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
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 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
875 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
878 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
879
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
880
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
881 SET search_path = land, pg_catalog;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
884 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
887 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
891 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
894 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
898 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
901 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
905 -- Name: datum; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
908 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
912 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
915 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
919 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
922 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
926 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
929 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
933 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
936 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
940 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
943 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
947 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
950 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
954 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
957 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
961 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
964 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
968 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
969 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
970
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
971 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
975 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
976 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
977
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
978 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
982 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
985 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
989 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
990 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
991
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
992 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
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 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
996 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
999 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
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 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1003 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1006 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1010 -- Name: id; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1013 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1017 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1020 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
1021
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1022
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1023 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1024
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1025 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1026 -- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1029 ALTER TABLE ONLY kommentar
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1030 ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1033 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1034 -- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1037 ALTER TABLE ONLY list
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1038 ADD CONSTRAINT list_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1041 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1042 -- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1045 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1046 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1049 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1050 -- 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
1051 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1052
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1053 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1054 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
1055
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1056
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1057 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1058 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1061 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1062 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1065 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1066 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace:
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
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1069 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1070 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1073 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1074 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1077 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
1078 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1081 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1082 -- 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
1083 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1084
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1085 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1086 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1089 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1090 -- 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
1091 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1092
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1093 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1094 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
1095
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1096
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1097 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1098
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1099 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1100 -- 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
1101 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1102
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1103 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1104 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1107 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1108 -- 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
1109 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1110
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1111 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1112 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1115 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1116 -- 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
1117 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1118
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1119 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1120 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1123 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1124 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1127 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1128 ADD CONSTRAINT messung_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1131 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1132 -- 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
1133 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1134
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1135 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1136 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1139 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1140 -- 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
1141 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1142
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1143 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1144 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1147 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1148 -- 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
1149 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1150
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1151 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1152 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
1153
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1154
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1155 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1156 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1159 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1160 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1163 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1164 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace:
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
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1167 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1168 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id);
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1171 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1172 -- 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
1173 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1174
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1175 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1176 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1179 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1180 -- 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
1181 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1182
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1183 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1184 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1187 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1188 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1191 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
1192 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id);
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1195 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1196 -- 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
1197 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1198
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1199 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1200 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1203 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1204 -- 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
1205 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1206
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1207 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1208 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
1209
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1210
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1211 SET search_path = bund, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1212
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1213 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1214 -- 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
1215 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1216
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1217 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
1218
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1219
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1220 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1221 -- 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
1222 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1223
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1224 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
1225
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1226
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1227 SET search_path = land, pg_catalog;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1228
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1229 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1230 -- 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
1231 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1232
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1233 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
1234
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1235
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1236 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1237 -- 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
1238 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1239
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1240 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
1241
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1242
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1243 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1244 -- 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
1245 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1246
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1247 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
1248
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1249
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1250 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1251 -- 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
1252 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1253
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1254 CREATE INDEX probe_id_idx ON probe USING btree (id);
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1257 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
1258 -- 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
1259 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1260
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1261 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
1262
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1263
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1264 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
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 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1267 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1270 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
1271
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1272
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1273 SET search_path = land, pg_catalog;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1276 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1279 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1283 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1286 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1290 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1291 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1292
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1293 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1297 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1300 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1304 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1307 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
1308
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1309
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1310 SET search_path = bund, pg_catalog;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1313 -- 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
1314 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1315
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1316 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1317 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1321 -- 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
1322 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1323
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1324 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1325 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1329 -- 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
1330 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1331
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1332 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1333 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1337 -- 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
1338 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1339
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1340 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1341 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
1342
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1343
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1344 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1345 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1348 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
1349 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1352 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1353 -- 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
1354 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1355
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1356 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1357 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1361 -- 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
1362 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1363
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1364 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1365 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1369 -- 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
1370 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1371
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1372 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1373 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
1374
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1375
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1376 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1377 -- 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
1378 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1379
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1380 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1381 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
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 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1385 -- 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
1386 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1387
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1388 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1389 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
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 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1393 -- 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
1394 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1395
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1396 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1397 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
1398
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1399
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1400 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1401 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1404 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
1405 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
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 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1409 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1412 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
1413 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1417 -- 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
1418 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1419
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1420 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1421 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1425 -- 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
1426 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1427
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1428 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1429 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
1430
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1431
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1432 SET search_path = land, pg_catalog;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1435 -- 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
1436 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1437
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1438 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1439 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1443 -- 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
1444 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1445
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1446 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1447 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1451 -- 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
1452 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1453
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1454 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1455 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1459 -- 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
1460 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1461
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1462 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1463 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1467 -- 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
1468 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1469
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1470 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1471 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1475 -- 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
1476 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1477
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1478 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1479 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
1480
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1481
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1482 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1483 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1486 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
1487 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1490 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1491 -- 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
1492 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1493
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1494 ALTER TABLE ONLY messung_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1495 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1499 -- 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
1500 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1501
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1502 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1503 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1507 -- 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
1508 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1509
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1510 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1511 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1515 -- 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
1516 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1517
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1518 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1519 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
1520
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1521
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1522 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1523 -- 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
1524 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1525
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1526 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1527 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
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 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1531 -- 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
1532 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1533
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1534 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1535 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
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 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1539 -- 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
1540 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1541
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1542 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
1543 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1547 -- 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
1548 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1549
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1550 ALTER TABLE ONLY probe_translation
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1551 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
1552
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1553
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1554 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1555 -- 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
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
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
1558 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
1559 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1563 -- 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
1564 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1565
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1566 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1567 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
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1571 -- 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
1572 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1573
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1574 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1575 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
1576
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1577
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
1578 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)