/**
   * Deletes a record from the Database Server using the provided delete request.
   *
   * @param request The delete request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result delete(DeleteRequest request) throws ErrorResultException {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String filterAttributeName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String filterAttributeValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent(1).rdn();
      final String OUName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + OUName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String columnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

      if (columnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + filterAttributeName
                + "'. Please check if mapping was succesful.");

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "DELETE FROM " + tableName + " WHERE " + columnName + "='" + filterAttributeValue + "'";
      st.executeUpdate(sql);
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }
  /**
   * Search the Database Server using the provided search request.
   *
   * @param request The search request.
   * @param handler A search result handler which can be used to asynchronously process the search
   *     result entries and references as they are received, may be null.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result search(SearchRequest request, SearchResultHandler handler)
      throws ErrorResultException {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final int DNSize = DN.size();
      final String baseDN = DN.parent(DNSize - 2).toString();
      RDN OU = null;
      RDN rDN = null;
      String OUName = "";
      String rDNAttributeName = "";
      String rDNAttributeValue = "";

      if (DNSize > 3) {
        OU = DN.parent(1).rdn();
        OUName = OU.getFirstAVA().getAttributeValue().toString();
        rDN = DN.parent(0).rdn();
        rDNAttributeName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
        rDNAttributeValue = rDN.getFirstAVA().getAttributeValue().toString();
      } else if (DNSize > 2) {
        OU = DN.parent(0).rdn();
        OUName = OU.getFirstAVA().getAttributeValue().toString();
      }
      List<String> returnAttributeNames = request.getAttributes();
      final List<String> removeAttributesList = new ArrayList<String>();

      // Get all column names to be returned to return full records.
      if (returnAttributeNames == null) {
        if (OU != null) {
          returnAttributeNames = jdbcm.getOrganizationalUnitAttributes(baseDN, OUName);

          if (returnAttributeNames == null)
            throw new NullPointerException(
                "JDBC Error: Could not find any matching attributes in OU: '" + OUName + "'.");
          final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

          for (Iterator<String> iterAttributes = returnAttributeNames.iterator();
              iterAttributes.hasNext(); ) {
            String returnAttributeName = iterAttributes.next();
            final String returnColumnName =
                jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, returnAttributeName);

            if (returnColumnName == null) removeAttributesList.add(returnAttributeName);
          }
          returnAttributeNames.removeAll(removeAttributesList);
        } else {
          final List<String> OUList = jdbcm.getOrganizationalUnits(baseDN);

          if (OUList == null)
            throw new NullPointerException(
                "JDBC Error: Could not find any matching organizational units in DN: '"
                    + baseDN
                    + "'.");

          for (Iterator<String> iterOU = OUList.iterator(); iterOU.hasNext(); ) {
            OUName = iterOU.next();
            final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);
            returnAttributeNames = jdbcm.getOrganizationalUnitAttributes(baseDN, OUName);

            for (Iterator<String> iterAttributes = returnAttributeNames.iterator();
                iterAttributes.hasNext(); ) {
              String returnAttributeName = iterAttributes.next();
              final String returnColumnName =
                  jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, returnAttributeName);

              if (returnColumnName == null) removeAttributesList.add(returnAttributeName);
            }
            returnAttributeNames.removeAll(removeAttributesList);
          }
        }
      }
      // Convert the search filter.
      final Filter searchFilter = request.getFilter();
      final String convertedFilterString = convertSearchFilter(searchFilter, baseDN, OUName);

      if (convertedFilterString.startsWith("Err:")) {
        String[] errorStringSplitter = convertedFilterString.split("Err:");
        throw new NullPointerException(errorStringSplitter[1]);
      }
      final List<String> returnColumnNames = new ArrayList<String>();

      // Search mapping for the corresponding column name for each return attribute.
      for (Iterator<String> iter = returnAttributeNames.iterator(); iter.hasNext(); ) {

        if (OU == null) {
          final String returnAttributeName = iter.next();
          final List<String> OUList = jdbcm.getOrganizationalUnits(baseDN);

          if (OUList == null)
            throw new NullPointerException(
                "JDBC Error: Could not find any matching organizational units in DN: '"
                    + baseDN
                    + "'.");

          for (Iterator<String> iterOU = OUList.iterator(); iterOU.hasNext(); ) {
            OUName = iterOU.next();
            final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);
            final String returnColumnName =
                jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, returnAttributeName);

            if (returnColumnName == null) continue;
            else returnColumnNames.add(returnColumnName);
            break;
          }
        } else {
          final String returnAttributeName = iter.next();
          final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

          if (tableName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching table name for OU: '"
                    + OUName
                    + "' in DN: '"
                    + baseDN
                    + "'. Please check if mapping was succesful.");
          final String returnColumnName =
              jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, returnAttributeName);

          if (returnColumnName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching column name for attribute: '"
                    + returnAttributeName
                    + "'. Please check if mapping was succesful.");
          returnColumnNames.add(returnColumnName);
        }
      }

      String selectString = "";
      String fromString = "";
      String whereString = convertedFilterString;

      if (returnAttributeNames.isEmpty()) selectString = "*";

      // Build the SQL SELECT string.
      if (OU == null) {
        final List<String> OUList = jdbcm.getOrganizationalUnits(baseDN);

        if (OUList == null)
          throw new NullPointerException(
              "JDBC Error: Could not find any matching organizational units in DN: '"
                  + baseDN
                  + "'.");

        for (int i = 0; i < OUList.size(); i++) {
          final String currentOU = OUList.get(i);
          final String currentTable = jdbcm.getTableNameFromMapping(baseDN, currentOU);

          if (currentTable == null) continue;
          final List<String> currentTableColumnNames = jdbcm.getTableColumns(currentTable);

          if (!fromString.isEmpty()) fromString = fromString.concat(",");
          fromString = fromString.concat(currentTable);

          for (int j = 0; j < returnColumnNames.size(); j++) {
            final String returnColumnName = returnColumnNames.get(j);

            if (currentTableColumnNames.contains(returnColumnName)) {

              if (!selectString.isEmpty()) selectString = selectString.concat(",");
              selectString = selectString.concat(currentTable + "." + returnColumnName);
            }
          }
        }
      } else {
        final String selectTable = jdbcm.getTableNameFromMapping(baseDN, OUName);
        if (selectTable == null)
          throw new NullPointerException(
              "JDBC Error: Could not find matching table name for OU: '"
                  + OUName
                  + "' in DN: '"
                  + baseDN
                  + "'. Please check if mapping was succesful.");

        fromString = fromString.concat(selectTable);

        for (int j = 0; j < returnColumnNames.size(); j++) {
          final String returnColumnName = returnColumnNames.get(j);

          if (returnColumnName == null) continue;

          if (!selectString.isEmpty()) selectString = selectString.concat(",");
          selectString = selectString.concat(returnColumnName);
        }

        if (rDN != null) {
          final String columnName =
              jdbcm.getColumnNameFromMapping(selectTable, baseDN, OUName, rDNAttributeName);

          if (columnName == null)
            throw new NullPointerException(
                "JDBC Error: Could not find matching column name for attribute: '"
                    + rDNAttributeName
                    + "'. Please check if mapping was succesful.");
          whereString = columnName + "='" + rDNAttributeValue + "'";
        }
      }

      // Build the SQL query.
      final Statement st = connection.createStatement();
      String sql = "SELECT " + selectString + " FROM " + fromString;

      if (!whereString.isEmpty()) sql = sql.concat(" WHERE " + whereString);
      ResultSet rs = st.executeQuery(sql);
      ResultSetMetaData rsmd = rs.getMetaData();

      // Fill the entries result for this query.
      searchEntries.clear();
      while (rs.next()) {
        Entry e = new LinkedHashMapEntry();

        for (int i = 1; i <= rsmd.getColumnCount(); i++) {
          e.addAttribute(rsmd.getTableName(i) + "_" + rsmd.getColumnName(i), rs.getObject(i));
        }
        searchEntries.add(e);
      }
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }
  /**
   * Modifies a record in the Database Server using the provided modify request.
   *
   * @param request The modify request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result modify(final ModifyRequest request) {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String filterAttributeName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String filterAttributeValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent(1).rdn();
      final String OUName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + OUName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String columnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

      if (columnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + filterAttributeName
                + "'. Please check if mapping was succesful.");

      // Get attribute and modificationtype for each modification.
      // Search mapping for the corresponding column name for the modification attribute.
      final List<Modification> modificationList = request.getModifications();
      final ListIterator<Modification> listIter = modificationList.listIterator();
      String modificationString = "";

      while (listIter.hasNext()) {
        final Modification modification = listIter.next();
        final ModificationType modificationType = modification.getModificationType();
        final Attribute modificationAttribute = modification.getAttribute();
        final String modificationAttributeName =
            modificationAttribute.getAttributeDescription().toString();
        final String modificationColumnName =
            jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, modificationAttributeName);

        if (modificationColumnName == null)
          throw new NullPointerException(
              "JDBC Error: Could not find matching column name for attribute: '"
                  + modificationAttributeName
                  + "'. Please check if mapping was succesful.");
        String modificationAttributeValue = "";

        if (modificationType == ModificationType.ADD) {
          final Iterator<ByteString> iter = modificationAttribute.iterator();

          int counter = 0;
          while (iter.hasNext()) {
            if (counter > 0) modificationAttributeValue = modificationAttributeValue.concat(", ");
            modificationAttributeValue = modificationAttributeValue.concat(iter.next().toString());
            counter++;
          }
          final Object classType = jdbcm.getTableColumnDataType(tableName, modificationColumnName);

          if (classType == Integer.class)
            modificationAttributeValue =
                "(CASE WHEN ("
                    + modificationColumnName
                    + " = 0) THEN ' "
                    + modificationAttributeValue
                    + "' ELSE concat("
                    + modificationColumnName
                    + ", ', "
                    + modificationAttributeValue
                    + "') END)";
          else
            modificationAttributeValue =
                "(CASE WHEN ("
                    + modificationColumnName
                    + " = 'Default Value') THEN ' "
                    + modificationAttributeValue
                    + "' ELSE concat("
                    + modificationColumnName
                    + ", ', "
                    + modificationAttributeValue
                    + "') END)";
        } else if (modificationType == ModificationType.REPLACE) {
          final Iterator<ByteString> iter = modificationAttribute.iterator();

          while (iter.hasNext()) {
            modificationAttributeValue = "'" + iter.next().toString() + "'";
          }
        } else {
          final boolean nullable = jdbcm.getTableColumnNullable(tableName, modificationColumnName);

          if (nullable == false)
            throw new SQLException("Cannot delete data from not-nullable column.");
          final Object classType = jdbcm.getTableColumnDataType(tableName, modificationColumnName);

          if (classType == Integer.class)
            modificationAttributeValue = "'" + Integer.toString(0) + "'";
          else modificationAttributeValue = "'Default Value'";
        }
        modificationString =
            modificationString.concat(
                modificationColumnName + "=" + modificationAttributeValue + ", ");
      }
      modificationString = modificationString.substring(0, modificationString.length() - 2);

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "UPDATE "
              + tableName
              + " SET "
              + modificationString
              + " WHERE "
              + columnName
              + "='"
              + filterAttributeValue
              + "'";
      st.executeUpdate(sql);
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }
  /**
   * Compares a record in the Database Server using the provided compare request.
   *
   * @param request The compare request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public CompareResult compare(final CompareRequest request) throws ErrorResultException {
    CompareResult cr;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String filterAttributeName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String filterAttributeValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent(1).rdn();
      final String OUName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, OUName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + OUName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String columnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, filterAttributeName);

      if (columnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + filterAttributeName
                + "'. Please check if mapping was succesful.");

      // Search mapping for the corresponding column name for the comparing attribute.
      final String compareAttributeName = request.getAttributeDescription().toString();
      final String compareAttributeValue = request.getAssertionValueAsString();
      final String compareColumnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, OUName, compareAttributeName);

      if (compareColumnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + compareAttributeName
                + "'. Please check if mapping was succesful.");

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "SELECT * FROM "
              + tableName
              + " WHERE "
              + columnName
              + "='"
              + filterAttributeValue
              + "' AND "
              + compareColumnName
              + "='"
              + compareAttributeValue
              + "'";
      final ResultSet rs = st.executeQuery(sql);
      if (rs.first()) cr = Responses.newCompareResult(ResultCode.COMPARE_TRUE);
      else cr = Responses.newCompareResult(ResultCode.COMPARE_FALSE);
    } catch (SQLException e) {
      System.out.println(e.toString());
      cr = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      cr.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      cr = Responses.newCompareResult(ResultCode.OPERATIONS_ERROR);
      cr.setCause(e);
    }
    return cr;
  }
  /**
   * Adds a record to the Database Server using the provided add request.
   *
   * @param request The add request.
   * @return The result of the operation.
   * @throws ErrorResultException If the result code indicates that the request failed for some
   *     reason.
   * @throws SQLException If the SQL query has an invalid format.
   * @throws NullPointerException If {@code request} was {@code null}, or if a corresponding mapping
   *     value could not be found in the mapping component.
   */
  @Override
  public Result add(final AddRequest request) throws ErrorResultException {
    Result r;
    try {
      // Split up the DN the of the request.
      final DN DN = request.getName();
      final RDN rDN = DN.rdn();
      final String rDNName = rDN.getFirstAVA().getAttributeType().getNameOrOID();
      final String rDNValue = rDN.getFirstAVA().getAttributeValue().toString();
      final RDN OU = DN.parent().rdn();
      final String organizationalUnitName = OU.getFirstAVA().getAttributeValue().toString();
      final String baseDN = DN.parent(2).toString();

      // Search mapping for the corresponding table and column names.
      final String tableName = jdbcm.getTableNameFromMapping(baseDN, organizationalUnitName);

      if (tableName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching table name for OU: '"
                + organizationalUnitName
                + "' in DN: '"
                + baseDN
                + "'. Please check if mapping was succesful.");
      final String rDNColumnName =
          jdbcm.getColumnNameFromMapping(tableName, baseDN, organizationalUnitName, rDNName);

      if (rDNColumnName == null)
        throw new NullPointerException(
            "JDBC Error: Could not find matching column name for attribute: '"
                + rDNName
                + "'. Please check if mapping was succesful.");
      final Map<String, Object> columnValuesMap = new HashMap<String, Object>();
      final Iterable<Attribute> attributesCollection = request.getAllAttributes();
      final Iterator<Attribute> attributeIter = attributesCollection.iterator();

      // Search mapping for the corresponding column name for each attribute.
      while (attributeIter.hasNext()) {
        final Attribute att = attributeIter.next();
        final Iterator<ByteString> valueIter = att.iterator();
        final String attributeName = att.getAttributeDescriptionAsString();

        if (attributeName == "objectClass") continue;
        final String columnName =
            jdbcm.getColumnNameFromMapping(
                tableName, baseDN, organizationalUnitName, attributeName);

        if (columnName == null)
          throw new NullPointerException(
              "JDBC Error: Could not find matching column name for attribute: '"
                  + attributeName
                  + "'. Please check if mapping was succesful.");
        String columnValue = "";

        while (valueIter.hasNext()) {
          columnValue = columnValue.concat(valueIter.next().toString());
        }
        columnValuesMap.put(columnName, columnValue);
      }

      // Build the SQL values String.
      final ArrayList<String> columnList = jdbcm.getTableColumns(tableName);
      String columnNamesString = " (" + rDNColumnName;
      String columnValuesString = " ('" + rDNValue + "'";

      for (int i = 0; i < columnList.size(); i++) {
        final String columnName = columnList.get(i);

        if (columnNamesString.contains(columnName)) continue;
        final Object columnDataType = jdbcm.getTableColumnDataType(tableName, columnName);
        Object columnValue = columnValuesMap.get(columnName);

        if (columnValue == null) {
          boolean columnNullable = jdbcm.getTableColumnNullable(tableName, columnName);

          if (columnNullable) continue;

          if (columnDataType.equals(Integer.class)) columnValue = "0";
          else columnValue = "Default Value";
        }

        if (columnDataType.equals(Integer.class))
          columnValue = Integer.parseInt(columnValue.toString());
        columnNamesString = columnNamesString.concat(", " + columnName);
        columnValuesString = columnValuesString.concat(", '" + columnValue + "'");
      }
      columnNamesString = columnNamesString.concat(")");
      columnValuesString = columnValuesString.concat(")");

      // Build the SQL query.
      final Statement st = connection.createStatement();
      final String sql =
          "INSERT INTO " + tableName + columnNamesString + " VALUES" + columnValuesString;
      st.executeUpdate(sql);
      r = Responses.newResult(ResultCode.SUCCESS);
    } catch (SQLException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    } catch (NullPointerException e) {
      System.out.println(e.toString());
      r = Responses.newResult(ResultCode.OPERATIONS_ERROR);
      r.setCause(e);
    }
    return r;
  }