--- /dev/null
+// $Id$\r
+/**\r
+* Copyright (C) 2009 EDIT\r
+* European Distributed Institute of Taxonomy \r
+* http://www.e-taxonomy.eu\r
+* \r
+* The contents of this file are subject to the Mozilla Public License Version 1.1\r
+* See LICENSE.TXT at the top of this package for the full license terms.\r
+*/\r
+package eu.etaxonomy.cdm.database.update;\r
+\r
+import java.sql.SQLException;\r
+\r
+import org.apache.log4j.Logger;\r
+\r
+import eu.etaxonomy.cdm.common.IProgressMonitor;\r
+import eu.etaxonomy.cdm.database.DatabaseTypeEnum;\r
+import eu.etaxonomy.cdm.database.ICdmDataSource;\r
+\r
+/**\r
+ * @author a.mueller\r
+ * @date 16.09.2010\r
+ *\r
+ */\r
+public class UniqueIndexDropper extends SchemaUpdaterStepBase implements ISchemaUpdaterStep {\r
+ private static final Logger logger = Logger.getLogger(UniqueIndexDropper.class);\r
+ \r
+ private String tableName;\r
+ private String indexColumn;\r
+ private boolean includeAudTable;\r
+ \r
+ public static final UniqueIndexDropper NewInstance(String tableName, String indexColumn, boolean includeAudTable){\r
+ String stepName = "Drop index " + tableName + "-" + indexColumn;\r
+ return new UniqueIndexDropper(stepName, tableName, indexColumn, includeAudTable);\r
+ }\r
+\r
+ \r
+ protected UniqueIndexDropper(String stepName, String tableName, String indexColumn, boolean includeAudTable) {\r
+ super(stepName);\r
+ this.tableName = tableName;\r
+ this.indexColumn = indexColumn;\r
+ this.includeAudTable = includeAudTable;\r
+ }\r
+\r
+ /* (non-Javadoc)\r
+ * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase#invoke(eu.etaxonomy.cdm.database.ICdmDataSource, eu.etaxonomy.cdm.common.IProgressMonitor)\r
+ */\r
+ @Override\r
+ public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {\r
+ boolean result = true;\r
+ result &= dropIndex(tableName, datasource, monitor);\r
+ if (includeAudTable){\r
+ String aud = "_AUD";\r
+ result &= dropIndex(tableName + aud, datasource, monitor);\r
+ }\r
+ return (result == true )? 0 : null;\r
+ }\r
+\r
+ private boolean dropIndex(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) {\r
+ try {\r
+ if (checkExists(datasource)){\r
+ String updateQuery = getUpdateQueryString(tableName, datasource, monitor);\r
+ datasource.executeUpdate(updateQuery);\r
+ }\r
+ return true;\r
+ } catch ( DatabaseTypeNotSupportedException e) {\r
+ e.printStackTrace();\r
+ return false;\r
+ } catch ( SQLException e) {\r
+ e.printStackTrace();\r
+ return false;\r
+ }\r
+ }\r
+\r
+ private boolean checkExists(ICdmDataSource datasource) throws SQLException, DatabaseTypeNotSupportedException {\r
+ DatabaseTypeEnum type = datasource.getDatabaseType();\r
+ if (type.equals(DatabaseTypeEnum.MySQL)){\r
+ String sql = "SELECT count(*) FROM information_schema.TABLE_CONSTRAINTS " + \r
+ " WHERE table_name ='@tableName' AND CONSTRAINT_SCHEMA = '@dbName' AND CONSTRAINT_TYPE = 'UNIQUE' ";\r
+ sql = sql.replace("@tableName", tableName);\r
+ sql = sql.replace("@columnName", indexColumn);\r
+ sql = sql.replace("@dbName", datasource.getDatabase());\r
+ long count = (Long)datasource.getSingleValue(sql);\r
+ return count > 0;\r
+ }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+ logger.warn("checkExists not yet implemented for PostGreSQL" );\r
+ return true;\r
+ }else if (type.equals(DatabaseTypeEnum.H2)){\r
+ String indexName = getIndexName(datasource);\r
+ return indexName != null;\r
+ }else{\r
+ // not needed\r
+ return true;\r
+ }\r
+ }\r
+\r
+\r
+ public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException, SQLException {\r
+ String updateQuery;\r
+ DatabaseTypeEnum type = datasource.getDatabaseType();\r
+ String indexName = getIndexName(datasource);\r
+ \r
+// if (type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+ //MySQL allows both syntaxes\r
+// updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";\r
+// }else\r
+ if (type.equals(DatabaseTypeEnum.H2)){\r
+ updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName";\r
+ }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+// updateQuery = "DROP INDEX IF EXISTS @indexName"; // does not work because index is used in the constraint\r
+// updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName"; //"if exists" does not work (version 8.4) \r
+ updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT @indexName";\r
+ }else if (type.equals(DatabaseTypeEnum.MySQL)){\r
+ updateQuery = "ALTER TABLE @tableName DROP INDEX @indexName";\r
+ }else{\r
+ updateQuery = null;\r
+ String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();\r
+ monitor.warning(warning);\r
+ throw new DatabaseTypeNotSupportedException(warning);\r
+ }\r
+ updateQuery = updateQuery.replace("@tableName", tableName);\r
+ updateQuery = updateQuery.replace("@indexName", indexName);\r
+ \r
+ return updateQuery;\r
+ }\r
+\r
+\r
+ private String getIndexName(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException, SQLException {\r
+ String result = this.indexColumn;\r
+ DatabaseTypeEnum type = datasource.getDatabaseType();\r
+ if (type.equals(DatabaseTypeEnum.SqlServer2005)){\r
+ throw new DatabaseTypeNotSupportedException(type.toString());\r
+ }else if (type.equals(DatabaseTypeEnum.MySQL)){\r
+ result = this.indexColumn;\r
+ }else if (type.equals(DatabaseTypeEnum.H2) ){\r
+// String sql = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @tableName AND INDEX_TYPE_NAME = 'UNIQUE INDEX'"; \r
+ String sql = "SELECT CONSTRAINT_NAME " + \r
+ " FROM INFORMATION_SCHEMA.CONSTRAINTS "+\r
+ " WHERE CONSTRAINT_CATALOG = '@dbName' AND "+\r
+ " TABLE_NAME = '@tableName' AND CONSTRAINT_TYPE = 'UNIQUE' AND "+ \r
+ " COLUMN_LIST = '@columnName'"; \r
+ sql = sql.replace("@tableName", tableName.toUpperCase());\r
+ sql = sql.replace("@columnName", indexColumn.toUpperCase());\r
+ sql = sql.replace("@dbName", datasource.getDatabase().toUpperCase());\r
+ String constraintName = (String)datasource.getSingleValue(sql);\r
+ result = constraintName;\r
+ }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){\r
+ result = this.tableName + "_" + this.indexColumn + "_key";\r
+ }else{\r
+ throw new DatabaseTypeNotSupportedException(type.toString());\r
+ }\r
+ return result;\r
+ \r
+ }\r
+\r
+\r
+}\r
--- /dev/null
+// $Id$
+/**
+* Copyright (C) 2007 EDIT
+* European Distributed Institute of Taxonomy
+* http://www.e-taxonomy.eu
+*
+* The contents of this file are subject to the Mozilla Public License Version 1.1
+* See LICENSE.TXT at the top of this package for the full license terms.
+*/
+
+package eu.etaxonomy.cdm.database.update.v30_31;
+
+import java.util.ArrayList;
+import java.util.List;
+
+import org.apache.log4j.Logger;
+
+import eu.etaxonomy.cdm.database.update.ColumnAdder;
+import eu.etaxonomy.cdm.database.update.ColumnNameChanger;
+import eu.etaxonomy.cdm.database.update.ColumnRemover;
+import eu.etaxonomy.cdm.database.update.ISchemaUpdater;
+import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
+import eu.etaxonomy.cdm.database.update.MapTableCreator;
+import eu.etaxonomy.cdm.database.update.MnTableCreator;
+import eu.etaxonomy.cdm.database.update.SchemaUpdaterBase;
+import eu.etaxonomy.cdm.database.update.TableCreator;
+import eu.etaxonomy.cdm.database.update.TableDroper;
+import eu.etaxonomy.cdm.database.update.TableNameChanger;
+import eu.etaxonomy.cdm.database.update.UniqueIndexDropper;
+import eu.etaxonomy.cdm.database.update.v24_25.SchemaUpdater_24_25;
+import eu.etaxonomy.cdm.database.update.v25_30.SchemaUpdater_25_30;
+
+
+/**
+ * @author a.mueller
+ * @created Nov 08, 2010
+ * @version 1.0
+ */
+public class SchemaUpdater_30_31 extends SchemaUpdaterBase {
+
+
+ @SuppressWarnings("unused")
+ private static final Logger logger = Logger.getLogger(SchemaUpdater_30_31.class);
+ private static final String startSchemaVersion = "3.0.0.0.201011090000";
+ private static final String endSchemaVersion = "3.0.0.1.201101050000";
+
+// ********************** FACTORY METHOD *******************************************
+
+ public static SchemaUpdater_30_31 NewInstance(){
+ return new SchemaUpdater_30_31();
+ }
+
+ /**
+ * @param startSchemaVersion
+ * @param endSchemaVersion
+ */
+ protected SchemaUpdater_30_31() {
+ super(startSchemaVersion, endSchemaVersion);
+ }
+
+ /* (non-Javadoc)
+ * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getUpdaterList()
+ */
+ @Override
+ protected List<ISchemaUpdaterStep> getUpdaterList() {
+
+ List<ISchemaUpdaterStep> stepList = new ArrayList<ISchemaUpdaterStep>();
+ String stepName;
+
+ //drop unique index for DefinedTermBase_media.media_id
+ UniqueIndexDropper step = UniqueIndexDropper.NewInstance("DefinedTermBase_media", "media_id", ! INCLUDE_AUDIT);
+ stepList.add(step);
+
+ return stepList;
+ }
+
+ /* (non-Javadoc)
+ * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getNextUpdater()
+ */
+ @Override
+ public ISchemaUpdater getNextUpdater() {
+ return null;
+ }
+
+ /* (non-Javadoc)
+ * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterBase#getPreviousUpdater()
+ */
+ @Override
+ public ISchemaUpdater getPreviousUpdater() {
+ return SchemaUpdater_25_30.NewInstance();
+ }
+
+}