您的位置: 首页  >  技术问答  >  使用liquibase比较数据库和生成sql脚本

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

分类: 技术问答 • 2022-04-21 11:36:08
问题描述：

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

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差异"变更日志
  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)
                }
            }
        }
    }
}

相关推荐