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