lada/lada-server: db_schema/lada

annotate db_schema/lada_schema.sql @ 1001:1c4aa7ea63aa schema-update

Moved trigger creation to table definition.

author	Raimund Renkert <raimund.renkert@intevation.de>
date	Fri, 01 Jul 2016 19:04:49 +0200
parents	1489f0ade850
children	8ec5ee1c2dc6

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

Mercurial > lada > lada-server

annotate db_schema/lada_schema.sql @ 1001:1c4aa7ea63aa schema-update