Added ora create script
diff --git a/db/oracle/create_db.sql b/db/oracle/create_db.sql
new file mode 100644
index 0000000..c8932bd
--- /dev/null
+++ b/db/oracle/create_db.sql
@@ -0,0 +1,361 @@
+DROP SEQUENCE htbl_responsibility_id_seq;
+DROP SEQUENCE ref_branch_id_seq;
+DROP SEQUENCE ref_grid_territory_id_seq;
+DROP SEQUENCE ref_notification_status_id_seq;
+DROP SEQUENCE tbl_notification_id_seq;
+DROP SEQUENCE tbl_notification_incident_seq;
+DROP SEQUENCE tbl_responsibility_id_seq;
+
+DROP TABLE TBL_RESPONSIBILITY;
+DROP TABLE HTBL_RESPONSIBILITY;
+DROP TABLE TBL_NOTIFICATION;
+DROP TABLE REF_BRANCH;
+DROP TABLE REF_GRID_TERRITORY;
+DROP TABLE REF_NOTIFICATION_STATUS;
+DROP TABLE REF_VERSION;
+
+
+CREATE SEQUENCE htbl_responsibility_id_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE ref_branch_id_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE ref_grid_territory_id_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE ref_notification_status_id_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE tbl_notification_id_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE tbl_notification_incident_seq
+ INCREMENT BY 1;
+
+CREATE SEQUENCE tbl_responsibility_id_seq
+ INCREMENT BY 1;
+
+
+CREATE TABLE REF_BRANCH
+(
+ "id" NUMBER NOT NULL,
+ "name" VARCHAR2(50) NOT NULL,
+ "description" VARCHAR2(255),
+ CONSTRAINT ref_branch_pkey PRIMARY KEY ("id")
+);
+
+
+CREATE OR REPLACE TRIGGER "REF_BRANCH_BIR"
+BEFORE INSERT ON REF_BRANCH
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT REF_BRANCH_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "REF_BRANCH_BIR" ENABLE;
+
+-- Table: public.ref_grid_territory
+CREATE TABLE REF_GRID_TERRITORY
+(
+ "id" integer NOT NULL,
+ "name" varchar2(50) NOT NULL,
+ "description" varchar2(255),
+ "fk_ref_master" integer NOT NULL,
+ CONSTRAINT ref_grid_territory_pkey PRIMARY KEY ("id"),
+ CONSTRAINT fk_ref_grid_territory_self FOREIGN KEY ("fk_ref_master")
+ REFERENCES ref_grid_territory ("id")
+ ON DELETE CASCADE
+);
+
+CREATE OR REPLACE TRIGGER "REF_GRID_TERRITORY_BIR"
+BEFORE INSERT ON "REF_GRID_TERRITORY"
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT REF_GRID_TERRITORY_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "REF_GRID_TERRITORY_BIR" ENABLE;
+
+-- Index: public.fki_fk_grid_territory_self
+CREATE INDEX fki_fk_grid_territory_self
+ ON "REF_GRID_TERRITORY"
+ ("fk_ref_master");
+
+-- Index: ref_grid_terr_descr_unique
+CREATE UNIQUE INDEX ref_grid_terr_descr_unique
+ ON "REF_GRID_TERRITORY" ("description");
+
+
+CREATE TABLE REF_NOTIFICATION_STATUS
+(
+ "id" integer NOT NULL,
+ "name" varchar2(50) NOT NULL,
+ CONSTRAINT ref_notification_status_pkey PRIMARY KEY ("id")
+);
+
+
+CREATE OR REPLACE TRIGGER "REF_NOTIFICATION_STATUS_BIR"
+BEFORE INSERT ON "REF_NOTIFICATION_STATUS"
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT REF_NOTIFICATION_STATUS_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "REF_NOTIFICATION_STATUS_BIR" ENABLE;
+
+-- Table: public.ref_version
+CREATE TABLE REF_VERSION
+(
+ "id" integer NOT NULL,
+ "version" varchar2(100) NOT NULL,
+ CONSTRAINT ref_version_pkey PRIMARY KEY ("id")
+);
+
+
+-- Table: public.tbl_notification
+
+
+
+CREATE TABLE "TBL_NOTIFICATION"
+(
+ "id" integer NOT NULL,
+ "incident_id" integer,
+ "version" integer DEFAULT 0 NOT NULL,
+ "fk_ref_branch" integer,
+ "notification_text" varchar2(200) NOT NULL,
+ "free_text" varchar2(1000),
+ "free_text_extended" varchar2(1000),
+ "fk_ref_notification_status" integer NOT NULL,
+ "responsibility_forwarding" varchar2(100),
+ "reminder_date" timestamp,
+ "expected_finished_date" timestamp,
+ "responsibility_control_point" varchar2(100),
+ "begin_date" timestamp NOT NULL,
+ "finished_date" timestamp,
+ "create_user" varchar2(100) NOT NULL,
+ "create_date" timestamp NOT NULL,
+ "mod_user" varchar2(100),
+ "mod_date" timestamp,
+ "fk_ref_grid_territory" integer,
+ "admin_flag" number DEFAULT 0 NOT NULL,
+
+ CONSTRAINT tbl_notification_pkey PRIMARY KEY ("id"),
+ CONSTRAINT fk_notification_fk_branch FOREIGN KEY ("fk_ref_branch")
+ REFERENCES ref_branch ("id"),
+ CONSTRAINT fk_notif_fk_grid_terr FOREIGN KEY ("fk_ref_grid_territory")
+ REFERENCES ref_grid_territory ("id"),
+ CONSTRAINT fk_notif_fk_status FOREIGN KEY ("fk_ref_notification_status")
+ REFERENCES ref_notification_status ("id")
+);
+
+CREATE OR REPLACE TRIGGER "TBL_NOTIFICATION_BIR"
+BEFORE INSERT ON "TBL_NOTIFICATION"
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT TBL_NOTIFICATION_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "TBL_NOTIFICATION_BIR" ENABLE;
+
+
+CREATE OR REPLACE TRIGGER "TBL_NOTIF_INCIDENT_BIR"
+BEFORE INSERT ON "TBL_NOTIFICATION"
+FOR EACH ROW
+ WHEN (new."incident_id" IS NULL) BEGIN
+ SELECT TBL_NOTIFICATION_INCIDENT_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "TBL_NOTIF_INCIDENT_BIR" ENABLE;
+
+-- Index: public.create_date_idx
+
+CREATE INDEX create_date_idx
+ ON tbl_notification( "create_date" );
+
+
+-- Index: public.fki_notification_fk_branch
+CREATE INDEX fki_notification_fk_branch
+ ON tbl_notification("fk_ref_branch");
+
+
+-- Index: public.fki_notification_fk_grid_territory
+CREATE INDEX fki_notif_fk_grid_terr
+ ON tbl_notification ("fk_ref_grid_territory");
+
+
+-- Index: public.fki_notification_fk_status
+CREATE INDEX fki_notification_fk_status
+ ON tbl_notification("fk_ref_notification_status");
+
+
+-- Index: public.mod_date_idx
+CREATE INDEX mod_date_idx
+ ON tbl_notification ("mod_date");
+
+
+-- Index: public.responsibility_forwarding_idx
+CREATE INDEX responsibility_forwarding_idx
+ ON tbl_notification( "responsibility_forwarding");
+
+
+-- Index: public.tbl_notification_incident_version_unique
+CREATE UNIQUE INDEX tbl_notif_incid_version_unique
+ ON tbl_notification ("incident_id", "version");
+
+-- Table: public.tbl_responsibility
+CREATE TABLE TBL_RESPONSIBILITY
+(
+ "id" NUMBER NOT NULL,
+ "fk_ref_grid_territory" NUMBER NOT NULL,
+ "fk_ref_branch" NUMBER NOT NULL,
+ "responsible_user" VARCHAR2(100) NOT NULL,
+ "new_responsible_user" VARCHAR2(100),
+ "create_user" VARCHAR2(100) NOT NULL,
+ "create_date" timestamp NOT NULL,
+ "mod_user" VARCHAR2(100),
+ "mod_date" timestamp,
+ CONSTRAINT tbl_responsibility_pkey PRIMARY KEY ("id"),
+ CONSTRAINT fk_branch FOREIGN KEY ("fk_ref_branch")
+ REFERENCES ref_branch ("id"),
+ CONSTRAINT fk_grid_territory FOREIGN KEY ("fk_ref_grid_territory")
+ REFERENCES ref_grid_territory ("id")
+);
+
+CREATE OR REPLACE TRIGGER "TBL_RESPONSIBILITY_BIR"
+BEFORE INSERT ON "TBL_RESPONSIBILITY"
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT TBL_RESPONSIBILITY_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "TBL_RESPONSIBILITY_BIR" ENABLE;
+
+
+-- Index: public.tbl_responsibility_territory_branch_user_unique
+CREATE UNIQUE INDEX tbl_resp_terr_branch_unique
+ ON tbl_responsibility ("fk_ref_grid_territory", "fk_ref_branch");
+
+-- Table: public.htbl_responsibility
+CREATE TABLE HTBL_RESPONSIBILITY
+(
+ "id" NUMBER NOT NULL,
+ "fk_ref_grid_territory" NUMBER NOT NULL,
+ "fk_ref_branch" NUMBER NOT NULL,
+ "transfer_date" timestamp NOT NULL,
+ "transaction_id" NUMBER NOT NULL,
+ "responsible_user" VARCHAR2(100) NOT NULL,
+ "former_responsible_user" VARCHAR2(100),
+ "create_user" VARCHAR2(100) NOT NULL,
+ "create_date" timestamp NOT NULL,
+ "mod_user" VARCHAR2(100),
+ "mod_date" timestamp,
+ CONSTRAINT htbl_responsibility_pkey PRIMARY KEY ("id"),
+ CONSTRAINT htbl_resp_fk_branch FOREIGN KEY ("fk_ref_branch")
+ REFERENCES ref_branch ("id"),
+ CONSTRAINT htbl_resp_fk_grid_territory FOREIGN KEY ("fk_ref_grid_territory")
+ REFERENCES ref_grid_territory ("id")
+);
+
+CREATE OR REPLACE TRIGGER "HTBL_RESPONSIBILITY_BIR"
+BEFORE INSERT ON "HTBL_RESPONSIBILITY"
+FOR EACH ROW
+ WHEN (new."id" IS NULL) BEGIN
+ SELECT HTBL_RESPONSIBILITY_ID_SEQ.NEXTVAL
+ INTO :new."id"
+ FROM dual;
+END;
+/
+ALTER TRIGGER "HTBL_RESPONSIBILITY_BIR" ENABLE;
+
+-- Index: public.htbl_resp_trans_id_unique
+CREATE UNIQUE INDEX htbl_resp_trans_id_unique
+ ON htbl_responsibility ("fk_ref_grid_territory", "fk_ref_branch", "transaction_id");
+
+
+CREATE OR REPLACE VIEW VIEW_ACTIVE_NOTIFICATION AS
+ SELECT s."id",
+ s."incident_id",
+ s."version",
+ s."fk_ref_branch",
+ s."notification_text",
+ s."free_text",
+ s."free_text_extended",
+ s."fk_ref_notification_status",
+ s."responsibility_forwarding",
+ s."reminder_date",
+ s."expected_finished_date",
+ s."responsibility_control_point",
+ s."begin_date",
+ s."finished_date",
+ s."create_user",
+ s."create_date",
+ s."mod_user",
+ s."mod_date",
+ s."fk_ref_grid_territory",
+ s."admin_flag"
+ FROM ( SELECT tbl_notification."id",
+ tbl_notification."incident_id",
+ tbl_notification."version",
+ tbl_notification."fk_ref_branch",
+ tbl_notification."notification_text",
+ tbl_notification."free_text",
+ tbl_notification."free_text_extended",
+ tbl_notification."fk_ref_notification_status",
+ tbl_notification."responsibility_forwarding",
+ tbl_notification."reminder_date",
+ tbl_notification."expected_finished_date",
+ tbl_notification."responsibility_control_point",
+ tbl_notification."begin_date",
+ tbl_notification."finished_date",
+ tbl_notification."create_user",
+ tbl_notification."create_date",
+ tbl_notification."mod_user",
+ tbl_notification."mod_date",
+ tbl_notification."fk_ref_grid_territory",
+ tbl_notification."admin_flag",
+ rank() OVER (PARTITION BY tbl_notification."incident_id" ORDER BY "TBL_NOTIFICATION"."version" DESC) AS rank
+ FROM tbl_notification) s
+ WHERE s.rank = 1;
+
+
+INSERT INTO "REF_NOTIFICATION_STATUS" ( "id", "name" ) VALUES ( 1, 'offen' );
+INSERT INTO "REF_NOTIFICATION_STATUS" ( "id", "name" ) VALUES ( 2, 'in Arbeit' );
+INSERT INTO "REF_NOTIFICATION_STATUS" ( "id", "name" ) VALUES ( 3, 'erledigt' );
+INSERT INTO "REF_NOTIFICATION_STATUS" ( "id", "name" ) VALUES ( 4, 'geschlossen' );
+
+INSERT INTO "REF_BRANCH" ("id", "name", "description" ) VALUES ( 1, 'S', 'Strom' );
+INSERT INTO "REF_BRANCH" ("id", "name", "description" ) VALUES ( 2, 'G', 'Gas' );
+INSERT INTO "REF_BRANCH" ("id", "name", "description" ) VALUES ( 3, 'FW', 'Fernwärme' );
+INSERT INTO "REF_BRANCH" ("id", "name", "description" ) VALUES ( 4, 'W', 'Wasser' );
+
+INSERT INTO "REF_GRID_TERRITORY" ("id", "name", "description", "fk_ref_master") VALUES ( 1, 'MA', 'Mannheim', 1);
+INSERT INTO "REF_GRID_TERRITORY" ("id", "name", "description", "fk_ref_master") VALUES ( 2, 'OF', 'Offenbach', 2);
+
+INSERT INTO REF_VERSION VALUES (1, '1.0.0_ORA');
+
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (1, 2, 'admin','admin', CURRENT_TIMESTAMP);
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (1, 3, 'admin','admin', CURRENT_TIMESTAMP);
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (1, 4, 'admin','admin', CURRENT_TIMESTAMP);
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (2, 1, 'admin','admin', CURRENT_TIMESTAMP);
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (2, 3, 'otto','admin', CURRENT_TIMESTAMP);
+INSERT INTO "TBL_RESPONSIBILITY" ("fk_ref_grid_territory", "fk_ref_branch", "responsible_user", "create_user", "create_date") VALUES (2, 4, 'otto','admin', CURRENT_TIMESTAMP);
+
+
+
+
+
+
+
