private final String sortIndexColumn;\r
private final String parentColumn;\r
private String idColumn = "id";\r
+ private String currentSortColumn = "id";\r
private final boolean includeAudTable;\r
private Integer baseValue = 0;\r
\r
public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){\r
- return new SortIndexUpdater(stepName, tableName, parentColumn, sortIndexColumn, "id", includeAudTable, 0);\r
+ return new SortIndexUpdater(stepName, tableName, parentColumn, sortIndexColumn, "id", "id", includeAudTable, 0);\r
}\r
\r
public static final SortIndexUpdater NewInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable){\r
- return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, idColumn, includeAudTable, 0);\r
+ return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, idColumn, idColumn, includeAudTable, 0);\r
}\r
\r
+ /**\r
+ * Returns an SortIndexUpdater that updates an existing sortindex which might have missing sortindex numbers in between.\r
+ *\r
+ */\r
+ public static final SortIndexUpdater NewUpdateExistingSortindexInstance(String stepName, String tableName, String parentColumn, String sortIndexColumn, boolean includeAudTable){\r
+ return new SortIndexUpdater(stepName, tableName, parentColumn,sortIndexColumn, "id", sortIndexColumn, includeAudTable, 0);\r
+ }\r
\r
- protected SortIndexUpdater(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, boolean includeAudTable, Integer baseValue) {\r
+\r
+ protected SortIndexUpdater(String stepName, String tableName, String parentColumn, String sortIndexColumn, String idColumn, String currentSortColumn, boolean includeAudTable, Integer baseValue) {\r
super(stepName);\r
this.tableName = tableName;\r
this.parentColumn = parentColumn;\r
this.sortIndexColumn = sortIndexColumn;\r
this.idColumn = idColumn;\r
+ this.currentSortColumn = currentSortColumn;\r
this.includeAudTable = includeAudTable;\r
this.baseValue = baseValue;\r
}\r
String resulsetQuery = "SELECT @id as id, @parentColumn " +\r
" FROM @tableName " +\r
" WHERE @parentColumn IS NOT NULL " +\r
- " ORDER BY @parentColumn, @id";\r
+ " ORDER BY @parentColumn, @sorted";\r
resulsetQuery = resulsetQuery.replace("@tableName", tableName);\r
resulsetQuery = resulsetQuery.replace("@parentColumn", parentColumn);\r
- resulsetQuery = resulsetQuery.replace("@id", idColumn);\r
+ resulsetQuery = resulsetQuery.replace("@sorted", currentSortColumn);\r
\r
ResultSet rs = datasource.executeQuery(resulsetQuery);\r
Integer index = baseValue;\r
tableName = "FeatureNode";
String parentIdColumn = "parent_id";
String sortIndexColumn = "sortIndex";
- SortIndexUpdater updateSortIndex = SortIndexUpdater.NewInstance(stepName, tableName, parentIdColumn, sortIndexColumn, INCLUDE_AUDIT);
+ SortIndexUpdater updateSortIndex = SortIndexUpdater.NewUpdateExistingSortindexInstance(stepName, tableName, parentIdColumn, sortIndexColumn, INCLUDE_AUDIT);
stepList.add(updateSortIndex);
//#5976
tableName = "TaxonNode";
parentIdColumn = "parent_id";
sortIndexColumn = "sortIndex";
- updateSortIndex = SortIndexUpdater.NewInstance(
+ updateSortIndex = SortIndexUpdater.NewUpdateExistingSortindexInstance(
stepName, tableName, parentIdColumn, sortIndexColumn,
INCLUDE_AUDIT);
stepList.add(updateSortIndex);
tableName = "PolytomousKeyNode";
parentIdColumn = "parent_id";
sortIndexColumn = "sortIndex";
- updateSortIndex = SortIndexUpdater.NewInstance(
+ updateSortIndex = SortIndexUpdater.NewUpdateExistingSortindexInstance(
stepName, tableName, parentIdColumn, sortIndexColumn,
INCLUDE_AUDIT);
stepList.add(updateSortIndex);
//move data
//move duplicates first
- step = SynonymDeduplicator.NewInstance();
+ step = SynonymDeduplicator.NewInstance();
stepList.add(step);
//update pro parte
import java.sql.ResultSet;
import java.sql.SQLException;
+import java.util.UUID;
import org.apache.log4j.Logger;
super(stepName);
}
- String idListSelect = " SELECT sr.uuid ";
+ String idListSelect = " SELECT sr.uuid, sr.relatedfrom_id ";
String selectAll = " SELECT syn.DTYPE, (SELECT Max(id)+1 FROM TaxonBase), " +
" sr.created, sr.uuid, sr.updated, " +
" syn.lsid_authority, syn.lsid_lsid, syn.lsid_namespace, syn.lsid_object, syn.lsid_revision, " +
@Override
public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {
+ //id list of all synonym relationships that need the synonym to be duplicated
String listSql = caseType.replaceTableNames(idListSelect + fromSQL + whereSQL);
ResultSet rs = datasource.executeQuery(listSql);
while (rs.next()){
String uuid = "'" + rs.getString(1) +"'";
+ Integer oldSynonymId = rs.getInt(2);
+
//create new synonym
String update = insert + selectAll + fromSQL + " WHERE sr.uuid = " + uuid;
datasource.executeUpdate(caseType.replaceTableNames(update));
+
+ String sqlGetId = "SELECT id FROM @@TaxonBase@@ WHERE uuid = " + uuid;
+ Integer newSynonymId = (Integer)datasource.getSingleValue(caseType.replaceTableNames(sqlGetId));
+
+ //clone annotations
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "annotations_id", "Annotation", "text, linkbackUri, language_id, annotationtype_id, commentator_id", false);
+ //clone marker
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "markers_id", "Marker", "flag, markertype_id", false);
+ //clone credit
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "credits_id", "Credit", "text, abbreviatedtext, language_id, agent_id", true);
+ //clone extension
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "extensions_id", "Extension", "value, type_id", false);
+ //clone identifier
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "identifiers_id", "Identifier", "identifier, type_id", true);
+ //clone sources
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "sources_id", "OriginalSourceBase", "DTYPE, citationmicroreference, originalnamestring, idinsource, idnamespace, citation_id, nameusedinsource_id, sourceType", false);
+ //clone rightsInfo
+ cloneExtensions(oldSynonymId, newSynonymId, datasource, caseType, "rights_id", "RightsInfo", "text, abbreviatedtext, uri, language_id, agent_id, type_id ", false);
+
//remove old relationship
String delete = "DELETE FROM @@SynonymRelationship@@ WHERE uuid = " + uuid;
datasource.executeUpdate(caseType.replaceTableNames(delete));
return 0;
}
+
+ private void cloneExtensions(Integer oldSynonymId, Integer newSynonymId, ICdmDataSource datasource, CaseType caseType, String mnCol, String tableName, String specificParams, boolean withSortIndex) throws SQLException {
+ String mnTableName = caseType.transformTo("TaxonBase_" + tableName);
+ String listSql = " SELECT @mnCol FROM @mnTable WHERE taxonBase_id = @oldSynonymId "
+ .replace("@mnCol", mnCol)
+ .replace("@mnTable", mnTableName)
+ .replace("@oldSynonymId", String.valueOf(oldSynonymId))
+ ;
+ if (withSortIndex){
+ listSql += " ORDER BY sortIndex ";
+ }
+ ResultSet rs = datasource.executeQuery(listSql);
+
+ Integer sortIndex = 0;
+ while (rs.next()){
+ sortIndex++;
+ Integer oldExtensionId = rs.getInt(1);
+ cloneExtension(newSynonymId, oldExtensionId, mnTableName, mnCol, datasource, caseType, tableName, specificParams, withSortIndex ? sortIndex : null);
+ }
+
+ }
+
+ /**
+ * @param id
+ * @param caseType
+ * @throws SQLException
+ */
+ private void cloneExtension(Integer newSynonymId, Integer oldExtensionId, String mnTableName, String mnCol, ICdmDataSource datasource, CaseType caseType,
+ String tableName, String specificParams, Integer sortIndex) throws SQLException {
+
+ try {
+ //new id
+ String maxIdSql = "SELECT max(id) FROM @tableName"
+ .replace("@tableName", caseType.transformTo(tableName));
+ int newExtensionId = (Integer)datasource.getSingleValue(maxIdSql) + 1;
+
+// insert clone record
+ String idParams = "id, uuid,";
+ String generalParams = "created, updated, createdBy_id, updatedBy_id,";
+ String allParams = idParams + generalParams + specificParams;
+ String idSelect = newExtensionId + ", '" + UUID.randomUUID() + "',";
+ String selectParams = idSelect + generalParams + specificParams;
+ String sql = "INSERT INTO @tableName (@allParams)"
+ + " SELECT @selectParams FROM @tableName WHERE id = " + oldExtensionId;
+ sql = sql.replace("@selectParams", selectParams)
+ .replace("@allParams", allParams)
+ .replace("@tableName", caseType.transformTo(tableName))
+ ;
+ datasource.executeUpdate(sql);
+
+ //insert MN
+ String sortIndexStr = "";
+ String sortIndexValueStr = "";
+ if (sortIndex != null){
+ sortIndexStr = ", sortIndex";
+ sortIndexValueStr = ", " + sortIndex;
+ }
+ String insertMNSql = ("INSERT INTO @mnTable (taxonbase_id, @mn_col @sortIndexInsert)"
+ + "VALUES (@newSynonymId, @extensionId @sortIndexValue)")
+ .replace("@mnTable", mnTableName)
+ .replace("@mn_col", mnCol)
+ .replace("@newSynonymId", String.valueOf(newSynonymId))
+ .replace("@extensionId", String.valueOf(newExtensionId))
+ .replace("@sortIndexInsert", sortIndexStr)
+ .replace("@sortIndexValue", sortIndexValueStr)
+ ;
+ datasource.executeUpdate(insertMNSql);
+ } catch (Exception e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ logger.error("Synonym extension could not be cloned");
+ throw e;
+ }
+
+ }
}
BGBM BDI Projects / cdmlib.git / commitdiff