+
−.. -*- 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
+
−