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.

Information

New ConfigurationManager Database Migration Tool 1.1 available.

Now with support for epsos-srdc.properties file also.

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 - MySQL / Postgres

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:

Sample configmanager.hibernate.xml

<?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>

You may also download it here.

Note that in the connection URL the parameters are separated by an encoded ampersand (&) rather than a literal ampersand (&).

Properties Description

PROPERTY NAME	PURPOSE
hibernate.dialect	Hibernate dialect
hibernate.connection.datasource	JNDI Resource name for datasource
hibernate.current_session_context_class	Supply a custom strategy for the scoping of the "current" Session.
hibernate.cache.provider_class	The classname of a custom CacheProvider.
show_sql	Write all SQL statements to console. This is an alternative to setting the log category org.hibernate.SQL to debug.
hibernate.hbm2ddl.auto	Automatically validates or exports schema DDL to the database when the SessionFactory is created. With create-drop, the database schema will be dropped when the SessionFactory is closed explicitly.

Important

You should adjust this file to your scenario. Specially the database name, the dialect and the JDBC URL and username / password.

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):

Run Tool Command

java -jar ConfigManagerMigrationTool.jar -f  epsos | srdc

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

Run Tool Command

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:

	File	Modified
Labels tool Preview View	Java Archive ConfigManagerMigrationTool.jar Revised 1.1 version, with support for epsos-srdc.properties file	Jan 11, 2013 by Marcelo Fonseca

Tool Source Code

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

Too Source Code

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/)

For more questions and issues found in the above described procedures, please leave a comment bellow or create a JIRA issue.

Browser not supported