LiquiBase.

Introducing LiquiBase to existing project.

Official statement about introducing LiquiBase to existing project are:

http://www.liquibase.org/documentation/existing_project.html

Adding Liquibase on an Existing project

There are two approaches:

• create full schema definition prior to introducing Liquibase
• works only with current changes becase Liquibase basically work only with changesets and only special supplied instruments care about full schema definition

To create full schema definition in LiquiBase XML changelog format you may run:

$ mvn liquibase:generateChangeLog -Dliquibase.outputChangeLogFile=...

on clean project without Liquibase. Or set name for changelog output file in pom.xml. (here reduced not fully worked declaration):

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>${liquibase.version}</version>
    <configuration>
        <outputChangeLogFile>changelog.xml</outputChangeLogFile>
    </configuration>
</plugin>

<properties>
    <liquibase.outputChangeLogFile>${project.build.directory}/changelog.xml</liquibase.outputChangeLogFile>
</properties>
<plugins>
    <plugin>
        <configuration>
            <outputChangeLogFile>${liquibase.outputChangeLogFile}</outputChangeLogFile>
        </configuration>
    </plugin>
</plugins>

To create full schema definition in LiquiBase SQL format you may first to dump your schema definition:

$ mysqldump --no-data -u $USER -p $DB_NAME > schema.sql

then check schema.sql for danger operations. For example I forget to add --skip-add-drop-table option to mysqldump and so remove any DROP TABLE lines by editor.

Becase we already have tables I replace CREATE TABLE statements with:

CREATE TABLE IF NOT EXISTS

Now my file is safely can be applied to production database without loosing data and can recreate new schema in empty database.

• http://www.operatornew.com/2012/11/automatic-db-migration-for-java-web.html
• http://www.baeldung.com/liquibase-refactor-schema-of-java-app
• http://www.liquibase.org/documentation/existing_project.html
• http://www.liquibase.org/tutorial-using-oracle

SQL syntax.

• http://www.liquibase.org/2015/09/liquibase-without-changelogs.html

Generate difference between databases.

This site suggest using Hibernate hibernate.hbm2ddl.auto=create to create clean schema with Hibernate and compare it with previous one. So you:

• create empty schema and grand permissions for user

• register project to new user and schema, for example with Spring config:

  <bean id="dataSource"
     class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">
     <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
     <property name="url" value="jdbc:mysql://localhost:3306/app"/>
     <property name="username" value="dbuser"/>
     <property name="password" value="123456"/>
  </bean>
  
  <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
     <property name="dataSource" ref="dataSource" />
     <property name="jpaVendorAdapter">
         <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
             <property name="generateDdl" value="false"/>
             <property name="showSql" value="false"/>
         </bean>
     </property>
     <property name="packagesToScan" value="com.app.domain" />
     <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">create</prop>
            <prop key="hibernate.default_schema">schema_v2_0</prop>
        </props>
     </property>
  </bean>
  

• run Hibernate initialisation code via test or application deploy so hibernate.hbm2ddl.auto=create trigger to new schema generation.

• configure Maven pom.xml with paths and authentication data to old and new schema:

    <plugin>
        <groupId>org.liquibase</groupId>
        <artifactId>liquibase-maven-plugin</artifactId>
        <version>${liquibase.version}</version>
        <configuration>
            <changeLogFile>${basedir}/src/main/resources/sql/master.xml</changeLogFile>
            <diffChangeLogFile>changelogDiff.xml</diffChangeLogFile>
            <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
  
            <driver>com.mysql.jdbc.Driver</driver>
            <username>dbuser</username>
            <password>123456</password>
            <url>jdbc:mysql://localhost:3306/app</url>
            <defaultSchemaName>schema_v1.1</defaultSchemaName>
  
            <referenceDriver>com.mysql.jdbc.Driver</referenceDriver>
            <referenceUsername>dbuser</referenceUsername>
            <referencePassword>123456</referencePassword>
            <referenceUrl>jdbc:mysql://127.0.0.1:3306/app</referenceUrl>
            <referenceDefaultSchemaName>schema_v2.0</referenceDefaultSchemaName>
        </configuration>
        <dependencies>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.6</version>
            </dependency>
        </dependencies>
    </plugin>
  
  .. NOTE::
  
     ``username`` and ``referenceUsername`` as ``url`` and ``referenceUrl``,
     and ``defaultSchemaName`` and ``referenceDefaultSchemaName``, etc pairs
     should be adopted to your connection/authentication data. This settings
     also possible externalize to::
  
       <changeLogFile>${basedir}/src/main/resources/sql/master.xml</changeLogFile>
  

• Review diff in changelogDiff.xml after:

  $ mvn liquibase:diff
  

Resulted changelogDiff.xml may be registered to project with schema_v1.1 schema state.

It is possible to move inner part of changelogDiff.xml to the last changeset that managed by LiquiBase in your project.

Alternatively that file may be registered via <include file="..."/> syntax.

After that staying in project at schema_v1.1 state you may upgrade from schema_v1.1 to schema_v2.0 schema by:

$ mvn liquibase:update

Or create, review and apply SQL upgrade script (for your DBA):

$ mvn liquibase:updateSQL

TODO:

$ mvn liquibase:update -Dliquibase.changesToApply=1

Generating difference between database and JPA.

LiquiBase come with plug-in which mimics like SQL driver. So we may run:

$ mvn liquibase:diff

to get difference in LiquiBase XML format between our current database and JPA schema description.

With:

$ mvn liquibase:updateSQL

we get SQL update code to state that described by Hibernate.

Depending on Hibernate metadata form we shoud adapt referenceUrl property. For example for Spring enabled project with JPA annotations:

<referenceUrl>hibernate:spring:com.app.domain?dialect=org.hibernate.dialect.MySQLDialect</referenceUrl>

Here com.app.domain represent package with JPA annotated entities.

So complete reference visit: https://github.com/liquibase/liquibase-hibernate/wiki

Also we need Spring Beans and LiquiBase driver in classpath to resolve dependencies during diff calculation. Complete Maven piece look like:

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>${liquibase.version}</version>
    <configuration>
        <changeLogFile>${basedir}/src/main/resources/sql/master.xml</changeLogFile>
        <propertyFile>${liquibase.profile}</propertyFile>
        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>
        <logging>info</logging>
        <driver>com.mysql.jdbc.Driver</driver>
        <defaultSchemaName>app</defaultSchemaName>
        <changelogSchemaName>app</changelogSchemaName>

        <!-- For mvn liquibase:updateSQL -->
        <migrationSqlOutputFile>migration.sql</migrationSqlOutputFile>

        <referenceUrl>hibernate:spring:com.app.domain?dialect=org.hibernate.dialect.MySQLDialect</referenceUrl>
        <diffChangeLogFile>changelogDiff.xml</diffChangeLogFile>
        <diffTypes>tables,views,columns,indexes,foreignkeys,primarykeys,uniqueconstraints</diffTypes>
    </configuration>
    <dependencies>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>${spring-data.version}</version>
        </dependency>
        <dependency>
            <groupId>org.liquibase.ext</groupId>
            <artifactId>liquibase-hibernate4.2</artifactId>
            <version>3.5</version>
        </dependency>
    </dependencies>
</plugin>

http://stackoverflow.com/questions/27877154/liquibase-and-jpa-annotated-entities

http://www.baeldung.com/liquibase-refactor-schema-of-java-app

How changelogs are identified.

Upstream deside use three field to identify changeset:

• each changeset mandatory marked by pair of user:id (which is actually any non-whitespace characters)
• full path to file with changeset

user:id is actually any non-whitespace and non-colon text with non-whitespace text. Upstream suggest to use changeset authro identity for user and numbers or reason/name for changeset for id.

user:id pair should be unique for file.

Reason to capture full path as part of identifier is very dumb. Upstream arguments:

• http://forum.liquibase.org/topic/why-does-the-change-log-contain-the-file-name
• http://forum.liquibase.org/topic/i-need-to-ignore-the-filename-in-the-processing-to-see-if-a-change-set-has-already-been-applied
• http://forum.liquibase.org/topic/logical-filepath-in-change-sets-and-changelog

is very Java centric and require sticking to fixed changeset file location in CLASSPATH.

When you work with same file on different hosts / tools you should very carefully check that path satisfy conventions that used with previous LiquiBase work for concrete DB.

I have incompatibilities between String integration and Maven plug-in. Because I use full path in Maven and CLASSPATH relative in String.

Hopefully there is logicalFilePath attribute.

In XML syntax in may be applied to top level tag:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"
                   logicalFilePath="legacy.xml">
    ...
</databaseChangeLog>

or may be overridden by each changeset:

<changeSet author="admin" id="fix-25" logicalFilePath="fix.xml">
   ...
</changeSet>

SQL syntax also have logicalFilePath attribute for top level file mark (implemented in v3.3):

--liquibase formatted sql  logicalFilePath:legacy.sql

and may be overridden by each changeset:

--changeset legacy:1  logicalFilePath:other.sql

To verify that settings have effect run:

mvn liquibase:changelogSyncSQL

and review migration.sql. Corresponding pom.xml part:

<plugin>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-maven-plugin</artifactId>
    <version>${liquibase.version}</version>
    <configuration>
        <changeLogFile>${basedir}/src/main/resources/sql/master.xml</changeLogFile>
        <propertyFile>${liquibase.profile}</propertyFile>
        <promptOnNonLocalDatabase>false</promptOnNonLocalDatabase>

        <migrationSqlOutputFile>migration.sql</migrationSqlOutputFile>
    </configuration>
</plugin>

• http://stackoverflow.com/questions/19896436/how-to-configure-liquibase-not-to-include-file-path-or-name-for-calculating-chec
• http://stackoverflow.com/questions/19959755/liquibase-how-to-disable-filename-column-check
• http://stackoverflow.com/questions/18767815/refactoring-liquibase-changelog-files

Consult liquibase-core/src/main/java/liquibase/parser/core/formattedsql/FormattedSqlChangeLogParser.java for further info .

Split changesets into files.

It is not possible to split file formatted in SQL LiquiBase syntax.

It is not possible to set tag with SQL LiquiBase syntax. To workaround this issue you may split SQL file and set tags in XML sytax:

<changeSet author="master" id="1">
    <tagDatabase tag="v0.1"/>
</changeSet>

<include file="legacy.sql" relativeToChangelogFile="true"/>

<changeSet author="master" id="2">
    <tagDatabase tag="v1.0"/>
</changeSet>

<include file="v1.0.sql" relativeToChangelogFile="true"/>

Also you should be interested in storing stored procedures in separate file and apply runOnChange attribure for XML syntax:

<changeSet author="admin" id="proc-hello">
    <createProcedure
            runOnChange="true"
            encoding="utf8"
            dbms="oracle">
        CREATE OR REPLACE PROCEDURE hello AS
        BEGIN
          DBMS_OUTPUT.PUT_LINE('Hello');
        END;
    </createProcedure>
</changeSet>

or with SQL syntax:

--changeset admin:proc-hello  runOnChange:true
CREATE OR REPLACE PROCEDURE hello AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;

Some people suggest to use one file per stored procedure / package.

How big chould be changeset?

Less is better.

Some databases doesn't support transactional DDL (e.g. Oracle, SQL Server), each DDL statement should be put into a single changeset.

Putting unrelated operations into single changeset leads to trouble when last or intermediate of them fail to apply.

Try to group constraint adding in corresponding update statements. If you have wrong update that doesn't prepare data to constraint it shouldn't be commited but rewritten.

• http://blog.mgm-tp.com/2010/11/data-modeling-part2/

Labels vs Contexts.

• http://forum.liquibase.org/topic/labels-vs-contexts-in-3-3-0
• http://www.liquibase.org/2014/11/contexts-vs-labels.html

How to apply LiquiBase to you DB?

• http://www.liquibase.org/2015/07/executing-liquibase.html

Comparison LiquiBase with Python Django South and Ruby ActiveRecord.

• http://south.readthedocs.org/en/latest/index.html
• https://code.djangoproject.com/wiki/SchemaEvolution
• http://guides.rubyonrails.org/active_record_basics.html