RosterItemProvider.java 14.1 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4 5
/**
 * $RCSfile$
 * $Revision$
 * $Date$
 *
Matt Tucker's avatar
Matt Tucker committed
6
 * Copyright (C) 2004 Jive Software. All rights reserved.
Matt Tucker's avatar
Matt Tucker committed
7
 *
Matt Tucker's avatar
Matt Tucker committed
8 9
 * This software is published under the terms of the GNU Public License (GPL),
 * a copy of which is included in this distribution.
Matt Tucker's avatar
Matt Tucker committed
10
 */
Matt Tucker's avatar
Matt Tucker committed
11

Matt Tucker's avatar
Matt Tucker committed
12
package org.jivesoftware.messenger.roster;
Matt Tucker's avatar
Matt Tucker committed
13

Derek DeMoro's avatar
Derek DeMoro committed
14 15
import org.jivesoftware.messenger.user.UserAlreadyExistsException;
import org.jivesoftware.messenger.user.UserNotFoundException;
Matt Tucker's avatar
Matt Tucker committed
16 17
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.database.SequenceManager;
Derek DeMoro's avatar
Derek DeMoro committed
18 19
import org.jivesoftware.util.JiveConstants;
import org.jivesoftware.util.Log;
Matt Tucker's avatar
Matt Tucker committed
20
import org.jivesoftware.util.LocaleUtils;
Derek DeMoro's avatar
Derek DeMoro committed
21
import org.xmpp.packet.JID;
Matt Tucker's avatar
Matt Tucker committed
22

Matt Tucker's avatar
Matt Tucker committed
23 24 25 26 27 28 29 30 31
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;
import java.util.LinkedList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

Matt Tucker's avatar
Matt Tucker committed
32
/**
Matt Tucker's avatar
Matt Tucker committed
33 34 35 36 37 38 39
 * <p>Defines the provider methods required for creating, reading, updating and deleting roster items.</p>
 * <p/>
 * <p>Rosters are another user resource accessed via the user or chatbot's long ID. A user/chatbot may have
 * zero or more roster items and each roster item may have zero or more groups. Each roster item is
 * additionaly keyed on a XMPP jid. In most cases, the entire roster will be read in from memory and manipulated
 * or sent to the user. However some operations will need to retrive specific roster items rather than the
 * entire roster.</p>
Matt Tucker's avatar
Matt Tucker committed
40 41 42
 *
 * @author Iain Shigeoka
 */
Matt Tucker's avatar
Matt Tucker committed
43
public class RosterItemProvider {
Matt Tucker's avatar
Matt Tucker committed
44 45

    private static final String CREATE_ROSTER_ITEM =
46
            "INSERT INTO jiveRoster (username, rosterID, jid, sub, ask, recv, nick) " +
Matt Tucker's avatar
Matt Tucker committed
47
            "VALUES (?, ?, ?, ?, ?, ?, ?)";
Matt Tucker's avatar
Matt Tucker committed
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70
    private static final String UPDATE_ROSTER_ITEM =
            "UPDATE jiveRoster SET sub=?, ask=?, recv=?, nick=? WHERE rosterID=?";
    private static final String DELETE_ROSTER_ITEM_GROUPS =
            "DELETE FROM jiveRosterGroups WHERE rosterID=?";
    private static final String CREATE_ROSTER_ITEM_GROUPS =
            "INSERT INTO jiveRosterGroups (rosterID, rank, groupName) VALUES (?, ?, ?)";
    private static final String DELETE_ROSTER_ITEM =
            "DELETE FROM jiveRoster WHERE rosterID=?";
    private static final String LOAD_USERNAMES =
            "SELECT DISTINCT username from jiveRoster WHERE jid=?";
    private static final String COUNT_ROSTER_ITEMS =
            "SELECT COUNT(rosterID) FROM jiveRoster WHERE username=?";
     private static final String LOAD_ROSTER =
             "SELECT jid, rosterID, sub, ask, recv, nick FROM jiveRoster WHERE username=?";
    private static final String LOAD_ROSTER_ITEM_GROUPS =
            "SELECT groupName FROM jiveRosterGroups WHERE rosterID=? ORDER BY rank";


    private static RosterItemProvider instance = new RosterItemProvider();

    public static RosterItemProvider getInstance() {
        return instance;
    }
Matt Tucker's avatar
Matt Tucker committed
71

Derek DeMoro's avatar
Derek DeMoro committed
72 73 74 75 76 77 78 79 80 81 82 83
    /**
     * <p>Creates a new roster item for the given user (optional operation).</p>
     * <p/>
     * <p><b>Important!</b> The item passed as a parameter to this method is strictly a convenience for passing all
     * of the data needed for a new roster item. The roster item returned from the method will be cached by Messenger.
     * In some cases, the roster item passed in will be passed back out. However, if an implementation may
     * return RosterItems as a separate class (for example, a RosterItem that directly accesses the backend
     * storage, or one that is an object in an object database).
     * <p/>
     * <p>If you don't want roster items edited through messenger, throw UnsupportedOperationException.</p>
     *
     * @param username the username of the user/chatbot that owns the roster item
Matt Tucker's avatar
Matt Tucker committed
84
     * @param item the settings for the roster item to create
Derek DeMoro's avatar
Derek DeMoro committed
85 86
     * @return The created roster item
     */
87
    public RosterItem createItem(String username, RosterItem item)
Matt Tucker's avatar
Matt Tucker committed
88 89
            throws UserAlreadyExistsException
    {
Matt Tucker's avatar
Matt Tucker committed
90 91 92 93 94 95 96
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();

            long rosterID = SequenceManager.nextID(JiveConstants.ROSTER);
            pstmt = con.prepareStatement(CREATE_ROSTER_ITEM);
97
            pstmt.setString(1, username);
Matt Tucker's avatar
Matt Tucker committed
98
            pstmt.setLong(2, rosterID);
Derek DeMoro's avatar
Derek DeMoro committed
99
            pstmt.setString(3, item.getJid().toBareJID());
Matt Tucker's avatar
Matt Tucker committed
100 101 102 103
            pstmt.setInt(4, item.getSubStatus().getValue());
            pstmt.setInt(5, item.getAskStatus().getValue());
            pstmt.setInt(6, item.getRecvStatus().getValue());
            pstmt.setString(7, item.getNickname());
Derek DeMoro's avatar
Derek DeMoro committed
104
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
105

106
            item.setID(rosterID);
Matt Tucker's avatar
Matt Tucker committed
107 108 109
            insertGroups(rosterID, item.getGroups().iterator(), pstmt, con);
        }
        catch (SQLException e) {
Derek DeMoro's avatar
Derek DeMoro committed
110
            throw new UserAlreadyExistsException(item.getJid().toBareJID());
Matt Tucker's avatar
Matt Tucker committed
111 112
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
113 114 115 116
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
117
        }
118
        return item;
Matt Tucker's avatar
Matt Tucker committed
119 120
    }

Derek DeMoro's avatar
Derek DeMoro committed
121 122 123 124 125 126
    /**
     * <p>Update the roster item in storage with the information contained in the given item (optional operation).</p>
     * <p/>
     * <p>If you don't want roster items edited through messenger, throw UnsupportedOperationException.</p>
     *
     * @param username the username of the user/chatbot that owns the roster item
Matt Tucker's avatar
Matt Tucker committed
127 128
     * @param item   The roster item to update
     * @throws org.jivesoftware.messenger.user.UserNotFoundException         If no entry could be found to update
Derek DeMoro's avatar
Derek DeMoro committed
129
     */
130
    public void updateItem(String username, RosterItem item) throws UserNotFoundException {
Matt Tucker's avatar
Matt Tucker committed
131 132 133 134 135 136 137 138 139 140 141 142
        Connection con = null;
        PreparedStatement pstmt = null;
        long rosterID = item.getID();
        try {
            con = DbConnectionManager.getConnection();
            // Update existing roster item
            pstmt = con.prepareStatement(UPDATE_ROSTER_ITEM);
            pstmt.setInt(1, item.getSubStatus().getValue());
            pstmt.setInt(2, item.getAskStatus().getValue());
            pstmt.setInt(3, item.getRecvStatus().getValue());
            pstmt.setString(4, item.getNickname());
            pstmt.setLong(5, rosterID);
Derek DeMoro's avatar
Derek DeMoro committed
143
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
144 145 146 147

            // Delete old group list
            pstmt = con.prepareStatement(DELETE_ROSTER_ITEM_GROUPS);
            pstmt.setLong(1, rosterID);
Derek DeMoro's avatar
Derek DeMoro committed
148
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
149 150 151 152 153 154 155 156

            insertGroups(rosterID, item.getGroups().iterator(), pstmt, con);

        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
157 158 159 160
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
161 162 163
        }
    }

Derek DeMoro's avatar
Derek DeMoro committed
164 165 166 167 168
    /**
     * <p>Delete the roster item with the given itemJID for the user (optional operation).</p>
     * <p/>
     * <p>If you don't want roster items deleted through messenger, throw UnsupportedOperationException.</p>
     *
Matt Tucker's avatar
Matt Tucker committed
169
     * @param username the long ID of the user/chatbot that owns the roster item
Derek DeMoro's avatar
Derek DeMoro committed
170 171
     * @param rosterItemID The roster item to delete
     */
Matt Tucker's avatar
Matt Tucker committed
172
    public void deleteItem(String username, long rosterItemID) {
Matt Tucker's avatar
Matt Tucker committed
173 174 175 176 177 178 179 180 181
        // Only try to remove the user if they exist in the roster already:
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            // Remove roster groups
            pstmt = con.prepareStatement(DELETE_ROSTER_ITEM_GROUPS);

            pstmt.setLong(1, rosterItemID);
Derek DeMoro's avatar
Derek DeMoro committed
182
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
183 184 185 186 187

            // Remove roster
            pstmt = con.prepareStatement(DELETE_ROSTER_ITEM);

            pstmt.setLong(1, rosterItemID);
Derek DeMoro's avatar
Derek DeMoro committed
188
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
189 190 191 192 193
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
194 195 196 197
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
198 199 200
        }
    }

Derek DeMoro's avatar
Derek DeMoro committed
201 202 203 204 205 206
    /**
     * Returns an iterator on the usernames whose roster includes the specified JID.
     *
     * @param jid the jid that the rosters should include.
     * @return an iterator on the usernames whose roster includes the specified JID.
     */
207 208
    public Iterator<String> getUsernames(String jid) {
        List<String> answer = new ArrayList<String>();
Matt Tucker's avatar
Matt Tucker committed
209 210 211 212
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
213 214
            pstmt = con.prepareStatement(LOAD_USERNAMES);
            pstmt.setString(1, jid);
Matt Tucker's avatar
Matt Tucker committed
215 216
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
217
                answer.add(rs.getString(1));
Matt Tucker's avatar
Matt Tucker committed
218 219 220 221 222 223
            }
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
224 225 226 227
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
228
        }
229
        return answer.iterator();
Matt Tucker's avatar
Matt Tucker committed
230 231
    }

Derek DeMoro's avatar
Derek DeMoro committed
232 233 234 235 236 237
    /**
     * <p>Obtain a count of the number of roster items available for the given user.</p>
     *
     * @param username the username of the user/chatbot that owns the roster items
     * @return The number of roster items available for the user
     */
238
    public int getItemCount(String username) {
Matt Tucker's avatar
Matt Tucker committed
239 240 241 242 243 244
        int count = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(COUNT_ROSTER_ITEMS);
245
            pstmt.setString(1, username);
Matt Tucker's avatar
Matt Tucker committed
246 247 248 249 250 251 252 253 254
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
255 256 257 258
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
259 260 261 262
        }
        return count;
    }

Derek DeMoro's avatar
Derek DeMoro committed
263 264 265 266 267 268 269 270 271 272
    /**
     * <p>Retrieve an iterator of RosterItems for the given user.</p>
     * <p/>
     * <p>This method will commonly be called when a user logs in. The data will be cached
     * in memory when possible. However, some rosters may be very large so items may need
     * to be retrieved from the provider more frequently than usual for provider data.
     *
     * @param username the username of the user/chatbot that owns the roster items
     * @return An iterator of all RosterItems owned by the user
     */
273 274
    public Iterator getItems(String username) {
        LinkedList itemList = new LinkedList();
Matt Tucker's avatar
Matt Tucker committed
275 276 277 278 279
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_ROSTER);
280
            pstmt.setString(1, username);
Matt Tucker's avatar
Matt Tucker committed
281
            ResultSet rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
282 283
            // TODO: this code must be refactored ASAP. Not legal to have two open pstmts
            // TODO: on many databases.
Matt Tucker's avatar
Matt Tucker committed
284
            while (rs.next()) {
285
                RosterItem item = new RosterItem(rs.getLong(2),
Derek DeMoro's avatar
Derek DeMoro committed
286
                        new JID(rs.getString(1)),
Matt Tucker's avatar
Matt Tucker committed
287 288 289 290 291
                        RosterItem.SubType.getTypeFromInt(rs.getInt(3)),
                        RosterItem.AskType.getTypeFromInt(rs.getInt(4)),
                        RosterItem.RecvType.getTypeFromInt(rs.getInt(5)),
                        rs.getString(6),
                        null);
292
                Connection con2 = DbConnectionManager.getConnection();
Matt Tucker's avatar
Matt Tucker committed
293 294 295
                PreparedStatement gstmt = null;
                ResultSet gs = null;
                try {
296
                    gstmt = con2.prepareStatement(LOAD_ROSTER_ITEM_GROUPS);
Matt Tucker's avatar
Matt Tucker committed
297 298 299 300 301 302 303 304
                    gstmt.setLong(1, item.getID());
                    gs = gstmt.executeQuery();
                    while (gs.next()) {
                        item.getGroups().add(gs.getString(1));
                    }
                    itemList.add(item);
                }
                finally {
305
                    try { if (gstmt != null) { gstmt.close(); } }
Matt Tucker's avatar
Matt Tucker committed
306
                    catch (Exception e) { Log.error(e); }
307
                    try { if (con2 != null) { con2.close(); } }
Matt Tucker's avatar
Matt Tucker committed
308
                    catch (Exception e) { Log.error(e); }
Matt Tucker's avatar
Matt Tucker committed
309 310 311 312 313 314
                }
            }
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
Matt Tucker's avatar
Matt Tucker committed
315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338
        finally {
            try { if (pstmt != null) { pstmt.close(); } }
            catch (Exception e) { Log.error(e); }
            try { if (con != null) { con.close(); } }
            catch (Exception e) { Log.error(e); }
        }
        return itemList.iterator();
    }

    /**
     * <p>Insert the groups into the given roster item.</p>
     *
     * @param rosterID The roster ID of the item the groups belong to
     * @param iter     An iterator over the group names to insert
     */
    private void insertGroups(long rosterID, Iterator iter, PreparedStatement pstmt,
            Connection con) throws SQLException
    {
        try {
            pstmt = con.prepareStatement(CREATE_ROSTER_ITEM_GROUPS);
            pstmt.setLong(1, rosterID);
            for (int i = 0; iter.hasNext(); i++) {
                pstmt.setInt(2, i);
                pstmt.setString(3, (String)iter.next());
339 340 341 342
                try {
                    pstmt.executeUpdate();
                }
                catch (SQLException e) {
343
                    Log.error(e);
344
                }
Matt Tucker's avatar
Matt Tucker committed
345 346
            }
        }
Matt Tucker's avatar
Matt Tucker committed
347
        finally {
Derek DeMoro's avatar
Derek DeMoro committed
348 349 350 351 352 353 354 355
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
            }
            catch (Exception e) {
                Log.error(e);
            }
Matt Tucker's avatar
Matt Tucker committed
356 357
        }
    }
Matt Tucker's avatar
Matt Tucker committed
358
}