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); } }
@SuppressWarnings("unchecked") @Test public void testAppendCustomerPrefixConstraint() throws Exception { CustomerDAO dao = new CustomerDAO(); StringBuffer clause = new StringBuffer(); SearchConstraint sc = new SearchConstraint( ICustomerService.PROP_CUST_CAPABILITY_PREFIX, SearchConstraintOperator.CONSTRAINT_EQUALS, "val3"); dao.appendCustomerPrefixConstraint(sc, clause); String expected = " c.customer_id in ( select customer_id from dat_customer_capabilities where cap_value = E'val3' and cap_name = E'' ) "; assertEquals("Wrong constraint added.", expected, clause.toString()); }
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); } }