对比数据库，并使用liquibase genrating sql脚本 [英] comparing databases and genrating sql script using liquibase

问题描述

我用的比较与liquibase蚂蚁集成了两个数据库。但它产生的输出是像一般格式。它是不是给SQL语句。请任何一个可以告诉我如何比较使用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 any one tell me how compare two databases using liquibase integrated with ant or command line utility.

我解释一些示例。

在此先感谢，
Bhasker。

Thanks in advance, Bhasker.

推荐答案

获取SQL语句，再presenting两个数据库之间的差异，是一个两步骤操作：

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

1. 生成XML差异的changelog


2. 生成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":"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(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)
                }
            }
        }
    }
}

这篇关于对比数据库，并使用liquibase genrating sql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！