/** * 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); } } }
/** 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"); } } } }