比较数据库并使用liquibase生成sql脚本

本文介绍了比较数据库并使用liquibase生成sql脚本的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在比较使用与ant集成的liquibase的两个数据库.但是它生成的输出类似于通用格式.它没有给出sql语句.请任何人告诉我如何使用与ant或命令行实用程序集成的liquibase比较两个数据库.

I'm comparing two databases using liquibase integrated with ant. But the output it is generating is like generic format. It is not giving sql statements. Please can anyone tell me how compare two databases using liquibase integrated with ant or command line utility.

推荐答案

获得表示两个数据库之间差异的SQL语句是一个两步操作:

Obtaining the SQL statements, representing the diff between two databases, is a two step operation:

生成XML"diff"变更日志

生成SQL语句

示例

此示例需要 liquibase.properties 文件(简化了命令行参数):

Example

This example requires a liquibase.properties file (simplifies the command-line parameters):

classpath=/path/to/jdbc/jdbc.jar driver=org.Driver url=jdbc:db_url1 username=user1 password=pass1 referenceUrl=jdbc:db_url2 referenceUsername=user2 referencePassword=pass2 changeLogFile=diff.xml

现在运行以下命令来创建SQL语句:

Now run the following commands to create the SQL statements:

liquibase diffChangeLog liquibase updateSQL > update.sql

liquibase的一个不错的功能是它还可以生成回滚SQL:

A nice feature of liquibase is that it can also generate the rollback SQL:

liquibase futureRollbackSQL > rollback.sql

更新

Liquibase不会在数据库之间生成数据差异，只会在架构之间生成数据差异.但是，可以将数据库数据转储为一系列变更集:

Update

Liquibase does not generate a data diff between databases, only the schema. However, it is possible to dump database data as a series of changesets:

liquibase --changeLogFile=data.xml --diffTypes=data generateChangeLog

一个人可以使用 data.xml 文件来迁移新表中包含的数据.

One can use the data.xml file to migrate data contained in new tables.

还可以使用groovy生成liquibase变更集.

Also possible to generate liquibase changesets using groovy.

import groovy.sql.Sql import groovy.xml.MarkupBuilder // // DB connection // this.class.classLoader.rootLoader.addURL(new URL("file:///home/path/to/h2-1.3.162.jar")) def sql = Sql.newInstance("jdbc:h2:db/db1","user","pass","org.h2.Driver") // // Generate liquibase changeset // def author = "generated" def id = 1 new File("extract.xml").withWriter { writer -> def xml = new MarkupBuilder(writer); xml.databaseChangeLog( "xmlns":"www.liquibase/xml/ns/dbchangelog", "xmlns:xsi":"www.w3/2001/XMLSchema-instance", "xsi:schemaLocation":"www.liquibase/xml/ns/dbchangelog www.liquibase/xml/ns/dbchangelog/dbchangelog-2.0.xsd" ) { changeSet(author:author, id:id++) { sql.eachRow("select * from employee") { row -> insert(tableName:"exmployee") { column(name:"empno", valueNumeric:row.empno) column(name:"name", value:row.name) column(name:"job", value:row.job) column(name:"hiredate", value:row.hiredate) column(name:"salary", valueNumeric:row.salary) } } } } }