Thursday, June 16, 2011

OpenFire, Pidgin, and MySQL

Realized this week that I'm extremely rusty with MySQL queries.  Was
chatting with bearm on #openfire this week, troubleshooting an issue
with a "last logged in" feature that he's working on.  Turns out that,
if you leave the "Resource" field blank in the Pidgin client, Pidgeon
or Openfire autogenerates a random string to put there.  Trouble is,
that string is unique for each session.  This causes the userStatus
field to contain multiple entries for the same user, when there should
only be one.

There's two fixes for this issue:

  1. if there's only a handful of users, have them enter something in the client's resource field, or 
  2. if there's a large number of users, write a work-around to ignore the issue.  

Bearm had the latter issue.  Because he wanted to list all users, even 
ones that had never connected with a Jabber client, we had to come
up with a MySQL query that would include all system users and would
filter them so that only the most recent incidence would be shown (i.e.,
the resource field would be ignored).  The following query seems to fit
the bill: 


select u.username,us1.lastLoginDate from (ofUser as u left join
ofGroupUser as gu on u.username=gu.username) left join userStatus as
us1 on u.username=us1.username left join userStatus as us2 on
(us1.username=us2.username and us1.lastLoginDate < us2.lastLoginDate)
where us2.username is NULL;

Thanks for hints for the above go to artful software for the "left self exclusion join".