// Copyright 2011 Leo Przybylski. All rights reserved.
   //
   // Redistribution and use in source and binary forms, with or without modification, are
   // permitted provided that the following conditions are met:
   //
   //    1. Redistributions of source code must retain the above copyright notice, this list of
   //       conditions and the following disclaimer.
   //
   //    2. Redistributions in binary form must reproduce the above copyright notice, this list
  //       of conditions and the following disclaimer in the documentation and/or other materials
  //       provided with the distribution.
  //
  // THIS SOFTWARE IS PROVIDED BY <COPYRIGHT HOLDER> ''AS IS'' AND ANY EXPRESS OR IMPLIED
  // WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
  // FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL <COPYRIGHT HOLDER> OR
  // CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
  // CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
  // SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON
  // ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
  // NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF
  // ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  //
  // The views and conclusions contained in the software and documentation are those of the
  // authors and should not be interpreted as representing official policies, either expressed
  // or implied, of Leo Przybylski.
  package com.rsmart.kuali.tools.liquibase;
  
  import liquibase.change.Change;
  import liquibase.change.ColumnConfig;
  import liquibase.change.ConstraintsConfig;
  import liquibase.change.core.*;
  import liquibase.changelog.ChangeSet;
  import liquibase.database.Database;
  import liquibase.database.jvm.JdbcConnection;
  import liquibase.database.structure.*;
  import liquibase.database.typeconversion.TypeConverter;
  import liquibase.database.typeconversion.TypeConverterFactory;
  import liquibase.diff.*;
  import liquibase.exception.DatabaseException;
  import liquibase.executor.ExecutorService;
  import liquibase.logging.LogFactory;
  import liquibase.parser.core.xml.LiquibaseEntityResolver;
  import liquibase.parser.core.xml.XMLChangeLogSAXParser;
  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.statement.ext.DescribeSequenceStatement;
  import liquibase.util.ISODateFormat;
  import liquibase.util.StringUtils;
  import liquibase.util.csv.CSVWriter;
  import liquibase.util.xml.DefaultXmlWriter;
  import liquibase.util.xml.XmlWriter;
  import org.w3c.dom.Document;
  import org.w3c.dom.Element;
  
  import javax.xml.parsers.DocumentBuilder;
  import javax.xml.parsers.DocumentBuilderFactory;
  import javax.xml.parsers.ParserConfigurationException;
  import java.io.*;
  import java.math.BigInteger;
  import java.sql.ResultSet;
  import java.sql.Statement;
  import java.util.*;
  
  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());
             try {
                 final DescribeSequenceStatement statement = new DescribeSequenceStatement(sequence.getName());
                 final BigInteger startValue = (BigInteger) ExecutorService.getInstance().getExecutor(referenceSnapshot.getDatabase()).queryForObject(statement, BigInteger.class);
                 change.setStartValue(startValue);
             }
             catch (Exception e) {
                 e.printStackTrace();
                 // quietly don't set start value
             }
 			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);
 		}
 	}
 }