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> implements ISchemaUpdaterStep {
  30         private static final Logger logger = Logger.getLogger(SortIndexUpdater.class);
  31
  32         private String tableName;
  33         private String sortIndexColumn;
  34         private String parentColumn;
  35         private String idColumn = "id";
  36         private boolean includeAudTable;
  37         private Integer baseValue = 0;
  38
  39         public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){
  40                 return new SortIndexUpdater(stepName, tableName, parentColumn, sortIndexColumn, "id", includeAudTable, 0);
  41         }
  42
  43         public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable){
  44                 return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, idColumn, includeAudTable, 0);
  45         }
  46
  47
  48         protected SortIndexUpdater(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable, Integer baseValue) {
  49                 super(stepName);
  50                 this.tableName = tableName;
  51                 this.parentColumn = parentColumn;
  52                 this.sortIndexColumn = sortIndexColumn;
  53                 this.idColumn = idColumn;
  54                 this.includeAudTable = includeAudTable;
  55                 this.baseValue = baseValue;
  56         }
  57
  58         @Override
  59         public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
  60                 boolean result = true;
  61                 result &= addColumn(tableName, datasource, monitor);
  62                 if (includeAudTable){
  63                         String aud = "_AUD";
  64                         result &= addColumn(tableName + aud, datasource, monitor);
  65                 }
  66                 return (result == true )? 0 : null;
  67         }
  68
  69         private boolean addColumn(String tableName, ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
  70
  71                 Map<Integer, Set<Integer>> indexMap = makeIndexMap(datasource);
  72
  73                 updateIndices(tableName, datasource, indexMap);
  74
  75                 return true;
  76         }
  77
  78         /**
  79          * @param datasource
  80          * @param rs
  81          * @param index
  82          * @param oldParentId
  83          * @return
  84          * @throws SQLException
  85          */
  86         private Map<Integer, Set<Integer>> makeIndexMap(ICdmDataSource datasource) throws SQLException {
  87                 String resulsetQuery = "SELECT @id as id, @parentColumn " +
  88                                 " FROM @tableName " +
  89                                 " WHERE @parentColumn IS NOT NULL " +
  90                                 " ORDER BY @parentColumn,    @id";
  91                 resulsetQuery = resulsetQuery.replace("@tableName", tableName);
  92                 resulsetQuery = resulsetQuery.replace("@parentColumn", parentColumn);
  93                 resulsetQuery = resulsetQuery.replace("@id", idColumn);
  94
  95                 ResultSet rs = datasource.executeQuery(resulsetQuery);
  96                 Integer index = baseValue;
  97                 int oldParentId = -1;
  98
  99
 100                 //increase index with each row, set to 0 if parent is not the same as the previous one
 101                 Map<Integer, Set<Integer>> indexMap = new HashMap<Integer, Set<Integer>>();
 102                 while (rs.next() ){
 103                         int id = rs.getInt("id");
 104                         Object oParentId = rs.getObject(parentColumn);
 105                         if (oParentId != null){
 106                                 int parentId = Integer.valueOf(oParentId.toString());
 107                                 if (oldParentId != parentId){
 108                                         index = baseValue;
 109                                         oldParentId = parentId;
 110                                 }else{
 111                                         index++;
 112                                 }
 113                                 putIndex(id, index, indexMap);
 114                         }else{
 115                                 logger.warn("This should not happen");
 116                                 index = baseValue;
 117                         }
 118 //                      System.out.println(oParentId + "," + id+","+ index+";");
 119                 }
 120                 return indexMap;
 121         }
 122
 123         private void updateIndices(String tableName, ICdmDataSource datasource, Map<Integer, Set<Integer>> indexMap)
 124                         throws SQLException {
 125                 for (Integer index :  indexMap.keySet()){
 126                         Set<Integer> set = indexMap.get(index);
 127                         String idSetString = makeIdSetString(set);
 128
 129                         String updateQuery = "UPDATE @tableName SET @sortIndexColumn = @index WHERE @id IN (@idList) ";
 130                         updateQuery = updateQuery.replace("@tableName", tableName);
 131                         updateQuery = updateQuery.replace("@sortIndexColumn", sortIndexColumn);
 132                         updateQuery = updateQuery.replace("@index", index.toString());
 133                         updateQuery = updateQuery.replace("@idList", idSetString);
 134                         updateQuery = updateQuery.replace("@id", idColumn);
 135                         datasource.executeUpdate(updateQuery);
 136                 }
 137         }
 138
 139         private static String makeIdSetString(Set<Integer> set) {
 140                 StringBuffer result = new StringBuffer(set.size() * 5);
 141                 for (Integer id:set){
 142                         result.append(id + ",");
 143                 }
 144                 return result.substring(0, result.length() - 1);
 145         }
 146
 147         /**
 148          * Adds the id to the index (each id is attached to an (sort)index)
 149          */
 150         private void putIndex(Integer id, Integer index, Map<Integer, Set<Integer>> indexMap) {
 151                 Set<Integer> set = indexMap.get(index);
 152                 if (set == null){
 153                         set = new HashSet<Integer>();
 154                         indexMap.put(index, set);
 155                 }
 156                 set.add(id);
 157         }
 158
 159 }