*
* @param search the search.
* @return the collection of conversations that match the search.
*/
private Collection<Conversation> databaseSearch(ArchiveSearch search) {
CachedPreparedStatement cachedPstmt = new CachedPreparedStatement();
// Build the SQL
StringBuilder query = new StringBuilder(160);
query.append("SELECT DISTINCT ofConversation.conversationID");
Collection<JID> participants = search.getParticipants();
boolean filterParticipants = !participants.isEmpty();
boolean filterDate = search.getDateRangeMin() != null || search.getDateRangeMax() != null;
boolean filterTimestamp = search.getIncludeTimestamp() != null;
boolean filterRoom = search.getRoom() != null;
// SELECT -- need to add value that we sort on. We always sort on date since that's
// the only valid current option for non-keyword searches.
query.append(", ofConversation.startDate");
// FROM -- values (in addition to jiveThread)
query.append(" FROM ofConversation");
if (filterParticipants) {
for (int i=0; i < participants.size(); i++) {
query.append(", ofConParticipant participant").append(i);
}
}
// WHERE BLOCK
boolean whereSet = false;
// See if we need to match against external conversations.
if (search.isExternalWildcardMode() && search.getParticipants().size() != 2) {
query.append(" WHERE isExternal=?");
cachedPstmt.addInt(1);
whereSet = true;
}
// Participants
if (filterParticipants) {
Iterator<JID> iter = participants.iterator();
for (int i=0; i < participants.size(); i++) {
if (!whereSet) {
query.append(" WHERE");
whereSet = true;
}
else {
query.append(" AND");
}
query.append(" ofConversation.conversationID=participant").append(i).append(".conversationID");
query.append(" AND ");
query.append("participant").append(i).append(".bareJID=?");
String partJID = iter.next().toString();
cachedPstmt.addString(partJID);
}
}
// Creation date range
if (filterDate) {
if (search.getDateRangeMin() != null) {
if (!whereSet) {
query.append(" WHERE");
whereSet = true;
}
else {
query.append(" AND");
}
query.append(" ofConversation.startDate >= ?");
cachedPstmt.addLong(search.getDateRangeMin().getTime());
}
if (search.getDateRangeMax() != null) {
if (!whereSet) {
query.append(" WHERE");
whereSet = true;
}
else {
query.append(" AND");
}
query.append(" ofConversation.startDate <= ?");
cachedPstmt.addLong(search.getDateRangeMax().getTime());
}
}
// Check if conversations have to happen at a given point in time
if (filterTimestamp) {
if (!whereSet) {
query.append(" WHERE");
whereSet = true;
}
else {
query.append(" AND");
}
query.append(" ofConversation.startDate <= ?");
cachedPstmt.addLong(search.getIncludeTimestamp().getTime());
query.append(" AND");
query.append(" ofConversation.lastActivity >= ?");
cachedPstmt.addLong(search.getIncludeTimestamp().getTime());
}
// Filter by room
if (filterRoom) {
if (!whereSet) {
query.append(" WHERE");
whereSet = true;
}
else {
query.append(" AND");
}
query.append(" ofConversation.room = ?");
cachedPstmt.addString(search.getRoom().toString());
}
// ORDER BY
query.append(" ORDER BY ofConversation.startDate");
if (search.getSortOrder() == ArchiveSearch.SortOrder.descending) {
query.append(" DESC");
}
else {
query.append(" ASC");
}
int startIndex = search.getStartIndex();
int numResults = search.getNumResults();
if (numResults != ArchiveSearch.NULL_INT) {
// MySQL optimization: use the LIMIT command to tell the database how many
// rows we need returned. The syntax is LIMIT [offset],[rows]
if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.mysql) {
query.append(" LIMIT ").append(startIndex).append(",").append(numResults);
}
// PostgreSQL optimization: use the LIMIT command to tell the database how many
// rows we need returned. The syntax is LIMIT [rows] OFFSET [offset]
else if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.postgresql) {
query.append(" LIMIT ").append(numResults).append(" OFFSET ").append(startIndex);
}
}
// Set the database query string.
cachedPstmt.setSQL(query.toString());
List<Long> conversationIDs = new ArrayList<Long>();
// Get all matching conversations from the database.
Connection con = null;
PreparedStatement pstmt = null;
try {
con = DbConnectionManager.getConnection();
pstmt = DbConnectionManager.createScrollablePreparedStatement(con, cachedPstmt.getSQL());
cachedPstmt.setParams(pstmt);
// Set the maximum number of rows to end at the end of this block.
// A MySQL optimization using the LIMIT command is part of the SQL.
// Therefore, we can skip this call on MySQL.
if (DbConnectionManager.getDatabaseType() != DbConnectionManager.DatabaseType.mysql
&& DbConnectionManager.getDatabaseType() != DbConnectionManager.DatabaseType.postgresql)