package liquibase.diff;

 import java.io.BufferedReader;

 import java.io.ByteArrayOutputStream;

 import java.io.File;

 import java.io.FileOutputStream;

 import java.io.FileReader;

 import java.io.FileWriter;

 import java.io.IOException;

 import java.io.PrintStream;

 import java.io.RandomAccessFile;

 import java.util.ArrayList;

 import java.util.Date;

 import java.util.List;

 import java.util.Map;

 import java.util.SortedSet;

 import java.util.TreeSet;

 import javax.xml.parsers.ParserConfigurationException;

 import liquibase.change.Change;

 import liquibase.change.ColumnConfig;

 import liquibase.change.ConstraintsConfig;

 import liquibase.change.core.AddColumnChange;

 import liquibase.change.core.AddForeignKeyConstraintChange;

 import liquibase.change.core.AddNotNullConstraintChange;

 import liquibase.change.core.AddPrimaryKeyChange;

 import liquibase.change.core.AddUniqueConstraintChange;

 import liquibase.change.core.CreateIndexChange;

 import liquibase.change.core.CreateSequenceChange;

 import liquibase.change.core.CreateTableChange;

 import liquibase.change.core.CreateViewChange;

 import liquibase.change.core.DropColumnChange;

 import liquibase.change.core.DropForeignKeyConstraintChange;

 import liquibase.change.core.DropIndexChange;

 import liquibase.change.core.DropNotNullConstraintChange;

 import liquibase.change.core.DropPrimaryKeyChange;

 import liquibase.change.core.DropSequenceChange;

 import liquibase.change.core.DropTableChange;

 import liquibase.change.core.DropUniqueConstraintChange;

 import liquibase.change.core.DropViewChange;

 import liquibase.change.core.InsertDataChange;

 import liquibase.change.core.LoadDataChange;

 import liquibase.change.core.LoadDataColumnConfig;

 import liquibase.change.core.ModifyDataTypeChange;

 import liquibase.changelog.ChangeSet;

 import liquibase.database.Database;

 import liquibase.database.structure.Column;

 import liquibase.database.structure.ForeignKey;

 import liquibase.database.structure.Index;

 import liquibase.database.structure.PrimaryKey;

 import liquibase.database.structure.Sequence;

 import liquibase.database.structure.Table;

 import liquibase.database.structure.UniqueConstraint;

 import liquibase.database.structure.View;

 import liquibase.database.typeconversion.TypeConverter;

 import liquibase.database.typeconversion.TypeConverterFactory;

 import liquibase.exception.DatabaseException;

 import liquibase.executor.ExecutorService;

 import liquibase.logging.LogFactory;

 import liquibase.serializer.ChangeLogSerializer;

 import liquibase.serializer.ChangeLogSerializerFactory;

 import liquibase.serializer.core.xml.XMLChangeLogSerializer;

 import liquibase.snapshot.DatabaseSnapshot;

 import liquibase.statement.DatabaseFunction;

 import liquibase.statement.core.RawSqlStatement;

 import liquibase.util.ISODateFormat;

 import liquibase.util.StringUtils;

 import liquibase.util.csv.CSVWriter;

 public class DiffResult {

     private String idRoot = String.valueOf(new Date().getTime());

     private int changeNumber = 1;

     private DatabaseSnapshot referenceSnapshot;

     private DatabaseSnapshot targetSnapshot;

     private DiffComparison productName;

     private DiffComparison productVersion;

     private SortedSet<Table> missingTables = new TreeSet<Table>();

     private SortedSet<Table> unexpectedTables = new TreeSet<Table>();

     private SortedSet<View> missingViews = new TreeSet<View>();

     private SortedSet<View> unexpectedViews = new TreeSet<View>();

     private SortedSet<View> changedViews = new TreeSet<View>();

     private SortedSet<Column> missingColumns = new TreeSet<Column>();

     private SortedSet<Column> unexpectedColumns = new TreeSet<Column>();

     private SortedSet<Column> changedColumns = new TreeSet<Column>();

     private SortedSet<ForeignKey> missingForeignKeys = new TreeSet<ForeignKey>();

     private SortedSet<ForeignKey> unexpectedForeignKeys = new TreeSet<ForeignKey>();

     private SortedSet<Index> missingIndexes = new TreeSet<Index>();

     private SortedSet<Index> unexpectedIndexes = new TreeSet<Index>();

     private SortedSet<PrimaryKey> missingPrimaryKeys = new TreeSet<PrimaryKey>();

     private SortedSet<PrimaryKey> unexpectedPrimaryKeys = new TreeSet<PrimaryKey>();

     private SortedSet<UniqueConstraint> missingUniqueConstraints = new TreeSet<UniqueConstraint>();

     private SortedSet<UniqueConstraint> unexpectedUniqueConstraints = new TreeSet<UniqueConstraint>();

     private SortedSet<Sequence> missingSequences = new TreeSet<Sequence>();

     private SortedSet<Sequence> unexpectedSequences = new TreeSet<Sequence>();

     private boolean diffData = false;

     private String dataDir = null;

     private String changeSetContext;

     private String changeSetAuthor;

     private ChangeLogSerializerFactory serializerFactory = ChangeLogSerializerFactory.getInstance();

     public DiffResult(DatabaseSnapshot referenceDatabaseSnapshot, DatabaseSnapshot targetDatabaseSnapshot) {

         this.referenceSnapshot = referenceDatabaseSnapshot;

         if (targetDatabaseSnapshot == null) {

             targetDatabaseSnapshot = new DatabaseSnapshot(referenceDatabaseSnapshot.getDatabase(), null);

         }

         this.targetSnapshot = targetDatabaseSnapshot;

     }

     public DiffComparison getProductName() {

         return productName;

     }

     public void setProductName(DiffComparison productName) {

         this.productName = productName;

     }

     public DiffComparison getProductVersion() {

         return productVersion;

     }

     public void setProductVersion(DiffComparison product) {

         this.productVersion = product;

     }

     public void addMissingTable(Table table) {

         missingTables.add(table);

     }

     public SortedSet<Table> getMissingTables() {

         return missingTables;

     }

     public void addUnexpectedTable(Table table) {

         unexpectedTables.add(table);

     }

     public SortedSet<Table> getUnexpectedTables() {

         return unexpectedTables;

     }

     public void addMissingView(View viewName) {

         missingViews.add(viewName);

     }

     public SortedSet<View> getMissingViews() {

         return missingViews;

     }

     public void addUnexpectedView(View viewName) {

         unexpectedViews.add(viewName);

     }

     public SortedSet<View> getUnexpectedViews() {

         return unexpectedViews;

     }

     public void addChangedView(View viewName) {

         changedViews.add(viewName);

     }

     public SortedSet<View> getChangedViews() {

         return changedViews;

     }

     public void addMissingColumn(Column columnName) {

         missingColumns.add(columnName);

     }

     public SortedSet<Column> getMissingColumns() {

         return missingColumns;

     }

     public void addUnexpectedColumn(Column columnName) {

         unexpectedColumns.add(columnName);

     }

     public SortedSet<Column> getUnexpectedColumns() {

         return unexpectedColumns;

     }

     public void addChangedColumn(Column columnName) {

         changedColumns.add(columnName);

     }

     public SortedSet<Column> getChangedColumns() {

         return changedColumns;

     }

     public void addMissingForeignKey(ForeignKey fkName) {

         missingForeignKeys.add(fkName);

     }

     public SortedSet<ForeignKey> getMissingForeignKeys() {

         return missingForeignKeys;

     }

     public void addUnexpectedForeignKey(ForeignKey fkName) {

         unexpectedForeignKeys.add(fkName);

     }

     public SortedSet<ForeignKey> getUnexpectedForeignKeys() {

         return unexpectedForeignKeys;

     }

     public void addMissingIndex(Index fkName) {

         missingIndexes.add(fkName);

     }

     public SortedSet<Index> getMissingIndexes() {

         return missingIndexes;

     }

     public void addUnexpectedIndex(Index fkName) {

         unexpectedIndexes.add(fkName);

     }

     public SortedSet<Index> getUnexpectedIndexes() {

         return unexpectedIndexes;

     }

     public void addMissingPrimaryKey(PrimaryKey primaryKey) {

         missingPrimaryKeys.add(primaryKey);

     }

     public SortedSet<PrimaryKey> getMissingPrimaryKeys() {

         return missingPrimaryKeys;

     }

     public void addUnexpectedPrimaryKey(PrimaryKey primaryKey) {

         unexpectedPrimaryKeys.add(primaryKey);

     }

     public SortedSet<PrimaryKey> getUnexpectedPrimaryKeys() {

         return unexpectedPrimaryKeys;

     }

     public void addMissingSequence(Sequence sequence) {

         missingSequences.add(sequence);

     }

     public SortedSet<Sequence> getMissingSequences() {

         return missingSequences;

     }

     public void addUnexpectedSequence(Sequence sequence) {

         unexpectedSequences.add(sequence);

     }

     public SortedSet<Sequence> getUnexpectedSequences() {

         return unexpectedSequences;

     }

     public void addMissingUniqueConstraint(UniqueConstraint uniqueConstraint) {

         missingUniqueConstraints.add(uniqueConstraint);

     }

     public SortedSet<UniqueConstraint> getMissingUniqueConstraints() {

         return this.missingUniqueConstraints;

     }

     public void addUnexpectedUniqueConstraint(UniqueConstraint uniqueConstraint) {

         unexpectedUniqueConstraints.add(uniqueConstraint);

     }

     public SortedSet<UniqueConstraint> getUnexpectedUniqueConstraints() {

         return unexpectedUniqueConstraints;

     }

     public boolean shouldDiffData() {

         return diffData;

     }

     public void setDiffData(boolean diffData) {

         this.diffData = diffData;

     }

     public String getDataDir() {

         return dataDir;

     }

     public void setDataDir(String dataDir) {

         this.dataDir = dataDir;

     }

     public String getChangeSetContext() {

         return changeSetContext;

     }

     public void setChangeSetContext(String changeSetContext) {

         this.changeSetContext = changeSetContext;

     }

     public boolean differencesFound() throws DatabaseException, IOException {

         boolean differencesInData = false;

         if (shouldDiffData()) {

             List<ChangeSet> changeSets = new ArrayList<ChangeSet>();

             addInsertDataChanges(changeSets, dataDir);

             differencesInData = !changeSets.isEmpty();

         }

         return getMissingColumns().size() > 0 || getMissingForeignKeys().size() > 0 || getMissingIndexes().size() > 0

                 || getMissingPrimaryKeys().size() > 0 || getMissingSequences().size() > 0

                 || getMissingTables().size() > 0 || getMissingUniqueConstraints().size() > 0

                 || getMissingViews().size() > 0 || getUnexpectedColumns().size() > 0

                 || getUnexpectedForeignKeys().size() > 0 || getUnexpectedIndexes().size() > 0

                 || getUnexpectedPrimaryKeys().size() > 0 || getUnexpectedSequences().size() > 0

                 || getUnexpectedTables().size() > 0 || getUnexpectedUniqueConstraints().size() > 0

                 || getUnexpectedViews().size() > 0 || differencesInData;

     }

     public void printResult(PrintStream out) throws DatabaseException {

         out.println("Reference Database: " + referenceSnapshot.getDatabase());

         out.println("Target Database: " + targetSnapshot.getDatabase());

         printComparision("Product Name", productName, out);

         printComparision("Product Version", productVersion, out);

         printSetComparison("Missing Tables", getMissingTables(), out);

         printSetComparison("Unexpected Tables", getUnexpectedTables(), out);

         printSetComparison("Missing Views", getMissingViews(), out);

         printSetComparison("Unexpected Views", getUnexpectedViews(), out);

         printSetComparison("Changed Views", getChangedViews(), out);

         printSetComparison("Missing Columns", getMissingColumns(), out);

         printSetComparison("Unexpected Columns", getUnexpectedColumns(), out);

         printColumnComparison(getChangedColumns(), out);

         printSetComparison("Missing Foreign Keys", getMissingForeignKeys(), out);

         printSetComparison("Unexpected Foreign Keys", getUnexpectedForeignKeys(), out);

         printSetComparison("Missing Primary Keys", getMissingPrimaryKeys(), out);

         printSetComparison("Unexpected Primary Keys", getUnexpectedPrimaryKeys(), out);

         printSetComparison("Unexpected Unique Constraints", getUnexpectedUniqueConstraints(), out);

         printSetComparison("Missing Unique Constraints", getMissingUniqueConstraints(), out);

         printSetComparison("Missing Indexes", getMissingIndexes(), out);

         printSetComparison("Unexpected Indexes", getUnexpectedIndexes(), out);

         printSetComparison("Missing Sequences", getMissingSequences(), out);

         printSetComparison("Unexpected Sequences", getUnexpectedSequences(), out);

     }

     private void printSetComparison(String title, SortedSet<?> objects, PrintStream out) {

         out.print(title + ": ");

         if (objects.size() == 0) {

             out.println("NONE");

         } else {

             out.println();

             for (Object object : objects) {

                 out.println("     " + object);

             }

         }

     }

     private void printColumnComparison(SortedSet<Column> changedColumns, PrintStream out) {

         out.print("Changed Columns: ");

         if (changedColumns.size() == 0) {

             out.println("NONE");

         } else {

             out.println();

             for (Column column : changedColumns) {

                 out.println("     " + column);

                 Column baseColumn = referenceSnapshot.getColumn(column.getTable().getName(), column.getName());

                 if (baseColumn != null) {

                     if (baseColumn.isDataTypeDifferent(column)) {

                         out.println("           from "

                                 + TypeConverterFactory.getInstance().findTypeConverter(referenceSnapshot.getDatabase())

                                         .convertToDatabaseTypeString(baseColumn, referenceSnapshot.getDatabase())

                                 + " to "

                                 + TypeConverterFactory

                                         .getInstance()

                                         .findTypeConverter(targetSnapshot.getDatabase())

                                         .convertToDatabaseTypeString(

                                                 targetSnapshot.getColumn(column.getTable().getName(), column.getName()),

                                                 targetSnapshot.getDatabase()));

                     }

                     if (baseColumn.isNullabilityDifferent(column)) {

                         Boolean nowNullable = targetSnapshot.getColumn(column.getTable().getName(), column.getName())

                                 .isNullable();

                         if (nowNullable == null) {

                             nowNullable = Boolean.TRUE;

                         }

                         if (nowNullable) {

                             out.println("           now nullable");

                         } else {

                             out.println("           now not null");

                         }

                     }

                 }

             }

         }

     }

     private void printComparision(String title, DiffComparison comparison, PrintStream out) {

         out.print(title + ":");

         if (comparison == null) {

             out.print("NULL");

             return;

         }

         if (comparison.areTheSame()) {

             out.println(" EQUAL");

         } else {

             out.println();

             out.println("     Reference:   '" + comparison.getReferenceVersion() + "'");

             out.println("     Target: '" + comparison.getTargetVersion() + "'");

         }

     }

     public void printChangeLog(String changeLogFile, Database targetDatabase) throws ParserConfigurationException,

             IOException, DatabaseException {

         ChangeLogSerializer changeLogSerializer = serializerFactory.getSerializer(changeLogFile);

         this.printChangeLog(changeLogFile, targetDatabase, changeLogSerializer);

     }

     public void printChangeLog(PrintStream out, Database targetDatabase) throws ParserConfigurationException,

             IOException, DatabaseException {

         this.printChangeLog(out, targetDatabase, new XMLChangeLogSerializer());

     }

     public void printChangeLog(String changeLogFile, Database targetDatabase, ChangeLogSerializer changeLogSerializer)

             throws ParserConfigurationException, IOException, DatabaseException {

         File file = new File(changeLogFile);

         if (!file.exists()) {

             LogFactory.getLogger().info(file + " does not exist, creating");

             FileOutputStream stream = new FileOutputStream(file);

             printChangeLog(new PrintStream(stream), targetDatabase, changeLogSerializer);

             stream.close();

         } else {

             LogFactory.getLogger().info(file + " exists, appending");

             ByteArrayOutputStream out = new ByteArrayOutputStream();

             printChangeLog(new PrintStream(out), targetDatabase, changeLogSerializer);

             String xml = new String(out.toByteArray());

             xml = xml.replaceFirst("(?ms).*<databaseChangeLog[^>]*>", "");

             xml = xml.replaceFirst("</databaseChangeLog>", "");

             xml = xml.trim();

             if ("".equals(xml)) {

                 LogFactory.getLogger().info("No changes found, nothing to do");

                 return;

             }

             String lineSeparator = System.getProperty("line.separator");

             BufferedReader fileReader = new BufferedReader(new FileReader(file));

             String line;

             long offset = 0;

             while ((line = fileReader.readLine()) != null) {

                 int index = line.indexOf("</databaseChangeLog>");

                 if (index >= 0) {

                     offset += index;

                 } else {

                     offset += line.getBytes().length;

                     offset += lineSeparator.getBytes().length;

                 }

             }

             fileReader.close();

             fileReader = new BufferedReader(new FileReader(file));

             fileReader.skip(offset);

             fileReader.close();

             // System.out.println("resulting XML: " + xml.trim());

             RandomAccessFile randomAccessFile = new RandomAccessFile(file, "rw");

             randomAccessFile.seek(offset);

             randomAccessFile.writeBytes("    ");

             randomAccessFile.write(xml.getBytes());

             randomAccessFile.writeBytes(lineSeparator);

             randomAccessFile.writeBytes("</databaseChangeLog>" + lineSeparator);

             randomAccessFile.close();

             // BufferedWriter fileWriter = new BufferedWriter(new

             // FileWriter(file));

             // fileWriter.append(xml);

             // fileWriter.close();

         }

     }

     /**

      * Prints changeLog that would bring the target database to be the same as the reference database

      */

     public void printChangeLog(PrintStream out, Database targetDatabase, ChangeLogSerializer changeLogSerializer)

             throws ParserConfigurationException, IOException, DatabaseException {

         List<ChangeSet> changeSets = new ArrayList<ChangeSet>();

         addMissingTableChanges(changeSets, targetDatabase);

         addMissingColumnChanges(changeSets, targetDatabase);

         addChangedColumnChanges(changeSets);

         addMissingPrimaryKeyChanges(changeSets);

         addUnexpectedPrimaryKeyChanges(changeSets);

         addUnexpectedForeignKeyChanges(changeSets);

         addMissingUniqueConstraintChanges(changeSets);

         addMissingIndexChanges(changeSets);

         addUnexpectedUniqueConstraintChanges(changeSets);

         if (diffData) {

             addInsertDataChanges(changeSets, dataDir);

         }

         addMissingForeignKeyChanges(changeSets);

         addUnexpectedIndexChanges(changeSets);

         addUnexpectedColumnChanges(changeSets);

         addMissingSequenceChanges(changeSets);

         addUnexpectedSequenceChanges(changeSets);

         addMissingViewChanges(changeSets);

         addUnexpectedViewChanges(changeSets);

         addChangedViewChanges(changeSets);

         addUnexpectedTableChanges(changeSets);

         changeLogSerializer.write(changeSets, out);

         out.flush();

     }

     private ChangeSet generateChangeSet(Change change) {

         ChangeSet changeSet = generateChangeSet();

         changeSet.addChange(change);

         return changeSet;

     }

     private ChangeSet generateChangeSet() {

         return new ChangeSet(generateId(), getChangeSetAuthor(), false, false, null, getChangeSetContext(), null);

     }

     private String getChangeSetAuthor() {

         if (changeSetAuthor != null) {

             return changeSetAuthor;

         }

         String author = System.getProperty("user.name");

         if (StringUtils.trimToNull(author) == null) {

             return "diff-generated";

         } else {

             return author + " (generated)";

         }

     }

     public void setChangeSetAuthor(String changeSetAuthor) {

         this.changeSetAuthor = changeSetAuthor;

     }

     public void setIdRoot(String idRoot) {

         this.idRoot = idRoot;

     }

     protected String generateId() {

         return idRoot + "-" + changeNumber++;

     }

     private void addUnexpectedIndexChanges(List<ChangeSet> changes) {

         for (Index index : getUnexpectedIndexes()) {

             if (index.getAssociatedWith().contains(Index.MARK_PRIMARY_KEY)

                     || index.getAssociatedWith().contains(Index.MARK_FOREIGN_KEY)

                     || index.getAssociatedWith().contains(Index.MARK_UNIQUE_CONSTRAINT)) {

                 continue;

             }

             DropIndexChange change = new DropIndexChange();

             change.setTableName(index.getTable().getName());

             change.setSchemaName(index.getTable().getSchema());

             change.setIndexName(index.getName());

             change.setAssociatedWith(index.getAssociatedWithAsString());

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingIndexChanges(List<ChangeSet> changes) {

         for (Index index : getMissingIndexes()) {

             CreateIndexChange change = new CreateIndexChange();

             change.setTableName(index.getTable().getName());

             change.setTablespace(index.getTablespace());

             change.setSchemaName(index.getTable().getSchema());

             change.setIndexName(index.getName());

             change.setUnique(index.isUnique());

             change.setAssociatedWith(index.getAssociatedWithAsString());

             if (index.getAssociatedWith().contains(Index.MARK_PRIMARY_KEY)

                     || index.getAssociatedWith().contains(Index.MARK_FOREIGN_KEY)

                     || index.getAssociatedWith().contains(Index.MARK_UNIQUE_CONSTRAINT)) {

                 continue;

             }

             for (String columnName : index.getColumns()) {

                 ColumnConfig column = new ColumnConfig();

                 column.setName(columnName);

                 change.addColumn(column);

             }

             changes.add(generateChangeSet(change));

         }

     }

     private void addUnexpectedPrimaryKeyChanges(List<ChangeSet> changes) {

         for (PrimaryKey pk : getUnexpectedPrimaryKeys()) {

             if (!getUnexpectedTables().contains(pk.getTable())) {

                 DropPrimaryKeyChange change = new DropPrimaryKeyChange();

                 change.setTableName(pk.getTable().getName());

                 change.setSchemaName(pk.getTable().getSchema());

                 change.setConstraintName(pk.getName());

                 changes.add(generateChangeSet(change));

             }

         }

     }

     private void addMissingPrimaryKeyChanges(List<ChangeSet> changes) {

         for (PrimaryKey pk : getMissingPrimaryKeys()) {

             AddPrimaryKeyChange change = new AddPrimaryKeyChange();

             change.setTableName(pk.getTable().getName());

             change.setSchemaName(pk.getTable().getSchema());

             change.setConstraintName(pk.getName());

             change.setColumnNames(pk.getColumnNames());

             change.setTablespace(pk.getTablespace());

             changes.add(generateChangeSet(change));

         }

     }

     private void addUnexpectedUniqueConstraintChanges(List<ChangeSet> changes) {

         for (UniqueConstraint uc : getUnexpectedUniqueConstraints()) {

             // Need check for nulls here due to NullPointerException using Postgres

             if (null != uc) {

                 if (null != uc.getTable()) {

                     DropUniqueConstraintChange change = new DropUniqueConstraintChange();

                     change.setTableName(uc.getTable().getName());

                     change.setSchemaName(uc.getTable().getSchema());

                     change.setConstraintName(uc.getName());

                     changes.add(generateChangeSet(change));

                 }

             }

         }

     }

     private void addMissingUniqueConstraintChanges(List<ChangeSet> changes) {

         for (UniqueConstraint uc : getMissingUniqueConstraints()) {

             // Need check for nulls here due to NullPointerException using Postgres

             if (null != uc)

                 if (null != uc.getTable()) {

                     AddUniqueConstraintChange change = new AddUniqueConstraintChange();

                     change.setTableName(uc.getTable().getName());

                     change.setTablespace(uc.getTablespace());

                     change.setSchemaName(uc.getTable().getSchema());

                     change.setConstraintName(uc.getName());

                     change.setColumnNames(uc.getColumnNames());

                     change.setDeferrable(uc.isDeferrable());

                     change.setInitiallyDeferred(uc.isInitiallyDeferred());

                     change.setDisabled(uc.isDisabled());

                     changes.add(generateChangeSet(change));

                 }

         }

     }

     private void addUnexpectedForeignKeyChanges(List<ChangeSet> changes) {

         for (ForeignKey fk : getUnexpectedForeignKeys()) {

             DropForeignKeyConstraintChange change = new DropForeignKeyConstraintChange();

             change.setConstraintName(fk.getName());

             change.setBaseTableName(fk.getForeignKeyTable().getName());

             change.setBaseTableSchemaName(fk.getForeignKeyTable().getSchema());

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingForeignKeyChanges(List<ChangeSet> changes) {

         for (ForeignKey fk : getMissingForeignKeys()) {

             AddForeignKeyConstraintChange change = new AddForeignKeyConstraintChange();

             change.setConstraintName(fk.getName());

             change.setReferencedTableName(fk.getPrimaryKeyTable().getName());

             change.setReferencedTableSchemaName(fk.getPrimaryKeyTable().getSchema());

             change.setReferencedColumnNames(fk.getPrimaryKeyColumns());

             change.setBaseTableName(fk.getForeignKeyTable().getName());

             change.setBaseTableSchemaName(fk.getForeignKeyTable().getSchema());

             change.setBaseColumnNames(fk.getForeignKeyColumns());

             change.setDeferrable(fk.isDeferrable());

             change.setInitiallyDeferred(fk.isInitiallyDeferred());

             change.setOnUpdate(fk.getUpdateRule());

             change.setOnDelete(fk.getDeleteRule());

             change.setReferencesUniqueColumn(fk.getReferencesUniqueColumn());

             changes.add(generateChangeSet(change));

         }

     }

     private void addUnexpectedSequenceChanges(List<ChangeSet> changes) {

         for (Sequence sequence : getUnexpectedSequences()) {

             DropSequenceChange change = new DropSequenceChange();

             change.setSequenceName(sequence.getName());

             change.setSchemaName(sequence.getSchema());

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingSequenceChanges(List<ChangeSet> changes) {

         for (Sequence sequence : getMissingSequences()) {

             CreateSequenceChange change = new CreateSequenceChange();

             change.setSequenceName(sequence.getName());

             change.setSchemaName(sequence.getSchema());

             changes.add(generateChangeSet(change));

         }

     }

     private void addUnexpectedColumnChanges(List<ChangeSet> changes) {

         for (Column column : getUnexpectedColumns()) {

             if (!shouldModifyColumn(column)) {

                 continue;

             }

             DropColumnChange change = new DropColumnChange();

             change.setTableName(column.getTable().getName());

             change.setSchemaName(column.getTable().getSchema());

             change.setColumnName(column.getName());

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingViewChanges(List<ChangeSet> changes) {

         for (View view : getMissingViews()) {

             CreateViewChange change = new CreateViewChange();

             change.setViewName(view.getName());

             change.setSchemaName(view.getSchema());

             String selectQuery = view.getDefinition();

             if (selectQuery == null) {

                 selectQuery = "COULD NOT DETERMINE VIEW QUERY";

             }

             change.setSelectQuery(selectQuery);

             changes.add(generateChangeSet(change));

         }

     }

     private void addChangedViewChanges(List<ChangeSet> changes) {

         for (View view : getChangedViews()) {

             CreateViewChange change = new CreateViewChange();

             change.setViewName(view.getName());

             change.setSchemaName(view.getSchema());

             String selectQuery = view.getDefinition();

             if (selectQuery == null) {

                 selectQuery = "COULD NOT DETERMINE VIEW QUERY";

             }

             change.setSelectQuery(selectQuery);

             change.setReplaceIfExists(true);

             changes.add(generateChangeSet(change));

         }

     }

     private void addChangedColumnChanges(List<ChangeSet> changes) {

         for (Column column : getChangedColumns()) {

             if (!shouldModifyColumn(column)) {

                 continue;

             }

             TypeConverter targetTypeConverter = TypeConverterFactory.getInstance().findTypeConverter(

                     targetSnapshot.getDatabase());

             boolean foundDifference = false;

             Column referenceColumn = referenceSnapshot.getColumn(column.getTable().getName(), column.getName());

             if (column.isDataTypeDifferent(referenceColumn)) {

                 ModifyDataTypeChange change = new ModifyDataTypeChange();

                 change.setTableName(column.getTable().getName());

                 change.setSchemaName(column.getTable().getSchema());

                 change.setColumnName(column.getName());

                 change.setNewDataType(targetTypeConverter.convertToDatabaseTypeString(referenceColumn,

                         targetSnapshot.getDatabase()));

                 changes.add(generateChangeSet(change));

                 foundDifference = true;

             }

             if (column.isNullabilityDifferent(referenceColumn)) {

                 if (referenceColumn.isNullable() == null || referenceColumn.isNullable()) {

                     DropNotNullConstraintChange change = new DropNotNullConstraintChange();

                     change.setTableName(column.getTable().getName());

                     change.setSchemaName(column.getTable().getSchema());

                     change.setColumnName(column.getName());

                     change.setColumnDataType(targetTypeConverter.convertToDatabaseTypeString(referenceColumn,

                             targetSnapshot.getDatabase()));

                     changes.add(generateChangeSet(change));

                     foundDifference = true;

                 } else {

                     AddNotNullConstraintChange change = new AddNotNullConstraintChange();

                     change.setTableName(column.getTable().getName());

                     change.setSchemaName(column.getTable().getSchema());

                     change.setColumnName(column.getName());

                     change.setColumnDataType(targetTypeConverter.convertToDatabaseTypeString(referenceColumn,

                             targetSnapshot.getDatabase()));

                     Object defaultValue = column.getDefaultValue();

                     String defaultValueString;

                     if (defaultValue != null) {

                         defaultValueString = targetTypeConverter.getDataType(defaultValue).convertObjectToString(

                                 defaultValue, targetSnapshot.getDatabase());

                         if (defaultValueString != null) {

                             change.setDefaultNullValue(defaultValueString);

                         }

                     }

                     changes.add(generateChangeSet(change));

                     foundDifference = true;

                 }

             }

             if (!foundDifference) {

                 throw new RuntimeException("Unknown difference");

             }

         }

     }

     private boolean shouldModifyColumn(Column column) {

         return column.getView() == null

                 && !referenceSnapshot.getDatabase().isLiquibaseTable(column.getTable().getName());

     }

     private void addUnexpectedViewChanges(List<ChangeSet> changes) {

         for (View view : getUnexpectedViews()) {

             DropViewChange change = new DropViewChange();

             change.setViewName(view.getName());

             change.setSchemaName(view.getSchema());

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingColumnChanges(List<ChangeSet> changes, Database database) {

         for (Column column : getMissingColumns()) {

             if (!shouldModifyColumn(column)) {

                 continue;

             }

             AddColumnChange change = new AddColumnChange();

             change.setTableName(column.getTable().getName());

             change.setSchemaName(column.getTable().getSchema());

             ColumnConfig columnConfig = new ColumnConfig();

             columnConfig.setName(column.getName());

             String dataType = TypeConverterFactory.getInstance().findTypeConverter(database)

                     .convertToDatabaseTypeString(column, database);

             columnConfig.setType(dataType);

             Object defaultValue = column.getDefaultValue();

             if (defaultValue != null) {

                 String defaultValueString = TypeConverterFactory.getInstance().findTypeConverter(database)

                         .getDataType(defaultValue).convertObjectToString(defaultValue, database);

                 if (defaultValueString != null) {

                     defaultValueString = defaultValueString.replaceFirst("'", "").replaceAll("'$", "");

                 }

                 columnConfig.setDefaultValue(defaultValueString);

             }

             if (column.getRemarks() != null) {

                 columnConfig.setRemarks(column.getRemarks());

             }

             ConstraintsConfig constraintsConfig = columnConfig.getConstraints();

             if (column.isNullable() != null && !column.isNullable()) {

                 if (constraintsConfig == null) {

                     constraintsConfig = new ConstraintsConfig();

                 }

                 constraintsConfig.setNullable(false);

             }

             if (column.isUnique()) {

                 if (constraintsConfig == null) {

                     constraintsConfig = new ConstraintsConfig();

                 }

                 constraintsConfig.setUnique(true);

             }

             if (constraintsConfig != null) {

                 columnConfig.setConstraints(constraintsConfig);

             }

             change.addColumn(columnConfig);

             changes.add(generateChangeSet(change));

         }

     }

     private void addMissingTableChanges(List<ChangeSet> changes, Database database) {

         for (Table missingTable : getMissingTables()) {

             if (referenceSnapshot.getDatabase().isLiquibaseTable(missingTable.getName())) {

                 continue;

             }

             CreateTableChange change = new CreateTableChange();

             change.setTableName(missingTable.getName());

             change.setSchemaName(missingTable.getSchema());

             if (missingTable.getRemarks() != null) {

                 change.setRemarks(missingTable.getRemarks());

             }

             for (Column column : missingTable.getColumns()) {

                 ColumnConfig columnConfig = new ColumnConfig();

                 columnConfig.setName(column.getName());

                 columnConfig.setType(TypeConverterFactory.getInstance().findTypeConverter(database)

                         .convertToDatabaseTypeString(column, database));

                 ConstraintsConfig constraintsConfig = null;

                 if (column.isPrimaryKey()) {

                     PrimaryKey primaryKey = null;

                     for (PrimaryKey pk : getMissingPrimaryKeys()) {

                         if (pk.getTable().getName().equalsIgnoreCase(missingTable.getName())) {

                             primaryKey = pk;

                         }

                     }

                     if (primaryKey == null || primaryKey.getColumnNamesAsList().size() == 1) {

                         constraintsConfig = new ConstraintsConfig();

                         constraintsConfig.setPrimaryKey(true);

                         constraintsConfig.setPrimaryKeyTablespace(column.getTablespace());

                         if (primaryKey != null) {

                             constraintsConfig.setPrimaryKeyName(primaryKey.getName());

                             getMissingPrimaryKeys().remove(primaryKey);

                         }

                     }

                 }

                 if (column.isAutoIncrement()) {

                     columnConfig.setAutoIncrement(true);

                 }

                 if (column.isNullable() != null && !column.isNullable()) {

                     if (constraintsConfig == null) {

                         constraintsConfig = new ConstraintsConfig();

                     }

                     constraintsConfig.setNullable(false);

                 }

                 if (column.isUnique()) {

                     if (constraintsConfig == null) {

                         constraintsConfig = new ConstraintsConfig();

                     }

                     constraintsConfig.setUnique(true);

                 }

                 if (constraintsConfig != null) {

                     columnConfig.setConstraints(constraintsConfig);

                 }

                 Object defaultValue = column.getDefaultValue();

                 if (defaultValue == null) {

                     // do nothing

                 } else if (column.isAutoIncrement()) {

                     // do nothing

                 } else if (defaultValue instanceof Date) {

                     columnConfig.setDefaultValueDate((Date) defaultValue);

                 } else if (defaultValue instanceof Boolean) {

                     columnConfig.setDefaultValueBoolean(((Boolean) defaultValue));

                 } else if (defaultValue instanceof Number) {

                     columnConfig.setDefaultValueNumeric(((Number) defaultValue));

                 } else if (defaultValue instanceof DatabaseFunction) {

                     columnConfig.setDefaultValueComputed((DatabaseFunction) defaultValue);

                 } else {

                     columnConfig.setDefaultValue(defaultValue.toString());

                 }

                 if (column.getRemarks() != null) {

                     columnConfig.setRemarks(column.getRemarks());

                 }

                 change.addColumn(columnConfig);

             }

             changes.add(generateChangeSet(change));

         }

     }

     private void addUnexpectedTableChanges(List<ChangeSet> changes) {

         for (Table unexpectedTable : getUnexpectedTables()) {

             DropTableChange change = new DropTableChange();

             change.setTableName(unexpectedTable.getName());

             change.setSchemaName(unexpectedTable.getSchema());

             changes.add(generateChangeSet(change));

         }

     }

     private void addInsertDataChanges(List<ChangeSet> changeSets, String dataDir) throws DatabaseException, IOException {

         try {

             String schema = referenceSnapshot.getSchema();

             for (Table table : referenceSnapshot.getTables()) {

                 List<Change> changes = new ArrayList<Change>();

                 List<Map> rs = ExecutorService

                         .getInstance()

                         .getExecutor(referenceSnapshot.getDatabase())

                         .queryForList(

                                 new RawSqlStatement("SELECT * FROM "

                                         + referenceSnapshot.getDatabase().escapeTableName(schema, table.getName())));

                 if (rs.size() == 0) {

                     continue;

                 }

                 List<String> columnNames = new ArrayList<String>();

                 for (Column column : table.getColumns()) {

                     columnNames.add(column.getName());

                 }

                 // if dataDir is not null, print out a csv file and use loadData

                 // tag

                 if (dataDir != null) {

                     String fileName = table.getName().toLowerCase() + ".csv";

                     if (dataDir != null) {

                         fileName = dataDir + "/" + fileName;

                     }

                     File parentDir = new File(dataDir);

                     if (!parentDir.exists()) {

                         parentDir.mkdirs();

                     }

                     if (!parentDir.isDirectory()) {

                         throw new RuntimeException(parentDir + " is not a directory");

                     }

                     CSVWriter outputFile = new CSVWriter(new FileWriter(fileName));

                     String[] dataTypes = new String[columnNames.size()];

                     String[] line = new String[columnNames.size()];

                     for (int i = 0; i < columnNames.size(); i++) {

                         line[i] = columnNames.get(i);

                     }

                     outputFile.writeNext(line);

                     for (Map row : rs) {

                         line = new String[columnNames.size()];

                         for (int i = 0; i < columnNames.size(); i++) {

                             Object value = row.get(columnNames.get(i).toUpperCase());

                             if (dataTypes[i] == null && value != null) {

                                 if (value instanceof Number) {

                                     dataTypes[i] = "NUMERIC";

                                 } else if (value instanceof Boolean) {

                                     dataTypes[i] = "BOOLEAN";

                                 } else if (value instanceof Date) {

                                     dataTypes[i] = "DATE";

                                 } else {

                                     dataTypes[i] = "STRING";

                                 }

                             }

                             if (value == null) {

                                 line[i] = "NULL";

                             } else {

                                 if (value instanceof Date) {

                                     line[i] = new ISODateFormat().format(((Date) value));

                                 } else {

                                     line[i] = value.toString();

                                 }

                             }

                         }

                         outputFile.writeNext(line);

                     }

                     outputFile.flush();

                     outputFile.close();

                     LoadDataChange change = new LoadDataChange();

                     change.setFile(fileName);

                     change.setEncoding("UTF-8");

                     change.setSchemaName(schema);

                     change.setTableName(table.getName());

                     for (int i = 0; i < columnNames.size(); i++) {

                         String colName = columnNames.get(i);

                         LoadDataColumnConfig columnConfig = new LoadDataColumnConfig();

                         columnConfig.setHeader(colName);

                         columnConfig.setName(colName);

                         columnConfig.setType(dataTypes[i]);

                         change.addColumn(columnConfig);

                     }

                     changes.add(change);

                 } else { // if dataDir is null, build and use insert tags

                     for (Map row : rs) {

                         InsertDataChange change = new InsertDataChange();

                         change.setSchemaName(schema);

                         change.setTableName(table.getName());

                         // loop over all columns for this row

                         for (int i = 0; i < columnNames.size(); i++) {

                             ColumnConfig column = new ColumnConfig();

                             column.setName(columnNames.get(i));

                             Object value = row.get(columnNames.get(i).toUpperCase());

                             if (value == null) {

                                 column.setValue(null);

                             } else if (value instanceof Number) {

                                 column.setValueNumeric((Number) value);

                             } else if (value instanceof Boolean) {

                                 column.setValueBoolean((Boolean) value);

                             } else if (value instanceof Date) {

                                 column.setValueDate((Date) value);

                             } else { // string

                                 column.setValue(value.toString().replace("\\", "\\\\"));

                             }

                             change.addColumn(column);

                         }

                         // for each row, add a new change

                         // (there will be one group per table)

                         changes.add(change);

                     }

                 }

                 if (changes.size() > 0) {

                     ChangeSet changeSet = generateChangeSet();

                     for (Change change : changes) {

                         changeSet.addChange(change);

                     }

                     changeSets.add(changeSet);

                 }

             }

         } catch (Exception e) {

             throw new RuntimeException(e);

         }

     }

 }