DBMigration.java

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package nz.co.gregs.dbvolution;

import java.io.Serializable;
import nz.co.gregs.dbvolution.internal.query.QueryDetails;
import nz.co.gregs.dbvolution.databases.DBDatabase;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import nz.co.gregs.dbvolution.actions.DBMigrationAction;
import nz.co.gregs.dbvolution.datatypes.QueryableDatatype;
import nz.co.gregs.dbvolution.exceptions.*;
import nz.co.gregs.dbvolution.expressions.*;
import nz.co.gregs.dbvolution.query.*;

public class DBMigration<M extends DBRow> extends RowDefinition implements Serializable{

	private static final long serialVersionUID = 1L;

	private final M mapper;
	private final ArrayList<DBRow> optionalTables = new ArrayList<>();
	private final DBDatabase database;
	
	private final ArrayList<SortProvider> sortColumns = new ArrayList<>();
	Boolean cartesian = false;
	Boolean blank = false;


	private DBMigration(DBDatabase database, M migrationMapper) {
		this.mapper = DBRow.copyDBRow(migrationMapper);
		this.database = database.copy();
	}

	public DBMigration<M> copy() {
		DBMigration<M> copiedMigration = using(database, mapper);
		copiedMigration.optionalTables.addAll(optionalTables);
		copiedMigration.sortColumns.addAll(sortColumns);
		copiedMigration.cartesian = cartesian;
		copiedMigration.blank = blank;
		return copiedMigration;
	}

	/**
	 * Create a migration that uses the mapper provided.
	 *
	 * <p>
	 * Mapper uses a subclass of a particular DBRow to create rows for that DBRow
	 * and insert them into the table.</p>
	 *
	 * <p>
	 * Central to this concept is using other existing data in table, represented
	 * by their own DBRow classes, transformed using DBvolution expressions</p>
	 * <pre>
	 * public static class MigrateToFight extends Fight {
	 * public Villain baddy = new Villain();
	 * public Hero goody = new Hero();
	 * {
	 * baddy.name.permittedPattern("Dr%");
	 * hero = goody.column(goody.name).asExpressionColumn();
	 * villain = baddy.column(baddy.name).asExpressionColumn();
	 * }
	 * }
	 * database.createTable(new Fight());
	 * DBMigration migration = DBMigration.using(new MigrateToFight());
	 * migration.createAllRows(database);
	 * </pre>
	 *
	 * @param <MAPPER> the transformation to apply
	 * @param database the database to work with
	 * @param migrationMapper the transformation class
	 * @return a DBMigration object
	 */
	public static <MAPPER extends DBRow> DBMigration<MAPPER> using(DBDatabase database, MAPPER migrationMapper) {
		return new DBMigration<MAPPER>(database, migrationMapper);
	}

	public DBMigration<M> addTablesAndExpressions(DBQuery query) {
		Field[] fields = mapper.getClass().getFields();
		if (fields.length == 0) {
			throw new UnableToAccessDBMigrationFieldException(this, null);
		}
		for (Field field : fields) {
			field.setAccessible(true);
			final Object value;
			try {
				value = field.get(mapper);
				if (value != null && DBRow.class.isAssignableFrom(value.getClass())) {
					if (value instanceof DBRow) {
						final DBRow dbRow = (DBRow) value;
						dbRow.removeAllFieldsFromResults();
						if (optionalTables.contains(dbRow)) {
							query.addOptional(dbRow);
						} else {
							query.add(dbRow);
						}
					}
				} else if (value != null && QueryableDatatype.class.isAssignableFrom(value.getClass())) {
					final QueryableDatatype<?> qdtValue = (QueryableDatatype) value;
					if ((value instanceof QueryableDatatype) && qdtValue.hasColumnExpression()) {
						query.addExpressionColumn(value, qdtValue);
						final DBExpression[] columnExpressions = qdtValue.getColumnExpression();
						for (DBExpression columnExpression : columnExpressions) {
							if (!columnExpression.isAggregator()) {
								query.addGroupByColumn(value, columnExpression);
							}
						}
					}
				}
			} catch (IllegalArgumentException | IllegalAccessException ex) {
				throw new UnableToAccessDBMigrationFieldException(this, field, ex);
			}
		}
		return this;
	}

	@SuppressWarnings("unchecked")
	M createInstanceOfMappingTarget() throws InstantiationException, IllegalAccessException, NoSuchMethodException, IllegalArgumentException, InvocationTargetException {
		Class<? extends DBRow> aClass = mapper.getClass();
		return (M) aClass.getConstructor().newInstance();
	}

	private M getMappedTarget(DBQueryRow row) {
		try {
			M newTarget = createInstanceOfMappingTarget();
			Field[] fields = mapper.getClass().getFields();
			for (Field field : fields) {
				field.setAccessible(true);
				final Object value;
				try {
					value = field.get(mapper);
					if (value != null && DBRow.class.isAssignableFrom(value.getClass())) {
						if (value instanceof DBRow) {
							DBRow gotDefinedRow = row.get((DBRow) value);
							try {
								Field targetField = newTarget.getClass().getField(field.getName());
								targetField.set(newTarget, gotDefinedRow);
							} catch (NoSuchFieldException ex) {
							}
						}
					} else if (value != null && QueryableDatatype.class.isAssignableFrom(value.getClass())) {
						if ((value instanceof QueryableDatatype) && ((QueryableDatatype) value).hasColumnExpression()) {
							final QueryableDatatype<?> expressionColumnValue = row.getExpressionColumnValue(value);
							try {
								Field targetField = newTarget.getClass().getField(field.getName());
								targetField.set(newTarget, expressionColumnValue);
							} catch (NoSuchFieldException ex) {
							}
						}
					}
				} catch (IllegalArgumentException ex) {
					throw new UnableToSetDBMigrationFieldException(newTarget, field, ex);
				} catch (IllegalAccessException ex) {
					throw new UnableToAccessDBMigrationFieldException(newTarget, field, ex);
				}
			}
			return newTarget;
		} catch (InstantiationException | IllegalAccessException | NoSuchMethodException | IllegalArgumentException | InvocationTargetException ex) {
			throw new UnableToInstantiateDBMigrationSubclassException(this, ex);
		}
	}

	/**
	 * Gets all the migrated rows using conditions in the DBMigration and the
	 * supplied examples.
	 *
	 * @param extraExamples extra rows defining additional criteria
	 * @return a list of DBReport instances representing the results of the report
	 * query. 1 Database exceptions may be thrown
	 * @throws java.sql.SQLException java.sql.SQLException
	 * @throws nz.co.gregs.dbvolution.exceptions.AccidentalBlankQueryException
	 * thrown if no conditions have been set and blank queries have not been
	 * explicitly allowed
	 */
	public List<M> getAllRows(DBRow... extraExamples) throws SQLException, AccidentalCartesianJoinException, AccidentalBlankQueryException {
		DBQuery query = getDBQuery(extraExamples);
		List<DBQueryRow> allRows = query.getAllRows();
		List<M> reportRows = getInsertedRowsFromQueryResults(allRows);
		return reportRows;
	}

	@Override
	public String toString() {
		StringBuilder str = new StringBuilder();
		Field[] fields = this.getClass().getFields();
		for (Field field : fields) {
			field.setAccessible(true);
			final Object value;
			try {
				value = field.get(this);
				if (value != null && DBRow.class.isAssignableFrom(value.getClass())) {
					if (value instanceof DBRow) {
						final DBRow dbRow = (DBRow) value;
						str.append(dbRow.toString());
					}
				} else if (value != null && QueryableDatatype.class.isAssignableFrom(value.getClass())) {
					if ((value instanceof QueryableDatatype)) {
						QueryableDatatype<?> qdt = (QueryableDatatype) value;
						str.append(field.getName()).append(": ").append(qdt.toString()).append(" ");
					}
				}
			} catch (IllegalArgumentException | IllegalAccessException ex) {
				throw new UnableToAccessDBMigrationFieldException(this, field, ex);
			}
		}
		return str.toString();
	}

	/**
	 * Gets all the report rows of the migration limited by the supplied example
	 * rows.
	 *
	 * <p>
	 * All supplied rows should be using a DBRow subclass that is included in the
	 * report.
	 *
	 * <p>
	 * Builtin report limitation will be used, the example rows supply further
	 * details for constraining the report.
	 *
	 * <p>
	 * This method allows you to create generic reports and apply dynamic
	 * limitations such as date ranges, department name, and other highly variable
	 * parameters.
	 *
	 * @param rows rows
	 * @return a list of DBReport instances representing the results of the report
	 * query. 1 Database exceptions may be thrown
	 * @throws java.sql.SQLException java.sql.SQLException
	 * @throws nz.co.gregs.dbvolution.exceptions.AccidentalBlankQueryException
	 * thrown if no conditions have been set and blank queries have not been
	 * explicitly allowed
	 */
	public List<M> getRows(DBRow... rows) throws SQLException, AccidentalCartesianJoinException, AccidentalBlankQueryException {
		DBQuery query = getDBQuery(rows);
		List<DBQueryRow> allRows = query.getAllRows();
		List<M> reportRows = getInsertedRowsFromQueryResults(allRows);
		return reportRows;
	}

	/**
	 * Gets all the report rows of this DBMigration limited by the supplied
	 * example rows but reduce the result to only those that match the conditions.
	 *
	 * <p>
	 * All conditions should only reference the fields/column of the DBMigration.
	 *
	 * <p>
	 * All supplied rows should be using a DBRow subclass that is included in the
	 * report.
	 *
	 * <p>
	 * Built-in report limitation will be used, the example rows supply further
	 * details for constraining the report.
	 *
	 * <p>
	 * This method allows you to create generic reports and apply dynamic
	 * limitations such as date ranges, department name, and other highly variable
	 * parameters.
	 *
	 * @param rows rows example rows that provide extra criteria
	 * @param conditions the conditions that will be supplied to the WHERE or
	 * HAVING clause of the query
	 * @return a list of DBReport instances representing the results of the report
	 * query
	 * @throws java.sql.SQLException Database exceptions may be thrown
	 * @throws nz.co.gregs.dbvolution.exceptions.AccidentalBlankQueryException
	 * thrown if no conditions have been set and blank queries have not been
	 * explicitly allowed
	 */
	public List<M> getRowsHaving(DBRow[] rows, BooleanExpression... conditions) throws SQLException, AccidentalCartesianJoinException, AccidentalBlankQueryException {
		DBQuery query = getDBQuery(rows);
		List<M> reportRows;
		List<DBQueryRow> allRows = query.addConditions(conditions).getAllRows();
		reportRows = getInsertedRowsFromQueryResults(allRows);
		return reportRows;
	}

	private List<M> getInsertedRowsFromQueryResults(List<DBQueryRow> allRows) {
		List<M> reportRows = allRows
				.stream()
				.map((t) -> getMappedTarget(t))
				.collect(Collectors.toList());
		return reportRows;
	}

	/**
	 * Generates and returns the actual SQL to be used by this DBQueryInsert to
	 * select the rows to insert.
	 *
	 * <p>
	 * Good for debugging and great for DBAs, this is how you find out what
	 * DBvolution is really doing.
	 *
	 * <p>
	 * Generates the SQL query for retrieving the objects but does not execute the
	 * SQL. Use
	 * {@link #getAllRows(nz.co.gregs.dbvolution.DBRow...) the getAllRows method}
	 * to retrieve the rows.
	 *
	 * <p>
	 * See also
	 * {@link #getSQLForCount(nz.co.gregs.dbvolution.DBRow...)  }
	 *
	 * @param rows additional conditions to apply to the report.
	 * @return a String of the SQL that will be used by this DBQuery. 1 Database
	 * exceptions may be thrown
	 */
	public String getSQLForQuery(DBRow... rows) {
		DBQuery query = getDBQuery(rows);
		return query.getSQLForQuery();
	}

	/**
	 * Generates and returns the actual SQL to be used by this DBQueryInsert to
	 * insert the queried rows.
	 *
	 * <p>
	 * Good for debugging and great for DBAs, this is how you find out what
	 * DBvolution is really doing.
	 *
	 * <p>
	 * Generates the SQL query for retrieving the objects but does not execute the
	 * SQL. Use
	 * {@link #getAllRows(nz.co.gregs.dbvolution.DBRow...) the getAllRows method}
	 * to retrieve the rows.
	 *
	 * <p>
	 * See also
	 * {@link #getSQLForCount(nz.co.gregs.dbvolution.DBRow...)  }
	 *
	 * @param rows additional conditions to apply to the report.
	 * @return a String of the SQL that will be used by this DBQuery. 1 Database
	 * exceptions may be thrown
	 */
	public String getSQLForInsert(DBRow... rows) {
		DBMigrationAction<M> action = getDBMigrationAction(rows);
		ArrayList<String> sqlStatements = action.getSQLStatements(database);
		if (sqlStatements.size() > 0) {
			return sqlStatements.get(0);
		} else {
			return "";
		}
	}

	/**
	 * Returns the SQL query that will used to count the rows returned for the
	 * supplied DBReport
	 *
	 * <p>
	 * Use this method to check the SQL that will be executed during
	 * {@link DBReport#count(nz.co.gregs.dbvolution.databases.DBDatabase, nz.co.gregs.dbvolution.DBReport, nz.co.gregs.dbvolution.DBRow...)  the count method}
	 *
	 * @param rows additional conditions to be applied.
	 * @return a String of the SQL query that will be used to count the rows
	 * returned by this report 1 Database exceptions may be thrown
	 * @throws java.sql.SQLException java.sql.SQLException
	 */
	public String getSQLForCount(DBRow... rows) throws SQLException {
		DBQuery query = getDBQuery(rows);
		return query.getSQLForCount();
	}

	/**
	 * Count the rows on the database without retrieving the rows.
	 *
	 * <p>
	 * Creates a
	 * {@link #getSQLForCount(nz.co.gregs.dbvolution.DBRow...)   count query}
	 * for the report and conditions and retrieves the number of rows that would
	 * have been returned had
	 * {@link #getAllRows(nz.co.gregs.dbvolution.DBRow...) getAllRows method}
	 * been called.
	 *
	 * @param rows additional conditions for the query.
	 * @return the number of rows that have or will be retrieved. 1 Database
	 * exceptions may be thrown
	 * @throws java.sql.SQLException java.sql.SQLException
	 */
	public Long count(DBRow... rows) throws SQLException, AccidentalCartesianJoinException, AccidentalBlankQueryException {
		DBQuery setUpQuery = getDBQuery(rows);
		return setUpQuery.count();
	}

	/**
	 * Sets the sort order of DBReport (field and/or method) by the given column
	 * providers.
	 *
	 * <p>
	 * For example the following code snippet will sort by just the name column:
	 * <pre>
	 * CustomerReport customers = ...;
	 * customers.setSortOrder(customers.column(customers.name));
	 * </pre>
	 *
	 * @param columns a list of columns to sort the query by.
	 * @return this DBReport instance
	 */
//	public DBMigration<M> setSortOrder(SortProvider... columns) {
//		sortColumns.clear();
//		sortColumns.addAll(Arrays.asList(columns));
//		return this;
//	}

	/**
	 * Sets the sort order of migration (field and/or method) by the given column
	 * providers.
	 *
	 * <p>
	 * ONLY USE FIELDS FROM THE SAME INSTANCE.
	 * <p>
	 * For example the following code snippet will sort by the name and
	 * accountNumber columns:
	 * <pre>
	 * CustomerReport customers = ...;
	 * customers.setSortOrder(customers.name, customers.accountNumber);
	 * </pre>
	 *
	 * @param columns a list of columns to sort the query by.
	 * @return this DBReport instance
	 */
//	public DBMigration<M> setSortOrder(QueryableDatatype<?>... columns) {
//		List<SortProvider> columnProviders = new ArrayList<>();
//		for (QueryableDatatype<?> qdt : columns) {
//			final ColumnProvider expr = this.column(qdt);
//			columnProviders.add(expr.getSortProvider());
//		}
//		sortColumns.addAll(columnProviders);
//		return this;
//	}

	/**
	 * Add the rows as optional tables in the query.
	 *
	 * <p>
	 * Optional tables are joined to the query using an outer join and results are
	 * returned regardless of whether there is a matching row in the optional
	 * table.</p>
	 *
	 * <p>
	 * Optional rows may contain only DBNull values, that is there getValue()
	 * method will return NULL and isDBNull() will be true.</p>
	 *
	 * @param examples additional tables to include in the query if possible.
	 */
	public void addAsOptionalTables(DBRow... examples) {
		optionalTables.addAll(Arrays.asList(examples));
	}

	DBQuery getDBQuery(DBRow... rows) {
		DBQuery query = database.getDBQuery();
		query.setBlankQueryAllowed(blank);
		query.setCartesianJoinsAllowed(cartesian);
		addTablesAndExpressions(query);
		query.addExtraExamples(rows);
		query.setSortOrder(this.getSortColumns());
		return query;
	}

	/**
	 * Returns the list of sort columns
	 *
	 * @return the sortColumns
	 */
	public SortProvider[] getSortColumns() {
		return sortColumns.toArray(new SortProvider[]{});
	}

	/**
	 * Suppresses Cartesian join error protection.
	 *
	 * <p>
	 * DBvolution protects you using accidental Cartesian joins but use this
	 * function if a Cartesian is required.</p>
	 *
	 * <p>
	 * Cartesian joins occur when there is no connection between 2 (or more)
	 * tables. Normally all tables are connect by a chain of relationships,
	 * usually primary key to foreign key.</p>
	 * <p>
	 * Sometimes a connection is missed: for instance 2 unrelated tables are being
	 * compared by price, but the price relating expression has not been added. In
	 * this case DBvolution will throw an {@link AccidentalCartesianJoinException}
	 * and abort the query. This exception avoids creating a probably massive
	 * dataset that will reduce database and network performance
	 * significantly.</p>
	 * <p>
	 * However there are valid cases for a Cartesian join: finding all possible
	 * combinations of cake and coffee for instance.</p>
	 * <p>
	 * If you are sure you need a Cartesian join, use this method to avoid the
	 * error-checking and the {@link AccidentalCartesianJoinException}</p>
	 *
	 * @param setting True if you need a Cartesian join in this DBQueryInsert.
	 * @return this DBQueryInsert object
	 */
	public DBMigration<M> setCartesianJoinAllowed(Boolean setting) {
		cartesian = setting;
		return this;
	}

	/**
	 * Change the Default Setting of Disallowing Blank Queries
	 *
	 * <p>
	 * A common mistake is creating a query without supplying criteria and
	 * accidentally retrieving a huge number of rows.
	 *
	 * <p>
	 * DBvolution detects this situation and, by default, throws a
	 * {@link nz.co.gregs.dbvolution.exceptions.AccidentalBlankQueryException AccidentalBlankQueryException}
	 * when it happens.
	 *
	 * <p>
	 * To change this behaviour, and allow blank queries, call
	 * {@code setBlankQueriesAllowed(true)}.
	 *
	 * @param setting - TRUE to allow blank queries, FALSE to return it to the
	 * default setting.
	 * @return this DBQueryInsert instance
	 */
	public DBMigration<M> setBlankQueryAllowed(Boolean setting) {
		blank = setting;
		return this;
	}

	public void createAllRows(DBRow... extraExamples) throws SQLException {
		DBMigrationAction<M> migrate = getDBMigrationAction(extraExamples);
		migrate.migrate(database);
	}

	private DBMigrationAction<M> getDBMigrationAction(DBRow[] extraExamples) {
		return new DBMigrationAction<>(this, mapper, extraExamples);
	}

	/**
	 * Perform a migration validation using DBMigrationValidation.
	 *
	 * <p>
	 * Validation will process all the available rows and try migrate them or to
	 * identify why they were not migrated. FAILURE TO MIGRATE MAY BE CORRECT
	 * BEHAVIOUR. Carefully check the results to determine if there are issues
	 * with the migration or the data.</p>
	 *
	 * @param extraExamples extra examples
	 * @return result from the migration validation
	 * @throws SQLException if the database has an issue with the validation script
	 */
	public DBMigrationValidation.Results validateAllRows(DBRow... extraExamples) throws SQLException {

		DBMigrationValidation<M> validate = new DBMigrationValidation<>(this, mapper, extraExamples);
		return validate.validate(database);
	}

	QueryDetails getQueryDetails() {
		return this.getDBQuery().getQueryDetails();
	}

	public boolean getCartesianJoinsAllowed() {
		return cartesian;
	}

	public boolean getBlankQueryAllowed() {
		return blank;
	}

	public M getMapper() {
		return mapper;
	}
	
	public List<DBRow> getOptionalTables(){
		if(optionalTables.isEmpty()){
			return new ArrayList<DBRow>();
		}
		return Arrays.asList((DBRow[]) optionalTables.toArray());
	}

}