Database Switch

MySQL database

PostgreSQL database

MSSQL database

Oracle database

• Case: ActivityTimeline is configured to use H2 database by default. A customer would like to migrate it to their MySQL database. 
• Q: Could you send directions for doing this? 
• A: Here is the documentation regarding switching a database engine: 
  • ActivityTimeline is pre-configured to use built-in H2 database.
  1. Open activitytimeline/webapps/ROOT/WEB-INF/classes/configuration.properties file and edit it if you'd like to change a database engine to MySQL. 
  2. Make sure MySQL database was installed on your server before and has "timeline" database created. 
  3. It is also required that “timeline” database uses UTF8 character set by default.
  4. Replace everything in configuration.properties file with the following data to connect to MySQL database:

     1. MySQL Database connection URL

        dataSource.url=jdbc:mysql://<MySQL_HOST>:<MySQL_PORT>/<MySQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE>
        dataSource.url=jdbc:mysql://127.0.0.1:3306/timeline?autoReconnect=true&autoreconnect=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8

     2. MySQL Database username

        dataSource.username=at_user

     3. MySQL Database password

        dataSource.password=at_password

     4. Other properties

        dataSource.pooled=true
        dataSource.driverClassName=com.mysql.jdbc.Driver
        dataSource.dbCreate=update
        # MySql 5.5, 5.6, 5.7:
        dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQLDialect
        # MySql 8.0+:
        # dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQL8Dialect

  5. Replace at_user and at_password with actual username/password combination of your MySQL database user.

  6. Restart ActivityTimeline afterwards.

     
     

      If you would like to migrate existing data from H2 database to MySQL, then you would need to use an external tools to migrate the database data like SQuirreL SQL with SQuirreL DB Copy Plugin (first you need to install SQuirreL SQL and on top of that the SQuirreL DB Copy Plugin). Also, you may need to start ActivityTimeline on an empty external database (schema) first for ActivityTimeline to create all required tables and relations.

     H2 database files are located at activitytimeline/bin/atdb folder.

     
     

     IMPORTANT: Upgrade to MySQL v8.0+

     MySQL 8.0 and above have "lead", "rank" and few other words reserved and cannot be used as column names.

     As a result it is required to perform a specific procedure to upgrade MySQL to v8.0.

     1. Open <AT_FOLDER>/activitytimeline/webapps/ROOT/WEB-INF/classes/configuration.properties file for editing and add the following line:

        dataSource.dialect=com.timeline.hibernate.ActivityTimelineMySQL8Dialect

     2. Save it.
     3. Alter database structure (see below)
     4. Restart ActivityTimeline.

     
     

     You should alter the MySQL 5.6/5.7 ActivityTimeline database schema before the upgrade to MySQL 8.0:

     1. Column "rank" of "issue" table should be renamed to "issue_rank"
     2. Table "level" should be renamed to "knowledge_level"
     3. Table "priority" should be renamed to "rlx_at_priority"
     4. Rename the following columns of "project" table:
        1. "priority" to "prj_priority"
        2. "active" to "prj_active"
        3. "lead" to "prj_lead"
     5. Rename the following columns of "worklog_record" table:
        1. "date" to "worklog_date"
        2. "comment" to "worklog_comment"

 

#  PostgreSQL Database connection URL
dataSource.url=jdbc:postgresql://localhost:5432/timeline?autoReconnect=true&autoreconnect=true&useUnicode=true&characterEncoding=utf8&characterSetResults=utf8

# PostgreSQL Database username
dataSource.username=at_user

# PostgreSQL Database password
dataSource.password=at_password

#  Other properties
dataSource.pooled=true
dataSource.driverClassName=org.postgresql.Driver
dataSource.dbCreate=update

.

#  MS SQL Server Database connection URL
# Structure: dataSource.url=jdbc:jtds:sqlserver://;serverName=<MSSQL_HOST>;portNumber=<MSSQL_PORT>;databaseName=<MSSQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE>;integratedsecurity=false;socketTimeout=60000
dataSource.url=jdbc:sqlserver://;serverName=localhost;portNumber=1433;databaseName=timeline;integratedsecurity=false;socketTimeout=60000

# MSSQL Database username
dataSource.username=at_user

# MSSQL Database password
dataSource.password=at_password

#  Other properties
dataSource.pooled=true
dataSource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
dataSource.dialect=com.timeline.hibernate.ActivityTimelineMSSqlServerDialect
dataSource.dbCreate=update


# -------------------------------------------------

# Or Alternative jTDS MSSQL driver

# Alternative MSSQL Database connection URL - jTDS driver
# Structure: dataSource.url=jdbc:jtds:sqlserver://<MSSQL_HOST>:<MSSQL_PORT>;databaseName=<MSSQL_DATABASE_NAME_FOR_ACTIVITYTIMELINE>
dataSource.url=jdbc:jtds:sqlserver://localhost:1433;databaseName=timeline

# MSSQL Database username
dataSource.username=at_user

# MSSQL Database password
dataSource.password=at_password

#  Other properties
dataSource.pooled=true
dataSource.driverClassName=net.sourceforge.jtds.jdbc.Driver
dataSource.dialect=org.hibernate.dialect.SQLServerDialect
dataSource.dbCreate=update
 

# Oracle Database connection URL
# Structure: dataSource.url=jdbc:oracle:thin:@//<ORACLE_DB_HOST>:<ORACLE_DB_PORT>/<SERVICE_NAME_FOR_ACTIVITYTIMELINE>
#dataSource.url=jdbc:oracle:thin:@//localhost:1521/timeline

# Oracle Database username
#dataSource.username=at_user

# Oracle Database password
#dataSource.password=at_password

# Other properties
#dataSource.pooled=true
#dataSource.driverClassName=oracle.jdbc.driver.OracleDriver
#dataSource.dialect=com.timeline.hibernate.ActivityTimelineOracleDialect
#dataSource.dbCreate=update
#dataSource.properties.validationQuery=SELECT 1 FROM DUAL

     dataSource.oracleLegacyMode=1