db/create_BTB_DB_0.1.2_Sprint4_OK145_ALTER_TABLE.sql

/************************************************************************************
 * Task: Add Foreign Key: tbl_notification references to ref_grid_territory,        *         
 *       ref_grid_territory is self-referencing to  obtain tree structure           *
 ************************************************************************************/


/***** 1. Add Foreign key to tbl_notification, referencing ref_grid_territory *****/

/* Add new column to tbl_notification */
ALTER TABLE public.tbl_notification
  ADD COLUMN fk_ref_grid_territory integer;       

/* Add constraint */
ALTER TABLE public.tbl_notification
  ADD CONSTRAINT fk_notification_fk_grid_territory FOREIGN KEY (fk_ref_grid_territory) REFERENCES public.ref_grid_territory (id)
  ON DELETE CASCADE;

/* Create index */
CREATE INDEX fki_notification_fk_grid_territory
  ON public.tbl_notification(fk_ref_grid_territory);
  
/* Initialize column fk_ref_grid_territory with an already existing value, column will be set to NOT NULL in next step */
UPDATE tbl_notification SET fk_ref_grid_territory = 1;

/* Set column fk_ref_grid_territory NOT NULL */
ALTER TABLE public.tbl_notification 
  ALTER COLUMN fk_ref_grid_territory SET NOT NULL;
 
 
/***** 2. Foreign key ref_master (self-referencing) *****/

/* Add new column to ref_grid_territory */
ALTER TABLE public.ref_grid_territory
  ADD COLUMN fk_ref_master integer;       

/* Add constraint */
ALTER TABLE public.ref_grid_territory
  ADD CONSTRAINT fk_ref_grid_territory_self FOREIGN KEY (fk_ref_master) REFERENCES public.ref_grid_territory (id)
   ON DELETE CASCADE;

/* Create index */
CREATE INDEX fki_fk_grid_territory_self
  ON public.ref_grid_territory(fk_ref_master);
  
/* Initialize column fk_ref_master with the value of column "id", fk_ref_master will be set to NOT NULL in next step */
UPDATE public.ref_grid_territory SET fk_ref_master = id;

/* Set column fk_ref_master NOT NULL */
ALTER TABLE public.ref_grid_territory 
  ALTER COLUMN fk_ref_master SET NOT NULL;


/***** 3. Change the view according to the new foreign key *****/

-- 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,
    s.fk_ref_grid_territory
   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,
            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;