Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1275:a356b818389a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use gem_id for ort_id default value if exists.
author Raimund Renkert <raimund.renkert@intevation.de>
date Wed, 01 Feb 2017 11:23:09 +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)