Continuous Database İntegration

        For most of us it is a new concept.  The  main idea behind the forest is to record database structural changes to a version control system (svn,git etc.)  at the same time code base versioning . With this way you keep the database structure corresponding to the code base. Thus there is no need to execute scripts in various enviroments manually.

 Liquibase is one of the best continuous database integration tool i may suggest. With this tool you write your database scripts in database change log files in human readable format by creating a changeSet.

And then run changeSets via ant, maven or command line the way shown below

<?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 id="prj-issue-1111" author="ozay.duman">

        <sql>

            CREATE TABLE BOOK( book_id NUMBER PRIMARY KEY,book_name VARCHAR2(30),author_name VARCHAR2(40),book_isbn VARCHAR2(20) )

                    </sql>

   </changeSet>

</databaseChangeLog>

liquibase --driver=com.mysql.jdbc.Driver \
     --classpath=/path/to/classes \
     --changeLogFile=com/example/db.changelog.xml \
     --url="jdbc:mysql://localhost/example" \
     --username=user \
     --password=asdf \
     migrate

Magic happens, check your database you will see two tables databasechandelog and databasechandeloglock created. The databasechangelog table contains a list of all the statements that have been run against the database. The databasechangeloglock table is used to make sure two machines don't attempt to modify the database at the same time.

Another feature of Liquibase is database change documentation generation:

liqubase.sh --driver=oracle.jdbc.OracleDriver \
        --url=jdbc:oracle:thin:@test:1521:test \
        --username=user \
        --password=asdf \
        --changeLogFile=path/to/changelog.xml
    dbDoc \
        /docs/dbdoc

You can also use Liquibase as database integrity checkher. To do this, you should use sqlcheck inside preCondition tag as follows:

<databaseChangeLog>
<changeSet id="1" author="bob">
        <preConditions onFail="WARN">
            <sqlCheck expectedResult="0">select count(*) from MY_TABLE</sqlCheck>
        </preConditions>
    </changeSet>
</databaseChangeLog>

When expectedResult differs from the result of the sql statement it will warn you.