cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/SortIndexUpdater.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.ResultSet;
  13 import java.sql.SQLException;
  14 import java.util.HashMap;
  15 import java.util.HashSet;
  16 import java.util.Map;
  17 import java.util.Set;
  18
  19 import org.apache.log4j.Logger;
  20
  21 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  22 import eu.etaxonomy.cdm.database.ICdmDataSource;
  23
  24 /**
  25  * @author a.mueller
  26  * @date 16.09.2010
  27  *
  28  */
  29 public class SortIndexUpdater extends SchemaUpdaterStepBase<SortIndexUpdater> {
  30         private static final Logger logger = Logger.getLogger(SortIndexUpdater.class);
  31
  32         private final String tableName;
  33         private final String sortIndexColumn;
  34         private final String parentColumn;
  35         private String idColumn = "id";
  36         private String currentSortColumn = "id";
  37         private final boolean includeAudTable;
  38         private Integer baseValue = 0;
  39
  40         public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){
  41                 return new SortIndexUpdater(stepName, tableName, parentColumn, sortIndexColumn, "id", "id", includeAudTable, 0);
  42         }
  43
  44         public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable){
  45                 return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, idColumn, idColumn, includeAudTable, 0);
  46         }
  47
  48     /**
  49      * Returns an SortIndexUpdater that updates an existing sortindex which might have missing sortindex numbers in between.
  50      *
  51      */
  52     public static final SortIndexUpdater NewUpdateExistingSortindexInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){
  53         return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, "id", sortIndexColumn, includeAudTable, 0);
  54     }
  55
  56
  57         protected SortIndexUpdater(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, String currentSortColumn, boolean includeAudTable, Integer baseValue) {
  58                 super(stepName);
  59                 this.tableName = tableName;
  60                 this.parentColumn = parentColumn;
  61                 this.sortIndexColumn = sortIndexColumn;
  62                 this.idColumn = idColumn;
  63                 this.currentSortColumn = currentSortColumn;
  64                 this.includeAudTable = includeAudTable;
  65                 this.baseValue = baseValue;
  66         }
  67
  68         @Override
  69         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {
  70                 boolean result = true;
  71                 result &= addColumn(caseType.transformTo(tableName), datasource);
  72                 if (includeAudTable){
  73                         String aud = "_AUD";
  74                         result &= addColumn(caseType.transformTo(tableName + aud), datasource);
  75                 }
  76                 return (result == true )? 0 : null;
  77         }
  78
  79         private boolean addColumn(String tableName, ICdmDataSource datasource) throws SQLException {
  80                 //Note: caseType not required here
  81                 Map<Integer, Set<Integer>> indexMap = makeIndexMap(tableName, datasource);
  82
  83                 updateIndices(tableName, datasource, indexMap);
  84
  85                 return true;
  86         }
  87
  88
  89         /**
  90          * For each (new) sortIndex value the according record ids are computed.
  91          * This allows updating all records sortindex by sortindex.
  92          * @param tableName
  93          * @param datasource
  94          * @return
  95          * @throws SQLException
  96          */
  97         private Map<Integer, Set<Integer>> makeIndexMap(String tableName, ICdmDataSource datasource) throws SQLException {
  98                 String resultsetQuery = "SELECT @id as id, @parentColumn " +
  99                                 " FROM @tableName " +
 100                                 " WHERE @parentColumn IS NOT NULL " +
 101                                 " ORDER BY @parentColumn, @sorted";
 102                 resultsetQuery = resultsetQuery.replace("@id", idColumn);
 103                 resultsetQuery = resultsetQuery.replace("@tableName", tableName);
 104                 resultsetQuery = resultsetQuery.replace("@parentColumn", parentColumn);
 105                 resultsetQuery = resultsetQuery.replace("@sorted", currentSortColumn);
 106
 107                 ResultSet rs = datasource.executeQuery(resultsetQuery);
 108                 Integer index = baseValue;
 109                 int oldParentId = -1;
 110
 111
 112                 //increase index with each row, set to 0 if parent is not the same as the previous one
 113                 Map<Integer, Set<Integer>> indexMap = new HashMap<>();
 114                 while (rs.next() ){
 115                         int id = rs.getInt("id");
 116                         Object oParentId = rs.getObject(parentColumn);
 117                         if (oParentId != null){
 118                                 int parentId = Integer.valueOf(oParentId.toString());
 119                                 if (oldParentId != parentId){
 120                                         index = baseValue;
 121                                         oldParentId = parentId;
 122                                 }else{
 123                                         index++;
 124                                 }
 125                                 putIndex(id, index, indexMap);
 126                         }else{
 127                                 logger.warn("This should not happen");
 128                                 index = baseValue;
 129                         }
 130 //                      System.out.println(oParentId + "," + id+","+ index+";");
 131                 }
 132                 return indexMap;
 133         }
 134
 135         private void updateIndices(String tableName, ICdmDataSource datasource, Map<Integer, Set<Integer>> indexMap)
 136                         throws SQLException {
 137                 for (Integer index :  indexMap.keySet()){
 138                         Set<Integer> set = indexMap.get(index);
 139                         String idSetString = makeIdSetString(set);
 140
 141                         String updateQuery = "UPDATE @tableName SET @sortIndexColumn = @index WHERE @id IN (@idList) ";
 142                         updateQuery = updateQuery.replace("@tableName", tableName);
 143                         updateQuery = updateQuery.replace("@sortIndexColumn", sortIndexColumn);
 144                         updateQuery = updateQuery.replace("@index", index.toString());
 145                         updateQuery = updateQuery.replace("@idList", idSetString);
 146                         updateQuery = updateQuery.replace("@id", idColumn);
 147                         datasource.executeUpdate(updateQuery);
 148                 }
 149         }
 150
 151         private static String makeIdSetString(Set<Integer> set) {
 152                 StringBuffer result = new StringBuffer(set.size() * 5);
 153                 for (Integer id:set){
 154                         result.append(id + ",");
 155                 }
 156                 return result.substring(0, result.length() - 1);
 157         }
 158
 159         /**
 160          * Adds the id to the index (each id is attached to an (sort)index)
 161          */
 162         private void putIndex(Integer id, Integer index, Map<Integer, Set<Integer>> indexMap) {
 163                 Set<Integer> set = indexMap.get(index);
 164                 if (set == null){
 165                         set = new HashSet<>();
 166                         indexMap.put(index, set);
 167                 }
 168                 set.add(id);
 169         }
 170
 171 }