RosterItemProvider.java 15.1 KB
Newer Older
1 2 3 4 5
/**
 * $RCSfile$
 * $Revision: 1751 $
 * $Date: 2005-08-07 20:08:47 -0300 (Sun, 07 Aug 2005) $
 *
6
 * Copyright (C) 2005-2008 Jive Software. All rights reserved.
7
 *
8 9 10 11 12 13 14 15 16 17 18
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
19 20
 */

21
package org.jivesoftware.openfire.roster;
22

23 24 25 26 27 28 29 30 31 32 33
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

34 35
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.database.SequenceManager;
36 37
import org.jivesoftware.openfire.user.UserAlreadyExistsException;
import org.jivesoftware.openfire.user.UserNotFoundException;
38 39
import org.jivesoftware.util.JiveConstants;
import org.jivesoftware.util.LocaleUtils;
40 41
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
import org.xmpp.packet.JID;

/**
 * Defines the provider methods required for creating, reading, updating and deleting roster
 * items.<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.
 *
 * @author Iain Shigeoka
 */
public class RosterItemProvider {

58 59
	private static final Logger Log = LoggerFactory.getLogger(RosterItemProvider.class);

60
    private static final String CREATE_ROSTER_ITEM =
61
            "INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) " +
62 63
            "VALUES (?, ?, ?, ?, ?, ?, ?)";
    private static final String UPDATE_ROSTER_ITEM =
64
            "UPDATE ofRoster SET sub=?, ask=?, recv=?, nick=? WHERE rosterID=?";
65
    private static final String DELETE_ROSTER_ITEM_GROUPS =
66
            "DELETE FROM ofRosterGroups WHERE rosterID=?";
67
    private static final String CREATE_ROSTER_ITEM_GROUPS =
68
            "INSERT INTO ofRosterGroups (rosterID, rank, groupName) VALUES (?, ?, ?)";
69
    private static final String DELETE_ROSTER_ITEM =
70
            "DELETE FROM ofRoster WHERE rosterID=?";
71
    private static final String LOAD_USERNAMES =
72
            "SELECT DISTINCT username from ofRoster WHERE jid=?";
73
    private static final String COUNT_ROSTER_ITEMS =
74
            "SELECT COUNT(rosterID) FROM ofRoster WHERE username=?";
75
     private static final String LOAD_ROSTER =
76
             "SELECT jid, rosterID, sub, ask, recv, nick FROM ofRoster WHERE username=?";
77
    private static final String LOAD_ROSTER_ITEM_GROUPS =
78
            "SELECT rosterID,groupName FROM ofRosterGroups";
79 80 81 82 83 84 85 86 87 88 89 90 91


    private static RosterItemProvider instance = new RosterItemProvider();

    public static RosterItemProvider getInstance() {
        return instance;
    }

    /**
     * Creates a new roster item for the given user (optional operation).<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
92
     * method will be cached by Openfire. In some cases, the roster item passed in will be passed
93 94 95 96
     * 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>
     *
Matt Tucker's avatar
Matt Tucker committed
97 98 99 100
     * @param username the username of the user/chatbot that owns the roster item.
     * @param item the settings for the roster item to create.
     * @return the new roster item.
     * @throws UserAlreadyExistsException if a roster item with the username already exists. 
101 102 103 104 105 106 107 108
     */
    public RosterItem createItem(String username, RosterItem item)
            throws UserAlreadyExistsException
    {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            long rosterID = SequenceManager.nextID(JiveConstants.ROSTER);
109
            con = DbConnectionManager.getConnection();
110 111 112 113 114 115 116 117 118 119 120 121 122 123
            pstmt = con.prepareStatement(CREATE_ROSTER_ITEM);
            pstmt.setString(1, username);
            pstmt.setLong(2, rosterID);
            pstmt.setString(3, item.getJid().toBareJID());
            pstmt.setInt(4, item.getSubStatus().getValue());
            pstmt.setInt(5, item.getAskStatus().getValue());
            pstmt.setInt(6, item.getRecvStatus().getValue());
            pstmt.setString(7, item.getNickname());
            pstmt.executeUpdate();

            item.setID(rosterID);
            insertGroups(rosterID, item.getGroups().iterator(), con);
        }
        catch (SQLException e) {
124
            Log.warn("Error trying to insert a new row in ofRoster", e);
125 126 127
            throw new UserAlreadyExistsException(item.getJid().toBareJID());
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
128
            DbConnectionManager.closeConnection(pstmt, con);
129 130 131 132 133 134 135 136
        }
        return item;
    }

    /**
     * Update the roster item in storage with the information contained in the given item
     * (optional operation).<p>
     *
137
     * If you don't want roster items edited through openfire, throw UnsupportedOperationException.
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157
     *
     * @param username the username of the user/chatbot that owns the roster item
     * @param item   The roster item to update
     * @throws UserNotFoundException If no entry could be found to update
     */
    public void updateItem(String username, RosterItem item) throws UserNotFoundException {
        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);
            pstmt.executeUpdate();
            // Close now the statement (do not wait to be GC'ed)
158
            DbConnectionManager.fastcloseStmt(pstmt);
159 160 161 162 163 164 165 166 167 168 169 170

            // Delete old group list
            pstmt = con.prepareStatement(DELETE_ROSTER_ITEM_GROUPS);
            pstmt.setLong(1, rosterID);
            pstmt.executeUpdate();

            insertGroups(rosterID, item.getGroups().iterator(), con);
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
171
            DbConnectionManager.closeConnection(pstmt, con);
172 173 174 175 176 177
        }
    }

    /**
     * Delete the roster item with the given itemJID for the user (optional operation).<p>
     *
178
     * If you don't want roster items deleted through openfire, throw
179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195
     * UnsupportedOperationException.
     *
     * @param username the long ID of the user/chatbot that owns the roster item
     * @param rosterItemID The roster item to delete
     */
    public void deleteItem(String username, long rosterItemID) {
        // 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);
            pstmt.executeUpdate();
            // Close now the statement (do not wait to be GC'ed)
196
            DbConnectionManager.fastcloseStmt(pstmt);
197 198 199 200 201 202 203 204 205 206 207

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

            pstmt.setLong(1, rosterItemID);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
208
            DbConnectionManager.closeConnection(pstmt, con);
209 210 211 212 213 214 215 216 217 218 219 220 221
        }
    }

    /**
     * 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.
     */
    public Iterator<String> getUsernames(String jid) {
        List<String> answer = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
Matt Tucker's avatar
Matt Tucker committed
222
        ResultSet rs = null;
223 224 225 226
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_USERNAMES);
            pstmt.setString(1, jid);
Matt Tucker's avatar
Matt Tucker committed
227
            rs = pstmt.executeQuery();
228 229 230 231 232 233 234 235
            while (rs.next()) {
                answer.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
236
            DbConnectionManager.closeConnection(rs, pstmt, con);
237 238 239 240 241 242 243 244 245 246 247 248 249 250
        }
        return answer.iterator();
    }

    /**
     * Obtain a count of the number of roster items available for the given user.
     *
     * @param username the username of the user/chatbot that owns the roster items
     * @return The number of roster items available for the user
     */
    public int getItemCount(String username) {
        int count = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
Matt Tucker's avatar
Matt Tucker committed
251
        ResultSet rs = null;
252 253 254 255
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(COUNT_ROSTER_ITEMS);
            pstmt.setString(1, username);
Matt Tucker's avatar
Matt Tucker committed
256
            rs = pstmt.executeQuery();
257 258 259 260 261 262 263 264
            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
265
            DbConnectionManager.closeConnection(rs, pstmt, con);
266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281
        }
        return count;
    }

    /**
     * Retrieve an iterator of RosterItems for the given user.<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
     */
    public Iterator<RosterItem> getItems(String username) {
        LinkedList<RosterItem> itemList = new LinkedList<RosterItem>();
282
        Map<Long, RosterItem> itemsByID = new HashMap<Long, RosterItem>();
283 284
        Connection con = null;
        PreparedStatement pstmt = null;
285
        ResultSet rs = null;
286 287 288 289 290
        try {
            // Load all the contacts in the roster
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_ROSTER);
            pstmt.setString(1, username);
Matt Tucker's avatar
Matt Tucker committed
291
            rs = pstmt.executeQuery();
292 293 294 295 296 297 298 299 300 301 302
            while (rs.next()) {
                // Create a new RosterItem (ie. user contact) from the stored information
                RosterItem item = new RosterItem(rs.getLong(2),
                        new JID(rs.getString(1)),
                        RosterItem.SubType.getTypeFromInt(rs.getInt(3)),
                        RosterItem.AskType.getTypeFromInt(rs.getInt(4)),
                        RosterItem.RecvType.getTypeFromInt(rs.getInt(5)),
                        rs.getString(6),
                        null);
                // Add the loaded RosterItem (ie. user contact) to the result
                itemList.add(item);
303
                itemsByID.put(item.getID(), item);
304
            }
305
            // Close the statement and result set
306
            DbConnectionManager.fastcloseStmt(rs, pstmt);
307
            // Set null to pstmt to be sure that it's not closed twice. It seems that
Matt Tucker's avatar
Matt Tucker committed
308
            // Sybase driver is raising an error when trying to close an already closed statement.
309 310
            // it2000 comment: TODO interesting, that's the only place with the sybase fix
            // it2000 comment: one should move this in closeStatement()
311
            pstmt = null;
312 313

            // Load the groups for the loaded contact
314 315 316 317 318 319 320 321 322
            if (!itemList.isEmpty()) {
                StringBuilder sb = new StringBuilder(100);
                sb.append(LOAD_ROSTER_ITEM_GROUPS).append(" WHERE rosterID IN (");
                for (RosterItem item : itemList) {
                    sb.append(item.getID()).append(",");
                }
                sb.setLength(sb.length()-1);
                sb.append(") ORDER BY rosterID, rank");
                pstmt = con.prepareStatement(sb.toString());
323 324
                rs = pstmt.executeQuery();
                while (rs.next()) {
325
                    itemsByID.get(rs.getLong(1)).getGroups().add(rs.getString(2));
326 327
                }
            }
328 329 330 331 332
        }
        catch (SQLException e) {
            Log.error(LocaleUtils.getLocalizedString("admin.error"), e);
        }
        finally {
333
            DbConnectionManager.closeConnection(rs, pstmt, con);
334 335 336 337 338 339 340
        }
        return itemList.iterator();
    }

    /**
     * Insert the groups into the given roster item.
     *
Matt Tucker's avatar
Matt Tucker committed
341 342 343 344
     * @param rosterID the roster ID of the item the groups belong to
     * @param iter an iterator over the group names to insert
     * @param con the database connection to use for the operation.
     * @throws SQLException if an SQL exception occurs.
345 346 347 348 349 350 351 352 353
     */
    private void insertGroups(long rosterID, Iterator<String> iter, Connection con) throws SQLException
    {
        PreparedStatement pstmt = null;
        try {
            pstmt = con.prepareStatement(CREATE_ROSTER_ITEM_GROUPS);
            pstmt.setLong(1, rosterID);
            for (int i = 0; iter.hasNext(); i++) {
                pstmt.setInt(2, i);
Matt Tucker's avatar
Matt Tucker committed
354
                String groupName = iter.next();
Gaston Dombiak's avatar
Gaston Dombiak committed
355
                pstmt.setString(3, groupName);
356 357 358 359
                try {
                    pstmt.executeUpdate();
                }
                catch (SQLException e) {
360 361 362
					Log.error("Unable to insert group with name '" + groupName
							+ "' (rank: '" + i + "')into roster with id '"
							+ rosterID + "'.", e);
363 364 365 366
                }
            }
        }
        finally {
Matt Tucker's avatar
Matt Tucker committed
367
            DbConnectionManager.closeStatement(pstmt);
368 369 370
        }
    }
}