DefaultGroupProvider.java 15.4 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
/**
 * $RCSfile$
 * $Revision$
 * $Date$
 *
 * Copyright (C) 2004 Jive Software. All rights reserved.
 *
 * This software is published under the terms of the GNU Public License (GPL),
 * a copy of which is included in this distribution.
 */

package org.jivesoftware.messenger.group;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.*;
import org.jivesoftware.messenger.user.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Collection;

/**
 * Database implementation of the GroupManager interface.
 *
29
 * @author Matt Tucker
Matt Tucker's avatar
Matt Tucker committed
30
 */
Matt Tucker's avatar
Matt Tucker committed
31
public class DefaultGroupProvider implements GroupProvider {
Matt Tucker's avatar
Matt Tucker committed
32 33

    private static final String INSERT_GROUP =
34
        "INSERT INTO jiveGroup (groupName, description) VALUES (?, ?)";
Matt Tucker's avatar
Matt Tucker committed
35
    private static final String SAVE_GROUP =
36
        "UPDATE jiveGroup SET description=? WHERE groupName=?";
Matt Tucker's avatar
Matt Tucker committed
37
    private static final String SET_GROUP_NAME_1 =
38
        "UPDATE jiveGroup SET groupName=? WHERE groupName=?";
Matt Tucker's avatar
Matt Tucker committed
39 40 41 42 43 44
    private static final String SET_GROUP_NAME_2 =
        "UPDATE jiveGroupProp SET groupName=? WHERE groupName=?";
    private static final String SET_GROUP_NAME_3 =
        "UPDATE jiveGroupUser SET groupName=? WHERE groupName=?";
    private static final String DELETE_GROUP_USERS =
        "DELETE FROM jiveGroupUser WHERE groupName=?";
45 46
    private static final String DELETE_PROPERTIES =
        "DELETE FROM jiveGroupProp WHERE groupName=?";
Matt Tucker's avatar
Matt Tucker committed
47
    private static final String DELETE_GROUP =
48
        "DELETE FROM jiveGroup WHERE groupName=?";
Matt Tucker's avatar
Matt Tucker committed
49 50 51 52 53
    private static final String GROUP_COUNT = "SELECT count(*) FROM jiveGroup";
     private static final String LOAD_ADMINS =
        "SELECT username FROM jiveGroupUser WHERE administrator=1 AND groupName=?";
    private static final String LOAD_MEMBERS =
        "SELECT username FROM jiveGroupUser WHERE administrator=0 AND groupName=?";
54
    private static final String LOAD_GROUP =
55
        "SELECT description FROM jiveGroup WHERE groupName=?";
Matt Tucker's avatar
Matt Tucker committed
56 57 58 59
    private static final String REMOVE_USER =
        "DELETE FROM jiveGroupUser WHERE groupName=? AND username=?";
    private static final String ADD_USER =
        "INSERT INTO jiveGroupUser (groupName, username, administrator) VALUES (?, ?, ?)";
60 61
    private static final String UPDATE_USER =
        "UPDATE jiveGroupUser SET administrator=? WHERE groupName=? AND username=?";
Matt Tucker's avatar
Matt Tucker committed
62 63
    private static final String USER_GROUPS =
        "SELECT groupName FROM jiveGroupUser WHERE username=?";
64
    private static final String ALL_GROUPS = "SELECT groupName FROM jiveGroup ORDER BY groupName";
Matt Tucker's avatar
Matt Tucker committed
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86

    public Group createGroup(String name) throws GroupAlreadyExistsException {
        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) {
            Log.error(e);
        }
        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); }
        }
        Collection<String> members = getMembers(name, false);
        Collection<String> administrators = getMembers(name, true);
Matt Tucker's avatar
Matt Tucker committed
87
        return new Group(this, name, "", members, administrators);
Matt Tucker's avatar
Matt Tucker committed
88 89 90 91 92 93 94 95 96
    }

    public Group getGroup(String name) throws GroupNotFoundException {
        String description = null;

        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
97
            pstmt = con.prepareStatement(LOAD_GROUP);
Matt Tucker's avatar
Matt Tucker committed
98 99 100 101 102 103
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if (!rs.next()) {
                throw new GroupNotFoundException("Group with name "
                    + name + " not found.");
            }
104
            description = rs.getString(1);
Matt Tucker's avatar
Matt Tucker committed
105 106 107 108 109 110 111 112 113 114 115 116
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
        Collection<String> members = getMembers(name, false);
        Collection<String> administrators = getMembers(name, true);
Matt Tucker's avatar
Matt Tucker committed
117
        return new Group(this, name, description, members, administrators);
Matt Tucker's avatar
Matt Tucker committed
118 119
    }

Matt Tucker's avatar
Matt Tucker committed
120 121
    public void setDescription(String name, String description)
            throws GroupNotFoundException
Matt Tucker's avatar
Matt Tucker committed
122 123 124 125 126 127 128
    {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(SAVE_GROUP);
            pstmt.setString(1, description);
Matt Tucker's avatar
Matt Tucker committed
129
            pstmt.setString(2, name);
Matt Tucker's avatar
Matt Tucker committed
130 131 132 133 134 135 136 137 138 139 140 141 142 143
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e);
            throw new GroupNotFoundException();
        }
        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); }
        }
    }

Matt Tucker's avatar
Matt Tucker committed
144
    public void setName(String oldName, String newName) throws UnsupportedOperationException,
Matt Tucker's avatar
Matt Tucker committed
145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188
            GroupAlreadyExistsException
    {
        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();
            pstmt.close();
            pstmt = con.prepareStatement(SET_GROUP_NAME_2);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = con.prepareStatement(SET_GROUP_NAME_3);
            pstmt.setString(1, newName);
            pstmt.setString(2, oldName);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e);
            abortTransaction = true;
        }
        finally {
            try { if (pstmt != null) pstmt.close(); }
            catch (Exception e) { Log.error(e); }
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

    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();
            pstmt.close();
189 190 191 192 193
            // Remove all properties of the group.
            pstmt = con.prepareStatement(DELETE_PROPERTIES);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
            pstmt.close();
Matt Tucker's avatar
Matt Tucker committed
194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354
            // Remove the group entry.
            pstmt = con.prepareStatement(DELETE_GROUP);
            pstmt.setString(1, groupName);
            pstmt.executeUpdate();
            pstmt.close();
        }
        catch (SQLException e) {
            Log.error(e);
            abortTransaction = true;
        }
        finally {
            try { if (pstmt != null) pstmt.close(); }
            catch (Exception e) { Log.error(e); }
            DbConnectionManager.closeTransactionConnection(con, abortTransaction);
        }
    }

    public int getGroupCount() {
        int count = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(GROUP_COUNT);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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 count;
    }

    public Collection<Group> getGroups() {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ALL_GROUPS);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
            rs.close();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
        List<Group> groups = new ArrayList<Group>(groupNames.size());
        GroupManager manager = GroupManager.getInstance();
        for (String groupName : groupNames) {
            try {
                groups.add(manager.getGroup(groupName));
            }
            catch (GroupNotFoundException e) {
                Log.error(e);
            }
        }
        return groups;
    }

    public Collection<Group> getGroups(int startIndex, int numResults) {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ALL_GROUPS);
            ResultSet rs = pstmt.executeQuery();
            DbConnectionManager.scrollResultSet(rs, startIndex);
            int count = 0;
            while (rs.next() && count < numResults) {
                groupNames.add(rs.getString(1));
                count++;
            }
            rs.close();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
        List<Group> groups = new ArrayList<Group>(groupNames.size());
        GroupManager manager = GroupManager.getInstance();
        for (String groupName : groupNames) {
            try {
                groups.add(manager.getGroup(groupName));
            }
            catch (GroupNotFoundException e) {
                Log.error(e);
            }
        }
        return groups;
    }

    public Collection<Group> getGroups(User user) {
        List<String> groupNames = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(USER_GROUPS);
            pstmt.setString(1, user.getUsername());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
            rs.close();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
        List<Group> groups = new ArrayList<Group>(groupNames.size());
        GroupManager manager = GroupManager.getInstance();
        for (String groupName : groupNames) {
            try {
                groups.add(manager.getGroup(groupName));
            }
            catch (GroupNotFoundException e) {
                Log.error(e);
            }
        }
        return groups;
    }

    public void addMember(String groupName, String username, boolean administrator) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(ADD_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, username);
            pstmt.setInt(3, administrator ? 1 : 0);
            pstmt.executeUpdate();
355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
    }

    public void updateMember(String groupName, String username, 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, username);
            pstmt.executeUpdate();
Matt Tucker's avatar
Matt Tucker committed
377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
    }

    public void deleteMember(String groupName, String username) {
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            pstmt = con.prepareStatement(REMOVE_USER);
            pstmt.setString(1, groupName);
            pstmt.setString(2, username);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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); }
        }
    }

410 411 412 413
    public boolean isReadOnly() {
        return false;
    }

Matt Tucker's avatar
Matt Tucker committed
414 415 416 417 418 419 420
    private Collection<String> getMembers(String groupName, boolean adminsOnly) {
        List<String> members = new ArrayList<String>();
        Connection con = null;
        PreparedStatement pstmt = null;
        try {
            con = DbConnectionManager.getConnection();
            if (adminsOnly) {
421
                pstmt = con.prepareStatement(LOAD_ADMINS);
Matt Tucker's avatar
Matt Tucker committed
422 423
            }
            else {
424
                pstmt = con.prepareStatement(LOAD_MEMBERS);
Matt Tucker's avatar
Matt Tucker committed
425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444
            }
            pstmt.setString(1, groupName);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                members.add(rs.getString(1));
            }
            rs.close();
        }
        catch (SQLException e) {
            Log.error(e);
        }
        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 members;
    }
}