@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);
}
}
/**
* Does the work of adding UUIDs to all rows.
*
* @see liquibase.change.custom.CustomTaskChange#execute(liquibase.database.Database)
*/
@Override
public void execute(Database database) throws CustomChangeException {
// if we're in a "generate sql file" mode, quit early
if (Context.getRuntimeProperties().size() == 0) return;
if (tableNamesArray == null || tableNamesArray.length == 0)
throw new CustomChangeException(
"At least one table name in the 'tableNames' parameter is required", null);
JdbcConnection connection = (JdbcConnection) database.getConnection();
// loop over all tables
for (String tableName : tableNamesArray) {
try {
Statement idStatement = null;
PreparedStatement updateStatement = null;
try {
String idSql = genericIdSql.replaceAll("tablename", tableName);
String updateSql = genericUpdateSql.replaceAll("tablename", tableName);
// hacky way to deal with tables that don't follow the tableNam_id convention
for (Map.Entry<String, String> idException : idExceptionsMap.entrySet()) {
idSql = idSql.replaceFirst(idException.getKey(), idException.getValue());
updateSql = updateSql.replaceFirst(idException.getKey(), idException.getValue());
}
idStatement = connection.createStatement();
updateStatement = connection.prepareStatement(updateSql);
// Map<Integer, UUID> uuids = new HashMap<Integer, UUID>();
ResultSet ids = idStatement.executeQuery(idSql);
while (ids.next()) {
updateStatement.setObject(2, ids.getObject(1)); // set the primary key number
updateStatement.setString(1, UUID.randomUUID().toString()); // set the uuid for this row
updateStatement.executeUpdate();
}
} finally {
if (idStatement != null) idStatement.close();
if (updateStatement != null) updateStatement.close();
}
} catch (DatabaseException e) {
throw new CustomChangeException("Unable to set uuid on table: " + tableName, e);
} catch (SQLException e) {
throw new CustomChangeException("Unable to set uuid on table: " + tableName, e);
}
}
}
@Override
public void execute(Database database) throws CustomChangeException {
// (Only Open Id for now)
if (dbAuthType.equals("OPENID")) {
JdbcConnection conn = (JdbcConnection) database.getConnection();
try {
Statement stmt = conn.createStatement();
PreparedStatement insertStmt =
conn.prepareStatement(
"insert into HCredentials "
+ "(account_id, type, user, email, creationDate, lastChanged, versionNum) values"
+ "(?, ?, ?, ?, ?, ?, ?)");
ResultSet rset =
stmt.executeQuery(
"select acc.id, acc.username, p.email, acc.creationDate, acc.lastChanged "
+ " from HAccount acc, HPerson p"
+ " where p.accountId = acc.id");
while (rset.next()) {
insertStmt.setLong(1, rset.getLong("id"));
insertStmt.setString(2, dbAuthType);
if (dbAuthType.equals("OPENID")) {
insertStmt.setString(
3, "http://" + rset.getString("username") + ".id.fedoraproject.org/");
} else {
insertStmt.setString(3, rset.getString("username"));
}
insertStmt.setString(4, rset.getString("email"));
insertStmt.setDate(5, rset.getDate("creationDate"));
insertStmt.setDate(6, rset.getDate("lastChanged"));
insertStmt.setLong(7, 0);
insertStmt.executeUpdate();
}
} catch (DatabaseException e) {
throw new CustomChangeException(e);
} catch (SQLException e) {
throw new CustomChangeException(e);
}
}
}
@Override
public void execute(Database database) throws CustomChangeException {
JdbcConnection databaseConnection = (JdbcConnection) database.getConnection();
try {
PreparedStatement getPreferences =
databaseConnection.prepareStatement("SELECT ID FROM PREFERENCES");
ResultSet res = getPreferences.executeQuery();
while (res.next()) {
String uuid = UUID.randomUUID().toString();
PreparedStatement updatePreferences =
databaseConnection.prepareStatement(
"UPDATE PREFERENCES SET UUID = '" + uuid + "' WHERE ID = " + res.getObject("ID"));
updatePreferences.execute();
updatePreferences.close();
PreparedStatement updateUsers =
databaseConnection.prepareStatement(
"UPDATE USERS SET PREFERENCES_UUID = '"
+ uuid
+ "' WHERE PREFERENCES_ID = "
+ res.getObject("ID"));
updateUsers.execute();
updateUsers.close();
PreparedStatement updateFilescanner =
databaseConnection.prepareStatement(
"UPDATE FILE_SCANNER_PREFERENCES SET PREFERENCE_UUID = '"
+ uuid
+ "' WHERE PREFERENCE_ID = "
+ res.getObject("ID"));
updateFilescanner.execute();
updateFilescanner.close();
PreparedStatement updateSearches =
databaseConnection.prepareStatement(
"UPDATE SEARCH_PREFERENCES SET PREFERENCE_UUID = '"
+ uuid
+ "' WHERE PREFERENCE_ID = "
+ res.getObject("ID"));
updateSearches.execute();
updateSearches.close();
}
getPreferences.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void migrateFrequenciesToCodedValue(
JdbcConnection connection, Set<String> uniqueFrequencies)
throws CustomChangeException, SQLException, DatabaseException {
PreparedStatement updateDrugOrderStatement = null;
Boolean autoCommit = null;
try {
autoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
updateDrugOrderStatement =
connection.prepareStatement(
"update drug_order set frequency = ? where frequency_text = ?");
for (String frequency : uniqueFrequencies) {
Integer conceptIdForFrequency = UpgradeUtil.getConceptIdForUnits(frequency);
if (conceptIdForFrequency == null) {
throw new CustomChangeException("No concept mapping found for frequency: " + frequency);
}
Integer orderFrequencyId =
UpgradeUtil.getOrderFrequencyIdForConceptId(
connection.getUnderlyingConnection(), conceptIdForFrequency);
if (orderFrequencyId == null) {
throw new CustomChangeException(
"No order frequency found for concept " + conceptIdForFrequency);
}
updateDrugOrderStatement.setInt(1, orderFrequencyId);
updateDrugOrderStatement.setString(2, frequency);
updateDrugOrderStatement.executeUpdate();
updateDrugOrderStatement.clearParameters();
}
connection.commit();
} catch (DatabaseException e) {
handleError(connection, e);
} catch (SQLException e) {
handleError(connection, e);
} finally {
if (autoCommit != null) {
connection.setAutoCommit(autoCommit);
}
if (updateDrugOrderStatement != null) {
updateDrugOrderStatement.close();
}
}
}
private void handleError(JdbcConnection connection, Exception e)
throws DatabaseException, CustomChangeException {
connection.rollback();
throw new CustomChangeException(e);
}
/** 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");
}
}
}
}
private void insertUniqueFrequencies(JdbcConnection connection, Set<String> uniqueFrequencies)
throws CustomChangeException, SQLException, DatabaseException {
PreparedStatement insertOrderFrequencyStatement = null;
Boolean autoCommit = null;
try {
autoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
insertOrderFrequencyStatement =
connection.prepareStatement(
"insert into order_frequency "
+ "(concept_id, creator, date_created, retired, uuid) values (?, ?, ?, ?, ?)");
Date date = new Date(new java.util.Date().getTime());
for (String frequency : uniqueFrequencies) {
if (StringUtils.isBlank(frequency)) {
continue;
}
Integer conceptIdForFrequency = UpgradeUtil.getConceptIdForUnits(frequency);
if (conceptIdForFrequency == null) {
throw new CustomChangeException("No concept mapping found for frequency: " + frequency);
}
Integer orderFrequencyId =
UpgradeUtil.getOrderFrequencyIdForConceptId(
connection.getUnderlyingConnection(), conceptIdForFrequency);
if (orderFrequencyId != null) {
// a single concept is mapped to more than one text or there is an order frequency already
continue;
}
// Generating UUID for order frequency. Generated UUIDs will be the same if concepts UUIDs
// are the same.
String uuid =
UpgradeUtil.getConceptUuid(connection.getUnderlyingConnection(), conceptIdForFrequency);
uuid += "-6925ebb0-7c69-11e3-baa7-0800200c9a66"; // Adding random value for order frequency
uuid = UUID.nameUUIDFromBytes(uuid.getBytes()).toString();
insertOrderFrequencyStatement.setInt(1, conceptIdForFrequency);
insertOrderFrequencyStatement.setInt(2, 1);
insertOrderFrequencyStatement.setDate(3, date);
insertOrderFrequencyStatement.setBoolean(4, false);
insertOrderFrequencyStatement.setString(5, uuid);
insertOrderFrequencyStatement.executeUpdate();
insertOrderFrequencyStatement.clearParameters();
}
connection.commit();
} catch (DatabaseException e) {
handleError(connection, e);
} catch (SQLException e) {
handleError(connection, e);
} finally {
if (autoCommit != null) {
connection.setAutoCommit(autoCommit);
}
if (insertOrderFrequencyStatement != null) {
insertOrderFrequencyStatement.close();
}
}
}