comparing databases and generating sql script usin

Question

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.

Answer 1

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

1. Generate the XML "diff" changelog
2. Generate SQL statements

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

Now run the following commands to create the SQL statements:

liquibase diffChangeLog
liquibase updateSQL > update.sql

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

liquibase futureRollbackSQL > rollback.sql

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

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

Update 2:

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)
                }
            }
        }
    }
}