1 /* 2 * Copyright (C) 2004-2008 Jive Software. All rights reserved. 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package org.jivesoftware.openfire.group; 18 19 import java.sql.Connection; 20 import java.sql.PreparedStatement; 21 import java.sql.ResultSet; 22 import java.sql.SQLException; 23 import java.util.ArrayList; 24 import java.util.Collection; 25 import java.util.Collections; 26 import java.util.List; 27 28 import org.jivesoftware.database.DbConnectionManager; 29 import org.jivesoftware.openfire.XMPPServer; 30 import org.slf4j.Logger; 31 import org.slf4j.LoggerFactory; 32 import org.xmpp.packet.JID; 33 34 /** 35 * Database implementation of the GroupManager interface. 36 * 37 * @author Matt Tucker 38 */ 39 public class DefaultGroupProvider extends AbstractGroupProvider { 40 41 private static final Logger Log = LoggerFactory.getLogger(DefaultGroupProvider.class); 42 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 "DELETE FROM ofGroup WHERE groupName=?"; 59 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 "SELECT groupName FROM ofGroupUser WHERE username=?"; 74 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 private XMPPServer server = XMPPServer.getInstance(); 78 79 @Override createGroup(String name)80 public Group createGroup(String name) { 81 Connection con = null; 82 PreparedStatement pstmt = null; 83 try { 84 con = DbConnectionManager.getConnection(); 85 pstmt = con.prepareStatement(INSERT_GROUP); 86 pstmt.setString(1, name); 87 pstmt.setString(2, ""); 88 pstmt.executeUpdate(); 89 } 90 catch (SQLException e) { 91 Log.error(e.getMessage(), e); 92 } 93 finally { 94 DbConnectionManager.closeConnection(pstmt, con); 95 } 96 Collection<JID> members = getMembers(name, false); 97 Collection<JID> administrators = getMembers(name, true); 98 return new Group(name, "", members, administrators); 99 } 100 101 @Override getGroup(String name)102 public Group getGroup(String name) throws GroupNotFoundException { 103 String description = null; 104 105 Connection con = null; 106 PreparedStatement pstmt = null; 107 ResultSet rs = null; 108 try { 109 con = DbConnectionManager.getConnection(); 110 pstmt = con.prepareStatement(LOAD_GROUP); 111 pstmt.setString(1, name); 112 rs = pstmt.executeQuery(); 113 if (!rs.next()) { 114 throw new GroupNotFoundException("Group with name " 115 + name + " not found."); 116 } 117 description = rs.getString(1); 118 } 119 catch (SQLException e) { 120 Log.error(e.getMessage(), e); 121 } 122 finally { 123 DbConnectionManager.closeConnection(rs, pstmt, con); 124 } 125 Collection<JID> members = getMembers(name, false); 126 Collection<JID> administrators = getMembers(name, true); 127 return new Group(name, description, members, administrators); 128 } 129 130 @Override setDescription(String name, String description)131 public void setDescription(String name, String description) throws GroupNotFoundException { 132 Connection con = null; 133 PreparedStatement pstmt = null; 134 try { 135 con = DbConnectionManager.getConnection(); 136 pstmt = con.prepareStatement(SAVE_GROUP); 137 pstmt.setString(1, description); 138 pstmt.setString(2, name); 139 pstmt.executeUpdate(); 140 } 141 catch (SQLException e) { 142 Log.error(e.getMessage(), e); 143 throw new GroupNotFoundException(); 144 } 145 finally { 146 DbConnectionManager.closeConnection(pstmt, con); 147 } 148 } 149 150 @Override setName(String oldName, String newName)151 public void setName(String oldName, String newName) throws GroupAlreadyExistsException 152 { 153 Connection con = null; 154 PreparedStatement pstmt = null; 155 boolean abortTransaction = false; 156 try { 157 con = DbConnectionManager.getTransactionConnection(); 158 pstmt = con.prepareStatement(SET_GROUP_NAME_1); 159 pstmt.setString(1, newName); 160 pstmt.setString(2, oldName); 161 pstmt.executeUpdate(); 162 DbConnectionManager.fastcloseStmt(pstmt); 163 164 pstmt = con.prepareStatement(SET_GROUP_NAME_2); 165 pstmt.setString(1, newName); 166 pstmt.setString(2, oldName); 167 pstmt.executeUpdate(); 168 DbConnectionManager.fastcloseStmt(pstmt); 169 170 pstmt = con.prepareStatement(SET_GROUP_NAME_3); 171 pstmt.setString(1, newName); 172 pstmt.setString(2, oldName); 173 pstmt.executeUpdate(); 174 } 175 catch (SQLException e) { 176 Log.error(e.getMessage(), e); 177 abortTransaction = true; 178 } 179 finally { 180 DbConnectionManager.closeStatement(pstmt); 181 DbConnectionManager.closeTransactionConnection(con, abortTransaction); 182 } 183 } 184 185 @Override deleteGroup(String groupName)186 public void deleteGroup(String groupName) { 187 Connection con = null; 188 PreparedStatement pstmt = null; 189 boolean abortTransaction = false; 190 try { 191 con = DbConnectionManager.getTransactionConnection(); 192 // Remove all users in the group. 193 pstmt = con.prepareStatement(DELETE_GROUP_USERS); 194 pstmt.setString(1, groupName); 195 pstmt.executeUpdate(); 196 DbConnectionManager.fastcloseStmt(pstmt); 197 198 // Remove all properties of the group. 199 pstmt = con.prepareStatement(DELETE_PROPERTIES); 200 pstmt.setString(1, groupName); 201 pstmt.executeUpdate(); 202 DbConnectionManager.fastcloseStmt(pstmt); 203 204 // Remove the group entry. 205 pstmt = con.prepareStatement(DELETE_GROUP); 206 pstmt.setString(1, groupName); 207 pstmt.executeUpdate(); 208 } 209 catch (SQLException e) { 210 Log.error(e.getMessage(), e); 211 abortTransaction = true; 212 } 213 finally { 214 DbConnectionManager.closeStatement(pstmt); 215 DbConnectionManager.closeTransactionConnection(con, abortTransaction); 216 } 217 } 218 219 @Override getGroupCount()220 public int getGroupCount() { 221 int count = 0; 222 Connection con = null; 223 PreparedStatement pstmt = null; 224 ResultSet rs = null; 225 try { 226 con = DbConnectionManager.getConnection(); 227 pstmt = con.prepareStatement(GROUP_COUNT); 228 rs = pstmt.executeQuery(); 229 if (rs.next()) { 230 count = rs.getInt(1); 231 } 232 } 233 catch (SQLException e) { 234 Log.error(e.getMessage(), e); 235 } 236 finally { 237 DbConnectionManager.closeConnection(rs, pstmt, con); 238 } 239 return count; 240 } 241 242 @Override getGroupNames()243 public Collection<String> getGroupNames() { 244 List<String> groupNames = new ArrayList<>(); 245 Connection con = null; 246 PreparedStatement pstmt = null; 247 ResultSet rs = null; 248 try { 249 con = DbConnectionManager.getConnection(); 250 pstmt = con.prepareStatement(ALL_GROUPS); 251 rs = pstmt.executeQuery(); 252 while (rs.next()) { 253 groupNames.add(rs.getString(1)); 254 } 255 } 256 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 getGroupNames(int startIndex, int numResults)265 public Collection<String> getGroupNames(int startIndex, int numResults) { 266 List<String> groupNames = new ArrayList<>(); 267 Connection con = null; 268 PreparedStatement pstmt = null; 269 ResultSet rs = null; 270 try { 271 con = DbConnectionManager.getConnection(); 272 pstmt = DbConnectionManager.createScrollablePreparedStatement(con, ALL_GROUPS); 273 rs = pstmt.executeQuery(); 274 DbConnectionManager.scrollResultSet(rs, startIndex); 275 int count = 0; 276 while (rs.next() && count < numResults) { 277 groupNames.add(rs.getString(1)); 278 count++; 279 } 280 } 281 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 getGroupNames(JID user)291 public Collection<String> getGroupNames(JID user) { 292 List<String> groupNames = new ArrayList<>(); 293 Connection con = null; 294 PreparedStatement pstmt = null; 295 ResultSet rs = null; 296 try { 297 con = DbConnectionManager.getConnection(); 298 pstmt = con.prepareStatement(USER_GROUPS); 299 pstmt.setString(1, server.isLocal(user) ? user.getNode() : user.toString()); 300 rs = pstmt.executeQuery(); 301 while (rs.next()) { 302 groupNames.add(rs.getString(1)); 303 } 304 } 305 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 addMember(String groupName, JID user, boolean administrator)315 public void addMember(String groupName, JID user, boolean administrator) { 316 Connection con = null; 317 PreparedStatement pstmt = null; 318 try { 319 con = DbConnectionManager.getConnection(); 320 pstmt = con.prepareStatement(ADD_USER); 321 pstmt.setString(1, groupName); 322 pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString()); 323 pstmt.setInt(3, administrator ? 1 : 0); 324 pstmt.executeUpdate(); 325 } 326 catch (SQLException e) { 327 Log.error(e.getMessage(), e); 328 } 329 finally { 330 DbConnectionManager.closeConnection(pstmt, con); 331 } 332 } 333 334 @Override updateMember(String groupName, JID user, boolean administrator)335 public void updateMember(String groupName, JID user, boolean administrator) { 336 Connection con = null; 337 PreparedStatement pstmt = null; 338 try { 339 con = DbConnectionManager.getConnection(); 340 pstmt = con.prepareStatement(UPDATE_USER); 341 pstmt.setInt(1, administrator ? 1 : 0); 342 pstmt.setString(2, groupName); 343 pstmt.setString(3, server.isLocal(user) ? user.getNode() : user.toString()); 344 pstmt.executeUpdate(); 345 } 346 catch (SQLException e) { 347 Log.error(e.getMessage(), e); 348 } 349 finally { 350 DbConnectionManager.closeConnection(pstmt, con); 351 } 352 } 353 354 @Override deleteMember(String groupName, JID user)355 public void deleteMember(String groupName, JID user) { 356 Connection con = null; 357 PreparedStatement pstmt = null; 358 try { 359 con = DbConnectionManager.getConnection(); 360 pstmt = con.prepareStatement(REMOVE_USER); 361 pstmt.setString(1, groupName); 362 pstmt.setString(2, server.isLocal(user) ? user.getNode() : user.toString()); 363 pstmt.executeUpdate(); 364 } 365 catch (SQLException e) { 366 Log.error(e.getMessage(), e); 367 } 368 finally { 369 DbConnectionManager.closeConnection(pstmt, con); 370 } 371 } 372 373 @Override isReadOnly()374 public boolean isReadOnly() { 375 return false; 376 } 377 378 @Override search(String query)379 public Collection<String> search(String query) { 380 return search(query, 0, Integer.MAX_VALUE); 381 } 382 383 @Override search(String query, int startIndex, int numResults)384 public Collection<String> search(String query, int startIndex, int numResults) { 385 if (query == null || "".equals(query)) { 386 return Collections.emptyList(); 387 } 388 // SQL LIKE queries don't map directly into a keyword/wildcard search like we want. 389 // Therefore, we do a best approximiation by replacing '*' with '%' and then 390 // surrounding the whole query with two '%'. This will return more data than desired, 391 // but is better than returning less data than desired. 392 query = "%" + query.replace('*', '%') + "%"; 393 if (query.endsWith("%%")) { 394 query = query.substring(0, query.length()-1); 395 } 396 397 List<String> groupNames = new ArrayList<>(); 398 Connection con = null; 399 PreparedStatement pstmt = null; 400 ResultSet rs = null; 401 try { 402 con = DbConnectionManager.getConnection(); 403 if ((startIndex==0) && (numResults==Integer.MAX_VALUE)) 404 { 405 pstmt = con.prepareStatement(SEARCH_GROUP_NAME); 406 pstmt.setString(1, query); 407 rs = pstmt.executeQuery(); 408 while (rs.next()) { 409 groupNames.add(rs.getString(1)); 410 } 411 } else { 412 pstmt = DbConnectionManager.createScrollablePreparedStatement(con, SEARCH_GROUP_NAME); 413 DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex, numResults); 414 pstmt.setString(1, query); 415 rs = pstmt.executeQuery(); 416 // Scroll to the start index. 417 DbConnectionManager.scrollResultSet(rs, startIndex); 418 int count = 0; 419 while (rs.next() && count < numResults) { 420 groupNames.add(rs.getString(1)); 421 count++; 422 } 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 isSearchSupported()435 public boolean isSearchSupported() { 436 return true; 437 } 438 439 @Override isSharingSupported()440 public boolean isSharingSupported() { 441 return true; 442 } 443 getMembers(String groupName, boolean adminsOnly)444 private Collection<JID> getMembers(String groupName, boolean adminsOnly) { 445 List<JID> members = new ArrayList<>(); 446 Connection con = null; 447 PreparedStatement pstmt = null; 448 ResultSet rs = null; 449 try { 450 con = DbConnectionManager.getConnection(); 451 if (adminsOnly) { 452 pstmt = con.prepareStatement(LOAD_ADMINS); 453 } 454 else { 455 pstmt = con.prepareStatement(LOAD_MEMBERS); 456 } 457 pstmt.setString(1, groupName); 458 rs = pstmt.executeQuery(); 459 while (rs.next()) { 460 String user = rs.getString(1); 461 JID userJID = null; 462 if (user.indexOf('@') == -1) { 463 // Create JID of local user if JID does not match a component's JID 464 if (!server.matchesComponent(userJID)) { 465 userJID = server.createJID(user, null); 466 } 467 } 468 else { 469 userJID = new JID(user); 470 } 471 members.add(userJID); 472 } 473 } 474 catch (SQLException e) { 475 Log.error(e.getMessage(), e); 476 } 477 finally { 478 DbConnectionManager.closeConnection(rs, pstmt, con); 479 } 480 return members; 481 } 482 483 } 484