author：skate
time:2014/08/18

Database(Mysql)发版控制

The Liquibase Tool related Database

一.Installation & Configration
二.Advanced Usage of the Liquibase
三.Frequently Questions
二.Advanced Usage of the Liquibase

1.实际场景模拟
实际需求：需要把不同环境的不同分支的数据库变更合并，并按需求应用或回滚到不同的DB环境中，实现对DB发版的管理

DB的环境：dev,qa,sandbox,prod

规划DB发版目录，如：

database
Release-2014-05
db.Changelog-master.xml
000_CreateTable.sql
001_Init.sql
....
032_Update.sql
Release-2014-06
db.Changelog-master.xml
000_CreateTable.sql
001_Init.sql
....
192_Update.sql
Release-2014-07
db.Changelog-master.xml
000_CreateTable.sql
001_Init.sql
....
132_Update.sql


db.Changelog-master.xml是数据库变更的主文件，其包括每一个数据库的changeset文件，这样可以控制每个changeset的执行顺序和大小，如下是一个例子：

# more db.Changelog-master.xml

# more 000_CreateTable.sql
--liquibase formatted sql

--changeset skate:Release-2014-05_000_CreateTable.sql context:test

CREATE TABLE table3 (
id int(11) NOT NULL,
name varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=Innodb;

ALTER TABLE table3 CHANGE id id INT( 11 ) AUTO_INCREMENT;
ALTER TABLE table3 CHANGE name firstname VARCHAR( 255 );
INSERT INTO table3 (id, firstname) VALUES (NULL, 'name1'),(NULL, 'name2'), (NULL, 'name3');

--rollback drop table table3;
或者用xml文件

# more 000_CreateTable.xml

每个developer可以通过自己本机的liquibase把数据库的changset同步到dev环境

首先检查changelog是否有错误
[root@skatedb55 liquibase30]# sh liquibase --contexts=dev --defaultSchemaName=test5 --logLevel=debug --changeLogFile=/tmp/db.Changelog-master.xml --driver=com.mysql.jdbc.Driver --classpath=/usr/share/java/mysql-connector-java-5.1.17.jar --url="jdbc:mysql://localhost/test2?useUnicode=true&characterEncoding=UTF-8" --username=skate --password=skate validate
Liquibase Home: /mysql/software/liquibase30
DEBUG 8/19/14 2:45 PM:liquibase: Connected to skate@127.0.0.1@jdbc:mysql://localhost/test2?useUnicode=true&characterEncoding=UTF-8
DEBUG 8/19/14 2:45 PM:liquibase: Setting auto commit to false from true
INFO 8/19/14 2:46 PM:liquibase: Reading from test5.DATABASECHANGELOG
DEBUG 8/19/14 2:46 PM:liquibase: Executing QUERY database command: SELECT FILENAME,AUTHOR,ID,MD5SUM,DATEEXECUTED,ORDEREXECUTED,TAG,EXECTYPE,DESCRIPTION,COMMENTS FROM test5.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
DEBUG 8/19/14 2:46 PM:liquibase: /tmp/db.Changelog-master.xml: /tmp/database/Release-2014-05/000_CreateTable.sql::Release-2014-05_000_CreateTable.sql::skate: Computed checksum for inputStream as 00ba919fb68564b7f5f8dab227e4f6a8
DEBUG 8/19/14 2:46 PM:liquibase: /tmp/db.Changelog-master.xml: /tmp/database/Release-2014-05/000_CreateTable.sql::Release-2014-05_000_CreateTable.sql::skate: Computed checksum for 7:00ba919fb68564b7f5f8dab227e4f6a8: as 283384b19a5c2734d153462833903290
No validation errors found
Liquibase 'validate' Successful
[root@skatedb55 liquibase30]#