Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1315:84bb7e2aecb1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do not redefine existing operator. The removed SQL failed on PostgreSQL 9.5 because the operator is provided and needed by the system. Further, it does not seem to be used here.
author Tom Gottfried <tom@intevation.de>
date Fri, 17 Mar 2017 13:25:57 +0100
parents 9dfb52db6a0f
children
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
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
19 SET search_path = land, pg_catalog;
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
20
1164
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
21 CREATE FUNCTION set_messung_id_alt() RETURNS trigger
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
22 LANGUAGE plpgsql
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
23 AS $$
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
24 BEGIN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
25 IF NEW.id_alt IS NULL THEN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
26 NEW.id_alt = (
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
27 SELECT count(*)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
28 FROM land.messung
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
29 WHERE probe_id = NEW.probe_id) + 1;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
30 END IF;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
31 RETURN NEW;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
32 END;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
33 $$;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
34
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
35 CREATE FUNCTION set_messung_status() RETURNS trigger
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
36 LANGUAGE plpgsql
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
37 AS $$
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
38 DECLARE status_id integer;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
39 BEGIN
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
40 INSERT INTO land.status_protokoll
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
41 (mst_id, datum, text, messungs_id, status_kombi)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
42 VALUES ((SELECT mst_id
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
43 FROM land.probe
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
44 WHERE id = NEW.probe_id),
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
45 now(), '', NEW.id, 1)
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
46 RETURNING id into status_id;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
47 UPDATE land.messung SET status = status_id where id = NEW.id;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
48 RETURN NEW;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
49 END;
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
50 $$;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
52 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
53 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
54 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
55 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
56 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
57 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
58 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
59 $$;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
60
741
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 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
63 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
66 CREATE FUNCTION update_tree_modified() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 NEW.tree_modified = now();
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 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
77 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
741
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
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
80 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
741
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
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
84 RAISE NOTICE 'messung is %',NEW.id;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 NEW.tree_modified = now();
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
86 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
87 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
88 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
89 END;
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
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 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
97 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
100 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
101 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 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
105 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
106 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
107 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
109 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
110
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
111
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
112 SET default_tablespace = '';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
113
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
114 SET default_with_oids = false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
115
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
116
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
117 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
118 -- 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
119 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
120
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
121 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
122 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
123 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
124 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
125 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
126 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
127
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
128
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
129 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
130 -- 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
131 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
132
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
133 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
134 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
135 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
136 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
137 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
138 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
139
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
140
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
141 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
142 -- 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
143 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
144
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
145 CREATE TABLE messprogramm (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
146 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
147 kommentar character varying(1000),
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
148 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
149 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
150 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
151 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
1120
7061388e0af7 Rev d1d7d684800c missed to adapt default FK value.
Tom Gottfried <tom@intevation.de>
parents: 1097
diff changeset
152 ba_id integer DEFAULT 1 REFERENCES stammdaten.betriebsart,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
153 gem_id character varying(8) REFERENCES stammdaten.verwaltungseinheit,
1122
1ef08710aaeb Do not allow empty string for media descriptor.
Tom Gottfried <tom@intevation.de>
parents: 1120
diff changeset
154 media_desk character varying(100) CHECK(media_desk LIKE '% %'),
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
155 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
156 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
157 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
158 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
159 teilintervall_bis integer NOT NULL,
1059
a435b9fd1d85 Set default in data model instead of on generation of Probe objects.
Tom Gottfried <tom@intevation.de>
parents: 1054
diff changeset
160 intervall_offset integer NOT NULL DEFAULT 0,
1077
7143cd96d3de Calendar.DAY_OF_YEAR is 1-based.
Tom Gottfried <tom@intevation.de>
parents: 1066
diff changeset
161 gueltig_von integer NOT NULL CHECK(gueltig_von BETWEEN 1 AND 365),
7143cd96d3de Calendar.DAY_OF_YEAR is 1-based.
Tom Gottfried <tom@intevation.de>
parents: 1066
diff changeset
162 gueltig_bis integer NOT NULL CHECK(gueltig_bis BETWEEN 1 AND 365),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
163 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
1189
4ee336c65ab3 Add MPL to Messprogramm
Evi Huber <ehuber@bfs.de>
parents: 1168
diff changeset
164 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
165 probe_kommentar character varying(80),
1044
a796e68da1cf A period with end before start is not meaningful.
Tom Gottfried <tom@intevation.de>
parents: 1019
diff changeset
166 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL,
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
167 CHECK (probenintervall = 'J'
1082
6499f2410c42 Fix validation of subintervall for yearly samples.
Tom Gottfried <tom@intevation.de>
parents: 1077
diff changeset
168 AND teilintervall_von BETWEEN gueltig_von AND gueltig_bis
6499f2410c42 Fix validation of subintervall for yearly samples.
Tom Gottfried <tom@intevation.de>
parents: 1077
diff changeset
169 AND teilintervall_bis BETWEEN gueltig_von AND gueltig_bis
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
170 AND intervall_offset BETWEEN 0 AND 364
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
171 OR probenintervall = 'H'
1065
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
172 AND teilintervall_von BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
173 AND teilintervall_bis BETWEEN 1 AND 184
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
174 AND intervall_offset BETWEEN 0 AND 183
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
175 OR probenintervall = 'Q'
1065
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
176 AND teilintervall_von BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
177 AND teilintervall_bis BETWEEN 1 AND 92
7d87ddba5520 Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents: 1063
diff changeset
178 AND intervall_offset BETWEEN 0 AND 91
1063
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
179 OR probenintervall = 'M'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
180 AND teilintervall_von BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
181 AND teilintervall_bis BETWEEN 1 AND 31
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
182 AND intervall_offset BETWEEN 0 AND 30
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
183 OR probenintervall = 'W4'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
184 AND teilintervall_von BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
185 AND teilintervall_bis BETWEEN 1 AND 28
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
186 AND intervall_offset BETWEEN 0 AND 27
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
187 OR probenintervall = 'W2'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
188 AND teilintervall_von BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
189 AND teilintervall_bis BETWEEN 1 AND 14
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
190 AND intervall_offset BETWEEN 0 AND 13
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
191 OR probenintervall = 'W'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
192 AND teilintervall_von BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
193 AND teilintervall_bis BETWEEN 1 AND 7
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
194 AND intervall_offset BETWEEN 0 AND 6
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
195 OR probenintervall = 'T'
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
196 AND teilintervall_von = 1
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
197 AND teilintervall_bis = 1
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
198 AND intervall_offset = 0
7788a805a98d Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents: 1059
diff changeset
199 ),
1054
3c9616e5439f Remove wrong period check.
Tom Gottfried <tom@intevation.de>
parents: 1045
diff changeset
200 CHECK (teilintervall_von <= teilintervall_bis)
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
201 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
202 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
203
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
204
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
205 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
206 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
207 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
208
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
209 CREATE TABLE messprogramm_mmt (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
210 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
211 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
212 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode,
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
213 messgroessen integer[],
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
214 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
215 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
216 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
217
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
218
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
219 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
220 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
221 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
222
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
223 CREATE TABLE probe (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
224 id serial PRIMARY KEY,
1129
48c0132dbc85 Adapt definition to LAF and validate uniqueness of idAlt.
Tom Gottfried <tom@intevation.de>
parents: 1126
diff changeset
225 id_alt character varying(16) UNIQUE NOT NULL
1126
70b51893f15b Fix UNIQUE-constraint and respective validation.
Tom Gottfried <tom@intevation.de>
parents: 1122
diff changeset
226 DEFAULT 'sss'
70b51893f15b Fix UNIQUE-constraint and respective validation.
Tom Gottfried <tom@intevation.de>
parents: 1122
diff changeset
227 || lpad(nextval('land.probe_probe_id_seq')::varchar, 12, '0')
70b51893f15b Fix UNIQUE-constraint and respective validation.
Tom Gottfried <tom@intevation.de>
parents: 1122
diff changeset
228 || 'Y',
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
229 test boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
230 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
231 labor_mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
232 hauptproben_nr character varying(20),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
233 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
234 ba_id integer REFERENCES stammdaten.betriebsart,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
235 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
1122
1ef08710aaeb Do not allow empty string for media descriptor.
Tom Gottfried <tom@intevation.de>
parents: 1120
diff changeset
236 media_desk character varying(100) CHECK(media_desk LIKE '% %'),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
237 media character varying(100),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
238 umw_id character varying(3) REFERENCES stammdaten.umwelt,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
239 probeentnahme_beginn timestamp with time zone,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
240 probeentnahme_ende timestamp with time zone,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
241 mittelungsdauer bigint,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
242 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
243 erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
244 probe_nehmer_id integer REFERENCES stammdaten.probenehmer,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
245 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
246 mpr_id integer REFERENCES messprogramm,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
247 solldatum_beginn timestamp without time zone,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
248 solldatum_ende timestamp without time zone,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
249 tree_modified timestamp without time zone DEFAULT now(),
1126
70b51893f15b Fix UNIQUE-constraint and respective validation.
Tom Gottfried <tom@intevation.de>
parents: 1122
diff changeset
250 UNIQUE (mst_id, hauptproben_nr),
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
251 CHECK(solldatum_beginn <= solldatum_ende)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
252 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
253 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
254 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
255
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
256
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
257 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
258 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
259 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
260
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
261 CREATE TABLE kommentar_p (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
262 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
263 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
264 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
265 text character varying(1024),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
266 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
267 );
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 --
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
271 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
272 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
273
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
274 CREATE TABLE ortszuordnung (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
275 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
276 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1215
6e393b614edf The referencing column does not need to be wider than what it refereces.
Tom Gottfried <tom@intevation.de>
parents: 1189
diff changeset
277 ort_id integer NOT NULL REFERENCES stammdaten.ort,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
278 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
279 ortszusatztext character varying(100),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
280 letzte_aenderung timestamp without time zone DEFAULT now(),
1168
cd9bf2430eef Let the database enforce,too, what rev 46db98d060ae introduced.
Tom Gottfried <tom@intevation.de>
parents: 1164
diff changeset
281 tree_modified timestamp without time zone DEFAULT now(),
cd9bf2430eef Let the database enforce,too, what rev 46db98d060ae introduced.
Tom Gottfried <tom@intevation.de>
parents: 1164
diff changeset
282 EXCLUDE (probe_id WITH =) WHERE (ortszuordnung_typ = 'E')
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
283 );
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
284 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
285 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
286
1262
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
287 --
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
288 -- Name: ortszuordnung_mp; Type: TABLE; Schema: land; Owner: -; Tablespace:
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
289 --
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
290
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
291 CREATE TABLE ortszuordnung_mp (
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
292 id serial PRIMARY KEY,
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
293 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
294 ort_id integer NOT NULL REFERENCES stammdaten.ort,
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
295 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
296 ortszusatztext character varying(100),
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
297 letzte_aenderung timestamp without time zone DEFAULT now(),
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
298 tree_modified timestamp without time zone DEFAULT now(),
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
299 EXCLUDE (messprogramm_id WITH =) WHERE (ortszuordnung_typ = 'E')
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
300 );
9dfb52db6a0f Make use of the ort factory in ort service.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1215
diff changeset
301 CREATE TRIGGER letzte_aenderung_ortszuordnung_mp BEFORE UPDATE ON ortszuordnung_mp FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
302
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
303 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
304 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
305 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
306
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
307 CREATE TABLE zusatz_wert (
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
308 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
309 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
310 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
311 messwert_pzs double precision,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
312 messfehler real,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
313 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
314 nwg_zu_messwert double precision,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
315 tree_modified timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
316 UNIQUE (probe_id, pzs_id)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
317 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
318 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
319 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
320
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
321
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
322 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
323 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
324 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
325
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
326 CREATE TABLE messung (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
327 id serial PRIMARY KEY,
1164
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
328 id_alt integer NOT NULL,
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
329 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
330 nebenproben_nr character varying(10),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
331 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
332 messdauer integer,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
333 messzeitpunkt timestamp with time zone,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
334 fertig boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
335 status integer,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
336 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
337 geplant boolean DEFAULT false NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
338 tree_modified timestamp without time zone DEFAULT now()
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
339 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
340 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
341 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
1164
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
342 CREATE TRIGGER id_alt_messung BEFORE INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_id_alt();
84ff77bba9b0 Let the database generate id_alt and initial status for new messung objects.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1129
diff changeset
343 CREATE TRIGGER status_messung AFTER INSERT ON land.messung FOR EACH ROW EXECUTE PROCEDURE set_messung_status();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
344
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
345 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
346 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
347 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
348
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
349 CREATE TABLE kommentar_m (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
350 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
351 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
352 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
353 text character varying(1024),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
354 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
355 );
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
356
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
357
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
358 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
359 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
360 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
361
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
362 CREATE TABLE messwert (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
363 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
364 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
365 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
366 messwert_nwg character varying(1),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
367 messwert double precision NOT NULL,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
368 messfehler real,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
369 nwg_zu_messwert double precision,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
370 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
371 grenzwertueberschreitung boolean DEFAULT false,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
372 letzte_aenderung timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
373 tree_modified timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
374 UNIQUE (messungs_id, messgroesse_id)
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
375 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
376 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
377 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
378
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
379 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
380 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
381 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
382
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
383 CREATE TABLE status_protokoll (
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
384 id serial PRIMARY KEY,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
385 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
386 datum timestamp without time zone DEFAULT now(),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
387 text character varying(1024),
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
388 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
389 status_kombi integer NOT NULL REFERENCES stammdaten.status_kombi,
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
390 tree_modified timestamp without time zone DEFAULT now()
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
391 );
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
392 CREATE TRIGGER tree_modified_status_protokoll BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
393
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
394 ALTER TABLE ONLY messung
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
395 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
396
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
397
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
398 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
399 -- 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
400 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
401
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
402 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
403
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
404
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
405 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
406 -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
407 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
408
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
409 CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id);
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
410
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
411
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
412 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
413 -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
414 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
415
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
416 CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id);
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
417
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
418
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
419 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
420 -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
421 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
422
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
423 CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id);
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
424
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
425
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
426 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
427 -- 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
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 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
431
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 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
434 -- Name: status_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
435 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
436
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
437 CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id);
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
438
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
439
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
440 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
441 -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
442 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
443
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
444 CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id);
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
445
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
446
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
447 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
448 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
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
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
451 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
452
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
453
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
454 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
455 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
808
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
456 --
2b0d99fbeecc Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 799
diff changeset
457
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
458 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
459
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
460
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
461 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
462 -- Name: COLUMN probe.test; Type: COMMENT; 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
463 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
464
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
465 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
466
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
467
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
468 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
469 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
470 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
471
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
472 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
473
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
474
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
475 --
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
476 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
477 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
478
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
479 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
480
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
481
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
482 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
483 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
484 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
485
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
486 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
487
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
488
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
489 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
490 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
491 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
492
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
493 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
494
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
495
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
496 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
497 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
498 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
499
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
500 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
501
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
502
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
503 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
504 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
505 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
506
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
507 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
508
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
509
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
510 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
511 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
512 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
513
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
514 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
515
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
516
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
517 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
518 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
519 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
520
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
521 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
522
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
523
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
524 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
525 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
526 --
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
527
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1082
diff changeset
528 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
529
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
530
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
531 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)