Friday, September 21, 2012

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.
  1. 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.
  2. 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.
  3. LiquiBase executes changes based on this XML file to handle different revisions of database structures and data.
  4. 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

22 comments:

  1. Thanks and I ll definitely have a look on the above URL.

    ReplyDelete
  2. hey great job Waheed, very clearly explained. Personally I don't like the idea of expressing all my DB structure and changes into a very proprietary format... so I am looking more into using the sqlfile option, but I need to investigate more and do some POC...

    ReplyDelete
  3. Thanks for the above tutorial. You explained the liquibase topic effectively.It is lengthy but informative .All the necessary details explained in your chapter.You did a good job.
    electronic signature

    ReplyDelete
  4. I gotta favorite this website it seems very helpful . best wireless cameras

    ReplyDelete
  5. It is almost impossible to locate knowledgeable Women and Men relating to That Topic. However, you seem to be exactly what goes on you're managing! Thank you hp officejet pro 8720 setup

    ReplyDelete
  6. It would be pretty difficult to set up their HP Printer in a suitable way. The unprotected printer users may vary for generative instruction for the printer tool. That’s why; our technical engineers have fixed to advance a website i.e., HP printer setup to give more information concerning HP Printer setup. So, if some users give access to this link, they will get to study how helpfully printers should be set up. Once the printer has effectively been set up also in a direct format, the users can flexibly print anything from their HP Printer system.

    ReplyDelete
  7. Do you have an HP printer? It needs some software to improve the performance of the hp printer, like driver software, smart app, scanner software, scan doctor. These all software are performing differently by respective works like scanning your hp printer performance, identifying the printer errors, checking ink cartridge level and also paper struck problems. So you need this type of software contact our website HP scan using smart app (smart app, scan doctor, scanner software)

    ReplyDelete
  8. Do you have an HP printer? It needs some software to improve the performance of the hp printer, like HP Easy scan. These all software are performing differently by respective works like scanning your hp printer performance, identifying the printer errors, checking ink cartridge level and also paper struck problems. So you need this type of software contact our website HP Easy Scan (HP Easy scan)

    ReplyDelete
  9. I want multiple printers for scanning and copying help. I have chosen to pick an HP printer. I am a new user of HP products, so I don’t know anything about its features and tasks. I need to start a multiple HP deskjet printer with the help of HP DeskJet 2652 installation setup.I am using my knowledge to open it in the internet browser and type my model number. After this command, I am finishing the next technical procedure. At the center of the process, I am experiencing issues. So I am sharing this issue with you too, guys. Could anyone refer to the right way to set up an HP printer using 123.hp.com/setup?

    ReplyDelete
  10. Am I a small business owner and having the essentials of a printer. So as per my needs, I suggest an HP printer. HP printer is totally an easy printing machine for users. This printing device has advanced and amazing features, so I suggest this brand most. I look further to using the HP printer via HP DeskJet 3752 driver downoad, but I don’t have enough skill to complete the setup process. rapidly, I look for the technician assists to help me with the HP printer setup procedure. So please someone can refer me to the proper instructions to set up an HP printer.

    ReplyDelete
  11. If you need to use any type of model of HP deskjet printer for your printing needs, you can set up your suggested model number of HP deskjet printers using HP Deskjet 2600 scan setup. This website assists you to set up the suggested model number of your HP printer in suitable ways. First of all, you want to open this link in your suggested browser and download the application step by step. After that, you want to perform shown steps in direct ways. If you get jammed in the procedure, you can take a good technician from an online technical specialist.

    ReplyDelete
  12. I want a printing machine for printing my official files. I accept using HP deskjet printer products for my printing needs. HP deskjet printer tools are the best quality printing device, so thousands of users suggest it for printing needs. So, I need to set up an HP deskjet printer for my printing needs. Structuring an HP deskjet printer is a hard task, so some users need a technical specialist. I am using my technical knowledge to set up an HP deskjet printer, but I am imperfect in finishing the setup process of my printer hp deskjet 2130 driver download. Due to poor technical knowledge, I am getting jam in the middle of the setup process of the HP deskjet printer. What to do? I am unable to guess the setup process of the HP deskjet printer. It has become a risky task for me, so I need to take the master technical help from a certified technical specialist. I am sharing the HP setup procedure, this discussion with all of you, guys. So please anyone can urge the simple ways to set up the HP deskjet printer perfectly. Your guidance would be praise.

    ReplyDelete
  13. Do you have an HP printer? It needs some software to improve the performance of the hp printer, like HP Drucker app. These all software are performing differently by respective works like scanning your hp printer performance, identifying the printer errors, checking ink cartridge level and also paper struck problems. So you need this type of software contact our website hp durcker app (HP drucker app)

    ReplyDelete
  14. Do you have an HP printer? It needs some software to improve the performance of the hp printer, like driver software,smart app, scanner software, scan doctor. These all software are performing differently by respective works like scanning your hp printer performance, identifying the printer errors, checking ink cartridge level and also paper struck problems. So you need this type of software contact our website HP scan using smart app (smart app, scan doctor, driver software, scanner software)

    ReplyDelete
  15. We can instruct you properly if you need to set up an HP wireless deskjet printer using 123.hp/setup. You can do the setup process easier, if you have correct technical knowledge about HP DeskJet 3752 driver downoad. First of all, you must go to open 123.hp/setup and open it in the internet browser. then, you should type the model number of your HP wireless deskjet printer in the shown box of 123.hp/setup. You can download the personal drivers of your HP wireless deskjet printer. After this process, you may get insert into setting up the HP wireless deskjet printer properly. If you don’t have any manual or choice, you can take the specialized expert instruction or assistance for completing the setup process of HP wireless deskjet printer using 123.hp/setup.

    ReplyDelete
  16. We are a completely preferred specialized guide, which is assisting all printer users online 24 hours for any type of technical problem. If you need to set up an HP deskjet printer using HP Deskjet 2600 scan setup, you can get the step-by-step technical instruction for setting up an HP deskjet printer indirectly. In the initial step, you want to open your web page in the chosen browser. Next step, you want to type the model number of your HP deskjet printer in the shown box. Following, you want to complete the instructions shown for setting up the HP printer. If you get inserted into the HP deskjet printer setup process, you can communicate with our live printer technician to get full instruction to set up the HP deskjet printer in a direct way. Our live technicians are available 24 hours to instruct you properly.

    ReplyDelete
  17. We can instruct you properly if you need to set up an HP Officejet Pro printer using 123.hp/setup. You can do the setup process easier if you have the correct technical knowledge about 123.hp/setup. First of all, you must go to open hp officejet pro 8710 and open it in the internet browser. then, you should type the model number of your HP Officejet Pro printer in the shown box of 123.hp/setup. You can download the personal drivers of your HP Officejet Pro printer. After this process, you may get inserted into setting up the HP Officejet Pro printer properly. If you don’t have any manual or choice, you can take the specialized expert instruction or assistance for completing the setup process of the HP Officejet Pro printer using 123.hp/setup.

    ReplyDelete
  18. I want a wireless printer for wireless printing help. I trust in using HP deskjet products for my wireless printer function. HP deskjet printer is an excellent printing system, so legion users choose it for their printing needs. I want to set up an HP deskjet printer with the assistance of hp deskjet 2541 driver download . First of all, I have opened this link in my suggested browser. Then, I entered the model number of my wireless deskjet printer in the shown box. I am finishing this step to introduce a printer driver or application for the HP deskjet printer setup procedure. After this step, I am getting stuck on finishing the HP deskjet printer using 123.hp.com/setup. I am facing technical issues to complete the HP deskjet printer setup procedure. So anyone can share the easy ways to set up an HP deskjet printer with the assistance of 123.hp.com/setup.

    ReplyDelete
  19. Do you have an HP printer? It needs some software to improve the performance of the hp printer, like driver HP drucker app. These all software are performing differently by respective works like scanning your hp printer performance, identifying the printer errors, checking ink cartridge level and also paper struck problems. So you need this type of software contact our website hp durcker app (Hp drucker app)

    ReplyDelete

How TOPT Works: Generating OTPs Without Internet Connection

Introduction Have you ever wondered how authentication apps like RSA Authenticator generate One-Time Passwords (OTPs) without requiring an i...