Liquibase Tutorial

What is Liquibase ?

1. LiquiBase — available since 2006 — is an open source, freely available tool for migrating from one database version to another, It is an open source database-independent library for tracking, managing and applying database changes.
   
2. A handful of other open source database-migration tools are on the scene as well, including openDBcopy and dbdeploy. LiquiBase supports 10 database types, including DB2, Apache Derby, MySQL, PostgreSQL, Oracle, Microsoft® SQL Server, Sybase, and HSQL.
   

2. All changes to the database are stored in XML files and identified by a combination of an "id" and "author" tag as well as the name of the file itself.
   
3. A list of all applied changes is stored in each database which is consulted on all database updates to determine what new changes need to be applied.
   
4. LiquiBase executes changes based on this XML file to handle different revisions of database structures and data.
   
5. When you first run a changelog, LiquiBase manages those changelogs by adding two tables into your database.
   databasechangelog: maintains the database changes that were run.
   databasechangeloglock: ensures that two machines don't attempt to modify the database at one time.
   

To install LiquiBase, download the compressed LiquiBase Core file, extract it, and place the included liquibase-version.jar file in your system's path.
Getting started with LiquiBase takes four steps:

1. Create a database change log file.
2. Create a change set inside the change log file.
3. Run the change set against a database via the command line or a build script.
4. Verify the change in the database.
   

Sample changeLog file:  The above is an example of creating table EMPLOYEE and adding columns into it.

<?xml version="1.0" encoding="UTF-8"?>
<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-2.0.xsd">
      
    <changeSet author="waheed" id="123456789-1">

 <comment> You can add comments to changeSets.</comment>
        <createTable tableName="EMPLOYEE">
            <column autoIncrement="true" name="EMPLOYEE_ID" type="BIGINT">
                <constraints nullable="false" primaryKey="true" />
            </column>
            <column name="NAME" type="VARCHAR(255)" />
            <column name="GENDER" type="VARCHAR(2)" />
            <column name="COUNTRY" type="VARCHAR(255)" />
            <column name="ABOUT_YOU" type="VARCHAR(255)" />
        </createTable>
    </changeSet>
</databaseChangeLog> 

Running LiquiBase from the command line:
After defining the change set, I can run LiquiBase from the command line:

Running LiquiBase from the command line

liquibase --driver=com.mysql.jdbc.Driver \ --classpath=mysql_connector.jar \ --changeLogFile=database.changelog.xml \ --url=jdbc:mysql://localhost:3306/Employees;create=true \ --username= --password= \ update

In this example, I run LiquiBase passing in:

• The database driver
• The classpath for the location of the database driver's JAR file
• The name of the change log file “database.changelog.xml”
• The URL for the database
• A username and password
  

Running LiquiBase in an automated build
Instead of using the command-line option, I can make the database changes as part of the automated build by calling the Ant task provided by LiquiBase.

Ant script to execute the updateDatabase Ant task

<target name="update-database"> <taskdef name="updateDatabase" classname="liquibase.ant.DatabaseUpdateTask" classpathref="project.class.path" /> <updateDatabase changeLogFile="database.changelog.xml" driver="com.mysql.jdbc.Driverr" url="jdbc:mysql://localhost:3306/Employees" username="" password="" dropFirst="true" classpathref="project.class.path"/> </target>

I create a target called update-database. In it, I define the specific LiquiBase Ant task I wish to use, calling it updateDatabase. I pass the required values, including the changeLogFile and connection information for the database. The classpath defined in classpathref must contain liquibase-version.jar.



Applying refactorings to an existing database
As new features are added to an application, the need often arises to apply structural changes to a database or modify table constraints. LiquiBase provides support for more than 30 database refactorings.



Add Column
It's sometimes next to impossible to consider all of the possible columns in a database at the beginning of a project. And sometimes users request new features — such as collecting more data for information stored in the system — that can require new columns to be added.

Using the Add Column database refactoring in a LiquiBase change set

<changeSet author="waheed" id=”123456789-2”> <addColumn tableName="EMPLOYEE"> <column name="PHONE_NUMBER" type="varchar(255)" defaultValue=”SOME_DEFAULT_VALUE”/> </addColumn> </changeSet>

The new PHONE_NUMBER column is defined as a varchar datatype.


Drop Column
Suppose, you choose to remove the PHONE_NUMBER column you added above. This is as simple as calling the dropColumn refactoring:

Dropping a database column

<dropColumn tableName="EMPLOYEE" columnName="PHONE_NUMBER"/>

Create Table
Adding a new table to a database is also a common database refactoring. Creates a new table called USER, defining its columns, constraints, and default values:

Creating a new database table in LiquiBase

<changeSet author="waheed" id=”123456789-3”> <createTable tableName="USER"> <column name="ID" type="int"> <constraints primaryKey="true" nullable="false"/> </column> <column name="NAME" type="varchar(255)"> <constraints nullable="false"/> </column> <column name="ADDRESS" type="varchar(255)"> <constraints nullable="true"/> </column> <column name="active" type="boolean" defaultValue="1"/> </createTable> </changeSet>

This example uses the createTable database refactoring as part of a change set (createTable was also used back).



Rename Column

<changeSet author="waheed" id=”123456789-5”> <comment>Add a username column so we can use "person" for authentication</comment> <addColumn tableName="EMPLOYEE"> <column name="usernae" type="varchar(8)"/> </addColumn> </changeSet>

The second update will add “usernae” (typo mistakes is on purpose) with width 8 characters
Now, we need to fix the usernae become username

<changeSet author="waheed" id=”123456789-6”> <comment>Fix misspelled "username" column</comment> <renameColumn tableName="EMPLOYEE" oldColumnName="usernae" newColumnName="username" columnDataType="varchar(8)"/> </changeSet>

Manipulating data
After applying structural database refactorings (such as Add Column and Create Table), you often need to insert data into tables affected by these refactorings. Furthermore, you might need to change the existing data in lookup tables or other types of tables. Below example shows how to insert data using a LiquiBase change set:

Inserting data in a LiquiBase change set

<changeSet author="waheed" id=”123456789-4”> <code type="section" width="100%"> <insert tableName="USER"> <column name="ID" valueNumeric="3"/> <column name="NAME" value="ABDUL"/> </insert> <insert tableName="USER"> <column name="ID" valueNumeric="4"/> <column name="NAME" value="WAHEED"/> </insert> </changeSet>

Suppose, You may have already written SQL scripts to manipulate data, or the LiquiBase XML change set may be too limiting. And sometimes it's simpler to use SQL scripts to apply mass changes to the database. LiquiBase can accommodate these situations too.

Running a custom SQL file from a LiquiBase change set

<changeSet author="Waheed" id=”123456789-5”> <sqlFile path="insert-distributor-data.sql"/> </changeSet>

How to integrate With Spring:
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />

<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/Employees" />
<property name="user" value="root" />
<property name="password" value="root123" />
<property name="minPoolSize" value="8" />
<property name="maxPoolSize" value="16" />
<property name="maxIdleTime" value="3600" />
</bean>
<!-- Updater is used to automatically update DB  upon startup if
        Application version has changed -->
    <bean id="LiquibaseUpdater" class="liquibase.integration.spring.SpringLiquibase">
        <property name="dataSource" ref="dataSource" />
        <property name="changeLog" value="classpath:db-changelog.xml" />
    </bean>
<!-- Needed here to make sure Liquibase updater runs prior to DAO's startup, Your DAO class -->
    <bean class="com.waheed.spring.hibernate.DaoImpl" id="dao"
        depends-on="LiquibaseUpdater">
    </bean>


Resources:
http://www.liquibase.org

How to get Current Thread Example

1. /*
2.         This Java example shows how to get reference of current thread using
3.         currentThread method of Java Thread class.
4. */
5.  
6. public class GetCurrentThread {
7.  
8.         public static void main(String[] args) {
9.                
10.                 /*
11.                  * To get the reference of currently running thread, use
12.                  * Thread currentThread() method of Thread class.
13.                  *
14.                  * This is a static method.
15.                  */
16.                
17.                 Thread currentThread = Thread.currentThread();
18.                 System.out.println(currentThread);
19.                
20.         }
21. }
22.  
23. /*
24. Output of the example would be
25. Thread[main,5,main]
26. */

Should Logger members of a class be declared as static?

Advantages for declaring loggers as static	Disadvantages for declaring loggers as static
common and well-established idiom less CPU overhead: loggers are retrieved and assigned only once, at hosting class initialization less memory overhead: logger declaration will consume one reference per class	For libraries shared between applications, not possible to take advantage of repository selectors. It should be noted that if the SLF4J binding and the underlying API ships with each application (not shared between applications), then each application will still have its own logging environment. not IOC-friendly
Advantages for declaring loggers as instance variables	Disadvantages for declaring loggers as instance variables
Possible to take advantage of repository selectors even for libraries shared between applications. However, repository selectors only work if the underlying logging system is logback-classic. Repository selectors do not work for the SLF4J+log4j combination. IOC-friendly	Less common idiom than declaring loggers as static variables higher CPU overhead: loggers are retrieved and assigned for each instance of the hosting class higher memory overhead: logger declaration will consume one reference per instance of the hosting class

Explanation

Static logger members cost a single variable reference for all instances of the class whereas an instance logger member will cost a variable reference for every instance of the class. For simple classes instantiated thousands of times there might be a noticeable difference.
However, more recent logging systems, e.g log4j or logback, support a distinct logger context for each application running in the application server. Thus, even if a single copy of log4j.jar or logback-classic.jar is deployed in the server, the logging system will be able to differentiate between applications and offer a distinct logging environment for each application.
More specifically, each time a logger is retrieved by invoking LoggerFactory.getLogger() method, the underlying logging system will return an instance appropriate for the current application. Please note that within the same application retrieving a logger by a given name will always return the same logger. For a given name, a different logger will be returned only for different applications.
If the logger is static, then it will only be retrieved once when the hosting class is loaded into memory. If the hosting class is used in only in one application, there is not much to be concerned about. However, if the hosting class is shared between several applications, then all instances of the shared class will log into the context of the application which happened to first load the shared class into memory - hardly the behavior expected by the user.
Unfortunately, for non-native implementations of the SLF4J API, namely with slf4j-log4j12, log4j's repository selector will not be able to do its job properly because slf4j-log4j12, a non-native SLF4J binding, will store logger instances in a map, short-circuiting context-dependent logger retrieval. For native SLF4J implementations, such as logback-classic, repository selectors will work as expected.


Summary
In summary, declaring logger members as static variables requires less CPU time and have a slightly smaller memory footprint. On the other hand, declaring logger members as instance variables requires more CPU time and have a slighlty higher memory overhead. However, instance variables make it possible to create a distinct logger environment for each application, even for loggers declared in shared libraries. Perhaps more important than previously mentioned considerations, instance variables are IOC-friendly whereas static variables are not.

Check link for more info.

How to get directory size in linux ?

 du -sm <dir_name>

What is Liquibase ?

1. Liquibase is an open source database-independent library for tracking, managing and applying database changes.
2. All changes to the database are stored in XML files and identified by a combination of an "id" and "author" tag as well as the name of the file itself.
3. A list of all applied changes is stored in each database which is consulted on all database updates to determine what new changes need to be applied.
4. Liquibase executes changes based on this XML file to handle different revisions of database structures and data.
5. When you first run a changelog, LiquiBase manages those changelogs by adding two tables into your database.
databasechangelog: maintains the database changes that were run.
databasechangeloglock: ensures that two machines don't attempt to modify the database at one time.
6. Limitations do exist such that it will not export triggers, stored procedures, functions and packages.

Sample changeLog file:

 The above is an example of creating table EMPLOYEE and adding columns into it.


<?xml version="1.0" encoding="UTF-8"?>
<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-2.0.xsd">
      
    <changeSet author="waheed" id="123456789-1">
        <createTable tableName="EMPLOYEE">
            <column autoIncrement="true" name="EMPLOYEE_ID" type="BIGINT">
                <constraints nullable="false" primaryKey="true" />
            </column>
            <column name="NAME" type="VARCHAR(255)" />
            <column name="GENDER" type="VARCHAR(2)" />
            <column name="COUNTRY" type="VARCHAR(255)" />
            <column name="ABOUT_YOU" type="VARCHAR(255)" />
        </createTable>
    </changeSet>
</databaseChangeLog>

How to integrate Liquibase with Spring and Hibernate ?

A sample tutorial on how to integrate Liquibase with Spring and Hibernate.

While writing this tutorial, I have added javadoc in the code for better understanding and I believe You already have good knowledge on Spring and Hibernate. The main motto of this tutorial is to give an idea on how you can integrate Liquibase with Spring and Hibernate.

If you are new to Liquibase : Click Here

To integrate liquibase into your project, you need liquibase jars, So download it before starting the project.

I have created an application named "SHLIntegration". The Structure of the project is as follows :

The dependencies are also listed here:

Lets start with Employee class :

1. Create Employee class having getter/setter and add proper JPA annotation to each variable as below.

  public class Employee {

    @Id
    @GeneratedValue
    @Column(name="EMPLOYEE_ID")
    private long id;

    @Column(name="NAME")
    private String name;
   
    @Column(name="GENDER")
    private String gender;
   
    @Column(name="COUNTRY")
    private String country;
   
    @Column(name="ABOUT_YOU")
    private String aboutYou;
.....
....
.... // getter setter of each object

}

2. Create liquibase file i,e db-changelog.xml file

<?xml version="1.0" encoding="UTF-8"?>
<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-2.0.xsd">
       
    <changeSet author="waheed" id="123456789-1">
        <createTable tableName="EMPLOYEE">
            <column autoIncrement="true" name="EMPLOYEE_ID" type="BIGINT">
                <constraints nullable="false" primaryKey="true" />
            </column>
            <column name="NAME" type="VARCHAR(255)" />
            <column name="GENDER" type="VARCHAR(2)" />
            <column name="COUNTRY" type="VARCHAR(255)" />
            <column name="ABOUT_YOU" type="VARCHAR(255)" />
        </createTable>
    </changeSet>
</databaseChangeLog>



3. Add liquibase bean in your bean :

    <bean id="LiquibaseUpdater" class="liquibase.integration.spring.SpringLiquibase">
        <property name="dataSource" ref="dataSource" />
        <property name="changeLog" value="classpath:db-changelog.xml" />
    </bean>

 and others beans which are required for Spring/Hibernate.  Check bean file


The complete tutorial : 
https://github.com/abdulwaheed18/SHLIntegration


Please feel free to do comment or drop me a mail regarding any suggestion/Feedback.
Email : waheedtechblog@gmail.com















 

Some ANT task

1. How to build  project from another build.

<target name="project2"
            description="Builds project2 project, required depedency">
        <!-- Build project2 first  -->

        <subant target="dist" verbose="yes" inheritall="false">
            <filelist dir="../com.waheed.project2"
                      files="build.xml" />
        </subant>
    </target>

 

2. How to read SVN revision and write into some file

<loadfile property="revision" srcFile="./.svn/entries">
        <filterchain>
            <headfilter skip="3" lines="1"/>
                  </filterchain>
        </loadfile>
            <tstamp>
                <format property="date" pattern="dd/MM/yyyy hh:mm:ss" />
            </tstamp>
            <echo append="true" file="<FILE_NAME>" >revision=${revision}${line.separator}</echo>

3. How to generate Keystore

    <target name="keystore">
        <delete file="workdir/keystore" failonerror="false"/>
        <genkey keystore="./keystore"
                alias="jetty"
                storepass="password"
                keypass="password"
                keyalg="RSA"
                validity="10">
            <dname>
                <param name="CN" value="NAME" />
                <param name="OU" value="NAME_OF_ORGANIZATION_UNIT" />
                <param name="O" value="ORGANIZATION_NAME" />
                <param name="C" value="COUNTRY_NAME" />
            </dname>
        </genkey>
    </target>

4. How to get current time

<target name="time">
        <tstamp>
            <format property="build-time" pattern="yyyy-MM-dd-HH-mm" />
        </tstamp>
        <echo>${build-start-time}</echo>
    </target>

5 . How to create jar with manifest

 <jar jarfile="${dist}/name_of_jar.jar"
             basedir="${build}">
            <manifest>
                <!-- Who is building this jar? -->
                <attribute name="Built-By" value="${user.name}" />
                <!-- Information about the program itself -->
                <attribute name="Implementation-Vendor"
                           value="Implementation-Vendor" />
                <attribute name="Implementation-Title"
                           value="Implementation-Title" />
                <attribute name="Implementation-Version" value="1.0" />
                <!-- details -->
                <section name="PATH_TO_MAIN_CLASS">
                    <attribute name="Sealed" value="false" />
                </section>
            </manifest>
        </jar>

6. How to compile source

 <!-- Compile the java code from ${src} into ${build} -->

        <javac destdir="bin" debug="true">
            <src path="src" />
            <classpath>
                <pathelement location="../dependency/bin" />
                <fileset dir="../lib">
                    <include name="*.jar" />
                </fileset>
            </classpath>
        </javac>

7. How to compile source with dependency class path

 <path id="class.path">
        <fileset dir="../lib/folder1">
            <include name="*.jar" />
        </fileset>
        <fileset dir="../lib/folder2">
             <include name="*.jar" />
        </fileset>
    </path>

 <!-- Compile the java code from ${src} into ${build} -->
        <javac destdir="bin" debug="true">
            <src path="src" />
            <classpath refid="class.path" />
        </javac>

8.  How to build Zip file

    <property name="product.name" value="product1" />
    <property name="product.version" value="1.0" />
<zip basedir="${dist}" destfile="${dist}/${product.name}-${product.version}.zip">
        </zip>

9. How to build tar file

    <property name="product.name" value="product1" />
    <property name="product.version" value="1.0" />

<exec executable="tar" dir="${dist}">
            <arg value="czf" />
            <arg value="${dist}/${product.name}-${product.version}.tgz" />
            <arg value="." />
        </exec>

10 . How to check OS 

<condition property="isWindows">
        <os family="windows" />
    </condition>

    <condition property="isUnix">
        <os family="unix" />
    </condition>

    <target name="dist.windows" if="isWindows" depends="a">
<!--  Task to be done-- >
     </target>

    <target name="dist.unix" if="isUnix" depends="b">
<!--  Task to be done-- >
    </target>

11.  How to set permission to file

 <chmod perm="500">
            <fileset dir="${dist}">
                <include name="**/*.sh" />
                <include name="jsvc" />
            </fileset>
        </chmod>

12 .How to read property file

Suppose I have following data in my filename1.properties file and want to read it and write it to another file lets say name filename2.properties. 

filename1.properties

REVISION 777

     <property file="${dist}/filename1.properties" prefix="version"/>
       <echo file="${dist}/filename2.properties" append="true">revision ${version.REVISION}${line.separator}</echo>