1
|
/**
|
2
|
* Copyright (C) 2016 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.v40_41;
|
10
|
|
11
|
import java.sql.ResultSet;
|
12
|
import java.sql.SQLException;
|
13
|
import java.util.UUID;
|
14
|
|
15
|
import org.apache.log4j.Logger;
|
16
|
|
17
|
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
|
18
|
import eu.etaxonomy.cdm.database.ICdmDataSource;
|
19
|
import eu.etaxonomy.cdm.database.update.CaseType;
|
20
|
import eu.etaxonomy.cdm.database.update.SchemaUpdateResult;
|
21
|
import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase;
|
22
|
|
23
|
/**
|
24
|
* Creates new synonym records for all synonym having >1 synonym relationships.
|
25
|
* Expects unplaced, excluded and taxonomicChildrenCount to not exist anymore
|
26
|
* @author a.mueller
|
27
|
\* @since 14.11.2016
|
28
|
*
|
29
|
*/
|
30
|
public class SynonymDeduplicator
|
31
|
extends SchemaUpdaterStepBase{
|
32
|
|
33
|
@SuppressWarnings("unused")
|
34
|
private static final Logger logger = Logger.getLogger(SynonymDeduplicator.class);
|
35
|
|
36
|
/**
|
37
|
* @return
|
38
|
*/
|
39
|
public static SynonymDeduplicator NewInstance() {
|
40
|
return new SynonymDeduplicator();
|
41
|
}
|
42
|
|
43
|
private static final String stepName = "Deduplicate synonyms code";
|
44
|
|
45
|
/**
|
46
|
* @param stepName
|
47
|
*/
|
48
|
protected SynonymDeduplicator() {
|
49
|
super(stepName);
|
50
|
}
|
51
|
|
52
|
String idListSelect = " SELECT sr.uuid, sr.relatedfrom_id ";
|
53
|
String selectAll = " SELECT syn.DTYPE, (SELECT Max(id)+1 FROM TaxonBase), " +
|
54
|
" sr.created, sr.uuid, sr.updated, " +
|
55
|
" syn.lsid_authority, syn.lsid_lsid, syn.lsid_namespace, syn.lsid_object, syn.lsid_revision, " +
|
56
|
" syn.protectedtitlecache, syn.titleCache, syn.appendedphrase, " +
|
57
|
" syn.doubtful, syn.usenamecache, syn.taxonstatusunknown, " +
|
58
|
" sr.createdby_id,sr.updatedby_id, " +
|
59
|
" syn.name_id, syn.sec_id, syn.publish, syn.secMicroReference,"
|
60
|
+ "sr.partial, sr.proParte, sr.type_id, sr.relatedTo_id ";
|
61
|
String insert = " INSERT INTO @@TaxonBase@@ (DTYPE, id, created,uuid, updated,"
|
62
|
+ "lsid_authority,lsid_lsid,lsid_namespace,lsid_object,lsid_revision, protectedtitlecache,titleCache, appendedphrase,"
|
63
|
+ "doubtful, usenamecache,taxonstatusunknown,"
|
64
|
+ "createdby_id, updatedby_id, name_id, sec_id, publish, secMicroReference,"
|
65
|
+ "partial, proParte, type_id, acceptedTaxon_id)";
|
66
|
|
67
|
String fromSQL =
|
68
|
" FROM @@TaxonBase@@ syn INNER JOIN @@SynonymRelationship@@ sr ON sr.relatedfrom_id = syn.id ";
|
69
|
String whereSQL = " WHERE EXISTS ( " +
|
70
|
" SELECT * FROM @@SynonymRelationship@@ srFirst " +
|
71
|
" WHERE srFirst.id < sr.id " +
|
72
|
" AND srFirst.relatedfrom_id = sr.relatedfrom_id " +
|
73
|
" )";
|
74
|
|
75
|
@Override
|
76
|
public void invoke(ICdmDataSource datasource, IProgressMonitor monitor,
|
77
|
CaseType caseType, SchemaUpdateResult result) throws SQLException {
|
78
|
|
79
|
//id list of all synonym relationships that need the synonym to be duplicated
|
80
|
String listSql = caseType.replaceTableNames(idListSelect + fromSQL + whereSQL);
|
81
|
ResultSet rs = datasource.executeQuery(listSql);
|
82
|
while (rs.next()){
|
83
|
String uuid = "'" + rs.getString(1) +"'";
|
84
|
Integer oldSynonymId = rs.getInt(2);
|
85
|
|
86
|
//create new synonym
|
87
|
String update = insert + selectAll + fromSQL + " WHERE sr.uuid = " + uuid;
|
88
|
datasource.executeUpdate(caseType.replaceTableNames(update));
|
89
|
|
90
|
String sqlGetId = "SELECT id FROM @@TaxonBase@@ WHERE uuid = " + uuid;
|
91
|
Integer newSynonymId = (Integer)datasource.getSingleValue(caseType.replaceTableNames(sqlGetId));
|
92
|
|
93
|
//clone annotations
|
94
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "annotations_id", "Annotation", "text, linkbackUri, language_id, annotationtype_id, commentator_id", false);
|
95
|
//clone marker
|
96
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "markers_id", "Marker", "flag, markertype_id", false);
|
97
|
//clone credit
|
98
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "credits_id", "Credit", "text, abbreviatedtext, language_id, agent_id", true);
|
99
|
//clone extension
|
100
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "extensions_id", "Extension", "value, type_id", false);
|
101
|
//clone identifier
|
102
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "identifiers_id", "Identifier", "identifier, type_id", true);
|
103
|
//clone sources
|
104
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "sources_id", "OriginalSourceBase", "DTYPE, citationmicroreference, originalnamestring, idinsource, idnamespace, citation_id, nameusedinsource_id, sourceType", false);
|
105
|
//clone rightsInfo
|
106
|
cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "rights_id", "RightsInfo", "text, abbreviatedtext, uri, language_id, agent_id, type_id ", false);
|
107
|
|
108
|
//remove old relationship
|
109
|
String delete = "DELETE FROM @@SynonymRelationship@@ WHERE uuid = " + uuid;
|
110
|
datasource.executeUpdate(caseType.replaceTableNames(delete));
|
111
|
}
|
112
|
|
113
|
return;
|
114
|
}
|
115
|
|
116
|
private void cloneExtensions(Integer oldSynonymId, Integer newSynonymId, ICdmDataSource datasource, CaseType caseType, String mnCol, String tableName, String specificParams, boolean withSortIndex) throws SQLException {
|
117
|
String mnTableName = caseType.transformTo("TaxonBase_" + tableName);
|
118
|
String listSql = " SELECT @mnCol FROM @mnTable WHERE taxonBase_id = @oldSynonymId "
|
119
|
.replace("@mnCol", mnCol)
|
120
|
.replace("@mnTable", mnTableName)
|
121
|
.replace("@oldSynonymId", String.valueOf(oldSynonymId))
|
122
|
;
|
123
|
if (withSortIndex){
|
124
|
listSql += " ORDER BY sortIndex ";
|
125
|
}
|
126
|
ResultSet rs = datasource.executeQuery(listSql);
|
127
|
|
128
|
Integer sortIndex = 0;
|
129
|
while (rs.next()){
|
130
|
sortIndex++;
|
131
|
Integer oldExtensionId = rs.getInt(1);
|
132
|
cloneExtension(newSynonymId, oldExtensionId, mnTableName, mnCol, datasource, caseType, tableName, specificParams, withSortIndex ? sortIndex : null);
|
133
|
}
|
134
|
|
135
|
}
|
136
|
|
137
|
/**
|
138
|
* @param id
|
139
|
* @param caseType
|
140
|
* @throws SQLException
|
141
|
*/
|
142
|
private void cloneExtension(Integer newSynonymId, Integer oldExtensionId, String mnTableName, String mnCol, ICdmDataSource datasource, CaseType caseType,
|
143
|
String tableName, String specificParams, Integer sortIndex) throws SQLException {
|
144
|
|
145
|
//new id
|
146
|
String maxIdSql = "SELECT max(id) FROM @tableName"
|
147
|
.replace("@tableName", caseType.transformTo(tableName));
|
148
|
int newExtensionId = (Integer)datasource.getSingleValue(maxIdSql) + 1;
|
149
|
|
150
|
// insert clone record
|
151
|
String idParams = "id, uuid,";
|
152
|
String generalParams = "created, updated, createdBy_id, updatedBy_id,";
|
153
|
String allParams = idParams + generalParams + specificParams;
|
154
|
String idSelect = newExtensionId + ", '" + UUID.randomUUID() + "',";
|
155
|
String selectParams = idSelect + generalParams + specificParams;
|
156
|
String sql = "INSERT INTO @tableName (@allParams)"
|
157
|
+ " SELECT @selectParams FROM @tableName WHERE id = " + oldExtensionId;
|
158
|
sql = sql.replace("@selectParams", selectParams)
|
159
|
.replace("@allParams", allParams)
|
160
|
.replace("@tableName", caseType.transformTo(tableName))
|
161
|
;
|
162
|
datasource.executeUpdate(sql);
|
163
|
|
164
|
//insert MN
|
165
|
String sortIndexStr = "";
|
166
|
String sortIndexValueStr = "";
|
167
|
if (sortIndex != null){
|
168
|
sortIndexStr = ", sortIndex";
|
169
|
sortIndexValueStr = ", " + sortIndex;
|
170
|
}
|
171
|
String insertMNSql = ("INSERT INTO @mnTable (taxonbase_id, @mn_col @sortIndexInsert)"
|
172
|
+ "VALUES (@newSynonymId, @extensionId @sortIndexValue)")
|
173
|
.replace("@mnTable", mnTableName)
|
174
|
.replace("@mn_col", mnCol)
|
175
|
.replace("@newSynonymId", String.valueOf(newSynonymId))
|
176
|
.replace("@extensionId", String.valueOf(newExtensionId))
|
177
|
.replace("@sortIndexInsert", sortIndexStr)
|
178
|
.replace("@sortIndexValue", sortIndexValueStr)
|
179
|
;
|
180
|
datasource.executeUpdate(insertMNSql);
|
181
|
|
182
|
}
|
183
|
}
|