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.

Continuous İntegration

They say a picture tells much more..from that truth i draw the big picture makes things clear

Continuous integration is a practise comes from Extreme Programming. In large projects  all developers makes their indiviual works but when it is time to integrate them it is a big problem. A lot more time has to be spend to solve integration problems. So each member of a team should make their commits frequently to a repository  this gives the team to re-use the common code early and recognise integration problems early.  An integration server such as hudson, jenkins etc. builds code, runs unit & integration test, inspects the quality of the code, deploys the build, updates the database to the same version of the code and runs Automated User Interface tests.Then developers look at the test results. QA manager  looks at Continuous Inpestion results. He/she opens issues on issue tracking system which continuous inspection rules marked as major or critical. Then the errors found in tests results are fixed and improvements are made for QA.  Finally a clean integrated work is ready for the production. You may read alot more about CI on Matin Fowler’ site.