Merge branch 'DEVELOP' of ssh://git.eclipse.org:29418/openk-usermodules/org.eclipse.openk-usermodules.gridFailureInformation.backend into SI-1643-Testabdeckung
diff --git a/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql b/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql new file mode 100644 index 0000000..9046706 --- /dev/null +++ b/gfsBackendService/src/main/resources/db/migration/V0_43__CREATE_GFI_DB.sql
@@ -0,0 +1,912 @@ +----------------------------------------------------------------------------------- +-- ******************************************************************************* +-- * Copyright (c) 2019 Contributors to the Eclipse Foundation +-- * +-- * See the NOTICE file(s) distributed with this work for additional +-- * information regarding copyright ownership. +-- * +-- * This program and the accompanying materials are made available under the +-- * terms of the Eclipse Public License v. 2.0 which is available at +-- * http://www.eclipse.org/legal/epl-2.0. +-- * +-- * SPDX-License-Identifier: EPL-2.0 +-- ******************************************************************************* +----------------------------------------------------------------------------------- +-- CREATE ROLE GFI_SERVICE LOGIN +-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; +-- ALTER ROLE GFI_SERVICE with password 'gfi_service'; + +DROP TABLE IF EXISTS public.VERSION CASCADE; +DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_STATUS CASCADE; +DROP SEQUENCE IF EXISTS public.REF_STATUS_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_BRANCH CASCADE; +DROP SEQUENCE IF EXISTS public.REF_BRANCH_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_FAILURE_CLASSIFICATION CASCADE; +DROP SEQUENCE IF EXISTS public.REF_FAILURE_CLASSIFICATION_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_FAILURE_TYPE CASCADE; +DROP SEQUENCE IF EXISTS public.REF_FAILURE_TYPE_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_RADIUS CASCADE; +DROP SEQUENCE IF EXISTS public.REF_RADIUS_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_EXPECTED_REASON CASCADE; +DROP SEQUENCE IF EXISTS public.REF_EXPECTED_REASON_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_IMPORT_DATA CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_IMPORT_DATA_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_DISTRIBUTION_GROUP CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_DISTRIBUTION_GROUP_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_STATION_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL; +DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL_SEQ; + +DROP TABLE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT_ID_SEQ; + +-- --------------------------------------------- +-- TABLE VERSION +-- --------------------------------------------- +CREATE TABLE public.VERSION +( + ID integer NOT NULL, + VERSION character varying(50) NOT NULL, + CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.VERSION + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.VERSION TO GFI_SERVICE; + +INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' ); + +-- --------------------------------------------- +-- TABLE REF_STATUS +-- --------------------------------------------- +CREATE SEQUENCE public.ref_status_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_status_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.REF_STATUS +( + ID integer NOT NULL DEFAULT nextval('REF_STATUS_ID_SEQ'::regclass), + UUID uuid NOT NULL, + STATUS character varying(50) NOT NULL, + CONSTRAINT REF_STATUS_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.REF_STATUS + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.REF_STATUS TO GFI_SERVICE; + +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (1, 'acabc8f6-2cf3-485a-a4f8-68d178c7df45', 'neu'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (2, 'f2e44dc5-d30c-4128-86bb-d3d5fc766b61', 'geplant'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (3, '23fc0254-cc3d-4371-97ad-54ef733008ae', 'angelegt'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (4, '74a4ca78-7268-11ea-bc55-0242ac130003', 'storniert'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (5, '7264e572-eae9-4cca-be05-af6b0d081247', 'qualifiziert'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (6, '9374219a-7419-4b72-899d-cd0576d85cdb', 'aktualisiert'); +INSERT INTO public.REF_STATUS (ID, UUID, STATUS) VALUES (7, '8c333345-5c3c-41ed-9de4-323045f64259', 'abgeschlossen'); + + +-- --------------------------------------------- +-- TABLE REF_FAILURE_CLASSIFICATION +-- --------------------------------------------- +CREATE SEQUENCE public.ref_failure_classification_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_failure_classification_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.REF_FAILURE_CLASSIFICATION +( + ID integer NOT NULL DEFAULT nextval('REF_FAILURE_CLASSIFICATION_ID_SEQ'::regclass), + UUID uuid NOT NULL, + CLASSIFICATION character varying(50) NOT NULL, + DESCRIPTION character varying(255) NULL, + CONSTRAINT REF_FAILURE_CLASSIFICATION_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.REF_FAILURE_CLASSIFICATION + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.REF_FAILURE_CLASSIFICATION TO GFI_SERVICE; + +INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '9255fb79-c57a-4448-a69c-5d57994f0c91', 'Störung', NULL ); +INSERT INTO public.REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( '8ec1e144-5230-4d43-a3df-f62dd64bb855', 'geplante Maßnahme', NULL ); + +-- --------------------------------------------- +-- TABLE REF_BRANCH +-- --------------------------------------------- +CREATE SEQUENCE public.ref_branch_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_branch_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.REF_BRANCH +( + ID integer NOT NULL DEFAULT nextval('REF_BRANCH_ID_SEQ'::regclass), + UUID uuid NOT NULL, + NAME character varying(50) NOT NULL, + DESCRIPTION character varying(255), + COLOR_CODE character varying(20), + CONSTRAINT REF_BRANCH_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.REF_BRANCH + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.REF_BRANCH TO GFI_SERVICE; + +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('535b4beb-9b17-4247-bb8b-26bd01b48f9a', 'S', 'Strom', '#fc6042'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d41f54e5-c4df-440e-b334-40e8f3a6854a', 'G', 'Gas', '#fdea64'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('62c6d361-96a0-41cc-bda1-4e58ad16f21a', 'F', 'Fernwärme', '#2cc990'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c', 'W', 'Wasser', '#2c82c9'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('7d4907fb-cb3f-4a4f-93e9-839052e76894', 'TK', 'Telekommunikation', '#ff33cc'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('014c4a2a-3cf1-4d28-af70-4573722bceb0', 'ST', 'Sekundärtechnik', '#9933ff'); +INSERT INTO public.ref_branch(uuid, name, description, color_code) VALUES ('8fb3c764-8fb5-11ea-bc55-0242ac130003', 'OS', 'ohne Sparte', '#ffffff'); + + +-- --------------------------------------------- +-- TABLE REF_EXPECTED_REASON +-- --------------------------------------------- +CREATE SEQUENCE public.ref_expected_reason_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_expected_reason_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.REF_EXPECTED_REASON +( + ID integer NOT NULL DEFAULT nextval('REF_EXPECTED_REASON_ID_SEQ'::regclass), + UUID uuid NOT NULL, + TEXT character varying(100) NOT NULL, + DESCRIPTION character varying(255) NULL, + BRANCHES character varying(100) NOT NULL, + CONSTRAINT REF_EXPECTED_REASON_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.REF_EXPECTED_REASON + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.REF_EXPECTED_REASON TO GFI_SERVICE; + +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07918-6aa0-11ea-bc55-0242ac130003', 'Defekt technische Anlage', 'Dies ist die Beschreibung für: Defekt technische Anlage', 'S,G,W,F,TK,ST,OS'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07c42-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung', 'S'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07d50-6aa0-11ea-bc55-0242ac130003', 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung', 'S'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07e22-6aa0-11ea-bc55-0242ac130003', 'Leitung beschädigt', 'Dies ist die Beschreibung für: Leitung beschädigt', 'S,G,W,F,TK,ST'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07eea-6aa0-11ea-bc55-0242ac130003', 'noch nicht bekannt', 'Dies ist die Beschreibung für: noch nicht bekannt', 'S,G,W,F,TK,ST,OS'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a07fbc-6aa0-11ea-bc55-0242ac130003', 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch', 'W'); +INSERT INTO public.ref_expected_reason(uuid, text, description, branches) VALUES ('27a08160-6aa0-11ea-bc55-0242ac130003', 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung', 'S,G,W,F,TK,ST'); + +-- --------------------------------------------- +-- TABLE REF_RADIUS +-- --------------------------------------------- +CREATE SEQUENCE public.ref_radius_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_radius_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.REF_RADIUS +( + ID integer NOT NULL DEFAULT nextval('REF_RADIUS_ID_SEQ'::regclass), + UUID uuid NOT NULL, + RADIUS integer NOT NULL, + CONSTRAINT REF_RADIUS_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.REF_RADIUS + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.REF_RADIUS TO GFI_SERVICE; + +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('e6439b49-ce5a-486e-a437-b362ec73dc44', 0); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671000-6aa6-11ea-bc55-0242ac130003', 50); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366712a8-6aa6-11ea-bc55-0242ac130003', 100); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('366713c0-6aa6-11ea-bc55-0242ac130003', 250); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667150a-6aa6-11ea-bc55-0242ac130003', 500); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671780-6aa6-11ea-bc55-0242ac130003', 1000); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('3667187a-6aa6-11ea-bc55-0242ac130003', 1500); +INSERT INTO public.REF_RADIUS(uuid, radius) VALUES ('36671ad2-6aa6-11ea-bc55-0242ac130003', 2000); + +-- --------------------------------------------- +-- TABLE TBL_FAILURE_INFORMATION +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_failure_information_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_failure_information_id_seq + OWNER TO GFI_SERVICE; + +CREATE TABLE public.TBL_FAILURE_INFORMATION +( + ID integer NOT NULL DEFAULT nextval('TBL_FAILURE_INFORMATION_ID_SEQ'::regclass), + UUID uuid NOT NULL, + VERSION_NUMBER integer NOT NULL, + FK_REF_FAILURE_CLASSIFICATION integer, + TITLE character varying(255) NULL, + DESCRIPTION character varying(255) NULL, + RESPONSIBILITY character varying(255), + FK_REF_STATUS_INTERN integer NOT NULL, + FK_REF_BRANCH integer NOT NULL, + VOLTAGE_LEVEL character varying(2), + PRESSURE_LEVEL character varying(2), + FAILURE_BEGIN timestamp NOT NULL, + FAILURE_END_PLANNED timestamp, + FAILURE_END_RESUPPLIED timestamp, + INTERNAL_REMARK character varying(1024), + POSTCODE character varying(30), + CITY character varying(255), + DISTRICT character varying(255), + STREET character varying(255), + HOUSENUMBER character varying(30), + STATION_ID character varying (30), + STATION_DESCRIPTION character varying (255), + STATION_COORDS character varying (255), + FK_REF_RADIUS integer, + LONGITUDE numeric(9,6), + LATITUDE numeric(9,6), + FK_TBL_FAILURE_INFORMATION_CONDENSED integer, + CONDENSED boolean, + CONDENSED_COUNT integer, + OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255), + PUBLICATION_STATUS character varying (40), + PUBLICATION_FREETEXT character varying (1024), + FK_REF_EXPECTED_REASON integer, + CREATE_DATE timestamp without time zone NOT NULL, + CREATE_USER character varying(100) NOT NULL, + MOD_DATE timestamp without time zone NOT NULL, + MOD_USER character varying(100), + CONSTRAINT TBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (id), + CONSTRAINT TBL_FAIL_INF__FCLASS_FKEY FOREIGN KEY (FK_REF_FAILURE_CLASSIFICATION) + REFERENCES public.REF_FAILURE_CLASSIFICATION (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_FAIL_INF_STATUSINT_FKEY FOREIGN KEY (FK_REF_STATUS_INTERN) + REFERENCES public.REF_STATUS (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_FAIL_INF__BRANCH_ID_FKEY FOREIGN KEY (FK_REF_BRANCH) + REFERENCES public.REF_BRANCH (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_FAIL_INF__EXPREASON_FKEY FOREIGN KEY (FK_REF_EXPECTED_REASON) + REFERENCES public.REF_EXPECTED_REASON (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); + +ALTER TABLE public.TBL_FAILURE_INFORMATION + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.TBL_FAILURE_INFORMATION TO GFI_SERVICE; + +INSERT INTO public.tbl_failure_information(UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, CONDENSED, CONDENSED_COUNT, FK_TBL_FAILURE_INFORMATION_CONDENSED, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER) +VALUES ('6432a9c9-0384-44af-9bb8-34f2878d7b49',3, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 1, 1,'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00', 'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, false, 0, null, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT'); + +INSERT INTO public.tbl_failure_information(UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, CONDENSED, CONDENSED_COUNT, FK_TBL_FAILURE_INFORMATION_CONDENSED, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER) +VALUES ('37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, false, 0, null, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT'); + + +-- --------------------------------------------- +-- TABLE TBL_DISTRIBUTION_GROUP +-- --------------------------------------------- +CREATE SEQUENCE public.TBL_DISTRIBUTION_GROUP_ID_SEQ + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.TBL_DISTRIBUTION_GROUP_ID_SEQ + OWNER TO GFI_SERVICE; + + +CREATE TABLE public.TBL_DISTRIBUTION_GROUP +( + ID integer NOT NULL DEFAULT nextval('TBL_DISTRIBUTION_GROUP_ID_SEQ'::regclass), + UUID uuid NOT NULL, + NAME character varying(255), + DISTRIBUTION_TEXT character varying(2048), + CONSTRAINT TBL_DISTRIBUTION_GROUP_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.TBL_DISTRIBUTION_GROUP + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP TO GFI_SERVICE; + +INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT) +VALUES('5f5017fe-887a-11ea-bc55-0242ac130003', 'Verteiler technisch - intern', 'Subject: Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert. + +Body: +Verteiler fachlich TEST + +Sehr geehrte Damen und Herren, + +die im Betreff genannte Meldung ist über folgenden Link erreichbar: + +$directFailureLink$ + +Mit freundlichen Grüßen +Ihre Admin-Meister-Team der PTA GmbH'); + +INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT) +VALUES('5f502046-887a-11ea-bc55-0242ac130003', 'Verteiler fachlich - intern', 'Subject: Die Störung (Sparte: $Sparte$) mit Beginn: $Störungsbeginn_gemeldet$ wurde in den Status veröffentlicht geändert. + +Body: +Verteiler fachlich TEST + +Sehr geehrte Damen und Herren, + +die im Betreff genannte Meldung ist über folgenden Link erreichbar: + +$directFailureLink$ + +Mit freundlichen Grüßen +Ihre Admin-Meister-Team der PTA GmbH'); + +INSERT INTO public.tbl_distribution_group(UUID, NAME, DISTRIBUTION_TEXT) +VALUES('f71660e2-aee1-11ea-b3de-0242ac130004', 'Veröffentlicher', 'Subject: Bitte Anpassen - Das geplannte Ende der Störung (Sparte: $Sparte$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch. + +Body: +Veröffentlicher + +Sehr geehrte Damen und Herren, + +die im Betreff genannte Meldung ist über folgenden Link erreichbar: + +$directFailureLink$ + +Mit freundlichen Grüßen'); + +-- --------------------------------------------- +-- TABLE TBL_DISTRIBUTION_GROUP_MEMBER +-- --------------------------------------------- +CREATE SEQUENCE public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ + OWNER TO GFI_SERVICE; + +CREATE TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER +( + ID integer NOT NULL DEFAULT nextval('TBL_DISTRIBUTION_GROUP_MEMBER_ID_SEQ'::regclass), + UUID uuid NOT NULL, + FK_TBL_DISTRIBUTION_GROUP integer NOT NULL, + CONTACT_ID uuid NOT NULL, + CONSTRAINT TBL_DISTRIBUTION_GROUP_MEMBER_PKEY PRIMARY KEY (id), + CONSTRAINT TBL_DIS_GRP_MEM__DIS_GRP_FKEY FOREIGN KEY (FK_TBL_DISTRIBUTION_GROUP) + REFERENCES public.TBL_DISTRIBUTION_GROUP (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION + ); + +ALTER TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.TBL_DISTRIBUTION_GROUP_MEMBER TO GFI_SERVICE; + + +INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID) +VALUES('5f5021a4-887a-11ea-bc55-0242ac130003', 1, 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e' ); + +INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID) +VALUES('5f50228a-887a-11ea-bc55-0242ac130003', 1, '7782179b-fb79-4370-8f71-f4c71470d006' ); + +INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID) +VALUES('14dcac8c-887f-11ea-bc55-0242ac130003', 1, 'fa3d981b-a7d6-4965-a623-cdbc69404153' ); + +INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID) +VALUES('14dcaef8-887f-11ea-bc55-0242ac130003', 2, 'c862d604-5766-43d6-a7e8-a4bac2bd01e1'); + +INSERT INTO public.tbl_distribution_group_member(UUID, FK_TBL_DISTRIBUTION_GROUP, CONTACT_ID) +VALUES('14dcaffc-887f-11ea-bc55-0242ac130003', 2, '8fe41b90-d10c-4a70-8fde-0990286ad3c6'); + + +-- --------------------------------------------- +-- TABLE TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_failure_information_distribution_group_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_failure_information_distribution_group_id_seq + OWNER TO gfi_service; + +CREATE TABLE public.tbl_failure_information_distribution_group +( + id integer NOT NULL DEFAULT nextval('tbl_failure_information_distribution_group_id_seq'::regclass), + fk_tbl_failure_information integer NOT NULL, + fk_tbl_distribution_group integer NOT NULL, + CONSTRAINT tbl_failure_information_distribution_group_pkey PRIMARY KEY (id), + CONSTRAINT tbl_fail_inf_dist_grp__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information) + REFERENCES public.tbl_failure_information (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT tbl_fail_inf_dist_grp__distribution_group_fkey FOREIGN KEY (fk_tbl_distribution_group) + REFERENCES public.tbl_distribution_group (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); +ALTER TABLE public.tbl_failure_information_distribution_group + OWNER TO gfi_service; +GRANT ALL ON TABLE public.tbl_failure_information_distribution_group TO gfi_service; + +INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP) +VALUES(1, 1); +INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP) +VALUES(1, 2); +INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP) +VALUES(2, 1); +INSERT INTO public.tbl_failure_information_distribution_group(FK_TBL_FAILURE_INFORMATION, FK_TBL_DISTRIBUTION_GROUP) +VALUES(2, 2); + +CREATE UNIQUE INDEX idx_tbl_fi_distribution_group_uq ON public.tbl_failure_information_distribution_group ( fk_tbl_failure_information, fk_tbl_distribution_group ); + +-- --------------------------------------------- +-- TABLE TBL_FAILURE_INFORMATION_STATION +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_failure_information_station_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_failure_information_station_id_seq + OWNER TO gfi_service; + +CREATE TABLE public.tbl_failure_information_station +( + id integer NOT NULL DEFAULT nextval('tbl_failure_information_station_id_seq'::regclass), + fk_tbl_failure_information integer NOT NULL, + station_station_id character varying(30) NOT NULL, + CONSTRAINT tbl_failure_information_station_pkey PRIMARY KEY (id), + CONSTRAINT tbl_fail_inf_station__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information) + REFERENCES public.tbl_failure_information (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); +ALTER TABLE public.tbl_failure_information_station + OWNER TO gfi_service; +GRANT ALL ON TABLE public.tbl_failure_information_station TO gfi_service; + +-- --------------------------------------------- +-- TABLE TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL +-- --------------------------------------------- + +CREATE SEQUENCE public.tbl_failure_information_publication_channel_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_failure_information_publication_channel_seq + OWNER TO gfi_service; + + +CREATE TABLE public.tbl_failure_information_publication_channel +( + id integer NOT NULL DEFAULT nextval('tbl_failure_information_publication_channel_seq'::regclass), + fk_tbl_failure_information integer NOT NULL, + publication_channel character varying(50) NOT NULL, + published boolean NOT NULL, + CONSTRAINT tbl_failure_information_publication_channel_pkey PRIMARY KEY (id), + CONSTRAINT tbl_failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information) + REFERENCES public.tbl_failure_information (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); + +ALTER TABLE public.tbl_failure_information_publication_channel + OWNER TO gfi_service; +GRANT ALL ON TABLE public.tbl_failure_information_publication_channel TO gfi_service; + +INSERT INTO public.tbl_failure_information_publication_channel(FK_TBL_FAILURE_INFORMATION, PUBLICATION_CHANNEL, PUBLISHED) +VALUES(1, 'Mail', false); + + +-- --------------------------------------------- +-- TABLE TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_failure_information_reminder_mail_sent_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_failure_information_reminder_mail_sent_id_seq + OWNER TO gfi_service; + +CREATE TABLE public.tbl_failure_information_reminder_mail_sent +( + id integer NOT NULL DEFAULT nextval('tbl_failure_information_reminder_mail_sent_id_seq'::regclass), + fk_tbl_failure_information integer NOT NULL, + mail_sent boolean NOT NULL, + date_mail_sent timestamp, + CONSTRAINT tbl_failure_information_reminder_mail_sent_pkey PRIMARY KEY (id), + CONSTRAINT tbl_fail_inf_rem_mail_sent__failure_information_id_fkey FOREIGN KEY (fk_tbl_failure_information) + REFERENCES public.tbl_failure_information (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); +ALTER TABLE public.tbl_failure_information_reminder_mail_sent + OWNER TO gfi_service; +GRANT ALL ON TABLE public.tbl_failure_information_reminder_mail_sent TO gfi_service; + +INSERT INTO public.tbl_failure_information_reminder_mail_sent(FK_TBL_FAILURE_INFORMATION, mail_sent, date_mail_sent) +VALUES(1, true, '2021-01-21 00:01:00'); + + +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- +-- HISTORY-TABLES +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- + + +-- PUBLIC.HTBL_FAILURE_INFORMATION Automatic generanted History Table DDL -- +-- <GENERATED CODE!> + +DROP TABLE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION; +DROP SEQUENCE IF EXISTS PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ; + +CREATE SEQUENCE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; + +ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION_ID_SEQ + OWNER TO gfi_service; + +CREATE TABLE PUBLIC.HTBL_FAILURE_INFORMATION +( + HID integer NOT NULL DEFAULT nextval('HTBL_FAILURE_INFORMATION_ID_SEQ'::regclass), + HACTION integer NOT NULL, + HDATE timestamp without time zone NOT NULL, + HUSER character varying(100), + + ID integer, + UUID uuid, + VERSION_NUMBER integer, + FK_REF_FAILURE_CLASSIFICATION integer, + TITLE character varying(255) NULL, + DESCRIPTION character varying(255) NULL, + RESPONSIBILITY character varying (255), + FK_REF_STATUS_INTERN integer, + FK_REF_BRANCH integer, + VOLTAGE_LEVEL character varying (2), + PRESSURE_LEVEL character varying (2), + FAILURE_BEGIN timestamp without time zone, + FAILURE_END_PLANNED timestamp without time zone, + FAILURE_END_RESUPPLIED timestamp without time zone, + INTERNAL_REMARK character varying(1024), + POSTCODE character varying (30), + CITY character varying (255), + DISTRICT character varying (255), + STREET character varying (255), + HOUSENUMBER character varying (30), + STATION_ID character varying (30), + STATION_DESCRIPTION character varying (255), + STATION_COORDS character varying (255), + FK_REF_RADIUS integer, + LONGITUDE numeric(9,6), + LATITUDE numeric(9,6), + FK_TBL_FAILURE_INFORMATION_CONDENSED integer, + CONDENSED boolean, + CONDENSED_COUNT integer, + OBJECT_REFERENCE_EXTERNAL_SYSTEM character varying (255), + PUBLICATION_STATUS character varying (40), + PUBLICATION_FREETEXT character varying (1024), + FK_REF_EXPECTED_REASON integer, + CREATE_USER character varying(100), + CREATE_DATE timestamp without time zone, + MOD_USER character varying(100), + MOD_DATE timestamp without time zone, + CONSTRAINT HTBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (HID) +) +WITH ( + OIDS=FALSE +); + +ALTER TABLE PUBLIC.HTBL_FAILURE_INFORMATION + OWNER TO gfi_service; +GRANT ALL ON TABLE PUBLIC.HTBL_FAILURE_INFORMATION TO gfi_service; + +INSERT INTO public.htbl_failure_information(HACTION, HDATE, HUSER, ID, UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, FK_TBL_FAILURE_INFORMATION_CONDENSED, CONDENSED, CONDENSED_COUNT, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER) +VALUES (1, '2020-03-13 00:00:00', 'SCRIPT', 1, '6432a9c9-0384-44af-9bb8-34f2878d7b49', 1, 1, 'Stromausfall Murr', 'Es gibt einen Stromausfall im Bereich Murr/Westbezirk', 'Rolf Rudis', 1, 1, 'NS', null, '2021-01-19 00:00:00', '2021-01-22 00:00:00', '2021-01-22 12:00:00', 'Der Bagger grub zu tief', '71711', 'Murr', 'Westbezirk', 'Ferdinand-Porsche-Straße', '2', '52863a', 'Trafo 1', '124,2323', 2, 9.247952, 48.955700, 'Link - extenes System', 'veröffentlicht', 'Freitext für die Veröffentlichung: Defekt an Trafostation', 1, null, false, 0, '2020-01-22 15:32:15', 'SCRIPT', '2020-01-24 08:02:44', 'SCRIPT'); + +INSERT INTO public.htbl_failure_information(HACTION, HDATE, HUSER, ID, UUID, VERSION_NUMBER, FK_REF_FAILURE_CLASSIFICATION, TITLE, DESCRIPTION, RESPONSIBILITY, FK_REF_STATUS_INTERN, FK_REF_BRANCH, VOLTAGE_LEVEL, PRESSURE_LEVEL, FAILURE_BEGIN, FAILURE_END_PLANNED, FAILURE_END_RESUPPLIED, INTERNAL_REMARK, POSTCODE, CITY, DISTRICT, STREET, HOUSENUMBER, STATION_ID, STATION_DESCRIPTION, STATION_COORDS, FK_REF_RADIUS, LONGITUDE, LATITUDE, OBJECT_REFERENCE_EXTERNAL_SYSTEM, PUBLICATION_STATUS, PUBLICATION_FREETEXT, FK_REF_EXPECTED_REASON, FK_TBL_FAILURE_INFORMATION_CONDENSED, CONDENSED, CONDENSED_COUNT, CREATE_DATE, CREATE_USER, MOD_DATE, MOD_USER) +VALUES (2, '2020-03-13 00:00:00', 'SCRIPT', 2, '37aef635-d0d4-4c47-ac25-c0d16c29e35c', 1, 2, 'Gasleck Oldenburg', 'Es gibt ein Gasleck in Oldenburg', 'Bernd Britzel', 2, 3, 'NS', 'HD', '2021-05-19 00:00:00', '2021-05-22 00:00:00', '2021-05-22 12:00:00', 'HD betroffen', '26133', 'Oldenburg', null, 'Cloppenburger Str.', '302', '1234863-b234', 'ertf', '124,2323', 4, 8.210150, 53.111820, 'Link - extenes System', 'nicht veröffentlicht', 'Freitext: Eine Gasleitung wurde beschädigt', 4, null, false, 0, '2020-02-03 15:15:15', 'SCRIPT', '2020-02-24 20:08:41', 'SCRIPT'); + + +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- +-- Tables ADDRESSIMPORT +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- + +/*DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ;*/ + +-- --------------------------------------------- +-- TABLE TBL_ADDRESS +-- --------------------------------------------- +/*CREATE SEQUENCE public.TBL_ADDRESS_ID_SEQ + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.TBL_ADDRESS_ID_SEQ + OWNER TO GFI_SERVICE; + +CREATE TABLE public.TBL_ADDRESS +( + ID integer NOT NULL DEFAULT nextval('TBL_ADDRESS_ID_SEQ'::regclass), + UUID uuid NOT NULL, + SDOX1 numeric(9,2) NOT NULL, + SDOY1 numeric(10,2) NOT NULL, + G3EFID numeric, + POSTCODE character varying(30), + COMMUNITY character varying(255), + DISTRICT character varying(255), + STREET character varying(255), + HOUSENUMBER character varying(30), + WATER_CONNECTION boolean, + WATER_GROUP character varying(255), + GAS_CONNECTION boolean, + GAS_GROUP character varying(255), + POWER_CONNECTION boolean, + STATION_ID character varying(30), + LONGITUDE numeric(9,6), + LATITUDE numeric(9,6), + CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.TBL_ADDRESS + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.TBL_ADDRESS TO GFI_SERVICE; + +CREATE INDEX idx_tbl_address_g3efid ON public.TBL_ADDRESS ( G3EFID ); +CREATE INDEX idx_tbl_address_postcode ON public.TBL_ADDRESS ( POSTCODE ); +CREATE INDEX idx_tbl_address_community ON public.TBL_ADDRESS ( COMMUNITY ); +CREATE INDEX idx_tbl_address_district ON public.TBL_ADDRESS ( DISTRICT ); +CREATE INDEX idx_tbl_address_street ON public.TBL_ADDRESS ( STREET ); +CREATE INDEX idx_tbl_address_station_id ON public.TBL_ADDRESS ( STATION_ID );*/ + + +-- --------------------------------------------- +-- TABLE TBL_STATION +-- --------------------------------------------- + +/*DROP TABLE IF EXISTS public.TBL_STATION CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_STATION_ID_SEQ; + +CREATE SEQUENCE public.TBL_STATION_ID_SEQ + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.TBL_STATION_ID_SEQ + OWNER TO GFI_SERVICE; + +CREATE TABLE public.TBL_STATION +( + ID integer NOT NULL DEFAULT nextval('TBL_STATION_ID_SEQ'::regclass), + UUID uuid NOT NULL, + SDOX1 numeric(9,2) NOT NULL, + SDOY1 numeric(10,2) NOT NULL, + G3EFID numeric, + STATION_ID character varying(30), + STATION_NAME character varying(255), + LONGITUDE numeric(9,6), + LATITUDE numeric(9,6), + CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.TBL_STATION + OWNER TO GFI_SERVICE; +GRANT ALL ON TABLE public.TBL_STATION TO GFI_SERVICE; + +CREATE INDEX idx_tbl_station_g3efid ON public.TBL_STATION ( G3EFID ); +CREATE INDEX idx_tbl_station_station_id ON public.TBL_STATION ( STATION_ID ); +CREATE INDEX idx_tbl_station_uuid ON public.TBL_STATION ( UUID ); +*/ + +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- +-- TRIGGER +-- ---------------------------------------------------------------------------- +-- ---------------------------------------------------------------------------- + + +-- PUBLIC.TBL_FAILURE_INFORMATION INSERT TRIGGER -- +-- <GENERATED CODE!> +CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG() + RETURNS trigger AS +$BODY$ + BEGIN + INSERT INTO HTBL_FAILURE_INFORMATION ( + ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN, FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER, + + HACTION, + HDATE, + HUSER + ) + VALUES ( + + NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN, NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER, + + 1, + current_timestamp, + NEW.CREATE_USER ); + + RETURN NEW; + END; +$BODY$ + LANGUAGE plpgsql VOLATILE + COST 100; +ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG() + OWNER TO gfi_service; + + +DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_INSERT_TRG ON PUBLIC.TBL_FAILURE_INFORMATION; + +CREATE TRIGGER TBL_FAILURE_INFORMATION_INSERT_TRG + BEFORE INSERT + ON PUBLIC.TBL_FAILURE_INFORMATION + FOR EACH ROW + EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_INSERT_TRG(); + + + +-- PUBLIC.TBL_FAILURE_INFORMATION UPDATE TRIGGER -- +-- <GENERATED CODE!> + +CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG() + RETURNS trigger AS +$BODY$ + BEGIN + INSERT INTO HTBL_FAILURE_INFORMATION ( + ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER, + + HACTION, + HDATE, + HUSER + ) + VALUES ( + NEW.ID,NEW.UUID,NEW.VERSION_NUMBER,NEW.FK_REF_FAILURE_CLASSIFICATION,NEW.TITLE,NEW.DESCRIPTION,NEW.RESPONSIBILITY,NEW.FK_REF_STATUS_INTERN,NEW.FK_REF_BRANCH,NEW.VOLTAGE_LEVEL,NEW.PRESSURE_LEVEL,NEW.FAILURE_BEGIN,NEW.FAILURE_END_PLANNED,NEW.FAILURE_END_RESUPPLIED,NEW.INTERNAL_REMARK,NEW.POSTCODE,NEW.CITY,NEW.DISTRICT,NEW.STREET,NEW.HOUSENUMBER,NEW.STATION_ID,NEW.STATION_DESCRIPTION,NEW.STATION_COORDS,NEW.FK_REF_RADIUS,NEW.LONGITUDE,NEW.LATITUDE,NEW.FK_TBL_FAILURE_INFORMATION_CONDENSED,NEW.CONDENSED,NEW.CONDENSED_COUNT,NEW.OBJECT_REFERENCE_EXTERNAL_SYSTEM,NEW.PUBLICATION_STATUS,NEW.PUBLICATION_FREETEXT,NEW.FK_REF_EXPECTED_REASON,NEW.CREATE_DATE,NEW.CREATE_USER,NEW.MOD_DATE,NEW.MOD_USER, + 2, + current_timestamp, + NEW.MOD_USER + ); + + RETURN NEW; + END; +$BODY$ + LANGUAGE plpgsql VOLATILE + COST 100; +ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG() + OWNER TO gfi_service; + +DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_UPDATE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION; + +CREATE TRIGGER TBL_FAILURE_INFORMATION_UPDATE_TRG + BEFORE UPDATE + ON PUBLIC.TBL_FAILURE_INFORMATION + FOR EACH ROW + EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_UPDATE_TRG(); + + + +-- PUBLIC.TBL_FAILURE_INFORMATION DELETE TRIGGER -- +-- <GENERATED CODE!> + +CREATE OR REPLACE FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG() + RETURNS trigger AS +$BODY$ + BEGIN + IF TG_OP = 'DELETE' THEN + INSERT INTO HTBL_FAILURE_INFORMATION ( + + ID,UUID,VERSION_NUMBER,FK_REF_FAILURE_CLASSIFICATION,TITLE,DESCRIPTION,RESPONSIBILITY,FK_REF_STATUS_INTERN,FK_REF_BRANCH,VOLTAGE_LEVEL,PRESSURE_LEVEL,FAILURE_BEGIN,FAILURE_END_PLANNED,FAILURE_END_RESUPPLIED,INTERNAL_REMARK,POSTCODE,CITY,DISTRICT,STREET,HOUSENUMBER,STATION_ID,STATION_DESCRIPTION,STATION_COORDS,FK_REF_RADIUS,LONGITUDE,LATITUDE,FK_TBL_FAILURE_INFORMATION_CONDENSED,CONDENSED,CONDENSED_COUNT,OBJECT_REFERENCE_EXTERNAL_SYSTEM,PUBLICATION_STATUS,PUBLICATION_FREETEXT,FK_REF_EXPECTED_REASON,CREATE_DATE,CREATE_USER,MOD_DATE,MOD_USER, + + HACTION, + HDATE, + HUSER + ) + VALUES ( + OLD.ID,OLD.UUID,OLD.VERSION_NUMBER,OLD.FK_REF_FAILURE_CLASSIFICATION,OLD.TITLE,OLD.DESCRIPTION,OLD.RESPONSIBILITY,OLD.FK_REF_STATUS_INTERN,OLD.FK_REF_BRANCH,OLD.VOLTAGE_LEVEL,OLD.PRESSURE_LEVEL,OLD.FAILURE_BEGIN,OLD.FAILURE_END_PLANNED,OLD.FAILURE_END_RESUPPLIED,OLD.INTERNAL_REMARK,OLD.POSTCODE,OLD.CITY,OLD.DISTRICT,OLD.STREET,OLD.HOUSENUMBER,OLD.STATION_ID,OLD.STATION_DESCRIPTION,OLD.STATION_COORDS,OLD.FK_REF_RADIUS,OLD.LONGITUDE,OLD.LATITUDE,OLD.FK_TBL_FAILURE_INFORMATION_CONDENSED,OLD.CONDENSED,OLD.CONDENSED_COUNT,OLD.OBJECT_REFERENCE_EXTERNAL_SYSTEM,OLD.PUBLICATION_STATUS,OLD.PUBLICATION_FREETEXT,OLD.FK_REF_EXPECTED_REASON,OLD.CREATE_DATE,OLD.CREATE_USER,OLD.MOD_DATE,OLD.MOD_USER, + + 3, + current_timestamp, + OLD.MOD_USER ); + END IF; + + RETURN OLD; + END; +$BODY$ + LANGUAGE plpgsql VOLATILE + COST 100; +ALTER FUNCTION PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG() + OWNER TO gfi_service; + +DROP TRIGGER IF EXISTS TBL_FAILURE_INFORMATION_DELETE_TRG ON PUBLIC.TBL_FAILURE_INFORMATION; + +CREATE TRIGGER TBL_FAILURE_INFORMATION_DELETE_TRG + BEFORE DELETE + ON PUBLIC.TBL_FAILURE_INFORMATION + FOR EACH ROW + EXECUTE PROCEDURE PUBLIC.TBL_FAILURE_INFORMATION_DELETE_TRG(); + +-- --------------------------------------------- +-- INSERT TEST DATA +-- --------------------------------------------- + +-- tbl_address + +INSERT INTO public.tbl_address +(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES +('0bb024ea-8974-4c39-a167-8c430cd09db9', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.351669311523438, 49.63756523757894); + +INSERT INTO public.tbl_address +(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES +('6669f4b1-1731-4b9e-8584-38fea6bd9b8a', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.336992263793945, 49.62066424962382); + + +INSERT INTO public.tbl_address +(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES +('c0a7f3c5-9b47-4214-b212-c76c4543314e', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.379220962524414, 49.62233237106394); + +INSERT INTO public.tbl_address +(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES +('93bb8dcb-a72c-472f-a570-09a4be00c2af', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.372011184692383, 49.64173398743793); + + +INSERT INTO public.tbl_address +(uuid, sdox1, sdoy1, g3efid, postcode, community, district, street, housenumber, water_connection, water_group, gas_connection, gas_group, power_connection, station_id, longitude, latitude) VALUES +('dd9f4ce3-4caa-4a7a-ab96-76475ba3cbc9', 493155.86, 5477555.33, 5026306, '11111', 'Test Polygon Worms', 'Test Polygon Worms', 'Test-Strasse', '18', false, NULL, false, NULL, true, '111001', 8.351669311523438, 49.63756523757894); + + +-- tbl_station + +INSERT INTO public.tbl_station +(uuid, sdox1, sdoy1, g3efid, station_id, station_name, longitude, latitude) VALUES +('c155a74e-1882-4fa9-a96b-76e313671618', 487484.68, 5527339.19, 5711305, '111001', 'Test Polygon Worms', 8.351669311523438, 49.63756523758894); +