liquibase.exception.CustomChangeException Java Examples

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String offlineUserSessionsTableName = database.correctObjectName("OFFLINE_USER_SESSION", Table.class);

    try {
        int currentTime = Time.currentTime();

        UpdateStatement updateStatement = new UpdateStatement(null, null, offlineUserSessionsTableName)
                .addNewColumnValue("LAST_SESSION_REFRESH", currentTime);

        statements.add(updateStatement);

        confirmationMessage.append("Updated column LAST_SESSION_REFRESH in OFFLINE_USER_SESSION table with time " + currentTime);
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    realmTableName = database.correctObjectName("REALM", Table.class);

    try {
        convertSocialToIdFedRealms();
        convertSocialToIdFedUsers();
        addAccessCodeLoginTimeout();
        addNewAdminRoles();
        addDefaultProtocolMappers();
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

protected boolean isApplicable() throws CustomChangeException {
    try {
        String correctedTableName = database.correctObjectName("REALM", Table.class);
        if (SnapshotGeneratorFactory.getInstance().has(new Table().setName(correctedTableName), database)) {
            try (Statement st = connection.createStatement(); ResultSet resultSet = st.executeQuery("SELECT ID FROM " + getTableName(correctedTableName))) {
                return (resultSet.next());
            }
        } else {
            return false;
        }
    } catch (Exception e) {
        throw new CustomChangeException("Failed to check database availability", e);
    }
}
 

@Override
public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
    this.database = database;
    jdbcConnection = (JdbcConnection) database.getConnection();
    connection = jdbcConnection.getWrappedConnection();

    if (isApplicable()) {
        confirmationMessage.append(getTaskId() + ": ");
        generateStatementsImpl();
    } else {
        confirmationMessage.append(getTaskId() + ": no update applicable for this task");
    }

    return statements.toArray(new SqlStatement[statements.size()]);
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String realmClientTableName = database.correctObjectName("REALM_CLIENT", Table.class);

    try {
        String trueValue = DataTypeFactory.getInstance().getTrueBooleanValue(database);
        PreparedStatement statement = jdbcConnection.prepareStatement("select CLIENT.REALM_ID, CLIENT.ID CLIENT_ID from " + getTableName("CLIENT") + " CLIENT where CLIENT.CONSENT_REQUIRED = " + trueValue);

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String realmId = resultSet.getString("REALM_ID");
                    String oauthClientId = resultSet.getString("CLIENT_ID");

                    InsertStatement realmClientInsert = new InsertStatement(null, null, realmClientTableName)
                            .addColumnValue("REALM_ID", realmId)
                            .addColumnValue("CLIENT_ID", oauthClientId);
                    statements.add(realmClientInsert);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Inserted " + statements.size() + " OAuth Clients to REALM_CLIENT table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    List<ProviderFactory> factories = kcSession.getKeycloakSessionFactory().getProviderFactories(UserStorageProvider.class);
    for (ProviderFactory factory : factories) {
        if (!factory.getId().equals(LDAPConstants.LDAP_PROVIDER)) {
            convertFedProviderToComponent(factory.getId(), null);
        }
    }
}
 

@Override
public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
    List<SqlStatement> statements = new ArrayList<>();

    Connection conn = ((JdbcConnection) (database.getConnection())).getWrappedConnection();

    try {
        conn.setAutoCommit(false);
        ResultSet resultSet = conn.createStatement().executeQuery("SELECT id, certificate from ios_variant");
        while (resultSet.next()) {
            String id = resultSet.getString("id");
            Blob blob = resultSet.getBlob("certificate");
            InputStream certificate = blob.getBinaryStream();
            ByteArrayOutputStream stream = new ByteArrayOutputStream();
            int bytesRead = -1;
            byte[] buffer = new byte[1024];
            while ((bytesRead = certificate.read(buffer)) != -1) {
                stream.write(buffer, 0, bytesRead);
            }
            final String certificateData = Base64.getEncoder().encodeToString(stream.toByteArray());

            UpdateStatement updateStatement = new UpdateStatement(null, null, "ios_variant")
                    .addNewColumnValue("cert_data", certificateData)
                    .setWhereClause("id='" + id + "'");
            statements.add(updateStatement);

        }
        conn.commit();

        if (!statements.isEmpty()) {
            confirmationMessage = "updated certificate data successfully";
        }

        return statements.toArray(new SqlStatement[statements.size()]);
    } catch (Exception e) {
        throw new CustomChangeException("Failed to migrate certificate data");
    }

}
 

@Override
public void execute(Database database) throws CustomChangeException {
    Connection conn = ((JdbcConnection) (database.getConnection())).getWrappedConnection();
    try {
        conn.setAutoCommit(false);
        List<InstallationData> list = new ArrayList<>();
        String query = "select installation.id as installation_id," +
                " installation.variant_id as installation_variant_id," +
                " variant.id as variant_id," +
                " variant.api_key as variant_api_key" +
                " from installation join variant on installation.variant_id = variant.api_key";
        PreparedStatement statement = conn.prepareStatement(query);
        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            String installationId = rs.getString("installation_id");
            String installationVariantId = rs.getString("installation_variant_id");
            String variantId = rs.getString("variant_id");
            String variantApiKey = rs.getString("variant_api_key");
            list.add(new InstallationData(installationId,variantId));
        }
        String update = "update installation" +
                " set variant_id = ?" +
                " where id = ?";
        PreparedStatement updateInstallationsStatement = conn.prepareStatement(update);
        for (InstallationData data: list) {
            updateInstallationsStatement.setString(1, data.variantId);
            updateInstallationsStatement.setString(2, data.installationId);
            updateInstallationsStatement.executeUpdate();
        }

        conn.commit();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select ID,URI from " + getTableName("RESOURCE_SERVER_RESOURCE") + " where URI is not null");

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String resourceId = resultSet.getString(1);
                    String resourceUri = resultSet.getString(2);

                    InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("RESOURCE_URIS", Table.class))
                            .addColumnValue("RESOURCE_ID", resourceId)
                            .addColumnValue("VALUE", resourceUri);

                    statements.add(insertComponent);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Moved " + statements.size() + " records from RESOURCE_SERVER_RESOURCE to RESOURCE_URIS table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String userAttributeTableName = database.correctObjectName("USER_ATTRIBUTE", Table.class);

    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select NAME, USER_ID from " + getTableName("USER_ATTRIBUTE"));

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String name = resultSet.getString(1);
                    String userId = resultSet.getString(2);

                    UpdateStatement updateStatement = new UpdateStatement(null, null, userAttributeTableName)
                            .addNewColumnValue("ID", KeycloakModelUtils.generateId())
                            .setWhereClause("NAME='" + name + "' AND USER_ID='" + userId + "'");
                    statements.add(updateStatement);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " attributes in USER_ATTRIBUTE table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String clientTableName = database.correctObjectName("CLIENT", Table.class);
    String clientScopeClientTableName = database.correctObjectName("CLIENT_SCOPE_CLIENT", Table.class);

    try (PreparedStatement statement = jdbcConnection.prepareStatement("SELECT ID, CLIENT_TEMPLATE_ID FROM " + clientTableName);
      ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            String clientId = rs.getString(1);
            String clientTemplateId = rs.getString(2);

            if (clientId == null || clientId.trim().isEmpty()) {
                continue;
            }
            if (clientTemplateId == null || clientTemplateId.trim().isEmpty()) {
                continue;
            }

            statements.add(
              new InsertStatement(null, null, clientScopeClientTableName)
                .addColumnValue("CLIENT_ID", clientId.trim())
                .addColumnValue("SCOPE_ID", clientTemplateId.trim())
                .addColumnValue("DEFAULT_SCOPE", Boolean.TRUE)
            );
        }

        confirmationMessage.append("Updated " + statements.size() + " records in CLIENT_SCOPE_CLIENT table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    String userFederationProviderTableName = database.correctObjectName("USER_FEDERATION_PROVIDER", Table.class);

    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select REALM_ID, USERFEDERATIONPROVIDERS_ID from " + getTableName("FED_PROVIDERS"));

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String realmId = resultSet.getString(1);
                    String userFederationProviderId = resultSet.getString(2);

                    UpdateStatement updateStatement = new UpdateStatement(null, null, userFederationProviderTableName)
                            .addNewColumnValue("REALM_ID", realmId)
                            .setWhereClause("ID='" + userFederationProviderId + "'");
                    statements.add(updateStatement);
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_PROVIDER table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getRestrictions =
         databaseConnection.prepareStatement ("SELECT ID FROM ACCESS_RESTRICTION");
      ResultSet res = getRestrictions.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateRestrictions =
            databaseConnection
               .prepareStatement ("UPDATE ACCESS_RESTRICTION SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateRestrictions.execute ();
         updateRestrictions.close ();

         PreparedStatement updateUserRestricitons =
            databaseConnection
               .prepareStatement ("UPDATE USER_RESTRICTIONS SET RESTRICTION_UUID = '" +
                  uuid + "' WHERE RESTRICTION_ID = " + res.getObject ("ID"));
         updateUserRestricitons.execute ();
         updateUserRestricitons.close ();
      }
      getRestrictions.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

@Override
public SqlStatement[] generateStatements(Database database) throws CustomChangeException {
    try {
        StringBuilder sb = new StringBuilder();
        sb.append("Generated codeSecret for realms: ");

        Connection connection = ((JdbcConnection) (database.getConnection())).getWrappedConnection();
        ArrayList<SqlStatement> statements = new ArrayList<SqlStatement>();

        String correctedTableName = database.correctObjectName("REALM", Table.class);
        String correctedSchemaName = database.escapeObjectName(database.getDefaultSchemaName(), Schema.class);

        if (SnapshotGeneratorFactory.getInstance().has(new Table().setName(correctedTableName), database)) {
            try (Statement st = connection.createStatement(); ResultSet resultSet = st.executeQuery("SELECT ID FROM " + LiquibaseJpaUpdaterProvider.getTable(correctedTableName, correctedSchemaName) + " WHERE CODE_SECRET IS NULL")) {
                while (resultSet.next()) {
                    String id = resultSet.getString(1);
                    
                    UpdateStatement statement = new UpdateStatement(null, null, correctedTableName)
                            .addNewColumnValue("CODE_SECRET", KeycloakModelUtils.generateCodeSecret())
                            .setWhereClause("ID=?").addWhereParameters(id);
                    statements.add(statement);
                    
                    if (!resultSet.isFirst()) {
                        sb.append(", ");
                    }
                    sb.append(id);
                }

                if (!statements.isEmpty()) {
                    confirmationMessage = sb.toString();
                }
            }
        }

        return statements.toArray(new SqlStatement[statements.size()]);
    } catch (Exception e) {
        throw new CustomChangeException("Failed to add realm code secret", e);
    }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getCarts =
         databaseConnection.prepareStatement ("SELECT ID FROM PRODUCTCARTS");
      ResultSet res = getCarts.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateCart =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTCARTS SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateCart.execute ();
         updateCart.close ();

         PreparedStatement updateProductsLink =
            databaseConnection
               .prepareStatement ("UPDATE CART_PRODUCTS SET CART_UUID = '" +
                  uuid + "' WHERE CART_ID = " + res.getObject ("ID"));
         updateProductsLink.execute ();
         updateProductsLink.close ();
      }
      getCarts.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getOwners =
         databaseConnection
            .prepareStatement ("SELECT o.USER_ID, p.ID FROM OWNER o, " +
                  "PRODUCTS p WHERE o.OWNEROFPRODUCT = p.IDENTIFIER");
      ResultSet res = getOwners.executeQuery ();

      while (res.next ())
      {
         Long productIdentifier = (Long) res.getObject ("ID");   
         Long userIdentifier = (Long) res.getObject ("USER_ID");  
         PreparedStatement updateOwner =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTS SET OWNER_ID = " +
                     userIdentifier+" WHERE ID = "+productIdentifier);
         updateOwner.execute ();
         updateOwner.close();
      }
      getOwners.close ();
   }
   catch (Exception e)
   {
      LOGGER.error("Error during liquibase update 'MoveOwnerInProduct'", e);
   }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getEvictions =
         databaseConnection
            .prepareStatement ("SELECT ID,STRATEGY FROM EVICTION");
      ResultSet res = getEvictions.executeQuery ();
      
      while (res.next ())
      {
         Integer strategy = (Integer) res.getObject ("STRATEGY");
         if (strategy == 2)
         {
            // 2 (old LFU) -> 0 (None)
            strategy = 0;
         }
         if (strategy == 3)
         {
            // 3 (old FIFO) -> 2 (new FIFO)
            strategy = 2;
         }
         PreparedStatement updateStrategy =
            databaseConnection
               .prepareStatement ("UPDATE EVICTION SET STRATEGY = '" +
                     strategy+"' WHERE ID = "+res.getObject ("ID"));
         updateStrategy.execute ();
         updateStrategy.close ();
      }
      getEvictions.close ();         
   }
   catch (Exception e)
   {
      LOGGER.error("Error during liquibase update 'removeLFUStrategy'", e);
   }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    convertFedProviderToComponent(LDAPConstants.LDAP_PROVIDER, "org.keycloak.storage.ldap.mappers.LDAPStorageMapper");
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select ID, PRIVATE_KEY, CERTIFICATE from " + getTableName("REALM"));

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String realmId = resultSet.getString(1);
                    String privateKeyPem = resultSet.getString(2);
                    String certificatePem = resultSet.getString(3);

                    String componentId = KeycloakModelUtils.generateId();

                    InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("COMPONENT", Table.class))
                            .addColumnValue("ID", componentId)
                            .addColumnValue("REALM_ID", realmId)
                            .addColumnValue("PARENT_ID", realmId)
                            .addColumnValue("NAME", "rsa")
                            .addColumnValue("PROVIDER_ID", "rsa")
                            .addColumnValue("PROVIDER_TYPE", KeyProvider.class.getName());

                    statements.add(insertComponent);

                    statements.add(componentConfigStatement(componentId, "priority", "100"));
                    statements.add(componentConfigStatement(componentId, "privateKey", privateKeyPem));
                    statements.add(componentConfigStatement(componentId, "certificate", certificatePem));
                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_PROVIDER table");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    Set<String> clientSessionIdsToDelete = new HashSet<>();

    String tableName = getTableName("OFFLINE_CLIENT_SESSION");
    String colClientSessionId = database.correctObjectName("CLIENT_SESSION_ID", Column.class);

    try (PreparedStatement ps = connection.prepareStatement(String.format(
        "SELECT t.CLIENT_SESSION_ID, t.USER_SESSION_ID, t.CLIENT_ID, t.OFFLINE_FLAG" +
        "  FROM %1$s t," +
        "    (SELECT USER_SESSION_ID, CLIENT_ID, OFFLINE_FLAG" +
        "      FROM %1$s" +
        "    GROUP BY USER_SESSION_ID, CLIENT_ID, OFFLINE_FLAG" +
        "    HAVING COUNT(*) > 1) t1" +
        "  WHERE t.USER_SESSION_ID = t1.USER_SESSION_ID" +
        "    AND t.CLIENT_ID = t1.CLIENT_ID" +
        "    AND t.OFFLINE_FLAG = t1.OFFLINE_FLAG" +
        "  ORDER BY t.USER_SESSION_ID, t.CLIENT_ID, t.OFFLINE_FLAG", tableName));

        ResultSet resultSet = ps.executeQuery()
      ) {
        // Find out all offending duplicates, keep first row only
        Key origKey = new Key(null, null, null);
        while (resultSet.next()) {
            String clientSessionId = resultSet.getString(1);
            Key key = new Key(resultSet.getString(2), resultSet.getString(3), resultSet.getString(4));

            if (key.equals(origKey)) {
                clientSessionIdsToDelete.add(clientSessionId);
            } else {
                origKey = key;
            }
        }
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }

    AtomicInteger ai = new AtomicInteger();
    clientSessionIdsToDelete.stream()
      .collect(Collectors.groupingByConcurrent(id -> ai.getAndIncrement() / 20, Collectors.toList())) // Split into chunks of at most 20 items

      .values().stream()
      .map(ids -> new DeleteStatement(null, null, "OFFLINE_CLIENT_SESSION")
        .setWhere(":name IN (" + ids.stream().map(id -> "?").collect(Collectors.joining(",")) + ")")
        .addWhereColumnName(colClientSessionId)
        .addWhereParameters(ids.toArray())
      )
      .forEach(statements::add);
}
 

protected void convertFedProviderToComponent(String providerId, String newMapperType) throws CustomChangeException {
    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select ID, REALM_ID, PRIORITY, DISPLAY_NAME, FULL_SYNC_PERIOD, CHANGED_SYNC_PERIOD, LAST_SYNC from " + getTableName("USER_FEDERATION_PROVIDER") + " WHERE PROVIDER_NAME=?");
        statement.setString(1, providerId);

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    int index = 1;
                    String id = resultSet.getString(index++);
                    String realmId = resultSet.getString(index++);
                    int priority = resultSet.getInt(index++);
                    String displayName = resultSet.getString(index++);
                    int fullSyncPeriod = resultSet.getInt(index++);
                    int changedSyncPeriod = resultSet.getInt(index++);
                    int lastSync = resultSet.getInt(index++);


                    InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("COMPONENT", Table.class))
                            .addColumnValue("ID", id)
                            .addColumnValue("REALM_ID", realmId)
                            .addColumnValue("PARENT_ID", realmId)
                            .addColumnValue("NAME", displayName)
                            .addColumnValue("PROVIDER_ID", providerId)
                            .addColumnValue("PROVIDER_TYPE", UserStorageProvider.class.getName());

                    statements.add(insertComponent);

                    statements.add(componentConfigStatement(id, "priority", Integer.toString(priority)));
                    statements.add(componentConfigStatement(id, "fullSyncPeriod", Integer.toString(fullSyncPeriod)));
                    statements.add(componentConfigStatement(id, "changedSyncPeriod", Integer.toString(changedSyncPeriod)));
                    statements.add(componentConfigStatement(id, "lastSync", Integer.toString(lastSync)));
                    PreparedStatement configStatement = jdbcConnection.prepareStatement("select name, VALUE from " + getTableName("USER_FEDERATION_CONFIG") + " WHERE USER_FEDERATION_PROVIDER_ID=?");
                    configStatement.setString(1, id);
                    try {
                        ResultSet configSet = configStatement.executeQuery();
                        try {
                            while (configSet.next()) {
                                String name = configSet.getString(1);
                                String value = configSet.getString(2);
                                //logger.info("adding component config: " + name + ": " + value);
                                statements.add(componentConfigStatement(id, name, value));
                            }
                        } finally {
                            configSet.close();
                        }
                    } finally {
                        configStatement.close();
                    }

                    if (newMapperType != null) {
                        convertFedMapperToComponent(realmId, id, newMapperType);
                    }

                    DeleteStatement configDelete = new DeleteStatement(null, null, database.correctObjectName("USER_FEDERATION_CONFIG", Table.class));
                    configDelete.setWhere("USER_FEDERATION_PROVIDER_ID=?");
                    configDelete.addWhereParameters(id);

                    statements.add(configDelete);
                    DeleteStatement deleteStatement = new DeleteStatement(null, null, database.correctObjectName("USER_FEDERATION_PROVIDER", Table.class));
                    deleteStatement.setWhere("ID=?");
                    deleteStatement.addWhereParameters(id);
                    statements.add(deleteStatement);

                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_PROVIDER table for " + providerId + " conversion to component model");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

protected void convertFedMapperToComponent(String realmId, String parentId, String newMapperType) throws CustomChangeException {
    try {
        PreparedStatement statement = jdbcConnection.prepareStatement("select ID, NAME, FEDERATION_MAPPER_TYPE from " + getTableName("USER_FEDERATION_MAPPER") + " WHERE FEDERATION_PROVIDER_ID=?");
        statement.setString(1, parentId);

        try {
            ResultSet resultSet = statement.executeQuery();
            try {
                while (resultSet.next()) {
                    String id = resultSet.getString(1);
                    String mapperName = resultSet.getString(2);
                    String fedMapperType = resultSet.getString(3);

                    InsertStatement insertComponent = new InsertStatement(null, null, database.correctObjectName("COMPONENT", Table.class))
                            .addColumnValue("ID", id)
                            .addColumnValue("REALM_ID", realmId)
                            .addColumnValue("PARENT_ID", parentId)
                            .addColumnValue("NAME", mapperName)
                            .addColumnValue("PROVIDER_ID", fedMapperType)
                            .addColumnValue("PROVIDER_TYPE", newMapperType);

                    statements.add(insertComponent);



                    PreparedStatement configStatement = jdbcConnection.prepareStatement("select name, VALUE from " + getTableName("USER_FEDERATION_MAPPER_CONFIG") + " WHERE USER_FEDERATION_MAPPER_ID=?");
                    configStatement.setString(1, id);
                    try {
                        ResultSet configSet = configStatement.executeQuery();
                        try {
                            while (configSet.next()) {
                                String name = configSet.getString(1);
                                String value = configSet.getString(2);
                                statements.add(componentConfigStatement(id, name, value));
                            }
                        } finally {
                            configSet.close();
                        }
                    } finally {
                        configStatement.close();
                    }
                    DeleteStatement configDelete = new DeleteStatement(null, null, database.correctObjectName("USER_FEDERATION_MAPPER_CONFIG", Table.class));
                    configDelete.setWhere("USER_FEDERATION_MAPPER_ID=?");
                    configDelete.addWhereParameters(id);
                    statements.add(configDelete);
                    DeleteStatement deleteStatement = new DeleteStatement(null, null, database.correctObjectName("USER_FEDERATION_MAPPER", Table.class));
                    deleteStatement.setWhere("ID=?");
                    deleteStatement.addWhereParameters(id);
                    statements.add(deleteStatement);


                }
            } finally {
                resultSet.close();
            }
        } finally {
            statement.close();
        }

        confirmationMessage.append("Updated " + statements.size() + " records in USER_FEDERATION_MAPPER table for " + parentId + " conversion to component model");
    } catch (Exception e) {
        throw new CustomChangeException(getTaskId() + ": Exception when updating data from previous version", e);
    }
}
 

@Override
protected void generateStatementsImpl() throws CustomChangeException {
    statements.add(generateUpdateStatement("RESOURCE_SERVER_POLICY"));
    statements.add(generateUpdateStatement("RESOURCE_SERVER_RESOURCE"));
    statements.add(generateUpdateStatement("RESOURCE_SERVER_SCOPE"));
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getUsers =
         databaseConnection.prepareStatement ("SELECT ID FROM USERS");
      ResultSet res = getUsers.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateUsers =
            databaseConnection
               .prepareStatement ("UPDATE USERS SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateUsers.execute ();
         updateUsers.close ();
                     
         PreparedStatement updateNetworkUsage =
            databaseConnection
               .prepareStatement ("UPDATE NETWORK_USAGE SET USER_UUID = '" + uuid +
                  "' WHERE USER_ID = " + res.getObject ("ID"));
         updateNetworkUsage.execute ();
         updateNetworkUsage.close ();

         PreparedStatement updateProducts =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTS SET OWNER_UUID = '" +
                  uuid + "' WHERE OWNER_ID = " + res.getObject ("ID"));
         updateProducts.execute ();
         updateProducts.close ();

         PreparedStatement updateCollectionuser =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTION_USER_AUTH SET USERS_UUID = '" +
                  uuid + "' WHERE USERS_ID = " + res.getObject ("ID"));
         updateCollectionuser.execute ();
         updateCollectionuser.close ();
         
         PreparedStatement updateProductCarts =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCTCARTS SET USER_UUID = '" +
                  uuid + "' WHERE USER_ID = " + res.getObject ("ID"));
         updateProductCarts.execute ();
         updateProductCarts.close ();

         PreparedStatement updateProductUser =
            databaseConnection
               .prepareStatement ("UPDATE PRODUCT_USER_AUTH SET USERS_UUID = '" +
                  uuid + "' WHERE USERS_ID = " + res.getObject ("ID"));
         updateProductUser.execute ();
         updateProductUser.close ();

         PreparedStatement updateRestrictions =
            databaseConnection
               .prepareStatement ("UPDATE USER_RESTRICTIONS SET USER_UUID = '" +
                  uuid + "' WHERE USER_ID = " + res.getObject ("ID"));
         updateRestrictions.execute ();
         updateRestrictions.close ();

         PreparedStatement updateRoles =
            databaseConnection
               .prepareStatement ("UPDATE USER_ROLES SET USER_UUID = '" +
                  uuid + "' WHERE USER_ID = " + res.getObject ("ID"));
         updateRoles.execute ();
         updateRoles.close ();
      }
      getUsers.close ();         
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getUsers =
         databaseConnection
            .prepareStatement ("SELECT ID, LOGIN, COUNTRY FROM USERS");
      ResultSet res = getUsers.executeQuery ();  
      
      while (res.next ())
      {
         String country = (String) res.getObject ("COUNTRY");
         String country2 = country.replaceAll(
               "[-\\[(){},.;!?><_|\\/%*^$\\]]", "");
         String found = null;
         
         PreparedStatement getCountries=databaseConnection.prepareStatement(
            "SELECT NAME, ALPHA2, ALPHA3 FROM COUNTRIES");
         ResultSet countries = getCountries.executeQuery ();
         while (countries.next ())
         {
            String ref = (String) countries.getObject ("NAME");
            String a2 = (String) countries.getObject ("ALPHA2");
            String a3 = (String) countries.getObject ("ALPHA3");
            if (ref.toLowerCase ().equals (country.toLowerCase ()) || 
                 a2.toLowerCase ().equals (country.toLowerCase ()) || 
                 a3.toLowerCase ().equals (country.toLowerCase ()) || 
                 ref.toLowerCase ().equals (country2.toLowerCase ()) || 
                 a2.toLowerCase ().equals (country2.toLowerCase ()) || 
                 a3.toLowerCase ().equals (country2.toLowerCase ()))
            {
               found = ref;
               break;
            }
         }
         if (found != null)
         {
            PreparedStatement updateUser = databaseConnection.
               prepareStatement("UPDATE USERS SET COUNTRY=? WHERE ID=?");
            
            updateUser.setString (1, found);
            updateUser.setLong (2, (Long)res.getObject ("ID"));
            
            updateUser.execute ();
            updateUser.close ();
         }
         else
         {
            LOGGER.warn("Unknown country for '"+res.getObject ("LOGIN")+"' : "+country);
         }
         getCountries.close ();
      }
      getUsers.close ();
   }
   catch (Exception e)
   {
      LOGGER.error(
            "Error during liquibase update 'TransformUserCountry'", e);
   }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {
      PreparedStatement getUsers =
            databaseConnection.prepareStatement (
                        "SELECT USER_ID,ROLES FROM USER_ROLES " +
                              "WHERE ROLES = 'DATARIGHT_MANAGER' OR " +
                              "ROLES = 'COLLECTION_MANAGER'");
       ResultSet res = getUsers.executeQuery ();
       Object prevId = null;
       while (res.next ())
       {
          // if we found two times the same user ID, user is DATARIGHT 
          // and COLLECTION manager. We can remove the last one to do
          // not create two times DATA MANAGER role for user.
          if (prevId == res.getObject ("USER_ID"))
          {
             PreparedStatement removeRole =
                databaseConnection
                   .prepareStatement ("DELETE FROM USER_ROLES "+
                      "WHERE ROLES = 'COLLECTION_MANAGER' AND USER_ID = " +
                         res.getObject ("USER_ID"));
             removeRole.execute ();
             removeRole.close ();
          }
          prevId = res.getObject ("USER_ID");
       }
               
      PreparedStatement updateRole =
         databaseConnection
            .prepareStatement ("UPDATE USER_ROLES SET ROLES = 'DATA_MANAGER"+
               "' WHERE ROLES = 'DATARIGHT_MANAGER' OR " +
                  "ROLES = 'COLLECTION_MANAGER'");
      updateRole.execute ();
      updateRole.close ();
      getUsers.close ();
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   JdbcConnection databaseConnection =
      (JdbcConnection) database.getConnection ();
   try
   {         
      PreparedStatement getCollections =
         databaseConnection.prepareStatement ("SELECT ID FROM COLLECTIONS");
      ResultSet res = getCollections.executeQuery ();
      while (res.next ())
      {
         String uuid = UUID.randomUUID ().toString ();
         PreparedStatement updateCollections =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTIONS SET UUID = '" + uuid +
                  "' WHERE ID = " + res.getObject ("ID"));
         updateCollections.execute ();
         updateCollections.close ();
         
         PreparedStatement updateParentCollections =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTIONS SET PARENT_COLLECTION_UUID = '" + uuid +
                  "' WHERE PARENT_COLLECTION_ID = " + res.getObject ("ID"));
         updateParentCollections.execute ();
         updateParentCollections.close ();

         PreparedStatement updateCollectionProducts =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTION_PRODUCT SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionProducts.execute ();
         updateCollectionProducts.close ();

         PreparedStatement updateCollectionuser =
            databaseConnection
               .prepareStatement ("UPDATE COLLECTION_USER_AUTH SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionuser.execute ();
         updateCollectionuser.close ();
         
         PreparedStatement updateCollectionFileScanner =
            databaseConnection
               .prepareStatement ("UPDATE FILESCANNER_COLLECTIONS SET COLLECTIONS_UUID = '" +
                  uuid + "' WHERE COLLECTIONS_ID = " + res.getObject ("ID"));
         updateCollectionFileScanner.execute ();
         updateCollectionFileScanner.close ();
      }
      getCollections.close ();         
   }
   catch (Exception e)
   {
      e.printStackTrace ();
   }
   
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   String rootName = CollectionDao.COLLECTION_ROOT_NAME;
   String rootId = "SELECT UUID FROM COLLECTIONS WHERE NAME='%s'";
   String auth = "DELETE FROM COLLECTION_USER_AUTH WHERE COLLECTIONS_UUID='%s'";
   String delete = "DELETE FROM COLLECTIONS WHERE NAME='%s'";
   try
   {
      String cid = null;
      String sql;
      PreparedStatement statement;
      JdbcConnection connection = (JdbcConnection) database.getConnection ();

      // get root collection id
      sql = String.format (rootId, rootName);
      statement = connection.prepareStatement (sql);
      statement.execute ();
      ResultSet resultSet = statement.getResultSet ();
      if (resultSet.next ())
      {
         cid = resultSet.getString (1);
      }
      statement.close ();

      if (cid != null)
      {
         // remove default authorization on root collection
         sql = String.format (auth, cid);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();

         // delete root collection
         sql = String.format (delete, rootName);
         statement = connection.prepareStatement (sql);
         statement.execute ();
         statement.close ();
      }
   }
   catch (DatabaseException | SQLException e)
   {
      throw new CustomChangeException (e);
   }
}
 

@Override
public void execute (Database database) throws CustomChangeException
{
   try
   {
      JdbcConnection jdbc = (JdbcConnection) database.getConnection ();
      String sql;
      Statement statement;
      ResultSet resultSet;

      // Retrieve unencrypted user password
      sql = "SELECT LOGIN, PASSWORD FROM USERS " +
            "WHERE PASSWORD_ENCRYPTION = 'NONE'";
      statement = jdbc.createStatement ();
      HashMap<String, String> unencrypted_user = new HashMap<> ();
      resultSet = statement.executeQuery (sql);
      while (resultSet.next ())
      {
         unencrypted_user.put (resultSet.getString ("LOGIN"),
               resultSet.getString ("PASSWORD"));
      }
      resultSet.close ();
      statement.close ();

      // Encrypt user password and update user
      MessageDigest md = MessageDigest.getInstance ("MD5");
      sql = "UPDATE USERS SET PASSWORD_ENCRYPTION = 'MD5', PASSWORD = '%s'" +
            " WHERE LOGIN = '%s'";
      String query;
      String password;
      for (String login : unencrypted_user.keySet ())
      {
         password = unencrypted_user.get (login);
         password = new String (
               Hex.encode (md.digest (password.getBytes ("UTF-8"))));
         query = String.format (sql, password, login);
         statement = jdbc.createStatement ();
         int updated =  statement.executeUpdate (query);
         if (updated != 1)
         {
            LOGGER.warn(updated + " encryption update perform on user : " + login);
         }
         statement.close ();
      }
      unencrypted_user.clear ();
   }
   catch (Exception e)
   {
      throw new CustomChangeException (
            "An error occurred during forceEncryptPassword changelog", 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 ();
   }
   
}