cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v25_30/SequenceTableCreator.java

   1 // $Id$
   2 /**
   3 * Copyright (C) 2007 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
  11 package eu.etaxonomy.cdm.database.update.v25_30;
  12
  13 import java.sql.DatabaseMetaData;
  14 import java.sql.ResultSet;
  15 import java.sql.SQLException;
  16
  17 import org.apache.log4j.Logger;
  18
  19 import com.mysql.jdbc.exceptions.MySQLSyntaxErrorException;
  20
  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.database.update.SchemaUpdaterStepBase;
  25
  26 /**
  27  * Creates the table needed by the {@link org.hibernate.id.enhanced.TableGenerator}
  28  * We expect the generator to be configured with <code>prefer_entity_table_as_segment_value</code>
  29  * set to <code>true</code> (the generator does not make lots of sense without this option)
  30  *
  31  * We also create sequences for all tables that are not empty. Otherwise we would run into
  32  * id conflicts, because the generator expects the database to be empty and creates sequences,
  33  * if they do not exist, as needed.
  34  *
  35  * @author n.hoffmann
  36  * @created Oct 27, 2010
  37  */
  38 public class SequenceTableCreator extends SchemaUpdaterStepBase {
  39
  40         @SuppressWarnings("unused")
  41         private static final Logger logger = Logger.getLogger(SequenceTableCreator.class);
  42
  43         // TODO These values are configurable in the enhanced.TableGenerator
  44         // can we retrieve these values from the identity generator directly?
  45         private static final String TABLE_NAME = "hibernate_sequences";
  46         private static final String SEGMENT_COLUMN_NAME = "sequence_name";
  47         private static final String VALUE_COLUMN_NAME = "next_val";
  48         private static final int INCREMENT_SIZE = 10;
  49
  50         /**
  51          * @param stepName
  52          */
  53         protected SequenceTableCreator(String stepName) {
  54                 super(stepName);
  55         }
  56
  57         public static SequenceTableCreator NewInstance(String stepName){
  58                 return new SequenceTableCreator(stepName);
  59         }
  60
  61         @Override
  62         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor){
  63                 boolean result = true;
  64                 try {
  65                         result &= createSequenceTable(datasource, monitor);
  66                         result &= makeEntriesForEntityTables(datasource, monitor);
  67                 } catch (Exception e) {
  68                         monitor.warning(e.getMessage(), e);
  69                         result = false;
  70                 }
  71
  72                 return (result == true ) ? 0 : null;
  73         }
  74
  75         /**
  76          * @param monitor
  77          * @param datasource
  78          * @return
  79          * @throws SQLException
  80          */
  81         private boolean createSequenceTable(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
  82                 boolean result = true;
  83                 String createTableQuery = null;
  84                 // TODO add create table statements for other supported databases
  85                 if(datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){
  86                         createTableQuery = "CREATE TABLE `" + TABLE_NAME + "` (" +
  87                                 "  `" + SEGMENT_COLUMN_NAME + "` varchar(255) NOT NULL," +
  88                                 "  `" + VALUE_COLUMN_NAME + "` bigint(20) default NULL," +
  89                                 "  PRIMARY KEY  (`" + SEGMENT_COLUMN_NAME + "`)" +
  90                                 ");";
  91                 }else{
  92                         throw new RuntimeException("Database type " + datasource.getDatabaseType() + " is currently not supported by the updater");
  93                 }
  94
  95                 datasource.executeUpdate(createTableQuery);
  96
  97                 return result;
  98         }
  99
 100         /**
 101          * @param monitor
 102          * @param datasource
 103          * @return
 104          * @throws SQLException
 105          */
 106         private boolean makeEntriesForEntityTables(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
 107
 108                 DatabaseMetaData metaData = datasource.getMetaData();
 109                 ResultSet resultSet = metaData.getTables(datasource.getDatabase(), null, null, null);
 110
 111                 String maxIdQuery = "SELECT MAX(ID) FROM @tableName";
 112                 String insertQuery = "INSERT INTO " + TABLE_NAME + " ( " + SEGMENT_COLUMN_NAME + ", " + VALUE_COLUMN_NAME + ")" +
 113                                 " VALUES ('@tableName', (@maxId + " + INCREMENT_SIZE + "))";
 114
 115                 Object maxId = null;
 116
 117                 while(resultSet.next()){
 118                         // through debugging we found out that the table name is in column 3.
 119                         // TODO improve this if you know that this will not always be the case for all database types
 120                         // and/or if you know of a good way to do it in more generic way
 121                         String tableName = resultSet.getString(3);
 122                         // this way we simply filter out all relation tables, could have done this with a tableNamePattern passed to getTables(...)
 123                         // but this was faster.
 124                         if(tableName.contains("_")){
 125                                 continue;
 126                         }
 127
 128                         try{
 129                                 String query = maxIdQuery.replace("@tableName", tableName);
 130                                 maxId = datasource.getSingleValue(query);
 131                         }catch(MySQLSyntaxErrorException e){
 132                                 // table does not have a column id, so it is not an entity table
 133                                 maxId = null;
 134                         }
 135
 136                         // empty tables will not set the maxId.
 137                         // Empty tables will also not need to be updated, because the identity generator creates sequences for tables it did not have before
 138                         if(maxId != null){
 139                                 monitor.subTask("Inserting sequence for table: " + tableName + " with maxId: " + maxId);
 140
 141                                 String query = insertQuery.replace("@tableName", tableName);
 142                                 query = query.replace("@maxId", maxId.toString());
 143
 144                                 datasource.executeUpdate(query);
 145                         }
 146
 147                 }
 148                 return true;
 149         }
 150
 151 }