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 testFindCustomersWithConnection() throws Exception {
CustomerDAO dao =
EasyMock.createMockBuilder(CustomerDAO.class)
.addMockedMethod("readNextCustomer")
.addMockedMethod("getCustomerQuery")
.createStrictMock();
ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
Connection connection = EasyMock.createStrictMock(Connection.class);
Statement statement = EasyMock.createStrictMock(Statement.class);
List<SearchConstraint> constraints = new LinkedList<SearchConstraint>();
EasyMock.expect(dao.getCustomerQuery(constraints)).andReturn("aQuery");
EasyMock.expect(connection.createStatement()).andReturn(statement);
EasyMock.expect(statement.executeQuery("aQuery")).andReturn(resultSet);
EasyMock.expect(resultSet.next()).andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(true);
EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
.andReturn(false);
resultSet.close();
EasyMock.expectLastCall();
statement.close();
}
protected static void logPoliciesUsingRemovedTopologyObjs(
String operation,
String topologyType,
Collection<Integer> deletedNodes,
final String constraintName,
Connection c)
throws SQLException {
String query =
"select sets.customer_id, p.policy_name, pc.policy_id, sets.user_set_id, sets.constraint_value "
+ " from policies p, policy_criteria pc, dat_saved_user_sets sets "
+ " where sets.constraint_name = "
+ QueryUtils.literal(constraintName)
+ " and "
+ QueryUtils.dbCast("sets.constraint_value", QueryUtils.CastType.INTEGER)
+ " in "
+ QueryUtils.literal(deletedNodes)
+ " and sets.user_set_id = pc.userset_id and pc.policy_id = p.policy_id";
s_logger.debug(query);
Statement s = null;
ResultSet rs = null;
try {
s = c.createStatement();
rs = s.executeQuery(query);
while (rs.next()) {
int custID = rs.getInt(1);
String policyName = rs.getString(2);
int policyID = rs.getInt(3);
int userSetID = rs.getInt(4);
int nodeID = rs.getInt(5);
StringBuilder bld =
new StringBuilder(operation)
.append(" ")
.append(topologyType)
.append(" ")
.append(nodeID)
.append(" which is refered to by userset ")
.append(userSetID)
.append(" in policy ")
.append(policyName)
.append(" with id ")
.append(policyID)
.append(" for customer ")
.append(custID);
s_logger.warn(bld.toString());
}
} finally {
if (s != null) s.close();
if (rs != null) rs.close();
}
}
private static Set<Integer> purgeDeletedSites(Connection c) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
try {
// find server objects that need to be purged
List<Integer> purgeSiteIds = new ArrayList<Integer>();
Set<Integer> custIds = new HashSet<Integer>();
String query =
"select site.site_id, src.customer_id from dat_dirsync_sources src, dat_customer_sites site where site.is_deleted = true and current_timestamp > site.purge_time and src.source_id = site.source_id";
stmt = c.createStatement();
s_logger.debug(query);
rs = stmt.executeQuery(query);
while (rs.next()) {
purgeSiteIds.add(rs.getInt(1));
custIds.add(rs.getInt(2));
}
if (CollectionsUtils.isNullOrEmpty(purgeSiteIds)) {
return null;
}
int totalDeletes = 0;
for (List<Integer> ids : ChunkedListIterator.iterable(purgeSiteIds, BATCH_SIZE)) {
logPoliciesUsingRemovedTopologyObjs(
"Purging ", "site", ids, IUserManager.PROP_ROUTING_GROUP_ID, c);
// purge servers
String idList = QueryUtils.literal(ids);
query = "delete from dat_customer_sites where site_id in " + idList;
s_logger.debug(query);
totalDeletes += stmt.executeUpdate(query);
// don't purge constraints because the scope of the constraints will expand. Worst case,
// when all constraints are deleted, a saved user set will have global scope and this
// is definitely not what the customer wants.
}
s_logger.info("Purged " + totalDeletes + " sites for " + custIds.size() + " customers");
return custIds;
} finally {
if (stmt != null) {
stmt.close();
}
if (rs != null) {
rs.close();
}
}
}
/**
* Returns the next value from the named sequence. Throws SQLException if the query fails or
* doesn't return any rows. The query is <code>select nextval('<i>seqName</i>')</code>.
*/
private static int getNextFromSequence(String seqName, Connection c) throws SQLException {
Statement st = null;
ResultSet rs = null;
try {
st = c.createStatement();
rs = st.executeQuery("select nextval('" + seqName + "')");
if (rs.next()) {
return rs.getInt(1);
} else {
throw new SQLException("Couldn't get new siteID from sequence '" + seqName + "'.");
}
} finally {
if (rs != null) {
rs.close();
}
if (st != null) {
st.close();
}
}
}