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         public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable){
  59                 return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar(255)", includeAudTable, null, false, null);
  60         }
  61
  62         public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, int length, boolean includeAudTable){
  63                 return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar("+length+")", includeAudTable, null, false, null);
  64         }
  65     public static final ColumnAdder NewStringInstance(String stepName, String tableName, String newColumnName, int length, String defaultValue, boolean includeAudTable){
  66         return new ColumnAdder(stepName, tableName, newColumnName, "nvarchar("+length+")", includeAudTable, defaultValue, false, null);
  67     }
  68
  69         public static final ColumnAdder NewClobInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable){
  70                 return new ColumnAdder(stepName, tableName, newColumnName, "clob", includeAudTable, null, false, null);
  71         }
  72
  73         public static final ColumnAdder NewDateTimeInstance(String stepName, String tableName, String newColumnName, boolean includeAudTable, boolean notNull){
  74                 return new ColumnAdder(stepName, tableName, newColumnName, "datetime", includeAudTable, null, notNull, null);
  75         }
  76
  77         protected ColumnAdder(String stepName, String tableName, String newColumnName, String columnType, boolean includeAudTable, Object defaultValue, boolean notNull, String referencedTable) {
  78                 super(stepName, tableName, includeAudTable);
  79                 this.newColumnName = newColumnName;
  80                 this.columnType = columnType;
  81                 this.defaultValue = defaultValue;
  82                 this.isNotNull = notNull;
  83                 this.referencedTable = referencedTable;
  84         }
  85
  86         public ColumnAdder setNotNull(boolean isNotNull) {
  87                 this.isNotNull = isNotNull;
  88                 return this;
  89         }
  90
  91     @Override
  92     protected void invokeOnTable(String tableName, ICdmDataSource datasource,
  93             IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) {
  94
  95         try {
  96                         String updateQuery = getUpdateQueryString(tableName, datasource, monitor);
  97                         datasource.executeUpdate(updateQuery);
  98
  99                         if (defaultValue instanceof Boolean){
 100                                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : getBoolean((Boolean) defaultValue, datasource));
 101                                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 102                                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 103                                 datasource.executeUpdate(defaultValueQuery);
 104                         }else if (defaultValue instanceof Integer){
 105                                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : defaultValue);
 106                                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 107                                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 108                                 datasource.executeUpdate(defaultValueQuery);
 109             }else if (defaultValue instanceof String){
 110                 String defaultValueQuery = "UPDATE @tableName SET @columnName = " + (defaultValue == null ? "NULL" : "'" + defaultValue + "'");
 111                 defaultValueQuery = defaultValueQuery.replace("@tableName", tableName);
 112                 defaultValueQuery = defaultValueQuery.replace("@columnName", newColumnName);
 113                 datasource.executeUpdate(defaultValueQuery);
 114                         }else if (defaultValue != null){
 115                                 logger.warn("Default Value not implemented for type " + defaultValue.getClass().getName());
 116                         }
 117                         if (referencedTable != null){
 118                                 TableCreator.makeForeignKey(tableName, datasource, monitor, newColumnName, referencedTable, caseType, result);
 119                         }
 120                         return;
 121                 } catch ( Exception e) {
 122                     String message = "Unhandled exception when trying to add column " +
 123                             newColumnName + " for table " +  tableName;
 124                         monitor.warning(message, e);
 125                         logger.error(e);
 126                         result.addException(e, message, getStepName());
 127                         return;
 128                 }
 129         }
 130
 131         /**
 132          * Returns the update query string. tableName must already be cased correctly. See {@link CaseType}.
 133          * @param tableName correctly cased table name
 134          * @param datasource data source
 135          * @param monitor monitor
 136          * @return the query string
 137          * @throws DatabaseTypeNotSupportedException
 138          */
 139         public String getUpdateQueryString(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {
 140                 String updateQuery;
 141                 DatabaseTypeEnum type = datasource.getDatabaseType();
 142                 String databaseColumnType = getDatabaseColumnType(datasource, this.columnType);
 143
 144                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 145                         //MySQL allows both syntaxes
 146                         updateQuery = "ALTER TABLE @tableName ADD @columnName @columnType";
 147                 }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.MySQL)){
 148                         updateQuery = "ALTER TABLE @tableName @addSeparator @columnName @columnType";
 149                 }else{
 150                         updateQuery = null;
 151                         String warning = "Update step '" + this.getStepName() + "' is not supported by " + type.getName();
 152                         monitor.warning(warning);
 153                         throw new DatabaseTypeNotSupportedException(warning);
 154                 }
 155                 if (isNotNull){
 156                         updateQuery += " NOT NULL";
 157                 }
 158                 updateQuery = updateQuery.replace("@tableName", tableName);
 159                 updateQuery = updateQuery.replace("@columnName", newColumnName);
 160                 updateQuery = updateQuery.replace("@columnType", databaseColumnType);
 161                 updateQuery = updateQuery.replace("@addSeparator", getAddColumnSeperator(datasource));
 162
 163                 return updateQuery;
 164         }
 165
 166         protected static String getDatabaseColumnType(ICdmDataSource datasource, String columnType) {
 167                 String result = columnType;
 168                 DatabaseTypeEnum dbType = datasource.getDatabaseType();
 169                 //nvarchar
 170                 if (dbType.equals(DatabaseTypeEnum.PostgreSQL)){  //TODO use PostgeSQL82 Dialect infos
 171                         result = result.replace("nvarchar", "varchar");
 172                         result = result.replace("double", "float8");
 173                         result = result.replace("bit", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.BIT));
 174                         result = result.replace("datetime", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.TIMESTAMP));
 175                         result = result.replace("tinyint", DatabaseTypeEnum.PostgreSQL.getHibernateDialect().getTypeName(Types.TINYINT));
 176                 }
 177                 //CLOB
 178                 if (columnType.equalsIgnoreCase("clob")){
 179                         //TODO use hibernate dialects
 180                         if (dbType.equals(DatabaseTypeEnum.MySQL)){
 181                                 result = "longtext";
 182                         }else if (dbType.equals(DatabaseTypeEnum.H2)){
 183                                 result = "CLOB";  //or NVARCHAR
 184                         }else if (dbType.equals(DatabaseTypeEnum.PostgreSQL)){
 185                                 result = "text";
 186                         }else if (dbType.equals(DatabaseTypeEnum.SqlServer2005)){
 187                                 result = "NVARCHAR(MAX)";
 188                         }
 189                 }
 190                 return result;
 191         }
 192
 193
 194         /**
 195          * Returns the sql keywords for adding a column. This is usually 'ADD' or 'ADD COLUMN'
 196          * @param datasource
 197          * @return
 198          * @throws DatabaseTypeNotSupportedException
 199          */
 200         public static String getAddColumnSeperator(ICdmDataSource datasource) throws DatabaseTypeNotSupportedException {
 201                 DatabaseTypeEnum type = datasource.getDatabaseType();
 202                 if (type.equals(DatabaseTypeEnum.SqlServer2005)){
 203                         return "ADD ";
 204                 }else if (type.equals(DatabaseTypeEnum.H2) || type.equals(DatabaseTypeEnum.PostgreSQL) || type.equals(DatabaseTypeEnum.MySQL)){
 205                         return "ADD COLUMN ";
 206                 }else{
 207                         throw new DatabaseTypeNotSupportedException(datasource.getName());
 208                 }
 209         }
 210
 211         public String getReferencedTable() {
 212                 return referencedTable;
 213         }
 214
 215         public String getNewColumnName() {
 216                 return newColumnName;
 217         }
 218 }