Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1004:c0ff3965c266 schema-update

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added index for "child"-tables and removed index for probe.
author Raimund Renkert <raimund.renkert@intevation.de>
date Fri, 01 Jul 2016 19:51:41 +0200
parents 552215760ba8
children e9116ec4eb40
rev   line source
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
1 \set ON_ERROR_STOP on
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
2
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
3 BEGIN;
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
4
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 SET statement_timeout = 0;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 SET lock_timeout = 0;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 SET client_encoding = 'UTF8';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 SET standard_conforming_strings = on;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 SET check_function_bodies = false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 SET client_min_messages = warning;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 -- Name: land; Type: SCHEMA; Schema: -; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 CREATE SCHEMA land;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
19 SET search_path = land, pg_catalog;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
22 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
23 LANGUAGE plpgsql
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
24 AS $$
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
25 BEGIN
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
26 NEW.letzte_aenderung = now();
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
27 RETURN NEW;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
28 END;
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
29 $$;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
31
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 --
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
33 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: -
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
36 CREATE FUNCTION update_tree_modified() RETURNS trigger
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
37 LANGUAGE plpgsql
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
38 AS $$
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
39 BEGIN
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
40 NEW.tree_modified = now();
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
41 RETURN NEW;
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
42 END;
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
43 $$;
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 -- Name: update_time_messung(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
50 CREATE FUNCTION update_tree_modified_messung() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 RAISE NOTICE 'messung is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id;
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
57 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
58 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 -- Name: update_time_probe(); Type: FUNCTION; Schema: land; Owner: -
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
67 CREATE FUNCTION update_tree_modified_probe() RETURNS trigger
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 LANGUAGE plpgsql
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 AS $$
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 BEGIN
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 RAISE NOTICE 'probe is %',NEW.id;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 NEW.tree_modified = now();
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 RAISE NOTICE 'updating other rows';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74 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
75 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
76 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
77 RETURN NEW;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 END;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 $$;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82 SET default_tablespace = '';
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84 SET default_with_oids = false;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
87 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
88 -- 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
89 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
90
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
91 CREATE SEQUENCE messung_messung_id_alt_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
92 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
93 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
94 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
95 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
96 CACHE 1;
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
97
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
98
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
99 --
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
100 -- 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
101 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
102
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
103 CREATE SEQUENCE probe_probe_id_seq
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
104 START WITH 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
105 INCREMENT BY 1
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
106 NO MINVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
107 NO MAXVALUE
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
108 CACHE 1;
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 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
112 -- 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
113 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
114
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
115 CREATE TABLE messprogramm (
994
5886384dcb92 Replaced explicit sequences by serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 993
diff changeset
116 id serial PRIMARY KEY,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
117 name character varying(256),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
118 test boolean DEFAULT false NOT NULL,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
119 netzbetreiber_id character varying(2) NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
120 REFERENCES stammdaten.netz_betreiber,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
121 mst_id character varying(5) NOT NULL REFERENCES stammdaten.mess_stelle,
901
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
122 labor_mst_id character varying(5) NOT NULL
56c9fb1d37c8 probe and messprogramm always need a mst_id and labor_mst_id.
Tom Gottfried <tom@intevation.de>
parents: 900
diff changeset
123 REFERENCES stammdaten.mess_stelle,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
124 datenbasis_id integer NOT NULL REFERENCES stammdaten.datenbasis,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
125 ba_id character varying(1),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
126 gem_id character varying(8),
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
127 ort_id integer NOT NULL REFERENCES stammdaten.ort,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
128 media_desk character varying(100),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
129 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
130 probenart_id integer NOT NULL REFERENCES stammdaten.probenart,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
131 probenintervall character varying(2) NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
132 teilintervall_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
133 teilintervall_bis integer NOT NULL,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
134 intervall_offset integer,
989
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
135 gueltig_von integer NOT NULL,
b943e956b767 Complement mandatory fields for messprogramm.
Tom Gottfried <tom@intevation.de>
parents: 927
diff changeset
136 gueltig_bis integer NOT NULL,
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
137 probe_nehmer_id integer,
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
138 probe_kommentar character varying(80),
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
139 letzte_aenderung timestamp without time zone DEFAULT now() NOT NULL
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
140 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
141 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
142
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
143
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
144 --
900
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
145 -- Name: COLUMN messprogramm.media_desk; Type: COMMENT; Schema: land; Owner: -
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
146 --
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
147
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
148 COMMENT ON COLUMN messprogramm.media_desk IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
149
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
150
26d57ae6dd2f Improve readability of DDL for messprogramm table.
Tom Gottfried <tom@intevation.de>
parents: 899
diff changeset
151 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
152 -- Name: messprogramm_mmt; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
153 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
154
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
155 CREATE TABLE messprogramm_mmt (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
156 id serial PRIMARY KEY,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
157 messprogramm_id integer NOT NULL REFERENCES messprogramm ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
158 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
159 messgroessen integer[],
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
160 letzte_aenderung timestamp without time zone DEFAULT now()
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
161 );
926
9121d99a471e Use databse triggers for update on field 'letzteAenderung'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 914
diff changeset
162 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
163
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
164
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
165 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
166 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace:
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
167 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
168
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
169 CREATE TABLE probe (
994
5886384dcb92 Replaced explicit sequences by serials.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 993
diff changeset
170 id serial PRIMARY KEY,
993
196800bb22b0 Replaced translation tables for probe and messung with id_alt columns.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 992
diff changeset
171 id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
172 test boolean DEFAULT false NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
173 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
174 mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
175 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
176 labor_mst_id character varying(5) NOT NULL
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
177 REFERENCES stammdaten.mess_stelle,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
178 hauptproben_nr character varying(20),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
179 datenbasis_id smallint REFERENCES stammdaten.datenbasis,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
180 ba_id character varying(1),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
181 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
182 media_desk character varying(100),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
183 media character varying(100),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
184 umw_id character varying(3) REFERENCES stammdaten.umwelt,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
185 probeentnahme_beginn timestamp with time zone,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
186 probeentnahme_ende timestamp with time zone,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
187 mittelungsdauer bigint,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
188 letzte_aenderung timestamp without time zone DEFAULT now(),
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
189 erzeuger_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
190 probe_nehmer_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
191 mp_kat character(1),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
192 mpl_id character varying(3),
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
193 mpr_id integer,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
194 solldatum_beginn timestamp without time zone,
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
195 solldatum_ende timestamp without time zone,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
196 tree_modified timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
197 UNIQUE (mst_id, hauptproben_nr)
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
198 );
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
199 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
200 CREATE TRIGGER tree_modified_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_probe();
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
201
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
202
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
203 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
204 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
205 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
206
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
207 CREATE TABLE kommentar_p (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
208 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
209 erzeuger character varying(5) NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
210 datum timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
211 text character varying(1024),
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
212 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
213 );
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
214
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
215
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
216 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
217 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
218 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
219
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
220 CREATE TABLE ortszuordnung (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
221 id serial PRIMARY KEY,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
222 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
223 ort_id bigint NOT NULL REFERENCES stammdaten.ort,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
224 ortszuordnung_typ character varying(1) REFERENCES stammdaten.ortszuordnung_typ,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
225 ortszusatztext character varying(100),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
226 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
227 tree_modified timestamp without time zone DEFAULT now()
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
228 );
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
229 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
230 CREATE TRIGGER tree_modified_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
231
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
232
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
233 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
234 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
235 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
236
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
237 CREATE TABLE zusatz_wert (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
238 id serial PRIMARY KEY,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
239 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
240 pzs_id character varying(3) NOT NULL REFERENCES stammdaten.proben_zusatz,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
241 messwert_pzs double precision,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
242 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
243 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
244 nwg_zu_messwert double precision,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
245 tree_modified timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
246 UNIQUE (probe_id, pzs_id)
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
247 );
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
248 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
249 CREATE TRIGGER tree_modified_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
250
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
251
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
252 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
253 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: -
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
254 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
255
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
256 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz';
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
257
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
258
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
259 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
260 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
261 --
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
262
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
263 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel';
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
264
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
265
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
266 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
267 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
268 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
269
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
270 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
271
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
272
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
273 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
274 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
275 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
276
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
277 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
278
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
279
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
280 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
281 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
282 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
283
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
284 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
285
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
286
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
287 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
288 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
289 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
290
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
291 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
292
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
293
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
294 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
295 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
296 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
297
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
298 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
299
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
300
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
301 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
302 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
303 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
304
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
305 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
306
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
307
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
308 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
309 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
310 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
311
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
312 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
313
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
314
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
315 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
316 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
317 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
318
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
319 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
320
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
321
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
322 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
323 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: -
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
324 --
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
325
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
326 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich';
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
327
899
4bbfc9cff5d7 Improve readability of DDL for probe tables.
Tom Gottfried <tom@intevation.de>
parents: 898
diff changeset
328
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
329 --
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
330 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
331 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
332
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
333 CREATE TABLE messung (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
334 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
335 id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
336 probe_id integer NOT NULL REFERENCES probe ON DELETE CASCADE,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
337 nebenproben_nr character varying(10),
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
338 mmt_id character varying(2) NOT NULL REFERENCES stammdaten.mess_methode ON DELETE CASCADE,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
339 messdauer integer,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
340 messzeitpunkt timestamp with time zone,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
341 fertig boolean DEFAULT false NOT NULL,
1003
552215760ba8 Moved messung.status FK constraint.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1002
diff changeset
342 status integer,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
343 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
344 geplant boolean DEFAULT false NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
345 tree_modified timestamp without time zone DEFAULT now()
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
346 );
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
347 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
348 CREATE TRIGGER tree_modified_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_tree_modified_messung();
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
349
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
350
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
351 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
352 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
353 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
354
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
355 CREATE TABLE kommentar_m (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
356 id serial PRIMARY KEY,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
357 erzeuger character varying(5) NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
358 datum timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
359 text character varying(1024),
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
360 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
361 );
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
362
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
363
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
364 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
365 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace:
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
366 --
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
367
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
368 CREATE TABLE messwert (
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
369 id serial PRIMARY KEY,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
370 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
371 messgroesse_id integer NOT NULL REFERENCES stammdaten.messgroesse,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
372 messwert_nwg character varying(1),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
373 messwert double precision NOT NULL,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
374 messfehler real,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
375 nwg_zu_messwert double precision,
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
376 meh_id smallint NOT NULL REFERENCES stammdaten.mess_einheit,
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
377 grenzwertueberschreitung boolean DEFAULT false,
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
378 letzte_aenderung timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
379 tree_modified timestamp without time zone DEFAULT now(),
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
380 UNIQUE (messungs_id, messgroesse_id)
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
381 );
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
382 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung();
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
383 CREATE TRIGGER tree_modified_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_tree_modified();
999
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
384
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
385
813461e9a7be Reordered table creation to allow inline definition of FKs.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 998
diff changeset
386 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
387 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace:
751
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
388 --
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
389
37a8f4d157c7 Updated lada schema: Added status workflow model and cleaned up some triggers.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 741
diff changeset
390 CREATE TABLE status_protokoll (
996
f77a9e85b122 Declare primary keys in tabel definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 995
diff changeset
391 id serial PRIMARY KEY,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
392 erzeuger character varying(5) NOT NULL,
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
393 datum timestamp without time zone DEFAULT now(),
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
394 text character varying(1024),
1000
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
395 messungs_id integer NOT NULL REFERENCES messung ON DELETE CASCADE,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
396 status_stufe integer NOT NULL REFERENCES stammdaten.status_stufe,
1489f0ade850 Moved FK constraints into table definition.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 999
diff changeset
397 status_wert integer NOT NULL REFERENCES stammdaten.status_wert,
992
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
398 tree_modified timestamp without time zone DEFAULT now()
9002ebe2a8af Removed schema 'bund'.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 989
diff changeset
399 );
1002
8ec5ee1c2dc6 Updated trigger and functions.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1001
diff changeset
400 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
401
1003
552215760ba8 Moved messung.status FK constraint.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1002
diff changeset
402 ALTER TABLE ONLY messung
552215760ba8 Moved messung.status FK constraint.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1002
diff changeset
403 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id);
552215760ba8 Moved messung.status FK constraint.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1002
diff changeset
404
741
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
405
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
406 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
407 -- 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
408 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
409
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
410 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
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 --
1004
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
414 -- Name: ort_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
415 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
416
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
417 CREATE INDEX ort_probe_id_idx ON ortszuordnung USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
418
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
419
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
420 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
421 -- Name: zusatz_wert_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
422 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
423
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
424 CREATE INDEX zusatz_wert_probe_id_idx ON zusatz_wert USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
425
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
426
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
427 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
428 -- Name: kommentar_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
429 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
430
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
431 CREATE INDEX kommentar_probe_id_idx ON kommentar_p USING btree (probe_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
432
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
433
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
434 --
799
2059ac26fd49 Whitespace-cleanup in schema definitions.
Tom Gottfried <tom@intevation.de>
parents: 798
diff changeset
435 -- 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
436 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
437
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
438 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
439
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
440
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
441 --
1004
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
442 -- 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
443 --
c998673c6d1e Add initial DB schema script.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
444
1004
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
445 CREATE INDEX status_messungs_id_idx ON status_protokoll USING btree (messungs_id);
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
446
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
447
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
448 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
449 -- Name: kommentar_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace:
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
450 --
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
451
c0ff3965c266 Added index for "child"-tables and removed index for probe.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1003
diff changeset
452 CREATE INDEX kommentar_messungs_id_idx ON kommentar_m USING btree (messungs_id);
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
797
e15c981fe310 Do not create database objects in case of erroneous definitions.
Tom Gottfried <tom@intevation.de>
parents: 792
diff changeset
455 COMMIT;
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)