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