Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

Problem

Some of our project classes come from Common Model component. To implement our schema migration, we need a mechanism to apply Common Model migration first and our changes second.

Solution

From this article you may learn about Flyway, how to make schema update.

First step

First step is registering Flyway beans into Spring Application Context. There is FlywayWrapper bean in common. This bean purpose is control (enabling/disabling) flyway migrations, schema cleanup and initialization. Due to current FlywayWrapper implementation we need used two the same bean registration records with different init methods for each schema.

Code Block
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="${jdbc.driverClassName}" />
    <property value="${jdbc.url}" />
    <property value="${jdbc.username:root}" />
    <property value="${jdbc.password:root}" />
  </bean>

  <bean class="org.jtalks.common.util.FlywayWrapper" init-method="smartInit" depends-on="dataSource">
        <property name="dataSource" ref="dataSource"/>
        <property name="basePackage" value="org.jtalks.common.migrations"/>
        <property value="/org/jtalks/common/migrations"/>
        <property value="common_schema_version"/>
        <property value="${migrations_enabled}"/>
    </bean>

    <bean class="org.jtalks.common.util.FlywayWrapper" init-method="migrate" depends-on="flyway_common_init">
        <property name="dataSource" ref="dataSource"/>
        <property name="basePackage" value="org.jtalks.common.migrations"/>
        <property value="/org/jtalks/common/migrations"/>
        <property value="common_schema_version"/>
        <property value="${migrations_enabled}"/>
    </bean>

    <bean class="org.jtalks.common.util.FlywayWrapper" init-method="smartInit"
          depends-on="flyway_common">
        <property name="dataSource" ref="dataSource"/>
        <property value="poulpe_schema_version"/>
        <property value="${migrations_enabled}"/>
    </bean>

    <bean class="org.jtalks.common.util.FlywayWrapper" init-method="migrate" depends-on="flyway_poulpe_init">
        <property name="dataSource" ref="dataSource"/>
        <property name="basePackage" value="org.jtalks.poulpe.migrations"/>
        <property value="/org/jtalks/poulpe/migrations"/>
        <property value="poulpe_schema_version"/>
        <property value="${migrations_enabled}"/>
    </bean>

Our beans require data source instance and we have the next init chain:

  1. dataSource bean created.

Applying Common schema migrations

  1. flyway_common_init bean created and make Flyway.init() if there is no common metadata table in the specified schema and if smartInit is enabled.
  2. flyway_common created and call Flyway.migrate() if enabled property is True.
    Applying Poulpe schema migrations
  3. flyway_poulpe_init bean created and make Flyway.init() if there is no poulpe metadata table in the specified schema and if smartInit is enabled.
  4. flyway_poulpe_init created and call Flyway.migrate() if enabled property is True.

So this chain may seem a little ugly. For our current developer needs we may use one FlywayWrapper method with two sequence calls for init() and migrate() Flyway methods.

Second step

We use Hibernate for persistence and we need avoid Hibernate schema object creation. Now Flyway will responsible for schema and data managing.

In datasource.properties file need to set next value

Code Block
hibernate.hbm2ddl.auto=validate

Third step

Now we need to write the first SQL migration and place it to resource directory. This migration contains all poulpe schema specific objects.

Code Block
ALTER TABLE `BRANCHES` ADD `TYPE` VARCHAR(255) NOT NULL;

ALTER TABLE `SECTIONS` ADD `TYPE` VARCHAR(255) NOT NULL;

ALTER TABLE `GROUPS` ADD `TYPE` VARCHAR(255) NOT NULL;

ALTER TABLE `GROUPS` ADD `BRANCH_ID` BIGINT(20) NULL DEFAULT NULL;

ALTER TABLE `GROUPS` ADD `POSITION` INT(11) NULL DEFAULT NULL;

ALTER TABLE `SECTIONS` ADD `POSITION` INT(11) NULL DEFAULT NULL;

ALTER TABLE `SECTIONS` ADD `JCOMMUNE_ID` BIGINT(20) NULL DEFAULT NULL;

CREATE  TABLE IF NOT EXISTS `BRANCH_USER_REF` (
  `BRANCH_ID` BIGINT(20) NOT NULL,
  `USER_ID` BIGINT(20) NOT NULL,
  CONSTRAINT `FK_BRANCH_USER_REF_USERS_USER_ID`
    FOREIGN KEY (`USER_ID`)
    REFERENCES `USERS` (`ID`),
  CONSTRAINT `FK_BRANCH`
    FOREIGN KEY (`BRANCH_ID`)
    REFERENCES `BRANCHES` (`BRANCH_ID`));

CREATE  TABLE IF NOT EXISTS `GROUP_USER_REF` (
  `GROUP_ID` BIGINT(20) NOT NULL,
  `USER_ID` BIGINT(20) NOT NULL,
  CONSTRAINT `FK_GROUP_USER_REF_GROUPS_GROUP_ID`
    FOREIGN KEY (`GROUP_ID`)
    REFERENCES `GROUPS` (`GROUP_ID`),
  CONSTRAINT `FK_GROUP_USER_REF_USERS_USER_ID`
    FOREIGN KEY (`USER_ID`)
    REFERENCES `USERS` (`ID`),
  CONSTRAINT `FK_GROUP_USER_REF_GROUPS_GROUP_ID`
    FOREIGN KEY (`GROUP_ID`)
    REFERENCES `GROUPS` (`GROUP_ID`));



CREATE  TABLE IF NOT EXISTS `TOPIC_TYPES`(
  `ID` BIGINT(20) NOT NULL AUTO_INCREMENT ,
  `UUID` VARCHAR(255) NOT NULL ,
  `TITLE` VARCHAR(255) NOT NULL ,
  `DESCRIPTION` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`ID`) ,
  UNIQUE INDEX `UUID` (`UUID` ASC) ,
  UNIQUE INDEX `TITLE` (`TITLE` ASC));

Flyway Logging

Sometimes there is a need to see exact migration actions. Through Flyway it is not possible, solution - usage of Apache Logging – Log4j.
Customize your log4j.xml to be able to see logs from Flyway Migrations in the following way:

Code Block
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">

<log4j:configuration debug="false" xmlns:log4j="http://jakarta.apache.org/log4j/">

  <appender name="FLYWAY_LOG" class="org.apache.log4j.DailyRollingFileAppender">
        <param name="File" value="logs/flyway_migration.log"/>
        <param name="Append" value="true"/>
        <param name="DatePattern" value=".yyyy-MM-dd"/>
        <param name="Encoding" value="UTF-8"/>
        <layout class="org.apache.log4j.EnhancedPatternLayout">
  	      <param name="ConversionPattern" value="%d{ISO8601} [%-5p][%-16.16t][%30c] - %m%n"/>
        </layout>
  </appender>


  <logger name="com.googlecode.flyway.core.migration" additivity="false">
	<level value="DEBUG"/> <!-- or use exact level you want -->
	<appender-ref ref="FLYWAY_LOG"/>
  </logger>

</log4j:configuration>

Notes

  1. Don’t forget use in SQL script only one case – upper or low (UNIX environment is case sensitive).
  2. Don’t forget add new resource to maven build goal.
Code Block
<resource>
        <directory>src/main/resources</directory>
        <includes>
          <include>**/migrations/**</include>
        </includes>
      </resource>

Now on our project we have 2 phase Flyway migrations. Please make all future schema changes according next instruction.