cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v33_34/UsernameConstraintUpdater.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.v33_34;
  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 import eu.etaxonomy.cdm.database.update.CaseType;
  20 import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep;
  21 import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase;
  22
  23 /**
  24  * @author a.mueller
  25  * @date 16.09.2010
  26  *
  27  */
  28 public class UsernameConstraintUpdater extends SchemaUpdaterStepBase<UsernameConstraintUpdater> implements ISchemaUpdaterStep {
  29         private static final Logger logger = Logger.getLogger(UsernameConstraintUpdater.class);
  30
  31         public static final UsernameConstraintUpdater NewInstance(String stepName){
  32                 return new UsernameConstraintUpdater(stepName);
  33         }
  34
  35
  36         protected UsernameConstraintUpdater(String stepName) {
  37                 super(stepName);
  38         }
  39
  40
  41         @Override
  42         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {
  43                 //remove 2-fold constraint
  44                 removeExistingConstraint(datasource, caseType);
  45                 createUsernameConstraint(datasource, caseType);
  46                 createUuidConstraint(datasource, caseType);
  47                 return null;
  48         }
  49
  50         private void createUuidConstraint(ICdmDataSource datasource,
  51                         CaseType caseType) {
  52                 try {
  53                         String updateQuery = getCreateQuery(datasource, caseType, "@@UserAccount@@", "username_", "username");
  54                         datasource.executeUpdate(updateQuery);
  55                 } catch (SQLException e) {
  56                         logger.warn("Unique index for UserAccount.uuid could not be created");
  57                 }
  58         }
  59
  60         private void createUsernameConstraint(ICdmDataSource datasource,
  61                         CaseType caseType) {
  62                 try {
  63                         String updateQuery = getCreateQuery(datasource, caseType, "@@UserAccount@@", "username_", "username");
  64                         datasource.executeUpdate(updateQuery);
  65                 } catch (SQLException e) {
  66                         logger.warn("Unique index for username could not be created");
  67                 }
  68         }
  69
  70         private String getCreateQuery(ICdmDataSource datasource, CaseType caseType, String tableName, String constraintName, String columnName) {
  71                         DatabaseTypeEnum type = datasource.getDatabaseType();
  72                         String indexName = "_UniqueKey";
  73                         String updateQuery;
  74                         if (type.equals(DatabaseTypeEnum.MySQL)){
  75                                 updateQuery = "ALTER TABLE @@"+ tableName + "@@ ADD UNIQUE INDEX " + constraintName + " ("+columnName+");";
  76                         }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.SqlServer2005)){
  77                                 updateQuery = "CREATE UNIQUE INDEX " + constraintName + " ON "+tableName+"(" + columnName + ")";
  78                         }else{
  79                                 throw new IllegalArgumentException("Datasource type not supported: " + type.getName());
  80                         }
  81                         updateQuery = updateQuery.replace("@indexName", indexName);
  82                         caseType.replaceTableNames("@@UserAccount@@");
  83                         return updateQuery;
  84         }
  85
  86
  87         private void removeExistingConstraint(ICdmDataSource datasource, CaseType caseType) {
  88                 try {
  89                         DatabaseTypeEnum type = datasource.getDatabaseType();
  90                         String indexName = "_UniqueKey";
  91                         String updateQuery;
  92                         if (type.equals(DatabaseTypeEnum.MySQL)){
  93                                 updateQuery = "ALTER TABLE @@UserAccount@@ DROP INDEX @indexName";
  94                         }else if (type.equals(DatabaseTypeEnum.H2)){
  95                                 updateQuery = "ALTER TABLE @@UserAccount@@ DROP CONSTRAINT IF EXISTS @indexName";
  96                         }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
  97                                 updateQuery = "ALTER TABLE @@UserAccount@@ DROP CONSTRAINT @indexName";
  98                         }else if (type.equals(DatabaseTypeEnum.SqlServer2005)){
  99                                 //TODO
 100                                 throw new RuntimeException("Remove index not yet supported for SQLServer");
 101                         }else{
 102                                 throw new IllegalArgumentException("Datasource type not supported: " + type.getName());
 103                         }
 104                         updateQuery = updateQuery.replace("@indexName", indexName);
 105                         updateQuery = caseType.replaceTableNames("@@UserAccount@@");
 106                         datasource.executeUpdate(updateQuery);
 107                 } catch (SQLException e) {
 108                         logger.warn("Old index could not be removed");
 109                 }
 110         }
 111
 112 //      private boolean checkExists(ICdmDataSource datasource) throws SQLException, DatabaseTypeNotSupportedException {
 113 //              DatabaseTypeEnum type = datasource.getDatabaseType();
 114 //              if (type.equals(DatabaseTypeEnum.MySQL)){
 115 //                      String sql = "SELECT count(*)   FROM information_schema.TABLE_CONSTRAINTS " +
 116 //                                      " WHERE table_name ='@tableName' AND CONSTRAINT_SCHEMA = '@dbName' AND CONSTRAINT_TYPE = 'UNIQUE' ";
 117 //                      sql = sql.replace("@tableName", tableName);
 118 //                      sql = sql.replace("@columnName", indexColumn);
 119 //                      sql = sql.replace("@dbName", datasource.getDatabase());
 120 //                      long count = (Long)datasource.getSingleValue(sql);
 121 //                      return count > 0;
 122 //              }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 123 //                      logger.warn("checkExists not yet implemented for PostGreSQL" );
 124 //                      return true;
 125 //              }else if (type.equals(DatabaseTypeEnum.H2)){
 126 //                      String indexName = getIndexName(datasource);
 127 //                      return indexName != null;
 128 //              }else{
 129 //                      // not needed
 130 //                      return true;
 131 //              }
 132 //      }
 133
 134
 135 //      public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException, SQLException {
 136 //              //NOTE: no caseType required here
 137 //              String updateQuery;
 138 //              DatabaseTypeEnum type = datasource.getDatabaseType();
 139 //              String indexName = getIndexName(datasource);
 140 //
 141 ////            if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 142 //                      //MySQL allows both syntaxes
 143 ////                    updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";
 144 ////            }else
 145 //                      if (type.equals(DatabaseTypeEnum.H2)){
 146 //                      updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName";
 147 //              }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 148 ////                    updateQuery = "DROP INDEX IF EXISTS @indexName";  // does not work because index is used in the constraint
 149 ////                    updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT IF EXISTS @indexName"; //"if exists" does not work (version 8.4)
 150 //                      updateQuery = "ALTER TABLE @tableName DROP CONSTRAINT @indexName";
 151 //              }else if (type.equals(DatabaseTypeEnum.MySQL)){
 152 //                      updateQuery = "ALTER TABLE @tableName DROP INDEX @indexName";
 153 //              }else{
 154 //                      updateQuery = null;
 155 //                      String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();
 156 //                      monitor.warning(warning);
 157 //                      throw new DatabaseTypeNotSupportedException(warning);
 158 //              }
 159 //              updateQuery = updateQuery.replace("@tableName", tableName);
 160 //              updateQuery = updateQuery.replace("@indexName", indexName);
 161 //
 162 //              return updateQuery;
 163 //      }
 164 //
 165 //
 166 //      private String getIndexName(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException, SQLException {
 167 //              String result = this.indexColumn;
 168 //              DatabaseTypeEnum type = datasource.getDatabaseType();
 169 //              if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 170 //                      throw new DatabaseTypeNotSupportedException(type.toString());
 171 //              }else if (type.equals(DatabaseTypeEnum.MySQL)){
 172 //                      result = this.indexColumn;
 173 //              }else if (type.equals(DatabaseTypeEnum.H2) ){
 174 ////                    String sql = "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.INDEXES WHERE TABLE_NAME = @tableName AND INDEX_TYPE_NAME = 'UNIQUE INDEX'";
 175 //                      String sql = "SELECT CONSTRAINT_NAME " +
 176 //                                      " FROM INFORMATION_SCHEMA.CONSTRAINTS "+
 177 //                                      " WHERE CONSTRAINT_CATALOG = '@dbName' AND "+
 178 //                                      " TABLE_NAME = '@tableName' AND CONSTRAINT_TYPE = 'UNIQUE' AND "+
 179 //                                      " COLUMN_LIST = '@columnName'";
 180 //                      sql = sql.replace("@tableName", tableName.toUpperCase());
 181 //                      sql = sql.replace("@columnName", indexColumn.toUpperCase());
 182 //                      sql = sql.replace("@dbName", datasource.getDatabase().toUpperCase());
 183 //                      String constraintName = (String)datasource.getSingleValue(sql);
 184 //                      result = constraintName;
 185 //              }else if (type.equals(DatabaseTypeEnum.PostgreSQL)){
 186 //                      //TODO do we need this cased?
 187 //                      result = this.tableName + "_" + this.indexColumn + "_key";
 188 //              }else{
 189 //                      throw new DatabaseTypeNotSupportedException(type.toString());
 190 //              }
 191 //              return result;
 192 //
 193 //      }
 194
 195
 196
 197
 198 }