Mercurial > lada > lada-server
comparison db_schema/lada_schema.sql @ 992:9002ebe2a8af schema-update
Removed schema 'bund'.
author | Raimund Renkert <raimund.renkert@intevation.de> |
---|---|
date | Fri, 01 Jul 2016 17:39:12 +0200 |
parents | b943e956b767 |
children | 196800bb22b0 |
comparison
equal
deleted
inserted
replaced
990:ff07474b65e0 | 992:9002ebe2a8af |
---|---|
9 SET standard_conforming_strings = on; | 9 SET standard_conforming_strings = on; |
10 SET check_function_bodies = false; | 10 SET check_function_bodies = false; |
11 SET client_min_messages = warning; | 11 SET client_min_messages = warning; |
12 | 12 |
13 -- | 13 -- |
14 -- Name: bund; Type: SCHEMA; Schema: -; Owner: - | |
15 -- | |
16 | |
17 CREATE SCHEMA bund; | |
18 | |
19 | |
20 -- | |
21 -- Name: land; Type: SCHEMA; Schema: -; Owner: - | 14 -- Name: land; Type: SCHEMA; Schema: -; Owner: - |
22 -- | 15 -- |
23 | 16 |
24 CREATE SCHEMA land; | 17 CREATE SCHEMA land; |
25 | 18 |
26 | 19 SET search_path = land, pg_catalog; |
27 SET search_path = bund, pg_catalog; | 20 |
28 | 21 -- |
29 -- | 22 -- Name: update_time_status(); Type: FUNCTION; Schema: land; Owner: - |
30 -- Name: update_time_status(); Type: FUNCTION; Schema: bund; Owner: - | |
31 -- | 23 -- |
32 | 24 |
33 CREATE FUNCTION update_time_status() RETURNS trigger | 25 CREATE FUNCTION update_time_status() RETURNS trigger |
34 LANGUAGE plpgsql | 26 LANGUAGE plpgsql |
35 AS $$ | 27 AS $$ |
46 NEW.letzte_aenderung = now(); | 38 NEW.letzte_aenderung = now(); |
47 RETURN NEW; | 39 RETURN NEW; |
48 END; | 40 END; |
49 $$; | 41 $$; |
50 | 42 |
51 SET search_path = land, pg_catalog; | |
52 | |
53 CREATE FUNCTION update_letzte_aenderung() RETURNS trigger | |
54 LANGUAGE plpgsql | |
55 AS $$ | |
56 BEGIN | |
57 NEW.letzte_aenderung = now(); | |
58 RETURN NEW; | |
59 END; | |
60 $$; | |
61 | 43 |
62 -- | 44 -- |
63 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - | 45 -- Name: is_probe_fertig(integer); Type: FUNCTION; Schema: land; Owner: - |
64 -- | 46 -- |
65 | 47 |
83 AS $$ | 65 AS $$ |
84 BEGIN | 66 BEGIN |
85 RAISE NOTICE 'messung is %',NEW.id; | 67 RAISE NOTICE 'messung is %',NEW.id; |
86 NEW.tree_modified = now(); | 68 NEW.tree_modified = now(); |
87 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id; | 69 UPDATE land.messwert SET tree_modified = now() WHERE messungs_id = NEW.id; |
88 UPDATE bund.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id; | 70 UPDATE land.status_protokoll SET tree_modified = now() WHERE messungs_id = NEW.id; |
89 RETURN NEW; | 71 RETURN NEW; |
90 END; | 72 END; |
91 $$; | 73 $$; |
92 | 74 |
93 | 75 |
150 RETURN NEW; | 132 RETURN NEW; |
151 END; | 133 END; |
152 $$; | 134 $$; |
153 | 135 |
154 | 136 |
155 SET search_path = bund, pg_catalog; | 137 -- |
156 | 138 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
157 -- | |
158 -- Name: kommentar_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
159 -- | 139 -- |
160 | 140 |
161 CREATE SEQUENCE kommentar_id_seq | 141 CREATE SEQUENCE kommentar_id_seq |
162 START WITH 1 | 142 START WITH 1 |
163 INCREMENT BY 1 | 143 INCREMENT BY 1 |
168 | 148 |
169 SET default_tablespace = ''; | 149 SET default_tablespace = ''; |
170 | 150 |
171 SET default_with_oids = false; | 151 SET default_with_oids = false; |
172 | 152 |
173 -- | 153 |
174 -- Name: kommentar; Type: TABLE; Schema: bund; Owner: -; Tablespace: | 154 -- |
175 -- | 155 -- Name: messung_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
176 | 156 -- |
177 CREATE TABLE kommentar ( | 157 |
158 CREATE SEQUENCE messung_id_seq | |
159 START WITH 1 | |
160 INCREMENT BY 1 | |
161 NO MINVALUE | |
162 NO MAXVALUE | |
163 CACHE 1; | |
164 | |
165 | |
166 -- | |
167 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: land; Owner: - | |
168 -- | |
169 | |
170 CREATE SEQUENCE messung_messung_id_alt_seq | |
171 START WITH 1 | |
172 INCREMENT BY 1 | |
173 NO MINVALUE | |
174 NO MAXVALUE | |
175 CACHE 1; | |
176 | |
177 | |
178 -- | |
179 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
180 -- | |
181 | |
182 CREATE SEQUENCE messung_messungs_id_seq | |
183 START WITH 1 | |
184 INCREMENT BY 1 | |
185 NO MINVALUE | |
186 NO MAXVALUE | |
187 CACHE 1; | |
188 | |
189 | |
190 -- | |
191 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
192 -- | |
193 | |
194 CREATE SEQUENCE messwert_id_seq | |
195 START WITH 1 | |
196 INCREMENT BY 1 | |
197 NO MINVALUE | |
198 NO MAXVALUE | |
199 CACHE 1; | |
200 | |
201 | |
202 -- | |
203 -- Name: ort_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
204 -- | |
205 | |
206 CREATE SEQUENCE ort_id_seq | |
207 START WITH 1 | |
208 INCREMENT BY 1 | |
209 NO MINVALUE | |
210 NO MAXVALUE | |
211 CACHE 1; | |
212 | |
213 | |
214 | |
215 -- | |
216 -- Name: probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
217 -- | |
218 | |
219 CREATE SEQUENCE probe_id_seq | |
220 START WITH 1 | |
221 INCREMENT BY 1 | |
222 NO MINVALUE | |
223 NO MAXVALUE | |
224 CACHE 1; | |
225 | |
226 | |
227 -- | |
228 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
229 -- | |
230 | |
231 CREATE SEQUENCE probe_probe_id_seq | |
232 START WITH 1 | |
233 INCREMENT BY 1 | |
234 NO MINVALUE | |
235 NO MAXVALUE | |
236 CACHE 1; | |
237 | |
238 | |
239 -- | |
240 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: land; Owner: - | |
241 -- | |
242 | |
243 CREATE SEQUENCE zusatz_wert_id_seq | |
244 START WITH 1 | |
245 INCREMENT BY 1 | |
246 NO MINVALUE | |
247 NO MAXVALUE | |
248 CACHE 1; | |
249 | |
250 -- | |
251 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: | |
252 -- | |
253 | |
254 CREATE TABLE kommentar_m ( | |
178 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, | 255 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, |
179 erzeuger character varying(5) NOT NULL, | 256 erzeuger character varying(5) NOT NULL, |
180 datum timestamp without time zone DEFAULT now(), | 257 datum timestamp without time zone DEFAULT now(), |
181 text character varying(1024) | 258 text character varying(1024), |
259 messungs_id integer NOT NULL | |
182 ); | 260 ); |
183 | 261 |
184 | 262 |
185 -- | 263 -- |
186 -- Name: kommentar_m; Type: TABLE; Schema: bund; Owner: -; Tablespace: | 264 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: |
187 -- | |
188 | |
189 CREATE TABLE kommentar_m ( | |
190 messungs_id integer NOT NULL | |
191 ) | |
192 INHERITS (kommentar); | |
193 | |
194 | |
195 -- | |
196 -- Name: kommentar_p; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
197 -- | 265 -- |
198 | 266 |
199 CREATE TABLE kommentar_p ( | 267 CREATE TABLE kommentar_p ( |
268 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, | |
269 erzeuger character varying(5) NOT NULL, | |
270 datum timestamp without time zone DEFAULT now(), | |
271 text character varying(1024), | |
200 probe_id integer NOT NULL | 272 probe_id integer NOT NULL |
201 ) | |
202 INHERITS (kommentar); | |
203 | |
204 | |
205 -- | |
206 -- Name: list; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
207 -- | |
208 | |
209 CREATE TABLE list ( | |
210 id integer NOT NULL, | |
211 typ character(1) NOT NULL, | |
212 bezeichnung character varying(20) NOT NULL, | |
213 beschreibuing character varying(512), | |
214 letzte_aenderung timestamp with time zone NOT NULL, | |
215 gueltig_bis timestamp with time zone | |
216 ); | 273 ); |
217 CREATE TRIGGER letzte_aenderung_list BEFORE UPDATE ON list FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
218 | |
219 | |
220 -- | |
221 -- Name: list_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
222 -- | |
223 | |
224 CREATE SEQUENCE list_id_seq | |
225 START WITH 1 | |
226 INCREMENT BY 1 | |
227 NO MINVALUE | |
228 NO MAXVALUE | |
229 CACHE 1; | |
230 | |
231 | |
232 -- | |
233 -- Name: list_id_seq; Type: SEQUENCE OWNED BY; Schema: bund; Owner: - | |
234 -- | |
235 | |
236 ALTER SEQUENCE list_id_seq OWNED BY list.id; | |
237 | |
238 | |
239 -- | |
240 -- Name: list_zuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
241 -- | |
242 | |
243 CREATE TABLE list_zuordnung ( | |
244 list_id integer NOT NULL, | |
245 probe_id integer | |
246 ); | |
247 | |
248 | |
249 -- | |
250 -- Name: messung_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
251 -- | |
252 | |
253 CREATE SEQUENCE messung_id_seq | |
254 START WITH 1 | |
255 INCREMENT BY 1 | |
256 NO MINVALUE | |
257 NO MAXVALUE | |
258 CACHE 1; | |
259 | |
260 | |
261 -- | |
262 -- Name: messung; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
263 -- | |
264 | |
265 CREATE TABLE messung ( | |
266 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, | |
267 probe_id integer NOT NULL, | |
268 nebenproben_nr character varying(10), | |
269 mmt_id character varying(2) NOT NULL, | |
270 messdauer integer, | |
271 messzeitpunkt timestamp with time zone, | |
272 fertig boolean DEFAULT false NOT NULL, | |
273 status integer, | |
274 letzte_aenderung timestamp without time zone DEFAULT now() | |
275 ); | |
276 CREATE TRIGGER letzte_aenderung_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
277 | |
278 | |
279 -- | |
280 -- Name: messung_messung_id_alt_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
281 -- | |
282 | |
283 CREATE SEQUENCE messung_messung_id_alt_seq | |
284 START WITH 1 | |
285 INCREMENT BY 1 | |
286 NO MINVALUE | |
287 NO MAXVALUE | |
288 CACHE 1; | |
289 | |
290 | |
291 -- | |
292 -- Name: messung_messungs_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
293 -- | |
294 | |
295 CREATE SEQUENCE messung_messungs_id_seq | |
296 START WITH 1 | |
297 INCREMENT BY 1 | |
298 NO MINVALUE | |
299 NO MAXVALUE | |
300 CACHE 1; | |
301 | |
302 | |
303 -- | |
304 -- Name: messwert_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
305 -- | |
306 | |
307 CREATE SEQUENCE messwert_id_seq | |
308 START WITH 1 | |
309 INCREMENT BY 1 | |
310 NO MINVALUE | |
311 NO MAXVALUE | |
312 CACHE 1; | |
313 | |
314 | |
315 -- | |
316 -- Name: messwert; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
317 -- | |
318 | |
319 CREATE TABLE messwert ( | |
320 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL, | |
321 messungs_id integer NOT NULL, | |
322 messgroesse_id integer NOT NULL, | |
323 messwert_nwg character varying(1), | |
324 messwert double precision NOT NULL, | |
325 messfehler real, | |
326 nwg_zu_messwert double precision, | |
327 meh_id smallint NOT NULL, | |
328 grenzwertueberschreitung boolean DEFAULT false, | |
329 letzte_aenderung timestamp without time zone DEFAULT now() | |
330 ); | |
331 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
332 | |
333 | |
334 -- | |
335 -- Name: ort_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
336 -- | |
337 | |
338 CREATE SEQUENCE ort_id_seq | |
339 START WITH 1 | |
340 INCREMENT BY 1 | |
341 NO MINVALUE | |
342 NO MAXVALUE | |
343 CACHE 1; | |
344 | |
345 | |
346 -- | |
347 -- Name: ortszuordnung; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
348 -- | |
349 | |
350 CREATE TABLE ortszuordnung ( | |
351 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL, | |
352 probe_id integer NOT NULL, | |
353 ort_id bigint NOT NULL, | |
354 ortszuordnung_typ character varying(1), | |
355 ortszusatztext character varying(100), | |
356 letzte_aenderung timestamp without time zone DEFAULT now() | |
357 ); | |
358 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
359 | |
360 | |
361 -- | |
362 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: bund; Owner: - | |
363 -- | |
364 | |
365 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz'; | |
366 | |
367 | |
368 -- | |
369 -- Name: probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
370 -- | |
371 | |
372 CREATE SEQUENCE probe_id_seq | |
373 START WITH 1 | |
374 INCREMENT BY 1 | |
375 NO MINVALUE | |
376 NO MAXVALUE | |
377 CACHE 1; | |
378 | |
379 | |
380 -- | |
381 -- Name: probe; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
382 -- | |
383 | |
384 CREATE TABLE probe ( | |
385 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), | |
386 test boolean DEFAULT false NOT NULL, | |
387 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, | |
388 mst_id character varying(5) NOT NULL | |
389 REFERENCES stammdaten.mess_stelle, | |
390 labor_mst_id character varying(5) NOT NULL | |
391 REFERENCES stammdaten.mess_stelle, | |
392 hauptproben_nr character varying(20), | |
393 datenbasis_id smallint REFERENCES stammdaten.datenbasis, | |
394 ba_id character varying(1), | |
395 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart, | |
396 media_desk character varying(100), | |
397 media character varying(100), | |
398 umw_id character varying(3) REFERENCES stammdaten.umwelt, | |
399 probeentnahme_beginn timestamp with time zone, | |
400 probeentnahme_ende timestamp with time zone, | |
401 mittelungsdauer bigint, | |
402 letzte_aenderung timestamp without time zone DEFAULT now(), | |
403 UNIQUE (mst_id, hauptproben_nr) | |
404 ); | |
405 | |
406 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
407 | |
408 -- | |
409 -- Name: COLUMN probe.id; Type: COMMENT; Schema: bund; Owner: - | |
410 -- | |
411 | |
412 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; | |
413 | |
414 | |
415 -- | |
416 -- Name: COLUMN probe.test; Type: COMMENT; Schema: bund; Owner: - | |
417 -- | |
418 | |
419 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; | |
420 | |
421 | |
422 -- | |
423 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: bund; Owner: - | |
424 -- | |
425 | |
426 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle'; | |
427 | |
428 | |
429 -- | |
430 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: bund; Owner: - | |
431 -- | |
432 | |
433 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor'; | |
434 | |
435 | |
436 -- | |
437 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: bund; Owner: - | |
438 -- | |
439 | |
440 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel'; | |
441 | |
442 | |
443 -- | |
444 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: bund; Owner: - | |
445 -- | |
446 | |
447 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)'; | |
448 | |
449 | |
450 -- | |
451 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: bund; Owner: - | |
452 -- | |
453 | |
454 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)'; | |
455 | |
456 | |
457 -- | |
458 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: bund; Owner: - | |
459 -- | |
460 | |
461 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)'; | |
462 | |
463 | |
464 -- | |
465 -- Name: COLUMN probe.media; Type: COMMENT; Schema: bund; Owner: - | |
466 -- | |
467 | |
468 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; | |
469 | |
470 | |
471 -- | |
472 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: bund; Owner: - | |
473 -- | |
474 | |
475 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; | |
476 | |
477 | |
478 -- | |
479 -- Name: probe_probe_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
480 -- | |
481 | |
482 CREATE SEQUENCE probe_probe_id_seq | |
483 START WITH 1 | |
484 INCREMENT BY 1 | |
485 NO MINVALUE | |
486 NO MAXVALUE | |
487 CACHE 1; | |
488 | |
489 | |
490 -- | |
491 -- Name: status_protokoll; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
492 -- | |
493 | |
494 CREATE TABLE status_protokoll ( | |
495 status_stufe integer NOT NULL, | |
496 status_wert integer NOT NULL, | |
497 tree_modified timestamp without time zone DEFAULT now() | |
498 ) | |
499 INHERITS (kommentar_m); | |
500 | |
501 | |
502 -- | |
503 -- Name: zusatz_wert_id_seq; Type: SEQUENCE; Schema: bund; Owner: - | |
504 -- | |
505 | |
506 CREATE SEQUENCE zusatz_wert_id_seq | |
507 START WITH 1 | |
508 INCREMENT BY 1 | |
509 NO MINVALUE | |
510 NO MAXVALUE | |
511 CACHE 1; | |
512 | |
513 | |
514 -- | |
515 -- Name: zusatz_wert; Type: TABLE; Schema: bund; Owner: -; Tablespace: | |
516 -- | |
517 | |
518 CREATE TABLE zusatz_wert ( | |
519 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL, | |
520 probe_id integer NOT NULL, | |
521 pzs_id character varying(3) NOT NULL, | |
522 messwert_pzs double precision, | |
523 messfehler real, | |
524 letzte_aenderung timestamp without time zone DEFAULT now(), | |
525 nwg_zu_messwert double precision | |
526 ); | |
527 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); | |
528 | |
529 | |
530 SET search_path = land, pg_catalog; | |
531 | |
532 -- | |
533 -- Name: kommentar_m; Type: TABLE; Schema: land; Owner: -; Tablespace: | |
534 -- | |
535 | |
536 CREATE TABLE kommentar_m ( | |
537 ) | |
538 INHERITS (bund.kommentar_m); | |
539 | |
540 | |
541 -- | |
542 -- Name: kommentar_p; Type: TABLE; Schema: land; Owner: -; Tablespace: | |
543 -- | |
544 | |
545 CREATE TABLE kommentar_p ( | |
546 ) | |
547 INHERITS (bund.kommentar_p); | |
548 | 274 |
549 | 275 |
550 -- | 276 -- |
551 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: - | 277 -- Name: messprogramm_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
552 -- | 278 -- |
641 -- | 367 -- |
642 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: | 368 -- Name: messung; Type: TABLE; Schema: land; Owner: -; Tablespace: |
643 -- | 369 -- |
644 | 370 |
645 CREATE TABLE messung ( | 371 CREATE TABLE messung ( |
372 id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, | |
373 probe_id integer NOT NULL, | |
374 nebenproben_nr character varying(10), | |
375 mmt_id character varying(2) NOT NULL, | |
376 messdauer integer, | |
377 messzeitpunkt timestamp with time zone, | |
378 fertig boolean DEFAULT false NOT NULL, | |
379 status integer, | |
380 letzte_aenderung timestamp without time zone DEFAULT now(), | |
646 geplant boolean DEFAULT false NOT NULL, | 381 geplant boolean DEFAULT false NOT NULL, |
647 tree_modified timestamp without time zone DEFAULT now() | 382 tree_modified timestamp without time zone DEFAULT now() |
648 ) | 383 ); |
649 INHERITS (bund.messung); | |
650 | 384 |
651 | 385 |
652 -- | 386 -- |
653 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: | 387 -- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: |
654 -- | 388 -- |
655 | 389 |
656 CREATE TABLE messung_translation ( | 390 CREATE TABLE messung_translation ( |
657 id integer NOT NULL, | 391 id integer NOT NULL, |
658 messungs_id integer NOT NULL, | 392 messungs_id integer NOT NULL, |
659 messungs_id_alt integer DEFAULT nextval('bund.messung_messung_id_alt_seq'::regclass) NOT NULL | 393 messungs_id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL |
660 ); | 394 ); |
661 | 395 |
662 | 396 |
663 -- | 397 -- |
664 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - | 398 -- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
682 -- | 416 -- |
683 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: | 417 -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: |
684 -- | 418 -- |
685 | 419 |
686 CREATE TABLE messwert ( | 420 CREATE TABLE messwert ( |
421 id integer DEFAULT nextval('messwert_id_seq'::regclass) NOT NULL, | |
422 messungs_id integer NOT NULL, | |
423 messgroesse_id integer NOT NULL, | |
424 messwert_nwg character varying(1), | |
425 messwert double precision NOT NULL, | |
426 messfehler real, | |
427 nwg_zu_messwert double precision, | |
428 meh_id smallint NOT NULL, | |
429 grenzwertueberschreitung boolean DEFAULT false, | |
430 letzte_aenderung timestamp without time zone DEFAULT now(), | |
687 tree_modified timestamp without time zone DEFAULT now() | 431 tree_modified timestamp without time zone DEFAULT now() |
688 ) | 432 ); |
689 INHERITS (bund.messwert); | 433 CREATE TRIGGER letzte_aenderung_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
690 | 434 |
691 | 435 |
692 -- | 436 -- |
693 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: | 437 -- Name: ortszuordnung; Type: TABLE; Schema: land; Owner: -; Tablespace: |
694 -- | 438 -- |
695 | 439 |
696 CREATE TABLE ortszuordnung ( | 440 CREATE TABLE ortszuordnung ( |
441 id integer DEFAULT nextval('ort_id_seq'::regclass) NOT NULL, | |
442 probe_id integer NOT NULL, | |
443 ort_id bigint NOT NULL, | |
444 ortszuordnung_typ character varying(1), | |
445 ortszusatztext character varying(100), | |
446 letzte_aenderung timestamp without time zone DEFAULT now(), | |
697 tree_modified timestamp without time zone DEFAULT now() | 447 tree_modified timestamp without time zone DEFAULT now() |
698 ) | 448 ); |
699 INHERITS (bund.ortszuordnung); | 449 CREATE TRIGGER letzte_aenderung_ortszuordnung BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
450 | |
451 -- | |
452 -- Name: COLUMN ortszuordnung.ortszuordnung_typ; Type: COMMENT; Schema: land; Owner: - | |
453 -- | |
454 | |
455 COMMENT ON COLUMN ortszuordnung.ortszuordnung_typ IS 'E = Entnahmeport, U = Ursprungsort, Z = Ortszusatz'; | |
700 | 456 |
701 | 457 |
702 -- | 458 -- |
703 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: | 459 -- Name: probe; Type: TABLE; Schema: land; Owner: -; Tablespace: |
704 -- | 460 -- |
705 | 461 |
706 CREATE TABLE probe ( | 462 CREATE TABLE probe ( |
463 id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), | |
464 test boolean DEFAULT false NOT NULL, | |
465 netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, | |
466 mst_id character varying(5) NOT NULL | |
467 REFERENCES stammdaten.mess_stelle, | |
468 labor_mst_id character varying(5) NOT NULL | |
469 REFERENCES stammdaten.mess_stelle, | |
470 hauptproben_nr character varying(20), | |
471 datenbasis_id smallint REFERENCES stammdaten.datenbasis, | |
472 ba_id character varying(1), | |
473 probenart_id smallint NOT NULL REFERENCES stammdaten.probenart, | |
474 media_desk character varying(100), | |
475 media character varying(100), | |
476 umw_id character varying(3) REFERENCES stammdaten.umwelt, | |
477 probeentnahme_beginn timestamp with time zone, | |
478 probeentnahme_ende timestamp with time zone, | |
479 mittelungsdauer bigint, | |
480 letzte_aenderung timestamp without time zone DEFAULT now(), | |
707 erzeuger_id integer, | 481 erzeuger_id integer, |
708 probe_nehmer_id integer, | 482 probe_nehmer_id integer, |
709 mp_kat character(1), | 483 mp_kat character(1), |
710 mpl_id character varying(3), | 484 mpl_id character varying(3), |
711 mpr_id integer, | 485 mpr_id integer, |
712 solldatum_beginn timestamp without time zone, | 486 solldatum_beginn timestamp without time zone, |
713 solldatum_ende timestamp without time zone, | 487 solldatum_ende timestamp without time zone, |
714 tree_modified timestamp without time zone DEFAULT now() | 488 tree_modified timestamp without time zone DEFAULT now(), |
715 ) | 489 UNIQUE (mst_id, hauptproben_nr) |
716 INHERITS (bund.probe); | 490 ); |
717 | 491 CREATE TRIGGER letzte_aenderung_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
718 ALTER TABLE ONLY probe | 492 |
719 ADD CONSTRAINT probe_pkey PRIMARY KEY (id); | 493 -- |
720 | 494 -- Name: COLUMN probe.id; Type: COMMENT; Schema: land; Owner: - |
721 ALTER TABLE ONLY probe | 495 -- |
722 ADD CONSTRAINT probe_datenbasis_id_fkey | 496 |
723 FOREIGN KEY (datenbasis_id) REFERENCES stammdaten.datenbasis(id); | 497 COMMENT ON COLUMN probe.id IS 'interner Probenschlüssel'; |
724 | 498 |
725 ALTER TABLE ONLY probe | 499 |
726 ADD CONSTRAINT probe_mst_id_fkey | 500 -- |
727 FOREIGN KEY (mst_id) REFERENCES stammdaten.mess_stelle(id); | 501 -- Name: COLUMN probe.test; Type: COMMENT; Schema: land; Owner: - |
728 | 502 -- |
729 ALTER TABLE ONLY probe | 503 |
730 ADD CONSTRAINT probe_labor_mst_id_fkey | 504 COMMENT ON COLUMN probe.test IS 'Ist Testdatensatz?'; |
731 FOREIGN KEY (labor_mst_id) REFERENCES stammdaten.mess_stelle(id); | 505 |
732 | 506 |
733 ALTER TABLE ONLY probe | 507 -- |
734 ADD CONSTRAINT probe_netzbetreiber_id_fkey | 508 -- Name: COLUMN probe.mst_id; Type: COMMENT; Schema: land; Owner: - |
735 FOREIGN KEY (netzbetreiber_id) REFERENCES stammdaten.netz_betreiber(id); | 509 -- |
736 | 510 |
737 ALTER TABLE ONLY probe | 511 COMMENT ON COLUMN probe.mst_id IS 'ID für Messstelle'; |
738 ADD CONSTRAINT probe_probenart_id_fkey | 512 |
739 FOREIGN KEY (probenart_id) REFERENCES stammdaten.probenart(id); | 513 |
740 | 514 -- |
741 ALTER TABLE ONLY probe | 515 -- Name: COLUMN probe.labor_mst_id; Type: COMMENT; Schema: land; Owner: - |
742 ADD CONSTRAINT probe_umw_id_fkey | 516 -- |
743 FOREIGN KEY (umw_id) REFERENCES stammdaten.umwelt(id); | 517 |
518 COMMENT ON COLUMN probe.labor_mst_id IS '-- ID für Messlabor'; | |
519 | |
520 | |
521 -- | |
522 -- Name: COLUMN probe.hauptproben_nr; Type: COMMENT; Schema: land; Owner: - | |
523 -- | |
524 | |
525 COMMENT ON COLUMN probe.hauptproben_nr IS 'externer Probensclüssel'; | |
526 | |
527 | |
528 -- | |
529 -- Name: COLUMN probe.ba_id; Type: COMMENT; Schema: land; Owner: - | |
530 -- | |
531 | |
532 COMMENT ON COLUMN probe.ba_id IS 'ID der Betriebsart (normal/Routine oder Störfall/intensiv)'; | |
533 | |
534 | |
535 -- | |
536 -- Name: COLUMN probe.probenart_id; Type: COMMENT; Schema: land; Owner: - | |
537 -- | |
538 | |
539 COMMENT ON COLUMN probe.probenart_id IS 'ID der Probenart(Einzel-, Sammel-, Misch- ...Probe)'; | |
540 | |
541 | |
542 -- | |
543 -- Name: COLUMN probe.media_desk; Type: COMMENT; Schema: land; Owner: - | |
544 -- | |
545 | |
546 COMMENT ON COLUMN probe.media_desk IS 'Mediencodierung (Deskriptoren oder ADV-Codierung)'; | |
547 | |
548 | |
549 -- | |
550 -- Name: COLUMN probe.media; Type: COMMENT; Schema: land; Owner: - | |
551 -- | |
552 | |
553 COMMENT ON COLUMN probe.media IS 'dekodierte Medienbezeichnung (aus media_desk abgeleitet)'; | |
554 | |
555 | |
556 -- | |
557 -- Name: COLUMN probe.umw_id; Type: COMMENT; Schema: land; Owner: - | |
558 -- | |
559 | |
560 COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; | |
561 | |
744 | 562 |
745 | 563 |
746 -- | 564 -- |
747 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: | 565 -- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: |
748 -- | 566 -- |
749 | 567 |
750 CREATE TABLE probe_translation ( | 568 CREATE TABLE probe_translation ( |
751 id integer NOT NULL, | 569 id integer NOT NULL, |
752 probe_id integer NOT NULL, | 570 probe_id integer NOT NULL, |
753 probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('bund.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL | 571 probe_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 |
754 ); | 572 ); |
755 | 573 |
756 | 574 |
757 -- | 575 -- |
758 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - | 576 -- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - |
776 -- | 594 -- |
777 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: | 595 -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: |
778 -- | 596 -- |
779 | 597 |
780 CREATE TABLE status_protokoll ( | 598 CREATE TABLE status_protokoll ( |
781 ) | 599 id integer DEFAULT nextval('kommentar_id_seq'::regclass) NOT NULL, |
782 INHERITS (bund.status_protokoll); | 600 erzeuger character varying(5) NOT NULL, |
601 datum timestamp without time zone DEFAULT now(), | |
602 text character varying(1024), | |
603 messungs_id integer NOT NULL, | |
604 status_stufe integer NOT NULL, | |
605 status_wert integer NOT NULL, | |
606 tree_modified timestamp without time zone DEFAULT now() | |
607 ); | |
783 | 608 |
784 | 609 |
785 -- | 610 -- |
786 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: | 611 -- Name: zusatz_wert; Type: TABLE; Schema: land; Owner: -; Tablespace: |
787 -- | 612 -- |
788 | 613 |
789 CREATE TABLE zusatz_wert ( | 614 CREATE TABLE zusatz_wert ( |
615 id integer DEFAULT nextval('zusatz_wert_id_seq'::regclass) NOT NULL, | |
616 probe_id integer NOT NULL, | |
617 pzs_id character varying(3) NOT NULL, | |
618 messwert_pzs double precision, | |
619 messfehler real, | |
620 letzte_aenderung timestamp without time zone DEFAULT now(), | |
621 nwg_zu_messwert double precision, | |
790 tree_modified timestamp without time zone DEFAULT now() | 622 tree_modified timestamp without time zone DEFAULT now() |
791 ) | 623 ); |
792 INHERITS (bund.zusatz_wert); | 624 CREATE TRIGGER letzte_aenderung_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_letzte_aenderung(); |
793 | 625 |
794 | 626 |
795 SET search_path = bund, pg_catalog; | 627 -- |
796 | 628 -- Name: id; Type: DEFAULT; Schema: land; Owner: - |
797 -- | 629 -- |
798 -- Name: id; Type: DEFAULT; Schema: bund; Owner: - | 630 |
799 -- | 631 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); |
800 | 632 |
801 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); | 633 |
802 | 634 -- |
803 | 635 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - |
804 -- | 636 -- |
805 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: - | 637 |
638 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); | |
639 | |
640 | |
641 SET search_path = land, pg_catalog; | |
642 | |
643 -- | |
644 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
645 -- | |
646 | |
647 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); | |
648 | |
649 | |
650 -- | |
651 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - | |
806 -- | 652 -- |
807 | 653 |
808 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now(); | 654 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now(); |
809 | 655 |
810 | 656 |
811 -- | 657 -- |
812 -- Name: id; Type: DEFAULT; Schema: bund; Owner: - | 658 -- Name: id; Type: DEFAULT; Schema: land; Owner: - |
813 -- | 659 -- |
814 | 660 |
815 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); | 661 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); |
816 | 662 |
817 | 663 |
818 -- | 664 -- |
819 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: - | 665 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - |
820 -- | 666 -- |
821 | 667 |
822 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now(); | 668 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now(); |
823 | 669 |
824 | 670 |
825 -- | 671 -- |
826 -- Name: id; Type: DEFAULT; Schema: bund; Owner: - | 672 -- Name: id; Type: DEFAULT; Schema: land; Owner: - |
827 -- | 673 -- |
828 | 674 |
829 ALTER TABLE ONLY list ALTER COLUMN id SET DEFAULT nextval('list_id_seq'::regclass); | 675 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass); |
830 | 676 |
831 | 677 |
832 -- | 678 -- |
833 -- Name: id; Type: DEFAULT; Schema: bund; Owner: - | 679 -- Name: id; Type: DEFAULT; Schema: land; Owner: - |
834 -- | 680 -- |
835 | 681 |
836 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('kommentar_id_seq'::regclass); | 682 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('land.messung_id_seq'::regclass); |
837 | 683 |
838 | 684 |
839 -- | 685 -- |
840 -- Name: datum; Type: DEFAULT; Schema: bund; Owner: - | 686 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: - |
687 -- | |
688 | |
689 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false; | |
690 | |
691 | |
692 -- | |
693 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
694 -- | |
695 | |
696 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
697 | |
698 | |
699 -- | |
700 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
701 -- | |
702 | |
703 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass); | |
704 | |
705 | |
706 -- | |
707 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
708 -- | |
709 | |
710 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('land.messwert_id_seq'::regclass); | |
711 | |
712 | |
713 -- | |
714 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: - | |
715 -- | |
716 | |
717 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false; | |
718 | |
719 | |
720 -- | |
721 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
722 -- | |
723 | |
724 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
725 | |
726 | |
727 -- | |
728 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
729 -- | |
730 | |
731 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('land.ort_id_seq'::regclass); | |
732 | |
733 | |
734 -- | |
735 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
736 -- | |
737 | |
738 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
739 | |
740 | |
741 -- | |
742 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
743 -- | |
744 | |
745 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass); | |
746 | |
747 | |
748 -- | |
749 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
750 -- | |
751 | |
752 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); | |
753 | |
754 | |
755 -- | |
756 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - | |
841 -- | 757 -- |
842 | 758 |
843 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); | 759 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); |
844 | 760 |
845 | 761 |
846 SET search_path = land, pg_catalog; | 762 -- |
763 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: - | |
764 -- | |
765 | |
766 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now(); | |
767 | |
847 | 768 |
848 -- | 769 -- |
849 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | 770 -- Name: id; Type: DEFAULT; Schema: land; Owner: - |
850 -- | 771 -- |
851 | 772 |
852 ALTER TABLE ONLY kommentar_m ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); | 773 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('land.zusatz_wert_id_seq'::regclass); |
853 | |
854 | |
855 -- | |
856 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - | |
857 -- | |
858 | |
859 ALTER TABLE ONLY kommentar_m ALTER COLUMN datum SET DEFAULT now(); | |
860 | |
861 | |
862 -- | |
863 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
864 -- | |
865 | |
866 ALTER TABLE ONLY kommentar_p ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); | |
867 | |
868 | |
869 -- | |
870 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - | |
871 -- | |
872 | |
873 ALTER TABLE ONLY kommentar_p ALTER COLUMN datum SET DEFAULT now(); | |
874 | |
875 | |
876 -- | |
877 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
878 -- | |
879 | |
880 ALTER TABLE ONLY messprogramm_mmt ALTER COLUMN id SET DEFAULT nextval('messprogramm_mmt_id_seq'::regclass); | |
881 | |
882 | |
883 -- | |
884 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
885 -- | |
886 | |
887 ALTER TABLE ONLY messung ALTER COLUMN id SET DEFAULT nextval('bund.messung_id_seq'::regclass); | |
888 | |
889 | |
890 -- | |
891 -- Name: fertig; Type: DEFAULT; Schema: land; Owner: - | |
892 -- | |
893 | |
894 ALTER TABLE ONLY messung ALTER COLUMN fertig SET DEFAULT false; | |
895 | 774 |
896 | 775 |
897 -- | 776 -- |
898 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | 777 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - |
899 -- | 778 -- |
900 | 779 |
901 ALTER TABLE ONLY messung ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
902 | |
903 | |
904 -- | |
905 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
906 -- | |
907 | |
908 ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass); | |
909 | |
910 | |
911 -- | |
912 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
913 -- | |
914 | |
915 ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('bund.messwert_id_seq'::regclass); | |
916 | |
917 | |
918 -- | |
919 -- Name: grenzwertueberschreitung; Type: DEFAULT; Schema: land; Owner: - | |
920 -- | |
921 | |
922 ALTER TABLE ONLY messwert ALTER COLUMN grenzwertueberschreitung SET DEFAULT false; | |
923 | |
924 | |
925 -- | |
926 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
927 -- | |
928 | |
929 ALTER TABLE ONLY messwert ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
930 | |
931 | |
932 -- | |
933 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
934 -- | |
935 | |
936 ALTER TABLE ONLY ortszuordnung ALTER COLUMN id SET DEFAULT nextval('bund.ort_id_seq'::regclass); | |
937 | |
938 | |
939 -- | |
940 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
941 -- | |
942 | |
943 ALTER TABLE ONLY ortszuordnung ALTER COLUMN letzte_aenderung SET DEFAULT now(); | |
944 | |
945 | |
946 -- | |
947 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
948 -- | |
949 | |
950 ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass); | |
951 | |
952 | |
953 -- | |
954 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
955 -- | |
956 | |
957 ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('bund.kommentar_id_seq'::regclass); | |
958 | |
959 | |
960 -- | |
961 -- Name: datum; Type: DEFAULT; Schema: land; Owner: - | |
962 -- | |
963 | |
964 ALTER TABLE ONLY status_protokoll ALTER COLUMN datum SET DEFAULT now(); | |
965 | |
966 | |
967 -- | |
968 -- Name: tree_modified; Type: DEFAULT; Schema: land; Owner: - | |
969 -- | |
970 | |
971 ALTER TABLE ONLY status_protokoll ALTER COLUMN tree_modified SET DEFAULT now(); | |
972 | |
973 | |
974 -- | |
975 -- Name: id; Type: DEFAULT; Schema: land; Owner: - | |
976 -- | |
977 | |
978 ALTER TABLE ONLY zusatz_wert ALTER COLUMN id SET DEFAULT nextval('bund.zusatz_wert_id_seq'::regclass); | |
979 | |
980 | |
981 -- | |
982 -- Name: letzte_aenderung; Type: DEFAULT; Schema: land; Owner: - | |
983 -- | |
984 | |
985 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now(); | 780 ALTER TABLE ONLY zusatz_wert ALTER COLUMN letzte_aenderung SET DEFAULT now(); |
986 | 781 |
987 | 782 |
988 SET search_path = bund, pg_catalog; | 783 -- |
989 | 784 -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
990 -- | 785 -- |
991 -- Name: kommentar_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 786 |
992 -- | 787 ALTER TABLE ONLY kommentar_m |
993 | 788 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id); |
994 ALTER TABLE ONLY kommentar | 789 |
995 ADD CONSTRAINT kommentar_pkey PRIMARY KEY (id); | 790 |
996 | 791 -- |
997 | 792 -- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
998 -- | 793 -- |
999 -- Name: list_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 794 |
1000 -- | 795 ALTER TABLE ONLY kommentar_p |
1001 | 796 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id); |
1002 ALTER TABLE ONLY list | 797 |
1003 ADD CONSTRAINT list_pkey PRIMARY KEY (id); | 798 |
1004 | 799 -- |
1005 | 800 -- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
1006 -- | 801 -- |
1007 -- Name: messung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 802 |
803 ALTER TABLE ONLY messprogramm_mmt | |
804 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id); | |
805 | |
806 | |
807 -- | |
808 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1008 -- | 809 -- |
1009 | 810 |
1010 ALTER TABLE ONLY messung | 811 ALTER TABLE ONLY messung |
1011 ADD CONSTRAINT messung_pkey PRIMARY KEY (id); | 812 ADD CONSTRAINT messung_pkey PRIMARY KEY (id); |
1012 | 813 |
1013 | 814 |
1014 -- | 815 -- |
1015 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 816 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
817 -- | |
818 | |
819 ALTER TABLE ONLY messung_translation | |
820 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id); | |
821 | |
822 | |
823 -- | |
824 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
825 -- | |
826 | |
827 ALTER TABLE ONLY messung_translation | |
828 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id); | |
829 | |
830 | |
831 -- | |
832 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1016 -- | 833 -- |
1017 | 834 |
1018 ALTER TABLE ONLY messwert | 835 ALTER TABLE ONLY messwert |
1019 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id); | 836 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id); |
1020 | 837 |
1021 | 838 |
1022 -- | 839 -- |
1023 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 840 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
1024 -- | 841 -- |
1025 | 842 |
1026 ALTER TABLE ONLY messwert | 843 ALTER TABLE ONLY messwert |
1027 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id); | 844 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id); |
1028 | 845 |
1029 | 846 |
1030 -- | 847 -- |
1031 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 848 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
1032 -- | 849 -- |
1033 | 850 |
1034 ALTER TABLE ONLY ortszuordnung | 851 ALTER TABLE ONLY ortszuordnung |
1035 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); | 852 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); |
1036 | 853 |
1037 | 854 |
1038 -- | 855 -- |
1039 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 856 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
857 -- | |
858 | |
859 ALTER TABLE ONLY probe_translation | |
860 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id); | |
861 | |
862 | |
863 -- | |
864 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
865 -- | |
866 | |
867 ALTER TABLE ONLY probe_translation | |
868 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id); | |
869 | |
870 | |
871 -- | |
872 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1040 -- | 873 -- |
1041 | 874 |
1042 ALTER TABLE ONLY status_protokoll | 875 ALTER TABLE ONLY status_protokoll |
1043 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); | 876 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); |
1044 | 877 |
1045 | 878 |
1046 -- | 879 -- |
1047 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 880 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
1048 -- | 881 -- |
1049 | 882 |
1050 ALTER TABLE ONLY zusatz_wert | 883 ALTER TABLE ONLY zusatz_wert |
1051 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id); | 884 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id); |
1052 | 885 |
1053 | 886 |
1054 -- | 887 -- |
1055 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: bund; Owner: -; Tablespace: | 888 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: |
1056 -- | 889 -- |
1057 | 890 |
1058 ALTER TABLE ONLY zusatz_wert | 891 ALTER TABLE ONLY zusatz_wert |
1059 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); | 892 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); |
1060 | 893 |
1061 | 894 |
1062 SET search_path = land, pg_catalog; | 895 SET search_path = land, pg_catalog; |
1063 | 896 |
1064 -- | 897 -- |
1065 -- Name: kommentar_m_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | 898 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: |
899 -- | |
900 | |
901 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id); | |
902 | |
903 | |
904 -- | |
905 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
906 -- | |
907 | |
908 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id); | |
909 | |
910 | |
911 -- | |
912 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
913 -- | |
914 | |
915 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id); | |
916 | |
917 | |
918 -- | |
919 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
920 -- | |
921 | |
922 CREATE INDEX probe_id_idx ON probe USING btree (id); | |
923 | |
924 | |
925 -- | |
926 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
927 -- | |
928 | |
929 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); | |
930 | |
931 | |
932 -- | |
933 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: - | |
934 -- | |
935 | |
936 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status(); | |
937 | |
938 | |
939 SET search_path = land, pg_catalog; | |
940 | |
941 -- | |
942 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: - | |
943 -- | |
944 | |
945 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung(); | |
946 | |
947 | |
948 -- | |
949 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: - | |
950 -- | |
951 | |
952 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert(); | |
953 | |
954 | |
955 -- | |
956 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: - | |
957 -- | |
958 | |
959 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort(); | |
960 | |
961 | |
962 -- | |
963 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: - | |
964 -- | |
965 | |
966 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe(); | |
967 | |
968 | |
969 -- | |
970 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: - | |
971 -- | |
972 | |
973 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); | |
974 | |
975 | |
976 -- | |
977 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
978 -- | |
979 | |
980 ALTER TABLE ONLY status_protokoll | |
981 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id); | |
982 | |
983 | |
984 -- | |
985 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
986 -- | |
987 | |
988 ALTER TABLE ONLY status_protokoll | |
989 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id); | |
990 | |
991 | |
992 -- | |
993 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1066 -- | 994 -- |
1067 | 995 |
1068 ALTER TABLE ONLY kommentar_m | 996 ALTER TABLE ONLY kommentar_m |
1069 ADD CONSTRAINT kommentar_m_pkey PRIMARY KEY (id); | 997 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; |
1070 | 998 |
1071 | 999 |
1072 -- | 1000 -- |
1073 -- Name: kommentar_p_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | 1001 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1074 -- | 1002 -- |
1075 | 1003 |
1076 ALTER TABLE ONLY kommentar_p | 1004 ALTER TABLE ONLY kommentar_p |
1077 ADD CONSTRAINT kommentar_p_pkey PRIMARY KEY (id); | 1005 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; |
1078 | 1006 |
1079 | 1007 |
1080 -- | 1008 -- |
1081 -- Name: messprogramm_mmt_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | 1009 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1082 -- | 1010 -- |
1083 | 1011 |
1084 ALTER TABLE ONLY messprogramm_mmt | 1012 ALTER TABLE ONLY messprogramm_mmt |
1085 ADD CONSTRAINT messprogramm_mmt_pkey PRIMARY KEY (id); | 1013 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE; |
1086 | 1014 |
1087 | 1015 |
1088 -- | 1016 -- |
1089 -- Name: messung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | 1017 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1018 -- | |
1019 | |
1020 ALTER TABLE ONLY messprogramm_mmt | |
1021 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id); | |
1022 | |
1023 | |
1024 -- | |
1025 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1090 -- | 1026 -- |
1091 | 1027 |
1092 ALTER TABLE ONLY messung | 1028 ALTER TABLE ONLY messung |
1093 ADD CONSTRAINT messung_pkey PRIMARY KEY (id); | 1029 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE; |
1094 | 1030 |
1095 | 1031 |
1096 -- | 1032 -- |
1097 -- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | 1033 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1098 -- | |
1099 | |
1100 ALTER TABLE ONLY messung_translation | |
1101 ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id); | |
1102 | |
1103 | |
1104 -- | |
1105 -- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1106 -- | |
1107 | |
1108 ALTER TABLE ONLY messung_translation | |
1109 ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id); | |
1110 | |
1111 | |
1112 -- | |
1113 -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1114 -- | |
1115 | |
1116 ALTER TABLE ONLY messwert | |
1117 ADD CONSTRAINT messwert_messungs_id_messgroesse_id_key UNIQUE (messungs_id, messgroesse_id); | |
1118 | |
1119 | |
1120 -- | |
1121 -- Name: messwert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1122 -- | |
1123 | |
1124 ALTER TABLE ONLY messwert | |
1125 ADD CONSTRAINT messwert_pkey PRIMARY KEY (id); | |
1126 | |
1127 | |
1128 -- | |
1129 -- Name: ortszuordnung_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1130 -- | |
1131 | |
1132 ALTER TABLE ONLY ortszuordnung | |
1133 ADD CONSTRAINT ortszuordnung_pkey PRIMARY KEY (id); | |
1134 | |
1135 | |
1136 -- | |
1137 -- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1138 -- | |
1139 | |
1140 ALTER TABLE ONLY probe_translation | |
1141 ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id); | |
1142 | |
1143 | |
1144 -- | |
1145 -- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1146 -- | |
1147 | |
1148 ALTER TABLE ONLY probe_translation | |
1149 ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id); | |
1150 | |
1151 | |
1152 -- | |
1153 -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1154 -- | |
1155 | |
1156 ALTER TABLE ONLY status_protokoll | |
1157 ADD CONSTRAINT status_protokoll_pkey PRIMARY KEY (id); | |
1158 | |
1159 | |
1160 -- | |
1161 -- Name: zusatz_wert_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1162 -- | |
1163 | |
1164 ALTER TABLE ONLY zusatz_wert | |
1165 ADD CONSTRAINT zusatz_wert_pkey PRIMARY KEY (id); | |
1166 | |
1167 | |
1168 -- | |
1169 -- Name: zusatz_wert_probe_id_pzs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: | |
1170 -- | |
1171 | |
1172 ALTER TABLE ONLY zusatz_wert | |
1173 ADD CONSTRAINT zusatz_wert_probe_id_pzs_id_key UNIQUE (probe_id, pzs_id); | |
1174 | |
1175 | |
1176 SET search_path = bund, pg_catalog; | |
1177 | |
1178 -- | |
1179 -- Name: p_list_zuord1; Type: INDEX; Schema: bund; Owner: -; Tablespace: | |
1180 -- | |
1181 | |
1182 CREATE INDEX p_list_zuord1 ON list_zuordnung USING btree (list_id); | |
1183 | |
1184 | |
1185 -- | |
1186 -- Name: p_list_zuord2; Type: INDEX; Schema: bund; Owner: -; Tablespace: | |
1187 -- | |
1188 | |
1189 CREATE INDEX p_list_zuord2 ON list_zuordnung USING hash (list_id); | |
1190 | |
1191 | |
1192 SET search_path = land, pg_catalog; | |
1193 | |
1194 -- | |
1195 -- Name: messung_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
1196 -- | |
1197 | |
1198 CREATE INDEX messung_probe_id_idx ON messung USING btree (probe_id); | |
1199 | |
1200 | |
1201 -- | |
1202 -- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
1203 -- | |
1204 | |
1205 CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id); | |
1206 | |
1207 | |
1208 -- | |
1209 -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
1210 -- | |
1211 | |
1212 CREATE INDEX messwert_messungs_id_idx ON messwert USING btree (messungs_id); | |
1213 | |
1214 | |
1215 -- | |
1216 -- Name: probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
1217 -- | |
1218 | |
1219 CREATE INDEX probe_id_idx ON probe USING btree (id); | |
1220 | |
1221 | |
1222 -- | |
1223 -- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: | |
1224 -- | |
1225 | |
1226 CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); | |
1227 | |
1228 | |
1229 SET search_path = bund, pg_catalog; | |
1230 | |
1231 -- | |
1232 -- Name: tree_timestamp_status; Type: TRIGGER; Schema: bund; Owner: - | |
1233 -- | |
1234 | |
1235 CREATE TRIGGER tree_timestamp_status BEFORE UPDATE ON status_protokoll FOR EACH ROW EXECUTE PROCEDURE update_time_status(); | |
1236 | |
1237 | |
1238 SET search_path = land, pg_catalog; | |
1239 | |
1240 -- | |
1241 -- Name: tree_timestamp_messung; Type: TRIGGER; Schema: land; Owner: - | |
1242 -- | |
1243 | |
1244 CREATE TRIGGER tree_timestamp_messung BEFORE UPDATE ON messung FOR EACH ROW EXECUTE PROCEDURE update_time_messung(); | |
1245 | |
1246 | |
1247 -- | |
1248 -- Name: tree_timestamp_messwert; Type: TRIGGER; Schema: land; Owner: - | |
1249 -- | |
1250 | |
1251 CREATE TRIGGER tree_timestamp_messwert BEFORE UPDATE ON messwert FOR EACH ROW EXECUTE PROCEDURE update_time_messwert(); | |
1252 | |
1253 | |
1254 -- | |
1255 -- Name: tree_timestamp_ort; Type: TRIGGER; Schema: land; Owner: - | |
1256 -- | |
1257 | |
1258 CREATE TRIGGER tree_timestamp_ort BEFORE UPDATE ON ortszuordnung FOR EACH ROW EXECUTE PROCEDURE update_time_ort(); | |
1259 | |
1260 | |
1261 -- | |
1262 -- Name: tree_timestamp_probe; Type: TRIGGER; Schema: land; Owner: - | |
1263 -- | |
1264 | |
1265 CREATE TRIGGER tree_timestamp_probe BEFORE UPDATE ON probe FOR EACH ROW EXECUTE PROCEDURE update_time_probe(); | |
1266 | |
1267 | |
1268 -- | |
1269 -- Name: tree_timestamp_zusatzwert; Type: TRIGGER; Schema: land; Owner: - | |
1270 -- | |
1271 | |
1272 CREATE TRIGGER tree_timestamp_zusatzwert BEFORE UPDATE ON zusatz_wert FOR EACH ROW EXECUTE PROCEDURE update_time_zusatzwert(); | |
1273 | |
1274 | |
1275 SET search_path = bund, pg_catalog; | |
1276 | |
1277 -- | |
1278 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | |
1279 -- | |
1280 | |
1281 ALTER TABLE ONLY kommentar_m | |
1282 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); | |
1283 | |
1284 | |
1285 -- | |
1286 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | |
1287 -- | |
1288 | |
1289 ALTER TABLE ONLY kommentar_p | |
1290 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); | |
1291 | |
1292 | |
1293 -- | |
1294 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | |
1295 -- | 1034 -- |
1296 | 1035 |
1297 ALTER TABLE ONLY messung | 1036 ALTER TABLE ONLY messung |
1298 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id); | 1037 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; |
1299 | 1038 |
1300 | 1039 |
1301 -- | 1040 -- |
1302 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1041 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1303 -- | |
1304 | |
1305 ALTER TABLE ONLY messung | |
1306 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); | |
1307 | |
1308 | |
1309 -- | |
1310 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | |
1311 -- | 1042 -- |
1312 | 1043 |
1313 ALTER TABLE ONLY messung | 1044 ALTER TABLE ONLY messung |
1314 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); | 1045 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); |
1315 | 1046 |
1316 | 1047 |
1317 -- | 1048 -- |
1318 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1049 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1050 -- | |
1051 | |
1052 ALTER TABLE ONLY messung_translation | |
1053 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; | |
1054 | |
1055 | |
1056 -- | |
1057 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1319 -- | 1058 -- |
1320 | 1059 |
1321 ALTER TABLE ONLY messwert | 1060 ALTER TABLE ONLY messwert |
1322 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); | 1061 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); |
1323 | 1062 |
1324 | 1063 |
1325 -- | 1064 -- |
1326 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1065 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1327 -- | 1066 -- |
1328 | 1067 |
1329 ALTER TABLE ONLY messwert | 1068 ALTER TABLE ONLY messwert |
1330 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id); | 1069 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id); |
1331 | 1070 |
1332 | 1071 |
1333 -- | 1072 -- |
1334 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1073 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1335 -- | 1074 -- |
1336 | 1075 |
1337 ALTER TABLE ONLY messwert | 1076 ALTER TABLE ONLY messwert |
1338 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id); | 1077 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; |
1339 | 1078 |
1340 | 1079 |
1341 -- | 1080 -- |
1342 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1081 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1343 -- | 1082 -- |
1344 | 1083 |
1345 ALTER TABLE ONLY ortszuordnung | 1084 ALTER TABLE ONLY ortszuordnung |
1346 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id); | 1085 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id); |
1347 | 1086 |
1348 | 1087 |
1349 -- | 1088 -- |
1350 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1089 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1351 -- | 1090 -- |
1352 | 1091 |
1353 ALTER TABLE ONLY ortszuordnung | 1092 ALTER TABLE ONLY ortszuordnung |
1354 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id); | 1093 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id); |
1355 | 1094 |
1356 | 1095 |
1357 -- | 1096 -- |
1358 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1097 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1359 -- | 1098 -- |
1360 | 1099 |
1361 ALTER TABLE ONLY ortszuordnung | 1100 ALTER TABLE ONLY ortszuordnung |
1362 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); | 1101 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; |
1363 | 1102 |
1364 | 1103 |
1365 -- | 1104 -- |
1366 -- Name: status_protokoll_status_stufe_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1105 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1106 -- | |
1107 | |
1108 ALTER TABLE ONLY probe_translation | |
1109 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1110 | |
1111 | |
1112 -- | |
1113 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1367 -- | 1114 -- |
1368 | 1115 |
1369 ALTER TABLE ONLY status_protokoll | 1116 ALTER TABLE ONLY status_protokoll |
1370 ADD CONSTRAINT status_protokoll_status_stufe_fkey FOREIGN KEY (status_stufe) REFERENCES stammdaten.status_stufe(id); | 1117 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; |
1371 | 1118 |
1372 | 1119 |
1373 -- | 1120 -- |
1374 -- Name: status_protokoll_status_wert_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1121 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1375 -- | |
1376 | |
1377 ALTER TABLE ONLY status_protokoll | |
1378 ADD CONSTRAINT status_protokoll_status_wert_fkey FOREIGN KEY (status_wert) REFERENCES stammdaten.status_wert(id); | |
1379 | |
1380 | |
1381 -- | |
1382 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | |
1383 -- | 1122 -- |
1384 | 1123 |
1385 ALTER TABLE ONLY zusatz_wert | 1124 ALTER TABLE ONLY zusatz_wert |
1386 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id); | 1125 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; |
1387 | 1126 |
1388 | 1127 |
1389 -- | 1128 -- |
1390 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: bund; Owner: - | 1129 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - |
1391 -- | 1130 -- |
1392 | 1131 |
1393 ALTER TABLE ONLY zusatz_wert | 1132 ALTER TABLE ONLY zusatz_wert |
1394 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); | 1133 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); |
1395 | 1134 |
1396 | 1135 |
1397 SET search_path = land, pg_catalog; | |
1398 | |
1399 -- | |
1400 -- Name: kommentar_m_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1401 -- | |
1402 | |
1403 ALTER TABLE ONLY kommentar_m | |
1404 ADD CONSTRAINT kommentar_m_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; | |
1405 | |
1406 | |
1407 -- | |
1408 -- Name: kommentar_p_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1409 -- | |
1410 | |
1411 ALTER TABLE ONLY kommentar_p | |
1412 ADD CONSTRAINT kommentar_p_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1413 | |
1414 | |
1415 -- | |
1416 -- Name: messprogramm_mmt_messprogramm_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1417 -- | |
1418 | |
1419 ALTER TABLE ONLY messprogramm_mmt | |
1420 ADD CONSTRAINT messprogramm_mmt_messprogramm_id_fkey FOREIGN KEY (messprogramm_id) REFERENCES messprogramm(id) ON DELETE CASCADE; | |
1421 | |
1422 | |
1423 -- | |
1424 -- Name: messprogramm_mmt_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1425 -- | |
1426 | |
1427 ALTER TABLE ONLY messprogramm_mmt | |
1428 ADD CONSTRAINT messprogramm_mmt_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id); | |
1429 | |
1430 | |
1431 -- | |
1432 -- Name: messung_mmt_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1433 -- | |
1434 | |
1435 ALTER TABLE ONLY messung | |
1436 ADD CONSTRAINT messung_mmt_id_fkey FOREIGN KEY (mmt_id) REFERENCES stammdaten.mess_methode(id) ON DELETE CASCADE; | |
1437 | |
1438 | |
1439 -- | |
1440 -- Name: messung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1441 -- | |
1442 | |
1443 ALTER TABLE ONLY messung | |
1444 ADD CONSTRAINT messung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1445 | |
1446 | |
1447 -- | |
1448 -- Name: messung_status_protokoll_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1449 -- | |
1450 | |
1451 ALTER TABLE ONLY messung | |
1452 ADD CONSTRAINT messung_status_protokoll_id_fkey FOREIGN KEY (status) REFERENCES status_protokoll(id); | |
1453 | |
1454 | |
1455 -- | |
1456 -- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1457 -- | |
1458 | |
1459 ALTER TABLE ONLY messung_translation | |
1460 ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; | |
1461 | |
1462 | |
1463 -- | |
1464 -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1465 -- | |
1466 | |
1467 ALTER TABLE ONLY messwert | |
1468 ADD CONSTRAINT messwert_meh_id_fkey FOREIGN KEY (meh_id) REFERENCES stammdaten.mess_einheit(id); | |
1469 | |
1470 | |
1471 -- | |
1472 -- Name: messwert_messgroesse_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1473 -- | |
1474 | |
1475 ALTER TABLE ONLY messwert | |
1476 ADD CONSTRAINT messwert_messgroesse_id_fkey FOREIGN KEY (messgroesse_id) REFERENCES stammdaten.messgroesse(id); | |
1477 | |
1478 | |
1479 -- | |
1480 -- Name: messwert_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1481 -- | |
1482 | |
1483 ALTER TABLE ONLY messwert | |
1484 ADD CONSTRAINT messwert_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; | |
1485 | |
1486 | |
1487 -- | |
1488 -- Name: ortszuordnung_ort_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1489 -- | |
1490 | |
1491 ALTER TABLE ONLY ortszuordnung | |
1492 ADD CONSTRAINT ortszuordnung_ort_id_fkey FOREIGN KEY (ort_id) REFERENCES stammdaten.ort(id); | |
1493 | |
1494 | |
1495 -- | |
1496 -- Name: ortszuordnung_ortszuordnung_typ_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1497 -- | |
1498 | |
1499 ALTER TABLE ONLY ortszuordnung | |
1500 ADD CONSTRAINT ortszuordnung_ortszuordnung_typ_fkey FOREIGN KEY (ortszuordnung_typ) REFERENCES stammdaten.ortszuordnung_typ(id); | |
1501 | |
1502 | |
1503 -- | |
1504 -- Name: ortszuordnung_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1505 -- | |
1506 | |
1507 ALTER TABLE ONLY ortszuordnung | |
1508 ADD CONSTRAINT ortszuordnung_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1509 | |
1510 | |
1511 -- | |
1512 -- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1513 -- | |
1514 | |
1515 ALTER TABLE ONLY probe_translation | |
1516 ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1517 | |
1518 | |
1519 -- | |
1520 -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1521 -- | |
1522 | |
1523 ALTER TABLE ONLY status_protokoll | |
1524 ADD CONSTRAINT status_protokoll_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; | |
1525 | |
1526 | |
1527 -- | |
1528 -- Name: zusatz_wert_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1529 -- | |
1530 | |
1531 ALTER TABLE ONLY zusatz_wert | |
1532 ADD CONSTRAINT zusatz_wert_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; | |
1533 | |
1534 | |
1535 -- | |
1536 -- Name: zusatz_wert_pzs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - | |
1537 -- | |
1538 | |
1539 ALTER TABLE ONLY zusatz_wert | |
1540 ADD CONSTRAINT zusatz_wert_pzs_id_fkey FOREIGN KEY (pzs_id) REFERENCES stammdaten.proben_zusatz(id); | |
1541 | |
1542 | |
1543 COMMIT; | 1136 COMMIT; |