cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/TableCreator.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 import java.util.ArrayList;
  14 import java.util.Arrays;
  15 import java.util.List;
  16
  17 import org.apache.commons.lang.StringUtils;
  18 import org.apache.log4j.Logger;
  19
  20 import eu.etaxonomy.cdm.common.CdmUtils;
  21 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  22 import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
  23 import eu.etaxonomy.cdm.database.ICdmDataSource;
  24 import eu.etaxonomy.cdm.model.agent.AgentBase;
  25
  26 /**
  27  * @author a.mueller
  28  * @date 16.09.2010
  29  *
  30  */
  31 public class TableCreator extends AuditedSchemaUpdaterStepBase<TableCreator> implements ISchemaUpdaterStep {
  32         private static final Logger logger = Logger.getLogger(TableCreator.class);
  33
  34         private static final boolean SORT_INDEX = true;
  35
  36         private List<String> columnNames;
  37         private List<String> columnTypes;
  38         private List<Object> defaultValues;
  39         private List<Boolean> isNotNull;
  40         private List<String> referencedTables;
  41         private boolean includeCdmBaseAttributes;
  42         private boolean includeIdentifiableEntity;
  43         private boolean includeAnnotatableEntity;
  44         private boolean includeEventBase;
  45         protected List<ColumnAdder> columnAdders = new ArrayList<ColumnAdder>();
  46         protected List<ISchemaUpdaterStep> mnTablesStepList = new ArrayList<ISchemaUpdaterStep>();
  47         private String primaryKeyParams;
  48         private String primaryKeyParams_AUD;
  49         private String uniqueParams;
  50         private String uniqueParams_AUD;
  51
  52
  53 //      public static final TableCreator NewInstance(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, List<Object> defaultValues, List<Boolean> isNull, boolean includeAudTable){
  54         public static final TableCreator NewInstance(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, boolean includeAudTable, boolean includeCdmBaseAttributes){
  55                 return new TableCreator(stepName, tableName, columnNames, columnTypes, null, null, null, includeAudTable, includeCdmBaseAttributes, false, false);
  56         }
  57
  58         public static final TableCreator NewInstance(String stepName, String tableName, String[] columnNames, String[] columnTypes, String[] referencedTables, boolean includeAudTable, boolean includeCdmBaseAttributes){
  59                 return new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), includeAudTable, includeCdmBaseAttributes, false, false);
  60         }
  61
  62         public static final TableCreator NewAnnotatableInstance(String stepName, String tableName, String[] columnNames, String[] columnTypes, String[] referencedTables, boolean includeAudTable){
  63                 return new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), includeAudTable, true, true, false);
  64         }
  65
  66         public static final TableCreator NewEventInstance(String stepName, String tableName, String[] columnNames, String[] columnTypes, String[] referencedTables, boolean includeAudTable){
  67                 TableCreator result = new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), includeAudTable, true, true, false);
  68                 result.includeEventBase = true;
  69                 return result;
  70         }
  71
  72         public static final TableCreator NewIdentifiableInstance(String stepName, String tableName, String[] columnNames, String[] columnTypes, String[] referencedTables, boolean includeAudTable){
  73                 return new TableCreator(stepName, tableName, Arrays.asList(columnNames), Arrays.asList(columnTypes), null, null, Arrays.asList(referencedTables), includeAudTable, true, true, true);
  74         }
  75
  76         protected TableCreator(String stepName, String tableName, List<String> columnNames, List<String> columnTypes, List<Object> defaultValues, List<Boolean> isNotNull, List<String> referencedTables,
  77                         boolean includeAudTable, boolean includeCdmBaseAttributes, boolean includeAnnotatableEntity, boolean includeIdentifiableEntity) {
  78                 super(stepName);
  79                 this.tableName = tableName;
  80                 this.columnNames = columnNames;
  81                 this.columnTypes = columnTypes;
  82                 this.defaultValues = defaultValues;
  83                 this.isNotNull = isNotNull;
  84                 this.referencedTables = referencedTables;
  85                 this.includeAudTable = includeAudTable;
  86                 this.includeCdmBaseAttributes = includeCdmBaseAttributes;
  87                 this.includeAnnotatableEntity = includeAnnotatableEntity;
  88                 this.includeIdentifiableEntity = includeIdentifiableEntity;
  89                 makeColumnAdders();
  90                 makeMnTables();
  91         }
  92
  93
  94         @Override
  95         public List<ISchemaUpdaterStep> getInnerSteps() {
  96                 return mnTablesStepList;
  97         }
  98
  99         /**
 100          * Fills the {@link #columnAdders} list.
 101          */
 102         private void makeColumnAdders() {
 103                 if (columnNames.size() != columnTypes.size()){
 104                         throw new RuntimeException ("ColumnNames and columnTypes must be of same size. Step: " + getStepName());
 105                 }
 106
 107                 for (int i = 0; i < columnNames.size(); i++){
 108                         boolean isNotNull = this.isNotNull == null ? false : this.isNotNull.get(i);
 109                         if ("integer".equals(columnTypes.get(i)) || "int".equals(columnTypes.get(i))){
 110                                 String referencedTable = (this.referencedTables == null) ?  null : this.referencedTables.get(i);
 111                                 ColumnAdder adder = ColumnAdder.NewIntegerInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull, referencedTable);
 112                                 this.columnAdders.add(adder);
 113                         }else if ("boolean".equals(columnTypes.get(i)) || "bit".equals(columnTypes.get(i))){
 114                                 String defaultValue = this.defaultValues == null ? null : this.defaultValues.get(i).toString();
 115                                 ColumnAdder adder = ColumnAdder.NewBooleanInstance(getStepName(), this.tableName,  this.columnNames.get(i), includeAudTable, Boolean.valueOf(defaultValue));
 116                                 this.columnAdders.add(adder);
 117                         }else if (columnTypes.get(i).startsWith("string")){
 118                                 Integer length = Integer.valueOf(columnTypes.get(i).substring("string_".length()));
 119                                 ColumnAdder adder = ColumnAdder.NewStringInstance(this.getStepName(), this.tableName, this.columnNames.get(i), length, includeAudTable);
 120                                 this.columnAdders.add(adder);
 121                         }else if ("tinyint".equals(columnTypes.get(i)) ){
 122                                 ColumnAdder adder = ColumnAdder.NewTinyIntegerInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull);
 123                                 this.columnAdders.add(adder);
 124                         }else if ("double".equals(columnTypes.get(i)) ){
 125                                 ColumnAdder adder = ColumnAdder.NewDoubleInstance(this.getStepName(), this.tableName, this.columnNames.get(i), includeAudTable, isNotNull);
 126                                 this.columnAdders.add(adder);
 127                         }
 128                 }
 129         }
 130
 131         /**
 132          * fills the mnTablesStepList
 133          */
 134         private void makeMnTables() {
 135                 TableCreator tableCreator;
 136
 137                 if (this.includeAnnotatableEntity){
 138                         //annotations
 139                         stepName= "Add @tableName annotations";
 140                         stepName = stepName.replace("@tableName", this.tableName);
 141                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Annotation", SchemaUpdaterBase.INCLUDE_AUDIT);
 142                         mnTablesStepList.add(tableCreator);
 143
 144                         //marker
 145                         stepName= "Add @tableName marker";
 146                         stepName = stepName.replace("@tableName", this.tableName);
 147                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Marker", SchemaUpdaterBase.INCLUDE_AUDIT);
 148                         mnTablesStepList.add(tableCreator);
 149
 150                 }
 151
 152                 if (this.includeIdentifiableEntity){
 153
 154                         //credits
 155                         stepName= "Add @tableName credits";
 156                         stepName = stepName.replace("@tableName", this.tableName);
 157                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "Credit", null, SchemaUpdaterBase.INCLUDE_AUDIT, SORT_INDEX, false);
 158                         mnTablesStepList.add(tableCreator);
 159
 160                         //extensions
 161                         stepName= "Add @tableName extensions";
 162                         stepName = stepName.replace("@tableName", this.tableName);
 163                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Extension", SchemaUpdaterBase.INCLUDE_AUDIT);
 164                         mnTablesStepList.add(tableCreator);
 165
 166                         //OriginalSourceBase
 167                         stepName= "Add @tableName sources";
 168                         stepName = stepName.replace("@tableName", this.tableName);
 169                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, null, "OriginalSourceBase", "sources", SchemaUpdaterBase.INCLUDE_AUDIT, false, true);
 170                         mnTablesStepList.add(tableCreator);
 171
 172                         //Rights
 173                         stepName= "Add @tableName rights";
 174                         stepName = stepName.replace("@tableName", this.tableName);
 175                         tableCreator = MnTableCreator.NewMnInstance(stepName, this.tableName, "Rights", SchemaUpdaterBase.INCLUDE_AUDIT);
 176                         mnTablesStepList.add(tableCreator);
 177                 }
 178         }
 179
 180
 181         @Override
 182         protected boolean invokeOnTable(String tableName, ICdmDataSource datasource, IProgressMonitor monitor)  {
 183                 try {
 184                         boolean result = true;
 185                         //CREATE
 186                         String updateQuery = "CREATE TABLE @tableName (";
 187                         //AUDIT
 188                         if (isAuditing){
 189                                 updateQuery += " REV integer not null, revtype tinyint, ";
 190                         }
 191                         //CdmBase
 192                         if (includeCdmBaseAttributes){
 193                                         updateQuery += " id integer not null,"
 194                                                 + " created datetime, "
 195                                                 + " uuid varchar(36),"
 196                                                 + " updated datetime, "
 197                                                 + " createdby_id integer,"
 198                                                 + " updatedby_id integer, ";
 199                         }
 200                         //EventBase
 201                         if (this.includeEventBase){
 202                                 updateQuery += "timeperiod_start varchar(255), timeperiod_end varchar(255), timeperiod_freetext varchar(255), actor_id int, description varchar(255),";
 203                         }
 204                         //Identifiable
 205                         if (this.includeIdentifiableEntity){
 206                                 updateQuery += "lsid_authority varchar(255), lsid_lsid varchar(255), lsid_namespace varchar(255), lsid_object varchar(255), lsid_revision varchar(255), protectedtitlecache bit not null, titleCache varchar(255),";
 207                         }
 208                         //specific columns
 209                         updateQuery +=  getColumnsSql(tableName, datasource, monitor);
 210
 211                         //primary and unique keys
 212                         String primaryKeySql = primaryKey(isAuditing)==null ? "" : "primary key (" + primaryKey(isAuditing) + "),";
 213                         String uniqueSql = unique(isAuditing)== null ? "" : "unique(" + unique(isAuditing) + "),";
 214                         updateQuery += primaryKeySql + uniqueSql;
 215
 216                         //finalize
 217                         updateQuery = StringUtils.chomp(updateQuery.trim(), ",") + ")";
 218
 219                         //replace
 220                         updateQuery = updateQuery.replace("@tableName", tableName);
 221
 222                         //append datasource specific string
 223                         updateQuery += datasource.getDatabaseType().getHibernateDialect().getTableTypeString();
 224                         logger.debug("UPDATE Query: " + updateQuery);
 225
 226                         //execute
 227                         datasource.executeUpdate(updateQuery);
 228
 229                         //Foreign Keys
 230                         result &= createForeignKeys(tableName, isAuditing, datasource, monitor);
 231
 232                         return result;
 233                 } catch (Exception e) {
 234                         monitor.warning(e.getMessage(), e);
 235                         logger.error(e);
 236                         return false;
 237                 }
 238         }
 239
 240
 241         /**
 242          * Returns the sql part for the {@link #columnAdders} columns.
 243          * This is done by reusing the same method in the ColumnAdder class and removing all the prefixes like 'ADD COLUMN'
 244          */
 245         private String getColumnsSql(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws DatabaseTypeNotSupportedException {
 246                 String result = "";
 247                 for (ColumnAdder adder : this.columnAdders){
 248                         String singleAdderSQL = adder.getUpdateQueryString(tableName, datasource, monitor) + ", ";
 249
 250                         String[] split = singleAdderSQL.split(ColumnAdder.getAddColumnSeperator(datasource));
 251                         result += split[1];
 252                 }
 253                 return result;
 254         }
 255
 256
 257         private boolean createForeignKeys(String tableName, boolean isAudit, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
 258                 boolean result = true;
 259                 if (includeCdmBaseAttributes){
 260                         String attribute = "updatedby";
 261                         String referencedTable = "UserAccount";
 262                         result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
 263
 264                         attribute = "createdby";
 265                         referencedTable = "UserAccount";
 266                         result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
 267
 268                 }
 269                 if (isAudit){
 270                         String attribute = "REV";
 271                         String referencedTable = "AuditEvent";
 272                         result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
 273                 }
 274                 if (this.includeEventBase){
 275                         String attribute = "actor_id";
 276                         String referencedTable = "AgentBase";
 277                         result &= makeForeignKey(tableName, datasource, monitor, attribute, referencedTable);
 278                 }
 279                 for (ColumnAdder adder : this.columnAdders){
 280                         if (adder.getReferencedTable() != null){
 281                                 result &= makeForeignKey(tableName, datasource, monitor, adder.getNewColumnName(), adder.getReferencedTable());
 282                         }
 283                 }
 284                 return result;
 285         }
 286
 287         public static boolean makeForeignKey(String tableName, ICdmDataSource datasource, IProgressMonitor monitor, String attribute, String referencedTable) throws SQLException {
 288                 boolean result = true;
 289
 290                 if (supportsForeignKeys(datasource, monitor, tableName, referencedTable)){
 291                         String index = "FK@tableName_@attribute";
 292                         index = index.replace("@tableName", tableName);
 293                         index = index.replace("@attribute", attribute);
 294
 295                         String idSuffix = "_id";
 296                         if (isRevAttribute(attribute) || attribute.endsWith(idSuffix)){
 297                                 idSuffix = "";
 298                         }
 299                         String updateQuery = "ALTER TABLE @tableName ADD INDEX @index (@attribute), ADD CONSTRAINT @index FOREIGN KEY (@attribute) REFERENCES @referencedTable (@id)";
 300                         updateQuery = updateQuery.replace("@tableName", tableName);
 301                         updateQuery = updateQuery.replace("@index", index);
 302                         updateQuery = updateQuery.replace("@attribute", attribute + idSuffix);
 303                         updateQuery = updateQuery.replace("@referencedTable", referencedTable);
 304                         if (isRevAttribute(attribute)){
 305                                 updateQuery = updateQuery.replace("@id", "revisionnumber");
 306                         }else{
 307                                 updateQuery = updateQuery.replace("@id", "id");
 308                         }
 309                         logger.debug(updateQuery);
 310                         try {
 311                                 datasource.executeUpdate(updateQuery);
 312                         } catch (Exception e) {
 313                                 String message = "Problem when creating Foreign Key for " + tableName +"." + attribute +": " + e.getMessage();
 314                                 monitor.warning(message);
 315                                 logger.warn(message, e);
 316                                 return true;
 317                         }
 318                         return result;
 319                 }else{
 320                         return true;
 321                 }
 322
 323         }
 324
 325         /**
 326          * Determines if the tables and the database support foreign keys. If determination is not possible true is returned as default.
 327          * @param datasource
 328          * @param monitor
 329          * @param tableName
 330          * @param referencedTable
 331          * @return
 332          */
 333         private static boolean supportsForeignKeys(ICdmDataSource datasource, IProgressMonitor monitor, String tableName, String referencedTable) {
 334                 boolean result = true;
 335                 if (! datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){
 336                         return true;
 337                 }else{
 338                         try {
 339                                 String myIsamTables = "";
 340                                 String format = "SELECT ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'";
 341                                 String sql = String.format(format, datasource.getDatabase(), tableName);
 342                                 String engine = (String)datasource.getSingleValue(sql);
 343                                 if (engine.equals("MyISAM")){
 344                                         result = false;
 345                                         myIsamTables = CdmUtils.concat(",", myIsamTables, tableName);
 346                                 }
 347                                 sql = String.format(format,  datasource.getDatabase(), referencedTable);
 348                                 engine = (String)datasource.getSingleValue(sql);
 349                                 if (engine.equals("MyISAM")){
 350                                         result = false;
 351                                         myIsamTables = CdmUtils.concat(",", myIsamTables, referencedTable);
 352                                 }
 353                                 if (result == false){
 354                                         String message = "Tables (%s) use MyISAM engine. MyISAM does not support foreign keys.";
 355                                         message = String.format(message, myIsamTables);
 356                                         monitor.warning(message);
 357                                 }
 358                                 return result;
 359                         } catch (Exception e) {
 360                                 String message = "Problems to determine table engine for MySQL.";
 361                                 monitor.warning(message);
 362                                 return true;  //default
 363                         }
 364
 365                 }
 366
 367
 368         }
 369
 370         private static boolean isRevAttribute(String attribute) {
 371                 return "REV".equalsIgnoreCase(attribute);
 372         }
 373
 374
 375         /**
 376          * Constructs the primary key creation string
 377          * @param isAudit
 378          * @return
 379          */
 380         protected String primaryKey(boolean isAudit){
 381                 String result = null;
 382                 if (! isAudit && this.primaryKeyParams != null){
 383                         return this.primaryKeyParams;
 384                 }else if (isAudit && this.primaryKeyParams_AUD != null){
 385                         return this.primaryKeyParams_AUD;
 386                 }
 387
 388                 if (includeCdmBaseAttributes || ! includeCdmBaseAttributes){ //TODO how to handle not CDMBase includes
 389                         if (! isAudit){
 390                                 result = "id";
 391                         }else{
 392                                 result = "id, REV";
 393                         }
 394                 }
 395                 return result;
 396         }
 397
 398         /**
 399          * Constructs the unique key creation string
 400          * @param isAudit
 401          * @return
 402          */
 403         protected String unique(boolean isAudit){
 404                 if (! isAudit){
 405                         if (this.uniqueParams != null){
 406                                 return this.uniqueParams;
 407                         }
 408                         if (includeCdmBaseAttributes){
 409                                 return "uuid"; //TODO how to handle not CDMBase includes
 410                         }
 411                         return null;
 412                 }else{
 413                         if (this.uniqueParams_AUD != null){
 414                                 return this.uniqueParams_AUD;
 415                         }
 416                         return null;
 417                 }
 418         }
 419
 420         public void setPrimaryKeyParams(String primaryKeyParams, String primaryKeyParams_AUD) {
 421                 this.primaryKeyParams = primaryKeyParams;
 422                 this.primaryKeyParams_AUD = primaryKeyParams_AUD;
 423         }
 424
 425         public void setUniqueParams(String uniqueParams, String uniqueParams_AUD) {
 426                 this.uniqueParams = uniqueParams;
 427                 this.uniqueParams_AUD = uniqueParams_AUD;
 428         }
 429 }