/**
   * Reads active registry entries assigned to this server.
   *
   * @return the list of the active registry entries associated to this server
   * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs
   */
  public List<RegistryEntry> getActiveEntries() throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    List<RegistryEntry> entries = new ArrayList<RegistryEntry>();

    try {
      if (log.isTraceEnabled()) {
        log.trace("Executing '" + queryDesc.getReadActiveEntriesQuery() + "'");
      }

      con = getConnection();
      con.setReadOnly(true);

      ps = con.prepareStatement(queryDesc.getReadActiveEntriesQuery());

      ps.setString(1, "Y");
      rs = ps.executeQuery();

      while (rs.next()) {
        entries.add(resultSetToRegistryEntry(rs));
      }

    } catch (Exception e) {

      throw new DataAccessException(e);

    } finally {
      DBTools.close(con, ps, rs);
    }

    return entries;
  }
  /**
   * Reads registry entries assigned to this server. If <code>lastUpdate</code> is different than 0,
   * just the changed entries with last_update bigger than the given one and the given status are
   * returned.
   *
   * @param lastUpdate the lastUpdate. 0 if all entries must be returned
   * @param status the wanted status
   * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs
   * @return the read entries
   */
  public List<RegistryEntry> getEntries(long lastUpdate, String status) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    List<RegistryEntry> entries = new ArrayList<RegistryEntry>();

    try {

      con = getConnection();
      con.setReadOnly(true);

      if (lastUpdate != 0) {
        //
        // Read only changed registry entries
        //
        if (log.isTraceEnabled()) {
          log.trace("Executing '" + queryDesc.getReadChangedEntriesQuery() + "'");
        }

        ps = con.prepareStatement(queryDesc.getReadChangedEntriesQuery());
        ps.setLong(1, lastUpdate);
        ps.setString(2, status);

      } else {
        //
        // Read all registry entries
        //
        if (log.isTraceEnabled()) {
          log.trace("Executing '" + queryDesc.getReadActiveEntriesQuery() + "'");
        }

        ps = con.prepareStatement(queryDesc.getReadActiveEntriesQuery());
        ps.setString(1, "Y");
      }

      rs = ps.executeQuery();

      while (rs.next()) {
        entries.add(resultSetToRegistryEntry(rs));
      }

    } catch (Exception e) {

      throw new DataAccessException(e);

    } finally {
      DBTools.close(con, ps, rs);
    }

    return entries;
  }
  /**
   * Marks as deleted the entry with the given id
   *
   * @param id the entry id
   * @throws DataAccessException if an error occurs
   * @return the number of updated entries
   */
  public int markAsDeleted(long id) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    int rowsUpdated = 0;

    try {

      con = getConnection();

      ps = con.prepareStatement(queryDesc.getUpdateStatusQuery());

      ps.setLong(1, System.currentTimeMillis());
      ps.setString(2, RegistryEntryStatus.DELETED);
      ps.setLong(3, id);

      rowsUpdated = ps.executeUpdate();

    } catch (Exception ex) {

      throw new DataAccessException("Error marking entry with id '" + id + "' as deleted", ex);

    } finally {
      DBTools.close(con, ps, null);
    }
    return rowsUpdated;
  }
  /**
   * Returns the registry entry with the given id
   *
   * @return the read entry
   * @param id the id of the pim registry entry
   * @throws DataAccessException if an error occurs
   */
  public RegistryEntry getEntryById(long id) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    RegistryEntry entry = null;

    try {

      con = getConnection();
      con.setReadOnly(true);

      ps = con.prepareStatement(queryDesc.getReadEntryQuery());
      ps.setLong(1, id);
      rs = ps.executeQuery();

      while (rs.next()) {
        entry = resultSetToRegistryEntry(rs);
      }

    } catch (Exception e) {
      throw new DataAccessException("Error reading entry with id: " + id, e);
    } finally {
      DBTools.close(con, ps, rs);
    }

    return entry;
  }
  /**
   * Inserts the given entry. If no error occurs, the entry id is returned and set as id in the
   * given entry object.
   *
   * @param entry the entry to updated
   * @return the entry id
   * @throws DataAccessException if an error occurs
   */
  public long insertRegistryEntry(RegistryEntry entry) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;

    long id = getNextEntryId();

    try {

      con = getConnection();

      ps = con.prepareStatement(queryDesc.getInsertEntryQuery());

      ps.setLong(1, id);
      ps.setLong(2, entry.getPeriod());
      ps.setString(3, entry.getActive() ? "Y" : "N");
      ps.setString(4, entry.getTaskBeanFile());
      ps.setLong(5, entry.getLastUpdate());
      ps.setString(6, RegistryEntryStatus.NEW);

      short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns();
      String[] extraProperties = queryDesc.getExtraProperties();
      if (numberOfExtraProperties > 0) {
        for (short j = 0; j < numberOfExtraProperties; j++) {
          Object value = null;
          if (entry.hasProperty(extraProperties[j])) {
            value = entry.getProperty(extraProperties[j]);
          }
          ps.setObject(7 + j, value);
        }
      }

      ps.executeUpdate();

      entry.setId(id);

    } catch (Exception e) {

      throw new DataAccessException("Error inserting a registry entry", e);

    } finally {
      DBTools.close(con, ps, null);
    }

    return id;
  }
  /**
   * Updates the given entry. If no error occurs, the number of updated rows is returned.
   *
   * @param entry the entry to updated
   * @return the number of updated rows
   * @throws DataAccessException if an error occurs
   */
  public int updateRegistryEntry(RegistryEntry entry) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    int rowsUpdated = 0;

    try {

      con = getConnection();

      ps = con.prepareStatement(queryDesc.getUpdateEntryQuery());

      ps.setLong(1, entry.getPeriod());
      ps.setString(2, entry.getActive() ? "Y" : "N");
      ps.setString(3, entry.getTaskBeanFile());
      ps.setLong(4, entry.getLastUpdate());
      ps.setString(5, RegistryEntryStatus.UPDATED);

      short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns();
      String[] extraProperties = queryDesc.getExtraProperties();
      if (numberOfExtraProperties > 0) {
        short j = 0;
        for (; j < numberOfExtraProperties; j++) {
          Object value = null;
          if (entry.hasProperty(extraProperties[j])) {
            value = entry.getProperty(extraProperties[j]);
          }
          ps.setObject(6 + j, value);
        }
        ps.setLong(6 + j, entry.getId());
      } else {
        ps.setLong(6, entry.getId());
      }
      rowsUpdated = ps.executeUpdate();

    } catch (Exception e) {

      throw new DataAccessException("Error updating registry entry '" + entry.getId() + "'", e);

    } finally {
      DBTools.close(con, ps, null);
    }
    return rowsUpdated;
  }
  /**
   * Creates a RegistryEntry reading the given ResultSet
   *
   * @return the RegistryEntry
   * @param rs the resultSet
   * @throws java.sql.SQLException if an error occurs
   */
  private RegistryEntry resultSetToRegistryEntry(ResultSet rs) throws SQLException {

    RegistryEntry entry = new RegistryEntry();

    entry.setId(rs.getLong(1));
    entry.setPeriod(rs.getLong(2));
    entry.setActive("Y".equalsIgnoreCase(rs.getString(3)));
    entry.setTaskBeanFile(rs.getString(4));
    entry.setLastUpdate(rs.getLong(5));

    String s = rs.getString(6);
    if (s == null || s.length() == 0) {
      entry.setStatus(RegistryEntryStatus.UNKNOWN);
    }
    if ("N".equals(s)) {
      entry.setStatus(RegistryEntryStatus.NEW);
    } else if ("U".equals(s)) {
      entry.setStatus(RegistryEntryStatus.UPDATED);
    } else if ("D".equals(s)) {
      entry.setStatus(RegistryEntryStatus.DELETED);
    } else {
      entry.setStatus(RegistryEntryStatus.UNKNOWN);
    }

    short numberOfExtraProperties = queryDesc.getNumberOfExtraColumns();
    String[] extraProperties = queryDesc.getExtraProperties();

    if (numberOfExtraProperties > 0) {
      Map<String, Object> extraPropertiesMap = new LinkedHashMap<String, Object>();
      for (short j = 0; j < numberOfExtraProperties; j++) {
        extraPropertiesMap.put(extraProperties[j], rs.getObject(7 + j));
      }
      entry.setProperties(extraPropertiesMap);
    }

    return entry;
  }
 /**
  * This method allows to establish how many extra property columns are specified in the given
  * ResultSet. Besides, it builds the two strings used to complete the query.
  *
  * @param rs it's the ResultSet to analyze.
  * @param basicColumnsSet it's the set of labels that must be considered as basic.
  * @throws java.sql.SQLException if any error occurs analyzing the ResultSet object.
  */
 protected void updateExtraPropertyInfo(ResultSet rs) throws SQLException {
   short numberOfExtraColumns = 0;
   String[] extraProperties = new String[0];
   columnSuffix = new StringBuilder();
   updateSuffix = new StringBuilder();
   placeholderSuffix = new StringBuilder();
   if (rs != null) {
     ResultSetMetaData metaData = rs.getMetaData();
     int numberOfColumns = metaData.getColumnCount();
     numberOfExtraColumns = (short) (numberOfColumns - basicColumns.size());
     extraProperties = new String[numberOfExtraColumns];
     int j = 0;
     if (numberOfExtraColumns > 0) {
       for (int i = 1; i <= numberOfColumns; i++) {
         String columnName = metaData.getColumnName(i);
         if (columnName != null) {
           String columnKey = columnName.toLowerCase();
           if (!basicColumns.contains(columnKey)) {
             extraProperties[j] = columnKey;
             columnSuffix.append(", ");
             columnSuffix.append(columnName);
             // create the suffix used to insert the extra properties
             placeholderSuffix.append(", ");
             placeholderSuffix.append("? ");
             // create the suffix used to update the extra properties
             updateSuffix.append(", ");
             updateSuffix.append(columnName);
             updateSuffix.append(" =? ");
             j++;
           }
         }
       }
     }
   }
   queryDesc.setNumberOfExtraColumns(numberOfExtraColumns);
   queryDesc.setExtraProperties(extraProperties);
 }
  /**
   * This method allows to create all queries used by the push listener framework
   *
   * @param registryTableName the name of the table storing information about the push listener
   *     entries
   * @param clusterSize number of the members in the cluster
   * @param serverIndex unique index of the instance in the cluster
   */
  private void createQueries(String registryTableName, int clusterSize, int serverIndex) {
    String segmentationClause = null;

    String queryReadEntries =
        MessageFormat.format(SQL_READ_ENTRIES, new Object[] {columnSuffix, registryTableName});

    StringBuffer queryReadChangedEntries = new StringBuffer(queryReadEntries);
    StringBuffer queryReadActiveEntries = new StringBuffer(queryReadEntries);
    StringBuffer queryDeleteEntries =
        new StringBuffer(
            MessageFormat.format(SQL_DELETE_ENTRIES, new Object[] {registryTableName}));
    if (clusterSize > 1) {
      segmentationClause =
          MessageFormat.format(
              SQL_SEGMENTATION_CLAUSE,
              new Object[] {String.valueOf(clusterSize), String.valueOf(serverIndex)});
      queryReadChangedEntries.append(segmentationClause).append(" and ");
      queryReadActiveEntries.append(segmentationClause).append(" and ");
      queryDeleteEntries.append(" and ").append(segmentationClause);
    }
    queryReadChangedEntries.append(SQL_WHERECLAUSE_STATUS);
    queryDesc.setReadChangedEntriesQuery(queryReadChangedEntries.toString());
    queryReadActiveEntries.append(SQL_WHERECLAUSE_ACTIVE_ENTRIES);
    queryDesc.setReadActiveEntriesQuery(queryReadActiveEntries.toString());
    queryDesc.setReadEntryQuery(
        MessageFormat.format(SQL_READ_ENTRY, new Object[] {columnSuffix, registryTableName}));
    queryDesc.setDeleteEntryQuery(
        MessageFormat.format(SQL_DELETE_ENTRY, new Object[] {registryTableName}));
    queryDesc.setDeleteEntriesQuery(queryDeleteEntries.toString());
    queryDesc.setUpdateEntryQuery(
        MessageFormat.format(SQL_UPDATE_ENTRY, new Object[] {registryTableName, updateSuffix}));
    queryDesc.setUpdateStatusQuery(
        MessageFormat.format(SQL_UPDATE_STATUS, new Object[] {registryTableName}));
    queryDesc.setInsertEntryQuery(
        MessageFormat.format(
            SQL_INSERT_ENTRY, new Object[] {registryTableName, columnSuffix, placeholderSuffix}));
  }
  /**
   * Removes the entry with status 'D' associated to this server
   *
   * @return the number of the removed entries
   * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs
   */
  public int removeAllDeletedRegistryEntry() throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    int rowsDeleted = 0;

    try {

      con = getConnection();

      ps = con.prepareStatement(queryDesc.getDeleteEntriesQuery());
      rowsDeleted = ps.executeUpdate();
      return rowsDeleted;

    } catch (Exception e) {

      throw new DataAccessException(e);

    } finally {
      DBTools.close(con, ps, null);
    }
  }
  /**
   * Deletes the entry with the given id
   *
   * @param id the id of the entry to delete
   * @return true if the entry is deleted, false otherwise
   * @throws com.funambol.pushlistener.service.registry.dao.DataAccessException if an error occurs
   */
  public boolean deleteRegistryEntry(long id) throws DataAccessException {

    Connection con = null;
    PreparedStatement ps = null;
    int rowsDeleted = 0;

    try {

      con = getConnection();

      ps = con.prepareStatement(queryDesc.getDeleteEntryQuery());
      ps.setLong(1, id);
      rowsDeleted = ps.executeUpdate();

      return (rowsDeleted > 0);

    } catch (Exception e) {

      throw new DataAccessException(e);

    } finally {
      DBTools.close(con, ps, null);
    }
  }