diff options
author | Dimitrios Chalepakis | 2020-03-06 12:52:57 +0000 |
---|---|---|
committer | Dimitrios Chalepakis | 2020-03-06 12:52:57 +0000 |
commit | d5d595bba5a420632f386e7da56b4338234ce05d (patch) | |
tree | 11a07890b0d0a1e00cd31430021024571b698819 | |
parent | 2b43f8e6ffdeab911971317e5f3ebcdf402895cb (diff) | |
download | backup.org.eclipse.openk-coremodules.contactBaseData.backend-d5d595bba5a420632f386e7da56b4338234ce05d.tar.gz backup.org.eclipse.openk-coremodules.contactBaseData.backend-d5d595bba5a420632f386e7da56b4338234ce05d.tar.xz backup.org.eclipse.openk-coremodules.contactBaseData.backend-d5d595bba5a420632f386e7da56b4338234ce05d.zip |
KON-701 Sortierung nach Kontakttyp erfolgt nicht alphabetischKON-701-Sortierung-nach-Kontakttyp-erfolgt-nicht-alphabetisch
Constants and scripts adapted
3 files changed, 718 insertions, 7 deletions
diff --git a/src/main/java/org/eclipse/openk/contactbasedata/constants/Constants.java b/src/main/java/org/eclipse/openk/contactbasedata/constants/Constants.java index 01ae819..c3a97b1 100644 --- a/src/main/java/org/eclipse/openk/contactbasedata/constants/Constants.java +++ b/src/main/java/org/eclipse/openk/contactbasedata/constants/Constants.java @@ -30,8 +30,8 @@ public final class Constants { // empty Constructor for the sake of SONAR } public static final String DB_VERSION_NOT_PRESENT = "DB-Version-not_present"; - public static final String CONTACT_TYPE_INTERNAL_PERSON = "I_P"; - public static final String CONTACT_TYPE_EXTERNAL_PERSON = "E_P"; - public static final String CONTACT_TYPE_CONTACT_PERSON = "C_P"; - public static final String CONTACT_TYPE_COMPANY = "COM"; + public static final String CONTACT_TYPE_CONTACT_PERSON = "1CP"; + public static final String CONTACT_TYPE_EXTERNAL_PERSON = "2EP"; + public static final String CONTACT_TYPE_INTERNAL_PERSON = "3IP"; + public static final String CONTACT_TYPE_COMPANY = "4CO"; } diff --git a/src/main/resources/db/migration/V0_22__CREATE_CBD_DB.sql b/src/main/resources/db/migration/V0_22__CREATE_CBD_DB.sql new file mode 100644 index 0000000..7ce1585 --- /dev/null +++ b/src/main/resources/db/migration/V0_22__CREATE_CBD_DB.sql @@ -0,0 +1,711 @@ +----------------------------------------------------------------------------------- +-- ******************************************************************************* +-- * 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 +-- ******************************************************************************* +----------------------------------------------------------------------------------- + +-- CREATE ROLE CBD_SERVICE LOGIN +-- NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; +-- ALTER ROLE CBD_SERVICE with password 'cbd_service'; +-- Insert new Columns into table REF_COMMUNICATION_TYPE ('EDITABLE, 'MAPPING_LDAP') + +-- --------------------------------------------- +-- DROPS +-- --------------------------------------------- +DROP TABLE IF EXISTS public.VERSION CASCADE; + +DROP TABLE IF EXISTS public.TBL_ADDRESS CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_ADDRESS_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_COMMUNICATION CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_COMMUNICATION_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_CONTACT_PERSON CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_CONTACT_PERSON_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_COMPANY CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_COMPANY_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_EXTERNAL_PERSON CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_EXTERNAL_PERSON_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_INTERNAL_PERSON CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_INTERNAL_PERSON_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_CONTACT CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_CONTACT_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_ADDRESS_TYPE CASCADE; +DROP SEQUENCE IF EXISTS public.REF_ADDRESS_TYPE_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_PERSON_TYPE CASCADE; +DROP SEQUENCE IF EXISTS public.REF_PERSON_TYPE_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_COMMUNICATION_TYPE CASCADE; +DROP SEQUENCE IF EXISTS public.REF_COMMUNICATION_TYPE_ID_SEQ; + +DROP TABLE IF EXISTS public.REF_SALUTATION CASCADE; +DROP SEQUENCE IF EXISTS public.REF_SALUTATION_ID_SEQ; + +DROP TABLE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT CASCADE; +DROP SEQUENCE IF EXISTS public.TBL_ASSIGNMENT_MODUL_CONTACT_ID_SEQ; + +-- --------------------------------------------- +-- TABLE VERSION +-- --------------------------------------------- +CREATE TABLE public.VERSION +( + ID integer NOT NULL, + VERSION character varying(50) NOT NULL, + CONSTRAINT REF_VERSION_PKEY PRIMARY KEY (id) +); + +ALTER TABLE public.VERSION + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.VERSION TO CBD_SERVICE; + +INSERT INTO public.VERSION (ID, VERSION) VALUES ( 1, '00-DEV' ); + + +-- --------------------------------------------- +-- TABLE TBL_CONTACT +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_contact_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_contact_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_CONTACT +( + ID bigint NOT NULL DEFAULT nextval('tbl_contact_id_seq'::regclass), + UUID uuid NOT NULL, + CONTACT_TYPE character varying(3), + NOTE character varying(255), + ANONYMIZED boolean, + CONSTRAINT TBL_CONTACT_PKEY PRIMARY KEY (ID) +); + + + +ALTER TABLE public.TBL_CONTACT + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_CONTACT TO CBD_SERVICE; + +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ae3f2ec1-ccc5-4269-a48f-dd40e37fa14e', '4CO', 'company 1 (id=1)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fc7f598b-0d51-46bb-9563-99851fe6a3ad', '4CO', 'company 2 (id=2)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '556b91be-6d57-432f-93ed-65604dd6e5cd', '1CP', 'contact person 1 (id=3)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '116380e3-25c5-4179-b40a-8abebe10fe07', '1CP', 'contact person 2 (id=4)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7782179b-fb79-4370-8f71-f4c71470d006', '3IP', 'internal person 1 (id=5)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8963aa38-d021-4dc9-bd70-d3734ccd20c4', '3IP', 'internal person 2 (id=6)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'c862d604-5766-43d6-a7e8-a4bac2bd01e1', '2EP', 'external person 1 (id=7)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'fa3d981b-a7d6-4965-a623-cdbc69404153', '2EP', 'external person 2 (id=8)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ceba21e9-e685-483b-840e-ad167860a696', '4CO', 'anonymous company A (id=9)', true ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '7889c814-9752-4e4e-a9fe-b46f36a38ccd', '4CO', 'anonymous company B (id=10)', true ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '618a01a3-f348-44cc-9ddd-c9df946b0212', '1CP', 'anoymous contact person A1 (id=11)', true ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '4ed82474-3878-457a-baef-c28b8e486f25', '1CP', 'anoymous contact persion A2 (id=12)', true ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( '8fe41b90-d10c-4a70-8fde-0990286ad3c6', '3IP', 'internal person 3 (id=13)', null ); +INSERT INTO public.TBL_CONTACT (UUID, CONTACT_TYPE, NOTE, ANONYMIZED) VALUES ( 'ab804610-d6a4-4803-a4a1-3f6cb742b2a4', '3IP', 'internal person 4 (id=14)', null ); + + +CREATE UNIQUE INDEX idx_tbl_contact_contact_type ON public.TBL_CONTACT ( ID ASC ); +CREATE UNIQUE INDEX idx_cntct_uuid ON public.TBL_CONTACT (UUID); +CREATE INDEX idx_cntct_anonym ON public.TBL_CONTACT (ANONYMIZED); + + +-- --------------------------------------------- +-- TABLE REF_ADDRESS_TYPE +-- --------------------------------------------- +CREATE SEQUENCE public.ref_address_type_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_address_type_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.REF_ADDRESS_TYPE +( + ID bigint NOT NULL DEFAULT nextval('ref_address_type_id_seq'::regclass), + UUID uuid NOT NULL, + TYPE character varying(30), + DESCRIPTION character varying(255), + CONSTRAINT REF_ADDRESS_TYPE_PKEY PRIMARY KEY (ID) +); + +ALTER TABLE public.REF_ADDRESS_TYPE + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.REF_ADDRESS_TYPE TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_ref_address_type_uuid ON public.REF_ADDRESS_TYPE ( UUID ASC ); + +INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '3802e681-9396-434e-b19c-5fedcec40ba7', 'Geschäftsadresse', 'Adresse des Hauptfirmensitzes' ); +INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'f43ed6ac-9e7a-40f6-acc9-ec6b73eebf79', 'Privatadresse', 'private Anschrift' ); +INSERT INTO public.REF_ADDRESS_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '70fd0811-f674-4f3a-96a7-7ae29fc95188', 'Lieferadresse', 'Adresse für Lieferungen' ); + + +-- --------------------------------------------- +-- TABLE REF_PERSON_TYPE +-- --------------------------------------------- +CREATE SEQUENCE public.ref_person_type_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_person_type_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.REF_PERSON_TYPE +( + ID bigint NOT NULL DEFAULT nextval('ref_person_type_id_seq'::regclass), + UUID uuid NOT NULL, + TYPE character varying(30), + DESCRIPTION character varying(255), + CONSTRAINT REF_PERSON_TYPE_PKEY PRIMARY KEY (ID) +); +ALTER TABLE public.REF_PERSON_TYPE + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.REF_PERSON_TYPE TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_ref_person_type_uuid ON public.REF_PERSON_TYPE ( UUID ASC ); + +INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '47ce68b7-6d44-453e-b421-19020fd791b5', 'Rechtsanwalt', '' ); +INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( 'a7522c72-14d0-4e9d-afe3-bfcb3ffbec10', 'Geschäftsführer', '' ); +INSERT INTO public.REF_PERSON_TYPE (UUID, TYPE, DESCRIPTION) VALUES ( '2eb4885e-7363-4918-90ed-b7d5d84cfd3f', 'Rechnungsempfänger', 'Person, der Rechnungen zukommen' ); + + +-- --------------------------------------------- +-- TABLE REF_COMMUNICATION_TYPE +-- --------------------------------------------- +CREATE SEQUENCE public.ref_communication_type_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_communication_type_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.REF_COMMUNICATION_TYPE +( + ID bigint NOT NULL DEFAULT nextval('ref_communication_type_id_seq'::regclass), + UUID uuid NOT NULL, + TYPE character varying(30), + DESCRIPTION character varying(255), + TYPE_EMAIL boolean, + CONSTRAINT REF_COMMUNICATION_TYPE_PKEY PRIMARY KEY (ID) +); +ALTER TABLE public.REF_COMMUNICATION_TYPE + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.REF_COMMUNICATION_TYPE TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_ref_communication_type_uuid ON public.REF_COMMUNICATION_TYPE ( UUID ASC ); + +INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '4757ca3a-72c2-4f13-a2f6-ce092e3eadf4', 'E-Mail', 'E-Mail Adresse', true ); +INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '77028572-ff57-4c1d-999a-78fa3fcbc1cd', 'Festnetz', '', false ); +INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( 'f7d5b343-00c2-4d7f-8e03-009aad3d90f7', 'Mobil', '', false ); +INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( '2bfe40f9-c4eb-4d2e-855f-6b0883912846', 'Fax', '', false ); +INSERT INTO public.REF_COMMUNICATION_TYPE (UUID, TYPE, DESCRIPTION, TYPE_EMAIL) VALUES ( 'd00d1a61-c8e7-43b2-959f-66e986731441', 'WhatsApp', '', false ); + +-- --------------------------------------------- +-- TABLE REF_SALUTATION +-- --------------------------------------------- +CREATE SEQUENCE public.ref_salutation_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.ref_salutation_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.REF_SALUTATION +( + ID bigint NOT NULL DEFAULT nextval('ref_salutation_id_seq'::regclass), + UUID uuid NOT NULL, + TYPE character varying(30), + DESCRIPTION character varying(255), + CONSTRAINT REF_SALUTATION_PKEY PRIMARY KEY (ID) +); +ALTER TABLE public.REF_SALUTATION + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.REF_SALUTATION TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_ref_salutation_uuid ON public.REF_SALUTATION ( UUID ASC ); + +INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '90119f18-5562-425d-9a36-3dd58ea125e5', 'Herr', 'Anrede männlich' ); +INSERT INTO public.REF_SALUTATION (UUID, TYPE, DESCRIPTION) VALUES ( '4e873baa-e4f5-4585-8b16-2db8fac66538', 'Frau', 'Anrede weiblich' ); + + +-- --------------------------------------------- +-- TABLE TBL_ADDRESS +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_address_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_address_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_ADDRESS +( + ID bigint NOT NULL DEFAULT nextval('tbl_address_id_seq'::regclass), + UUID uuid NOT NULL, + FK_CONTACT_ID bigint NOT NULL, + FK_ADDRESS_TYPE bigint, + IS_MAIN_ADDRESS boolean, + POSTCODE character varying(30), + COMMUNITY character varying(255), + COMMUNITY_SUFFIX character varying(255), + STREET character varying(255), + HOUSENUMBER character varying(30), + WGS_84_ZONE character varying(255), + LATITUDE character varying(255), + LONGITUDE character varying(255), + URL_MAP character varying(255), + NOTE character varying(255), + + CONSTRAINT TBL_ADDRESS_PKEY PRIMARY KEY (ID), + CONSTRAINT TBL_ADDRESS__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_ADDRESS__ADDRESS_TYPE_ID_FKEY FOREIGN KEY (FK_ADDRESS_TYPE) + REFERENCES public.REF_ADDRESS_TYPE (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_ADDRESS + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_ADDRESS TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_tbl_address_uuid ON public.TBL_ADDRESS ( UUID ASC ); + +INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '37e800fe-64f0-4834-8b83-8453cbb936a5', 2, 1, true, '12345', 'Heringsdorf','', 'Flunderweg', '5', '', '53 NL', '3 WB','www.xyz', 'nur über Seeweg erreichbar'); +INSERT INTO public.TBL_ADDRESS (UUID, FK_CONTACT_ID, FK_ADDRESS_TYPE, IS_MAIN_ADDRESS, POSTCODE, COMMUNITY, COMMUNITY_SUFFIX, STREET, HOUSENUMBER, WGS_84_ZONE, LATITUDE, LONGITUDE, URL_MAP, NOTE) VALUES ( '8a1202ae-2532-474e-8367-a1f0e13e9fbd', 1, 2, false, '67890', 'Stralsund','', 'Schollendamm', '18', '', '53 N', '2 WB','www.xyz', 'Hochwassergefahr'); + + +-- --------------------------------------------- +-- TABLE TBL_COMMUNICATION +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_communication_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_communication_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_COMMUNICATION +( + ID bigint NOT NULL DEFAULT nextval('tbl_communication_id_seq'::regclass), + UUID uuid NOT NULL, + FK_CONTACT_ID bigint NOT NULL, + FK_COMMUNICATION_TYPE bigint, + COMMUNICATION_DATA character varying(1024), + NOTE character varying(255), + + CONSTRAINT TBL_COMMUNICATION_PKEY PRIMARY KEY (ID), + CONSTRAINT TBL_COMMUNICATION__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_COMMUNICATION__COMMUNICATION_TYPE_ID_FKEY FOREIGN KEY (FK_COMMUNICATION_TYPE) + REFERENCES public.REF_COMMUNICATION_TYPE (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_COMMUNICATION + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_COMMUNICATION TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_tbl_communication_uuid ON public.TBL_COMMUNICATION ( UUID ASC ); + +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '25f6d7cc-b168-4dd5-a36d-6f14b2f956e9', 2, 2, 'bitte melden Sie sich bei uns', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'a5fa380e-8f33-4ea7-9416-e03d11b91cae', 1, 3, 'bitte melden zwecks Terminabstimmung', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'c0dcef80-ca07-48b7-a3ed-2c99c4388928', 1, 1, 'info@bigbang.com', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'd19d9e10-d2dd-4383-84ec-2fe96421c0a3', 2, 1, 'info@pharmapeekltd.com', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '234b63e4-d8db-48ab-899f-0320903c01af', 3, 1, 'reinbold.tab@pharmapeek.com', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '11e25c02-de00-430d-b6cd-f02f7c60e026', 5, 1, 'PaulineF@gmx.net', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '9ca29c3b-e189-4ce9-9401-15001c769627', 6, 1, 'mo@gmail.com', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( '05505b1a-61df-45c0-b006-64165cbadfa2', 7, 1, 'MGruebelSport@fogger.mil', 'Info...'); +INSERT INTO public.TBL_COMMUNICATION (UUID, FK_CONTACT_ID, FK_COMMUNICATION_TYPE, COMMUNICATION_DATA, NOTE) VALUES ( 'b52dabaf-d156-4fd0-a07c-510673112a15', 8, 1, 'Mini.osterbrink@yahoo.ie', 'Info...'); + +-- --------------------------------------------- +-- TABLE TBL_COMPANY +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_company_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_company_id_seq + OWNER TO CBD_SERVICE; + + +CREATE TABLE public.TBL_COMPANY +( + ID bigint NOT NULL DEFAULT nextval('tbl_company_id_seq'::regclass), + COMPANY_NAME character varying(255), + COMPANY_TYPE character varying(30), + HR_NUMBER character varying(255), + FK_CONTACT_ID bigint NOT NULL, + CONSTRAINT TBL_COMPANY_PKEY PRIMARY KEY (id), + CONSTRAINT TBL_COMPANY__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT(ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_COMPANY + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_COMPANY TO CBD_SERVICE; + +INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'BigBang Logistic', 'Logistik', '123', 1 ); +INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( 'Pharma Peek', 'Pharma', '345', 2 ); +INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 9 ); +INSERT INTO public.TBL_COMPANY (COMPANY_NAME, COMPANY_TYPE, HR_NUMBER, FK_CONTACT_ID) VALUES ( '***', '***', null, 10 ); + + +-- --------------------------------------------- +-- TABLE TBL_CONTACT_PERSON +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_contact_person_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_contact_person_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_CONTACT_PERSON +( + ID bigint NOT NULL DEFAULT nextval('tbl_contact_person_id_seq'::regclass), + FIRST_NAME character varying(255), + LAST_NAME character varying(255), + TITLE character varying(255), + FK_SALUTATION_ID bigint, + FK_REF_PERSON_TYPE_ID bigint NULL, + FK_CONTACT_ID bigint NOT NULL, + FK_COMPANY_ID bigint NOT NULL, + CONSTRAINT TBL_CONTACT_PERSON_PKEY PRIMARY KEY (ID), + CONSTRAINT TBL_CONTACT_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_CONTACT_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) + REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_CONTACT_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) + REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_CONTACT_PERSON__COMPANY_ID_FKEY FOREIGN KEY (FK_COMPANY_ID) + REFERENCES public.TBL_COMPANY (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_CONTACT_PERSON + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_CONTACT_PERSON TO CBD_SERVICE; + +INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Tabea', 'Reinebold', 'Dr.', 2, 1, 3, 2); +INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( 'Jan', 'Wacker', '', 1, 1, 4, 2); +INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 11, 3); +INSERT INTO public.TBL_CONTACT_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID, FK_COMPANY_ID) VALUES ( '***', '***', null, null, null, 12, 2); + +-- --------------------------------------------- +-- TABLE TBL_EXTERNAL_PERSON +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_external_person_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_external_person_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_EXTERNAL_PERSON +( + ID bigint NOT NULL DEFAULT nextval('tbl_external_person_id_seq'::regclass), + FIRST_NAME character varying(255), + LAST_NAME character varying(255), + TITLE character varying(255), + FK_SALUTATION_ID bigint, + FK_REF_PERSON_TYPE_ID bigint, + FK_CONTACT_ID bigint NOT NULL, + CONSTRAINT TBL_EXTERNAL_PERSON_PKEY PRIMARY KEY (id), + CONSTRAINT TBL_EXTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_EXTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) + REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_EXTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) + REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_EXTERNAL_PERSON + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_EXTERNAL_PERSON TO CBD_SERVICE; + +INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Monica', 'Grübel', 'Dipl.-Sportlehrerin', 2, 1, 7); +INSERT INTO public.TBL_EXTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, FK_CONTACT_ID) VALUES ( 'Maurice', 'Fürstenberg', 'B.A.', 2, 2, 8); + + +-- --------------------------------------------- +-- TABLE TBL_INTERNAL_PERSON +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_internal_person_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_internal_person_id_seq + OWNER TO CBD_SERVICE; +CREATE TABLE public.TBL_INTERNAL_PERSON +( + ID bigint NOT NULL DEFAULT nextval('tbl_internal_person_id_seq'::regclass), + FIRST_NAME character varying(255), + LAST_NAME character varying(255), + TITLE character varying(255), + FK_SALUTATION_ID bigint, + FK_REF_PERSON_TYPE_ID bigint, + DEPARTMENT character varying(255), + UID character varying(255), + USER_REF character varying(255), + FK_CONTACT_ID bigint NOT NULL, + CONSTRAINT TBL_INTERNAL_PERSON_PKEY PRIMARY KEY (ID), + CONSTRAINT TBL_INTERNAL_PERSON__CONTACT_ID_FKEY FOREIGN KEY (FK_CONTACT_ID) + REFERENCES public.TBL_CONTACT (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_INTERNAL_PERSON__SALUTATION_ID_FKEY FOREIGN KEY (FK_SALUTATION_ID) + REFERENCES public.REF_SALUTATION (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION, + CONSTRAINT TBL_INTERNAL_PERSON__PERSON_TYPE_ID_FKEY FOREIGN KEY (FK_REF_PERSON_TYPE_ID) + REFERENCES public.REF_PERSON_TYPE (ID) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +); +ALTER TABLE public.TBL_INTERNAL_PERSON + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.TBL_INTERNAL_PERSON TO CBD_SERVICE; + +CREATE UNIQUE INDEX idx_tbl_internal_person_uid ON public.TBL_INTERNAL_PERSON ( UID ASC ); +CREATE UNIQUE INDEX idx_tbl_internal_person_user_ref ON public.TBL_INTERNAL_PERSON ( USER_REF ASC ); + +INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Pauline', 'Freudenberg', 'B.Sc.', 1, 1,'Abteilung Rechnungsstellung', '66cd78c3-6716-4ab3-b834-a199fc796b88', 'PFREUD', 5); +INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Bernhardt', 'Iffland', '', 2, 2,'Kreativ', '4124e4e7-3488-4492-bf39-75e6a23a1c1a', 'BIFFL', 6); +INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Horatio', 'Hornblower', 'Capt.', 1, 2,'Royal Navy', 'hhornblo', 'HORNB', 13); +INSERT INTO public.TBL_INTERNAL_PERSON (FIRST_NAME, LAST_NAME, TITLE, FK_SALUTATION_ID, FK_REF_PERSON_TYPE_ID, DEPARTMENT, UID, USER_REF, FK_CONTACT_ID) VALUES ( 'Cornelius', 'Buckley', '', 1, 2,'Royal Navy', null, 'BUCKC', 14); +-- --------------------------------------------- +-- TABLE TBL_ASSIGNMENT_MODUL_CONTACT +-- --------------------------------------------- +CREATE SEQUENCE public.tbl_assignment_modul_contact_id_seq + INCREMENT 1 + MINVALUE 1 + MAXVALUE 9223372036854775807 + START 1 + CACHE 1; +ALTER TABLE public.tbl_assignment_modul_contact_id_seq + OWNER TO CBD_SERVICE; + +CREATE TABLE public.TBL_ASSIGNMENT_MODUL_CONTACT +( + id bigint NOT NULL DEFAULT nextval('tbl_assignment_modul_contact_id_seq'::regclass), + uuid uuid NOT NULL, + fk_contact_id bigint NOT NULL, + modul_name character varying(255), + assignment_date timestamp, + expiring_date timestamp, + deletion_lock_until timestamp, + assignment_note character varying(2048), + CONSTRAINT tbl_assignment_modul_contact_pkey PRIMARY KEY (id), + CONSTRAINT tbl_assignment_modul_contact_id_fkey FOREIGN KEY (fk_contact_id) + REFERENCES public.tbl_contact (id) MATCH SIMPLE + ON UPDATE NO ACTION ON DELETE NO ACTION +) +WITH ( + OIDS=FALSE +); +ALTER TABLE public.tbl_assignment_modul_contact + OWNER TO cbd_service; +GRANT ALL ON TABLE public.tbl_assignment_modul_contact TO cbd_service; + +INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( 'dfc2979c-40e3-11ea-b77f-2e728ce88125', 1, 'Betriebstagebuch', '2020-01-19 10:23:54', '2021-01-19 00:00:00', '2021-01-19 00:00:00', 'seit Januar zugeordnet'); +INSERT INTO public.tbl_assignment_modul_contact(uuid, fk_contact_id, modul_name, assignment_date, expiring_date, deletion_lock_until, assignment_note) VALUES ( '4009bce2-40e5-11ea-b77f-2e728ce88125', 1, 'Störinfos', '2020-02-27 10:23:54', '2019-02-27 00:00:00', '2019-02-27 00:00:00', 'seit Februar zugeordnet'); + +-- ------------------------------------- +-- VIEWS ------------------------------- +-- ------------------------------------- +DROP VIEW IF EXISTS VW_GENERAL_CONTACT CASCADE; + +CREATE VIEW VW_GENERAL_CONTACT +AS +SELECT g.id, + c.uuid, + g.name, + c.contact_type, + g.fk_contact_id, + g.company_name, + g.company_type, + g.company_id, + g.fk_salutation_id, + g.fk_ref_person_type_id, + g.title, + g.first_name, + g.last_name, + g.department, + c.note, + c.anonymized +FROM tbl_contact c +INNER JOIN ( + +SELECT company.id, + company_name as name, + fk_contact_id, + company_name, + company_type, + cntct.uuid as company_id, + null as fk_salutation_id, + null as fk_ref_person_type_id, + null as title, + null as first_name, + null as last_name, + null as department +FROM tbl_company company +INNER JOIN tbl_contact cntct ON company.fk_contact_id = cntct.id + +UNION + +SELECT p.id, + COALESCE(p.last_name, '') + || CASE WHEN COALESCE(p.last_name, '') <> '' AND COALESCE(p.first_name) <> '' THEN ', ' ELSE '' END + || COALESCE(p.first_name,'') || ' [' || COALESCE(c.company_name, '') || ']' as name, + p.fk_contact_id, + c.company_name, + c.company_type, + company_contact.uuid as company_id, + p.fk_salutation_id, + p.fk_ref_person_type_id, + p.title, + p.first_name, + p.last_name, + null as department +FROM tbl_contact_person p +INNER JOIN tbl_company c ON c.id = p.fk_company_id +INNER JOIN tbl_contact company_contact ON c.fk_contact_id = company_contact.id + +UNION + +SELECT id, + COALESCE(last_name, '') + || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END + || COALESCE(first_name, '') as name, + fk_contact_id, + null as company_name, + null as company_type, + null as company_id, + fk_salutation_id, + fk_ref_person_type_id, + title, + first_name, + last_name, + department +FROM tbl_internal_person + +UNION + +SELECT id, + COALESCE(last_name, '') + || CASE WHEN COALESCE(last_name, '') <> '' AND COALESCE(first_name) <> '' THEN ', ' ELSE '' END + || COALESCE(first_name, '') as name, + fk_contact_id, + null as company_name, + null as company_type, + null as company_id, + fk_salutation_id, + fk_ref_person_type_id, + title, + first_name, + last_name, + null as department +FROM tbl_external_person + ) g +ON g.fk_contact_id = c.ID; + +ALTER VIEW public.VW_GENERAL_CONTACT + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.VW_GENERAL_CONTACT TO CBD_SERVICE; + + +DROP VIEW IF EXISTS VW_DETAILED_CONTACT; + +CREATE VIEW VW_DETAILED_CONTACT +AS +SELECT c.id, + c.uuid, + c.name, + c.contact_type, + c.fk_contact_id, + c.company_name, + c.company_type, + c.company_id, + c.anonymized, + s.uuid as salutation_uuid, + t.uuid as person_type_uuid, + c.title, + c.first_name, + c.last_name, + c.department, + c.note, + s.type as salutation_type, + t.type as person_type, + COALESCE(a.community, '') || + CASE WHEN COALESCE(a.street, '') || COALESCE(a.housenumber, '') = '' THEN '' ELSE ', ' END || + COALESCE(a.street, '') || + CASE WHEN COALESCE(a.housenumber, '') = '' THEN '' ELSE ' ' END || + COALESCE(a.housenumber, '') as main_address, + com.communication_data as email, + + UPPER( + COALESCE(c.name, '') || '|@|' + || COALESCE(company_name, '') || '|@|' + || COALESCE(c.company_type, '') || '|@|' + || COALESCE(c.title, '') || '|@|' + || COALESCE(c.first_name, '') || '|@|' + || COALESCE(c.last_name, '') || '|@|' + || COALESCE(c.department, '') || '|@|' + || COALESCE(c.note, '') || '|@|' + || COALESCE(s.type, '') || '|@|' + || COALESCE(t.type, '') || '|@|' + || COALESCE(a.street, '') || '|@|' + || COALESCE(a.housenumber, '') || '|@|' + || COALESCE(a.community, '') || '|@|' + || COALESCE(com.communication_data, '') + )as searchfield +FROM VW_GENERAL_CONTACT c +LEFT OUTER JOIN ref_salutation s ON c.fk_salutation_id = s.id +LEFT OUTER JOIN tbl_address a ON a.fk_contact_id = c.fk_contact_id and is_main_address = true +LEFT OUTER JOIN ref_person_type t ON c.fk_ref_person_type_id = t.id +LEFT OUTER JOIN tbl_communication com ON (com.fk_contact_id = c.fk_contact_id AND com.fk_communication_type = + (SELECT ct.id FROM ref_communication_type ct WHERE ct.type_email = true)) ; + +ALTER VIEW public.VW_DETAILED_CONTACT + OWNER TO CBD_SERVICE; +GRANT ALL ON TABLE public.VW_DETAILED_CONTACT TO CBD_SERVICE; + + + + diff --git a/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java b/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java index ed5fc35..8ce842f 100644 --- a/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java +++ b/src/test/java/org/eclipse/openk/contactbasedata/support/MockDataHelper.java @@ -534,7 +534,7 @@ public class MockDataHelper { contactPersonDto.setCompanyType(Constants.CONTACT_TYPE_CONTACT_PERSON); contactPersonDto.setTitle("Prof. Dr."); contactPersonDto.setContactUuid(UUID.randomUUID()); - contactPersonDto.setContactType("C_P"); + contactPersonDto.setContactType("1CP"); contactPersonDto.setContactNote("eine Notiz"); contactPersonDto.setSalutationUuid(UUID.randomUUID()); contactPersonDto.setPersonTypeUuid(UUID.randomUUID()); @@ -561,7 +561,7 @@ public class MockDataHelper { tblContact.setId(5L); tblContact.setUuid(UUID.randomUUID()); tblContact.setNote("Notiz"); - tblContact.setContactType("E_P"); + tblContact.setContactType("2EP"); return tblContact; } @@ -570,7 +570,7 @@ public class MockDataHelper { ContactDto contactDto = new ContactDto(); contactDto.setUuid(UUID.randomUUID()); contactDto.setNote("Notiz"); - contactDto.setContactType("E_P"); + contactDto.setContactType("2EP"); return contactDto; } |