Database

This documentation describes the steps required to store the COMPASS data in an SQL database, and how to access these tables from COMPASS. The used tables, indexes and views/synonyms are described in detail.

Accessing case data and result data includes the information about the case and result data. This data might be accessed or created by your own application: Case data (input interfaces) can be produced by your application and be inserted into the CaseData table, while results (result interfaces) can be retrieved from the ResultData table.

Setting up a Database for special Market Versions describes configurations for some market versions.

Cache describes an additional feature of COMPASS, namely the cache implementation, which enables to store the state of a COMPASS client application temporarily, in order to establish COMPASS in a “stateless” environment, e.g. in a cloud.

Please also have a look at the document Database Utilities, which describes some utilities to work with the COMPASS database you might find useful.

Compared to previous COMPASS versions, the C40_Author table has an additional column, which is used to ensure a single login to the RuleManager. This column will contain NULL for all rows, unless a user is logged in – in which case a timestamp is found in this column for the user. Knowledge Data contains the DDL for the table.
Should you desire to update the existing table, use one of the following SQL statements:

or

depending on the data types supported by the chosen RDBMS.

By default, most RDBMS create new columns nullable. Make sure this is the case for the new column!

This document will be mainly read during the installation process. The installation of COMPASS Service and its components is described in the document Getting Started

Please also have a look at the document Database Utilities, which describes some utilities to work with the COMPASS database you might find useful.

A case is the data from an application; it comprises data about the Insured Person like name, age, occupation, but also about the benefits like sum insured, duration of the policy, etc.

A System is the set of all data needed by COMPASS to capture, display, assess and save a case.

This includes

The cases and assessment results are referred to as Case Data and Result Data; the other data is part of the Knowledge Data.

COMPASS accesses the database to store or modify 3 different sets of data: Knowledge Data (rules and UI definitions), Case Data and Cache. Of these three types of data, Knowledge Data is mandatory, while storing Case Data in tables is optional, and the use of Cache table is only required in the particular situation that COMPASS captures the user-input in a stateless manner via a fullRequest. These three types of data can be stored in the same environment, but also can be stored in different environments, even in different database systems. This means that all data could be stored in one database, say DB2, but also separated: Case Data in MySQL, Knowledge Data in DB2 and Cache table in MS SQL Server.

Case Data is stored in 3 tables:

Case Data table contains Input Interfaces, sorted by the System and a Subsystem (SubID). The SubID enables the administration of several sets of cases in the same table, e.g. a set of cases from the life environment plus a set of test cases, or a set of cases per user. This way, each user only sees the relevant cases.

The table ResultData has a similar structure. Sorted by System and SubID the assessment results (Result Interfaces) for each case are stored here.

The table AuthorCase stores the names of the users who have modified a Case and / or a Result in any of the two previously named tables.

The cache table is described in Cache:

The Knowledge Data is stored in 11 tables:

The Knowledge Data is designed as a set of objects and their relations. The first 7 tables store the objects with their name, index, keys and attributes. The Relation table stores the relations between those objects. The GroupID table contains the highest allocated id_ for each type of objects. The Author table stores all names of users, who have done modifications to one of the other tables. The table Search stores information for the search engine, which works for some types of objects (disorders, pursuits, etc.).

Before starting with the creation of tables, one must be aware of the systems that are needed, and which database systems are going to store the data for each system.

Example:

A company has an administration system, which uses COMPASS. This environment is separated completely from the testing environment. The productive database system is DB2. The System is called ProdSystem.

Two more systems are required for two underwriters who work on their local PCs. One of them is developing a new Point-of-Sale (POS) system, while the other is modifying assessment rules for the Head Office version and performs first tests with his modified Knowledge Data. The underwriter „POS“ works on a local MySQL, and saves his case data on a Sybase in the network. This system is called TestPOS. The underwriter „HeadOffice“ works completely on the Sybase in the network. This system is called TestHeadOffice.

The following tables have to be created:
DB2: Tables for the Knowledge Data and Case Data of the system ProdSystem.
MySQL: Tables for the Knowledge Data of the system TestPOS.
Sybase: Tables for the Knowledge Data of the system TestHeadOffice and tables for the Case Data for the systems TestPOS and TestHeadOffice.

The following picture explains this architecture:

When you know which systems store their data on which database systems, the required JDBC drivers have to be found, and made available to COMPASS via property file (see Settings in the compass.properties file). Alternatively COMPASS can use DataSources to retrieve database connections (see DataSources - COMPASS Service).

Each system needs tables to store the Knowledge Data. When cases and/or results are being stored in databases, additional tables for Case Data must exist. These tables can be used from various COMPASS systems.

For each table, views or synonyms can be created. COMPASS accesses the tables via the name in the property file. This name can be a synonym name, a view name or a table name.

Not all database systems support views or synonyms, or the data types numeric(14), varchar, etc., therefore the attached DDL scripts have to be adapted to the particular database system.

A list of the tables with their structure follows:

To exchange Knowledge Data between Gen Re and our COMPASS customers, there is a standard format of 11 flat files, which correspond to the 11 tables as described in Knowledge Data File-names contain the table-names , with the suffix .txt, e.g. C40_Relation.txt is the file that contains the data belonging to the C40_relation table

The files are in the following format:

To enable a quick exchange of data between our customers and Gen Re, the process of downloading and loading data should be automated, and setup and tested at an early stage of integration.

The compass.properties file contains several variables needed by COMPASS to find the correct data for a system. Those variables are listed here; in this example for the system Systemname, RDBMS is of type Oracle:

Access of Case data:

Systemname.CaseData.driver=[name of your JDBC-driver, i.e. oracle.jdbc.driver.OracleDriver]
Name of the database driver

Systemname.CaseData.password=[Passwort for DB-user]
Password for the user that connects to the database.

Systemname.CaseData.url=[JDBC-URL to access the database, i.e. jdbc:oracle:thin:@DEKOWD155E:1521:ORCL]
JDBC-URL of the database (different for each environment, database system and JDBC driver)

Systemname.CaseData.user=[Name of DB-user]
Name of the user that connects to the database.

Systemname.CaseData.reconnect=true
If this property is set to “true”, then the database connection will be reconnected, if it has been dropped or is otherwise unavailable. This should be used e.g. when the database server drops unused connections after a certain time (e.g. 30 minutes). Set this property only if required, as it slows down the database performance. When this property is not in included in the compass.properties-file, or its value is set to anything other than “true”, the reconnect is not attempted.

Systemname.CaseData.viewAuthor=C40_AuthorCase
The Author table is known under this name (table name, view name or synonym name)

Systemname.CaseData.viewCaseData=C40_CaseData
The Case table is known under this name (table name, view name or synonym name)

Systemname.CaseData.viewResultData=C40_ResultData
The Result table is known under this name (table name, view name or synonym name)

Accessing Knowledge Data:

Systemname.database.driver=[name of your JDBC-driver, i.e. oracle.jdbc.driver.OracleDriver]
Name of the database driver

Systemname.database.password=[Passwort for DB-user]
Password for the user that connects to the database.

Systemname.database.real=java.lang.Float
java.lang.Float or java.lang.Double; responsible for the representation of decimal numbers

Systemname.database.url=[JDBC-URL to access the database, i.e. jdbc:oracle:thin:@DEKOWD155E:1521:ORCL]
JDBC-URL of the database (different for each environment, database system and JDBC driver)

Systemname.database.user=[Name of DB-user]
Name of the user that connects to the database.

Systemname.database.reconnect=true
If this property is included with the value “true”, then the connection described by the parameters above will be reconnected, if it has been dropped or is otherwise unavailable. This feature can be used e.g. when the database server drops unused connections after a certain time (e.g. 30 minutes). Set this property only if required, as it slows down the database performance. When this property is not in included in the compass.properties-file, or its value is set to anything other than “true”, the reconnect is not attempted.

Systemname.viewname.Author=C40_Author
The Author table is known under this name (table name, view name or synonym name)

Systemname.viewname.BoolOrInt=C40_BoolOrInt
The BoolOrInt table is known under this name (table name, view name or synonym name)

Systemname.viewname.Double=C40_Double
The Double table is known under this name (table name, view name or synonym name)

Systemname.viewname.Group=C40_GroupID
The GroupID table is known under this name (table name, view name or synonym name)

Systemname.viewname.Index=C40_Index
The Index table is known under this name (table name, view name or synonym name)

Systemname.viewname.Keys=C40_Keys
The Keys table is known under this name (table name, view name or synonym name)

Systemname.viewname.Name=C40_Name
The Name table is known under this name (table name, view name or synonym name)

Systemname.viewname.PersObj=C40_PersObj
The PersObj table is known under this name (table name, view name or synonym name)

Systemname.viewname.Relation=C40_Relation
The Relation table is known under this name (table name, view name or synonym name)

Systemname.viewname.Search=C40_Search
The Search table is known under this name (table name, view name or synonym name)

Systemname.viewname.String=C40_String
The String table is known under this name (table name, view name or synonym name)

Remark: When running under Unix, table-, synonym- or view-names are case-sensitive - this may also hold true on Windows depending on your configuration of the operating system.

Example

MySQLLocal is the name of a System. For this system the following properties are required in the file compass.properties. Please be aware that for other languages/encodings, e.g. Korean, Chinese et al., additional properties may be required (see Setting up a Database for special Market Versions).

Entries describing the Case Data:

Entries describing the Knowledge Data:

This example shows that the Knowledge Data and the Case Data are stored in the same database: a MySQL running on localhost, port 3306, database is rex. The user root with password "your_password" can access these tables. All tables have the default names.

Remark: When using a DataSource to establish the connection for Case Data and/or Knowledge Data, several of the above properties are not used: driver, user, password and url, i.e. are ignored.

Language/Encoding Support: There are further properties that can be used for special languages and encodings. These properties are not mandatory; a default encoding from the system properties of the operating system is taken if the properties are not defined in compass.properties.

Please be aware that the values of the following encoding-properties have to be valid Java names for the corresponding encodings!

3 Properties required for the Knowledge Data:

Systemname.database.characterEncoding=UTF-8
Encoding in which the Strings in the Name-table are encoded. The data to be loaded into the tables will be encoded in this encoding.

Systemname.database.databaseEncoding=ISO8859_1
Encoding in which the database is set up.

Systemname.database.specialeurosignhandling=false
When ”true”, the Euro-sign “€” is coded as “&EURO” in the database, otherwise no special handling is done.

4 Properties describing Case Data:

Systemname.CaseData.characterEncoding=UTF-8
Encoding in which the Strings in the Case Data and Result Data-tables are encoded.

Systemname.CaseData.databaseEncoding=ISO8859_1
Encoding in which the database for the cases / results is set up.

Systemname.CaseData.qsamEncoding=ISO8859_1
For the download- and load-utilities (see Down-/ Upload of Cases and Assessment Results) a description of the encoding in which the cases and results are written to / read from file.

Systemname.CaseData.specialeurosignhandling=false
When ”true”, the Euro-sign “€” is encoded as “&EURO” in the database, otherwise no special handling is done.

Systemname.CaseData.rightPadChar=false
When ”true”, the values of char-columns are right-padded with blanks up to the length of the column, when COMPASS accesses these tables. This is required for certain drivers and database systems, e.g. Oracle.

Remark: In most environments, the properties databaseEncoding, characterEncoding and qsamEncoding should be used together.

You can access these tables from other programs. The access of the Case Data is explained in detail in the next chapter. Knowledge Data should only be accessed in read-only mode from other applications. Most probably only the search data and the keys (tables Keys and Search) are being accessed from other applications.

To use this feature, six entries are required:

Systemname.CacheData.cacheType=SQL
This entry defines a type of the cache implementation. Currently, supports only SQL.

Systemname.CacheData.viewCacheData=C40_Cache
This entry defines a table name in which the state of a client should be saved
This entry needs to be specified for both connection-types PLAIN and CONNECTION_POOL (see below).

Systemname.CacheData.driver=org.gjt.mm.mysql.Driver
Name of the database driver (different for each JDBC driver)
This entry needs to be specified for both connection-types PLAIN and CONNECTION_POOL (see below).

Systemname.CacheData.user=ROOT_POS
Name of the user that connects to the database
This entry needs to be specified for both connection-types PLAIN and CONNECTION_POOL (see below).

Systemname.CacheData.password=
Password of the user defined above
This entry needs to be specified for both connection-types PLAIN and CONNECTION_POOL (see below).

Systemname.CacheData.url=jdbc:mysql://localhost:3306/COMPASS_CACHE
URL of the database (different for each environment, database system and JDBC driver)
This entry needs to be specified for both connection-types PLAIN and CONNECTION_POOL (see below).

Systemname.CacheData.rightPadChar=false
When ”true”, the values of char-columns are right-padded with blanks up to the length of the column, when COMPASS accesses these tables. This is required for certain drivers and database systems, e.g. Oracle. Default is "false".

The last 5 properties are ignored when working with a DataSource.

CacheConnectionType =[ CONNECTION_POOL | PLAIN | DATASOURCE ]

When CacheConnectionType is not specified, default behaviour is CacheConnectionType.PLAIN.

If you decide to use the implemented connection pool for the Cache Database, the following properties are relevant:

1) For MySQL database system:

2) For Oracle database system:

3) For MS-SQL Server:

The timestamp in the fourth column can be used to identify cancelled applications. Such rows can be removed after a specified time. Such a deletion is responsibility of the COMPASS customer.

For certain market versions the compass.properties requires special system- and database-dependant properties. In this chapter we will focus on the properties for the database systems MySQL and Oracle.