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

   1 /**
   2 * Copyright (C) 2009 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9 package eu.etaxonomy.cdm.database.update;
  10
  11 import java.sql.ResultSet;
  12 import java.sql.SQLException;
  13 import java.util.ArrayList;
  14 import java.util.HashMap;
  15 import java.util.HashSet;
  16 import java.util.List;
  17 import java.util.Map;
  18 import java.util.Set;
  19
  20 import org.apache.logging.log4j.LogManager;
  21 import org.apache.logging.log4j.Logger;
  22
  23 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  24 import eu.etaxonomy.cdm.database.ICdmDataSource;
  25
  26 /**
  27  * @author a.mueller
  28  * @since 16.09.2010
  29  */
  30 public class SortIndexUpdater extends SchemaUpdaterStepBase {
  31
  32     private static final Logger logger = LogManager.getLogger();
  33
  34         private final String tableName;
  35     private final String sortIndexColumn;
  36         private final String parentColumn;
  37     private String idColumn = "id";
  38         private String currentSortColumn = "id";
  39         private final boolean includeAudTable;
  40         private Integer baseValue = 0;
  41
  42         public static final SortIndexUpdater NewInstance(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){
  43                 return new SortIndexUpdater(stepList, stepName, tableName, parentColumn, sortIndexColumn, "id", "id", includeAudTable, 0);
  44         }
  45
  46         public static final SortIndexUpdater NewInstance(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable){
  47                 return new SortIndexUpdater(stepList, stepName, tableName, parentColumn, sortIndexColumn, idColumn, idColumn, includeAudTable, 0);
  48         }
  49
  50     /**
  51      * Returns an SortIndexUpdater that updates an existing sort index which might have missing sortindex numbers in between.
  52      */
  53     public static final SortIndexUpdater NewUpdateExistingSortindexInstance(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){
  54         return new SortIndexUpdater(stepList, stepName, tableName, parentColumn, sortIndexColumn, "id", sortIndexColumn, includeAudTable, 0);
  55     }
  56
  57         protected SortIndexUpdater(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, String currentSortColumn, boolean includeAudTable, Integer baseValue) {
  58                 super(stepList, 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 void invoke(ICdmDataSource datasource, IProgressMonitor monitor,
  70             CaseType caseType, SchemaUpdateResult result) throws SQLException {
  71                 addColumn(tableName, datasource, result);
  72                 if (includeAudTable){
  73                         String aud = "_AUD";
  74                         addColumn(caseType.transformTo(tableName + aud), datasource, result);
  75                 }
  76                 return;
  77         }
  78
  79         private void addColumn( String tableName, ICdmDataSource datasource, SchemaUpdateResult result) throws SQLException {
  80                 //Note: caseType not required here
  81             Map<Integer, Set<Integer>> indexMap ;
  82             if (tableName == null){
  83                 tableName = this.tableName;
  84             }
  85             indexMap = makeIndexMap(tableName, datasource);
  86             updateIndices(tableName, datasource, indexMap);
  87
  88                 return;
  89         }
  90
  91         public String createIndexMapQuery(){
  92                String resultsetQuery = "SELECT @id as id, @parentColumn " +
  93                         " FROM @tableName " +
  94                         " WHERE @parentColumn IS NOT NULL " +
  95                         " ORDER BY @parentColumn, @sorted";
  96                 resultsetQuery = resultsetQuery.replace("@id", idColumn);
  97                 resultsetQuery = resultsetQuery.replace("@tableName", tableName);
  98                 resultsetQuery = resultsetQuery.replace("@parentColumn", parentColumn);
  99                 resultsetQuery = resultsetQuery.replace("@sorted", currentSortColumn);
 100                 return resultsetQuery;
 101         }
 102
 103         /**
 104          * For each (new) sortIndex value the according record ids are computed.
 105          * This allows updating all records sortindex by sortindex.
 106          * @param tableName
 107          * @param datasource
 108          * @return
 109          * @throws SQLException
 110          */
 111         private Map<Integer, Set<Integer>> makeIndexMap(String tableName, ICdmDataSource datasource) throws NumberFormatException, SQLException {
 112             String resultsetQuery = createIndexMapQuery();
 113
 114                 ResultSet rs = datasource.executeQuery(resultsetQuery);
 115                 List<Integer[]> result = new ArrayList<>();
 116                 while (rs.next()){
 117                     int id = rs.getInt("id");
 118             Object oParentId = rs.getObject(parentColumn);
 119             if (oParentId != null){
 120                 int parentId = Integer.valueOf(oParentId.toString());
 121                 result.add(new Integer[]{id,parentId});
 122             }
 123                 }
 124         return makeIndexMap( result);
 125
 126                 //increase index with each row, set to 0 if parent is not the same as the previous one
 127         }
 128
 129         public Map<Integer, Set<Integer>> makeIndexMap(List<Integer[]> oldIndexMap) throws NumberFormatException{
 130             int oldParentId = -1;
 131             Integer index = baseValue;
 132             Map<Integer, Set<Integer>> indexMap = new HashMap<>();
 133         for (Integer[] entry: oldIndexMap){
 134             int id = entry[0];
 135             Integer oParentId = entry[1];
 136             if (oParentId != null){
 137                 int parentId = Integer.valueOf(oParentId.toString());
 138                 if (oldParentId != parentId){
 139                     index = baseValue;
 140                     oldParentId = parentId;
 141                 }else{
 142                     index++;
 143                 }
 144                 putIndex(id, index, indexMap);
 145             }else{
 146                 logger.warn("This should not happen");
 147                 index = baseValue;
 148             }
 149 //          System.out.println(oParentId + "," + id+","+ index+";");
 150         }
 151         return indexMap;
 152         }
 153
 154         public String createUpdateIndicesQuery(String tableName, Integer index, String idSetString){
 155             if (tableName == null){
 156                 tableName = this.tableName;
 157             }
 158             String updateQuery = "UPDATE @tableName SET @sortIndexColumn = @index WHERE @id IN (@idList) ";
 159         updateQuery = updateQuery.replace("@tableName", tableName);
 160         updateQuery = updateQuery.replace("@sortIndexColumn", sortIndexColumn);
 161         updateQuery = updateQuery.replace("@index", index.toString());
 162         updateQuery = updateQuery.replace("@idList", idSetString);
 163         updateQuery = updateQuery.replace("@id", idColumn);
 164         return updateQuery;
 165         }
 166
 167         private void updateIndices(String tableName, ICdmDataSource datasource, Map<Integer, Set<Integer>> indexMap)
 168                         throws SQLException {
 169             String updateQuery ;
 170                 for (Integer index :  indexMap.keySet()){
 171                         Set<Integer> set = indexMap.get(index);
 172                         String idSetString = makeIdSetString(set);
 173
 174                         updateQuery = createUpdateIndicesQuery(tableName, index, idSetString);
 175                         datasource.executeUpdate(updateQuery);
 176                 }
 177         }
 178
 179         public static String makeIdSetString(Set<Integer> set) {
 180                 StringBuffer result = new StringBuffer(set.size() * 5);
 181                 for (Integer id:set){
 182                         result.append(id + ",");
 183                 }
 184                 return result.substring(0, result.length() - 1);
 185         }
 186
 187         /**
 188          * Adds the id to the index (each id is attached to an (sort)index)
 189          */
 190         private void putIndex(Integer id, Integer index, Map<Integer, Set<Integer>> indexMap) {
 191                 Set<Integer> set = indexMap.get(index);
 192                 if (set == null){
 193                         set = new HashSet<>();
 194                         indexMap.put(index, set);
 195                 }
 196                 set.add(id);
 197         }
 198
 199         public String getChildrenCountQuery(){
 200
 201         String countSelect = "SELECT COUNT(child.id) as realCount, parent.countChildren as countChildren, parent.id as parentID FROM @tableName child RIGHT JOIN @tableName parent ON child.parent_id = parent.id GROUP BY parent.id";
 202         countSelect = countSelect.replace("@tableName", tableName);
 203         return countSelect;
 204         }
 205
 206         public String getUpdateChildrenCountQuery(int count, int id){
 207         String countUpdate =
 208                   " UPDATE @tableName "
 209                 + " SET countChildren = " + count
 210                 + " WHERE id = " + id;
 211         countUpdate = countUpdate.replace("@tableName", tableName);
 212         return countUpdate;
 213     }
 214
 215         private void updateChildrenCount(String select, ICdmDataSource datasource) throws SQLException{
 216
 217             ResultSet rs = datasource.executeQuery(select);
 218
 219         int count ;
 220         int countChildren;
 221         int parentId;
 222         while (rs.next()){
 223             count = rs.getInt("realCount");
 224             countChildren = rs.getInt("countChildren");
 225             parentId = rs.getInt("parentID");
 226             if (count != countChildren){
 227                 String updateQuery = getUpdateChildrenCountQuery(count, parentId);
 228                 datasource.executeUpdate(updateQuery);
 229             }
 230         }
 231         }
 232
 233     public String getTableName() {
 234         return tableName;
 235     }
 236 }