Monitoring plugin list request with max tag does not work correctly

Ivan_A_Shmakov · June 29, 2015, 3:29pm

There is 10 messages in archive

Client:

<iq type="get" id="list12">
     <list xmlns="urn:xmpp:archive" with="room@conference.voximplant.com">
          <set xmlns="http://jabber.org/protocol/rsm"></set>
     </list>
</iq>

Result:

<iq type="result" id="list12" to="111(app.shmakov)@voximplant.com/QIP">
     <list xmlns="urn:xmpp:archive">
          <chat with="room@conference.voximplant.com" start="2015-06-03T12:05:19.525Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T11:10:59.872Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T12:14:14.979Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T12:11:36.096Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T13:31:19.933Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T12:39:07.797Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T13:55:33.761Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T13:49:50.126Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T14:20:39.356Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T10:49:32.785Z" />
          <set xmlns="http://jabber.org/protocol/rsm">
               <first index="0">15</first>
               <last>24</last>
               <count>10</count>
          </set>
</list>
</iq>

But when I add tag, result is not correct

Client:

<iq type="get" id="list13">
      <list xmlns="urn:xmpp:archive" with="room@conference.voximplant.com">
     <set xmlns="http://jabber.org/protocol/rsm">
          <max>5</max>
     </set>
</list>
</iq>

Result:

<iq type="result" id="list13" to="111(app.shmakov)@voximplant.com/QIP">
     <list xmlns="urn:xmpp:archive">
          <chat with="room@conference.voximplant.com" start="2015-06-03T12:05:19.525Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T11:10:59.872Z" />
          <chat with="room@conference.voximplant.com" start="2015-06-03T10:49:32.785Z" />
          <set xmlns="http://jabber.org/protocol/rsm">
               <first index="0">15</first>
               <last>17</last>
               <count>10</count>
          </set>
     </list>
</iq>

There is 3 result, but should be 5 (!!!)

XEP-0059: Result Set Management

2. Use Cases

2.1 Limiting the Number of Items

In order to limit the number of items of a result set to be returned, the requesting entity specifies a request type of “set” and the maximum size of the desired subset (via the XML character data of the element):

Example 1. Requesting a Limit to the Result Set

<iq type='set' from='stpeter@jabber.org/roundabout' to='users.jabber.org' id='limit1'>
  <query xmlns='jabber:iq:search'>
    <nick>Pete</nick>
    <set xmlns='http://jabber.org/protocol/rsm'>
      <max>10</max>
    </set>
  </query>
</iq>

The responding entity then returns the first items of the result set in order. The number of items is limited to the requested size:

Example 2. Returning a Limited Result Set

<iq type='result' from='users.jabber.org' to='stpeter@jabber.org/roundabout' id='limit1'>
  <query xmlns='jabber:iq:search'>
    <item jid='stpeter@jabber.org'>
      <first>Peter</first>
      <last>Saint-Andre</last>
      <nick>Pete</nick>
    </item>
    .
    [8 more items]
    .
    <item jid='peterpan@neverland.lit'>
      <first>Peter</first>
      <last>Pan</last>
      <nick>Pete</nick>
    </item>
  </query>
</iq>

Ivan_A_Shmakov · June 29, 2015, 4:27pm

by the way, aslo i note that does not order by date, but in postgres it’s ordered by date and id

15;"room@conference.voximplant.com";0;1433328572785;1433328572786;1

16;"room@conference.voximplant.com";0;1433329859872;1433330369666;2

17;"room@conference.voximplant.com";0;1433333119525;1433333119526;1

18;"room@conference.voximplant.com";0;1433333496096;1433333496097;1

19;"room@conference.voximplant.com";0;1433333654979;1433333662431;2

20;"room@conference.voximplant.com";0;1433335147797;1433335147798;1

21;"room@conference.voximplant.com";0;1433338279933;1433338307700;3

22;"room@conference.voximplant.com";0;1433339390126;1433339390127;1

23;"room@conference.voximplant.com";0;1433339733761;1433339751959;2

24;"room@conference.voximplant.com";0;1433341239356;1433341593207;4

Ivan_A_Shmakov · July 3, 2015, 12:28pm

So. The problem is in SQL of SELECT_CONVERSATIONS at findConversations method

This sql allow resultset with same conversationID, because use can exit and coming into chatroom.

As we limit in SQL and select from index in SQL next we use Tree to remove duplication and page mechanism and max limitation does not work correct.
I need some time to understand to fix this problem with little changes…

Ivan_A_Shmakov · July 3, 2015, 2:33pm

So. Let’s try to fix this problem with following changes. At first view it fix problem, but need to check.

com/reucon/openfire/plugin/archive/impl/JdbcPersistenceManager.java:

@@ -45,17 +45,28 @@

// "SELECT messageId,time,direction,type,subject,body "
// + “FROM archiveMessages WHERE conversationId = ? ORDER BY time”;

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, "
- "ofConParticipant.bareJID as fromJID, "
- "ofMessageArchive.toJID "
- "FROM ofConversation "
- "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
- "INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive "
- "union all "
- “SELECT conversationID, fromJID as toJID FROM ofMessageArchive) ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID”;

public static final String SELECT_CONVERSATIONS = "SELECT "
- "ofConversation.conversationID, " + " ofConversation.room, " + "ofConversation.isExternal, "+ "ofConversation.lastActivity, "
- "ofConversation.messageCount, " + "ofConversation.startDate, " + "ofConParticipant.bareJID, " + “ofConParticipant.jidResource,”
- "ofConParticipant.nickname, " + "ofConParticipant.bareJID AS fromJID, " + "ofMessageArchive.toJID, "
- "min(ofConParticipant.joinedDate) AS startDate, " + "max(ofConParticipant.leftDate) as leftDate "
- "FROM ofConversation "
- "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
- "INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive union all SELECT conversationID, fromJID as toJID FROM ofMessageArchive) ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID ";
public static final String SELECT_CONVERSATIONS_GROUP_BY = " GROUP BY ofConversation.conversationID, ofConversation.room, ofConversation.isExternal, ofConversation.lastActivity, ofConversation.messageCount, ofConversation.startDate, ofConParticipant.bareJID, ofConParticipant.jidResource, ofConParticipant.nickname, ofConParticipant.bareJID, ofMessageArchive.toJID";
// 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, "
// + "ofConParticipant.bareJID as fromJID, "
// + "ofMessageArchive.toJID "
// + "FROM ofConversation "
// + "INNER JOIN ofConParticipant ON ofConversation.conversationID = ofConParticipant.conversationID "
// + "INNER JOIN (SELECT conversationID, toJID FROM ofMessageArchive "
// + "union all "
// + “SELECT conversationID, fromJID as toJID FROM ofMessageArchive) ofMessageArchive ON ofConParticipant.conversationID = ofMessageArchive.conversationID”;

// public static final String SELECT_CONVERSATIONS =
// “SELECT c.conversationId,c.startTime,c.endTime,c.ownerJid,c.ownerResource,c.withJid,c.w ithResource,”
@@ -231,6 +242,9 @@ public Date getAuditedStartDate(Date startDate) {
if (whereSB.length() != 0) {
querySB.append(" WHERE ").append(whereSB);
}
querySB.append(SELECT_CONVERSATIONS_GROUP_BY);
if (DbConnectionManager.getDatabaseType() == DbConnectionManager.DatabaseType.sqlserver) {
querySB.insert(0,“SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY “+CONVERSATION_ID+”) AS RowNum FROM ( “);
querySB.append(”) ofConversation ) t2 WHERE RowNum”);