Mercurial > lada > lada-server
annotate db_schema/lada_schema.sql @ 1204:45308f8a4092
That statement has surely never been successfully tested.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 02 Dec 2016 19:15:35 +0100 |
parents | 4ee336c65ab3 |
children | 6e393b614edf |
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, |
989
b943e956b767
Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents:
927
diff
changeset
|
154 ort_id integer NOT NULL REFERENCES stammdaten.ort, |
1122
1ef08710aaeb
Do not allow empty string for media descriptor.
Tom Gottfried <tom@intevation.de>
parents:
1120
diff
changeset
|
155 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
|
156 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
|
157 probenart_id integer NOT NULL REFERENCES stammdaten.probenart, |
989
b943e956b767
Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents:
927
diff
changeset
|
158 probenintervall character varying(2) NOT NULL, |
b943e956b767
Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents:
927
diff
changeset
|
159 teilintervall_von integer NOT NULL, |
b943e956b767
Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents:
927
diff
changeset
|
160 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
|
161 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
|
162 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
|
163 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
|
164 probe_nehmer_id integer REFERENCES stammdaten.probenehmer, |
1189 | 165 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
|
166 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
|
167 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
|
168 CHECK (probenintervall = 'J' |
1082
6499f2410c42
Fix validation of subintervall for yearly samples.
Tom Gottfried <tom@intevation.de>
parents:
1077
diff
changeset
|
169 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
|
170 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
|
171 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
|
172 OR probenintervall = 'H' |
1065
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
173 AND teilintervall_von BETWEEN 1 AND 184 |
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
174 AND teilintervall_bis BETWEEN 1 AND 184 |
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
175 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
|
176 OR probenintervall = 'Q' |
1065
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
177 AND teilintervall_von BETWEEN 1 AND 92 |
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
178 AND teilintervall_bis BETWEEN 1 AND 92 |
7d87ddba5520
Validate against possible maxima.
Tom Gottfried <tom@intevation.de>
parents:
1063
diff
changeset
|
179 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
|
180 OR probenintervall = 'M' |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
181 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
|
182 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
|
183 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
|
184 OR probenintervall = 'W4' |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
185 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
|
186 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
|
187 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
|
188 OR probenintervall = 'W2' |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
189 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
|
190 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
|
191 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
|
192 OR probenintervall = 'W' |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
193 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
|
194 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
|
195 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
|
196 OR probenintervall = 'T' |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
197 AND teilintervall_von = 1 |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
198 AND teilintervall_bis = 1 |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
199 AND intervall_offset = 0 |
7788a805a98d
Complement data model and validation of sub-intervalls.
Tom Gottfried <tom@intevation.de>
parents:
1059
diff
changeset
|
200 ), |
1054
3c9616e5439f
Remove wrong period check.
Tom Gottfried <tom@intevation.de>
parents:
1045
diff
changeset
|
201 CHECK (teilintervall_von <= teilintervall_bis) |
900
26d57ae6dd2f
Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents:
899
diff
changeset
|
202 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
914
diff
changeset
|
203 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
|
204 |
26d57ae6dd2f
Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents:
899
diff
changeset
|
205 |
26d57ae6dd2f
Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents:
899
diff
changeset
|
206 -- |
799
2059ac26fd49
Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents:
798
diff
changeset
|
207 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
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 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
210 CREATE TABLE messprogramm_mmt ( |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
211 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
212 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
|
213 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
|
214 messgroessen integer[], |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
215 letzte_aenderung timestamp without time zone DEFAULT now() |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
216 ); |
926
9121d99a471e
Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
914
diff
changeset
|
217 CREATE TRIGGER letzte_aenderung_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
|
218 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
219 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
220 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
221 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
222 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
223 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
224 CREATE TABLE probe ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
225 id serial PRIMARY KEY, |
1129
48c0132dbc85
Adapt definition to LAF and validate uniqueness of idAlt.
Tom Gottfried <tom@intevation.de>
parents:
1126
diff
changeset
|
226 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
|
227 DEFAULT 'sss' |
70b51893f15b
Fix UNIQUE-constraint and respective validation.
Tom Gottfried <tom@intevation.de>
parents:
1122
diff
changeset
|
228 || 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
|
229 || 'Y', |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
230 test boolean DEFAULT false NOT NULL, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
231 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 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
|
233 hauptproben_nr character varying(20), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
234 datenbasis_id smallint REFERENCES stammdaten.datenbasis, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
235 ba_id integer REFERENCES stammdaten.betriebsart, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
236 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
|
237 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
|
238 media character varying(100), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
239 umw_id character varying(3) REFERENCES stammdaten.umwelt, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
240 probeentnahme_beginn timestamp with time zone, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
241 probeentnahme_ende timestamp with time zone, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
242 mittelungsdauer bigint, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
243 letzte_aenderung timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
244 erzeuger_id integer REFERENCES stammdaten.datensatz_erzeuger, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
245 probe_nehmer_id integer REFERENCES stammdaten.probenehmer, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
246 mpl_id integer REFERENCES stammdaten.messprogramm_kategorie, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
247 mpr_id integer REFERENCES messprogramm, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
248 solldatum_beginn timestamp without time zone, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
249 solldatum_ende timestamp without time zone, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
250 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
|
251 UNIQUE (mst_id, hauptproben_nr), |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
252 CHECK(solldatum_beginn <= solldatum_ende) |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
253 ); |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
254 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
|
255 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
|
256 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
257 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
258 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
259 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
260 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
261 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
262 CREATE TABLE kommentar_p ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
263 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
264 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
|
265 datum timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
266 text character varying(1024), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
267 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
|
268 ); |
741
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 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
271 -- |
808
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
272 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: |
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 |
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
275 CREATE TABLE ortszuordnung ( |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
276 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
277 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
|
278 ort_id bigint NOT NULL REFERENCES stammdaten.ort, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
279 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
|
280 ortszusatztext character varying(100), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
281 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
|
282 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
|
283 EXCLUDE (probe_id WITH =) WHERE (ortszuordnung_typ = 'E') |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
284 ); |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
285 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
|
286 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
|
287 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
288 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
289 -- |
799
2059ac26fd49
Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents:
798
diff
changeset
|
290 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
291 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
292 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
293 CREATE TABLE zusatz_wert ( |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
294 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
295 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
|
296 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
|
297 messwert_pzs double precision, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
298 messfehler real, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
299 letzte_aenderung timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
300 nwg_zu_messwert double precision, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
301 tree_modified timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
302 UNIQUE (probe_id, pzs_id) |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
303 ); |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
304 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
|
305 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
|
306 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
307 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
308 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
309 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
310 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
311 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
312 CREATE TABLE messung ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
313 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
|
314 id_alt integer NOT NULL, |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
315 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
|
316 nebenproben_nr character varying(10), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
317 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
|
318 messdauer integer, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
319 messzeitpunkt timestamp with time zone, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
320 fertig boolean DEFAULT false NOT NULL, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
321 status integer, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
322 letzte_aenderung timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
323 geplant boolean DEFAULT false NOT NULL, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
324 tree_modified timestamp without time zone DEFAULT now() |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
325 ); |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
326 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
|
327 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
|
328 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
|
329 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
|
330 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
331 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
332 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
333 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
334 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
335 CREATE TABLE kommentar_m ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
336 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
337 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
|
338 datum timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
339 text character varying(1024), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
340 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
|
341 ); |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
342 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
343 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
344 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
345 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
346 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
347 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
348 CREATE TABLE messwert ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
349 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
350 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
|
351 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
352 messwert_nwg character varying(1), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
353 messwert double precision NOT NULL, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
354 messfehler real, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
355 nwg_zu_messwert double precision, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
356 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
|
357 grenzwertueberschreitung boolean DEFAULT false, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
358 letzte_aenderung timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
359 tree_modified timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
360 UNIQUE (messungs_id, messgroesse_id) |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
361 ); |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
362 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
|
363 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
|
364 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
365 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
366 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
367 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
368 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
369 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
370 CREATE TABLE status_protokoll ( |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
371 id serial PRIMARY KEY, |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
372 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
|
373 datum timestamp without time zone DEFAULT now(), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
374 text character varying(1024), |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
375 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
|
376 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
|
377 tree_modified timestamp without time zone DEFAULT now() |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
378 ); |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
379 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
|
380 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
381 ALTER TABLE ONLY messung |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
382 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
|
383 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
384 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
385 -- |
799
2059ac26fd49
Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents:
798
diff
changeset
|
386 -- 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
|
387 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
388 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
389 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
|
390 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
391 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
392 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
393 -- 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
|
394 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
395 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
396 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
|
397 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
398 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
399 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
400 -- 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
|
401 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
402 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
403 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
|
404 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
405 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
406 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
407 -- 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
|
408 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
409 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
410 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
|
411 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
412 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
413 -- |
799
2059ac26fd49
Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents:
798
diff
changeset
|
414 -- 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
|
415 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
416 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
417 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
|
418 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
419 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
420 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
421 -- 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
|
422 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
423 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
424 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
|
425 |
37a8f4d157c7
Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
741
diff
changeset
|
426 |
37a8f4d157c7
Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
741
diff
changeset
|
427 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
428 -- 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
|
429 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
430 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
431 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
|
432 |
37a8f4d157c7
Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
741
diff
changeset
|
433 |
37a8f4d157c7
Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
741
diff
changeset
|
434 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
435 -- 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
|
436 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
437 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
438 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
|
439 |
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
440 |
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
441 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
442 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: - |
808
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
443 -- |
2b0d99fbeecc
Updated database schema.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
799
diff
changeset
|
444 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
445 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
446 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
447 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
448 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
449 -- 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
|
450 -- |
37a8f4d157c7
Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
741
diff
changeset
|
451 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
452 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; |
741
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
453 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
454 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
455 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
456 -- 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
|
457 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
458 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
459 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
|
460 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
461 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
462 -- |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
463 -- 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
|
464 -- |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
465 |
1097
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
466 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
|
467 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
468 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
469 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
470 -- 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
|
471 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
472 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
473 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
|
474 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
475 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
476 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
477 -- 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
|
478 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
479 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
480 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
|
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 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
484 -- 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
|
485 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
486 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
487 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
|
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 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
491 -- 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
|
492 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
493 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
494 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
|
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 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
498 -- 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
|
499 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
500 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
501 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
|
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 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
505 -- 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
|
506 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
507 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
508 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
|
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 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
512 -- 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
|
513 -- |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
514 |
186d602e031a
Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents:
1082
diff
changeset
|
515 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
|
516 |
c998673c6d1e
Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
517 |
797
e15c981fe310
Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents:
792
diff
changeset
|
518 COMMIT; |