Today early i was checking some archiving packages(XEP-0136) and then i realized that the count value returned on the XmppResultSet (XEP-0059) andthe total of conversation never match.
Looking into the queries in com.reucon.openfire.plugin.archive.impl.JdbcPersistenceManager i understood the reason. I would like to check with you if it’s a bug or i just didn’t understand how i should work.
JdbcPersistenceManager.java
public static final String SELECT_CONVERSATIONS = "SELECT DISTINCT " + "ofConversation.conversationID, " + "ofConversation.room, "
+ "ofConversation.isExternal, " + "ofConversation.startDate, " + "ofConversation.lastActivity, " + "ofConversation.messageCount, "
+ "ofConParticipant.joinedDate, " + "ofConParticipant.leftDate, " + "ofConParticipant.bareJID, " + "ofConParticipant.jidResource, "
+ "ofConParticipant.nickname, " + "ofMessageArchive.fromJID, " + "ofMessageArchive.toJID, " + "ofMessageArchive.sentDate, "
+ "ofMessageArchive.body " + "FROM ofConversation "
+ "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
+ "INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID"; public static final String COUNT_CONVERSATIONS = "SELECT COUNT(DISTINCT ofConversation.conversationID) FROM ofConversation "
+ "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
+ "INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID";
**Query sent to the database to get conversation count. **In my case it always 105
SELECT DISTINCT COUNT(DISTINCT ofConversation.conversationID) FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConParticipant.bareJID = '{email}' AND (ofMessageArchive.toJID = '{email}' OR ofMessageArchive.fromJID = '{email}')
**Query sent to the database to retrieve the conversation list. **In my case it returns 200 rows, if i remove the limit it return 947 rows.
SELECT DISTINCT ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.startDate, ofConversation.lastActivity, ofConversation.messageCount, ofConParticipant.joinedDate, ofConParticipant.leftDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofMessageArchive.fromJID, ofMessageArchive.toJID, ofMessageArchive.sentDate, ofMessageArchive.body FROM ofConversation INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID INNER JOIN ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID WHERE ofConParticipant.bareJID = '{email}'
AND (ofMessageArchive.toJID = '{email}' OR ofMessageArchive.fromJID = '{email}')
ORDER BY ofConversation.conversationID LIMIT 200 OFFSET 0
It happens because it sent a row for each row of ofMessageArchive. I really don’t know if it’s right and didn’t find how configure it on the panel but what i really want is receive just a chat element for conversation.
Anyway to fix it for me i’ve change findConversation method, the final code is below.
public Collection<Conversation> findConversations(Date startDate, Date endDate, String ownerJid, String withJid, XmppResultSet xmppResultSet) {
final HashMap<Long, Conversation> conversations;
final StringBuilder querySB;
final StringBuilder whereSB;
final StringBuilder limitSB; conversations = new HashMap<Long, Conversation>(); querySB = new StringBuilder(SELECT_CONVERSATIONS);
whereSB = new StringBuilder();
limitSB = new StringBuilder(); startDate = getAuditedStartDate(startDate);
if (startDate != null) {
appendWhere(whereSB, CONVERSATION_START_TIME, " >= ?");
}
if (endDate != null) {
appendWhere(whereSB, CONVERSATION_END_TIME, " <= ?");
} if (ownerJid != null) {
appendWhere(whereSB, CONVERSATION_OWNER_JID, " = ?");
}
if (withJid != null) {
appendWhere(whereSB, CONVERSATION_WITH_JID);
} if (xmppResultSet != null) {
Integer firstIndex = null;
int max = xmppResultSet.getMax() != null ? xmppResultSet.getMax() : DEFAULT_MAX; xmppResultSet.setCount(countConversations(startDate, endDate, ownerJid, withJid, whereSB.toString()));
if (xmppResultSet.getIndex() != null) {
firstIndex = xmppResultSet.getIndex();
} else if (xmppResultSet.getAfter() != null) {
firstIndex = countConversationsBefore(startDate, endDate, ownerJid, withJid, xmppResultSet.getAfter(), whereSB.toString());
firstIndex += 1;
} else if (xmppResultSet.getBefore() != null) {
firstIndex = countConversationsBefore(startDate, endDate, ownerJid, withJid, xmppResultSet.getBefore(), whereSB.toString());
firstIndex -= max;
if (firstIndex < 0) {
firstIndex = 0;
}
}
firstIndex = firstIndex != null ? firstIndex : 0; limitSB.append(" LIMIT ").append(max);
limitSB.append(" OFFSET ").append(firstIndex);
xmppResultSet.setFirstIndex(firstIndex);
} if (whereSB.length() != 0) {
querySB.append(" WHERE ").append(whereSB);
}
//Group by added, after that i'll just receive a row per conversation
querySB.append(" GROUP BY ").append(CONVERSATION_ID);
querySB.append(" ORDER BY ").append(CONVERSATION_ID);
querySB.append(limitSB); Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = DbConnectionManager.getConnection();
pstmt = con.prepareStatement(querySB.toString());
bindConversationParameters(startDate, endDate, ownerJid, withJid, pstmt);
rs = pstmt.executeQuery();
Log.debug("findConversations: SELECT_CONVERSATIONS: " + pstmt.toString());
while (rs.next()) {
Conversation conv = extractConversation(rs);
conversations.put(conv.getId(), conv);
}
} catch (SQLException sqle) {
Log.error("Error selecting conversations", sqle);
} finally {
DbConnectionManager.closeConnection(rs, pstmt, con);
} if (xmppResultSet != null && conversations.size() > 0) {
ArrayList<Long> sortedConvKeys = new ArrayList<Long>(conversations.keySet());
Collections.sort(sortedConvKeys);
xmppResultSet.setFirst(sortedConvKeys.get(0));
xmppResultSet.setLast(sortedConvKeys.get(sortedConvKeys.size() - 1));
}
return conversations.values();
}