powered by Jive Software

Archive Conversations list and count doesn't match

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();
}

Would you mind creating a diff of code that you changed? This makes it easier for us to spot the changes.

7d6
< import java.text.DateFormat;
10d8
< import java.util.Collections;
12d9
< import java.util.HashMap;
15d11
< 20c16,17
< import org.jivesoftware.util.Log;
---
> import org.slf4j.Logger;
> import org.slf4j.LoggerFactory;
22d18
< 34a31,32
> >           private static final Logger          Log = LoggerFactory.getLogger(JdbcPersistenceManager.class);
136,137c134
<                     long maxRetrievable = JiveGlobals.getIntProperty("conversation.maxRetrievable", ConversationManager.DEFAULT_MAX_RETRIEVABLE)
<                                         * JiveConstants.DAY;
---
>                     long maxRetrievable = JiveGlobals.getIntProperty("conversation.maxRetrievable", ConversationManager.DEFAULT_MAX_RETRIEVABLE) * JiveConstants.DAY;
146a144,145
>                     } else {
>                               result = startDate;
152c151
<                     final HashMap<Long, Conversation> conversations;
---
>                     final List<Conversation> conversations;
157c156
<                     conversations = new HashMap<Long, Conversation>();
---
>                     conversations = new ArrayList<Conversation>();
163a163,167
> >                     if (ownerJid != null && withJid != null && ownerJid.contains(withJid)) {
>                               withJid = null;
>                     }
> 204c208,209
<                     querySB.append(" ORDER BY ").append(CONVERSATION_ID);
---
>                     querySB.append(" GROUP BY ").append(CONVERSATION_ID);
>                     querySB.append(" ORDER BY ").append(CONVERSATION_ID).append(" ").append(JiveGlobals.getProperty("conversation.list.order", ""));
218c223
<                                         conversations.put(conv.getId(), conv);
---
>                                         conversations.add(conv);
220c225,226
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
222c228,229
<                     } finally {
---
>                     }
>                     finally {
226,230c233,235
<                     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));
---
>                     if (xmppResultSet != null && !conversations.isEmpty()) {
>                               xmppResultSet.setFirst(conversations.get(0).getId());
>                               xmppResultSet.setLast(conversations.get(conversations.size() - 1).getId());
232c237
<                     return conversations.values();
---
>                     return conversations;
266c271,272
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
269c275,276
<                     } finally {
---
>                     }
>                     finally {
300c307,308
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
303c311,312
<                     } finally {
---
>                     }
>                     finally {
308,309c317
<           private int bindConversationParameters(Date startDate, Date endDate, String ownerJid, String withJid, PreparedStatement pstmt)
<                               throws SQLException {
---
>           private int bindConversationParameters(Date startDate, Date endDate, String ownerJid, String withJid, PreparedStatement pstmt) throws SQLException {
346c354,355
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
348c357,358
<                     } finally {
---
>                     }
>                     finally {
360,362c370
<                     if (conversationIds.isEmpty()) {
<                               return conversations;
<                     }
---
>                     if (conversationIds.isEmpty()) { return conversations; }
393c401,402
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
395c404,405
<                     } finally {
---
>                     }
>                     finally {
440a451
> 442a454
> 451a464
> 491c504,505
<                     } catch (SQLException sqle) {
---
>                     }
>                     catch (SQLException sqle) {
493c507,508
<                     } finally {
---
>                     }
>                     finally {

This patch contains a lot of noise from code formatting changes. It’s hard to spot the actual semantic differences. Please generate one that sticks to the original code formating rules and only contains semantic code changes.

7d6
< import java.text.DateFormat;
10d8
< import java.util.Collections;
12d9
< import java.util.HashMap;
15d11
< 20c16,17
< import org.jivesoftware.util.Log;
---
> import org.slf4j.Logger;
> import org.slf4j.LoggerFactory;
22d18
< 34a31,32
> >           private static final Logger          Log = LoggerFactory.getLogger(JdbcPersistenceManager.class);
152c150
<                     final HashMap<Long, Conversation> conversations;
---
>                     final List<Conversation> conversations;
157c155
<                     conversations = new HashMap<Long, Conversation>();
---
>                     conversations = new ArrayList<Conversation>();
163a162,166
> >                     if (ownerJid != null && withJid != null && ownerJid.contains(withJid)) {
>                               withJid = null;
>                     }
> 204c207,208
<                     querySB.append(" ORDER BY ").append(CONVERSATION_ID);
---
>                     querySB.append(" GROUP BY ").append(CONVERSATION_ID);
>                     querySB.append(" ORDER BY ").append(CONVERSATION_ID).append(" ").append(JiveGlobals.getProperty("conversation.list.order", ""));
218c222
<                                         conversations.put(conv.getId(), conv);
---
>                                         conversations.add(conv);
226,230c230,232
<                     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));
---
>                     if (xmppResultSet != null && !conversations.isEmpty()) {
>                               xmppResultSet.setFirst(conversations.get(0).getId());
>                               xmppResultSet.setLast(conversations.get(conversations.size() - 1).getId());
232c234
<                     return conversations.values();
---
>                     return conversations;

Thanks (The patch also includes a change from the Log API, but the actual change is better visible)