@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);
    }
  }
Esempio n. 2
0
  /**
   * 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();
      }
    }
  }