# HG changeset patch # User Raimund Renkert # Date 1467387999 -7200 # Node ID 196800bb22b0e46b5c9f4ef904043935a1765bc0 # Parent 9002ebe2a8af38db491c0e36aca0a8f18bd3a9b1 Replaced translation tables for probe and messung with id_alt columns. diff -r 9002ebe2a8af -r 196800bb22b0 db_schema/lada_schema.sql --- a/db_schema/lada_schema.sql Fri Jul 01 17:39:12 2016 +0200 +++ b/db_schema/lada_schema.sql Fri Jul 01 17:46:39 2016 +0200 @@ -370,6 +370,7 @@ CREATE TABLE messung ( id integer DEFAULT nextval('messung_id_seq'::regclass) NOT NULL, + id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL, probe_id integer NOT NULL, nebenproben_nr character varying(10), mmt_id character varying(2) NOT NULL, @@ -384,36 +385,6 @@ -- --- Name: messung_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: --- - -CREATE TABLE messung_translation ( - id integer NOT NULL, - messungs_id integer NOT NULL, - messungs_id_alt integer DEFAULT nextval('land.messung_messung_id_alt_seq'::regclass) NOT NULL -); - - --- --- Name: messung_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - --- - -CREATE SEQUENCE messung_translation_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: messung_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: - --- - -ALTER SEQUENCE messung_translation_id_seq OWNED BY messung_translation.id; - - --- -- Name: messwert; Type: TABLE; Schema: land; Owner: -; Tablespace: -- @@ -461,6 +432,7 @@ CREATE TABLE probe ( id integer PRIMARY KEY DEFAULT nextval('probe_id_seq'::regclass), + 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, test boolean DEFAULT false NOT NULL, netzbetreiber_id character varying(2) REFERENCES stammdaten.netz_betreiber, mst_id character varying(5) NOT NULL @@ -560,37 +532,6 @@ COMMENT ON COLUMN probe.umw_id IS 'ID für Umweltbereich'; - --- --- Name: probe_translation; Type: TABLE; Schema: land; Owner: -; Tablespace: --- - -CREATE TABLE probe_translation ( - id integer NOT NULL, - probe_id integer NOT NULL, - probe_id_alt character varying(20) DEFAULT (('sss'::text || lpad(((nextval('land.probe_probe_id_seq'::regclass))::character varying)::text, 12, '0'::text)) || 'Y'::text) NOT NULL -); - - --- --- Name: probe_translation_id_seq; Type: SEQUENCE; Schema: land; Owner: - --- - -CREATE SEQUENCE probe_translation_id_seq - START WITH 1 - INCREMENT BY 1 - NO MINVALUE - NO MAXVALUE - CACHE 1; - - --- --- Name: probe_translation_id_seq; Type: SEQUENCE OWNED BY; Schema: land; Owner: - --- - -ALTER SEQUENCE probe_translation_id_seq OWNED BY probe_translation.id; - - -- -- Name: status_protokoll; Type: TABLE; Schema: land; Owner: -; Tablespace: -- @@ -700,13 +641,6 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY messung_translation ALTER COLUMN id SET DEFAULT nextval('messung_translation_id_seq'::regclass); - - --- --- Name: id; Type: DEFAULT; Schema: land; Owner: - --- - ALTER TABLE ONLY messwert ALTER COLUMN id SET DEFAULT nextval('land.messwert_id_seq'::regclass); @@ -742,13 +676,6 @@ -- Name: id; Type: DEFAULT; Schema: land; Owner: - -- -ALTER TABLE ONLY probe_translation ALTER COLUMN id SET DEFAULT nextval('probe_translation_id_seq'::regclass); - - --- --- Name: id; Type: DEFAULT; Schema: land; Owner: - --- - ALTER TABLE ONLY status_protokoll ALTER COLUMN id SET DEFAULT nextval('land.kommentar_id_seq'::regclass); @@ -813,22 +740,6 @@ -- --- Name: messung_translation_messungs_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: --- - -ALTER TABLE ONLY messung_translation - ADD CONSTRAINT messung_translation_messungs_id_key UNIQUE (messungs_id); - - --- --- Name: messung_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: --- - -ALTER TABLE ONLY messung_translation - ADD CONSTRAINT messung_translation_pkey PRIMARY KEY (id); - - --- -- Name: messwert_messungs_id_messgroesse_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: -- @@ -853,22 +764,6 @@ -- --- Name: probe_translation_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: --- - -ALTER TABLE ONLY probe_translation - ADD CONSTRAINT probe_translation_pkey PRIMARY KEY (id); - - --- --- Name: probe_translation_probe_id_key; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: --- - -ALTER TABLE ONLY probe_translation - ADD CONSTRAINT probe_translation_probe_id_key UNIQUE (probe_id); - - --- -- Name: status_protokoll_pkey; Type: CONSTRAINT; Schema: land; Owner: -; Tablespace: -- @@ -902,13 +797,6 @@ -- --- Name: messung_translation_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: --- - -CREATE INDEX messung_translation_messungs_id_idx ON messung_translation USING btree (messungs_id); - - --- -- Name: messwert_messungs_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: -- @@ -923,13 +811,6 @@ -- --- Name: probe_translation_probe_id_idx; Type: INDEX; Schema: land; Owner: -; Tablespace: --- - -CREATE INDEX probe_translation_probe_id_idx ON probe_translation USING btree (probe_id); - - --- -- Name: tree_timestamp_status; Type: TRIGGER; Schema: land; Owner: - -- @@ -1046,14 +927,6 @@ -- --- Name: messung_translation_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - --- - -ALTER TABLE ONLY messung_translation - ADD CONSTRAINT messung_translation_messungs_id_fkey FOREIGN KEY (messungs_id) REFERENCES messung(id) ON DELETE CASCADE; - - --- -- Name: messwert_meh_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - -- @@ -1102,14 +975,6 @@ -- --- Name: probe_translation_probe_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - --- - -ALTER TABLE ONLY probe_translation - ADD CONSTRAINT probe_translation_probe_id_fkey FOREIGN KEY (probe_id) REFERENCES probe(id) ON DELETE CASCADE; - - --- -- Name: status_protokoll_messungs_id_fkey; Type: FK CONSTRAINT; Schema: land; Owner: - --