.. -*- coding: utf-8; -*-

.. include:: HEADER.rst

============

 LiquiBase.

============

.. contents::

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>

.. NOTE::

   ``pom.xml`` settings have precedence over ``-Dliquibase.outputChangeLogFile=...`` unless you

   define it as::

     <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

Generate difference between databases.

======================================

`This site

<http://www.broadleafcommerce.com/docs/core/current/appendix/managing-db-versions-migrations-with-liquibase>`_

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/