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;
	}
	
}