Merge branch 'DEVELOP' of ssh://git.eclipse.org:29418/openk-coremodules/org.eclipse.openk-coremodules.contactBaseData.backend
diff --git a/deploy/get_started.txt b/deploy/get_started.txt
index fd48550..389bd68 100644
--- a/deploy/get_started.txt
+++ b/deploy/get_started.txt
@@ -90,8 +90,8 @@
--------------------------
Active Develop Branches: |
-Backend -> DEVELOP_BE |
-Frontend -> DEVELOP_FE |
+Backend -> DEVELOP |
+Frontend -> DEVELOP |
-------------------------|
Stable Branches: master |
--------------------------
diff --git a/src/main/asciidoc/deployment/deploymentSetup.adoc b/src/main/asciidoc/deployment/deploymentSetup.adoc
deleted file mode 100644
index 938eb27..0000000
--- a/src/main/asciidoc/deployment/deploymentSetup.adoc
+++ /dev/null
@@ -1,201 +0,0 @@
-////
-******************************************************************************
-* Copyright © 2017-2018 PTA GmbH.
-* All rights reserved. This program and the accompanying materials
-* are made available under the terms of the Eclipse Public License v1.0
-* which accompanies this distribution, and is available at
-*
-* http://www.eclipse.org/legal/epl-v10.html
-*
-******************************************************************************
-////
-= [ WIRD AUF KSM ANGEPASST NACH RÜCKSPRACHE IM Kontaktstammdaten: Sprint Review (26.02.2020) ] openKonsequenz - How to deploy the module "eLogbook@openK"
-Simon Reis <simon.reis@pta.de>
-:Date: 2020-02-25
-:Revision: 2
-:icons:
-:source-highlighter: highlightjs
-:highlightjs-theme: solarized_dark
-
-<<<
-
-== Deployment
-
-The elogbook application consists of 3 parts regarding the deployment.
-
-. Frontend: "elogbookFE"-Directory
-. Backend: "elogbook.war"
-. Database: The scripts are found in the "db" folder within the backend sources.
-
-=== Deployment of the application components
-
-==== Deployment of the frontend
-
-The Frontend SPA is built as a folder, that contains all the required files for
-the application. If you have built the frontend yourself then the content of the "dist"-folder
-within the Frontend development-directory has to be copied into the target-directory of the apache-tomcat.
-If you got the Frontend delivered in form of a archive (elogbookFE.zip) put *its content* into the
-target-directory "elogbookFE" of the apache-tomcat:
-
- <Apache-Tomcat>/webapps/elogbookFE
-
-If the target folder does not exist, it has to be created manually.
-
-==== Deployment of the backend
-The backend REST-services are built in form of a "WAR"-file (to be found
-in the "target"-directory of the MAVEN-directory structure or delivered to you).
-For deployment, this file has to be copied to the directory
-
- <Apache-Tomcat>/webapps
-
-==== Deployment of the database
-Currently there is no automatic mechanism for distributing structural
-or content related changes to the database.
-DB related changes are deployed using database scripts directly in suitable
-DB management applications like *pgAdminIII* or directly in the *postgres-console*.
-These script can be found in the backend project in the directory
-
- <elogbook backend project directory>/db/postgreSQL
-
-
-. If needed, create the Database and the access role "btbservice" with *"01_add_DB.sql"*
-. The script *"01a_drop_DB_1.0.0.sql"* is only needed for dropping all objects in the db!
-. Use the script *"02_create_DB_1.0.0.sql"* to create all database objects
-. You will probably need to modify the last script *"03_config_DB_1.0.0.sql"* before you run it. Configurate
-your branches, grid territories and your responsibilities here!
-
-TIP: If you want to install the system on an oracle dbms, please take the db scripts from
-the */db/oracle* subfolder.
-
-==== Configuration of the system
-
-===== DB based configuration
-
-. Grid territories - The grid-territories have to be configured in the DB-Table *ref_grid_territory*
-
-Please modify the "03_config_DB_*.sql" script (in "<elogbook backend project dir>\db\postgreSQL" ) to change the
-configuration. (This file is contained in the *oracle* folder as well.
-
-===== Configuration of the webserver
-
-There exists the file *context.xml* in the "conf" subdirectory (*<TOMCAT>/conf*) of the target apache tomcat installation.
-Add the following parameter and resource in order to access the correct backend configuration and to
-gain access to the database:
-
-.context.xml
-[source,xml]
-----
-[...]
- <!-- Uncomment this to disable session persistence across Tomcat restarts -->
-
- <!--Manager pathname=""/>
-
- <Parameter name="environment" override="false" value="Development"/-->
-
- <Parameter name="OK_ELOGBOOK_ENVIRONMENT" override="false" value="Production"/>
-
- <Resource name="jdbc/okBetriebstagebuchDS"
- auth="Container"
- type="javax.sql.DataSource"
- driverClassName="org.postgresql.Driver"
- url="jdbc:postgresql://{dbserver}:5432/{dbname}"
- username="{dbuser}"
- password="{dbpassword}"/>
-
-
- <!-- Uncomment this to enable Comet connection tacking (provides events
- on session expiration as well as webapp lifecycle) -->
-[...]
-----
-
-For oracle please use...
-
-.contextOracle.xml
-[source,xml]
-----
-[...]
- <!-- Uncomment this to disable session persistence across Tomcat restarts -->
-
- <!--Manager pathname=""/>
-
-
- <Parameter name="environment" override="false" value="Development"/-->
-
- <!-- Parameter for different Backend configs -->
- <Parameter name="OK_ELOGBOOK_ENVIRONMENT" override="false" value="Production"/>
-
- <Resource name="jdbc/okBetriebstagebuchDSO"
- auth="Container"
- type="javax.sql.DataSource"
- driverClassName="oracle.jdbc.driver.OracleDriver"
- url="jdbc:oracle:thin:@{orasvr}:1521:{dbname}"
- username="{dbuser}"
- password="{dbpassword}"/>
-
-
- <!-- Uncomment this to enable Comet connection tacking (provides events
- on session expiration as well as webapp lifecycle) -->
-[...]
-----
-
-*{dbserver}*, *{dbname}*, *{dbuser}* and *{dbpassword}* need to be replaced by the actual values.
-
-CAUTION: The postgres database drivers (postgresql-42.0.0.jar or ojdbc7_g.jar} referenced in the *context.xml* needs to be copied from the lib folder within the
-backend source files to the *<TOMCAT>/lib* folder of the target tomcat installation.
-
-(The jar is located in *deploy/lib/*)
-
-CAUTION: If you want to use an oracle db then you have to change
-the file backendConfigProduction: Set "activePersistencyUnit" to "betriebstagebuchORA".
-(see next chapter "Configuration of the backend").
-
-
-===== Configuration of the backend
-
-After the backend war file has been deployed and unpacked inside of the *<TOMCAT>/webapps* folder there are different
- backend config files to be found in the folder *<TOMCAT>/webapps/elogbook/WEB-INF/classes*
-
-* backendConfigCustom.json
-* backendConfigDevLocal.json
-* backendConfigDevServer.json
-* backendConfigProduction.json
-
-The active configuration is chosen by parameter *OK_ELOGBOOK_ENVIRONMENT* (see context.xml above).
-Possible values are:
-
-* *Custom* (for backendConfigCustom.json)
-* *DevLocal* (for backendConfigDevLocal.json)
-* *DevServer* (for backendConfigDevServer.json)
-* *Production* (for backendConfigProduction.json)
-
-After choosing an environment the corresponding json file has to be configured:
-
-.backendConfigXXX.json
-[source,json]
-----
-{
- "portalBaseURL" : "http://{hostname}:{port}/portal/rest/beservice",
- "fileRowToRead": "9",
- "importFilesFolderPath": "/home/btbservice/importFiles",
- "activePersistencyUnit": "betriebstagebuch",
- "applicationAccessRole": "elogbook-access"
-}
-----
-* *portalBaseURL* - The portal base url should point the the address where the portal backend (the *Auth&Auth*-Server)
- can be accessed. This is important for the authorization of the backend services.
-* *fileRowToRead* - Defines the line number inside the import-file of the relevant text line.
-* *importFilesFolderPath* - Defines the exchange directory for the import functionality
-* *activePersistencyUnit* - Defines the active part of the file persistenc.xml. Choose *"betriebstagebuchORA"* for the
- usage of OracleDB and *"betriebstagebuch"* to use PostgreSQL.
-* *applicationAccessRole* - Defines the role for which users are fetched (For the selection in shift-change-dialog).
-
-
-== Design Decisions
-
-All architecture decisions based on the Architecture Committee Handbook. There are no deviations.
-
-== Risks and Technical Debts
-
-(Currently there aren't any known issues)
-
-<<<
diff --git a/src/main/asciidoc/howto/howtoRun.adoc b/src/main/asciidoc/howto/howtoRun.adoc
index e204555..6ab858a 100644
--- a/src/main/asciidoc/howto/howtoRun.adoc
+++ b/src/main/asciidoc/howto/howtoRun.adoc
@@ -94,10 +94,18 @@
For SSL access, use the ldaps protocol and the appropriate port, e.g. ldaps://myserver.example.com:636
* *ldap-sync*:
+** *db-id-mapping*:
+*** *telephone-number-id*: the primary ID for "telephone number" row in table REF_COMMUNICATION_TYPE.
+(Default: 1) If set to -1 ldapmapping is disable for "telephone number"
+*** *mail-id*: the primary ID for "mail" row in table REF_COMMUNICATION_TYPE.
+(Default: 2) If set to -1 ldapmapping is disable for "mail"
+** *scheduling*:
+*** *enabled*: (true or false) Switches LDAP synchronisation on/off
+*** *cron-expression*: http://www.quartz-scheduler.org/documentation/quartz-2.3.0/tutorials/crontrigger.html[Cron Trigger Tutorial] +
+Spring Cron only takes 5 parameters not 6, the year is exlcuded! +
+Examples: '*/10 * * * * *' = every 10 seconds, '0 0 */3 ? * *' = every 3 hours, '0 0 0 * * ?' = every day at midnight.
** *attribute-mapping*:
-*** [not changeable variable in contact base data modul]: [attribute field name in your LDAP to be mapped].
-All possible mappings are:
-
+*** [not changeable variable in contact base data modul]: [attribute field name in your LDAP to be mapped].All possible mappings are:
[source,yml]
----
uid: uid
@@ -110,17 +118,6 @@
telephone-number: phone
----
-** *db-id-mapping*:
-*** *telephone-number-id*: the primary ID for "telephone number" row in table REF_COMMUNICATION_TYPE.
-(Default: 1) If set to -1 ldapmapping is disable for "telephone number"
-*** *mail-id*: the primary ID for "mail" row in table REF_COMMUNICATION_TYPE.
-(Default: 2) If set to -1 ldapmapping is disable for "mail"
-** *scheduling*:
-*** *enabled*: (true or false) Switches LDAP synchronisation on/off
-*** *cron-expression*: http://www.quartz-scheduler.org/documentation/quartz-2.3.0/tutorials/crontrigger.html[Cron Trigger Tutorial] +
-Spring Cron only takes 5 parameters not 6, the year is exlcuded! +
-Examples: '*/10 * * * * *' = every 10 seconds, '0 0 */3 ? * *' = every 3 hours, '0 0 0 * * ?' = every day at midnight.
-
* *authnauth-sync*:
** *attribute-mapping*:
*** *lastname*: (true or false) Switches AuthNAuth synchronisation of field Lastname on/off
@@ -133,6 +130,7 @@
** *technical-username*: A technical user from the AuthNAuth modul doesn't have to be an admin
** *technical-userpassword*: Technical user password
+
* *server*:
** *port*: (Default: 9155) Port which is used for this backend server
** *max-http-header-size*: Maximum size for the http-headers
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 @@
// 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 @@
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 @@
tblContact.setId(5L);
tblContact.setUuid(UUID.randomUUID());
tblContact.setNote("Notiz");
- tblContact.setContactType("E_P");
+ tblContact.setContactType("2EP");
return tblContact;
}
@@ -570,7 +570,7 @@
ContactDto contactDto = new ContactDto();
contactDto.setUuid(UUID.randomUUID());
contactDto.setNote("Notiz");
- contactDto.setContactType("E_P");
+ contactDto.setContactType("2EP");
return contactDto;
}
