EDIT

// $Id$

/**

* Copyright (C) 2009 EDIT

* See LICENSE.TXT at the top of this package for the full license terms.

*/

package eu.etaxonomy.cdm.database.update;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Arrays;

import java.util.List;

import org.apache.commons.lang.StringUtils;

import org.apache.log4j.Logger;

import eu.etaxonomy.cdm.database.ICdmDataSource;

/**

 * @author a.mueller

 * @date 16.09.2010

 *

 */

	private final List<String> columnNames;

	private final List<String> columnTypes;

	private final List<Object> defaultValues;

	private final List<Boolean> isNotNull;

	private final List<String> referencedTables;

	private final boolean includeCdmBaseAttributes;

	private final boolean includeIdentifiableEntity;

	private final boolean includeAnnotatableEntity;

	protected List<ISchemaUpdaterStep> mnTablesStepList = new ArrayList<ISchemaUpdaterStep>();

	private String primaryKeyParams;

	private String primaryKeyParams_AUD;

	private String uniqueParams;

	private String uniqueParams_AUD;

	public static final TableCreator NewInstance(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, boolean includeAudTable, boolean includeCdmBaseAttributes){

	}

		return new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), false, true, false, false, true);

	}

		return new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), includeAudTable, true, false, false, false);

		return result;

	protected TableCreator(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, List<Object> defaultValues, List<Boolean> isNotNull, List<String> referencedTables,

		this.columnTypes = columnTypes;

		this.defaultValues = defaultValues;

		this.isNotNull = isNotNull;

		this.referencedTables = referencedTables;

		this.includeCdmBaseAttributes = includeCdmBaseAttributes;

	public List<ISchemaUpdaterStep> getInnerSteps() {

		return mnTablesStepList;

	}

	/**

	 * Fills the {@link #columnAdders} list.

	 */

		if (columnNames.size() != columnTypes.size()){

			throw new RuntimeException ("ColumnNames and columnTypes must be of same size. Step: " + getStepName());

		}

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

				boolean isNotNull = this.isNotNull == null ? false : this.isNotNull.get(i);

				if ("integer".equals(columnTypes.get(i)) || "int".equals(columnTypes.get(i))){

					String referencedTable = (this.referencedTables == null) ?  null : this.referencedTables.get(i);

					ColumnAdder adder = ColumnAdder.NewIntegerInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull, referencedTable);

					this.columnAdders.add(adder);

				}else if ("boolean".equals(columnTypes.get(i)) || "bit".equals(columnTypes.get(i))){

					String defaultValue = this.defaultValues == null ? null : this.defaultValues.get(i).toString();

					ColumnAdder adder = ColumnAdder.NewBooleanInstance(getStepName(), this.tableName,  this.columnNames.get(i), includeAudTable, Boolean.valueOf(defaultValue));

					this.columnAdders.add(adder);

				}else if (columnTypes.get(i).startsWith("string")){

					Integer length = Integer.valueOf(columnTypes.get(i).substring("string_".length()));

					ColumnAdder adder = ColumnAdder.NewStringInstance(this.getStepName(), this.tableName, this.columnNames.get(i), length, includeAudTable);

					this.columnAdders.add(adder);

					ColumnAdder adder = ColumnAdder.NewClobInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable);

					this.columnAdders.add(adder);

					ColumnAdder adder = ColumnAdder.NewTinyIntegerInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull);

					this.columnAdders.add(adder);

					ColumnAdder adder = ColumnAdder.NewDateTimeInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull);

					this.columnAdders.add(adder);

					ColumnAdder adder = ColumnAdder.NewDoubleInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull);

					this.columnAdders.add(adder);

					throw new RuntimeException("Column type " + columnTypes.get(i) + " not yet supported");

			throw new RuntimeException(e);

	}

	 * fills the mnTablesStepList

			stepName= "Add @tableName annotations";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, "Annotation", SchemaUpdaterBase.INCLUDE_AUDIT);

			stepName= "Add @tableName marker";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, "Marker", SchemaUpdaterBase.INCLUDE_AUDIT);

			stepName= "Add @tableName credits";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, null, "Credit", null, SchemaUpdaterBase.INCLUDE_AUDIT, SORT_INDEX, false);

			mnTablesStepList.add(tableCreator);

			stepName= "Add @tableName identifiers";

			stepName = stepName.replace("@tableName", tableName);

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, null, "Identifier", null, SchemaUpdaterBase.INCLUDE_AUDIT, SORT_INDEX, false);

			stepName= "Add @tableName extensions";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, "Extension", SchemaUpdaterBase.INCLUDE_AUDIT);

			stepName= "Add @tableName sources";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, null, "OriginalSourceBase", "sources", SchemaUpdaterBase.INCLUDE_AUDIT, false, true);

			//Rights

			stepName= "Add @tableName rights";

			tableCreator = MnTableCreator.NewMnInstance(stepName, tableName, "Rights", SchemaUpdaterBase.INCLUDE_AUDIT);

	}

						+ " created " + ColumnAdder.getDatabaseColumnType(datasource, "datetime") + ", "

				updateQuery += "timeperiod_start varchar(255), timeperiod_end varchar(255), timeperiod_freetext varchar(255), actor_id int, description varchar(255),";

			}

				updateQuery += "lsid_authority varchar(255), lsid_lsid varchar(255), lsid_namespace varchar(255), lsid_object varchar(255), lsid_revision varchar(255), protectedtitlecache bit not null, titleCache varchar(255),";

			}

			String uniqueSql = unique(isAuditing)== null ? "" : "unique(" + unique(isAuditing) + "),";

			updateQuery += primaryKeySql + uniqueSql;

			updateQuery = StringUtils.chomp(updateQuery.trim(), ",") + ")";

			updateQuery += datasource.getDatabaseType().getHibernateDialect().getTableTypeString();

			logger.debug("UPDATE Query: " + updateQuery);

		} catch (Exception e) {

			monitor.warning(e.getMessage(), e);

	 * Returns the sql part for the {@link #columnAdders} columns.

	 * This is done by reusing the same method in the ColumnAdder class and removing all the prefixes like 'ADD COLUMN'

	 */

	private String getColumnsSql(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {

		String result = "";

		for (ColumnAdder adder : this.columnAdders){

			String singleAdderSQL = adder.getUpdateQueryString(tableName, datasource, monitor) + ", ";

			result += split[1];

				String attribute = "updatedby";

				String referencedTable = "UserAccount";

				result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable, caseType);

			}

			String referencedTable = "UserAccount";

		if (isAudit){

			String attribute = "REV";

			String referencedTable = "AuditEvent";

		if (this.includeEventBase){

			String attribute = "actor_id";

			String referencedTable = "AgentBase";

		for (ColumnAdder adder : this.columnAdders){

			if (adder.getReferencedTable() != null){

		}

			String index = "FK@tableName_@attribute";

			index = index.replace("@tableName", tableName);

			index = index.replace("@attribute", attribute);

			if (isRevAttribute(attribute) || attribute.endsWith(idSuffix)){

				idSuffix = "";

			}

//			String updateQuery = "ALTER TABLE @tableName ADD INDEX @index (@attribute), ADD FOREIGN KEY (@attribute) REFERENCES @referencedTable (@id)";

			String updateQuery = "ALTER TABLE @tableName ADD @constraintName FOREIGN KEY (@attribute) REFERENCES @referencedTable (@id)";

			updateQuery = updateQuery.replace("@referencedTable", referencedTable);

				updateQuery = updateQuery.replace("@constraintName", "CONSTRAINT " + index);

			}else{

				updateQuery = updateQuery.replace("@constraintName", "");  //H2 does not support "CONSTRAINT", didn't check for others

			}

				updateQuery = updateQuery.replace("@id", "revisionnumber");

			}else{

				updateQuery = updateQuery.replace("@id", "id");

			}

			logger.debug(updateQuery);

			try {

				datasource.executeUpdate(updateQuery);

			} catch (Exception e) {

				String message = "Problem when creating Foreign Key for " + tableName +"." + attribute +": " + e.getMessage();

				monitor.warning(message);

				logger.warn(message, e);

			return true;

	}

	/**

	 * Determines if the tables and the database support foreign keys. If determination is not possible true is returned as default.

	 * @param datasource

	 * @param monitor

	 * @param tableName

	 * @param referencedTable

	 * @return

	 */

	private static boolean supportsForeignKeys(ICdmDataSource datasource, IProgressMonitor monitor, String tableName, String referencedTable) {

		boolean result = true;

		if (! datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){

			return true;

		}else{

			try {

				String myIsamTables = "";

				String format = "SELECT ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'";

				String sql = String.format(format, datasource.getDatabase(), tableName);

				String engine = (String)datasource.getSingleValue(sql);

				if (engine.equals("MyISAM")){

					result = false;

					myIsamTables = CdmUtils.concat(",", myIsamTables, tableName);

				}

				sql = String.format(format,  datasource.getDatabase(), referencedTable);

				engine = (String)datasource.getSingleValue(sql);

				if (engine.equals("MyISAM")){

					result = false;

					myIsamTables = CdmUtils.concat(",", myIsamTables, referencedTable);

				}

				if (result == false){

					String message = "Tables (%s) use MyISAM engine. MyISAM does not support foreign keys.";

					message = String.format(message, myIsamTables);

					monitor.warning(message);

				}

				return result;

			} catch (Exception e) {

				String message = "Problems to determine table engine for MySQL.";

				monitor.warning(message);

	private static boolean isRevAttribute(String attribute) {

		return "REV".equalsIgnoreCase(attribute);

	 * Constructs the primary key creation string

	 * @param isAudit

	 * @return

	 */

		String result = null;

		if (includeCdmBaseAttributes || ! includeCdmBaseAttributes){ //TODO how to handle not CDMBase includes

			if (! isAudit){

				result = "id";

			}else{

				result = "id, REV";

			}

		}

		return result;

	}

	 * Constructs the unique key creation string

	 * @param isAudit

	 * @return

	 */

		if (! isAudit){

			if (this.uniqueParams != null){

				return this.uniqueParams;

			}

			if (includeCdmBaseAttributes){

				return "uuid"; //TODO how to handle not CDMBase includes

			}

			return null;

		}else{

			if (this.uniqueParams_AUD != null){

				return this.uniqueParams_AUD;

			}

			return null;

		}

	}

	public void setPrimaryKeyParams(String primaryKeyParams, String primaryKeyParams_AUD) {

		this.primaryKeyParams = primaryKeyParams;

		this.primaryKeyParams_AUD = primaryKeyParams_AUD;

	}

	public void setUniqueParams(String uniqueParams, String uniqueParams_AUD) {

		this.uniqueParams = uniqueParams;

		this.uniqueParams_AUD = uniqueParams_AUD;

	}