Skip to content
Snippets Groups Projects
Select Git revision
  • master default protected
  • develop protected
  • gitlab-build-pipeline
  • v1.0.0
4 results
Blame Permalink
Code owners
Assign users and groups as approvers for specific file changes. Learn more.
createDB-Oracle.sql 25.33 KiB 
-----------------------------------------------------------------------------------
-- *******************************************************************************
-- * 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
-- *******************************************************************************
-----------------------------------------------------------------------------------

-- V1_0_0__InitialSetup

-- ATTACHMENT

CREATE SEQUENCE tbl_attachment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
   

CREATE TABLE tbl_attachment (
    id NUMBER(10) DEFAULT tbl_attachment_id_seq.nextval NOT NULL,
    file_name character varying(255),
    file_type character varying(255),
    statement_id NUMBER(19),
    attachment_lob_id NUMBER(10),
    len NUMBER(19),
    ts timestamp
);


ALTER TABLE tbl_attachment ADD (
  CONSTRAINT tbl_attachment_pk PRIMARY KEY (id));
 
CREATE SEQUENCE tbl_attachment_lob_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
   

CREATE TABLE tbl_attachment_lob (
    id NUMBER(10) DEFAULT tbl_attachment_lob_id_seq.nextval NOT NULL,
    value BLOB NOT NULL
);


ALTER TABLE tbl_attachment_lob ADD (
  CONSTRAINT tbl_attachment_lob_pk PRIMARY KEY (id));

-- TAG

 
CREATE TABLE tbl_tag (
    id character varying(255) NOT NULL,
    name character varying(255) NOT NULL
);

ALTER TABLE tbl_tag ADD (
  CONSTRAINT tbl_tag_pk PRIMARY KEY (ID));

 
-- ATTACHMENT_2_TAG


CREATE TABLE tbl_attachment2tag (
    tag_id character varying(255) NOT NULL,
    attachment_id NUMBER(19) NOT NULL
);

ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_pkey PRIMARY KEY (attachment_id, tag_id);

-- COMMENT

CREATE SEQUENCE tbl_comment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_comment (
    id NUMBER(10) DEFAULT tbl_comment_id_seq.nextval NOT NULL,
    disabled NUMBER(1),
    text CLOB NOT NULL,
    ts timestamp,
    user_id NUMBER(10),
    workflow_id NUMBER(10)
);

ALTER TABLE tbl_comment ADD (
  CONSTRAINT tbl_comment_pk PRIMARY KEY (ID));

-- DEPARTMENT

 CREATE SEQUENCE tbl_department_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_department (
    id NUMBER(10) DEFAULT tbl_department_id_seq.nextval NOT NULL,
    departmentgroup character varying(255),
    name character varying(255)
);

ALTER TABLE tbl_department ADD (
  CONSTRAINT tbl_department_pk PRIMARY KEY (id));

ALTER TABLE tbl_department
    ADD CONSTRAINT tbl_department_name_departmentgroup_u UNIQUE (name, departmentgroup);

-- DEPARTMENT_STRUCTURE

 CREATE SEQUENCE tbl_departmentstructure_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


 CREATE TABLE tbl_departmentstructure (
    id NUMBER(10) DEFAULT tbl_departmentstructure_id_seq.nextval NOT NULL,
    definition CLOB NOT NULL,
    version NUMBER(10) NOT NULL
);

ALTER TABLE tbl_departmentstructure ADD (
  CONSTRAINT tbl_departmentstructure_pk PRIMARY KEY (id));
 
-- REQ_DEPARTMENT

 CREATE SEQUENCE tbl_reqdepartment_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_reqdepartment (
    id NUMBER(10) DEFAULT tbl_reqdepartment_id_seq.nextval NOT NULL,
    contributed NUMBER(1),
    optional NUMBER(1),
    department_id NUMBER(10),
    workflowdata_id NUMBER(10)
);

ALTER TABLE tbl_reqdepartment ADD (
  CONSTRAINT tbl_reqdepartment_pk PRIMARY KEY (id));

-- STATEMENT


CREATE SEQUENCE tbl_statement_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_statement (
    id NUMBER(10) DEFAULT tbl_statement_id_seq.nextval NOT NULL,
    business_key character varying(40) NOT NULL,
    city character varying(255) NOT NULL,
    district character varying(255) NOT NULL,
    due_date date NOT NULL,
    finished NUMBER(1) NOT NULL,
    receipt_date date NOT NULL,
    title character varying(255) NOT NULL,
    contact_db_id character varying(255) NOT NULL,
    department_structure_id NUMBER(10) NOT NULL,
    type_id NUMBER(10) NOT NULL,
    source_mail_id character varying(255),
    creation_date date NOT NULL,
    customer_reference character varying(255),
    finished_date date
);

ALTER TABLE tbl_statement ADD (
  CONSTRAINT tbl_statement_pk PRIMARY KEY (id));

-- STATMENET_2_PARENT

CREATE TABLE tbl_statement2parent (
    statement_id NUMBER(10) NOT NULL,
    parent_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_pkey PRIMARY KEY (parent_id, statement_id);

-- STATEMENT_EDIT_LOG

CREATE SEQUENCE tbl_statement_edit_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

CREATE TABLE tbl_statement_edit_log (
    id NUMBER(10) DEFAULT tbl_statement_edit_log_id_seq.nextval NOT NULL,
    accesstype character varying(255) NOT NULL,
    ts timestamp(0) NOT NULL,
    statement_id NUMBER(10) NOT NULL,
    user_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_statement_edit_log ADD (
  CONSTRAINT tbl_statement_edit_log_pk PRIMARY KEY (id));

-- STATEMENT_TYPE
 
CREATE SEQUENCE tbl_statementtype_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

 
CREATE TABLE tbl_statementtype (
    id NUMBER(10) DEFAULT tbl_statementtype_id_seq.nextval NOT NULL,
    name character varying(255)
);


ALTER TABLE tbl_statementtype ADD (
  CONSTRAINT tbl_statementtype_pk PRIMARY KEY (id));

-- TEXTBLOCK_DEFINITION

CREATE SEQUENCE tbl_textblockdefinition_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
 
CREATE TABLE tbl_textblockdefinition (
    id NUMBER(10) DEFAULT tbl_textblockdefinition_id_seq.nextval NOT NULL,
    definition CLOB NOT NULL,
    version NUMBER(10) NOT NULL
);

ALTER TABLE tbl_textblockdefinition ADD (
  CONSTRAINT tbl_textblockdefinition_pk PRIMARY KEY (id));
 
-- USER

CREATE SEQUENCE tbl_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


CREATE TABLE tbl_user (
    id NUMBER(10) DEFAULT tbl_user_id_seq.nextval NOT NULL,
    first_name character varying(255) NOT NULL,
    last_name character varying(255) NOT NULL,
    username character varying(255) NOT NULL,
    email_address character varying(255),
    department_id NUMBER(10)
);

ALTER TABLE tbl_user ADD (
  CONSTRAINT tbl_user_pk PRIMARY KEY (id));
 
ALTER TABLE tbl_user
    ADD CONSTRAINT tbl_user_username_u UNIQUE (username);

 
-- WORKFLOW_DATA

CREATE SEQUENCE tbl_workflowdata_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;

 
CREATE TABLE tbl_workflowdata (
    id NUMBER(10) DEFAULT tbl_workflowdata_id_seq.nextval NOT NULL,
    draft CLOB,
    initial_state NUMBER(1),
    pos character varying(255),
    statement_id NUMBER(10) NOT NULL,
    textblockdefinition_id NUMBER(10) NOT NULL
);

ALTER TABLE tbl_workflowdata ADD (
  CONSTRAINT tbl_workflowdata_pk PRIMARY KEY (id));
 
ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_statement_u UNIQUE (statement_id);
    
    
-- FKs

ALTER TABLE tbl_statement_edit_log
    ADD CONSTRAINT tbl_statement_edit_log_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_statement_edit_log
    ADD CONSTRAINT tbl_statement_edit_log_fk_2 FOREIGN KEY (user_id) REFERENCES tbl_user(id);
   

ALTER TABLE tbl_reqdepartment
    ADD CONSTRAINT tbl_reqdepartment_fk_1 FOREIGN KEY (department_id) REFERENCES tbl_department(id);
ALTER TABLE tbl_reqdepartment
    ADD CONSTRAINT tbl_reqdepartment_fk_2 FOREIGN KEY (workflowdata_id) REFERENCES tbl_workflowdata(id);


ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_fk_1 FOREIGN KEY (attachment_id) REFERENCES tbl_attachment(id);
ALTER TABLE tbl_attachment2tag
    ADD CONSTRAINT tbl_attachment2tag_fk_2 FOREIGN KEY (tag_id) REFERENCES tbl_tag(id);

   
ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_fk_1 FOREIGN KEY (parent_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_statement2parent
    ADD CONSTRAINT tbl_statement2parent_fk_2 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);


ALTER TABLE tbl_statement
    ADD CONSTRAINT tbl_statement_fk_1 FOREIGN KEY (type_id) REFERENCES tbl_statementtype(id);
ALTER TABLE tbl_statement
    ADD CONSTRAINT tbl_statement_fk_2 FOREIGN KEY (department_structure_id) REFERENCES tbl_departmentstructure(id);


ALTER TABLE tbl_comment
    ADD CONSTRAINT tbl_comment_fk_1 FOREIGN KEY (user_id) REFERENCES tbl_user(id);


ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);
ALTER TABLE tbl_workflowdata
    ADD CONSTRAINT tbl_workflowdata_fk_2 FOREIGN KEY (textblockdefinition_id) REFERENCES tbl_textblockdefinition(id);


ALTER TABLE tbl_attachment
    ADD CONSTRAINT tbl_attachment_fk_1 FOREIGN KEY (statement_id) REFERENCES tbl_statement(id);

ALTER TABLE tbl_attachment
    ADD CONSTRAINT tbl_attachment_fk_2 FOREIGN KEY (attachment_lob_id) REFERENCES tbl_attachment_lob(id);

ALTER TABLE tbl_comment
    ADD CONSTRAINT tbl_comment_fk_2 FOREIGN KEY (workflow_id) REFERENCES tbl_workflowdata(id);



-- VW_STATEMENT_POSITION_SEARCH

CREATE VIEW vw_statement_position_search AS
 SELECT s.id,
    s.due_date,
    s.finished,
    s.title,
    s.type_id,
    w.pos
   FROM tbl_statement s,
    tbl_workflowdata w
  WHERE (w.statement_id = s.id);

-- VW_STATEMENT_REQDEPARTMENT_USERS

 
CREATE VIEW vw_statement_reqdepartment_users AS
 SELECT row_number() OVER (ORDER BY u.id) AS id,
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.username AS user_name,
    u.email_address,
    wd.statement_id,
    wd.id AS workflow_id,
    u.department_id,
    d.name AS department_name,
    d.departmentgroup AS department_group
   FROM (((tbl_user u
     JOIN tbl_department d ON ((u.department_id = d.id)))
     JOIN tbl_reqdepartment rd ON ((rd.department_id = d.id)))
     JOIN tbl_workflowdata wd ON ((wd.id = rd.workflowdata_id)));

-- VW_STATEMENT_SEARCH

CREATE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));
 
 

 
-- INSERT STATEMENT_TYPES

 
INSERT INTO tbl_statementtype VALUES (1, 'Bauleitplanung');
INSERT INTO tbl_statementtype VALUES (2, 'Mitverlegungsanfrage');
INSERT INTO tbl_statementtype VALUES (3, 'Bauvoranfrage');
INSERT INTO tbl_statementtype VALUES (4, 'Planfeststellungsverfahren');
INSERT INTO tbl_statementtype VALUES (5, 'Formlose Anfrage');


-- INSERT TAGS

INSERT INTO tbl_tag VALUES ('email-text', 'Emailanfrage');
INSERT INTO tbl_tag VALUES ('email', 'Email');
INSERT INTO tbl_tag VALUES ('outbox', 'Ausgang');
INSERT INTO tbl_tag VALUES ('consideration', 'Abwägungsergebnis');
INSERT INTO tbl_tag VALUES ('statement', 'Stellungnahme');
INSERT INTO tbl_tag VALUES ('cover-letter', 'Anschreiben');
INSERT INTO tbl_tag VALUES ('overview', 'Übersicht');
INSERT INTO tbl_tag VALUES ('expertise', 'Gutachten');
INSERT INTO tbl_tag VALUES ('plan', 'Plan');
INSERT INTO tbl_tag VALUES ('explanatory-report', 'Erläuterungsbericht');

-- INSERT DEPARTMENTSTRUCTURE

INSERT INTO TBL_DEPARTMENTSTRUCTURE (DEFINITION, VERSION) VALUES ('{}', 1);

-- INSERT TEXTBLOCKDEFINITION

INSERT INTO TBL_TEXTBLOCKDEFINITION (DEFINITION, VERSION) VALUES('{"groups":[],"negativeGroups":[],"selects":{}}', 1);


-- Updates: 

-- V2_0_0__ViewReqDepartmentUsersAddContribution 

CREATE OR REPLACE VIEW vw_statement_reqdepartment_users
AS SELECT row_number() OVER (ORDER BY u.id) AS id,
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.username AS user_name,
    u.email_address,
    wd.statement_id,
    wd.id AS workflow_id,
    u.department_id,
    d.name AS department_name,
    d.departmentgroup AS department_group,
    rd.contributed AS department_contributed
   FROM (((tbl_user u
     JOIN tbl_department d ON ((u.department_id = d.id)))
     JOIN tbl_reqdepartment rd ON ((rd.department_id = d.id)))
     JOIN tbl_workflowdata wd ON ((wd.id = rd.workflowdata_id)));



-- V2_0_1__DepartmentsDueDate

-- Add a due date for department contributions parameter to statement table

-- Add new department_due_date column
ALTER TABLE tbl_statement ADD departments_due_date date NULL;

-- Copy Value of due_date to department_due_date
UPDATE tbl_statement SET departments_due_date = due_date where departments_due_date is null ;

-- Set new department_due_date column to NOT NULL
ALTER TABLE tbl_statement modify departments_due_date NOT NULL; 


-- VW_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));
 


-- V2_0_2__MultipleUserDepartments

-- create sequence user2department_id_seq

CREATE SEQUENCE tbl_user2department_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;
   
-- create table user2department 

CREATE TABLE tbl_user2department (
    id NUMBER(10) DEFAULT tbl_user2department_id_seq.nextval NOT NULL,
    user_id NUMBER(10) NOT NULL,
    department_id NUMBER(10) NOT NULL,
    stand_in NUMBER(1) NOT NULL
);

ALTER TABLE tbl_user2department ADD
    CONSTRAINT tbl_user2department_pk PRIMARY KEY (id);

ALTER TABLE tbl_user2department ADD
    CONSTRAINT tbl_user2department_uk UNIQUE (user_id, department_id);

-- public.tbl_user2department foreign keys

ALTER TABLE tbl_user2department 
    ADD CONSTRAINT tbl_user2department_fk_1 FOREIGN KEY (user_id) REFERENCES tbl_user(id);
ALTER TABLE tbl_user2department ADD CONSTRAINT tbl_user2department_fk_2 FOREIGN KEY (department_id) REFERENCES tbl_department(id);

-- create entries for existing

INSERT into tbl_user2department (user_id, department_id, stand_in) select id, department_id , 0 from tbl_user where department_id is not null;

-- update view vw_statement_reqdepartment_users, replace user.department_id with ref via tbl_user2department

CREATE OR REPLACE VIEW vw_statement_reqdepartment_users
AS SELECT row_number() OVER (ORDER BY u.id) AS id,
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.username AS user_name,
    u.email_address,
    wd.statement_id,
    wd.id AS workflow_id,
    ud.department_id,
    d.name AS department_name,
    d.departmentgroup AS department_group,
    rd.contributed AS department_contributed,
    rd.optional AS department_optional,
    ud.stand_in as stand_in
   FROM tbl_user2department ud
     JOIN tbl_user u ON ud.user_id  = u.id
     JOIN tbl_department d ON ud.department_id = d.id
     JOIN tbl_reqdepartment rd ON rd.department_id = d.id
     JOIN tbl_workflowdata wd ON wd.id = rd.workflowdata_id;


-- rm reference from user to department

ALTER table tbl_user DROP COLUMN department_id;


-- V2_0_3__StatementDraft

-- create sequence tbl_statementdraft_id_seq

CREATE SEQUENCE tbl_statementdraft_id_seq
    START WITH 1
    INCREMENT BY 1
    NOMAXVALUE
    NOMINVALUE
    NOCACHE;


-- create table tbl_statementdraft 

CREATE TABLE tbl_statementdraft (
    id NUMBER(10) DEFAULT tbl_statementdraft_id_seq.nextval NOT NULL,
    workflow_id NUMBER(10) NOT NULL,
    user_id NUMBER(10),
    draft CLOB NOT NULL,
    msg character varying(255),
    ts timestamp NOT NULL
);

ALTER TABLE tbl_statementdraft ADD
    CONSTRAINT tbl_statementdraft_pk PRIMARY KEY (id);

-- public.tbl_statementdraft foreign keys

ALTER TABLE tbl_statementdraft ADD CONSTRAINT tbl_statementdraft_fk_1 FOREIGN KEY (workflow_id) REFERENCES tbl_workflowdata(id);
ALTER TABLE tbl_statementdraft ADD CONSTRAINT tbl_statementdraft_fk_2 FOREIGN KEY (user_id) REFERENCES tbl_user(id);

-- copy existing values

INSERT into tbl_statementdraft (workflow_id, draft, ts, msg) select id, draft , current_timestamp, 'initial-migrate'  from tbl_workflowdata where draft is not null;

-- view

CREATE OR REPLACE VIEW vw_statementdraft_history
AS select sd.id as id, row_number() over (PARTITION by sd.workflow_id order by sd.id) as v, wd.statement_id as statement_id, sd.workflow_id as workflow_id, sd.user_id as user_id, u.username as username, u.first_name as user_first_name, u.last_name as user_last_name, sd.ts as ts, sd.msg as msg, sd.draft as draft from tbl_statementdraft sd join tbl_workflowdata wd on wd.id = sd.workflow_id left join tbl_user u on u.id = sd.user_id;


-- delete column workflowdata draft entries

ALTER table tbl_workflowdata DROP COLUMN draft;


-- V2_0_4__UserDetails

-- add phone, fax and initials to table user

ALTER TABLE tbl_user ADD phone character varying(255);
ALTER TABLE tbl_user ADD fax character varying(255);
ALTER TABLE tbl_user ADD initials character varying(255);


-- V2_0_5__CancelStatements

-- add canceled indicator to tbl_statement

ALTER TABLE tbl_statement ADD canceled NUMBER(1) DEFAULT 0 NOT NULL;


-- VW_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));
 
 
-- VW_STATEMENT_POSITION_SEARCH

CREATE OR REPLACE VIEW vw_statement_position_search AS
 SELECT s.id,
    s.due_date,
    s.finished,
    s.title,
    s.type_id,
    w.pos,
    s.canceled
   FROM tbl_statement s,
    tbl_workflowdata w
  WHERE (w.statement_id = s.id);
  
  
-- V_2_0_6__DisableTagsAndStatementtypes


-- disable tags: add standard flag for tags not allowed to disable and disabled flag
ALTER TABLE tbl_tag ADD standard NUMBER(1) DEFAULT 0 NOT NULL;
ALTER TABLE tbl_tag ADD disabled NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE tbl_tag ADD CONSTRAINT tbl_tag_name_u UNIQUE (name);

UPDATE tbl_tag set standard = 1 WHERE id IN ('email-text', 'email', 'outbox', 'consideration', 'statement', 'cover-letter', 'overview', 'expertise', 'plan', 'explanatory-report');


-- disable statementtype: add disabled flag

ALTER TABLE tbl_statementtype ADD disabled NUMBER(1) DEFAULT 0 NOT NULL;

ALTER TABLE tbl_statementtype ADD CONSTRAINT tbl_statementtype_name_u UNIQUE (name);


CREATE OR REPLACE VIEW vw_deletable_tag AS
SELECT * from tbl_tag where id not in (select distinct t.id from tbl_attachment2tag tat join tbl_tag t on t.id = tat.tag_id) and standard != 1 and disabled = 1;


CREATE OR REPLACE VIEW vw_deletable_statementtype AS
SELECT * from tbl_statementtype ts where id not in (select distinct st.id from tbl_statement s join tbl_statementtype st  on st.id = s.type_id) and disabled = 1;


-- increment id sequence by 5 to ensure valid nextval

SELECT tbl_statementtype_id_seq.nextval FROM dual;
SELECT tbl_statementtype_id_seq.nextval FROM dual;
SELECT tbl_statementtype_id_seq.nextval FROM dual;
SELECT tbl_statementtype_id_seq.nextval FROM dual;
SELECT tbl_statementtype_id_seq.nextval FROM dual;



-- add street column to tbl_statement

ALTER TABLE tbl_statement ADD street character varying(255) NULL;


-- VW_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
   upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.street, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled,
  	s.street
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
    upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.street, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled,
    s.street
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));



-- V_2_0_8__MoreInfo

ALTER TABLE tbl_statement ADD more_info character varying(255) NULL;


-- VW_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_statement_search AS
 SELECT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.more_info,
    s.title,
    s.type_id,
    s.contact_db_id,
    s.source_mail_id,
   upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.street, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled,
    s.street,
    s.more_info
   FROM tbl_statement s,
    tbl_statementtype t
  WHERE (s.type_id = t.id);


-- VW_USER_STATEMENT_SEARCH

CREATE OR REPLACE VIEW vw_user_statement_search AS
 SELECT DISTINCT s.id,
    s.business_key,
    s.city,
    s.district,
    s.due_date,
    s.finished,
    s.receipt_date,
    s.creation_date,
    s.customer_reference,
    s.title,
    s.type_id,
    s.contact_db_id,
    e.user_id,
    s.source_mail_id,
   upper(s.id || '|@|' || COALESCE(t.name, '') ||  '|@|' || COALESCE (s.title, '') || '|@|' || COALESCE (s.city, '')  || '|@|' || COALESCE (s.street, '')  || '|@|' || COALESCE (s.district, '') || '|@|' || COALESCE (s.customer_reference, '') || '|@|' ) AS searchfield,
    s.departments_due_date,
  	s.canceled,
    s.street,
  	s.more_info
   FROM tbl_statement s,
    tbl_statementtype t,
    tbl_statement_edit_log e
  WHERE ((s.type_id = t.id) AND (e.statement_id = s.id));

Copyright © Eclipse Foundation, Inc. All Rights Reserved.     Privacy Policy | Terms of Use | Copyright Agent