/**
* 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import liquibase.FileOpener;
import liquibase.change.custom.CustomTaskChange;
import liquibase.database.Database;
import liquibase.database.DatabaseConnection;
import liquibase.exception.CustomChangeException;
import liquibase.exception.InvalidChangeDefinitionException;
import liquibase.exception.SetupException;
import liquibase.exception.UnsupportedChangeException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.openmrs.util.OpenmrsConstants;
/**
* Adds yes/no boolean concepts and changes all boolean obs values to match these concepts
*/
public class BooleanConceptChangeSet implements CustomTaskChange {
private static Log log = LogFactory.getLog(BooleanConceptChangeSet.class);
private Integer trueConceptId;
private Integer falseConceptId;
//string values for boolean concepts
private static Map<String, String[]> trueNames = new HashMap<String, String[]>();
private static Map<String, String[]> falseNames = new HashMap<String, String[]>();
// how to say True and Yes in OpenMRS core languages
static {
// names may not include spaces, or else the logic to create concept words will break
trueNames.put("en", new String[] { "True", "Yes" });
trueNames.put("fr", new String[] { "Vrai", "Oui" });
trueNames.put("es", new String[] { "Verdadero", "Sí" });
trueNames.put("it", new String[] { "Vero", "Sì" });
trueNames.put("pt", new String[] { "Verdadeiro", "Sim" });
falseNames.put("en", new String[] { "False", "No" });
falseNames.put("fr", new String[] { "Faux", "Non" });
falseNames.put("es", new String[] { "Falso", "No" });
falseNames.put("it", new String[] { "Falso", "No" });
falseNames.put("pt", new String[] { "Falso", "Não" });
}
/**
* @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database)
*/
public void execute(Database database) throws CustomChangeException, UnsupportedChangeException {
DatabaseConnection connection = database.getConnection();
// try to find existing concepts with the right names
trueConceptId = findConceptByName(connection, trueNames);
falseConceptId = findConceptByName(connection, falseNames);
// if they don't exist, create them
if (trueConceptId == null)
trueConceptId = createConcept(connection, trueNames);
if (falseConceptId == null)
falseConceptId = createConcept(connection, falseNames);
// create the global properties
final boolean trueFalseGlobalPropertiesPresent = getInt(connection,
"SELECT COUNT(*) FROM global_property WHERE property IN ('" + OpenmrsConstants.GLOBAL_PROPERTY_TRUE_CONCEPT
+ "', '" + OpenmrsConstants.GLOBAL_PROPERTY_FALSE_CONCEPT + "')") == 2;
if (!trueFalseGlobalPropertiesPresent)
createGlobalProperties(connection, trueConceptId, falseConceptId);
// now change all the existing obs
changeObs(connection);
}
/**
* Finds a concept that has any of the the given names in the given locale. If you have a
* concept named 'True' in 'en_US' and you search for 'True' in 'en' this will be returned.
*
* @param connection
* @param names a Map from (2-letter) locale to all possible names in that locale
* @return a concept id.
* @throws CustomChangeException
*/
private Integer findConceptByName(DatabaseConnection connection, Map<String, String[]> names)
throws CustomChangeException {
for (Map.Entry<String, String[]> e : names.entrySet()) {
String locale = e.getKey();
for (String name : e.getValue()) {
Integer ret = getInt(connection, "select concept_id from concept_name where name = '" + name
+ "' and locale like '" + locale + "%'");
if (ret != null)
return ret;
}
}
return null;
}
/**
* creates a concept
*
* @param connection a DatabaseConnection
* @param names a Map from locale to names in that locale, which will be added to the new
* concept
* @throws CustomChangeException
*/
private Integer createConcept(DatabaseConnection connection, Map<String, String[]> names) throws CustomChangeException {
PreparedStatement updateStatement = null;
try {
int conceptId = getInt(connection, "SELECT MAX(concept_id) FROM concept");
conceptId++;
updateStatement = connection
.prepareStatement("INSERT INTO concept (concept_id, short_name, description, datatype_id, class_id, is_set, creator, date_created, uuid) VALUES (?, '', '', 4, 11, 0, 1, NOW(), ?)");
updateStatement.setInt(1, conceptId);
updateStatement.setString(2, UUID.randomUUID().toString());
updateStatement.executeUpdate();
boolean preferredDoneAlready = false; // only tag one name as preferred
int conceptNameId = getInt(connection, "SELECT MAX(concept_name_id) FROM concept_name");
for (Map.Entry<String, String[]> e : names.entrySet()) {
String locale = e.getKey();
for (String name : e.getValue()) {
conceptNameId++;
updateStatement = connection
.prepareStatement("INSERT INTO concept_name (concept_name_id, concept_id, locale, name, creator, date_created, uuid) VALUES (?, ?, ?, ?, 1, NOW(), ?)");
updateStatement.setInt(1, conceptNameId);
updateStatement.setInt(2, conceptId);
updateStatement.setString(3, locale);
updateStatement.setString(4, name);
updateStatement.setString(5, UUID.randomUUID().toString());
updateStatement.executeUpdate();
// Tag the first english name as preferred. This is ugly, but it's not feasible to
// fix this before refactoring concept_name_tags.
if (!preferredDoneAlready && "en".equals(locale)) {
updateStatement = connection
.prepareStatement("INSERT INTO concept_name_tag_map (concept_name_id, concept_name_tag_id) VALUES (?, 4)");
updateStatement.setInt(1, conceptNameId);
updateStatement.executeUpdate();
preferredDoneAlready = true;
}
updateStatement = connection
.prepareStatement("INSERT INTO concept_word (concept_id, word, locale, concept_name_id) VALUES (?, ?, ?, ?)");
updateStatement.setInt(1, conceptId);
updateStatement.setString(2, name);
updateStatement.setString(3, locale);
updateStatement.setInt(4, conceptNameId);
updateStatement.executeUpdate();
}
}
return conceptId;
}
catch (SQLException e) {
throw new CustomChangeException("Unable to create concept with names " + names, e);
}
finally {
if (updateStatement != null) {
try {
updateStatement.close();
}
catch (SQLException e) {}
}
}
}
/**
* changes all obs which have boolean values to the new (coded) representation of boolean
* values.
*
* @param connection a DatabaseConnection
* @param trueConceptName the concept name for boolean true values
* @param falseConceptName the concept name for boolean false values
* @throws CustomChangeException
*/
private void changeObs(DatabaseConnection connection) throws CustomChangeException {
PreparedStatement updateStatement = null;
try {
/* replace value_numerical boolean values by coded boolean values */
updateStatement = connection
.prepareStatement("UPDATE obs SET value_coded = ?, value_numeric = NULL WHERE value_numeric != 0 AND concept_id IN (SELECT concept_id FROM concept WHERE datatype_id = 10)");
updateStatement.setInt(1, trueConceptId);
updateStatement.executeUpdate();
updateStatement = connection
.prepareStatement("UPDATE obs SET value_coded = ?, value_numeric = NULL WHERE value_numeric = 0 AND concept_id IN (SELECT concept_id FROM concept WHERE datatype_id = 10)");
updateStatement.setInt(1, falseConceptId);
updateStatement.executeUpdate();
}
catch (SQLException e) {
throw new CustomChangeException("Unable to change obs", e);
}
finally {
if (updateStatement != null) {
try {
updateStatement.close();
}
catch (SQLException e) {}
}
}
}
/**
* Inserts global properties 'Concept.true' and 'Concept.false' into the global_property table
*
* @param connection a DatabaseConnection
* @param trueConceptId the concept id for true boolean concept
* @param falseConceptId the concept id for false boolean concept
* @throws CustomChangeException
*/
private void createGlobalProperties(DatabaseConnection connection, Integer trueConceptId, Integer falseConceptId)
throws CustomChangeException {
if (trueConceptId == null || trueConceptId < 1 || falseConceptId == null || falseConceptId < 1)
throw new CustomChangeException("Can't create global properties for true/false concepts with invalid conceptIds");
PreparedStatement updateStatement = null;
try {
updateStatement = connection
.prepareStatement("INSERT INTO global_property (property, property_value, description, uuid) VALUES (?, ?, ?, ?)");
updateStatement.setString(1, OpenmrsConstants.GLOBAL_PROPERTY_TRUE_CONCEPT);
updateStatement.setInt(2, trueConceptId);
updateStatement.setString(3, "Concept id of the concept defining the TRUE boolean concept");
updateStatement.setString(4, UUID.randomUUID().toString());
updateStatement.executeUpdate();
updateStatement.setString(1, OpenmrsConstants.GLOBAL_PROPERTY_FALSE_CONCEPT);
updateStatement.setInt(2, falseConceptId);
updateStatement.setString(3, "Concept id of the concept defining the FALSE boolean concept");
updateStatement.setString(4, UUID.randomUUID().toString());
updateStatement.executeUpdate();
}
catch (SQLException e) {
throw new CustomChangeException("Unable to create global properties for concept ids defining boolean concepts",
e);
}
finally {
if (updateStatement != null) {
try {
updateStatement.close();
}
catch (SQLException e) {}
}
}
}
/**
* returns an integer resulting from the execution of an sql statement
*
* @param connection a DatabaseConnection
* @param sql the sql statement to execute
* @return integer resulting from the execution of the sql statement
* @throws CustomChangeException
*/
private Integer getInt(DatabaseConnection connection, String sql) throws CustomChangeException {
Statement stmt = null;
try {
stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
Integer result = null;
if (rs.next()) {
result = rs.getInt(1);
} else {
// this is okay, we just return null in this case
log.debug("Query returned no results: " + sql);
}
if (rs.next()) {
log.warn("Query returned multiple results when we expected just one: " + sql);
}
return result;
}
catch (SQLException e) {
throw new CustomChangeException("Unable to get int", e);
}
finally {
if (stmt != null) {
try {
stmt.close();
}
catch (SQLException e) {}
}
}
}
/**
* @see liquibase.change.custom.CustomChange#getConfirmationMessage()
*/
public String getConfirmationMessage() {
return "Finished creating boolean concepts";
}
/**
* @see liquibase.change.custom.CustomChange#setFileOpener(liquibase.FileOpener)
*/
public void setFileOpener(FileOpener fileOpener) {
}
/**
* @see liquibase.change.custom.CustomChange#setUp()
*/
public void setUp() throws SetupException {
}
/**
* @see liquibase.change.custom.CustomChange#validate(liquibase.database.Database)
*/
public void validate(Database database) throws InvalidChangeDefinitionException {
}
}