Configuration Manager - Database Version

This page will provide you more information about the Configuration Manager - Database Version and the migration process of from properties file to a database.

Motivational aspects

Related Issues:

• INT-14 - Getting issue details... STATUS
• INT-15 - Getting issue details... STATUS
• INT-16 - Getting issue details... STATUS
• CCS-10 - Getting issue details... STATUS

Changes performed

The previous existing epsos.properties file was replaced by a database that stores the properties in a simple table with just two columns (name and value of property).

In order to support this change the implementation of Configuration Manager was changed, and now it accesses the database for storing and revtrieving properties.

Currently it is compatible with MySQL and Postgres instances, as it bundles the respective JDBC connectors. For Oracle, you need to supply Oracle thin client JDBC jar in your Tomcat lib directory or bundle it with your national connector implementation.

(For compability with other databases please contact us).

Setup

1. Database setup

The Configuration manager will use hibernate to connect to the database, so in order to set it up you will need to create one (e.g: "epsos.properties"), you are free to select the database name. In MySQL, the database name does not support dots ('.'), so the database should be named "epsos_properties".

Example: 

CREATE DATABASE <database_name>;

2. Hibernate file setup

Once you have created the database, you need to configure your Hibernate file (adjust database name and other properties), named configmanager.hibernate.xml, available in your EPSOS_PROPS_PATH location.

In case you're using MySQL, to avoid a "portalb is temporarily unavailable" error message in the Portal due to MySQL's wait_timeout being exceeded (that is, no requests made during that time), you should use the following setup:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://databasehost:3306/epsos_properties?useUnicode=true&amp;characterEncoding=UTF-8&amp;useFastDateParsing=false</property>
        <property name="hibernate.connection.username">username</property>
        <property name="hibernate.connection.password">password</property>
         
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">yes</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
         
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
            <property name="hibernate.c3p0.min_size">1</property>
            <property name="hibernate.c3p0.max_size">50</property>
            <property name="hibernate.c3p0.timeout">7200</property>
            <property name="hibernate.c3p0.max_statements">50</property>
            <property name="hibernate.c3p0.validate">true</property>
        <property name="hibernate.c3p0.idle_test_period">30</property>
        <property name="hibernate.c3p0.preferredTestQuery">SELECT 1 FROM DUAL</property>
        <mapping class="eu.epsos.configmanager.database.model.Property"/>
    </session-factory>
</hibernate-configuration>

Properties Description

How to migrate

1. Migration Tool

In order to migrate your previous configuration files to the database, you may use a tool developed for this purpose.

In order to run this tool you should have all the following tasks performed:

• Create Database (and user to access it);
• Setup EPSOS_PROPS_PATH;
• Create / modify and place configmanager.hibernate.xml file in EPSOS_PROPS_PATH;

This tool support both previous existent properties files, such as epsos.properties and epsos-srdc.propeties, that you can specify, using the -f flag, with the following parameters:

• -f epsos : for epsos.properties;
• -f srdc : for epsos-srdc.properties;

You may now run the tool at any location, with the following command (you also need to specify the file flag in the command):

java -jar ConfigManagerMigrationTool.jar -f  epsos | srdc

For Oracle, run the tool supplying also the Oracle thin client JDBC jar:

java -cp ConfigManagerMigrationTool.jar:ojdbc6-11.2.0.1.jar pt.spms.epsos.main.MigrateProperties -f epsos | srdc

It is advised to brefly look and verify the conversion result at the database, for that please use the tools presented in point 4.

You may download the tool here:

Tool Source Code

The tool code source is available at (under the tools folder):

git clone https://code.google.com/p/epsos-common-components

4. Database access Tools

To view and modify the properties directly you may use some available software, for example:

MySQL

• MySQL Workbench (Available at: http://www.mysql.com/products/workbench/)

Postgres

• pgAdmin (Available at: http://www.pgadmin.org)

Oracle

• Squirrel (Available at: http://squirrel-sql.sourceforge.net/)