annotate db_schema/lada_schema.sql @ 997:1f23867d69e9 schema-update

Removed duplicated defaults.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 18:07:33 +0200
parents f77a9e85b122
children c22a99d5c6f1
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: land; 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 land;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
19 SET search_path = land, pg_catalog;
741
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 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
22 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
23 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
24
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
25 CREATE FUNCTION update_time_status() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 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
29 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
30 RETURN NEW;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
34 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
35 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
36 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
37 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
38 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
39 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
40 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
41 $$;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
43
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 CREATE FUNCTION is_probe_fertig(i1 integer) RETURNS boolean
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 LANGUAGE plpgsql STABLE SECURITY DEFINER
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 AS $_$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 DECLARE result BOOLEAN;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 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
54 RETURN result;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 $_$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 --
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 CREATE FUNCTION update_time_messung() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 RAISE NOTICE 'messung is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
70 UPDATE land.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
71 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 $$;
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 -- Name: update_time_messwert(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 --
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 CREATE FUNCTION update_time_messwert() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
88
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 -- Name: update_time_ort(); Type: FUNCTION; Schema: land; Owner: -
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 CREATE FUNCTION update_time_ort() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
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 CREATE FUNCTION update_time_probe() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115 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
116 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
117 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
118 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
119 END;
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 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 -- Name: update_time_zusatzwert(); Type: FUNCTION; Schema: land; Owner: -
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127 CREATE FUNCTION update_time_zusatzwert() RETURNS trigger
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
130 BEGIN
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 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
134 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137 SET default_tablespace = '';
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 SET default_with_oids = false;
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 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
143 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: -
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
146 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
147 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
148 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
149 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
150 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
151 CACHE 1;
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 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
155 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: -
741
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 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
159 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
162 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
163 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
166 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
167 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
170 CREATE TABLE kommentar_m (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
171 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
172 erzeuger character varying(5) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
173 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
174 text character varying(1024),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
175 messungs_id integer NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
176 );
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
177
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
178
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
179 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
180 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
181 --
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 CREATE TABLE kommentar_p (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
184 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
185 erzeuger character varying(5) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
186 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
187 text character varying(1024),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
188 probe_id integer NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
189 );
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190
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 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
193 -- 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
194 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
195
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
196 CREATE TABLE messprogramm (
994
5886384dcb92 Replaced explicit sequences by serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 993
diff changeset
197 id serial PRIMARY KEY,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
198 name character varying(256),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
199 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
200 netzbetreiber_id character varying(2) NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
201 REFERENCES stammdaten.netz_betreiber,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
202 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
203 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
204 REFERENCES stammdaten.mess_stelle,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
205 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
206 ba_id character varying(1),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
207 gem_id character varying(8),
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
208 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
209 media_desk character varying(100),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
210 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
211 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
212 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
213 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
214 teilintervall_bis integer NOT NULL,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
215 intervall_offset integer,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
216 gueltig_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
217 gueltig_bis integer NOT NULL,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
218 probe_nehmer_id integer,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
219 probe_kommentar character varying(80),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
220 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
221 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
222 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
223
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
224
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
225 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
226 -- 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
227 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
228
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
229 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
230
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
231
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
232 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
233 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
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 CREATE TABLE messprogramm_mmt (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
237 id serial PRIMARY KEY,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
238 messprogramm_id integer NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
239 mmt_id character varying(2) NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
240 messgroessen integer[],
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
241 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
242 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
243 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
244
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
245
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
246 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
247 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
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 CREATE TABLE messung (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
251 id serial PRIMARY KEY,
993
196800bb22b0 Replaced translation tables for probe and messung with id_alt columns.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 992
diff changeset
252 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
253 probe_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
254 nebenproben_nr character varying(10),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
255 mmt_id character varying(2) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
256 messdauer integer,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
257 messzeitpunkt timestamp with time zone,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
258 fertig boolean DEFAULT false NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
259 status integer,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
260 letzte_aenderung timestamp without time zone DEFAULT now(),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
261 geplant boolean DEFAULT false NOT NULL,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
262 tree_modified timestamp without time zone DEFAULT now()
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
263 );
741
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
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
266 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
267 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
268 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
269
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
270 CREATE TABLE messwert (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
271 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
272 messungs_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
273 messgroesse_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
274 messwert_nwg character varying(1),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
275 messwert double precision NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
276 messfehler real,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
277 nwg_zu_messwert double precision,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
278 meh_id smallint NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
279 grenzwertueberschreitung boolean DEFAULT false,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
280 letzte_aenderung timestamp without time zone DEFAULT now(),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
281 tree_modified timestamp without time zone DEFAULT now()
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
282 );
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
283 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
284
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
285
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
286 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
287 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
288 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
289
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
290 CREATE TABLE ortszuordnung (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
291 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
292 probe_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
293 ort_id bigint NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
294 ortszuordnung_typ character varying(1),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
295 ortszusatztext character varying(100),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
296 letzte_aenderung timestamp without time zone DEFAULT now(),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
297 tree_modified timestamp without time zone DEFAULT now()
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
298 );
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
299 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
300
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
301 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
302 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
303 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
304
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
305 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
306
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
307
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
308 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
309 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
310 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
311
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
312 CREATE TABLE probe (
994
5886384dcb92 Replaced explicit sequences by serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 993
diff changeset
313 id serial PRIMARY KEY,
993
196800bb22b0 Replaced translation tables for probe and messung with id_alt columns.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 992
diff changeset
314 id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
315 test boolean DEFAULT false NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
316 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
317 mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
318 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
319 labor_mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
320 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
321 hauptproben_nr character varying(20),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
322 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
323 ba_id character varying(1),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
324 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
325 media_desk character varying(100),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
326 media character varying(100),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
327 umw_id character varying(3) REFERENCES stammdaten.umwelt,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
328 probeentnahme_beginn timestamp with time zone,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
329 probeentnahme_ende timestamp with time zone,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
330 mittelungsdauer bigint,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
331 letzte_aenderung timestamp without time zone DEFAULT now(),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
332 erzeuger_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
333 probe_nehmer_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
334 mp_kat character(1),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
335 mpl_id character varying(3),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
336 mpr_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
337 solldatum_beginn timestamp without time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
338 solldatum_ende timestamp without time zone,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
339 tree_modified timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
340 UNIQUE (mst_id, hauptproben_nr)
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
341 );
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
342 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
343
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
344 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
345 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
346 --
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
347
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
348 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
349
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
350
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
351 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
352 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
353 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
354
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
355 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
356
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
357
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
358 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
359 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
360 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
361
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
362 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
363
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
364
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
365 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
366 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
367 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
368
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
369 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
370
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
371
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
372 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
373 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
374 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
375
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
376 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
377
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
378
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
379 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
380 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
381 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
382
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
383 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
384
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
385
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
386 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
387 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
388 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
389
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
390 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
391
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
392
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
393 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
394 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
395 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
396
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
397 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
398
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
399
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
400 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
401 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
402 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
403
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
404 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
405
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
406
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
407 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
408 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
409 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
410
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
411 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
412
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
413
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
414 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
415 -- 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
416 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
417
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
418 CREATE TABLE status_protokoll (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
419 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
420 erzeuger character varying(5) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
421 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
422 text character varying(1024),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
423 messungs_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
424 status_stufe integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
425 status_wert integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
426 tree_modified timestamp without time zone DEFAULT now()
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
427 );
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
428
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
429
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
430 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
431 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
432 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
433
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
434 CREATE TABLE zusatz_wert (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
435 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
436 probe_id integer NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
437 pzs_id character varying(3) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
438 messwert_pzs double precision,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
439 messfehler real,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
440 letzte_aenderung timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
441 nwg_zu_messwert double precision,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
442 tree_modified timestamp without time zone DEFAULT now()
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
443 );
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
444 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
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 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
448 -- 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
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 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
452 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
453
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
454
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
455 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
456 -- 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
457 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
458
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460 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
461
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 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
464 -- 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
465 --
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 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
468
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 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
471 -- 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
472 --
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 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
475
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 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
478 -- 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
479 --
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 CREATE INDEX probe_id_idx ON probe USING btree (id);
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
482
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
483
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
484 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
485 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: -
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
486 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
487
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
488 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
489
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
490
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
491 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
492 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
493 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
494
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
495 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
496
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
497
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
498 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
499 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: -
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 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
503
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 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
507 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
508
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
509 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
510
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
511
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 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
514 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
515
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
516 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
517
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
518
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
519 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
520 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
521 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
522
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
523 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
524
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
525
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
526 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
527 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
528 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
529
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
530 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
531 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
532
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
533
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
534 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
535 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: land; Owner: -
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
536 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
537
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
538 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
539 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
540
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
541
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
542 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
543 -- 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
544 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
545
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
546 ALTER TABLE ONLY kommentar_m
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
547 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
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: 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
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 ALTER TABLE ONLY kommentar_p
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
555 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
556
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
557
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
558 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
559 -- 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
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 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
563 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
564
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
565
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
566 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
567 -- 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
568 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
569
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
570 ALTER TABLE ONLY messprogramm_mmt
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
571 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
572
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
573
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
574 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
575 -- 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
576 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
577
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
578 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
579 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
580
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
581
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
582 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
583 -- 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
584 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
585
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
586 ALTER TABLE ONLY messung
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
587 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
588
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
589
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
590 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
591 -- 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
592 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
593
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
594 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
595 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
596
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
597
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
598 --
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
599 -- 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
600 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
601
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
602 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
603 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
604
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
605
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
606 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
607 -- 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
608 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
609
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
610 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
611 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
612
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
613
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
614 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
615 -- 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
616 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
617
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
618 ALTER TABLE ONLY messwert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
619 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
620
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
621
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
622 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
623 -- 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
624 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
625
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
626 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
627 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
628
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
629
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
630 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
631 -- 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
632 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
633
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
634 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
635 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
636
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
637
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
638 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
639 -- 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
640 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
641
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
642 ALTER TABLE ONLY ortszuordnung
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
643 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
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 --
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
647 -- 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
648 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
649
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
650 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
651 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
652
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
653
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 -- 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
656 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
657
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
658 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
659 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
660
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
661
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
662 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
663 -- 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
664 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
665
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
666 ALTER TABLE ONLY zusatz_wert
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
667 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
668
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
669
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
670 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)