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.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 SchemaUpdaterStepBase implements ISchemaUpdaterStep {
  26         private static final Logger logger = Logger.getLogger(UniqueIndexDropper.class);
  27
  28         private String tableName;
  29         private String indexColumn;
  30         private boolean includeAudTable;
  31
  32         public static final UniqueIndexDropper NewInstance(String tableName, String indexColumn, boolean includeAudTable){
  33                 String stepName = "Drop index " + tableName + "-" + indexColumn;
  34                 return new UniqueIndexDropper(stepName, tableName, indexColumn, includeAudTable);
  35         }
  36
  37
  38         protected UniqueIndexDropper(String stepName, String tableName, String indexColumn, boolean includeAudTable) {
  39                 super(stepName);
  40                 this.tableName = tableName;
  41                 this.indexColumn = indexColumn;
  42                 this.includeAudTable = includeAudTable;
  43         }
  44
  45         /* (non-Javadoc)
  46          * @see eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase#invoke(eu.etaxonomy.cdm.database.ICdmDataSource, eu.etaxonomy.cdm.common.IProgressMonitor)
  47          */
  48         @Override
  49         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
  50                 boolean result = true;
  51                 result &= dropIndex(tableName, datasource, monitor);
  52                 if (includeAudTable){
  53                         String aud = "_AUD";
  54                         result &= dropIndex(tableName + aud, datasource, monitor);
  55                 }
  56                 return (result == true )? 0 : null;
  57         }
  58
  59         private boolean dropIndex(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) {
  60                 try {
  61                         if (checkExists(datasource)){
  62                                 String updateQuery = getUpdateQueryString(tableName, datasource, monitor);
  63                                 datasource.executeUpdate(updateQuery);
  64                         }
  65                         return true;
  66                 } catch ( DatabaseTypeNotSupportedException e) {
  67                         e.printStackTrace();
  68                         return false;
  69                 } catch ( SQLException e) {
  70                         e.printStackTrace();
  71                         return false;
  72                 }
  73         }
  74
  75         private boolean checkExists(ICdmDataSource datasource) throws SQLException, DatabaseTypeNotSupportedException {
  76                 DatabaseTypeEnum type = datasource.getDatabaseType();
  77                 if (type.equals(DatabaseTypeEnum.MySQL)){
  78                         String sql = "SELECT count(*)   FROM information_schema.TABLE_CONSTRAINTS " +
  79                         " WHERE table_name ='@tableName' AND CONSTRAINT_SCHEMA = '@dbName' AND CONSTRAINT_TYPE = 'UNIQUE' ";
  80                         sql = sql.replace("@tableName", tableName);
  81                         sql = sql.replace("@columnName", indexColumn);
  82                         sql = sql.replace("@dbName", datasource.getDatabase());
  83                         long count = (Long)datasource.getSingleValue(sql);
  84                         return count > 0;
  85                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
  86                         logger.warn("checkExists not yet implemented for PostGreSQL" );
  87                         return true;
  88                 }else if (type.equals(DatabaseTypeEnum.H2)){
  89                         String indexName = getIndexName(datasource);
  90                         return indexName != null;
  91                 }else{
  92                         // not needed
  93                         return true;
  94                 }
  95         }
  96
  97
  98         public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException, SQLException {
  99                 String updateQuery;
 100                 DatabaseTypeEnum type = datasource.getDatabaseType();
 101                 String indexName = getIndexName(datasource);
 102
 103 //              if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 104                         //MySQL allows both syntaxes
 105 //                      updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";
 106 //              }else
 107                         if (type.equals(DatabaseTypeEnum.H2)){
 108                         updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName";
 109                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 110 //                      updateQuery = "DROP INDEX IF EXISTS @indexName";  // does not work because index is used in the constraint
 111 //                      updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName"; //"if exists" does not work (version 8.4)
 112                         updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT @indexName";
 113                 }else if (type.equals(DatabaseTypeEnum.MySQL)){
 114                         updateQuery = "ALTER TABLE @tableName DROP INDEX @indexName";
 115                 }else{
 116                         updateQuery = null;
 117                         String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();
 118                         monitor.warning(warning);
 119                         throw new DatabaseTypeNotSupportedException(warning);
 120                 }
 121                 updateQuery = updateQuery.replace("@tableName", tableName);
 122                 updateQuery = updateQuery.replace("@indexName", indexName);
 123
 124                 return updateQuery;
 125         }
 126
 127
 128         private String getIndexName(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException, SQLException {
 129                 String result = this.indexColumn;
 130                 DatabaseTypeEnum type = datasource.getDatabaseType();
 131                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 132                         throw new DatabaseTypeNotSupportedException(type.toString());
 133                 }else if (type.equals(DatabaseTypeEnum.MySQL)){
 134                         result = this.indexColumn;
 135                 }else if (type.equals(DatabaseTypeEnum.H2) ){
 136 //                      String sql = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @tableName AND INDEX_TYPE_NAME = 'UNIQUE INDEX'";
 137                         String sql = "SELECT CONSTRAINT_NAME " +
 138                                         " FROM INFORMATION_SCHEMA.CONSTRAINTS "+
 139                                         " WHERE CONSTRAINT_CATALOG = '@dbName' AND "+
 140                                         " TABLE_NAME = '@tableName' AND CONSTRAINT_TYPE = 'UNIQUE' AND "+
 141                                         " COLUMN_LIST = '@columnName'";
 142                         sql = sql.replace("@tableName", tableName.toUpperCase());
 143                         sql = sql.replace("@columnName", indexColumn.toUpperCase());
 144                         sql = sql.replace("@dbName", datasource.getDatabase().toUpperCase());
 145                         String constraintName = (String)datasource.getSingleValue(sql);
 146                         result = constraintName;
 147                 }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 148                         result = this.tableName + "_" + this.indexColumn + "_key";
 149                 }else{
 150                         throw new DatabaseTypeNotSupportedException(type.toString());
 151                 }
 152                 return result;
 153
 154         }
 155
 156
 157 }