My requirement is I want to diff two database then I want to generate a diff sql.Note I want to do it through java only. I am new to Liquibase.I have found a java api called CommandLineUtils using which I am able to generate changeLog of a new database. When I am executing method doDiffToChangeLog then my changeLog xml is empty. Can you please tell me where I am doing wrong.
here is my java code
Database database=liqui.createDatabaseObject(OracleDriver.class.getClassLoader(), "jdbc:oracle:thin:@localhost:1521:XE", "archive_pmd", "archive_pmd", "oracle.jdbc.driver.OracleDriver", "archive_pmd", null, null);
liqui.doGenerateChangeLog("test.xml", database, "archive_pmd", null, null, null, null);
Database database2=liqui.createDatabaseObject(OracleDriver.class.getClassLoader(), "jdbc:oracle:thin:@localhost:1521:XE", "archive_dbs", "archive_dbs", "oracle.jdbc.driver.OracleDriver", "archive_pmd", null, null);
liqui.doDiffToChangeLog("chageLog.xml", database2, database);
here is my generated test.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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="DELL (generated)" id="1331066786490-1">
<createTable schemaName="archive_pmd" tableName="PMD00_SEQUENCE">
<column name="PMD00_ID" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="TABLE_IDENTIFIER" type="VARCHAR2(50 BYTE)"/>
<column defaultValueNumeric="0" name="CURRENT_VALUE" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="LAST_ACCESSED_TS" type="DATE"/>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-2">
<createTable schemaName="archive_pmd" tableName="PMD01_META_DATA">
<column name="PMD01_ID" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="CATEGORY" type="VARCHAR2(30 BYTE)">
<constraints nullable="false"/>
</column>
<column name="NAME" type="VARCHAR2(50 BYTE)">
<constraints nullable="false"/>
</column>
<column name="CODE" type="VARCHAR2(100 BYTE)">
<constraints nullable="false"/>
</column>
<column name="STATUS" type="VARCHAR2(1 BYTE)">
<constraints nullable="false"/>
</column>
<column name="PMD01_METADATAVER" type="NUMBER(4,0)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATEDATE" type="DATE">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-3">
<createTable schemaName="archive_pmd" tableName="PMD13_DB_APPLICATION">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="APPLICATIONNAME" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="ENVIRONMENT" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="SERVERIP" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="USERID" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="PASSWORD" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DBTYPE" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="SERVERPORT" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="SERVICE" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-4">
<createTable schemaName="archive_pmd" tableName="PMD14_DB_VERSION">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MAJORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MINORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="PATCHVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DATEEXECUTED" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DML_SCRIPT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="STATUS" type="VARCHAR2(20 BYTE)">
<constraints nullable="false"/>
</column>
<column name="REQUESTOR" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DML_SCRIPTNAME" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DB_APPLICATION_FK" type="NUMBER">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-5">
<createTable schemaName="archive_pmd" tableName="PMD15_DBCOMMIT_LOG">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MAJORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MINORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="PATCHVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DATEEXECUTED" type="DATE">
<constraints nullable="false"/>
</column>
<column name="SCRIPT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="EXECUTIONSTATUS" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="REQUESTOR" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="LOGREPORT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DBAPPLICATIONFK" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="LOGREPORTNAME" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="SCRIPTNAME" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-6">
<createIndex indexName="SYS_C004087" schemaName="archive_pmd" tableName="PMD00_SEQUENCE" tablespace="SYSTEM" unique="true">
<column name="PMD00_ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-7">
<createIndex indexName="SYS_C004084" schemaName="archive_pmd" tableName="PMD01_META_DATA" tablespace="SYSTEM" unique="true">
<column name="PMD01_ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-8">
<createIndex indexName="SYS_C004050" schemaName="archive_pmd" tableName="PMD13_DB_APPLICATION" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-9">
<createIndex indexName="SYS_C004062" schemaName="archive_pmd" tableName="PMD14_DB_VERSION" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066786490-10">
<createIndex indexName="SYS_C004076" schemaName="archive_pmd" tableName="PMD15_DBCOMMIT_LOG" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-1">
<createTable schemaName="archive_pmd" tableName="PMD00_SEQUENCE">
<column name="PMD00_ID" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="TABLE_IDENTIFIER" type="VARCHAR2(50 BYTE)"/>
<column defaultValueNumeric="0" name="CURRENT_VALUE" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="LAST_ACCESSED_TS" type="DATE"/>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-2">
<createTable schemaName="archive_pmd" tableName="PMD01_META_DATA">
<column name="PMD01_ID" type="NUMBER">
<constraints nullable="false"/>
</column>
<column name="CATEGORY" type="VARCHAR2(30 BYTE)">
<constraints nullable="false"/>
</column>
<column name="NAME" type="VARCHAR2(50 BYTE)">
<constraints nullable="false"/>
</column>
<column name="CODE" type="VARCHAR2(100 BYTE)">
<constraints nullable="false"/>
</column>
<column name="STATUS" type="VARCHAR2(1 BYTE)">
<constraints nullable="false"/>
</column>
<column name="PMD01_METADATAVER" type="NUMBER(4,0)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATEDATE" type="DATE">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-3">
<createTable schemaName="archive_pmd" tableName="PMD13_DB_APPLICATION">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="APPLICATIONNAME" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="ENVIRONMENT" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="SERVERIP" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="USERID" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="PASSWORD" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DBTYPE" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="SERVERPORT" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="SERVICE" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-4">
<createTable schemaName="archive_pmd" tableName="PMD14_DB_VERSION">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MAJORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MINORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="PATCHVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DATEEXECUTED" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DML_SCRIPT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="STATUS" type="VARCHAR2(20 BYTE)">
<constraints nullable="false"/>
</column>
<column name="REQUESTOR" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DML_SCRIPTNAME" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="DB_APPLICATION_FK" type="NUMBER">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-5">
<createTable schemaName="archive_pmd" tableName="PMD15_DBCOMMIT_LOG">
<column name="ID" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MAJORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="MINORVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="PATCHVERSION" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="DATEEXECUTED" type="DATE">
<constraints nullable="false"/>
</column>
<column name="SCRIPT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="EXECUTIONSTATUS" type="VARCHAR2(200 BYTE)">
<constraints nullable="false"/>
</column>
<column name="REQUESTOR" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="LOGREPORT" type="BLOB">
<constraints nullable="false"/>
</column>
<column name="DMLASTUPDATE" type="DATE">
<constraints nullable="false"/>
</column>
<column name="DBAPPLICATIONFK" type="NUMBER(17,0)">
<constraints nullable="false"/>
</column>
<column name="LOGREPORTNAME" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
<column name="SCRIPTNAME" type="VARCHAR2(400 BYTE)">
<constraints nullable="false"/>
</column>
</createTable>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-6">
<createIndex indexName="SYS_C004087" schemaName="archive_pmd" tableName="PMD00_SEQUENCE" tablespace="SYSTEM" unique="true">
<column name="PMD00_ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-7">
<createIndex indexName="SYS_C004084" schemaName="archive_pmd" tableName="PMD01_META_DATA" tablespace="SYSTEM" unique="true">
<column name="PMD01_ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-8">
<createIndex indexName="SYS_C004050" schemaName="archive_pmd" tableName="PMD13_DB_APPLICATION" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-9">
<createIndex indexName="SYS_C004062" schemaName="archive_pmd" tableName="PMD14_DB_VERSION" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
<changeSet author="DELL (generated)" id="1331066972401-10">
<createIndex indexName="SYS_C004076" schemaName="archive_pmd" tableName="PMD15_DBCOMMIT_LOG" tablespace="SYSTEM" unique="true">
<column name="ID"/>
</createIndex>
</changeSet>
</databaseChangeLog>
=== here is my changeLog.xml(diff one)
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<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"/>
Note archive_dbs does not contain any table.Why liquibase didint showed that table in archive_pmd is missing in archive_dbs?
Anyway Its working now..here is the updated code..I am loving liquibase now
Database database=CommandLineUtils.createDatabaseObject(OracleDriver.class.getClassLoader(), "jdbc:oracle:thin:@xxx.xx.xxx.xx:1521:XE", "archive_dbs", "123", "oracle.jdbc.driver.OracleDriver", "archive_dbs", null, null);
//CommandLineUtils.doGenerateChangeLog("test.xml", database, "archive_dbs", null, null, null, null);uncomment to generate changelog
Database database2=CommandLineUtils.createDatabaseObject(OracleDriver.class.getClassLoader(), "jdbc:oracle:thin:@yy.yy.yy.yy:1521:XE", "archive_dbs", "123", "oracle.jdbc.driver.OracleDriver", "archive_dbs", null, null);
/* CommandLineUtils.doDiffToChangeLog("chageLog.xml", database2, database);//This can generate changeLog for diff
ResourceAccessor resource=new FileSystemResourceAccessor();
File sql=new File("SQLscript.sql");
FileWriter writer=new FileWriter(sql);
Liquibase base=new Liquibase("chageLog.xml",resource,database);
base.update("Update",writer);//store diff sql to file,but donot execute it
base.update("Update");//execute diff sql*/
//CommandLineUtils.doDiff(database2, database);//show diff in console
/* DatabaseSnapshot dbsnap1=new DatabaseSnapshot(database,"archive_dbs");
DatabaseSnapshot dbsnap2=new DatabaseSnapshot(database2,"archive_dbs");*/
Diff res=new Diff(database2,database);
DiffResult result=res.compare();
if(result.differencesFound()){
System.out.println("Different");
}else{
System.out.println("Equal");
}
ByteArrayOutputStream baos=new ByteArrayOutputStream();
PrintStream stream=new PrintStream(baos);
result.printResult(stream);
System.out.println(baos.toString());//show diff report
System.out.println("Done");