数据库版本控制工具 Dbdeploy

What

Dbdeploy 是thoughtworks公司开发的开源的，java-based数据库版本管理工具，曾获得第17届Jolt奖。

 

 

Why

系统=Code和db配合一起工作，因此两者的版本需要确保一致。

软件的开发过程是一个迭代过过程，数据库也是如此。因此数据库的版本和代码一样同样也需要被管理。

 

Without Dbdeploy

修改PowerDesinger建模文件--->通知dba手动修改测试环境数据库--->修改程序--->

开发完成（上线）--->dba查看建模文件将修改部署至生产环境

信息传递主要靠文档和口口相传

 

With Dbdeploy

修改程序---增加相应的sql脚本--->DBA查看变更的脚本---加入新的脚本如index，表空间等

开发完成上线-->再次执行相应任务

 

 

与代码版本的不同之处

数据库版本化与代码版本化的区别在于数据库中的生产数据是现场（即用户）创造的，当我们的表结构发生改变时，不能直接用drop table然后再create table，因为这样会导致生产数据丢失。而代码则完全由开发人员创造，可以用完全覆盖的方式升级。由于这点不同，致使数据库在版本化的过程中必然要采用与代码不同的方法。

 

 

Dbdeploy 工作原理

dbdeploy works by checking to see if a particular delta script has been run against a particular database. To do this it uses the name of the delta script plus the name of the delta set (which will be "All" unless otherwise specified) and compares this against the content of the schema version table. If a delta script that has already been applied to a database is subsequently modified that subsequent modification will not be applied to the database.

 

 

1.   Read the entries from the changelog table to which scripts had currently been applied: Changes currently applied to database: (none)

2.   Scanned the script directory for sql scripts, found the two provided (001_create_table.sql and 002_insert_data.sql), and parsed the file names to discover their numbers: Scripts available: 1, 2

3.   Worked out what needed applying: To be applied: 1, 2

4.   Applied those changes to the database

 

执行的两步走

1.   在changelog表中比对已经部署的sql脚本，并生成需要部署的所有脚本

2.   批量执行生成的sql脚本，进行部署

3.   执行target时，除非指定outputfile 属性将未部署的变更输出到本地sql文件中，再由dba手动执行，否则默认地会将两个操作一起执行(3.0M2之前，必须再去跑一个antsqltask以执行相应的sql)

 

 

使用注意事项

1.        Make sure that EVERY database modification is written as a delta script to be picked up by dbdeploy.

2.        Follow the naming convention for delta scripts. Script names must begin with a number that indicates the order in which it should be run (1.sql gets run first, then 2.sql and so on). You can optionally add a comment to the file name to describe what the script does (eg 1 Created the CustomerAddress table.sql) the comment will get written to the schema version table as the script is applied. 只认数字，不认描述信息（其中数字前可以任意+0，最好采用00001这样的编号开始）

3.        You can optionally add an undo section to your script. Write the script so it performs the do action first (eg create the CustomerAddress table) once all do actions have been scripted include the token --//@UNDO on a new line. Include the undo steps after this token.

4.        Always perform a local build before you check in, this way the problem is caught and can be fixed prior to it getting under version control.

5.        Use continuous integration – if your CI build breaks then the source code repository doesn’t get tagged until you’ve fixed the problem.

 

 

 

优点：

方便自动构建部署，满足了包括db在内的所有代码可以进行持续集成和顺利迁移

和PowerDesinger相比，方便做文件比对。

尽早做数据迁移（在开发阶段进行数据迁移，而不是最后在去做，方便尽早发现问题）

 

缺点：

缺少可视化环境，不是很方便

Bug：和ant整合有乱码问题

 

 

类似工具：Unitils

 

 

 

http://blog.csdn.net/tywo45/archive/2008/05/25/2480078.aspx

http://www.sadalage.com/2008/01/exprerience_using_dbdeploy_on_1.html

本文基于Dbdeploy 3.0M2