Revision 7747019c
Added by Andreas Müller over 10 years ago
| cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/database/update/v25_30/PolytomousKeyDataMover.java | ||
|---|---|---|
| 17 | 17 |
|
| 18 | 18 |
import eu.etaxonomy.cdm.common.monitor.IProgressMonitor; |
| 19 | 19 |
import eu.etaxonomy.cdm.database.ICdmDataSource; |
| 20 |
import eu.etaxonomy.cdm.database.update.CaseType; |
|
| 20 | 21 |
import eu.etaxonomy.cdm.database.update.ISchemaUpdaterStep; |
| 21 | 22 |
import eu.etaxonomy.cdm.database.update.SchemaUpdaterStepBase; |
| 22 | 23 |
|
| ... | ... | |
| 49 | 50 |
|
| 50 | 51 |
|
| 51 | 52 |
@Override |
| 52 |
public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor) throws SQLException {
|
|
| 53 |
public Integer invoke(ICdmDataSource datasource, IProgressMonitor monitor, CaseType caseType) throws SQLException {
|
|
| 53 | 54 |
try {
|
| 54 | 55 |
boolean result = true; |
| 55 |
result &= movePolytomousKeys(featureTreeTableName, featureNodeTableName, polytomousKeyTableName, polytomousKeyNodeTableName, datasource, monitor, false);
|
|
| 56 |
result &= movePolytomousKeys(caseType.transformTo(featureTreeTableName), caseType.transformTo(featureNodeTableName), caseType.transformTo(polytomousKeyTableName), caseType.transformTo(polytomousKeyNodeTableName), datasource, monitor, false, caseType);
|
|
| 56 | 57 |
if (includeAudTable){
|
| 57 | 58 |
String aud = "_AUD"; |
| 58 |
result &= movePolytomousKeys(featureTreeTableName + aud, featureNodeTableName + aud, polytomousKeyTableName + aud, polytomousKeyNodeTableName + aud, datasource, monitor, true);
|
|
| 59 |
result &= movePolytomousKeys(caseType.transformTo(featureTreeTableName + aud), caseType.transformTo(featureNodeTableName + aud), caseType.transformTo(polytomousKeyTableName + aud), caseType.transformTo(polytomousKeyNodeTableName + aud), datasource, monitor, true, caseType);
|
|
| 59 | 60 |
} |
| 60 | 61 |
return (result == true )? 0 : null; |
| 61 | 62 |
} catch (Exception e) {
|
| ... | ... | |
| 65 | 66 |
} |
| 66 | 67 |
} |
| 67 | 68 |
|
| 68 |
private boolean movePolytomousKeys(String featureTreeTableName, String featureNodeTableName, String polytomousKeyTableName, String polytomousKeyNodeTableName, ICdmDataSource datasource, IProgressMonitor monitor, boolean isAudit) throws SQLException {
|
|
| 69 |
private boolean movePolytomousKeys(String featureTreeTableName, String featureNodeTableName, String polytomousKeyTableName, String polytomousKeyNodeTableName, ICdmDataSource datasource, IProgressMonitor monitor, boolean isAudit, CaseType caseType) throws SQLException {
|
|
| 69 | 70 |
boolean result = true; |
| 70 | 71 |
result &= movePolytomousKey(featureTreeTableName, polytomousKeyTableName, datasource, isAudit); |
| 71 |
result &= movePolytomousKeyMns(featureTreeTableName, polytomousKeyTableName, datasource, isAudit); |
|
| 72 |
result &= movePolytomousKeyMns(featureTreeTableName, polytomousKeyTableName, datasource, isAudit, caseType);
|
|
| 72 | 73 |
result &= movePolytomousKeyNodes(featureTreeTableName, featureNodeTableName, polytomousKeyNodeTableName, datasource, isAudit); |
| 73 | 74 |
|
| 74 |
result &= moveQuestions(featureNodeTableName, polytomousKeyNodeTableName, datasource, isAudit); |
|
| 75 |
result &= moveQuestions(featureNodeTableName, polytomousKeyNodeTableName, datasource, isAudit, caseType);
|
|
| 75 | 76 |
|
| 76 |
result &= deleteOldData(datasource, isAudit); |
|
| 77 |
result &= deleteOldData(datasource, isAudit, caseType);
|
|
| 77 | 78 |
return result; |
| 78 | 79 |
} |
| 79 | 80 |
|
| 80 | 81 |
|
| 81 |
private boolean moveQuestions(String featureNodeTableName, String polytomousKeyNodeTableName, ICdmDataSource datasource, boolean isAudit) throws SQLException {
|
|
| 82 |
private boolean moveQuestions(String featureNodeTableName, String polytomousKeyNodeTableName, ICdmDataSource datasource, boolean isAudit, CaseType caseType) throws SQLException {
|
|
| 82 | 83 |
boolean result = true; |
| 83 | 84 |
String aud = ""; |
| 84 | 85 |
String audValue = ""; |
| ... | ... | |
| 90 | 91 |
} |
| 91 | 92 |
//For each Question |
| 92 | 93 |
String questionSql = " SELECT * " + |
| 93 |
" FROM FeatureNode_Representation@_aud mn INNER JOIN Representation@_aud r ON mn.questions_id = r.id ";
|
|
| 94 |
" FROM @@FeatureNode_Representation@_aud@@ mn INNER JOIN Representation@_aud r ON mn.questions_id = r.id ";
|
|
| 94 | 95 |
questionSql = questionSql.replace("@_aud", aud);
|
| 95 |
ResultSet rs = datasource.executeQuery(questionSql);
|
|
| 96 |
ResultSet rs = datasource.executeQuery(caseType.replaceTableNames(questionSql));
|
|
| 96 | 97 |
while (rs.next()){
|
| 97 | 98 |
|
| 98 | 99 |
//Created KeyStatement |
| 99 |
String updateQuery = " INSERT INTO KeyStatement@_aud (id, uuid, created, updated, createdby_id, updatedby_id @audParam)" +
|
|
| 100 |
String updateQuery = " INSERT INTO @@KeyStatement@_aud@@ (id, uuid, created, updated, createdby_id, updatedby_id @audParam)" +
|
|
| 100 | 101 |
" VALUES (@id, @uuid, @createdWhen, @updatedWhen, @createdby_id, @updatedby_id @audValue)"; |
| 101 | 102 |
updateQuery = updateQuery.replace("@audValue", audValue);
|
| 102 | 103 |
updateQuery = updateQuery.replace("@id", rs.getObject("FeatureNode_id").toString()); //use feature node id for key statement id
|
| ... | ... | |
| 112 | 113 |
updateQuery = updateQuery.replace("@_aud", aud);
|
| 113 | 114 |
updateQuery = updateQuery.replace("@audParam", audParam);
|
| 114 | 115 |
try {
|
| 115 |
datasource.executeUpdate(updateQuery);
|
|
| 116 |
datasource.executeUpdate(caseType.replaceTableNames(updateQuery));
|
|
| 116 | 117 |
} catch (SQLException e) {
|
| 117 | 118 |
logger.error(e); |
| 118 | 119 |
result = false; |
| 119 | 120 |
} |
| 120 | 121 |
|
| 121 | 122 |
//create entry in Language String |
| 122 |
updateQuery = " INSERT INTO LanguageString@_aud (id, created, uuid, updated, text, createdby_id, updatedby_id, language_id @audParam) " +
|
|
| 123 |
updateQuery = " INSERT INTO @@LanguageString@_aud@@ (id, created, uuid, updated, text, createdby_id, updatedby_id, language_id @audParam) " +
|
|
| 123 | 124 |
" VALUES (@id, @createdWhen, @uuid, @updatedWhen, @text, @createdby_id, @updatedby_id, @language_id @audValue)"; |
| 124 | 125 |
updateQuery = updateQuery.replace("@audValue", audValue);
|
| 125 | 126 |
updateQuery = updateQuery.replace("@id", rs.getObject("id").toString());
|
| ... | ... | |
| 137 | 138 |
updateQuery = updateQuery.replace("@_aud", aud);
|
| 138 | 139 |
updateQuery = updateQuery.replace("@audParam", audParam);
|
| 139 | 140 |
try {
|
| 140 |
datasource.executeUpdate(updateQuery);
|
|
| 141 |
datasource.executeUpdate(caseType.replaceTableNames(updateQuery));
|
|
| 141 | 142 |
} catch (SQLException e) {
|
| 142 | 143 |
logger.error(e); |
| 143 | 144 |
result = false; |
| 144 | 145 |
} |
| 145 | 146 |
|
| 146 | 147 |
//create entry in KeyStatement_LanguageString |
| 147 |
updateQuery = " INSERT INTO KeyStatement_LanguageString@_aud (KeyStatement_id, label_id, label_mapkey_id @audParam) " +
|
|
| 148 |
updateQuery = " INSERT INTO @@KeyStatement_LanguageString@_aud@@ (KeyStatement_id, label_id, label_mapkey_id @audParam) " +
|
|
| 148 | 149 |
" VALUES (@keystatement_id, @languagestring_id, @language_id @audValue) "; |
| 149 | 150 |
updateQuery = updateQuery.replace("@audValue", audValue);
|
| 150 | 151 |
updateQuery = updateQuery.replace("@keystatement_id", nullSafe(rs.getObject("FeatureNode_id")));
|
| ... | ... | |
| 157 | 158 |
updateQuery = updateQuery.replace("@_aud", aud);
|
| 158 | 159 |
updateQuery = updateQuery.replace("@audParam", audParam);
|
| 159 | 160 |
try {
|
| 160 |
datasource.executeUpdate(updateQuery);
|
|
| 161 |
datasource.executeUpdate(caseType.replaceTableNames(updateQuery));
|
|
| 161 | 162 |
} catch (SQLException e) {
|
| 162 | 163 |
logger.error(e); |
| 163 | 164 |
result = false; |
| 164 | 165 |
} |
| 165 | 166 |
|
| 166 | 167 |
//link polytomouskeynode statement to KeyStatement |
| 167 |
updateQuery = " UPDATE PolytomousKeyNode@_aud " +
|
|
| 168 |
updateQuery = " UPDATE @@PolytomousKeyNode@_aud@@ " +
|
|
| 168 | 169 |
" SET statement_id = id " + |
| 169 | 170 |
" WHERE id = @id "; |
| 170 | 171 |
updateQuery = updateQuery.replace("@id", nullSafe(rs.getObject("FeatureNode_id")));
|
| 171 | 172 |
updateQuery = updateQuery.replace("@_aud", aud);
|
| 172 | 173 |
try {
|
| 173 |
datasource.executeUpdate(updateQuery);
|
|
| 174 |
datasource.executeUpdate(caseType.replaceTableNames(updateQuery));
|
|
| 174 | 175 |
} catch (SQLException e) {
|
| 175 | 176 |
logger.error(e); |
| 176 | 177 |
result = false; |
| ... | ... | |
| 257 | 258 |
} |
| 258 | 259 |
} |
| 259 | 260 |
|
| 260 |
private boolean deleteOldData(ICdmDataSource datasource, boolean isAudit) {
|
|
| 261 |
private boolean deleteOldData(ICdmDataSource datasource, boolean isAudit, CaseType caseType) {
|
|
| 261 | 262 |
boolean result = true; |
| 262 | 263 |
String updateQuery; |
| 263 |
String featureNodeTable = "FeatureNode" + (isAudit ? "_AUD" : "");
|
|
| 264 |
String featureTreeTable = "FeatureTree" + (isAudit ? "_AUD" : "");
|
|
| 265 |
String representationTable = "Representation" + (isAudit ? "_AUD" : "");
|
|
| 266 |
String oldMnTable = "FeatureNode_Representation" + (isAudit ? "_AUD" : "");
|
|
| 264 |
String featureNodeTable = caseType.transformTo("FeatureNode" + (isAudit ? "_AUD" : ""));
|
|
| 265 |
String featureTreeTable = caseType.transformTo("FeatureTree" + (isAudit ? "_AUD" : ""));
|
|
| 266 |
String representationTable = caseType.transformTo("Representation" + (isAudit ? "_AUD" : ""));
|
|
| 267 |
String oldMnTable = caseType.transformTo("FeatureNode_Representation" + (isAudit ? "_AUD" : ""));
|
|
| 267 | 268 |
|
| 268 | 269 |
// statements |
| 269 | 270 |
updateQuery = " DELETE FROM @representationTable WHERE id IN (SELECT questions_id FROM @oldMnTable)"; |
| ... | ... | |
| 335 | 336 |
return true; |
| 336 | 337 |
} |
| 337 | 338 |
|
| 338 |
private boolean movePolytomousKeyMns(String featureTreeTableName, String polytomousKeyTableName, ICdmDataSource datasource, boolean isAudit) {
|
|
| 339 |
private boolean movePolytomousKeyMns(String featureTreeTableName, String polytomousKeyTableName, ICdmDataSource datasource, boolean isAudit, CaseType caseType) {
|
|
| 339 | 340 |
//PolytomousKey MN update |
| 340 | 341 |
boolean result = true; |
| 341 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Annotation", null, datasource, isAudit, false); |
|
| 342 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Credit", null, datasource, isAudit, true); |
|
| 343 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Extension", null, datasource, isAudit, false); |
|
| 344 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Marker", null, datasource, isAudit, false); |
|
| 345 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "OriginalSourceBase", "Sources", datasource, isAudit, false); |
|
| 346 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Rights", "Rights", datasource, isAudit, false); |
|
| 342 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Annotation", null, datasource, isAudit, false, caseType);
|
|
| 343 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Credit", null, datasource, isAudit, true, caseType);
|
|
| 344 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Extension", null, datasource, isAudit, false, caseType);
|
|
| 345 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Marker", null, datasource, isAudit, false, caseType);
|
|
| 346 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "OriginalSourceBase", "Sources", datasource, isAudit, false, caseType);
|
|
| 347 |
result &= updateMnTables(featureTreeTableName, polytomousKeyTableName, "Rights", "Rights", datasource, isAudit, false, caseType);
|
|
| 347 | 348 |
return result; |
| 348 | 349 |
} |
| 349 | 350 |
|
| ... | ... | |
| 371 | 372 |
return true; |
| 372 | 373 |
} |
| 373 | 374 |
|
| 374 |
private boolean updateMnTables(String featureTreeTableName, String polytomousKeyTableName, String attributeName, String attributePluralString, ICdmDataSource datasource, boolean isAudit, boolean hasSortIndex) {
|
|
| 375 |
private boolean updateMnTables(String featureTreeTableName, String polytomousKeyTableName, String attributeName, String attributePluralString, ICdmDataSource datasource, boolean isAudit, boolean hasSortIndex, CaseType caseType) {
|
|
| 375 | 376 |
String updateQuery; |
| 376 | 377 |
String audit; |
| 377 | 378 |
if (isAudit){
|
| 378 |
featureTreeTableName = featureTreeTableName.replace("_AUD", "");
|
|
| 379 |
polytomousKeyTableName = polytomousKeyTableName.replace("_AUD", "");
|
|
| 379 |
featureTreeTableName = featureTreeTableName.replace(caseType.transformTo("_AUD"), "");
|
|
| 380 |
polytomousKeyTableName = polytomousKeyTableName.replace(caseType.transformTo("_AUD"), "");
|
|
| 380 | 381 |
} |
| 381 |
String newMnTable = polytomousKeyTableName + "_" + attributeName + (isAudit? "_AUD" : "");
|
|
| 382 |
String oldMnTable = featureTreeTableName + "_" + attributeName + (isAudit? "_AUD" : "");
|
|
| 382 |
String newMnTable = caseType.transformTo(polytomousKeyTableName + "_" + attributeName + (isAudit? "_AUD" : ""));
|
|
| 383 |
String oldMnTable = caseType.transformTo(featureTreeTableName + "_" + attributeName + (isAudit? "_AUD" : ""));
|
|
| 383 | 384 |
String pluralIdAttribute = ((attributePluralString == null) ? attributeName + "s" : attributePluralString) + "_id"; |
| 384 | 385 |
String sortIndex = (hasSortIndex ? ", sortIndex" : ""); |
| 385 | 386 |
updateQuery = " INSERT INTO @newMnTable(PolytomousKey_id, @pluralIdAttribute @sortIndex @audit) " + |
Also available in: Unified diff
add Case handling for update scripts #3682