JDBCGroupProvider.java 12.3 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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
/**
 * $Revision: $
 * $Date: $
 *
 * Copyright (C) 2006 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.wildfire.group;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.util.JiveGlobals;
import org.jivesoftware.util.Log;
import org.xmpp.packet.JID;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.*;

/**
 * The JDBC group provider allows you to use an external database to define the make up of groups.
 * It is best used with the JDBCAuthProvider to provide integration between your external system and
 * Wildfire.  All data is treated as read-only so any set operations will result in an exception.
 *
 * To enable this provider, set the following in the XML configuration file:
 *
 * <pre>
 * &lt;provider&gt;
 *     &lt;group&gt;
 *         &lt;className&gt;org.jivesoftware.wildfire.group.JDBCGroupProvider&lt;/className&gt;
 *     &lt;/group&gt;
 * &lt;/provider&gt;
 * </pre>
 *
 * Then you need to set your driver, connection string and SQL statements:
 *
 * <pre>
44 45 46 47 48
 * &lt;jdbcProvider&gt;
 *     &lt;driver&gt;com.mysql.jdbc.Driver&lt;/driver&gt;
 *     &lt;connectionString&gt;jdbc:mysql://localhost/dbname?user=username&amp;password=secret&lt;/connectionString&gt;
 * &lt;/jdbcProvider&gt;
 *
Matt Tucker's avatar
Matt Tucker committed
49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
 * &lt;jdbcGroupProvider&gt;
 *      &lt;groupCountSQL&gt;SELECT count(*) FROM myGroups&lt;/groupCountSQL&gt;
 *      &lt;allGroupsSQL&gt;SELECT groupName FROM myGroups&lt;/allGroupsSQL&gt;
 *      &lt;userGroupsSQL&gt;SELECT groupName FORM myGroupUsers WHERE
 * username=?&lt;/userGroupsSQL&gt;
 *      &lt;descriptionSQL&gt;SELECT groupDescription FROM myGroups WHERE
 * groupName=?&lt;/descriptionSQL&gt;
 *      &lt;loadMembersSQL&gt;SELECT username FORM myGroupUsers WHERE groupName=? AND
 * isAdmin='N'&lt;/loadMembersSQL&gt;
 *      &lt;loadAdminsSQL&gt;SELECT username FORM myGroupUsers WHERE groupName=? AND
 * isAdmin='Y'&lt;/loadAdminsSQL&gt;
 * &lt;/jdbcGroupProvider&gt;
 * </pre>
 *
 * @author David Snopek
 */
public class JDBCGroupProvider implements GroupProvider {

67
    private String connectionString;
Matt Tucker's avatar
Matt Tucker committed
68 69 70 71 72 73 74 75 76 77 78 79

    private String groupCountSQL;
    private String descriptionSQL;
    private String allGroupsSQL;
    private String userGroupsSQL;
    private String loadMembersSQL;
    private String loadAdminsSQL;

    /**
     * Constructor of the JDBCGroupProvider class.
     */
    public JDBCGroupProvider() {
80 81
        // Load the JDBC driver and connection string.
        String jdbcDriver = JiveGlobals.getXMLProperty("jdbcProvider.driver");
Matt Tucker's avatar
Matt Tucker committed
82 83 84 85 86 87 88
        try {
            Class.forName(jdbcDriver).newInstance();
        }
        catch (Exception e) {
            Log.error("Unable to load JDBC driver: " + jdbcDriver, e);
            return;
        }
89
        connectionString = JiveGlobals.getXMLProperty("jdbcProvider.connectionString");
Matt Tucker's avatar
Matt Tucker committed
90

91
        // Load SQL statements
Matt Tucker's avatar
Matt Tucker committed
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122
        groupCountSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.groupCountSQL");
        allGroupsSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.allGroupsSQL");
        userGroupsSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.userGroupsSQL");
        descriptionSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.descriptionSQL");
        loadMembersSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.loadMembersSQL");
        loadAdminsSQL = JiveGlobals.getXMLProperty("jdbcGroupProvider.loadAdminsSQL");
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param name the name of the group to create.
     * @throws UnsupportedOperationException when called.
     */
    public Group createGroup(String name) throws UnsupportedOperationException {
        throw new UnsupportedOperationException();
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param name the name of the group to delete
     * @throws UnsupportedOperationException when called.
     */
    public void deleteGroup(String name) throws UnsupportedOperationException {
        throw new UnsupportedOperationException();
    }

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

123
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
124
        PreparedStatement pstmt = null;
125
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
126
        try {
127 128
            con = DriverManager.getConnection(connectionString);
            pstmt = con.prepareStatement(descriptionSQL);
Matt Tucker's avatar
Matt Tucker committed
129
            pstmt.setString(1, name);
130
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
131 132 133 134 135 136 137 138 139 140
            if (!rs.next()) {
                throw new GroupNotFoundException("Group with name "
                        + name + " not found.");
            }
            description = rs.getString(1);
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
141
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
142 143 144 145 146 147 148 149
        }
        Collection<JID> members = getMembers(name, false);
        Collection<JID> administrators = getMembers(name, true);
        return new Group(name, description, members, administrators);
    }

    private Collection<JID> getMembers(String groupName, boolean adminsOnly) {
        List<JID> members = new ArrayList<JID>();
150 151

        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
152
        PreparedStatement pstmt = null;
153
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
154
        try {
155
            con = DriverManager.getConnection(connectionString);
Matt Tucker's avatar
Matt Tucker committed
156 157 158 159
            if (adminsOnly) {
                if (loadAdminsSQL == null) {
                    return members;
                }
160
                pstmt = con.prepareStatement(loadAdminsSQL);
Matt Tucker's avatar
Matt Tucker committed
161 162
            }
            else {
163
                pstmt = con.prepareStatement(loadMembersSQL);
Matt Tucker's avatar
Matt Tucker committed
164 165 166
            }

            pstmt.setString(1, groupName);
167
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
168 169 170 171 172 173 174 175 176 177 178 179
            while (rs.next()) {
                String user = rs.getString(1);
                if (user != null) {
                    JID userJID = new JID(user);
                    members.add(userJID);
                }
            }
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
180
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209
        }
        return members;
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param oldName the current name of the group.
     * @param newName the desired new name of the group.
     * @throws UnsupportedOperationException when called.
     */
    public void setName(String oldName, String newName) throws UnsupportedOperationException {
        throw new UnsupportedOperationException();
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param name the group name.
     * @param description the group description.
     * @throws UnsupportedOperationException when called.
     */
    public void setDescription(String name, String description)
            throws UnsupportedOperationException {
        throw new UnsupportedOperationException();
    }

    public int getGroupCount() {
        int count = 0;
210
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
211
        PreparedStatement pstmt = null;
212
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
213
        try {
214 215 216
            con = DriverManager.getConnection(connectionString);
            pstmt = con.prepareStatement(groupCountSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
217 218 219 220 221 222 223 224
            if (rs.next()) {
                count = rs.getInt(1);
            }
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
225
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
226 227 228 229
        }
        return count;
    }

230
    public Collection<String> getGroupNames() {
Matt Tucker's avatar
Matt Tucker committed
231
        List<String> groupNames = new ArrayList<String>();
232
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
233
        PreparedStatement pstmt = null;
234
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
235
        try {
236 237 238
            con = DriverManager.getConnection(connectionString);
            pstmt = con.prepareStatement(allGroupsSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
239 240 241 242 243 244 245 246
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
247
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
248
        }
249
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
250 251
    }

252
    public Collection<String> getGroupNames(int start, int num) {
Matt Tucker's avatar
Matt Tucker committed
253
        List<String> groupNames = new ArrayList<String>();
254
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
255
        PreparedStatement pstmt = null;
256
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
257
        try {
258 259 260
            con = DriverManager.getConnection(connectionString);
            pstmt = DbConnectionManager.createScrollablePreparedStatement(con, allGroupsSQL);
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
261 262 263 264 265 266 267 268 269 270 271
            DbConnectionManager.scrollResultSet(rs, start);
            int count = 0;
            while (rs.next() && count < num) {
                groupNames.add(rs.getString(1));
                count++;
            }
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
272
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
273
        }
274
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
275 276
    }

277
    public Collection<String> getGroupNames(JID user) {
Matt Tucker's avatar
Matt Tucker committed
278
        List<String> groupNames = new ArrayList<String>();
279
        Connection con = null;
Matt Tucker's avatar
Matt Tucker committed
280
        PreparedStatement pstmt = null;
281
        ResultSet rs = null;
Matt Tucker's avatar
Matt Tucker committed
282
        try {
283 284
            con = DriverManager.getConnection(connectionString);
            pstmt = con.prepareStatement(userGroupsSQL);
Matt Tucker's avatar
Matt Tucker committed
285
            pstmt.setString(1, user.toString());
286
            rs = pstmt.executeQuery();
Matt Tucker's avatar
Matt Tucker committed
287 288 289 290 291 292 293 294
            while (rs.next()) {
                groupNames.add(rs.getString(1));
            }
        }
        catch (SQLException e) {
            Log.error(e);
        }
        finally {
295
            DbConnectionManager.closeConnection(rs, pstmt, con);
Matt Tucker's avatar
Matt Tucker committed
296
        }
297
        return groupNames;
Matt Tucker's avatar
Matt Tucker committed
298 299 300 301 302 303 304 305 306 307 308
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param groupName name of a group.
     * @param user the JID of the user to add
     * @param administrator true if is an administrator.
     * @throws UnsupportedOperationException when called.
     */
    public void addMember(String groupName, JID user, boolean administrator)
309 310
            throws UnsupportedOperationException
    {
Matt Tucker's avatar
Matt Tucker committed
311 312 313 314 315 316 317 318 319 320 321 322
        throw new UnsupportedOperationException();
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param groupName the naame of a group.
     * @param user the JID of the user with new privileges
     * @param administrator true if is an administrator.
     * @throws UnsupportedOperationException when called.
     */
    public void updateMember(String groupName, JID user, boolean administrator)
323 324
            throws UnsupportedOperationException
    {
Matt Tucker's avatar
Matt Tucker committed
325 326 327 328 329 330 331 332 333 334 335
        throw new UnsupportedOperationException();
    }

    /**
     * Always throws an UnsupportedOperationException because JDBC groups are read-only.
     *
     * @param groupName the name of a group.
     * @param user the JID of the user to delete.
     * @throws UnsupportedOperationException when called.
     */
    public void deleteMember(String groupName, JID user)
336 337
            throws UnsupportedOperationException
    {
Matt Tucker's avatar
Matt Tucker committed
338 339 340 341 342 343 344 345 346 347 348
        throw new UnsupportedOperationException();
    }

    /**
     * Always returns true because JDBC groups are read-only.
     *
     * @return true because all JDBC functions are read-only.
     */
    public boolean isReadOnly() {
        return true;
    }
349

350
    public Collection<String> search(String query) {
351 352 353
        return Collections.emptyList();
    }

354
    public Collection<String> search(String query, int startIndex, int numResults) {
Matt Tucker's avatar
Matt Tucker committed
355 356 357 358
        return Collections.emptyList();
    }

    public boolean isSearchSupported() {
359 360
        return false;
    }
Matt Tucker's avatar
Matt Tucker committed
361
}