lada/lada-server: db_schema/lada

annotate db_schema/lada_schema.sql @ 1230:f48be4a5c6a0

Correct package.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 09 Dec 2016 18:15:42 +0100
parents	6e393b614edf
children	9dfb52db6a0f

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

Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1230:f48be4a5c6a0