| -- Database: betriebstagebuch |
| |
| |
| -- DROP DATABASE betriebstagebuch; |
| |
| -- REVIEW: Why is drop commented out? A clean install is strongly encouraged |
| |
| /* |
| CREATE DATABASE betriebstagebuch |
| WITH |
| OWNER = postgres |
| ENCODING = 'UTF8' |
| LC_COLLATE = 'German_Germany.1252' |
| LC_CTYPE = 'German_Germany.1252' |
| TABLESPACE = pg_default |
| CONNECTION LIMIT = -1; |
| |
| -- Role: btbservice |
| |
| -- DROP ROLE btbservice; |
| CREATE ROLE btbservice LOGIN |
| ENCRYPTED PASSWORD 'md5014d35e4f8632560130b265acb21413f' |
| NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; |
| */ |
| |
| |
| /* |
| DROP SEQUENCE public.tbl_notification_incident_id_seq; |
| |
| DROP TABLE public.tbl_notification; |
| DROP TABLE public."ref_version"; |
| DROP TABLE public.ref_branch; |
| DROP TABLE public.ref_notification_status; |
| DROP TABLE public.tbl_settings; |
| |
| DROP VIEW public.view_active_notification; |
| */ |
| |
| -- Table: public."REF_VERSION" ------------------------------------------------- |
| CREATE TABLE public."ref_version" |
| ( |
| "id" integer NOT NULL, |
| "version" varchar(100) NOT NULL, |
| CONSTRAINT "ref_version_pkey" PRIMARY KEY ("id") |
| ) |
| WITH ( |
| OIDS = FALSE |
| ) |
| TABLESPACE pg_default; |
| |
| ALTER TABLE public."ref_version" |
| OWNER to btbservice; |
| |
| |
| |
| -- Table: public."REF_BRANCH" (Sparte) ----------------------------------------- |
| |
| CREATE TABLE public.ref_branch |
| ( |
| id SERIAL PRIMARY KEY, |
| name character varying(50) NOT NULL, |
| description character varying(255) NULL |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.ref_branch |
| OWNER TO btbservice; |
| |
| -- Table: public."REF_NOTIFICATION_STATUS" ----------------------------------------- |
| |
| CREATE TABLE public.ref_notification_status |
| ( |
| id SERIAL PRIMARY KEY, |
| name character varying(50) NOT NULL |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.ref_notification_status |
| OWNER TO btbservice; |
| |
| -- Table: public.tbl_settings -------------------------------------------------------- |
| |
| CREATE TABLE public.tbl_settings |
| ( |
| parameter_name character varying(100) COLLATE pg_catalog."default" NOT NULL, |
| parameter_value character varying(100) COLLATE pg_catalog."default" NOT NULL |
| ) |
| WITH ( |
| OIDS = FALSE |
| ) |
| TABLESPACE pg_default; |
| |
| ALTER TABLE public.tbl_settings |
| OWNER to btbservice; |
| |
| -- Table: public."TBL_NOTIFICATION" --------------------------------------------- |
| -- DROP SEQUENCE public.tbl_notification_incident_id_seq; |
| CREATE SEQUENCE public.tbl_notification_incident_id_seq; |
| |
| CREATE TABLE public.tbl_notification |
| ( |
| id SERIAL PRIMARY KEY, |
| incident_id integer, |
| version integer NOT NULL DEFAULT 0, |
| fk_ref_branch integer NOT NULL, |
| notification_text character varying(200) NOT NULL, |
| free_text character varying(1000) NULL, |
| free_text_extended character varying(1000) NULL, |
| fk_ref_notification_status integer NOT NULL, |
| responsibility_forwarding character varying(100), |
| reminder_date timestamp without time zone NULL, |
| expected_finished_date timestamp without time zone NULL, |
| responsibility_control_point character varying(100), |
| begin_date timestamp without time zone NOT NULL, |
| finished_date timestamp without time zone NULL, |
| create_user character varying(100) NOT NULL, |
| create_date timestamp without time zone NOT NULL, |
| mod_user character varying(100), |
| mod_date timestamp without time zone |
| ) |
| WITH ( |
| OIDS=FALSE |
| ); |
| ALTER TABLE public.tbl_notification |
| OWNER TO btbservice; |
| |
| CREATE UNIQUE INDEX tbl_notification_incident_version_unique |
| ON public.tbl_notification (incident_id ASC NULLS LAST, version ASC NULLS LAST); |
| |
| ALTER TABLE public.tbl_notification |
| ADD CONSTRAINT fk_notification_fk_status FOREIGN KEY (fk_ref_notification_status) REFERENCES public.ref_notification_status (id) |
| ON UPDATE NO ACTION ON DELETE NO ACTION; |
| CREATE INDEX fki_notification_fk_status |
| ON public.tbl_notification(fk_ref_notification_status); |
| |
| ALTER TABLE public.tbl_notification |
| ADD CONSTRAINT fk_notification_fk_branch FOREIGN KEY (fk_ref_branch) REFERENCES public.ref_branch (id) |
| ON UPDATE NO ACTION ON DELETE NO ACTION; |
| CREATE INDEX fki_notification_fk_branch |
| ON public.tbl_notification(fk_ref_branch); |
| |
| |
| -------------------------------------------------------------------------------- |
| -- Triggers |
| -------------------------------------------------------------------------------- |
| |
| -- Trigger Function for setting the incident_id of a notification. |
| -- This function is called on every insert on tbl_notification. |
| -- Only for newly created notification we copy the id into the incident_id. |
| |
| -- drop trigger tbl_notification_incident_trg on tbl_notification_test |
| CREATE or REPLACE FUNCTION tbl_notification_incident_trg() RETURNS trigger AS $tbl_notification_incident_trg$ |
| BEGIN |
| IF NEW.incident_id IS NULL THEN |
| NEW.incident_id := NEW.id; |
| END IF; |
| RETURN NEW; |
| END; |
| $tbl_notification_incident_trg$ LANGUAGE plpgsql; |
| CREATE TRIGGER tbl_notification_incident_trg BEFORE INSERT ON tbl_notification |
| FOR EACH ROW EXECUTE PROCEDURE tbl_notification_incident_trg(); |
| |
| -------------------------------------------------------------------------------- |
| -- Views |
| -------------------------------------------------------------------------------- |
| |
| -- View: public.view_active_notification |
| -- DROP VIEW public.view_active_notification; |
| |
| -- Erstellt eine View, die die jeweils höchste Version einer Notification aus tbl_notification enthält. |
| CREATE OR REPLACE VIEW public.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 |
| 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, |
| rank() OVER (PARTITION BY tbl_notification.incident_id ORDER BY tbl_notification.version DESC) AS rank |
| FROM tbl_notification) s |
| WHERE s.rank = 1; |
| |
| ALTER TABLE public.view_active_notification |
| OWNER TO btbservice; |
| |
| |
| |
| |
| -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| -- Fill DB Script |
| INSERT INTO public."ref_version" ("id", "version") VALUES ( 1, '0.1.1_Snapshot'); |
| |
| INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 1, 'offen' ); |
| INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 2, 'in Arbeit' ); |
| INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 3, 'erledigt' ); |
| INSERT INTO public."ref_notification_status" ( id, name ) VALUES ( 4, 'geschlossen' ); |
| SELECT setval('ref_notification_status_id_seq', 5, true); |
| |
| INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 1, 'S', 'Strom' ); |
| INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 2, 'G', 'Gas' ); |
| INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 3, 'FW', 'Fernwärme' ); |
| INSERT INTO public."ref_branch" (id, name, description ) VALUES ( 4, 'W', 'Wasser' ); |
| SELECT setval('ref_branch_id_seq', 5, true); |
| |
| INSERT INTO public."tbl_settings" (parameter_name, parameter_value ) VALUES ( 'notification_list_closed_max_age', '6'); |
| |
| -- REVIEW: Why are the Sprint comments necessary? |
| |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -- ENDE Sprint 1 |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -- Anfang Sprint 2 30.05.2017 |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| UPDATE public."ref_branch" SET name='F' WHERE id=3; |
| |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -- Anfang Sprint 3 12.06.2017 |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| |
| -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| -- Drop Tables |
| DROP TABLE public.tbl_responsibility; |
| DROP TABLE public.ref_grid_territory; |
| |
| -- Table: public."ref_grid_territory" (Netzgebiete) ------------------------------------------------- |
| CREATE TABLE public."ref_grid_territory" |
| ( |
| id SERIAL PRIMARY KEY, |
| name character varying(50) NOT NULL, |
| description character varying(255) NULL |
| ); |
| |
| CREATE UNIQUE INDEX ref_grid_territory_description_unique |
| ON public.ref_grid_territory (description ASC); |
| |
| ALTER TABLE public.ref_grid_territory |
| OWNER TO btbservice; |
| |
| -- Table: public."tbl_responsibility" (Verantwortlichkeiten) ------------------------------------------------- |
| CREATE TABLE public."tbl_responsibility" |
| ( |
| id SERIAL PRIMARY KEY, |
| fk_ref_grid_territory integer NOT NULL, |
| fk_ref_branch integer NOT NULL, |
| responsible_user character varying(100) NOT NULL, |
| new_responsible_user character varying(100), |
| create_user character varying(100) NOT NULL, |
| create_date timestamp without time zone NOT NULL, |
| mod_user character varying(100), |
| mod_date timestamp without time zone |
| ); |
| |
| CREATE UNIQUE INDEX tbl_responsibility_territory_branch_user_unique |
| ON public.tbl_responsibility (fk_ref_grid_territory ASC, fk_ref_branch ASC); |
| |
| ALTER TABLE public.tbl_responsibility |
| ADD CONSTRAINT fk_grid_territory FOREIGN KEY (fk_ref_grid_territory) REFERENCES public.ref_grid_territory (id) |
| ON UPDATE NO ACTION ON DELETE NO ACTION; |
| |
| ALTER TABLE public.tbl_responsibility |
| ADD CONSTRAINT fk_branch FOREIGN KEY (fk_ref_branch) REFERENCES public.ref_branch (id) |
| ON UPDATE NO ACTION ON DELETE NO ACTION; |
| |
| |
| ALTER TABLE public.tbl_responsibility |
| OWNER TO btbservice; |
| |
| --REVIEW: Should be refactored to test data. We should have one script with DDL and multiple, content oriented DML scripts |
| |
| |
| -- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| -- Fill DB Script |
| |
| -- Table: public."ref_grid_territory" (Netzgebiete) |
| INSERT INTO public."ref_grid_territory" (id, name, description) VALUES ( 1, 'MA', 'Mannheim'); |
| INSERT INTO public."ref_grid_territory" (id, name, description) VALUES ( 2, 'OF', 'Offenbach'); |
| |
| |
| -- Table: public."tbl_responsibility" (Verantwortlichkeiten) ------------------------------------------------- |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 1, 1, 1, 'admin','admin',now()); |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 2, 1, 2, 'admin','admin',now()); |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 3, 1, 3, 'admin','admin',now()); |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 4, 2, 2, 'admin','admin',now()); |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 5, 2, 3, 'otto','admin',now()); |
| INSERT INTO public."tbl_responsibility" (id, fk_ref_grid_territory, fk_ref_branch, responsible_user, create_user, create_date) VALUES ( 6, 1, 4, 'otto','admin',now()); |
| |
| |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -- Ende Sprint 3 21.06.2017 |
| --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ |
| -->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> |