MigrateConceptReferenceTermChangeSet.java
/**
* The contents of this file are subject to the OpenMRS Public License
* Version 1.0 (the "License"); you may not use this file except in
* compliance with the License. You may obtain a copy of the License at
* http://license.openmrs.org
*
* Software distributed under the License is distributed on an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific language governing rights and limitations
* under the License.
*
* Copyright (C) OpenMRS, LLC. All Rights Reserved.
*/
package org.openmrs.util.databasechange;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import liquibase.change.custom.CustomTaskChange;
import liquibase.database.Database;
import liquibase.database.jvm.JdbcConnection;
import liquibase.exception.CustomChangeException;
import liquibase.exception.DatabaseException;
import liquibase.exception.SetupException;
import liquibase.exception.ValidationErrors;
import liquibase.resource.ResourceAccessor;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* Creates concept reference terms from existing rows in the concept_reference_map table.
* <p>
* The terms are created only for a unique source and code.
*/
public class MigrateConceptReferenceTermChangeSet implements CustomTaskChange {
protected final Log log = LogFactory.getLog(getClass());
public static final String DEFAULT_CONCEPT_MAP_TYPE = "NARROWER-THAN";
/**
* @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database)
*/
@Override
public void execute(Database database) throws CustomChangeException {
final JdbcConnection connection = (JdbcConnection) database.getConnection();
Boolean prevAutoCommit = null;
PreparedStatement selectTypes = null;
PreparedStatement batchUpdateMap = null;
PreparedStatement selectMap = null;
PreparedStatement updateMapTerm = null;
PreparedStatement insertTerm = null;
PreparedStatement updateMapType = null;
try {
prevAutoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
//Prepare a list of types and their ids.
Map<String, Integer> typesToIds = new HashMap<String, Integer>();
selectTypes = connection.prepareStatement("select * from concept_map_type");
selectTypes.execute();
ResultSet selectTypeResult = selectTypes.getResultSet();
while (selectTypeResult.next()) {
typesToIds.put(selectTypeResult.getString("name").trim().toUpperCase(), selectTypeResult
.getInt("concept_map_type_id"));
}
selectTypes.close();
//The FK on concept_reference_term_id is not yet created so we are safe to copy over IDs.
//The trims are done to be able to compare properly.
batchUpdateMap = connection.prepareStatement("update concept_reference_map set"
+ " concept_reference_term_id = concept_map_id,"
+ " source_code = trim(source_code), comment = trim(comment)");
batchUpdateMap.execute();
batchUpdateMap.close();
//Preparing statements for use in the loop.
updateMapTerm = connection.prepareStatement("update concept_reference_map set"
+ " concept_reference_term_id = ? where concept_map_id = ?");
insertTerm = connection.prepareStatement("insert into concept_reference_term"
+ " (concept_reference_term_id, uuid, concept_source_id, code, creator, date_created, description)"
+ " values (?, ?, ?, ?, ?, ?, ?)");
updateMapType = connection.prepareStatement("update concept_reference_map set"
+ " concept_map_type_id = ? where concept_map_id = ?");
int prevSource = -1;
String prevSourceCode = null;
String prevComment = null;
int prevInsertedTerm = -1;
//In addition to source and source_code we order by UUID to always insert the same term if run on different systems.
selectMap = connection.prepareStatement("select * from concept_reference_map"
+ " order by source, source_code, uuid");
selectMap.execute();
final ResultSet selectMapResult = selectMap.getResultSet();
while (selectMapResult.next()) {
final int conceptMapId = selectMapResult.getInt("concept_map_id");
final int source = selectMapResult.getInt("source");
final String sourceCode = selectMapResult.getString("source_code");
final String comment = selectMapResult.getString("comment");
final int creator = selectMapResult.getInt("creator");
final Date dateCreated = selectMapResult.getDate("date_created");
final String uuid = selectMapResult.getString("uuid");
final Integer mapTypeId = determineMapTypeId(comment, typesToIds);
final int updatedMapTypeId = (mapTypeId == null) ? typesToIds.get(DEFAULT_CONCEPT_MAP_TYPE) : mapTypeId;
updateMapType.setInt(1, updatedMapTypeId);
updateMapType.setInt(2, conceptMapId);
updateMapType.execute();
if (updateMapType.getUpdateCount() != 1) {
throw new CustomChangeException("Failed to set map type: " + mapTypeId + " for map: " + conceptMapId
+ ", updated rows: " + updateMapType.getUpdateCount());
}
if (source == prevSource
&& (sourceCode == prevSourceCode || (sourceCode != null && sourceCode.equals(prevSourceCode)))) {
if (mapTypeId == null && comment != null && !comment.equals(prevComment)) {
log.warn("Lost comment '" + comment + "' for map " + conceptMapId + ". Preserved comment "
+ prevComment);
}
//We need to use the last inserted term.
updateMapTerm.setInt(1, prevInsertedTerm);
updateMapTerm.setInt(2, conceptMapId);
updateMapTerm.execute();
if (updateMapTerm.getUpdateCount() != 1) {
throw new CustomChangeException("Failed to set reference term: " + prevInsertedTerm + " for map: "
+ conceptMapId + ", updated rows: " + updateMapTerm.getUpdateCount());
}
} else {
insertTerm.setInt(1, conceptMapId);
//We need to guaranty that UUIDs are always the same when run on different systems.
insertTerm.setString(2, UUID.nameUUIDFromBytes(uuid.getBytes()).toString());
insertTerm.setInt(3, source);
insertTerm.setString(4, sourceCode);
insertTerm.setInt(5, creator);
insertTerm.setDate(6, dateCreated);
if (mapTypeId == null) {
insertTerm.setString(7, comment);
} else {
insertTerm.setString(7, null);
}
insertTerm.execute();
prevInsertedTerm = conceptMapId;
}
prevSource = source;
prevSourceCode = sourceCode;
prevComment = comment;
}
selectMap.close();
updateMapType.close();
updateMapTerm.close();
insertTerm.close();
connection.commit();
}
catch (Exception e) {
try {
if (connection != null) {
connection.rollback();
}
}
catch (Exception ex) {
log.error("Failed to rollback", ex);
}
throw new CustomChangeException(e);
}
finally {
closeStatementQuietly(selectTypes);
closeStatementQuietly(batchUpdateMap);
closeStatementQuietly(selectMap);
closeStatementQuietly(updateMapTerm);
closeStatementQuietly(insertTerm);
closeStatementQuietly(updateMapType);
if (connection != null && prevAutoCommit != null) {
try {
connection.setAutoCommit(prevAutoCommit);
}
catch (DatabaseException e) {
log.error("Failed to reset auto commit", e);
}
}
}
}
/**
* Closes the statement quietly.
*
* @param statement
*/
private void closeStatementQuietly(PreparedStatement statement) {
if (statement != null) {
try {
statement.close();
}
catch (SQLException e) {
log.error("Failed to close statement", e);
}
}
}
/**
* Determines the map type based on the given comment.
*
* @param comment
* @param typesToIds
* @return map type id or null if not recognized
*/
protected Integer determineMapTypeId(String comment, Map<String, Integer> typesToIds) {
Integer mapTypeId = null;
if (!StringUtils.isBlank(comment)) {
comment = comment.toUpperCase();
if (comment.startsWith("MAP TYPE:")) {
comment = comment.substring(9).trim();
if (comment.equals("SAME-AS FROM RXNORM")) {
comment = "SAME-AS";
}
mapTypeId = typesToIds.get(comment);
}
}
return mapTypeId;
}
/**
* @see liquibase.change.custom.CustomChange#getConfirmationMessage()
*/
@Override
public String getConfirmationMessage() {
return "Finished migrating concept reference terms";
}
/**
* @see liquibase.change.custom.CustomChange#setUp()
*/
@Override
public void setUp() throws SetupException {
}
/**
* @see liquibase.change.custom.CustomChange#setFileOpener(liquibase.resource.ResourceAccessor)
*/
@Override
public void setFileOpener(ResourceAccessor resourceAccessor) {
}
/**
* @see liquibase.change.custom.CustomChange#validate(liquibase.database.Database)
*/
@Override
public ValidationErrors validate(Database database) {
return null;
}
}