比较数据库并使用liquibase生成sql脚本 [英] comparing databases and generating sql script using liquibase

问题描述

我正在比较使用与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:

1. 生成XML"diff"变更日志
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生成sql脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！