public List<ClientEventEntry> findLogEvents(
List<SearchConstraint> constraints, List<String> orderBy, int offset, int limit, Connection c)
throws SQLException {
StringBuffer sql =
new StringBuffer(
"select e.event_id, e.customer_id, e.user_id, e.event_time, e.description, e.has_log_file from dat_client_log_events e ");
appendWhere(sql, constraints, s_propToColumnMap);
appendOrderBy(sql, orderBy, "e.event_id desc", s_propToColumnMap);
appendLimits(sql, offset, limit);
Statement stmt = null;
ResultSet rs = null;
try {
stmt = c.createStatement();
rs = stmt.executeQuery(sql.toString());
List<ClientEventEntry> results = new ArrayList<ClientEventEntry>();
while (rs.next()) {
ClientEventEntry entry =
new ClientEventEntry(
rs.getInt(1),
rs.getInt(2),
rs.getInt(3),
resolveDate(rs.getTimestamp(4)),
rs.getString(5),
rs.getInt(6) != 0);
results.add(entry);
}
return results;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
}
}
/** Return the user's secure_hash_key. If one wasn't found create one on the fly. */
private SecureHashKeyResult getSecureHashKey(UserAccount user, Connection c) throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
int userId = user.getUserID();
boolean justCreated = false;
byte[] key = null;
try {
// TODO: consider having UserManager returning secure_hash_key.
// TODO: We have similar logic in several places for creating secure_hash_key just-in-time.
// D.R.Y. this out. Sorry I couldn't resist using this cliche :)
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
} else {
key = rs.getBytes(1);
if (key == null || key.length == 0) {
// hash key not found; create one on the fly.
update =
c.prepareStatement("UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
} else {
justCreated = true;
}
} // needed to set key.
} // user found
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
return new SecureHashKeyResult(key, justCreated);
}
/** Returns event_id of newly created logging entry. */
private Integer logEvent(IClient client, String eventText, InputStream logFileSteam, Connection c)
throws SQLException {
PreparedStatement stmt = null;
int id = 0;
try {
long longId = getSequenceId("seq_client_log_event_ids", c);
if (longId > Integer.MAX_VALUE) {
// dat_client_log_events.event_id is a 32 bit integer numeric type.
// this is a bad problem; ensure it's logged; don't depend on whoever's catching
// the exception.
m_logger.log("seq_client_log_event_ids overflowed").error();
throw new IllegalStateException("seq_client_log_event_ids overflowed.");
}
id = (int) longId;
stmt =
c.prepareStatement(
"insert into dat_client_log_events "
+ "(event_id, customer_id, user_id, event_time, description, has_log_file) "
+ "values "
+ "(?, ?, ?, current_timestamp, ?, ?)");
stmt.setInt(1, id);
stmt.setInt(2, client.getCustomerId());
stmt.setInt(3, client.getUserId());
stmt.setString(4, eventText);
stmt.setInt(5, logFileSteam == null ? 0 : 1);
if (stmt.executeUpdate() != 1) {
throw new SQLException("Can't insert client event for " + client);
}
} finally {
DbUtils.safeClose(stmt);
}
return new Integer(id);
}
public void purgeLogEvents(final List<SearchConstraint> constraints, final int daysToKeep) {
blockHereIfBadNfs();
StringBuffer sql =
new StringBuffer(
"select u.mail_directory || '/logs/' || e.event_id || '.log' "
+ "from dat_client_log_events e, dat_user_account u "
+ "where event_time < current_timestamp - (? || ' days')::interval "
+ "and e.has_log_file != 0 "
+ "and e.user_id = u.object_id");
appendWhereConstraints(sql, constraints, s_propToColumnMap);
m_logCategory.info("Purge event logs query is\n" + sql);
StringBuffer sql2 =
new StringBuffer(
"delete from dat_client_log_events "
+ "where event_time < current_timestamp - (? || ' days')::interval");
if (!constraints.isEmpty()) {
sql2.append(" and event_id in (select e.event_id from dat_client_log_events e ");
appendWhere(sql2, constraints, s_propToColumnMap);
sql2.append(")");
}
m_logCategory.info("Purge event logs query is\n" + sql2);
try {
Connection c = m_txManager.getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
boolean needsRollback = true;
try {
stmt = c.prepareStatement(sql.toString());
stmt.setInt(1, daysToKeep);
rs = stmt.executeQuery();
while (rs.next()) {
File logFile = new File(getNfsRoot(), rs.getString(1));
// nfs usage, but DB transaction takes no locks.
if (logFile.exists()) {
boolean ok = logFile.delete();
if (!ok) {
m_logger
.log("Unable to delete")
.param(LoggingConsts.FILENAME, logFile.getAbsolutePath())
.warn();
}
} // file exists.
} // each row
// Below, no nfs usage occurs. We can use the same connection.
// the 'sql delete' may use DB locks.
stmt = c.prepareStatement(sql2.toString());
stmt.setInt(1, daysToKeep);
stmt.executeUpdate();
c.commit();
needsRollback = false;
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(stmt);
if (needsRollback) {
c.rollback();
}
m_txManager.returnConnection(c);
}
} catch (Exception ex) {
m_logger.log("Error in purgeLogEvents").warn(ex);
}
}
public void setKeyCreatingIfNeeded(
List<? extends IClientInfo> clients,
List<IClientInfo> clientsNeedingSignalUpdate,
Connection c)
throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
try {
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
for (IClientInfo client : clients) {
int userId = client.getUserId();
// ensure dat_user_account.secure_hash_key is filled.
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
continue;
}
boolean firstTimeCreate = false;
byte[] key = rs.getBytes(1);
if (key == null || key.length == 0) {
if (update == null) {
update =
c.prepareStatement(
"UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
}
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
continue;
} else {
firstTimeCreate = true;
}
} // no existing key.
client.getHashSupport().setHashKey(key);
if (firstTimeCreate) {
if (clientsNeedingSignalUpdate != null) {
// EMSDEV-7854. Don't actually do
// updateSignalFiles(client) right here; we want to avoid nfs usage in the middle of a
// db
// transaction.
clientsNeedingSignalUpdate.add(client);
}
}
} // each client.
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
}
/** Writes user state information to the shared filesystem */
private void updateStateCache(
Customer customer, int transitionId, CustomerState newState, Connection c)
throws SQLException {
PreparedStatement pst = null;
ResultSet rs = null;
try {
pst =
c.prepareStatement(
"SELECT u.secure_hash_key, u.object_id, u.user_state, u.is_deleted, u.last_activation_id "
+ "FROM dat_user_account u, dat_transition_users tr "
+ "WHERE u.object_id = tr.user_id AND u.customer_id=? "
+ "AND tr.transition_id=?;");
// The state files are used by Outlook, BB, and maybe future clients.
pst.setInt(1, customer.getCustID());
pst.setInt(2, transitionId);
rs = pst.executeQuery();
while (rs.next()) {
int i = 0;
byte[] key = rs.getBytes(++i);
int userId = rs.getInt(++i);
CustomerState state = CustomerState.fromInt(rs.getInt(++i));
int isDeletedInt = rs.getInt(++i);
int lastActivationId = rs.getInt(++i);
// If the user is marked deleted but has a key, we'll try cleaning
// up state files.
boolean isDeleted = isDeletedInt != IUserManager.USER_NOT_DELETED;
if (key == null || key.length == 0) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account.secure_hash_key not set");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.info(lmg.toString());
// Without a key, we can't determine the signal filenames
// so no cleanup is possible.
continue;
}
ClientHashSupport hash = null;
hash = new ClientHashSupport();
hash.setCustomerId(customer.getCustID());
hash.setUserId(userId);
hash.setHashKey(key);
hash.setLastActivationId(lastActivationId);
if (m_logCategory.isInfoEnabled()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Updating signal files");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.info(lmg.toString());
}
// wrt EMSDEV-7854 nfs calls and database transactions.
// Above is only doing a select; no locks are taken and this
// method is private.
updateSignalFiles(hash, state, isDeleted);
} // each row.
} finally {
DbUtils.safeClose(rs);
DbUtils.safeClose(pst);
}
}
