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