db/postgreSQL/UpdateTo2.0.0FromDB_1.0.0/01_migrate_from_1.0.0_to_2.0.0.sql

CREATE SEQUENCE public.REF_NOTIF_PRIORITY_ID_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 3
  CACHE 1;
ALTER TABLE public.REF_NOTIF_PRIORITY_ID_SEQ
  OWNER TO btbservice;

CREATE TABLE public.REF_NOTIFICATION_PRIORITY
(
        id integer NOT NULL DEFAULT nextval('ref_notif_priority_id_seq'::regclass),
        name character varying(50) NOT NULL,
        weighting integer NOT NULL,
        image_name character varying(50) NOT NULL,
        CONSTRAINT REF_NOTIFICATION_PRIORITY_PKEY PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);
ALTER TABLE public.REF_NOTIFICATION_PRIORITY
    OWNER TO btbservice;
GRANT ALL ON TABLE public.REF_NOTIFICATION_PRIORITY TO btbservice;


ALTER TABLE public.TBL_NOTIFICATION ADD
  fk_ref_notification_priority integer;
ALTER TABLE public.TBL_NOTIFICATION ADD
  type character varying(100);

ALTER TABLE public.TBL_NOTIFICATION
ADD CONSTRAINT FK_NOTIFICATION_FK_PRIORITY FOREIGN KEY (fk_ref_notification_priority)
         REFERENCES public.REF_NOTIFICATION_PRIORITY (id) MATCH SIMPLE
         ON UPDATE NO ACTION ON DELETE NO ACTION;

-- Index: public.fki_notification_fk_priority

-- DROP INDEX public.fki_notification_fk_priority;

CREATE INDEX FKI_NOTIFICATION_FK_PRIORITY
  ON public.TBL_NOTIFICATION
  USING btree
  (fk_ref_notification_priority);

DROP VIEW public.VIEW_ACTIVE_NOTIFICATION;
CREATE VIEW public.VIEW_ACTIVE_NOTIFICATION AS
 SELECT s.id,
    s.incident_id,
    s.version,
    s.fk_ref_branch,
    s.fk_ref_notification_priority,
    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,
    s.type
   FROM ( SELECT tbl_notification.id,
            tbl_notification.incident_id,
            tbl_notification.version,
            tbl_notification.fk_ref_branch,
            tbl_notification.fk_ref_notification_priority,
            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,
            tbl_notification.type,
            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;
GRANT ALL ON TABLE public.VIEW_ACTIVE_NOTIFICATION TO btbservice;


-- ---------------------------------------------
-- TABLE TBL_USER_SETTINGS
-- ---------------------------------------------

CREATE SEQUENCE public.tbl_user_settings_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.tbl_user_settings_id_seq
  OWNER TO btbservice;

CREATE TABLE public.tbl_user_settings
(
  id integer NOT NULL DEFAULT nextval('tbl_user_settings_id_seq'::regclass),
  username character varying(50) NOT NULL,
  setting_type character varying(50),
  value character varying(4096),
  CONSTRAINT tbl_user_settings_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_user_settings
  OWNER TO btbservice;
GRANT ALL ON TABLE public.tbl_user_settings TO btbservice;

CREATE UNIQUE INDEX tbl_user_set_unique_key ON public.tbl_user_settings (username ASC, setting_type ASC );

-- ---------------------------------------------
-- TABLE TBL_NOTIFICATION_MAILSENT
-- ---------------------------------------------
CREATE SEQUENCE public.tbl_notification_mailsent_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE public.tbl_notification_mailsent_seq
  OWNER TO btbservice;

CREATE TABLE public.tbl_notification_mailsent
(
  id integer NOT NULL DEFAULT nextval('tbl_notification_mailsent_seq'::regclass),
  incident_id integer NOT NULL,
  mail_sent boolean NOT NULL,
  date_mail_sent timestamp,
  CONSTRAINT tbl_notif_mail_sent_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.tbl_notification_mailsent
  OWNER TO btbservice;
GRANT ALL ON TABLE public.tbl_notification_mailsent TO btbservice;

create unique index idx_ri_reminder_sent_uq on public.tbl_notification_mailsent ( incident_id );

DELETE FROM REF_VERSION;
INSERT INTO REF_VERSION VALUES (1, '2.0.0_PG');


INSERT INTO REF_NOTIFICATION_PRIORITY ("id", "name", "weighting", "image_name") VALUES ( 1, 'Wichtig', 30, 'prio-important-icon-web.svg');
INSERT INTO REF_NOTIFICATION_PRIORITY ("id", "name", "weighting", "image_name") VALUES ( 2, 'Information', 20, 'prio-info-icon-web.svg');
INSERT INTO REF_NOTIFICATION_PRIORITY ("id", "name", "weighting", "image_name") VALUES ( 3, 'Normal', 10, 'prio-normal-icon-web.svg');