001    /**
002     * Copyright (c) 2000-2012 Liferay, Inc. All rights reserved.
003     *
004     * This library is free software; you can redistribute it and/or modify it under
005     * the terms of the GNU Lesser General Public License as published by the Free
006     * Software Foundation; either version 2.1 of the License, or (at your option)
007     * any later version.
008     *
009     * This library is distributed in the hope that it will be useful, but WITHOUT
010     * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
011     * FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
012     * details.
013     */
014    
015    package com.liferay.portal.upgrade.v6_1_0;
016    
017    import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
019    import com.liferay.portal.kernel.util.StringBundler;
020    import com.liferay.portal.kernel.util.StringUtil;
021    
022    import java.sql.Connection;
023    import java.sql.PreparedStatement;
024    import java.sql.ResultSet;
025    import java.sql.Timestamp;
026    
027    /**
028     * @author Shuyang Zhou
029     */
030    public class UpgradeMessageBoards extends UpgradeProcess {
031    
032            protected void addThreadFlag(
033                            long threadFlagId, long userId, long threadId,
034                            Timestamp modifiedDate)
035                    throws Exception {
036    
037                    Connection con = null;
038                    PreparedStatement ps = null;
039                    ResultSet rs = null;
040    
041                    try {
042                            con = DataAccess.getConnection();
043    
044                            ps = con.prepareStatement(
045                                    "insert into MBThreadFlag (threadFlagId, userId, " +
046                                            "modifiedDate, threadId) values (?, ?, ?, ?)");
047    
048                            ps.setLong(1, threadFlagId);
049                            ps.setLong(2, userId);
050                            ps.setTimestamp(3, modifiedDate);
051                            ps.setLong(4, threadId);
052    
053                            ps.executeUpdate();
054                    }
055                    finally {
056                            DataAccess.cleanUp(con, ps, rs);
057                    }
058            }
059    
060            @Override
061            protected void doUpgrade() throws Exception {
062                    updateMessage();
063                    updateThread();
064                    updateThreadFlag();
065            }
066    
067            protected void updateMessage() throws Exception {
068                    Connection con = null;
069                    PreparedStatement ps = null;
070                    ResultSet rs = null;
071    
072                    try {
073                            con = DataAccess.getConnection();
074    
075                            ps = con.prepareStatement(
076                                    "select messageId, body from MBMessage where (body like " +
077                                            "'%<3%') or (body like '%>_>%') or (body like '%<_<%')");
078    
079                            rs = ps.executeQuery();
080    
081                            while (rs.next()) {
082                                    long messageId = rs.getLong("messageId");
083                                    String body = rs.getString("body");
084    
085                                    body = StringUtil.replace(
086                                            body,
087                                            new String[] {"<3", ">_>", "<_<"},
088                                            new String[] {":love:", ":glare:", ":dry:"});
089    
090                                    updateMessageBody(messageId, body);
091                            }
092                    }
093                    finally {
094                            DataAccess.cleanUp(con, ps, rs);
095                    }
096    
097                    try {
098                            con = DataAccess.getConnection();
099    
100                            StringBundler sb = new StringBundler(4);
101    
102                            sb.append("select messageFlag.messageId as messageId from ");
103                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
104                            sb.append("message on messageFlag.messageId = message.messageId ");
105                            sb.append("where message.parentMessageId != 0 and flag = 3");
106    
107                            String sql = sb.toString();
108    
109                            ps = con.prepareStatement(sql);
110    
111                            rs = ps.executeQuery();
112    
113                            while (rs.next()) {
114                                    long messageId = rs.getLong("messageId");
115    
116                                    updateMessageAnswer(messageId, true);
117                            }
118                    }
119                    finally {
120                            DataAccess.cleanUp(con, ps, rs);
121                    }
122            }
123    
124            protected void updateMessageAnswer(long messageId, boolean answer)
125                    throws Exception {
126    
127                    Connection con = null;
128                    PreparedStatement ps = null;
129    
130                    try {
131                            con = DataAccess.getConnection();
132    
133                            ps = con.prepareStatement(
134                                    "update MBMessage set answer = ? where messageId = " +
135                                            messageId);
136    
137                            ps.setBoolean(1, answer);
138    
139                            ps.executeUpdate();
140                    }
141                    finally {
142                            DataAccess.cleanUp(con, ps);
143                    }
144            }
145    
146            protected void updateMessageBody(long messageId, String body)
147                    throws Exception {
148    
149                    Connection con = null;
150                    PreparedStatement ps = null;
151    
152                    try {
153                            con = DataAccess.getConnection();
154    
155                            ps = con.prepareStatement(
156                                    "update MBMessage set body = ? where messageId = " + messageId);
157    
158                            ps.setString(1, body);
159    
160                            ps.executeUpdate();
161                    }
162                    finally {
163                            DataAccess.cleanUp(con, ps);
164                    }
165            }
166    
167            protected void updateThread() throws Exception {
168                    Connection con = null;
169                    PreparedStatement ps = null;
170                    ResultSet rs = null;
171    
172                    try {
173                            con = DataAccess.getConnection();
174    
175                            ps = con.prepareStatement(
176                                    "select MBThread.threadId, MBMessage.companyId, " +
177                                            "MBMessage.userId from MBThread inner join MBMessage on " +
178                                                    "MBThread.rootMessageId = MBMessage.messageId");
179    
180                            rs = ps.executeQuery();
181    
182                            while (rs.next()) {
183                                    long threadId = rs.getLong("threadId");
184                                    long companyId = rs.getLong("companyId");
185                                    long userId = rs.getLong("userId");
186    
187                                    runSQL(
188                                            "update MBThread set companyId = " + companyId +
189                                                    ", rootMessageUserId = " + userId +
190                                                            " where threadId = " + threadId);
191                            }
192                    }
193                    finally {
194                            DataAccess.cleanUp(con, ps, rs);
195                    }
196    
197                    try {
198                            con = DataAccess.getConnection();
199    
200                            ps = con.prepareStatement(
201                                    "select threadId from MBMessageFlag where flag = 2");
202    
203                            rs = ps.executeQuery();
204    
205                            while (rs.next()) {
206                                    long threadId = rs.getLong("threadId");
207    
208                                    updateThreadQuestion(threadId, true);
209                            }
210                    }
211                    finally {
212                            DataAccess.cleanUp(con, ps, rs);
213                    }
214    
215                    try {
216                            con = DataAccess.getConnection();
217    
218                            StringBundler sb = new StringBundler(4);
219    
220                            sb.append("select messageFlag.threadId as threadId from ");
221                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
222                            sb.append("message on messageFlag.messageId = message.messageId ");
223                            sb.append("where message.parentMessageId = 0 and flag = 3");
224    
225                            ps = con.prepareStatement(sb.toString());
226    
227                            rs = ps.executeQuery();
228    
229                            while (rs.next()) {
230                                    long threadId = rs.getLong("threadId");
231    
232                                    updateThreadQuestion(threadId, true);
233                            }
234                    }
235                    finally {
236                            DataAccess.cleanUp(con, ps, rs);
237                    }
238            }
239    
240            protected void updateThreadFlag() throws Exception {
241                    Connection con = null;
242                    PreparedStatement ps = null;
243                    ResultSet rs = null;
244    
245                    try {
246                            con = DataAccess.getConnection();
247    
248                            ps = con.prepareStatement(
249                                    "select userId, threadId, modifiedDate from MBMessageFlag " +
250                                            "where flag = 1");
251    
252                            rs = ps.executeQuery();
253    
254                            while (rs.next()) {
255                                    long userId = rs.getLong("userId");
256                                    long threadId = rs.getLong("threadId");
257                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
258    
259                                    addThreadFlag(increment(), userId, threadId, modifiedDate);
260                            }
261                    }
262                    finally {
263                            DataAccess.cleanUp(con, ps, rs);
264                    }
265    
266                    runSQL("drop table MBMessageFlag");
267            }
268    
269            protected void updateThreadQuestion(long threadId, boolean question)
270                    throws Exception {
271    
272                    Connection con = null;
273                    PreparedStatement ps = null;
274    
275                    try {
276                            con = DataAccess.getConnection();
277    
278                            ps = con.prepareStatement(
279                                    "update MBThread set question = ? where threadId =" + threadId);
280    
281                            ps.setBoolean(1, question);
282    
283                            ps.executeUpdate();
284                    }
285                    finally {
286                            DataAccess.cleanUp(con, ps);
287                    }
288            }
289    
290    }