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.util.List;
|
12
|
|
13
|
import org.apache.logging.log4j.LogManager;import org.apache.logging.log4j.Logger;
|
14
|
|
15
|
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
|
16
|
import eu.etaxonomy.cdm.database.DatabaseTypeEnum;
|
17
|
import eu.etaxonomy.cdm.database.ICdmDataSource;
|
18
|
import eu.etaxonomy.cdm.model.common.ITreeNode;
|
19
|
|
20
|
/**
|
21
|
* @author a.mueller
|
22
|
* @since 09.08.2013
|
23
|
*/
|
24
|
public class TreeIndexUpdater
|
25
|
extends AuditedSchemaUpdaterStepBase {
|
26
|
|
27
|
private static final Logger logger = LogManager.getLogger(TreeIndexUpdater.class);
|
28
|
|
29
|
private String indexColumnName = "treeIndex";
|
30
|
private final String treeIdColumnName;
|
31
|
private final String parentIdColumnName = "parent_id";
|
32
|
|
33
|
public static final TreeIndexUpdater NewInstance(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String treeIdColumnName, boolean includeAudTable){
|
34
|
return new TreeIndexUpdater(stepList, stepName, tableName, treeIdColumnName, null, includeAudTable);
|
35
|
}
|
36
|
|
37
|
public static final TreeIndexUpdater NewInstance(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String treeIdColumnName, String indexColumnName, boolean includeAudTable){
|
38
|
return new TreeIndexUpdater(stepList, stepName, tableName, treeIdColumnName, indexColumnName, includeAudTable);
|
39
|
}
|
40
|
|
41
|
protected TreeIndexUpdater(List<ISchemaUpdaterStep> stepList, String stepName, String tableName, String treeIdColumnName, String indexColumnName, boolean includeAudTable) {
|
42
|
super(stepList, stepName, tableName, includeAudTable);
|
43
|
this.treeIdColumnName = treeIdColumnName;
|
44
|
this.indexColumnName = indexColumnName == null ? this.indexColumnName : indexColumnName;
|
45
|
}
|
46
|
|
47
|
@Override
|
48
|
protected void invokeOnTable(String tableName, ICdmDataSource datasource,
|
49
|
IProgressMonitor monitor, CaseType caseType, SchemaUpdateResult result) {
|
50
|
|
51
|
try{
|
52
|
// String charType = "CHAR"; //TODO may depend on database type
|
53
|
|
54
|
//clean up nodes without classification //this should not happen with correct "delete" implementation
|
55
|
String sql = String.format(" DELETE FROM %s WHERE %s IS NULL ", tableName, treeIdColumnName);
|
56
|
datasource.executeUpdate(sql);
|
57
|
|
58
|
//... set all index entries to NULL
|
59
|
sql = String.format(" UPDATE %s SET %s = NULL", tableName, indexColumnName);
|
60
|
datasource.executeUpdate(sql);
|
61
|
|
62
|
//start
|
63
|
String separator = ITreeNode.separator;
|
64
|
String treePrefix = ITreeNode.treePrefix;
|
65
|
sql = String.format(" UPDATE %s " +
|
66
|
" SET %s = CONCAT('%s%s', %s, '%s', id, '%s') " +
|
67
|
" WHERE %s IS NULL AND %s IS NOT NULL ",
|
68
|
tableName,
|
69
|
indexColumnName, separator, treePrefix, treeIdColumnName, separator, separator,
|
70
|
parentIdColumnName, treeIdColumnName);
|
71
|
datasource.executeUpdate(sql);
|
72
|
|
73
|
//width search index creation
|
74
|
String sqlCount = String.format(" SELECT count(*) as n " +
|
75
|
" FROM %s child INNER JOIN %s parent ON child.%s = parent.id " +
|
76
|
" WHERE parent.%s IS NOT NULL AND child.%s IS NULL ",
|
77
|
tableName, tableName, parentIdColumnName, indexColumnName, indexColumnName);
|
78
|
|
79
|
Long n;
|
80
|
do {
|
81
|
|
82
|
//MySQL
|
83
|
if (datasource.getDatabaseType().equals(DatabaseTypeEnum.MySQL)){
|
84
|
sql = String.format(" UPDATE %s child " +
|
85
|
" INNER JOIN %s parent ON child.%s = parent.id " +
|
86
|
" SET child.%s = CONCAT( parent.%s, child.id, '%s') " +
|
87
|
" WHERE parent.%s IS NOT NULL AND child.%s IS NULL ",
|
88
|
tableName,
|
89
|
tableName, parentIdColumnName,
|
90
|
indexColumnName, indexColumnName, separator,
|
91
|
indexColumnName, indexColumnName);
|
92
|
}else{
|
93
|
//ANSI
|
94
|
//http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql
|
95
|
//does not work with MySQL as MySQL does not allow to use the same table in Subselect and Update (error 1093: http://dev.mysql.com/doc/refman/5.1/de/subquery-errors.html)
|
96
|
sql = String.format(" UPDATE %s " +
|
97
|
" SET %s = ( " +
|
98
|
" ( SELECT CONCAT ( parent.%s, %s.id, '%s') " +
|
99
|
" FROM %s parent " +
|
100
|
" WHERE parent.id = %s.%s ) " +
|
101
|
" ) " +
|
102
|
" WHERE EXISTS ( " +
|
103
|
" SELECT * " +
|
104
|
" FROM %s parent " +
|
105
|
" WHERE parent.id = %s.%s AND parent.%s IS NOT NULL AND %s.%s IS NULL " +
|
106
|
") " +
|
107
|
" ",
|
108
|
tableName,
|
109
|
indexColumnName,
|
110
|
indexColumnName, tableName, separator,
|
111
|
tableName,
|
112
|
tableName, parentIdColumnName,
|
113
|
|
114
|
tableName,
|
115
|
tableName, parentIdColumnName, indexColumnName, tableName, indexColumnName
|
116
|
);
|
117
|
}
|
118
|
|
119
|
datasource.executeUpdate(sql);
|
120
|
|
121
|
n = (Long)datasource.getSingleValue(sqlCount);
|
122
|
} while (n > 0) ;
|
123
|
|
124
|
sqlCount = String.format(" SELECT count(*) as n " +
|
125
|
" FROM %s " +
|
126
|
" WHERE %s IS NULL ", tableName, indexColumnName);
|
127
|
n = (Long)datasource.getSingleValue(sqlCount);
|
128
|
if (n > 0){
|
129
|
String message = "There are tree nodes with no tree index in %s. This indicates that there is a problem in the tree structure of 1 or more classifications.";
|
130
|
logger.error(String.format(message, tableName));
|
131
|
result.addWarning(message, (String)null, getStepName());
|
132
|
}
|
133
|
|
134
|
return;
|
135
|
}catch(Exception e){
|
136
|
String message = e.getMessage();
|
137
|
monitor.warning(message, e);
|
138
|
logger.error(message);
|
139
|
result.addException(e, message, getStepName() + ", TreeIndexUpdater.invokeOnTable");
|
140
|
return;
|
141
|
}
|
142
|
}
|
143
|
|
144
|
}
|