Database Change Management using Liquibase and Maven

Hi Guys, this is my recent research

We never develop code without version control, why should we develop database without it?

Liquibase is an open source (Apache 2.0 Licensed), database-independent library for tracking, managing and applying database changes. It is built on a simple premise: All database changes are stored in a human readable yet trackable form.

Simple configuration of liquibase project is projected here.

Step1: Create a Maven Project using your IDE(in my case Eclipse3.7)

Step2: Place the liquibase Maven Plugin in the POM.xml

as shown below:


<project>

..........

<build>
 <plugins>
 <plugin>
 <groupId>org.liquibase</groupId>
 <artifactId>liquibase-maven-plugin</artifactId>
 <version>2.0.1</version>
 <dependencies>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.21</version>
 </dependency>
 </dependencies>
 <executions>
 <execution>
 <configuration>
 <propertyFile>src/main/resources/liquibase.properties</propertyFile>
 </configuration>
 <phase>process-resources</phase>
 <goals>
 <goal>update</goal>
 </goals>
 </execution>
 </executions>
 </plugin>
 </plugins>
 </build>

</project>

Step3: Place the liquibase.properties file in src/main/resources/ folder  – and a sample is here:


#liquibase.properties

changeLogFile=src/main/resources/changelogs/change_log.xml
 driver=com.mysql.jdbc.Driver
 url=jdbc:mysql://localhost:3306/testdbbkp
 username=user
 password=pwd

Note: the schema should exist before you run the project

Step4: Now create a change_log.xml to create all tables and even modifications to those tables as shown below


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

<changeSet id="1" author="pavan">
 <createTable tableName="department">
 <column name="id" type="int">
 <constraints primaryKey="true" nullable="false"/>
 </column>
 <column name="name" type="varchar(50)">
 <constraints nullable="false"/>
 </column>
 <column name="active" type="boolean" defaultValueBoolean="true"/>
 </createTable>
 </changeSet>

<changeSet id="2" author="Mantha Pavan Kumar">
 <comment>
 changeSet 2 with modifications to existing table;
 </comment>

<renameColumn tableName="department" oldColumnName="id" newColumnName="dept_id" columnDataType="int"/>
 <renameColumn tableName="department" oldColumnName="name" newColumnName="dept_name" columnDataType="varchar(50)"/>
 </changeSet>

<changeSet id="3" author="Mantha Pavan Kumar">
 <comment>
 changeSet 2 with modifications to existing table;
 </comment>

<loadData tableName="department" file="src/main/resources/csv/Book1.csv">
 <column name="dept_id" type="numeric"/>
 <column name="dept_name" type="string"/>
 </loadData>
 </changeSet>

<changeSet id="4" author="Mantha Pavan Kumar">
 <comment>
 creating table using sql commands with liqibase;
 </comment>

<sql>
 create table employee(id int,first_name varchar(50),last_name varchar(50));
 </sql>
 </changeSet>

<changeSet id="5" author="Mantha Pavan Kumar">
 <comment>
 creating complete database with data using sql backup file;
 </comment>

<sqlFile path="src/main/resources/sql/database.sql"/>
 </changeSet>

<changeSet id="6" author="Mantha Pavan Kumar">
 <comment>
 creating a lookuptable
 </comment>
 <createTable tableName="lookup">
 <column name="lookup_id" type="int">
 <constraints primaryKey="true" nullable="false"/>
 </column>
 <column name="lookup_name" type="varchar(50)">
 <constraints nullable="false"/>
 </column>
 <column name="active" type="boolean" defaultValueBoolean="true"/>
 </createTable>
 </changeSet>

</databaseChangeLog>

The above file shows you the different configurations that can be made. once the project is ran liquibase will create two tables databasechangelog and databasechangeloglock. now the liquibase will store all the modifications we do in databasechangelog table according to the <changeSet> defined in “change_log.xml”

to run the project issue the command : mvn clean install package

all your tables gets installed and track will be maintained

Happy coding 🙂

Do any one know there exist a Language named ‘R’ ?

Last week i was trying to draw some representation of biological data using java…which was very tough to code and i was searching for some language which make my work easy and i found it..Yes its ‘R’ Language.

R is an open source programming language and software environment for statistical computingand graphics. The R language is widely used among statisticians for developing statistical softwareand data analysis.

R is an implementation of the S programming language combined with lexical scoping semantics inspired by Scheme. S was created by John Chambers while at Bell Labs. R was created by Ross Ihaka and Robert Gentlemanat the University of Auckland, New Zealand, and now, R is developed by the R Development Core Team, of which Chambers is a member. R is named partly after the first names of the first two R authors (Robert Gentleman and Ross Ihaka), and partly as a play on the name of S.

R is part of the GNU Project. The source code for the R software environment is written primarily in C, FORTRAN, and R.R is freely available under the GNU General Public License, and pre-compiled binary versions are provided for various operating systems. R uses a command line interface; however, several graphical user interfaces are available for use with R.

One of the Graphical User Interface i feel good is “R-Studio”.

Happy Coding