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