Installation with external database

Installation package

For using the on premise version will receive an installation package containing:

an account to the Peak Solution docker registry
zip archive with the necessary configuration files for the application
zip archive including the necessary database scripts
a license file

Prepare database

warning

Make sure that the server application components and the database are running in the same time zone! This is necessary to run the application properly, when working with different timezones is enabled.

Oracle

In the database folder, go into “oracle/initial” folder and you will see the following scripts:

/ls
   01_CrTblspUser_TMS.sql
   02_DB_Ojekte.sql
   03_GrantTabs.sql
   04_indexTblspace.sql
   05_initial_Data.sql
   06_initial_index.sql
   07_createIndex.sql
   08_CrFullTestSearchIndex.sql

Create table spaces and users (DBA user)

Edit the first file 01_CrTblspUser_TMS.sql to change the data file location for your table spaces according to your system settings. This will create

the necessary table spaces
the user tms (table owner)
the user u4tms (application user)

Create necessary tables (user tms)

Now use the created tms user account to run the following second script 02_DB-Objekte.sql.

Grant tables to application user (user tms)

Run the third script 03_GrantTabs.sql to grant table access to u4tms.

Build indexes (user tms)

Run the script 04_indexTblspace.sql.

Create initial application meta data (user tms)

Running the script 05_initialData.sql will create the necessary application.

Enable Oracle fulltext library (DBA user)

The script 06_initial_index.sql will activate the Oracle fulltext search module. Make sure to replace "@" with your specific database installation path.

@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK;

Grant Fulltext search creation rights to user tms (DBA user)

The script 07_createIndex.sql will give the user tms the necessary rights to use the fulltext search module. Also here make sure to replace "@" with your specific database installation path.

@?/ctx/admin/defaults/dr0defin.sql "AMERICAN";

Create TMS application full text search index (user tms)

Run the last script 08_CrFullTextSearchIndex.sql

Congratulations, you have successfully set up the database for the TMS application.

MS SQL server

In the database folder, go into “mssql/initial” folder and you will see the following scripts:

/ls
   01_initial_Database.sql
   02_DB_Ojekte.sql
   03_initialData.sql
   04_initial_searchIndex.sql
   05_initial_tblspace.sql

Copy those scripts to MS SQL database server.

Create initial database user and table spaces (DBA user)

Edit the first file 01_initial_Database.sql to change the data file location, database owner and password. Make sure the folder exists, has enough space, and sufficient permissions.

After adapting to your needs, please execute the script as DB Administrator, e.g. in the MS SQL Server Management Studio SQL script console.

The script will create the necessary table spaces as well as the user TMS (table owner) and U4TMS (application user).

Create necessary tables and views (user TMS)

Now use new created (TMS) account to run the following second script 02_DB-Objekte.sql. If you created different user credentials adapt accordingly.

Insert necessary application meta data (user TMS)

You are still connected via the database owner account (TMS) to run the following 3rd script 03_initialData.sql. If you created different user credentials adapt accordingly.

Create fulltext search indexes (user TMS)

You are still connected via the database owner account (TMS) to run the following 4th script 04_initial_prp_searchIndex.sql. If you created different user credentials adapt accordingly.

Congratulations, you have successfully set up the database for the TMS application.

PostGreSQL

In the database folder, go into “mssql/initial” folder and you will see the following scripts:

/ls
   01_CrTblspUser_TMS.sql
   02_DB_Ojekte.sql
   03_IndexTblspace.sql
   04_initialData.sql
   05_CrFullTextSearchIndex.sql

Copy those scripts to PostgresSQL database server.

Creating table spaces, table owner and application user (DBA user, e.g. postgres)

Edit the file 01CrTblspUser_TMS and change the locations of the table spaces to existing folders on the database server and make sure the data base process has write access.

Then run the script as a DBA user. It will create

the necessary table spaces
the user tms (table owner)

There you also can edit the line with CREATE ROLE tms_ to set your companies individual user account name and password.

Create necessary tables (user tms)

Afterward you should login as user tms and run the script 02_DB-Objekte.sql. This will create necessary tables and views.

Build Indexes (user tms)

Run the script 03_IndexTblSpace which will set the table space for all indexes as well as rebuild the indexes.

Create application meta data (user tms)

Run the script 04_initialData.sql to create the necessary meta data needed by the application to start.

Create fulltext search indexes (user tms)

Run the script 05_CrFullTestSearchIndex.sql to create the search index necessary for the application. To support Chinese / Japanese character support make sure to uncommend the following entries:

-- ---------------------------------------
-- if chinese posgresql extension
-- https://github.com/amutu/zhparser/
-- was installed
-- ---------------------------------------
--CREATE INDEX idx_prpsearchdata_zh ON prpsearchdata USING GIN (to_tsvector('zhparser', searchcontent)) TABLESPACE tbs_tms_idx;

-- ---------------------------------------
-- if japanese posgresql extension
-- https://github.com/jaiminpan/pg_jieba
-- was installed
-- ---------------------------------------
--CREATE INDEX idx_prpsearchdata_zh ON prpsearchdata USING GIN (to_tsvector('jiebacfg', searchcontent)) TABLESPACE tbs_tms_idx;

Afterward you have finished the DB set up, congratulations!

Install application

Add Peak Solution docker registry

To be able to retrieve the application images from the Peak Solution application registry login with the credentials given to you.

docker login peak-solution.de

What Happens Next?

Prompt for Username: The CLI will ask for your registry username.
Prompt for Password: It will then ask for your password (or an access token). Note: Characters will not appear on the screen as you type for security reasons.
Success Message: If the credentials are correct, you will see Login Succeeded.

Prepare the installation folder

Unpack the received zip-archive into your chosen installation folder and add the received license file as "tms.l4j" into the same folder. Afterward you should see following files in your installation folder:

/ls
    .env
    auth.properties
    auth.properties-template
    config.properties
    docker-compose.yml
    holiday.properties
    tmsscheduler.properties
    tms.l4j

Change neccessary config parameters

All necessary changes can be done in the ".env" file.

Setting the http application port

Set the web application port for delivering the web application

TMS_WEBAPP_PORT=80

Define administration client for license server

You might define a list of IP addresses which are allowed to connect to the license server administration

TMS_L4JSRV_ADMIN_HOSTS=127.0.0.1

Define the data base jdbc connection URL

Open the .env file and got to the param TMS_DB_URL to set the path

For oracle

TMS_DB_URL=jdbc:oracle:thin:@[host]:[port]:[sid]

For mssql

TMS_DB_URL=jdbc:sqlserver://[host]:[port];databaseName=tms

For PostgresSQL

TMS_DB_URL=jdbc:postgresql://[host]:[port]/tms

Setting the base URL

Tell others under which URL the system will be reached. This will e.g. be used to provide automatic links in emails.

TMS_BASEURL=https://tms1.peak-applications.com

Build and start the application

Make sure you run bash / cmd within the installation folder

docker compose up -d

Afterward the application will be available on the specifified web application port, e.g. http://localhost/tms-webapp.

Installation package​

Prepare database​

Oracle​

Create table spaces and users (DBA user)​

Create necessary tables (user tms)​

Grant tables to application user (user tms)​

Build indexes (user tms)​

Create initial application meta data (user tms)​

Enable Oracle fulltext library (DBA user)​

Grant Fulltext search creation rights to user tms (DBA user)​

Create TMS application full text search index (user tms)​

MS SQL server​

Create initial database user and table spaces (DBA user)​

Create necessary tables and views (user TMS)​

Insert necessary application meta data (user TMS)​

Create fulltext search indexes (user TMS)​

PostGreSQL​

Creating table spaces, table owner and application user (DBA user, e.g. postgres)​

Create necessary tables (user tms)​

Build Indexes (user tms)​

Create application meta data (user tms)​

Create fulltext search indexes (user tms)​

Install application​

Add Peak Solution docker registry​

Prepare the installation folder​

Change neccessary config parameters​

Setting the http application port​

Define administration client for license server​

Define the data base jdbc connection URL​

For oracle​

For mssql​

For PostgresSQL​

Setting the base URL​

Build and start the application​

Installation package

Prepare database

Oracle

Create table spaces and users (DBA user)

Create necessary tables (user tms)

Grant tables to application user (user tms)

Build indexes (user tms)

Create initial application meta data (user tms)

Enable Oracle fulltext library (DBA user)

Grant Fulltext search creation rights to user tms (DBA user)

Create TMS application full text search index (user tms)

MS SQL server

Create initial database user and table spaces (DBA user)

Create necessary tables and views (user TMS)

Insert necessary application meta data (user TMS)

Create fulltext search indexes (user TMS)

PostGreSQL

Creating table spaces, table owner and application user (DBA user, e.g. postgres)

Create necessary tables (user tms)

Build Indexes (user tms)

Create application meta data (user tms)

Create fulltext search indexes (user tms)

Install application

Add Peak Solution docker registry

Prepare the installation folder

Change neccessary config parameters

Setting the http application port

Define administration client for license server

Define the data base jdbc connection URL

For oracle

For mssql

For PostgresSQL

Setting the base URL

Build and start the application