Java > Open Source Codes > org > jivesoftware > messenger > muc > spi > MUCPersistenceManager


1 /**
2  * $RCSfile: MUCPersistenceManager.java,v $
3  * $Revision: 1.29 $
4  * $Date: 2005/07/12 21:40:16 $
5  *
6  * Copyright (C) 2004 Jive Software. All rights reserved.
7  *
8  * This software is published under the terms of the GNU Public License (GPL),
9  * a copy of which is included in this distribution.
10  */
11
12 package org.jivesoftware.messenger.muc.spi;
13
14 import java.sql.Connection ;
15 import java.sql.SQLException ;
16 import java.sql.PreparedStatement ;
17 import java.sql.ResultSet ;
18 import java.util.*;
19
20 import org.jivesoftware.database.DbConnectionManager;
21 import org.jivesoftware.messenger.muc.MUCRole;
22 import org.jivesoftware.messenger.muc.MUCRoom;
23 import org.jivesoftware.messenger.muc.MultiUserChatServer;
24 import org.jivesoftware.messenger.PacketRouter;
25 import org.jivesoftware.util.Log;
26 import org.jivesoftware.util.StringUtils;
27
28 /**
29  * A manager responsible for ensuring room persistence. There are different ways to make a room
30  * persistent. The first attempt will be to save the room in a relation database. If for some reason
31  * the room can't be saved in the database an alternative repository will be used to save the room
32  * such as XML files.<p>
33  *
34  * After the problem with the database has been solved, the information saved in the XML files will
35  * be moved to the database.
36  *
37  * @author Gaston Dombiak
38  */
39 public class MUCPersistenceManager {
40
41     private static final String GET_RESERVED_NAME =
42         "SELECT nickname FROM mucMember WHERE roomID=? AND jid=?";
43     private static final String LOAD_ROOM =
44         "SELECT roomID, creationDate, modificationDate, naturalName, description, lockedDate, " +
45         "emptyDate, canChangeSubject, maxUsers, publicRoom, moderated, membersOnly, canInvite, " +
46         "password, canDiscoverJID, logEnabled, subject, rolesToBroadcast, useReservedNick, " +
47         "canChangeNick, canRegister FROM mucRoom WHERE name=?";
48     private static final String LOAD_AFFILIATIONS =
49         "SELECT jid, affiliation FROM mucAffiliation WHERE roomID=?";
50     private static final String LOAD_MEMBERS =
51         "SELECT jid, nickname FROM mucMember WHERE roomID=?";
52     private static final String LOAD_HISTORY =
53         "SELECT sender, nickname, time, subject, body FROM mucConversationLog " +
54         "WHERE time>? AND roomID=? AND (nickname <> '' OR subject IS NOT NULL) ORDER BY time";
55     private static final String LOAD_ALL_ROOMS =
56         "SELECT roomID, creationDate, modificationDate, name, naturalName, description, " +
57         "lockedDate, emptyDate, canChangeSubject, maxUsers, publicRoom, moderated, membersOnly, " +
58         "canInvite, password, canDiscoverJID, logEnabled, subject, rolesToBroadcast, " +
59         "useReservedNick, canChangeNick, canRegister " +
60         "FROM mucRoom WHERE emptyDate IS NULL or emptyDate > ?";
61     private static final String LOAD_ALL_AFFILIATIONS =
62         "SELECT roomID,jid,affiliation FROM mucAffiliation";
63     private static final String LOAD_ALL_MEMBERS =
64         "SELECT roomID,jid, nickname FROM mucMember";
65     private static final String LOAD_ALL_HISTORY =
66         "SELECT roomID, sender, nickname, time, subject, body FROM mucConversationLog " +
67         "WHERE time>? AND (nickname <> '' OR subject IS NOT NULL) ORDER BY time";
68     private static final String UPDATE_ROOM =
69         "UPDATE mucRoom SET modificationDate=?, naturalName=?, description=?, " +
70         "canChangeSubject=?, maxUsers=?, publicRoom=?, moderated=?, membersOnly=?, " +
71         "canInvite=?, password=?, canDiscoverJID=?, logEnabled=?, rolesToBroadcast=?, " +
72         "useReservedNick=?, canChangeNick=?, canRegister=? WHERE roomID=?";
73     private static final String ADD_ROOM =
74         "INSERT INTO mucRoom (roomID, creationDate, modificationDate, name, naturalName, " +
75         "description, lockedDate, emptyDate, canChangeSubject, maxUsers, publicRoom, moderated, " +
76         "membersOnly, canInvite, password, canDiscoverJID, logEnabled, subject, " +
77         "rolesToBroadcast, useReservedNick, canChangeNick, canRegister) VALUES (?,?,?,?,?,?,?,?," +
78             "?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
79     private static final String UPDATE_SUBJECT =
80         "UPDATE mucRoom SET subject=? WHERE roomID=?";
81     private static final String UPDATE_LOCK =
82         "UPDATE mucRoom SET lockedDate=? WHERE roomID=?";
83     private static final String UPDATE_EMPTYDATE =
84         "UPDATE mucRoom SET emptyDate=? WHERE roomID=?";
85     private static final String DELETE_ROOM =
86         "DELETE FROM mucRoom WHERE roomID=?";
87     private static final String DELETE_AFFILIATIONS =
88         "DELETE FROM mucAffiliation WHERE roomID=?";
89     private static final String DELETE_MEMBERS =
90         "DELETE FROM mucMember WHERE roomID=?";
91     private static final String ADD_MEMBER =
92         "INSERT INTO mucMember (roomID,jid,nickname) VALUES (?,?,?)";
93     private static final String UPDATE_MEMBER =
94         "UPDATE mucMember SET nickname=? WHERE roomID=? AND jid=?";
95     private static final String DELETE_MEMBER =
96         "DELETE FROM mucMember WHERE roomID=? AND jid=?";
97     private static final String ADD_AFFILIATION =
98         "INSERT INTO mucAffiliation (roomID,jid,affiliation) VALUES (?,?,?)";
99     private static final String UPDATE_AFFILIATION =
100         "UPDATE mucAffiliation SET affiliation=? WHERE roomID=? AND jid=?";
101     private static final String DELETE_AFFILIATION =
102         "DELETE FROM mucAffiliation WHERE roomID=? AND jid=?";
103
104     private static final String ADD_CONVERSATION_LOG =
105         "INSERT INTO mucConversationLog (roomID,sender,nickname,time,subject,body) " +
106         "VALUES (?,?,?,?,?,?)";
107
108     /**
109      * Returns the reserved room nickname for the bare JID in a given room or null if none.
110      *
111      * @param room the room where the user would like to obtain his reserved nickname.
112      * @param bareJID The bare jid of the user of which you'd like to obtain his reserved nickname.
113      * @return the reserved room nickname for the bare JID or null if none.
114      */
115     public static String getReservedNickname(MUCRoom room, String bareJID) {
116         Connection con = null;
117         PreparedStatement pstmt = null;
118         String answer = null;
119         try {
120             con = DbConnectionManager.getConnection();
121             pstmt = con.prepareStatement(GET_RESERVED_NAME);
122             pstmt.setLong(1, room.getID());
123             pstmt.setString(2, bareJID);
124             ResultSet rs = pstmt.executeQuery();
125             if (rs.next()) {
126                 answer = rs.getString(1);
127             }
128             rs.close();
129             pstmt.close();
130         }
131         catch (SQLException sqle) {
132             Log.error(sqle);
133         }
134         finally {
135             try { if (pstmt != null) pstmt.close(); }
136             catch (Exception e) { Log.error(e); }
137             try { if (con != null) con.close(); }
138             catch (Exception e) { Log.error(e); }
139         }
140         return answer;
141     }
142
143     /**
144      * Loads the room configuration from the database if the room was persistent.
145      *
146      * @param room the room to load from the database if persistent
147      */
148     public static void loadFromDB(MUCRoomImpl room) {
149         Connection con = null;
150         PreparedStatement pstmt = null;
151         try {
152             con = DbConnectionManager.getConnection();
153             pstmt = con.prepareStatement(LOAD_ROOM);
154             pstmt.setString(1, room.getName());
155             ResultSet rs = pstmt.executeQuery();
156             if (!rs.next()) {
157                 throw new IllegalArgumentException ("Room " + room.getName() + " was not found in the database.");
158             }
159             room.setID(rs.getLong(1));
160             room.setCreationDate(new Date(Long.parseLong(rs.getString(2).trim()))); // creation date
161 room.setModificationDate(new Date(Long.parseLong(rs.getString(3).trim()))); // modification date
162 room.setNaturalLanguageName(rs.getString(4));
163             room.setDescription(rs.getString(5));
164             room.setLockedDate(new Date(Long.parseLong(rs.getString(6).trim())));
165             if (rs.getString(7) != null) {
166                 room.setEmptyDate(new Date(Long.parseLong(rs.getString(7).trim())));
167             }
168             else {
169                 room.setEmptyDate(null);
170             }
171             room.setCanOccupantsChangeSubject(rs.getInt(8) == 1 ? true : false);
172             room.setMaxUsers(rs.getInt(9));
173             room.setPublicRoom(rs.getInt(10) == 1 ? true : false);
174             room.setModerated(rs.getInt(11) == 1 ? true : false);
175             room.setMembersOnly(rs.getInt(12) == 1 ? true : false);
176             room.setCanOccupantsInvite(rs.getInt(13) == 1 ? true : false);
177             room.setPassword(rs.getString(14));
178             room.setCanAnyoneDiscoverJID(rs.getInt(15) == 1 ? true : false);
179             room.setLogEnabled(rs.getInt(16) == 1 ? true : false);
180             room.setSubject(rs.getString(17));
181             List<String > rolesToBroadcast = new ArrayList<String >();
182             String roles = Integer.toBinaryString(rs.getInt(18));
183             if (roles.charAt(0) == '1') {
184                 rolesToBroadcast.add("moderator");
185             }
186             if (roles.length() > 1 && roles.charAt(1) == '1') {
187                 rolesToBroadcast.add("participant");
188             }
189             if (roles.length() > 2 && roles.charAt(2) == '1') {
190                 rolesToBroadcast.add("visitor");
191             }
192             room.setRolesToBroadcastPresence(rolesToBroadcast);
193             room.setLoginRestrictedToNickname(rs.getInt(19) == 1 ? true : false);
194             room.setChangeNickname(rs.getInt(20) == 1 ? true : false);
195             room.setRegistrationEnabled(rs.getInt(21) == 1 ? true : false);
196             room.setPersistent(true);
197             rs.close();
198             pstmt.close();
199
200             pstmt = con.prepareStatement(LOAD_HISTORY);
201             // Recreate the history until two days ago
202 long from = System.currentTimeMillis() - (86400000 * 2);
203             pstmt.setString(1, StringUtils.dateToMillis(new Date(from)));
204             pstmt.setLong(2, room.getID());
205             rs = pstmt.executeQuery();
206             while (rs.next()) {
207                 String senderJID = rs.getString(1);
208                 String nickname = rs.getString(2);
209                 Date sentDate = new Date(Long.parseLong(rs.getString(3).trim()));
210                 String subject = rs.getString(4);
211                 String body = rs.getString(5);
212                 // Recreate the history only for the rooms that have the conversation logging
213 // enabled
214 if (room.isLogEnabled()) {
215                     room.getRoomHistory().addOldMessage(senderJID, nickname, sentDate, subject,
216                             body);
217                 }
218             }
219             rs.close();
220             pstmt.close();
221
222             // If the room does not include the last subject in the history then recreate one if
223 // possible
224 if (!room.getRoomHistory().hasChangedSubject() && room.getSubject() != null &&
225                     room.getSubject().length() > 0) {
226                 room.getRoomHistory().addOldMessage(room.getRole().getRoleAddress().toString(),
227                         null, room.getModificationDate(), room.getSubject(), null);
228             }
229
230             pstmt = con.prepareStatement(LOAD_AFFILIATIONS);
231             pstmt.setLong(1, room.getID());
232             rs = pstmt.executeQuery();
233             while (rs.next()) {
234                 String jid = rs.getString(1);
235                 MUCRole.Affiliation affiliation = MUCRole.Affiliation.valueOf(rs.getInt(2));
236                 try {
237                     switch (affiliation) {
238                         case owner:
239                             room.addOwner(jid, room.getRole());
240                             break;
241                         case admin:
242                             room.addAdmin(jid, room.getRole());
243                             break;
244                         case outcast:
245                             room.addOutcast(jid, null, room.getRole());
246                             break;
247                         default:
248                             Log.error("Unkown affiliation value " + affiliation + " for user "
249                                     + jid + " in persistent room " + room.getID());
250                     }
251                 }
252                 catch (Exception e) {
253                     Log.error(e);
254                 }
255             }
256             rs.close();
257             pstmt.close();
258
259             pstmt = con.prepareStatement(LOAD_MEMBERS);
260             pstmt.setLong(1, room.getID());
261             rs = pstmt.executeQuery();
262             while (rs.next()) {
263                 try {
264                     room.addMember(rs.getString(1), rs.getString(2), room.getRole());
265                 }
266                 catch (Exception e) {
267                     Log.error(e);
268                 }
269             }
270             rs.close();
271             // Set now that the room's configuration is updated in the database. Note: We need to
272 // set this now since otherwise the room's affiliations will be saved to the database
273 // "again" while adding them to the room!
274 room.setSavedToDB(true);
275             if (room.getEmptyDate() == null) {
276                 // The service process was killed somehow while the room was being used. Since
277 // the room won't have occupants at this time we need to set the best date when
278 // the last occupant left the room that we can
279 room.setEmptyDate(new Date());
280             }
281         }
282         catch (SQLException sqle) {
283             Log.error(sqle);
284         }
285         finally {
286             try { if (pstmt != null) pstmt.close(); }
287             catch (Exception e) { Log.error(e); }
288             try { if (con != null) con.close(); }
289             catch (Exception e) { Log.error(e); }
290         }
291     }
292
293     /**
294      * Save the room configuration to the DB.
295      *
296      * @param room The room to save its configuration.
297      */
298     public static void saveToDB(MUCRoomImpl room) {
299         Connection con = null;
300         PreparedStatement pstmt = null;
301         try {
302             con = DbConnectionManager.getConnection();
303             if (room.wasSavedToDB()) {
304                 pstmt = con.prepareStatement(UPDATE_ROOM);
305                 pstmt.setString(1, StringUtils.dateToMillis(room.getModificationDate()));
306                 pstmt.setString(2, room.getNaturalLanguageName());
307                 pstmt.setString(3, room.getDescription());
308                 pstmt.setInt(4, (room.canOccupantsChangeSubject() ? 1 : 0));
309                 pstmt.setInt(5, room.getMaxUsers());
310                 pstmt.setInt(6, (room.isPublicRoom() ? 1 : 0));
311                 pstmt.setInt(7, (room.isModerated() ? 1 : 0));
312                 pstmt.setInt(8, (room.isMembersOnly() ? 1 : 0));
313                 pstmt.setInt(9, (room.canOccupantsInvite() ? 1 : 0));
314                 pstmt.setString(10, room.getPassword());
315                 pstmt.setInt(11, (room.canAnyoneDiscoverJID() ? 1 : 0));
316                 pstmt.setInt(12, (room.isLogEnabled() ? 1 : 0));
317                 pstmt.setInt(13, marshallRolesToBroadcast(room));
318                 pstmt.setInt(14, (room.isLoginRestrictedToNickname() ? 1 : 0));
319                 pstmt.setInt(15, (room.canChangeNickname() ? 1 : 0));
320                 pstmt.setInt(16, (room.isRegistrationEnabled() ? 1 : 0));
321                 pstmt.setLong(17, room.getID());
322                 pstmt.executeUpdate();
323             }
324             else {
325                 pstmt = con.prepareStatement(ADD_ROOM);
326                 pstmt.setLong(1, room.getID());
327                 pstmt.setString(2, StringUtils.dateToMillis(room.getCreationDate()));
328                 pstmt.setString(3, StringUtils.dateToMillis(room.getModificationDate()));
329                 pstmt.setString(4, room.getName());
330                 pstmt.setString(5, room.getNaturalLanguageName());
331                 pstmt.setString(6, room.getDescription());
332                 pstmt.setString(7, StringUtils.dateToMillis(room.getLockedDate()));
333                 Date emptyDate = room.getEmptyDate();
334                 if (emptyDate == null) {
335                     pstmt.setString(8, null);
336                 }
337                 else {
338                     pstmt.setString(8, StringUtils.dateToMillis(emptyDate));
339                 }
340                 pstmt.setInt(9, (room.canOccupantsChangeSubject() ? 1 : 0));
341                 pstmt.setInt(10, room.getMaxUsers());
342                 pstmt.setInt(11, (room.isPublicRoom() ? 1 : 0));
343                 pstmt.setInt(12, (room.isModerated() ? 1 : 0));
344                 pstmt.setInt(13, (room.isMembersOnly() ? 1 : 0));
345                 pstmt.setInt(14, (room.canOccupantsInvite() ? 1 : 0));
346                 pstmt.setString(15, room.getPassword());
347                 pstmt.setInt(16, (room.canAnyoneDiscoverJID() ? 1 : 0));
348                 pstmt.setInt(17, (room.isLogEnabled() ? 1 : 0));
349                 pstmt.setString(18, room.getSubject());
350                 pstmt.setInt(19, marshallRolesToBroadcast(room));
351                 pstmt.setInt(20, (room.isLoginRestrictedToNickname() ? 1 : 0));
352                 pstmt.setInt(21, (room.canChangeNickname() ? 1 : 0));
353                 pstmt.setInt(22, (room.isRegistrationEnabled() ? 1 : 0));
354                 pstmt.executeUpdate();
355             }
356         }
357         catch (SQLException sqle) {
358             Log.error(sqle);
359         }
360         finally {
361             try { if (pstmt != null) pstmt.close(); }
362             catch (Exception e) { Log.error(e); }
363             try { if (con != null) con.close(); }
364             catch (Exception e) { Log.error(e); }
365         }
366     }
367
368     /**
369      * Removes the room configuration and its affiliates from the database.
370      *
371      * @param room the room to remove from the database.
372      */
373     public static void deleteFromDB(MUCRoom room) {
374         if (!room.isPersistent() || !room.wasSavedToDB()) {
375             return;
376         }
377         Connection con = null;
378         PreparedStatement pstmt = null;
379         boolean abortTransaction = false;
380         try {
381             con = DbConnectionManager.getTransactionConnection();
382             pstmt = con.prepareStatement(DELETE_AFFILIATIONS);
383             pstmt.setLong(1, room.getID());
384             pstmt.executeUpdate();
385             pstmt.close();
386
387             pstmt = con.prepareStatement(DELETE_MEMBERS);
388             pstmt.setLong(1, room.getID());
389             pstmt.executeUpdate();
390             pstmt.close();
391
392             pstmt = con.prepareStatement(DELETE_ROOM);
393             pstmt.setLong(1, room.getID());
394             pstmt.executeUpdate();
395
396             // Update the room (in memory) to indicate the it's no longer in the database.
397 room.setSavedToDB(false);
398         }
399         catch (SQLException sqle) {
400             Log.error(sqle);
401             abortTransaction = true;
402         }
403         finally {
404             try { if (pstmt != null) pstmt.close(); }
405             catch (Exception e) { Log.error(e); }
406             DbConnectionManager.closeTransactionConnection(con, abortTransaction);
407         }
408     }
409
410     /**
411      * Loads all the rooms that had occupants after a given date from the database. This query
412      * will be executed only when the service is starting up.
413      *
414      * @param chatserver the chat server that will hold the loaded rooms.
415      * @param emptyDate rooms that hadn't been used before this date won't be loaded.
416      * @param packetRouter the PacketRouter that loaded rooms will use to send packets.
417      * @return a collection with all the persistent rooms.
418      */
419     public static Collection<MUCRoom> loadRoomsFromDB(MultiUserChatServer chatserver,
420             Date emptyDate, PacketRouter packetRouter) {
421         Connection con = null;
422         PreparedStatement pstmt = null;
423         Map<Long ,MUCRoom> rooms = new HashMap<Long ,MUCRoom>();
424         try {
425             con = DbConnectionManager.getConnection();
426             pstmt = con.prepareStatement(LOAD_ALL_ROOMS);
427             pstmt.setString(1, StringUtils.dateToMillis(emptyDate));
428             ResultSet rs = pstmt.executeQuery();
429             MUCRoomImpl room = null;
430             while (rs.next()) {
431                 room = new MUCRoomImpl(chatserver, rs.getString(4), packetRouter);
432                 room.setID(rs.getLong(1));
433                 room.setCreationDate(new Date(Long.parseLong(rs.getString(2).trim()))); // creation date
434 room.setModificationDate(new Date(Long.parseLong(rs.getString(3).trim()))); // modification date
435 room.setNaturalLanguageName(rs.getString(5));
436                 room.setDescription(rs.getString(6));
437                 room.setLockedDate(new Date(Long.parseLong(rs.getString(7).trim())));
438                 if (rs.getString(8) != null) {
439                     room.setEmptyDate(new Date(Long.parseLong(rs.getString(8).trim())));
440                 }
441                 else {
442                     room.setEmptyDate(null);
443                 }
444                 room.setCanOccupantsChangeSubject(rs.getInt(9) == 1 ? true : false);
445                 room.setMaxUsers(rs.getInt(10));
446                 room.setPublicRoom(rs.getInt(11) == 1 ? true : false);
447                 room.setModerated(rs.getInt(12) == 1 ? true : false);
448                 room.setMembersOnly(rs.getInt(13) == 1 ? true : false);
449                 room.setCanOccupantsInvite(rs.getInt(14) == 1 ? true : false);
450                 room.setPassword(rs.getString(15));
451                 room.setCanAnyoneDiscoverJID(rs.getInt(16) == 1 ? true : false);
452                 room.setLogEnabled(rs.getInt(17) == 1 ? true : false);
453                 room.setSubject(rs.getString(18));
454                 List<String > rolesToBroadcast = new ArrayList<String >();
455                 String roles = Integer.toBinaryString(rs.getInt(19));
456                 if (roles.charAt(0) == '1') {
457                     rolesToBroadcast.add("moderator");
458                 }
459                 if (roles.length() > 1 && roles.charAt(1) == '1') {
460                     rolesToBroadcast.add("participant");
461                 }
462                 if (roles.length() > 2 && roles.charAt(2) == '1') {
463                     rolesToBroadcast.add("visitor");
464                 }
465                 room.setRolesToBroadcastPresence(rolesToBroadcast);
466                 room.setLoginRestrictedToNickname(rs.getInt(20) == 1 ? true : false);
467                 room.setChangeNickname(rs.getInt(21) == 1 ? true : false);
468                 room.setRegistrationEnabled(rs.getInt(22) == 1 ? true : false);
469                 room.setPersistent(true);
470                 rooms.put(room.getID(), room);
471             }
472             rs.close();
473             pstmt.close();
474
475             pstmt = con.prepareStatement(LOAD_ALL_HISTORY);
476             // Recreate the history until two days ago
477 long from = System.currentTimeMillis() - (86400000 * 2);
478             pstmt.setString(1, StringUtils.dateToMillis(new Date(from)));
479             // Load the rooms conversations from the last two days
480 rs = pstmt.executeQuery();
481             while (rs.next()) {
482                 room = (MUCRoomImpl) rooms.get(rs.getLong(1));
483                 // Skip to the next position if the room does not exist
484 if (room == null) {
485                     continue;
486                 }
487                 String senderJID = rs.getString(2);
488                 String nickname = rs.getString(3);
489                 Date sentDate = new Date(Long.parseLong(rs.getString(4).trim()));
490                 String subject = rs.getString(5);
491                 String body = rs.getString(6);
492                 try {
493                     // Recreate the history only for the rooms that have the conversation logging
494 // enabled
495 if (room.isLogEnabled()) {
496                         room.getRoomHistory().addOldMessage(senderJID, nickname, sentDate, subject,
497                                 body);
498                     }
499                 }
500                 catch (Exception e) {
501                     Log.error(e);
502                 }
503             }
504             rs.close();
505             pstmt.close();
506
507             // Add the last known room subject to the room history only for those rooms that still
508 // don't have in their histories the last room subject
509 for (MUCRoom loadedRoom : rooms.values()) {
510                 if (!loadedRoom.getRoomHistory().hasChangedSubject() &&
511                         loadedRoom.getSubject() != null &&
512                         loadedRoom.getSubject().length() > 0) {
513                     loadedRoom.getRoomHistory().addOldMessage(loadedRoom.getRole().getRoleAddress()
514                             .toString(), null,
515                             loadedRoom.getModificationDate(), loadedRoom.getSubject(), null);
516                 }
517             }
518
519             pstmt = con.prepareStatement(LOAD_ALL_AFFILIATIONS);
520             rs = pstmt.executeQuery();
521             while (rs.next()) {
522                 long roomID = rs.getLong(1);
523                 String jid = rs.getString(2);
524                 MUCRole.Affiliation affiliation = MUCRole.Affiliation.valueOf(rs.getInt(3));
525                 room = (MUCRoomImpl) rooms.get(roomID);
526                 // Skip to the next position if the room does not exist
527 if (room == null) {
528                     continue;
529                 }
530                 try {
531                     switch (affiliation) {
532                         case owner:
533                             room.addOwner(jid, room.getRole());
534                             break;
535                         case admin:
536                             room.addAdmin(jid, room.getRole());
537                             break;
538                         case outcast:
539                             room.addOutcast(jid, null, room.getRole());
540                             break;
541                         default:
542                             Log.error("Unkown affiliation value " + affiliation + " for user "
543                                     + jid + " in persistent room " + room.getID());
544                     }
545                 }
546                 catch (Exception e) {
547                     Log.error(e);
548                 }
549             }
550             rs.close();
551             pstmt.close();
552
553             pstmt = con.prepareStatement(LOAD_ALL_MEMBERS);
554             rs = pstmt.executeQuery();
555             while (rs.next()) {
556                 room = (MUCRoomImpl) rooms.get(rs.getLong(1));
557                 // Skip to the next position if the room does not exist
558 if (room == null) {
559                     continue;
560                 }
561                 try {
562                     room.addMember(rs.getString(2), rs.getString(3), room.getRole());
563                 }
564                 catch (Exception e) {
565                     Log.error(e);
566                 }
567             }
568             rs.close();
569         }
570         catch (SQLException sqle) {
571             Log.error(sqle);
572         }
573         finally {
574             try { if (pstmt != null) pstmt.close(); }
575             catch (Exception e) { Log.error(e); }
576             try { if (con != null) con.close(); }
577             catch (Exception e) { Log.error(e); }
578         }
579         // Set now that the room's configuration is updated in the database. Note: We need to
580 // set this now since otherwise the room's affiliations will be saved to the database
581 // "again" while adding them to the room!
582 for (MUCRoom room : rooms.values()) {
583             room.setSavedToDB(true);
584             if (room.getEmptyDate() == null) {
585                 // The service process was killed somehow while the room was being used. Since
586 // the room won't have occupants at this time we need to set the best date when
587 // the last occupant left the room that we can
588 room.setEmptyDate(new Date());
589             }
590         }
591
592         return rooms.values();
593     }
594
595     /**
596      * Updates the room's subject in the database.
597      *
598      * @param room the room to update its subject in the database.
599      */
600     public static void updateRoomSubject(MUCRoom room) {
601         if (!room.isPersistent() || !room.wasSavedToDB()) {
602             return;
603         }
604
605         Connection con = null;
606         PreparedStatement pstmt = null;
607         try {
608             con = DbConnectionManager.getConnection();
609             pstmt = con.prepareStatement(UPDATE_SUBJECT);
610             pstmt.setString(1, room.getSubject());
611             pstmt.setLong(2, room.getID());
612             pstmt.executeUpdate();
613         }
614         catch (SQLException sqle) {
615             Log.error(sqle);
616         }
617         finally {
618             try { if (pstmt != null) pstmt.close(); }
619             catch (Exception e) { Log.error(e); }
620             try { if (con != null) con.close(); }
621             catch (Exception e) { Log.error(e); }
622         }
623     }
624
625     /**
626      * Updates the room's lock status in the database.
627      *
628      * @param room the room to update its lock status in the database.
629      */
630     public static void updateRoomLock(MUCRoomImpl room) {
631         if (!room.isPersistent() || !room.wasSavedToDB()) {
632             return;
633         }
634
635         Connection con = null;
636         PreparedStatement pstmt = null;
637         try {
638             con = DbConnectionManager.getConnection();
639             pstmt = con.prepareStatement(UPDATE_LOCK);
640             pstmt.setString(1, StringUtils.dateToMillis(room.getLockedDate()));
641             pstmt.setLong(2, room.getID());
642             pstmt.executeUpdate();
643         }
644         catch (SQLException sqle) {
645             Log.error(sqle);
646         }
647         finally {
648             try { if (pstmt != null) pstmt.close(); }
649             catch (Exception e) { Log.error(e); }
650             try { if (con != null) con.close(); }
651             catch (Exception e) { Log.error(e); }
652         }
653     }
654
655     /**
656      * Updates the room's lock status in the database.
657      *
658      * @param room the room to update its lock status in the database.
659      */
660     public static void updateRoomEmptyDate(MUCRoom room) {
661         if (!room.isPersistent() || !room.wasSavedToDB()) {
662             return;
663         }
664
665         Connection con = null;
666         PreparedStatement pstmt = null;
667         try {
668             con = DbConnectionManager.getConnection();
669             pstmt = con.prepareStatement(UPDATE_EMPTYDATE);
670             Date emptyDate = room.getEmptyDate();
671             if (emptyDate == null) {
672                 pstmt.setString(1, null);
673             }
674             else {
675                 pstmt.setString(1, StringUtils.dateToMillis(emptyDate));
676             }
677             pstmt.setLong(2, room.getID());
678             pstmt.executeUpdate();
679         }
680         catch (SQLException sqle) {
681             Log.error(sqle);
682         }
683         finally {
684             try { if (pstmt != null) pstmt.close(); }
685             catch (Exception e) { Log.error(e); }
686             try { if (con != null) con.close(); }
687             catch (Exception e) { Log.error(e); }
688         }
689     }
690
691     /**
692      * Update the DB with the new affiliation of the user in the room. The new information will be
693      * saved only if the room is_persistent and has already been saved to the database previously.
694      *
695      * @param room The room where the affiliation of the user was updated.
696      * @param bareJID The bareJID of the user to update this affiliation.
697      * @param nickname The reserved nickname of the user in the room or null if none.
698      * @param newAffiliation the new affiliation of the user in the room.
699      * @param oldAffiliation the previous affiliation of the user in the room.
700      */
701     public static void saveAffiliationToDB(MUCRoom room, String bareJID, String nickname,
702             MUCRole.Affiliation newAffiliation, MUCRole.Affiliation oldAffiliation)
703     {
704         if (!room.isPersistent() || !room.wasSavedToDB()) {
705             return;
706         }
707         if (MUCRole.Affiliation.none == oldAffiliation) {
708             if (MUCRole.Affiliation.member == newAffiliation) {
709                 // Add the user to the members table
710 Connection con = null;
711                 PreparedStatement pstmt = null;
712                 try {
713                     con = DbConnectionManager.getConnection();
714                     pstmt = con.prepareStatement(ADD_MEMBER);
715                     pstmt.setLong(1, room.getID());
716                     pstmt.setString(2, bareJID);
717                     pstmt.setString(3, nickname);
718                     pstmt.executeUpdate();
719                 }
720                 catch (SQLException sqle) {
721                     Log.error(sqle);
722                 }
723                 finally {
724                     try { if (pstmt != null) pstmt.close(); }
725                     catch (Exception e) { Log.error(e); }
726                     try { if (con != null) con.close(); }
727                     catch (Exception e) { Log.error(e); }
728                 }
729             }
730             else {
731                 // Add the user to the generic affiliations table
732 Connection con = null;
733                 PreparedStatement pstmt = null;
734                 try {
735                     con = DbConnectionManager.getConnection();
736                     pstmt = con.prepareStatement(ADD_AFFILIATION);
737                     pstmt.setLong(1, room.getID());
738                     pstmt.setString(2, bareJID);
739                     pstmt.setInt(3, newAffiliation.getValue());
740                     pstmt.executeUpdate();
741                 }
742                 catch (SQLException sqle) {
743                     Log.error(sqle);
744                 }
745                 finally {
746                     try { if (pstmt != null) pstmt.close(); }
747                     catch (Exception e) { Log.error(e); }
748                     try { if (con != null) con.close(); }
749                     catch (Exception e) { Log.error(e); }
750                 }
751             }
752         }
753         else {
754             if (MUCRole.Affiliation.member == newAffiliation &&
755                     MUCRole.Affiliation.member == oldAffiliation)
756             {
757                 // Update the member's data in the member table.
758 Connection con = null;
759                 PreparedStatement pstmt = null;
760                 try {
761                     con = DbConnectionManager.getConnection();
762                     pstmt = con.prepareStatement(UPDATE_MEMBER);
763                     pstmt.setString(1, nickname);
764                     pstmt.setLong(2, room.getID());
765                     pstmt.setString(3, bareJID);
766                     pstmt.executeUpdate();
767                 }
768                 catch (SQLException sqle) {
769                     Log.error(sqle);
770                 }
771                 finally {
772                     try { if (pstmt != null) pstmt.close(); }
773                     catch (Exception e) { Log.error(e); }
774                     try { if (con != null) con.close(); }
775                     catch (Exception e) { Log.error(e); }
776                 }
777             }
778             else if (MUCRole.Affiliation.member == newAffiliation) {
779                 Connection con = null;
780                 PreparedStatement pstmt = null;
781                 boolean abortTransaction = false;
782                 try {
783                     // Remove the user from the generic affiliations table
784 con = DbConnectionManager.getTransactionConnection();
785                     pstmt = con.prepareStatement(DELETE_AFFILIATION);
786                     pstmt.setLong(1, room.getID());
787                     pstmt.setString(2, bareJID);
788                     pstmt.executeUpdate();
789                     pstmt.close();
790
791                     // Add them as a member.
792 pstmt = con.prepareStatement(ADD_MEMBER);
793                     pstmt.setLong(1, room.getID());
794                     pstmt.setString(2, bareJID);
795                     pstmt.setString(3, nickname);
796                     pstmt.executeUpdate();
797                 }
798                 catch (SQLException sqle) {
799                     Log.error(sqle);
800                     abortTransaction = true;
801                 }
802                 finally {
803                     try { if (pstmt != null) pstmt.close(); }
804                     catch (Exception e) { Log.error(e); }
805                     DbConnectionManager.closeTransactionConnection(con, abortTransaction);
806                 }
807             }
808             else if (MUCRole.Affiliation.member == oldAffiliation) {
809                 Connection con = null;
810                 PreparedStatement pstmt = null;
811                 boolean abortTransaction = false;
812                 try {
813                     con = DbConnectionManager.getTransactionConnection();
814                     pstmt = con.prepareStatement(DELETE_MEMBER);
815                     pstmt.setLong(1, room.getID());
816                     pstmt.setString(2, bareJID);
817                     pstmt.executeUpdate();
818                     pstmt.close();
819
820                     pstmt = con.prepareStatement(ADD_AFFILIATION);
821                     pstmt.setLong(1, room.getID());
822                     pstmt.setString(2, bareJID);
823                     pstmt.setInt(3, newAffiliation.getValue());
824                     pstmt.executeUpdate();
825                 }
826                 catch (SQLException sqle) {
827                     Log.error(sqle);
828                     abortTransaction = true;
829                 }
830                 finally {
831                     try { if (pstmt != null) pstmt.close(); }
832                     catch (Exception e) { Log.error(e); }
833                     DbConnectionManager.closeTransactionConnection(con, abortTransaction);
834                 }
835             }
836             else {
837                 // Update the user in the generic affiliations table.
838 Connection con = null;
839                 PreparedStatement pstmt = null;
840                 try {
841                     con = DbConnectionManager.getConnection();
842                     pstmt = con.prepareStatement(UPDATE_AFFILIATION);
843                     pstmt.setInt(1, newAffiliation.getValue());
844                     pstmt.setLong(2, room.getID());
845                     pstmt.setString(3, bareJID);
846                     pstmt.executeUpdate();
847                 }
848                 catch (SQLException sqle) {
849                     Log.error(sqle);
850                 }
851                 finally {
852                     try { if (pstmt != null) pstmt.close(); }
853                     catch (Exception e) { Log.error(e); }
854                     try { if (con != null) con.close(); }
855                     catch (Exception e) { Log.error(e); }
856                 }
857             }
858         }
859     }
860
861     /**
862      * Removes the affiliation of the user from the DB if the room is persistent.
863      *
864      * @param room The room where the affiliation of the user was removed.
865      * @param bareJID The bareJID of the user to remove his affiliation.
866      * @param oldAffiliation the previous affiliation of the user in the room.
867      */
868     public static void removeAffiliationFromDB(MUCRoom room, String bareJID,
869             MUCRole.Affiliation oldAffiliation)
870     {
871         if (room.isPersistent() && room.wasSavedToDB()) {
872             if (MUCRole.Affiliation.member == oldAffiliation) {
873                 // Remove the user from the members table
874 Connection con = null;
875                 PreparedStatement pstmt = null;
876                 try {
877                     con = DbConnectionManager.getConnection();
878                     pstmt = con.prepareStatement(DELETE_MEMBER);
879                     pstmt.setLong(1, room.getID());
880                     pstmt.setString(2, bareJID);
881                     pstmt.executeUpdate();
882                 }
883                 catch (SQLException sqle) {
884                     Log.error(sqle);
885                 }
886                 finally {
887                     try { if (pstmt != null) pstmt.close(); }
888                     catch (Exception e) { Log.error(e); }
889                     try { if (con != null) con.close(); }
890                     catch (Exception e) { Log.error(e); }
891                 }
892             }
893             else {
894                 // Remove the user from the generic affiliations table
895 Connection con = null;
896                 PreparedStatement pstmt = null;
897                 try {
898                     con = DbConnectionManager.getConnection();
899                     pstmt = con.prepareStatement(DELETE_AFFILIATION);
900                     pstmt.setLong(1, room.getID());
901                     pstmt.setString(2, bareJID);
902                     pstmt.executeUpdate();
903                 }
904                 catch (SQLException sqle) {
905                     Log.error(sqle);
906                 }
907                 finally {
908                     try { if (pstmt != null) pstmt.close(); }
909                     catch (Exception e) { Log.error(e); }
910                     try { if (con != null) con.close(); }
911                     catch (Exception e) { Log.error(e); }
912                 }
913             }
914         }
915     }
916
917     /**
918      * Saves the conversation log entry to the database.
919      *
920      * @param entry the ConversationLogEntry to save to the database.
921      * @return true if the ConversationLogEntry was saved successfully to the database.
922      */
923     public static boolean saveConversationLogEntry(ConversationLogEntry entry) {
924         Connection con = null;
925         PreparedStatement pstmt = null;
926         try {
927             con = DbConnectionManager.getConnection();
928             pstmt = con.prepareStatement(ADD_CONVERSATION_LOG);
929             pstmt.setLong(1, entry.getRoomID());
930             pstmt.setString(2, entry.getSender().toString());
931             pstmt.setString(3, entry.getNickname());
932             pstmt.setString(4, StringUtils.dateToMillis(entry.getDate()));
933             pstmt.setString(5, entry.getSubject());
934             pstmt.setString(6, entry.getBody());
935             pstmt.executeUpdate();
936             return true;
937         }
938         catch (SQLException sqle) {
939             Log.error("Error saving conversation log entry", sqle);
940             return false;
941         }
942         finally {
943             try { if (pstmt != null) pstmt.close(); }
944             catch (Exception e) { Log.error(e); }
945             try { if (con != null) con.close(); }
946             catch (Exception e) { Log.error(e); }
947         }
948     }
949
950     /**
951      * Returns an integer based on the binary representation of the roles to broadcast.
952      *
953      * @param room the room to marshall its roles to broadcast.
954      * @return an integer based on the binary representation of the roles to broadcast.
955      */
956     private static int marshallRolesToBroadcast(MUCRoom room) {
957         StringBuilder buffer = new StringBuilder ();
958         buffer.append((room.canBroadcastPresence("moderator") ? "1" : "0"));
959         buffer.append((room.canBroadcastPresence("participant") ? "1" : "0"));
960         buffer.append((room.canBroadcastPresence("visitor") ? "1" : "0"));
961         return Integer.parseInt(buffer.toString(), 2);
962     }
963 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags