Continuous integration of database development-Liquibase introduction and application

This series of articles
Continuous integration of database development-SQL Server deployment and upgrade Tool
Continuous integration of database development-SQL Server database structure comparison
Continuous integration of database development-Methods and Procedures
Continuous integration of database development-Liquibase introduction and application
Continuous integration of databases-project configuration of CruiseControl. Net

In the continuous integration of database development-methods and procedures, we talked about the application of Liquibase in continuous integration. Here we will briefly introduce the specific application methods of Liquibase in the process of continuous integration.

Liquibase Introduction

Liquibase is an open-source database reconstruction tool for tracking, managing, and applying database changes. It saves all database changes (including structure and data) in the XML file to facilitate version control.
Liquibase has the following features:
* Does not rely on specific databases. Currently, 12 types of databases are supported, including Oracle, SQL Server, DB2, MySql, Sybase, PostgreSQL, and Cach, in this way, the application system can support multiple databases during database deployment and upgrade.
* The database comparison function is provided, and the comparison results are saved in XML. Based on this XML, you can use Liquibase to easily deploy or upgrade the database.
* Stores database changes in XML format. The author and ID uniquely identify a change. This feature supports Database Change merging. Therefore, multiple developers can work simultaneously.
* Save the database modification history (DatabaseChangeHistory) in the database, and automatically skip the changed application (ChangSet) during Database Upgrade ).
* The rollback function of a changed application is provided to roll back changes of an application by time, quantity, or tag. In this way, developers can easily restore the database status at any time point.
* Generate database modification documents (in HTML format)
* Provides independent IDE and Eclipse plug-ins for Data Reconstruction

The core of Liquibase is to store the changed XML, for example:

<? Xml version = "1.0" encoding = "UTF-8"?>
<DatabaseChangeLog
Xmlns = "http://www.liquibase.org/xml/ns/dbchangelog/1.6"
Xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"
Xsi: schemaLocation = "http://www.liquibase.org/xml/ns/dbchangelog/1.6
Http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.6.xsd>
 
<ChangeSet id = "1" author = "bob">
<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" defaultValue = "1"/>
</CreateTable>
</ChangeSet>
</DatabaseChangeLog>

Among them, changeSet contains different database changes, covering almost all Database Change types. The supported types depend on the API. Here are several examples:
* Create and Delete tables, views, stored procedures, primary keys, foreign keys, and indexes.
* Rename tables, views, and columns
* Add column default values, unique constraints, and non-empty constraints.
* Merge two columns
* Create a dictionary table based on the data of a table

In addition, Liquibase allows you to run your own SQL scripts and Shell programs.
I have mentioned "almost covered" because I have found that user-defined data and user-defined functions are not supported. Because my new database development principles abandon their dependence on them, it is not painful.

Application of Liquibase in continuous integration

If you are developing Based On Java, you may prefer Liquibase. If you are developing based On Ruby On Rails, you may choose Migration and Rake. Based on. Net, I tested Red Gate ($), Migration. Net, Machine. Migrations, Power Tools, and so on. Finally, I chose Liquibase. How to integrate it into my development process? I select the command line and command line-based MsBuild task.
Use the command line to create a bat file and execute Liquibase to deploy the user database (see deploy. bat in my example ).
Msbuild provides a full set of functions, such as comparison, deployment, and document generation, for the entire development process, and of course CC. Net (see ci. proj In the example ).

Specific applications include:
* After a developer completes a stage of development locally, use ci. the diff target in proj compares local data and baseline data to generate changelog. xml: submit it to SVN (here, the Author in changeSet in the XML file takes the User Name of the developer's local machine)
* CC. net detects SVN changes, triggers the continuous integration process, and CALLS ci. deploy in proj creates the latest data structure in a test database, and then tests the object class and access layer (the same is true for developers testing on local machines ). The test is successful. Use the doc in ci. proj to generate the document and publish it to IIS on the continuous integration server.
* If it is a trunk project, Tag it on SVN after the test is complete, and then use publish in ci. proj to publish the database deployment and Upgrade Program (including changelog. xml)
* The released database deployment and Upgrade Program contains deploy. bat for deployment personnel to deploy or upgrade the existing product system database.

Merge changelog. xml:
* Each time a developer submits a new changelog. xml file, I will Merge the new changeSet through the SVN Merge function.
* This process can be automatically completed through SVN. However, due to the huge impact of database changes, you need to carefully review the process.

Test. proj: this is a test case written in MsBuild. it tests the database comparison and upgrade functions provided by ci. proj.

Description of ci. proj:
* Special processing of dtproperties
Once you have used the SQL Server Enterprise Manager to operate the database, the table will automatically generate the attributes of some database objects. Although it is displayed as a system table in the Enterprise Manager, its object_id is large, so that liquibase regards it as a user table, which will affect the database comparison results. Therefore, in ci. in proj, check whether the two data involved in the comparison exist in the table. If not, create the table.
* <Target Name = "CheckExec">
MsBuild uses the Java command line to call liquibase. If an error occurs during execution, MsBuild cannot get this error. This will cause continuous integration of CC. Net to succeed if it fails. No better way is found. I write the output obtained by running Java to the exec. log file, and then use CheckExec to check whether there is a Failed keyword. Other keywords may need to be checked. If you find out, please let me know.

<Target Name = "CheckExec">
<ReadLinesFromFile File = "$ (ExecLog)">
<Output TaskParameter = "Lines" PropertyName = "ExecOutput"/>
</ReadLinesFromFile>
<Message Text = "$ (ExecOutput)" Importance = "high"/>
<StringComparison Comparison = "Contains" Param1 = "$ (ExecOutput)" Param2 = "Failed">
<Output TaskParameter = "Result" PropertyName = "StringSearchResult"/>
</StringComparison>
<Delete Files = "$ (ExecLog)"/>
<Error Text = "liquibase report error" Condition = "$ (StringSearchResult)"/>
</Target>

Attachment: continuous integration project file

Update20080718 detected two bugs in the change history generated by the Liquibase application SQL Server:
* When you delete a column that defines a foreign key (reference other tables), the delete Constraint script is placed after the delete column script, resulting in deployment failure.
* The Constraint of NotNull is added. columnDataType and defaultNullValue are missing in the script, causing deployment failure.