Mark block quot in HTMLe.
.. -*- coding: utf-8; -*-
============
LiquiBase.
============
.. contents::
:local:
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
SQL syntax.
===========
* http://www.liquibase.org/2015/09/liquibase-without-changelogs.html
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/
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