DefaultGroupProvider.java 17.3 KB
Newer Older
1
/*
2
 * Copyright (C) 2004-2008 Jive Software. All rights reserved.
3
 *
4 5 6 7 8 9 10 11 12 13 14
 * 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.
15 16
 */

17
package org.jivesoftware.openfire.group;
18

19 20 21 22
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
23 24 25 26
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
27

28 29 30 31 32 33
import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.XMPPServer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;

34 35 36 37 38
/**
 * Database implementation of the GroupManager interface.
 *
 * @author Matt Tucker
 */
39
public class DefaultGroupProvider extends AbstractGroupProvider {
40

41 42
	private static final Logger Log = LoggerFactory.getLogger(DefaultGroupProvider.class);

43
    private static final String INSERT_GROUP =
44
        "INSERT INTO ofGroup (groupName, description) VALUES (?, ?)";
45
    private static final String SAVE_GROUP =
46
        "UPDATE ofGroup SET description=? WHERE groupName=?";
47
    private static final String SET_GROUP_NAME_1 =
48
        "UPDATE ofGroup SET groupName=? WHERE groupName=?";
49
    private static final String SET_GROUP_NAME_2 =
50
        "UPDATE ofGroupProp SET groupName=? WHERE groupName=?";
51
    private static final String SET_GROUP_NAME_3 =
52
        "UPDATE ofGroupUser SET groupName=? WHERE groupName=?";
53
    private static final String DELETE_GROUP_USERS =
54
        "DELETE FROM ofGroupUser WHERE groupName=?";
55
    private static final String DELETE_PROPERTIES =
56
        "DELETE FROM ofGroupProp WHERE groupName=?";
57
    private static final String DELETE_GROUP =
58 59
        "DELETE FROM ofGroup WHERE groupName=?";
    private static final String GROUP_COUNT = "SELECT count(*) FROM ofGroup";
60
     private static final String LOAD_ADMINS =
61
        "SELECT username FROM ofGroupUser WHERE administrator=1 AND groupName=? ORDER BY username";
62
    private static final String LOAD_MEMBERS =
63
        "SELECT username FROM ofGroupUser WHERE administrator=0 AND groupName=? ORDER BY username";
64
    private static final String LOAD_GROUP =
65
        "SELECT description FROM ofGroup WHERE groupName=?";
66
    private static final String REMOVE_USER =
67
        "DELETE FROM ofGroupUser WHERE groupName=? AND username=?";
68
    private static final String ADD_USER =
69
        "INSERT INTO ofGroupUser (groupName, username, administrator) VALUES (?, ?, ?)";
70
    private static final String UPDATE_USER =
71
        "UPDATE ofGroupUser SET administrator=? WHERE groupName=? AND username=?";
72
    private static final String USER_GROUPS =
73 74
        "SELECT groupName FROM ofGroupUser WHERE username=?";
    private static final String ALL_GROUPS = "SELECT groupName FROM ofGroup ORDER BY groupName";
75
    private static final String SEARCH_GROUP_NAME = "SELECT groupName FROM ofGroup WHERE groupName LIKE ? ORDER BY groupName";
76 77 78

    private XMPPServer server = XMPPServer.getInstance();

79
    @Override
80
    public Group createGroup(String name) {
81 82 83 84 85 86 87 88 89 90
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(INSERT_GROUP);
            pstmt.setString(1, name);
            pstmt.setString(2, "");
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
91
            Log.error(e.getMessage(), e);
92 93
        }
        finally {
94
            DbConnectionManager.closeConnection(pstmt, con);
95 96 97
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
Matt Tucker's avatar
Matt Tucker committed
98
        return new Group(name, "", members, administrators);
99 100
    }

101
    @Override
102 103 104 105 106
    public Group getGroup(String name) throws GroupNotFoundException {
        String description = null;

        Connection con = null;
        PreparedStatement pstmt = null;
107
        ResultSet rs = null;
108 109 110 111
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(LOAD_GROUP);
            pstmt.setString(1, name);
112
            rs = pstmt.executeQuery();
113 114 115 116 117 118 119
            if (!rs.next()) {
                throw new GroupNotFoundException("Group with name "
                    + name + " not found.");
            }
            description = rs.getString(1);
        }
        catch (SQLException e) {
120
            Log.error(e.getMessage(), e);
121 122
        }
        finally {
123
            DbConnectionManager.closeConnection(rs, pstmt, con);
124 125 126
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
Matt Tucker's avatar
Matt Tucker committed
127
        return new Group(name, description, members, administrators);
128 129
    }

130
    @Override
131
    public void setDescription(String name, String description) throws GroupNotFoundException {
132 133 134 135 136 137 138 139 140 141
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(SAVE_GROUP);
            pstmt.setString(1, description);
            pstmt.setString(2, name);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
142
            Log.error(e.getMessage(), e);
143 144 145
            throw new GroupNotFoundException();
        }
        finally {
146
            DbConnectionManager.closeConnection(pstmt, con);
147 148 149
        }
    }

150
    @Override
151
    public void setName(String oldName, String newName) throws GroupAlreadyExistsException
152 153 154 155 156 157 158 159 160 161
    {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean abortTransaction = false;
        try {
            con = DbConnectionManager.getTransactionConnection();
            pstmt = con.prepareStatement(SET_GROUP_NAME_1);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
162 163
            DbConnectionManager.fastcloseStmt(pstmt);
            
164 165 166 167
            pstmt = con.prepareStatement(SET_GROUP_NAME_2);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
168 169
            DbConnectionManager.fastcloseStmt(pstmt);
            
170 171 172 173 174 175
            pstmt = con.prepareStatement(SET_GROUP_NAME_3);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
176
            Log.error(e.getMessage(), e);
177 178 179
            abortTransaction = true;
        }
        finally {
180
            DbConnectionManager.closeStatement(pstmt);
181 182 183 184
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

185
    @Override
186 187 188 189 190 191 192 193 194 195
    public void deleteGroup(String groupName) {
        Connection con = null;
        PreparedStatement pstmt = null;
        boolean abortTransaction = false;
        try {
            con = DbConnectionManager.getTransactionConnection();
            // Remove all users in the group.
            pstmt = con.prepareStatement(DELETE_GROUP_USERS);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
196 197
            DbConnectionManager.fastcloseStmt(pstmt);
            
198 199 200 201
            // Remove all properties of the group.
            pstmt = con.prepareStatement(DELETE_PROPERTIES);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
202 203
            DbConnectionManager.fastcloseStmt(pstmt);
            
204 205 206 207 208 209
            // Remove the group entry.
            pstmt = con.prepareStatement(DELETE_GROUP);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
210
            Log.error(e.getMessage(), e);
211 212 213
            abortTransaction = true;
        }
        finally {
214
            DbConnectionManager.closeStatement(pstmt);
215 216 217 218
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

219
    @Override
220 221 222 223
    public int getGroupCount() {
        int count = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
224
        ResultSet rs = null;
225 226 227
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(GROUP_COUNT);
228
            rs = pstmt.executeQuery();
229 230 231 232 233
            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException e) {
234
            Log.error(e.getMessage(), e);
235 236
        }
        finally {
237
            DbConnectionManager.closeConnection(rs, pstmt, con);
238 239 240 241
        }
        return count;
    }

242
    @Override
243
    public Collection<String> getGroupNames() {
244
        List<String> groupNames = new ArrayList<>();
245 246
        Connection con = null;
        PreparedStatement pstmt = null;
247
        ResultSet rs = null;
248 249 250
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ALL_GROUPS);
251
            rs = pstmt.executeQuery();
252 253 254 255 256
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
257
            Log.error(e.getMessage(), e);
258 259
        }
        finally {
260
            DbConnectionManager.closeConnection(rs, pstmt, con);       }
261
        return groupNames;
262 263
    }

264
    @Override
265
    public Collection<String> getGroupNames(int startIndex, int numResults) {
266
        List<String> groupNames = new ArrayList<>();
267 268
        Connection con = null;
        PreparedStatement pstmt = null;
269
        ResultSet rs = null;
270 271
        try {
            con = DbConnectionManager.getConnection();
272
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_GROUPS);
273
            rs = pstmt.executeQuery();
274 275 276 277 278 279 280 281
            DbConnectionManager.scrollResultSet(rs, startIndex);
            int count = 0;
            while (rs.next() && count < numResults) {
                groupNames.add(rs.getString(1));
                count++;
            }
        }
        catch (SQLException e) {
282
            Log.error(e.getMessage(), e);
283 284
        }
        finally {
285
            DbConnectionManager.closeConnection(rs, pstmt, con);
286
        }
287
        return groupNames;
288 289
    }

290
    @Override
291
    public Collection<String> getGroupNames(JID user) {
292
        List<String> groupNames = new ArrayList<>();
293 294
        Connection con = null;
        PreparedStatement pstmt = null;
295
        ResultSet rs = null;
296 297 298 299
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(USER_GROUPS);
            pstmt.setString(1, server.isLocal(user) ? user.getNode() : user.toString());
300
            rs = pstmt.executeQuery();
301 302 303 304 305
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
306
            Log.error(e.getMessage(), e);
307 308
        }
        finally {
309
            DbConnectionManager.closeConnection(rs, pstmt, con);
310
        }
311
        return groupNames;
312 313
    }

314
    @Override
315 316 317 318 319 320 321 322 323 324 325 326
    public void addMember(String groupName, JID user, boolean administrator) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ADD_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.setInt(3, administrator ? 1 : 0);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
327
            Log.error(e.getMessage(), e);
328 329
        }
        finally {
330
            DbConnectionManager.closeConnection(pstmt, con);
331 332 333
        }
    }

334
    @Override
335 336 337 338 339 340 341 342 343 344 345 346
    public void updateMember(String groupName, JID user, boolean administrator) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(UPDATE_USER);
            pstmt.setInt(1, administrator ? 1 : 0);
            pstmt.setString(2, groupName);
            pstmt.setString(3, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
347
            Log.error(e.getMessage(), e);
348 349
        }
        finally {
350
            DbConnectionManager.closeConnection(pstmt, con);
351 352 353
        }
    }

354
    @Override
355 356 357 358 359 360 361 362 363 364 365
    public void deleteMember(String groupName, JID user) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(REMOVE_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
366
            Log.error(e.getMessage(), e);
367 368
        }
        finally {
369
            DbConnectionManager.closeConnection(pstmt, con);
370 371 372
        }
    }

373
    @Override
374 375 376 377
    public boolean isReadOnly() {
        return false;
    }

378
    @Override
379
    public Collection<String> search(String query) {
380
        return search(query, 0, Integer.MAX_VALUE);
381 382
    }

383
    @Override
384
    public Collection<String> search(String query, int startIndex, int numResults) {
385 386 387 388 389 390 391 392 393 394 395 396
        if (query == null || "".equals(query)) {
            return Collections.emptyList();
        }
        // SQL LIKE queries don't map directly into a keyword/wildcard search like we want.
        // Therefore, we do a best approximiation by replacing '*' with '%' and then
        // surrounding the whole query with two '%'. This will return more data than desired,
        // but is better than returning less data than desired.
        query = "%" + query.replace('*', '%') + "%";
        if (query.endsWith("%%")) {
            query = query.substring(0, query.length()-1);
        }

397
        List<String> groupNames = new ArrayList<>();
398
        Connection con = null;
399
        PreparedStatement pstmt = null;
400 401 402
        ResultSet rs = null;
        try {
            con = DbConnectionManager.getConnection();
403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422
            if ((startIndex==0) && (numResults==Integer.MAX_VALUE))
            {
               pstmt = con.prepareStatement(SEARCH_GROUP_NAME);
               pstmt.setString(1, query);
               rs = pstmt.executeQuery();
               while (rs.next()) {
                   groupNames.add(rs.getString(1));
               }
            } else {
               pstmt = DbConnectionManager.createScrollablePreparedStatement(con, SEARCH_GROUP_NAME);
               DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults);
               pstmt.setString(1, query);
               rs = pstmt.executeQuery();
               // Scroll to the start index.
               DbConnectionManager.scrollResultSet(rs, startIndex);
               int count = 0;
               while (rs.next() && count < numResults) {
                   groupNames.add(rs.getString(1));
                   count++;
               }	
423 424 425
            }
        }
        catch (SQLException e) {
426
            Log.error(e.getMessage(), e);
427 428
        }
        finally {
429
            DbConnectionManager.closeConnection(rs, pstmt, con);
430
        }
431
        return groupNames;
432 433
    }

434
    @Override
435 436 437 438
    public boolean isSearchSupported() {
        return true;
    }

439
    @Override
440 441 442 443
    public boolean isSharingSupported() {
        return true;
    }

444
    private Collection<JID> getMembers(String groupName, boolean adminsOnly) {
445
        List<JID> members = new ArrayList<>();
446 447
        Connection con = null;
        PreparedStatement pstmt = null;
448
        ResultSet rs = null;
449 450 451 452 453 454 455 456 457
        try {
            con = DbConnectionManager.getConnection();
            if (adminsOnly) {
                pstmt = con.prepareStatement(LOAD_ADMINS);
            }
            else {
                pstmt = con.prepareStatement(LOAD_MEMBERS);
            }
            pstmt.setString(1, groupName);
458
            rs = pstmt.executeQuery();
459 460
            while (rs.next()) {
                String user = rs.getString(1);
461
                JID userJID = null;
462 463 464 465 466 467
                if (user.indexOf('@') == -1) {
                    // Create JID of local user if JID does not match a component's JID
                    if (!server.matchesComponent(userJID)) {
                        userJID = server.createJID(user, null);
                    }
                }
468 469 470
                else {
                    userJID = new JID(user);
                }
471 472 473 474
                members.add(userJID);
            }
        }
        catch (SQLException e) {
475
            Log.error(e.getMessage(), e);
476 477
        }
        finally {
478
            DbConnectionManager.closeConnection(rs, pstmt, con);
479 480 481
        }
        return members;
    }
482

483
}