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