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

   1 /**
   2 * Copyright (C) 2009 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9 package eu.etaxonomy.cdm.database.update;
  10
  11 import java.sql.Types;
  12
  13 import org.apache.log4j.Logger;
  14
  15 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  16 import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
  17 import eu.etaxonomy.cdm.database.ICdmDataSource;
  18
  19 /**
  20  * @author a.mueller
  21  * @since 16.09.2010
  22  *
  23  */
  24 public class ColumnAdder extends AuditedSchemaUpdaterStepBase {
  25         private static final Logger logger = Logger.getLogger(ColumnAdder.class);
  26
  27         private final String newColumnName;
  28         private final String columnType;
  29         private final Object defaultValue;
  30         private boolean isNotNull;
  31         private final String referencedTable;
  32
  33         /**
  34          * Add ForeignKey.
  35          * @param referencedTable
  36          * @return
  37          */
  38         public static final ColumnAdder NewIntegerInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, boolean notNull, String referencedTable){
  39                 return new ColumnAdder(stepName, tableName, newColumnName, "int", includeAudTable, null, notNull, referencedTable);
  40         }
  41
  42         public static final ColumnAdder NewIntegerInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, Integer defaultValue, boolean notNull){
  43                 return new ColumnAdder(stepName, tableName, newColumnName, "int", includeAudTable, defaultValue, notNull, null);
  44         }
  45
  46         public static final ColumnAdder NewTinyIntegerInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, boolean notNull){
  47                 return new ColumnAdder(stepName, tableName, newColumnName, "tinyint", includeAudTable, null, notNull, null);
  48         }
  49
  50         public static final ColumnAdder NewDoubleInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, boolean notNull){
  51                 return new ColumnAdder(stepName, tableName, newColumnName, "double", includeAudTable, null, notNull, null);
  52         }
  53
  54         public static final ColumnAdder NewBooleanInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, Boolean defaultValue){
  55                 return new ColumnAdder(stepName, tableName, newColumnName, "bit", includeAudTable, defaultValue, false, null);
  56         }
  57
  58         /**
  59          * Adds a string column with length 255 and default value <code>null</code>
  60          */
  61         public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable){
  62                 return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar(255)", includeAudTable, null, false, null);
  63         }
  64
  65         public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, int length, boolean includeAudTable){
  66                 return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar("+length+")", includeAudTable, null, false, null);
  67         }
  68     public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, int length, String defaultValue, boolean includeAudTable){
  69         return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar("+length+")", includeAudTable, defaultValue, false, null);
  70     }
  71
  72         public static final ColumnAdder NewClobInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable){
  73                 return new ColumnAdder(stepName, tableName, newColumnName, "clob", includeAudTable, null, false, null);
  74         }
  75
  76         public static final ColumnAdder NewDateTimeInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, boolean notNull){
  77                 return new ColumnAdder(stepName, tableName, newColumnName, "datetime", includeAudTable, null, notNull, null);
  78         }
  79
  80         protected ColumnAdder(String stepName, String tableName, String newColumnName, String columnType, boolean includeAudTable, Object defaultValue, boolean notNull, String referencedTable) {
  81                 super(stepName, tableName, includeAudTable);
  82                 this.newColumnName = newColumnName;
  83                 this.columnType = columnType;
  84                 this.defaultValue = defaultValue;
  85                 this.isNotNull = notNull;
  86                 this.referencedTable = referencedTable;
  87         }
  88
  89         public ColumnAdder setNotNull(boolean isNotNull) {
  90                 this.isNotNull = isNotNull;
  91                 return this;
  92         }
  93
  94     @Override
  95     protected void invokeOnTable(String tableName, ICdmDataSource datasource,
  96             IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) {
  97
  98         try {
  99                         String updateQuery = getUpdateQueryString(tableName, datasource, monitor);
 100                         datasource.executeUpdate(updateQuery);
 101
 102                         if (defaultValue instanceof Boolean){
 103                                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : getBoolean((Boolean) defaultValue, datasource));
 104                                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 105                                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 106                                 datasource.executeUpdate(defaultValueQuery);
 107                         }else if (defaultValue instanceof Integer){
 108                                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : defaultValue);
 109                                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 110                                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 111                                 datasource.executeUpdate(defaultValueQuery);
 112             }else if (defaultValue instanceof String){
 113                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : "'" + defaultValue + "'");
 114                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 115                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 116                 datasource.executeUpdate(defaultValueQuery);
 117                         }else if (defaultValue != null){
 118                                 logger.warn("Default Value not implemented for type " + defaultValue.getClass().getName());
 119                         }
 120                         if (referencedTable != null){
 121                                 TableCreator.makeForeignKey(tableName, datasource, monitor, newColumnName, referencedTable, caseType, result);
 122                         }
 123                         return;
 124                 } catch ( Exception e) {
 125                     String message = "Unhandled exception when trying to add column " +
 126                             newColumnName + " for table " +  tableName;
 127                         monitor.warning(message, e);
 128                         logger.error(e);
 129                         result.addException(e, message, getStepName());
 130                         return;
 131                 }
 132         }
 133
 134         /**
 135          * Returns the update query string. tableName must already be cased correctly. See {@link CaseType}.
 136          * @param tableName correctly cased table name
 137          * @param datasource data source
 138          * @param monitor monitor
 139          * @return the query string
 140          * @throws DatabaseTypeNotSupportedException
 141          */
 142         public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {
 143                 String updateQuery;
 144                 DatabaseTypeEnum type = datasource.getDatabaseType();
 145                 String databaseColumnType = getDatabaseColumnType(datasource, this.columnType);
 146
 147                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 148                         //MySQL allows both syntaxes
 149                         updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";
 150                 }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.MySQL)){
 151                         updateQuery = "ALTER TABLE @tableName @addSeparator @columnName @columnType";
 152                 }else{
 153                         updateQuery = null;
 154                         String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();
 155                         monitor.warning(warning);
 156                         throw new DatabaseTypeNotSupportedException(warning);
 157                 }
 158                 if (isNotNull){
 159                         updateQuery += " NOT NULL";
 160                 }
 161                 updateQuery = updateQuery.replace("@tableName", tableName);
 162                 updateQuery = updateQuery.replace("@columnName", newColumnName);
 163                 updateQuery = updateQuery.replace("@columnType", databaseColumnType);
 164                 updateQuery = updateQuery.replace("@addSeparator", getAddColumnSeperator(datasource));
 165
 166                 return updateQuery;
 167         }
 168
 169         protected static String getDatabaseColumnType(ICdmDataSource datasource, String columnType) {
 170                 String result = columnType;
 171                 DatabaseTypeEnum dbType = datasource.getDatabaseType();
 172                 //nvarchar
 173                 if (dbType.equals(DatabaseTypeEnum.PostgreSQL)){  //TODO use PostgeSQL82 Dialect infos
 174                         result = result.replace("nvarchar", "varchar");
 175                         result = result.replace("double", "float8");
 176                         result = result.replace("bit", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.BIT));
 177                         result = result.replace("datetime", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.TIMESTAMP));
 178                         result = result.replace("tinyint", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.TINYINT));
 179                 }
 180                 //CLOB
 181                 if (columnType.equalsIgnoreCase("clob")){
 182                         //TODO use hibernate dialects
 183                         if (dbType.equals(DatabaseTypeEnum.MySQL)){
 184                                 result = "longtext";
 185                         }else if (dbType.equals(DatabaseTypeEnum.H2)){
 186                                 result = "CLOB";  //or NVARCHAR
 187                         }else if (dbType.equals(DatabaseTypeEnum.PostgreSQL)){
 188                                 result = "text";
 189                         }else if (dbType.equals(DatabaseTypeEnum.SqlServer2005)){
 190                                 result = "NVARCHAR(MAX)";
 191                         }
 192                 }
 193                 return result;
 194         }
 195
 196
 197         /**
 198          * Returns the sql keywords for adding a column. This is usually 'ADD' or 'ADD COLUMN'
 199          * @param datasource
 200          * @return
 201          * @throws DatabaseTypeNotSupportedException
 202          */
 203         public static String getAddColumnSeperator(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException {
 204                 DatabaseTypeEnum type = datasource.getDatabaseType();
 205                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 206                         return "ADD ";
 207                 }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.MySQL)){
 208                         return "ADD COLUMN ";
 209                 }else{
 210                         throw new DatabaseTypeNotSupportedException(datasource.getName());
 211                 }
 212         }
 213
 214         public String getReferencedTable() {
 215                 return referencedTable;
 216         }
 217
 218         public String getNewColumnName() {
 219                 return newColumnName;
 220         }
 221 }