| |
| ----------------------------------------------------------------------------------- |
| -- ******************************************************************************* |
| -- * 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 |
| -- ******************************************************************************* |
| ----------------------------------------------------------------------------------- |
| |
| |
| -- --------------------------------------------- |
| -- TABLE VERSION |
| -- --------------------------------------------- |
| CREATE TABLE GFI_VERSION |
| ( |
| ID integer NOT NULL, |
| VERSION VARCHAR2(50) NOT NULL, |
| CONSTRAINT GFI_VERSION_PKEY PRIMARY KEY (id) |
| ); |
| |
| INSERT INTO GFI_VERSION (ID, VERSION) VALUES ( 1, '01.00-ORACLE' ); |
| |
| -- --------------------------------------------- |
| -- TABLE REF_STATUS |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_STATUS_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_STATUS |
| ( |
| ID integer, |
| UUID RAW(16) NOT NULL, |
| STATUS VARCHAR2(50) NOT NULL, |
| CONSTRAINT REF_STATUS_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_STATUS_BIR" |
| BEFORE INSERT ON REF_STATUS |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_STATUS_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_STATUS_BIR" ENABLE; |
| |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (1, hextoraw(replace('acabc8f6-2cf3-485a-a4f8-68d178c7df45','-','')), 'neu'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (2, hextoraw(replace('f2e44dc5-d30c-4128-86bb-d3d5fc766b61','-','')), 'geplant'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (3, hextoraw(replace('23fc0254-cc3d-4371-97ad-54ef733008ae','-','')), 'angelegt'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (4, hextoraw(replace('74a4ca78-7268-11ea-bc55-0242ac130003','-','')), 'storniert'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (5, hextoraw(replace('7264e572-eae9-4cca-be05-af6b0d081247','-','')), 'qualifiziert'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (6, hextoraw(replace('9374219a-7419-4b72-899d-cd0576d85cdb','-','')), 'aktualisiert'); |
| INSERT INTO REF_STATUS (ID, UUID, STATUS) VALUES (7, hextoraw(replace('8c333345-5c3c-41ed-9de4-323045f64259','-','')), 'abgeschlossen'); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE REF_FAILURE_CLASSIFICATION |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_FAILURE_CLASS_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_FAILURE_CLASSIFICATION |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| CLASSIFICATION VARCHAR2(50) NOT NULL, |
| DESCRIPTION VARCHAR2(255) NULL, |
| CONSTRAINT REF_FAILURE_CLASS_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_FAILURE_CLASS_BIR" |
| BEFORE INSERT ON REF_FAILURE_CLASSIFICATION |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_FAILURE_CLASS_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_FAILURE_CLASS_BIR" ENABLE; |
| |
| INSERT INTO REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( hextoraw(replace('9255fb79-c57a-4448-a69c-5d57994f0c91','-','')), 'Störung', NULL ); |
| INSERT INTO REF_FAILURE_CLASSIFICATION (UUID, CLASSIFICATION, DESCRIPTION) VALUES ( hextoraw(replace('8ec1e144-5230-4d43-a3df-f62dd64bb855','-','')), 'geplante Maßnahme', NULL ); |
| |
| -- --------------------------------------------- |
| -- TABLE REF_BRANCH |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_GFI_BRANCH_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_GFI_BRANCH |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| NAME VARCHAR2(50) NOT NULL, |
| DESCRIPTION VARCHAR2(255), |
| COLOR_CODE VARCHAR2(20), |
| CONSTRAINT REF_GFI_BRANCH_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_GFI_BRANCH_BIR" |
| BEFORE INSERT ON REF_GFI_BRANCH |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_GFI_BRANCH_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_GFI_BRANCH_BIR" ENABLE; |
| |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('535b4beb-9b17-4247-bb8b-26bd01b48f9a','-','')), 'S', 'Strom', '#fc6042'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('d41f54e5-c4df-440e-b334-40e8f3a6854a','-','')), 'G', 'Gas', '#fdea64'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('62c6d361-96a0-41cc-bda1-4e58ad16f21a','-','')), 'F', 'Fernwärme', '#2cc990'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('d8d93e0e-5c8c-4ab8-9625-f820de55ee7c','-','')), 'W', 'Wasser', '#2c82c9'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('7d4907fb-cb3f-4a4f-93e9-839052e76894','-','')), 'TK', 'Telekommunikation', '#ff33cc'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('014c4a2a-3cf1-4d28-af70-4573722bceb0','-','')), 'ST', 'Sekundärtechnik', '#9933ff'); |
| INSERT INTO ref_gfi_branch(uuid, name, description, color_code) VALUES (hextoraw(replace('8fb3c764-8fb5-11ea-bc55-0242ac130003','-','')), 'OS', 'ohne Sparte', '#ffffff'); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE REF_EXPECTED_REASON |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_EXPECTED_REASON_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_EXPECTED_REASON |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| TEXT VARCHAR2(100) NOT NULL, |
| DESCRIPTION VARCHAR2(255) NULL, |
| BRANCHES VARCHAR2(100) NOT NULL, |
| CONSTRAINT REF_EXPECTED_REASON_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_EXPECTED_REASON_BIR" |
| BEFORE INSERT ON REF_EXPECTED_REASON |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_EXPECTED_REASON_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_EXPECTED_REASON_BIR" ENABLE; |
| |
| INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('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 ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07c42-6aa0-11ea-bc55-0242ac130003','-','')), 'Kabelfehler Mittelspannung', 'Dies ist die Beschreibung für: Kabelfehler Mittelspannung', 'S'); |
| INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07d50-6aa0-11ea-bc55-0242ac130003','-','')), 'Kabelfehler Niederspannung', 'Dies ist die Beschreibung für: Kabelfehler Niederspannung', 'S'); |
| INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('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 ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('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 ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a07fbc-6aa0-11ea-bc55-0242ac130003','-','')), 'Wasserrohrbruch', 'Dies ist die Beschreibung für: Wasserrohrbruch', 'W'); |
| INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('27a08160-6aa0-11ea-bc55-0242ac130003','-','')), 'Überregionale Störung', 'Dies ist die Beschreibung für: Überregionale Störung', 'S,G,W,F,TK,ST'); |
| INSERT INTO ref_expected_reason(uuid, text, description, branches) VALUES (hextoraw(replace('343bd0e9-b958-4a94-950f-52223b58f62d','-','')), 'Sonstiges', 'Dies ist die Beschreibung für: sonstiges', 'S,G,W,F,TK,ST,OS'); |
| |
| -- --------------------------------------------- |
| -- TABLE REF_RADIUS |
| -- --------------------------------------------- |
| CREATE SEQUENCE REF_RADIUS_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE REF_RADIUS |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| RADIUS integer NOT NULL, |
| CONSTRAINT REF_RADIUS_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "REF_RADIUS_BIR" |
| BEFORE INSERT ON REF_RADIUS |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT REF_RADIUS_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "REF_RADIUS_BIR" ENABLE; |
| |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('e6439b49-ce5a-486e-a437-b362ec73dc44','-','')), 0); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671000-6aa6-11ea-bc55-0242ac130003','-','')), 50); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('366712a8-6aa6-11ea-bc55-0242ac130003','-','')), 100); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('366713c0-6aa6-11ea-bc55-0242ac130003','-','')), 250); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('3667150a-6aa6-11ea-bc55-0242ac130003','-','')), 500); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671780-6aa6-11ea-bc55-0242ac130003','-','')), 1000); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('3667187a-6aa6-11ea-bc55-0242ac130003','-','')), 1500); |
| INSERT INTO REF_RADIUS(uuid, radius) VALUES (hextoraw(replace('36671ad2-6aa6-11ea-bc55-0242ac130003','-','')), 2000); |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_FAILURE_INFORMATION |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_FAILURE_INFORMATION_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_FAILURE_INFORMATION |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| VERSION_NUMBER integer NOT NULL, |
| FK_REF_FAILURE_CLASSIFICATION integer, |
| TITLE VARCHAR2(255) NULL, |
| DESCRIPTION VARCHAR2(255) NULL, |
| RESPONSIBILITY VARCHAR2(255), |
| FK_REF_STATUS_INTERN integer NOT NULL, |
| FK_REF_BRANCH integer NOT NULL, |
| VOLTAGE_LEVEL VARCHAR2(2), |
| PRESSURE_LEVEL VARCHAR2(2), |
| FAILURE_BEGIN timestamp NOT NULL, |
| FAILURE_END_PLANNED timestamp, |
| FAILURE_END_RESUPPLIED timestamp, |
| INTERNAL_REMARK VARCHAR2(1024), |
| POSTCODE VARCHAR2(30), |
| CITY VARCHAR2(255), |
| DISTRICT VARCHAR2(255), |
| STREET VARCHAR2(255), |
| HOUSENUMBER VARCHAR2(30), |
| STATION_ID VARCHAR2 (30), |
| STATION_DESCRIPTION VARCHAR2 (255), |
| STATION_COORDS VARCHAR2 (255), |
| FK_REF_RADIUS integer, |
| LONGITUDE numeric(9,6), |
| LATITUDE numeric(9,6), |
| FK_TBL_FAILINFO_CONDENSED integer, |
| CONDENSED NUMBER, |
| CONDENSED_COUNT integer, |
| OBJECT_REF_EXT_SYSTEM VARCHAR2 (255), |
| PUBLICATION_STATUS VARCHAR2 (40), |
| PUBLICATION_FREETEXT VARCHAR2 (1024), |
| FK_REF_EXPECTED_REASON integer, |
| ADDRESS_TYPE varchar(10), |
| fault_location_area varchar(20), |
| CREATE_DATE timestamp NOT NULL, |
| CREATE_USER VARCHAR2(100) NOT NULL, |
| MOD_DATE timestamp NOT NULL, |
| MOD_USER VARCHAR2(100), |
| CONSTRAINT TBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (id), |
| CONSTRAINT TBL_FAIL_INF__FCLASS_FKEY FOREIGN KEY (FK_REF_FAILURE_CLASSIFICATION) |
| REFERENCES REF_FAILURE_CLASSIFICATION (ID), |
| CONSTRAINT TBL_FAIL_INF_STATUSINT_FKEY FOREIGN KEY (FK_REF_STATUS_INTERN) |
| REFERENCES REF_STATUS (ID), |
| CONSTRAINT TBL_FAIL_INF__BRANCH_ID_FKEY FOREIGN KEY (FK_REF_BRANCH) |
| REFERENCES REF_GFI_BRANCH (ID), |
| CONSTRAINT TBL_FAIL_INF__EXPREASON_FKEY FOREIGN KEY (FK_REF_EXPECTED_REASON) |
| REFERENCES REF_EXPECTED_REASON (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_FAILURE_INFORMATION_BIR" |
| BEFORE INSERT ON TBL_FAILURE_INFORMATION |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_FAILURE_INFORMATION_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_FAILURE_INFORMATION_BIR" ENABLE; |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_DISTRIBUTION_GROUP |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_DISTRIBUTION_GROUP_ID_SEQ |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE TBL_DISTRIBUTION_GROUP |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| NAME VARCHAR2(255), |
| DISTRIBUTION_TEXT_PUBLISH VARCHAR2(2048), |
| EMAIL_SUBJECT_PUBLISH varchar(255), |
| DISTRIBUTION_TEXT_COMPLETE VARCHAR2(2048), |
| EMAIL_SUBJECT_COMPLETE varchar(255), |
| DISTRIBUTION_TEXT_UPDATE VARCHAR2(2048), |
| EMAIL_SUBJECT_UPDATE varchar(255), |
| CONSTRAINT TBL_DISTRIBUTION_GROUP_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_DISTRIBUTION_GROUP_BIR" |
| BEFORE INSERT ON TBL_DISTRIBUTION_GROUP |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_DISTRIBUTION_GROUP_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_DISTRIBUTION_GROUP_BIR" ENABLE; |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_DISTRIBUTION_GROUP_MEMBER |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_DISTR_GROUP_MEMB_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_DISTRIBUTION_GROUP_MEMBER |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| FK_TBL_DISTRIBUTION_GROUP integer NOT NULL, |
| CONTACT_ID RAW(16) NOT NULL, |
| POSTCODES varchar(1024), |
| CONSTRAINT TBL_DISTR_GROUP_MEMBER_PKEY PRIMARY KEY (id), |
| CONSTRAINT TBL_DIS_GRP_MEM__DIS_GRP_FKEY FOREIGN KEY (FK_TBL_DISTRIBUTION_GROUP) |
| REFERENCES TBL_DISTRIBUTION_GROUP (ID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_DISTR_GROUP_MEMB_BIR" |
| BEFORE INSERT ON TBL_DISTRIBUTION_GROUP_MEMBER |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_DISTR_GROUP_MEMB_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_DISTR_GROUP_MEMB_BIR" ENABLE; |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_FAILURE_INFORMATION_DISTRIBUTION_GROUP |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_FAILINFO_DISTGROUP_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE tbl_failinfo_distgroup |
| ( |
| id integer NOT NULL, |
| fk_tbl_failure_information integer NOT NULL, |
| fk_tbl_distribution_group integer NOT NULL, |
| CONSTRAINT tbl_fi_dist_group_pkey PRIMARY KEY (id), |
| CONSTRAINT tbl_fi_dist_group_fi_id_fkey FOREIGN KEY (fk_tbl_failure_information) |
| REFERENCES tbl_failure_information (id), |
| CONSTRAINT tbl_fi_dist_group__digrp_fkey FOREIGN KEY (fk_tbl_distribution_group) |
| REFERENCES tbl_distribution_group (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_FAILINFO_DISTGROUP_BIR" |
| BEFORE INSERT ON TBL_FAILINFO_DISTGROUP |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_FAILINFO_DISTGROUP_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_FAILINFO_DISTGROUP_BIR" ENABLE; |
| |
| CREATE UNIQUE INDEX idx_tbl_fi_d_group_uq ON tbl_failinfo_distgroup ( fk_tbl_failure_information, fk_tbl_distribution_group ); |
| |
| INSERT INTO tbl_distribution_group(UUID, NAME, EMAIL_SUBJECT_PUBLISH, DISTRIBUTION_TEXT_PUBLISH) |
| VALUES(hextoraw(replace('f71660e2-aee1-11ea-b3de-0242ac130004','-','')), 'Veröffentlicher', 'Bitte Anpassen - Das geplannte Ende der Störung (Sparte: \$Sparte\$) wird demnächst erreicht. Der Statuswechsel erfolgt automatisch.', |
| 'Sehr geehrte Damen und Herren, |
| |
| die im Betreff genannte Meldung ist über folgenden Link erreichbar: \$Direkter_Link_zur_Störung\$'); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_FAILURE_INFORMATION_STATION |
| -- --------------------------------------------- |
| CREATE SEQUENCE tbl_fi_station_id_seq |
| INCREMENT BY 1; |
| |
| CREATE TABLE tbl_failinfo_station |
| ( |
| id integer, |
| fk_tbl_failure_information integer NOT NULL, |
| station_station_id VARCHAR2(30) NOT NULL, |
| CONSTRAINT tbl_fi_station_pkey PRIMARY KEY (id), |
| CONSTRAINT tbl_fi_station__fi_id_fkey FOREIGN KEY (fk_tbl_failure_information) |
| REFERENCES tbl_failure_information (id) |
| ); |
| |
| |
| CREATE OR REPLACE TRIGGER "tbl_fi_station_BIR" |
| BEFORE INSERT ON tbl_failinfo_station |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT tbl_fi_station_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "tbl_fi_station_BIR" ENABLE; |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_FAILURE_INFORMATION_PUBLICATION_CHANNEL |
| -- --------------------------------------------- |
| |
| CREATE SEQUENCE tbl_fi_pub_channel_seq |
| INCREMENT BY 1; |
| |
| |
| CREATE TABLE tbl_failinfo_pub_channel |
| ( |
| id integer NOT NULL, |
| fk_tbl_failure_information integer NOT NULL, |
| publication_channel VARCHAR2(50) NOT NULL, |
| published NUMBER NOT NULL, |
| CONSTRAINT tbl_fi_pubchannel_pkey PRIMARY KEY (id), |
| CONSTRAINT tbl_fi_id_fkey FOREIGN KEY (fk_tbl_failure_information) |
| REFERENCES tbl_failure_information (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "tbl_fi_pub_channel_bir" |
| BEFORE INSERT ON TBL_FAILINFO_PUB_CHANNEL |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT tbl_fi_pub_channel_seq.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "tbl_fi_pub_channel_bir" ENABLE; |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_FAILURE_INFORMATION_REMINDER_MAIL_SENT |
| -- --------------------------------------------- |
| CREATE SEQUENCE tbl_failinfo_reminsent_id_seq |
| INCREMENT BY 1; |
| |
| CREATE TABLE tbl_failinfo_remind_mail_sent |
| ( |
| id integer NOT NULL, |
| fk_tbl_failure_information integer NOT NULL, |
| mail_sent NUMBER NOT NULL, |
| date_mail_sent timestamp, |
| CONSTRAINT tbl_fi_remmail_sent_pkey PRIMARY KEY (id), |
| CONSTRAINT tbl_fi_remmail_sent__fi_fkey FOREIGN KEY (fk_tbl_failure_information) |
| REFERENCES tbl_failure_information (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "tbl_failinfo_reminsent_BIR" |
| BEFORE INSERT ON tbl_failinfo_remind_mail_sent |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT tbl_failinfo_reminsent_id_seq.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "tbl_failinfo_reminsent_BIR" ENABLE; |
| |
| create unique index idx_ri_reminder_sent_uq on tbl_failinfo_remind_mail_sent ( fk_tbl_failure_information ); |
| |
| |
| -- ---------------------------------------------------------------------------- |
| -- ---------------------------------------------------------------------------- |
| -- HISTORY-TABLES |
| -- ---------------------------------------------------------------------------- |
| -- ---------------------------------------------------------------------------- |
| |
| |
| |
| CREATE SEQUENCE HTBL_FAILUREINFORMATION_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE HTBL_FAILURE_INFORMATION |
| ( |
| HID integer NOT NULL, |
| HACTION integer NOT NULL, |
| HDATE timestamp NOT NULL, |
| HUSER VARCHAR2(100), |
| |
| ID integer, |
| UUID RAW(16), |
| VERSION_NUMBER integer, |
| FK_REF_FAILURE_CLASSIFICATION integer, |
| TITLE VARCHAR2(255) NULL, |
| DESCRIPTION VARCHAR2(255) NULL, |
| RESPONSIBILITY VARCHAR2 (255), |
| FK_REF_STATUS_INTERN integer, |
| FK_REF_BRANCH integer, |
| VOLTAGE_LEVEL VARCHAR2 (2), |
| PRESSURE_LEVEL VARCHAR2 (2), |
| FAILURE_BEGIN timestamp, |
| FAILURE_END_PLANNED timestamp, |
| FAILURE_END_RESUPPLIED timestamp, |
| INTERNAL_REMARK VARCHAR2(1024), |
| POSTCODE VARCHAR2 (30), |
| CITY VARCHAR2 (255), |
| DISTRICT VARCHAR2 (255), |
| STREET VARCHAR2 (255), |
| HOUSENUMBER VARCHAR2 (30), |
| STATION_ID VARCHAR2 (30), |
| STATION_DESCRIPTION VARCHAR2 (255), |
| STATION_COORDS VARCHAR2 (255), |
| FK_REF_RADIUS integer, |
| LONGITUDE numeric(9,6), |
| LATITUDE numeric(9,6), |
| FK_TBL_FAILINFO_CONDENSED integer, |
| CONDENSED NUMBER, |
| CONDENSED_COUNT integer, |
| OBJECT_REF_EXT_SYSTEM VARCHAR2 (255), |
| PUBLICATION_STATUS VARCHAR2 (40), |
| PUBLICATION_FREETEXT VARCHAR2 (1024), |
| FK_REF_EXPECTED_REASON integer, |
| ADDRESS_TYPE varchar(10), |
| fault_location_area varchar(20), |
| CREATE_USER VARCHAR2(100), |
| CREATE_DATE timestamp, |
| MOD_USER VARCHAR2(100), |
| MOD_DATE timestamp, |
| CONSTRAINT HTBL_FAILURE_INFORMATION_PKEY PRIMARY KEY (HID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "HTBL_FAILUREINFORMATION_BIR" |
| BEFORE INSERT ON HTBL_FAILURE_INFORMATION |
| FOR EACH ROW |
| WHEN (NEW."HID" IS NULL) BEGIN |
| SELECT HTBL_FAILUREINFORMATION_ID_SEQ.NEXTVAL |
| INTO :NEW."HID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "HTBL_FAILUREINFORMATION_BIR" ENABLE; |
| |
| -- ---------------------------------------------------------------------------- |
| -- HTBL_FAILURE_INFORMATION_STATION |
| -- ---------------------------------------------------------------------------- |
| |
| |
| CREATE SEQUENCE HTBL_FAILINFO_STATION_ID_SEQ |
| INCREMENT BY 1; |
| CREATE TABLE HTBL_FAILINFO_STATION |
| ( |
| HID integer NOT NULL, |
| HDATE timestamp NOT NULL, |
| |
| FK_TBL_FAILURE_INFORMATION integer, |
| VERSION_NUMBER integer, |
| STATION_STATION_ID VARCHAR2 (30), |
| CONSTRAINT HTBL_FAILINFO_STATION_PKEY PRIMARY KEY (HID) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "HTBL_FAILINFO_STATION_BIR" |
| BEFORE INSERT ON HTBL_FAILINFO_STATION |
| FOR EACH ROW |
| WHEN (NEW."HID" IS NULL) BEGIN |
| SELECT HTBL_FAILINFO_STATION_ID_SEQ.NEXTVAL |
| INTO :NEW."HID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "HTBL_FAILINFO_STATION_BIR" ENABLE; |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_ADDRESS |
| -- --------------------------------------------- |
| CREATE SEQUENCE TBL_GFI_ADDRESS_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_GFI_ADDRESS |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| SDOX1 numeric(9,2) NOT NULL, |
| SDOY1 numeric(10,2) NOT NULL, |
| G3EFID numeric, |
| POSTCODE VARCHAR2(30), |
| COMMUNITY VARCHAR2(255), |
| DISTRICT VARCHAR2(255), |
| STREET VARCHAR2(255), |
| HOUSENUMBER VARCHAR2(30), |
| WATER_CONNECTION NUMBER, |
| WATER_GROUP VARCHAR2(255), |
| GAS_CONNECTION NUMBER, |
| GAS_GROUP VARCHAR2(255), |
| POWER_CONNECTION NUMBER, |
| DISTRICTHEATING_CONNECTION NUMBER, |
| TELECOMM_CONNECTION NUMBER, |
| STATION_ID VARCHAR2(30), |
| LONGITUDE numeric(9,6), |
| LATITUDE numeric(9,6), |
| CONSTRAINT TBL_GFI_ADDRESS_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_GFI_ADDRESS_BIR" |
| BEFORE INSERT ON TBL_GFI_ADDRESS |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_GFI_ADDRESS_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_GFI_ADDRESS_BIR" ENABLE; |
| |
| CREATE INDEX idx_tbl_gfiaddress_g3efid ON TBL_GFI_ADDRESS ( G3EFID ); |
| CREATE INDEX idx_tbl_gfiaddress_postcode ON TBL_GFI_ADDRESS ( POSTCODE ); |
| CREATE INDEX idx_tbl_gfiaddress_community ON TBL_GFI_ADDRESS ( COMMUNITY ); |
| CREATE INDEX idx_tbl_gfiaddress_district ON TBL_GFI_ADDRESS ( DISTRICT ); |
| CREATE INDEX idx_tbl_gfiaddress_street ON TBL_GFI_ADDRESS ( STREET ); |
| CREATE INDEX idx_tbl_gfiaddress_station_id ON TBL_GFI_ADDRESS ( STATION_ID ); |
| |
| |
| -- --------------------------------------------- |
| -- TABLE TBL_STATION |
| -- --------------------------------------------- |
| |
| CREATE SEQUENCE TBL_STATION_ID_SEQ |
| INCREMENT BY 1; |
| |
| CREATE TABLE TBL_STATION |
| ( |
| ID integer NOT NULL, |
| UUID RAW(16) NOT NULL, |
| SDOX1 numeric(9,2) NOT NULL, |
| SDOY1 numeric(10,2) NOT NULL, |
| G3EFID numeric, |
| STATION_ID VARCHAR2(30), |
| STATION_NAME VARCHAR2(255), |
| LONGITUDE numeric(9,6), |
| LATITUDE numeric(9,6), |
| CONSTRAINT TBL_STATION_PKEY PRIMARY KEY (id) |
| ); |
| |
| CREATE OR REPLACE TRIGGER "TBL_STATION_BIR" |
| BEFORE INSERT ON TBL_STATION |
| FOR EACH ROW |
| WHEN (NEW."ID" IS NULL) BEGIN |
| SELECT TBL_STATION_ID_SEQ.NEXTVAL |
| INTO :NEW."ID" |
| FROM DUAL; |
| END; |
| / |
| ALTER TRIGGER "TBL_STATION_BIR" ENABLE; |
| |
| CREATE INDEX idx_tbl_station_g3efid ON TBL_STATION ( G3EFID ); |
| CREATE INDEX idx_tbl_station_station_id ON TBL_STATION ( STATION_ID ); |
| CREATE INDEX idx_tbl_station_uuid ON TBL_STATION ( UUID ); |
| |
| |
| -- ---------------------------------------------------------------------------- |
| -- ---------------------------------------------------------------------------- |
| -- TRIGGER |
| -- ---------------------------------------------------------------------------- |
| -- ---------------------------------------------------------------------------- |
| CREATE OR REPLACE TRIGGER TBL_FAIINFO_INSERT_TRG BEFORE |
| INSERT ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue NEW AS newValue |
| FOR EACH ROW |
| 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_FAILINFO_CONDENSED, |
| CONDENSED, |
| CONDENSED_COUNT, |
| OBJECT_REF_EXT_SYSTEM, |
| PUBLICATION_STATUS, |
| PUBLICATION_FREETEXT, |
| FK_REF_EXPECTED_REASON, |
| ADDRESS_TYPE, |
| FAULT_LOCATION_AREA, |
| CREATE_DATE, |
| CREATE_USER, |
| MOD_DATE, |
| MOD_USER, |
| HACTION, |
| HDATE, |
| HUSER |
| ) |
| VALUES ( |
| :newValue.ID, |
| :newValue.UUID, |
| :newValue.VERSION_NUMBER, |
| :newValue.FK_REF_FAILURE_CLASSIFICATION, |
| :newValue.TITLE, |
| :newValue."DESCRIPTION", |
| :newValue.RESPONSIBILITY, |
| :newValue.FK_REF_STATUS_INTERN, |
| :newValue.FK_REF_BRANCH, |
| :newValue.VOLTAGE_LEVEL, |
| :newValue.PRESSURE_LEVEL, |
| :newValue.FAILURE_BEGIN, |
| :newValue.FAILURE_END_PLANNED, |
| :newValue.FAILURE_END_RESUPPLIED, |
| :newValue.INTERNAL_REMARK, |
| :newValue.POSTCODE, |
| :newValue.CITY, |
| :newValue.DISTRICT, |
| :newValue.STREET, |
| :newValue.HOUSENUMBER, |
| :newValue.STATION_ID, |
| :newValue.STATION_DESCRIPTION, |
| :newValue.STATION_COORDS, |
| :newValue.FK_REF_RADIUS, |
| :newValue.LONGITUDE, |
| :newValue.LATITUDE, |
| :newValue.FK_TBL_FAILINFO_CONDENSED, |
| :newValue.CONDENSED, |
| :newValue.CONDENSED_COUNT, |
| :newValue.OBJECT_REF_EXT_SYSTEM, |
| :newValue.PUBLICATION_STATUS, |
| :newValue.PUBLICATION_FREETEXT, |
| :newValue.FK_REF_EXPECTED_REASON, |
| :newValue.ADDRESS_TYPE, |
| :newValue.FAULT_LOCATION_AREA, |
| :newValue.CREATE_DATE, |
| :newValue.CREATE_USER , |
| :newValue.MOD_DATE, |
| :newValue.MOD_USER, |
| 1, |
| CURRENT_TIMESTAMP, |
| :newValue.CREATE_USER ); |
| |
| END; |
| / |
| |
| |
| CREATE OR REPLACE TRIGGER TBL_FAIINFO_UPDATE_TRG BEFORE |
| UPDATE ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue NEW AS newValue |
| FOR EACH ROW |
| 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_FAILINFO_CONDENSED, |
| CONDENSED, |
| CONDENSED_COUNT, |
| OBJECT_REF_EXT_SYSTEM, |
| PUBLICATION_STATUS, |
| PUBLICATION_FREETEXT, |
| FK_REF_EXPECTED_REASON, |
| ADDRESS_TYPE, |
| FAULT_LOCATION_AREA, |
| CREATE_DATE, |
| CREATE_USER, |
| MOD_DATE, |
| MOD_USER, |
| HACTION, |
| HDATE, |
| HUSER |
| ) |
| VALUES ( |
| :newValue.ID, |
| :newValue.UUID, |
| :newValue.VERSION_NUMBER, |
| :newValue.FK_REF_FAILURE_CLASSIFICATION, |
| :newValue.TITLE, |
| :newValue."DESCRIPTION", |
| :newValue.RESPONSIBILITY, |
| :newValue.FK_REF_STATUS_INTERN, |
| :newValue.FK_REF_BRANCH, |
| :newValue.VOLTAGE_LEVEL, |
| :newValue.PRESSURE_LEVEL, |
| :newValue.FAILURE_BEGIN, |
| :newValue.FAILURE_END_PLANNED, |
| :newValue.FAILURE_END_RESUPPLIED, |
| :newValue.INTERNAL_REMARK, |
| :newValue.POSTCODE, |
| :newValue.CITY, |
| :newValue.DISTRICT, |
| :newValue.STREET, |
| :newValue.HOUSENUMBER, |
| :newValue.STATION_ID, |
| :newValue.STATION_DESCRIPTION, |
| :newValue.STATION_COORDS, |
| :newValue.FK_REF_RADIUS, |
| :newValue.LONGITUDE, |
| :newValue.LATITUDE, |
| :newValue.FK_TBL_FAILINFO_CONDENSED, |
| :newValue.CONDENSED, |
| :newValue.CONDENSED_COUNT, |
| :newValue.OBJECT_REF_EXT_SYSTEM, |
| :newValue.PUBLICATION_STATUS, |
| :newValue.PUBLICATION_FREETEXT, |
| :newValue.FK_REF_EXPECTED_REASON, |
| :newValue.ADDRESS_TYPE, |
| :newValue.FAULT_LOCATION_AREA, |
| :newValue.CREATE_DATE, |
| :newValue.CREATE_USER , |
| :newValue.MOD_DATE, |
| :newValue.MOD_USER, |
| 2, |
| CURRENT_TIMESTAMP, |
| :newValue.CREATE_USER ); |
| |
| END; |
| / |
| |
| |
| |
| CREATE OR REPLACE TRIGGER TBL_FAIINFO_DELETE_TRG |
| BEFORE DELETE ON TBL_FAILURE_INFORMATION REFERENCING OLD as oldValue |
| FOR EACH ROW |
| 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_FAILINFO_CONDENSED, |
| CONDENSED, |
| CONDENSED_COUNT, |
| OBJECT_REF_EXT_SYSTEM, |
| PUBLICATION_STATUS, |
| PUBLICATION_FREETEXT, |
| FK_REF_EXPECTED_REASON, |
| ADDRESS_TYPE, |
| FAULT_LOCATION_AREA, |
| CREATE_DATE, |
| CREATE_USER, |
| MOD_DATE, |
| MOD_USER, |
| HACTION, |
| HDATE, |
| HUSER |
| ) |
| VALUES ( |
| :oldValue.ID, |
| :oldValue.UUID, |
| :oldValue.VERSION_NUMBER, |
| :oldValue.FK_REF_FAILURE_CLASSIFICATION, |
| :oldValue.TITLE, |
| :oldValue."DESCRIPTION", |
| :oldValue.RESPONSIBILITY, |
| :oldValue.FK_REF_STATUS_INTERN, |
| :oldValue.FK_REF_BRANCH, |
| :oldValue.VOLTAGE_LEVEL, |
| :oldValue.PRESSURE_LEVEL, |
| :oldValue.FAILURE_BEGIN, |
| :oldValue.FAILURE_END_PLANNED, |
| :oldValue.FAILURE_END_RESUPPLIED, |
| :oldValue.INTERNAL_REMARK, |
| :oldValue.POSTCODE, |
| :oldValue.CITY, |
| :oldValue.DISTRICT, |
| :oldValue.STREET, |
| :oldValue.HOUSENUMBER, |
| :oldValue.STATION_ID, |
| :oldValue.STATION_DESCRIPTION, |
| :oldValue.STATION_COORDS, |
| :oldValue.FK_REF_RADIUS, |
| :oldValue.LONGITUDE, |
| :oldValue.LATITUDE, |
| :oldValue.FK_TBL_FAILINFO_CONDENSED, |
| :oldValue.CONDENSED, |
| :oldValue.CONDENSED_COUNT, |
| :oldValue.OBJECT_REF_EXT_SYSTEM, |
| :oldValue.PUBLICATION_STATUS, |
| :oldValue.PUBLICATION_FREETEXT, |
| :oldValue.FK_REF_EXPECTED_REASON, |
| :oldValue.ADDRESS_TYPE, |
| :oldValue.FAULT_LOCATION_AREA, |
| :oldValue.CREATE_DATE, |
| :oldValue.CREATE_USER , |
| :oldValue.MOD_DATE, |
| :oldValue.MOD_USER, |
| 3, |
| CURRENT_TIMESTAMP, |
| :oldValue.CREATE_USER ); |
| |
| END; |
| / |
| |
| |
| |
| |
| -- ---------------------------------------------------------------- |
| -- Insert initial values Distribution group |
| -- |
| -- TODO |
| -- ---------------------------------------------------------------- |
| |
| UPDATE VERSION SET VERSION = '1.0.0_ORACLE' WHERE ID = 1; |
| |
| |
