/** @see org.openmrs.api.db.AdministrationDAO#executeSQL(java.lang.String, boolean) */
public List<List<Object>> executeSQL(String sql, boolean selectOnly) throws DAOException {
// (solution for junit tests that usually use hsql
// hsql does not like the backtick. Replace the backtick with the hsql
// escape character: the double quote (or nothing).
if (HibernateUtil.isHSQLDialect(sessionFactory)) {
sql = sql.replace("`", "");
}
return DatabaseUtil.executeSQL(
sessionFactory.getCurrentSession().connection(), sql, selectOnly);
}
@Override
public void execute(Database database) throws CustomChangeException {
JdbcConnection connection = (JdbcConnection) database.getConnection();
try {
Set<String> uniqueFrequencies =
DatabaseUtil.getUniqueNonNullColumnValues(
"frequency_text", "drug_order", String.class, connection.getUnderlyingConnection());
migrateFrequenciesToCodedValue(connection, uniqueFrequencies);
} catch (Exception e) {
throw new CustomChangeException(e);
}
}
private static Connection connect(String url) throws SQLException {
// Step 1: Load the JDBC driver.
try {
DatabaseUtil.loadDatabaseDriver(url);
} catch (ClassNotFoundException e) {
log.error("Could not find JDBC driver class.", e);
throw (SQLException) e.fillInStackTrace();
}
// Step 2: Establish the connection to the database.
String username = Context.getRuntimeProperties().getProperty("connection.username");
String password = Context.getRuntimeProperties().getProperty("connection.password");
log.debug(
"connecting to DATABASE: "
+ OpenmrsConstants.DATABASE_NAME
+ " USERNAME: "******" URL: "
+ url);
return DriverManager.getConnection(url, username, password);
}
/** Method to perform validation and resolution of duplicate EncounterType names */
@Override
public void execute(Database database) throws CustomChangeException {
JdbcConnection connection = (JdbcConnection) database.getConnection();
Map<String, HashSet<Integer>> duplicates = new HashMap<String, HashSet<Integer>>();
Statement stmt = null;
PreparedStatement pStmt = null;
ResultSet rs = null;
try {
// set auto commit mode to false for UPDATE action
connection.setAutoCommit(false);
stmt = connection.createStatement();
rs =
stmt.executeQuery(
"SELECT * FROM encounter_type INNER JOIN (SELECT name FROM encounter_type GROUP BY name HAVING count(name) > 1) dup ON encounter_type.name = dup.name");
Integer id = null;
String name = null;
while (rs.next()) {
id = rs.getInt("encounter_type_id");
name = rs.getString("name");
if (duplicates.get(name) == null) {
HashSet<Integer> results = new HashSet<Integer>();
results.add(id);
duplicates.put(name, results);
} else {
HashSet<Integer> results = duplicates.get(name);
results.add(id);
}
}
Iterator it2 = duplicates.entrySet().iterator();
while (it2.hasNext()) {
Map.Entry pairs = (Map.Entry) it2.next();
HashSet values = (HashSet) pairs.getValue();
List<Integer> editableNames = new ArrayList<Integer>(values);
int duplicateNameId = 1;
for (int i = 1; i < editableNames.size(); i++) {
String newName = pairs.getKey() + "_" + duplicateNameId;
List<List<Object>> duplicateResult = null;
boolean duplicateName = false;
Connection con = DatabaseUpdater.getConnection();
do {
String sqlValidatorString =
"select * from encounter_type where name = '" + newName + "'";
duplicateResult = DatabaseUtil.executeSQL(con, sqlValidatorString, true);
if (!duplicateResult.isEmpty()) {
duplicateNameId += 1;
newName = pairs.getKey() + "_" + duplicateNameId;
duplicateName = true;
} else {
duplicateName = false;
}
} while (duplicateName);
pStmt =
connection.prepareStatement(
"update encounter_type set name = ? where encounter_type_id = ?");
pStmt.setString(1, newName);
pStmt.setInt(2, editableNames.get(i));
duplicateNameId += 1;
pStmt.executeUpdate();
}
}
} catch (BatchUpdateException e) {
log.warn("Error generated while processsing batch insert", e);
try {
log.debug("Rolling back batch", e);
connection.rollback();
} catch (Exception rbe) {
log.warn("Error generated while rolling back batch insert", e);
}
// marks the changeset as a failed one
throw new CustomChangeException(
"Failed to update one or more suplicate EncounterType names", e);
} catch (DatabaseException e) {
throw new CustomChangeException(
"Error while updating duplicate EncounterType object names", e);
} catch (SQLException e) {
throw new CustomChangeException(
"Error while updating duplicate EncounterType object names", e);
} catch (DAOException e) {
throw new CustomChangeException("Error accessing database connection", e);
} catch (Exception e) {
throw new CustomChangeException("Error accessing database connection", e);
} finally {
// reset to auto commit mode
try {
connection.setAutoCommit(true);
} catch (DatabaseException e) {
log.warn("Failed to reset auto commit back to true", e);
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
log.warn("Failed to close the resultset object");
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
log.warn(
"Failed to close the select statement used to identify duplicate EncounterType object names");
}
}
if (pStmt != null) {
try {
pStmt.close();
} catch (SQLException e) {
log.warn(
"Failed to close the prepared statement used to update duplicate EncounterType object names");
}
}
}
}
/** @see CustomTaskChange#execute(Database) */
@Override
public void execute(Database database) throws CustomChangeException {
Connection conn = ((JdbcConnection) database.getConnection()).getUnderlyingConnection();
List<String> messages = new ArrayList<String>();
// Warn if any states are configured as both initial and terminal
StringBuilder message = new StringBuilder();
message.append(
"Starting now, when you transition a patient into a state that is configured as terminal, ");
message.append("then that whole program enrollment will be marked as completed.<br/>");
message.append("Please check that programs, workflows, and states are configured.<br/>");
message.append("This check will highlight two things: ");
message.append("<ul><li>states that are marked as both initial and terminal ");
message.append(
"(if you start someone in that state their program enrollment will be instantly closed)</li>");
message.append(
"<li>workflows that have no initial states (because you don't have a state to start people in)</li>");
message.append("</ul><br/>");
message.append("The following states are configured as both initial and terminal:<br/>");
StringBuilder query = new StringBuilder();
query.append(" select s.concept_id, min(n.name) as name ");
query.append(" from program_workflow_state s, concept_name n ");
query.append(" where s.concept_id = n.concept_id and initial = '1' and terminal = '1' ");
query.append(" group by s.concept_id ");
List<List<Object>> results = DatabaseUtil.executeSQL(conn, query.toString(), true);
if (results.isEmpty()) {
message.append("None found.");
} else {
for (List<Object> row : results) {
message.append(row.get(1).toString()).append("<br/>");
}
}
// Warn if any workflows have no initial states
message.append("<br/>The following workflows have no initial states...<br/>");
query = new StringBuilder();
query.append(" select w.concept_id, s.initial, count(*) as num ");
query.append(" from program_workflow w, program_workflow_state s ");
query.append(" where w.program_workflow_id = s.program_workflow_id ");
query.append(" group by w.concept_id, s.initial ");
results = DatabaseUtil.executeSQL(conn, query.toString(), true);
List<Integer> missingInitial = new ArrayList<Integer>();
for (List<Object> row : results) {
missingInitial.add(Integer.valueOf(row.get(0).toString()));
}
for (List<Object> row : results) {
Integer conceptId = Integer.valueOf(row.get(0).toString());
boolean isInitial = row.get(1).toString().equals("1");
int num = Integer.parseInt(row.get(2).toString());
if (isInitial && num > 0) {
missingInitial.remove(conceptId);
}
}
if (missingInitial.isEmpty()) {
message.append("None found.");
} else {
for (Integer conceptId : missingInitial) {
String sql = "select min(name) from concept_name where concept_id = " + conceptId;
String name = DatabaseUtil.executeSQL(conn, sql, true).get(0).get(0).toString();
message.append(name).append("<br/>");
}
}
messages.add(message.toString());
DatabaseUpdater.reportUpdateWarnings(messages);
}