easyTravel - using other RDBMSes

[Karolina_Linda]

By default, easyTravel uses an internal Derby Database on the Java side and an SQL Server Compact internal database on the .NET side. However, you can change this to use other types of RDBMS, this page describes how you can configure this.

Java

Connection settings

In the EasyTravel installation (dist) folder, search for this file:

resources/easyTravelConfig.properties

 These properties control the Database:

# for starting the internal Derby database in the Launcher
config.internalDatabaseEnabled=true
config.internalDatabasePort=1527

# how to connect to the Java database, user/password can be empty on Derby
config.databaseDriver=org.apache.derby.jdbc.ClientDriver
config.databaseUrl=jdbc:derby://localhost:1527/easyTravelBusiness;create=true
config.databaseUser=APP
config.databasePassword=APP

The above values are part of the standard easyTravel installation.

They can be changed to use any database desired. The following sets of properties have been
tested against DB/2, SQLServer and Oracle DBMS.

⚠️If external RDBMS is on the same machine, the property config.internalDatabasePort=1527
must point to some free port. easyTravel must start a derby instance, even when it is using external RDBMS.

DB/2

config.internalDatabaseEnabled=false

config.databaseDriver=com.ibm.db2.jcc.DB2Driver
config.databaseUrl=jdbc:db2://dbserv:50000/easyTravelBusiness
config.databaseUser=easytraveluser
config.databasePassword=labpass

Oracle

 ⚠️Oracle versions 12 is only supported in easyTravel builds 2542 or higher due to a required newer version of Hibernate (see HHH-9044). 

config.internalDatabaseEnabled=false

config.databaseDriver=oracle.jdbc.OracleDriver
config.databaseUrl=jdbc:oracle:thin:$user_name/$user_pass@dbserv:1521:easyTravelBusiness
config.databaseUser=easytraveluser
config.databasePassword=labpass

⚠️ easyTravelBusiness is an Oracle SID $user_name and $user_pass should be filled with real values

Using Oracle service:

config.databaseUrl=jdbc:oracle:thin:@//dbhost:1521/easytravel1

User and password must be provided as parameters. easytravel1 is service name

Warning! Due to issue ORA-01882: timezone region not found while connecting to Oracle workaround there is workaround, jvm parameters have to contain -Duser.timezone=GMT. Of course it should be adjusted to your timezone. Example is below:

config.javaopts=-Djava.util.logging.config.file=../resources/logging.properties,-Duser.timezone="+01:00"

config.frontendJavaopts=-Xmx160m,-Duser.timezone="+01:00"

config.backendJavaopts=-Xmx64m,-Duser.timezone="+01:00"

also files launching jvm like weblauncher/weblauncher.sh should look like below:

$JAVA_BIN $COVERAGE  -Duser.timezone='+01:00'  -Dcom.dynatrace.easytravel.install.dir.correction=.. -jar ../com.dynatrace.easytravel.weblauncher.jar "$@"

Example code for the DB user creation:

create user easytraveluser identified by labpass;

grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, 
CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, 
CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE
to easytraveluser;

SQL Server

ℹ️Locale

Note that easyTravel will insert a number of locations with non-ASCII characters as part of the name, e.g. Århus. Make sure you set up the SQL Server database with a local that supports these characters.

SQL_Latin1_General_CP1_CI_AS is reported to work, also any UTF-8 or UTF-16 based encoding should be fine.

There are at least two different JDBC drivers available for SQL Server, depending on which one you want to use the following settings apply:

SQL Server with JTDS-JDBC Driver

config.internalDatabaseEnabled=false

config.databaseDriver=net.sourceforge.jtds.jdbc.Driver
config.databaseUrl=jdbc:jtds:sqlserver://dbserv5:1433/easyTravelBusiness;instance=dynasqlserver
config.databaseUser=easytraveluser
config.databasePassword=labpass

⚠️ Parameter 'instance' is optional and can be omitted.

There are two stored procedures used by easyTravel but not created automatically:

* sp_findJourneys - if you intend to use the FindJourneysSqlServerQueryOverride easyTravel plugin, you can either setup the sp_findJourneys stored procedure yourself, or use the provided source file sp_findJourneys.sql, which is located in the plugin's JAR file root.

* sp_verifyLocation - used by SlowAuthentication plugin. You can create the procedure using provided source file in the sp_verifyLocation.sql which is located in the plugin's JAR file root.

SQL Server with Microsoft JDBC Driver

config.internalDatabaseEnabled=false

config.databaseDriver=com.microsoft.sqlserver.jdbc.SQLServerDriver
config.databaseUrl=jdbc:sqlserver://[dbhostname]:1433;databaseName=easyTravelBusiness
config.databaseUser=easytravel
config.databasePassword=easytravel

easyTravel cannot ship the Microsoft JDBC driver because of license considerations. To make it work, you need to complete the following steps:

• Download the MS SQL JDBC drivers at: http://go.microsoft.com/fwlink/?LinkId=245496
• Unpack "mssql-jdbc-7.0.0.jre8.jar" to C:\Program Files\dynaTrace\easyTravel (x64)\lib
• Unpack "META-INF/MANIFEST.MF" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
• Add "lib/mssql-jdbc-7.0.0.jre8.jar" to the end of the Class Path in MANIFEST.MF
• Overwrite the MANIFEST.MF in "com.dynatrace.easytravel.database.jar" with your changed file.

⚠️If you are using a new edition of SQL Server you might encounter an error which looks like "Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'". In such a case, you need to:

• Unpack "META-INF/persistence.xml" from "com.dynatrace.easytravel.database.jar" in your main easyTravel directory
• Modify this file by adding a new property in the properties section:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>

• Overwrite the persistence.xml in "com.dynatrace.easytravel.database.jar" with your changed file.

MySQL

⚠️Using easyTravel with MySQL leads to errors about duplicate entries with special characters in the Journey/Location-Name, e.g. "Ibagué" and "Ibague"! (Detailed informations: https://bugs.mysql.com/bug.php?id=57877). There are two possible solutions for this:

1. Setting collation to utf8_bin, as described below should remove this problem (tested with MySQL 5.5, 5.6).
2. You can try to adjust the file "data/Cities.txt" inside the file com.dynatrace.easytravel.database.jar, by removing following cities: Vitória, Ibagué, Århus, Székesfehérvár, Mérida, Gdañsk, Córdoba, Skövde, Tabor, Malmo, Villa Maria.

config.internalDatabaseEnabled=false config.databaseDriver=com.mysql.jdbc.Driver config.databaseUrl=jdbc:mysql://<client-host-ip>/easyTravelBusiness config.databaseUser=root config.databasePassword=labpass

How to configure MySQL

Create a database and allow access from other machines:
Adjust config at /etc/mysql/my.cnf, in section [mysqld]:

• add

  character-set-server = utf8
  collation-server = utf8_bin

• comment out 
  bind-address = 127.0.0.1

mysql -u root -p

create database easyTravelBusiness;
GRANT ALL PRIVILEGES ON *.* TO root@'<client-host>' IDENTIFIED BY 'labpass';

http://dev.mysql.com/doc/refman/5.0/en/, Install MySQL on Ubuntu 14.04, Install MySQL on Ubuntu 16.04

Database schema

easyTravel will try to automatically create the schema, which works for built-in Derby and the above mentioned DBMS. This is done with the hibernate feature hibernate.hbm2ddl.auto set to update.

It also creates the database content on startup. Existing content will not be erased.

.NET

Currently, two databases are supported:

• SQL Server Compact Edition (which is default)
• SQL Server

to switch to SQL Server you have to modify dotNetPaymentBackend/web.config.

<connectionStrings>
    <add name="PaymentEntities" connectionString="metadata=res://*/AppData.PaymentDB.csdl|res://*/AppData.PaymentDB.ssdl|res://*/AppData.PaymentDB.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|DataDirectory|PaymentData.sdf;Password=easyTravelDB;Persist Security Info=True&quot;" providerName="System.Data.EntityClient"/>
    <add name="SQLConnection" connectionString="Data Source=|DataDirectory|PaymentData.sdf;Password=easyTravelDB;Persist Security Info=True" providerName="System.Data.SqlServerCe.3.5" />
    <!--add name="SQLConnection" connectionString="Data Source=**source**;Database=**database**;User ID=**user**;Password=**password**" providerName="System.Data.SqlClient" />
    <add name="PaymentEntities" connectionString="metadata=res://*/dotNetPaymentBackend.AppData.PaymentDBSQLServer.ssdl|res://*/AppData.PaymentDB.csdl|res://*/AppData.PaymentDB.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=**source**;Initial Catalog=**database**;User ID=**user**;Password=**password**;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" /-->
  </connectionStrings>

at the bottom of the file you will find the element "connectionStrings". There remove the currently active connection-strings for "PaymentEntities" and "SQLConnection" and uncomment the other two, which are prepared for SQL Server.
Then fill out the place-holders with the corresponding values:

• **source** --> server
• **database** --> datebase-name
• **user** --> db-user
• **password** --> db-password

Make sure to modify both the SQLConnection and PaymentEntities.

Currently Schema has to be set to "dbo" which is the default value of SQL Server.

To create the tables on SQL Server with the correct constraints the following Script can be used:

CREATE TABLE [CreditCard] (
  [number] nvarchar(32) NOT NULL
, [user] nvarchar(64) NOT NULL
, [validThrough] datetime NOT NULL
);
GO
CREATE TABLE [Payment] (
  [bookingId] nvarchar(64) NOT NULL
, [ccNumber] nvarchar(32) NOT NULL
, [amount] money NOT NULL
, [date] datetime NOT NULL
);
GO
CREATE TABLE [Report] (
  [tenant] nvarchar(255) NOT NULL
, [date] datetime NOT NULL
, [xmlData] ntext NULL
);
GO
ALTER TABLE [CreditCard] ADD CONSTRAINT [PK__CreditCard__0000000000000084] PRIMARY KEY ([number]);
GO
ALTER TABLE [Payment] ADD CONSTRAINT [PK_Payment] PRIMARY KEY ([bookingId]);
GO
ALTER TABLE [Report] ADD CONSTRAINT [PK__Report__000000000000006C] PRIMARY KEY ([tenant],[date]);
GO
CREATE UNIQUE INDEX [UQ__CreditCard__0000000000000089] ON [CreditCard] ([number] ASC);
GO
CREATE UNIQUE INDEX [UQ__Payment__000000000000002D] ON [Payment] ([bookingId] ASC);
GO
ALTER TABLE [Payment] ADD CONSTRAINT [FK_CREDITCARD_PAYMENT] FOREIGN KEY ([ccNumber]) REFERENCES [CreditCard]([number]) ON DELETE NO ACTION ON UPDATE NO ACTION;

[ChadTurner]

Thank you for the information @Karolina_Linda