cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/UniqueIndexDropper.java

   1 // $Id$
   2 /**
   3 * Copyright (C) 2009 EDIT
   4 * European Distributed Institute of Taxonomy
   5 * http://www.e-taxonomy.eu
   6 *
   7 * The contents of this file are subject to the Mozilla Public License Version 1.1
   8 * See LICENSE.TXT at the top of this package for the full license terms.
   9 */
  10 package eu.etaxonomy.cdm.database.update;
  11
  12 import java.sql.SQLException;
  13
  14 import org.apache.log4j.Logger;
  15
  16 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  17 import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
  18 import eu.etaxonomy.cdm.database.ICdmDataSource;
  19
  20 /**
  21  * @author a.mueller
  22  * @date 16.09.2010
  23  *
  24  */
  25 public class UniqueIndexDropper extends AuditedSchemaUpdaterStepBase<UniqueIndexDropper> implements ISchemaUpdaterStep {
  26         private static final Logger logger = Logger.getLogger(UniqueIndexDropper.class);
  27
  28         private String indexColumn;
  29
  30         public static final UniqueIndexDropper NewInstance(String tableName, String indexColumn, boolean includeAudTable){
  31                 String stepName = "Drop index " + tableName + "-" + indexColumn;
  32                 return new UniqueIndexDropper(stepName, tableName, indexColumn, includeAudTable);
  33         }
  34
  35
  36         protected UniqueIndexDropper(String stepName, String tableName, String indexColumn, boolean includeAudTable) {
  37                 super(stepName);
  38                 this.tableName = tableName;
  39                 this.indexColumn = indexColumn;
  40                 this.includeAudTable = includeAudTable;
  41         }
  42
  43         @Override
  44         protected boolean invokeOnTable(String tableName, ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) {
  45                 try {
  46                         if (checkExists(datasource)){
  47                                 String updateQuery = getUpdateQueryString(tableName, datasource, monitor);
  48                                 datasource.executeUpdate(updateQuery);
  49                         }
  50                         return true;
  51                 } catch ( Exception e) {
  52                         monitor.warning(e.getMessage(), e);
  53                         return false;
  54                 }
  55         }
  56
  57         private boolean checkExists(ICdmDataSource datasource) throws SQLException, DatabaseTypeNotSupportedException {
  58                 DatabaseTypeEnum type = datasource.getDatabaseType();
  59                 if (type.equals(DatabaseTypeEnum.MySQL)){
  60                         String sql = "SELECT count(*)   FROM information_schema.TABLE_CONSTRAINTS " +
  61                                         " WHERE table_name ='@tableName' AND CONSTRAINT_SCHEMA = '@dbName' AND CONSTRAINT_TYPE = 'UNIQUE' ";
  62                         sql = sql.replace("@tableName", tableName);
  63                         sql = sql.replace("@columnName", indexColumn);
  64                         sql = sql.replace("@dbName", datasource.getDatabase());
  65                         long count = (Long)datasource.getSingleValue(sql);
  66                         return count > 0;
  67                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
  68                         logger.warn("checkExists not yet implemented for PostGreSQL" );
  69                         return true;
  70                 }else if (type.equals(DatabaseTypeEnum.H2)){
  71                         String indexName = getIndexName(datasource);
  72                         return indexName != null;
  73                 }else{
  74                         // not needed
  75                         return true;
  76                 }
  77         }
  78
  79
  80         public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException, SQLException {
  81                 //NOTE: no caseType required here
  82                 String updateQuery;
  83                 DatabaseTypeEnum type = datasource.getDatabaseType();
  84                 String indexName = getIndexName(datasource);
  85
  86 //              if (type.equals(DatabaseTypeEnum.SqlServer2005)){
  87                         //MySQL allows both syntaxes
  88 //                      updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";
  89 //              }else
  90                         if (type.equals(DatabaseTypeEnum.H2)){
  91                         updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName";
  92                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
  93 //                      updateQuery = "DROP INDEX IF EXISTS @indexName";  // does not work because index is used in the constraint
  94 //                      updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName"; //"if exists" does not work (version 8.4)
  95                         updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT @indexName";
  96                 }else if (type.equals(DatabaseTypeEnum.MySQL)){
  97                         updateQuery = "ALTER TABLE @tableName DROP INDEX @indexName";
  98                 }else{
  99                         updateQuery = null;
 100                         String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();
 101                         monitor.warning(warning);
 102                         throw new DatabaseTypeNotSupportedException(warning);
 103                 }
 104                 updateQuery = updateQuery.replace("@tableName", tableName);
 105                 updateQuery = updateQuery.replace("@indexName", indexName);
 106
 107                 return updateQuery;
 108         }
 109
 110
 111         private String getIndexName(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException, SQLException {
 112                 String result = this.indexColumn;
 113                 DatabaseTypeEnum type = datasource.getDatabaseType();
 114                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 115                         throw new DatabaseTypeNotSupportedException(type.toString());
 116                 }else if (type.equals(DatabaseTypeEnum.MySQL)){
 117                         result = this.indexColumn;
 118                 }else if (type.equals(DatabaseTypeEnum.H2) ){
 119 //                      String sql = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @tableName AND INDEX_TYPE_NAME = 'UNIQUE INDEX'";
 120                         String sql = "SELECT CONSTRAINT_NAME " +
 121                                         " FROM INFORMATION_SCHEMA.CONSTRAINTS "+
 122                                         " WHERE CONSTRAINT_CATALOG = '@dbName' AND "+
 123                                         " TABLE_NAME = '@tableName' AND CONSTRAINT_TYPE = 'UNIQUE' AND "+
 124                                         " COLUMN_LIST = '@columnName'";
 125                         sql = sql.replace("@tableName", tableName.toUpperCase());
 126                         sql = sql.replace("@columnName", indexColumn.toUpperCase());
 127                         sql = sql.replace("@dbName", datasource.getDatabase().toUpperCase());
 128                         String constraintName = (String)datasource.getSingleValue(sql);
 129                         result = constraintName;
 130                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 131                         //TODO do we need this cased?
 132                         result = this.tableName + "_" + this.indexColumn + "_key";
 133                 }else{
 134                         throw new DatabaseTypeNotSupportedException(type.toString());
 135                 }
 136                 return result;
 137
 138         }
 139
 140
 141 }