001    /**
002     * Copyright (c) 2000-2011 Liferay, Inc. All rights reserved.
003     *
004     * The contents of this file are subject to the terms of the Liferay Enterprise
005     * Subscription License ("License"). You may not use this file except in
006     * compliance with the License. You can obtain a copy of the License by
007     * contacting Liferay, Inc. See the License for the specific language governing
008     * permissions and limitations under the License, including but not limited to
009     * distribution rights of the Software.
010     *
011     *
012     *
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 from MBMessageFlag ");
103                            sb.append("messageFlag inner join MBMessage message on ");
104                            sb.append("messageFlag.messageId = message.messageId where ");
105                            sb.append("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("messageFlag.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                            StringBundler sb = new StringBundler(4);
212    
213                            sb.append("select messageFlag.threadId from MBMessageFlag ");
214                            sb.append("messageFlag inner join MBMessage message on ");
215                            sb.append("messageFlag.messageId = message.messageId where ");
216                            sb.append("message.parentMessageId = 0 and flag = 3");
217    
218                            ps = con.prepareStatement(sb.toString());
219    
220                            rs = ps.executeQuery();
221    
222                            while (rs.next()) {
223                                    long threadId = rs.getLong("messageFlag.threadId");
224    
225                                    updateThreadQuestion(threadId, true);
226                            }
227                    }
228                    finally {
229                            DataAccess.cleanUp(con, ps, rs);
230                    }
231            }
232    
233            protected void updateThreadFlag() throws Exception {
234                    Connection con = null;
235                    PreparedStatement ps = null;
236                    ResultSet rs = null;
237    
238                    try {
239                            con = DataAccess.getConnection();
240    
241                            ps = con.prepareStatement(
242                                    "select userId, threadId, modifiedDate from MBMessageFlag " +
243                                            "where flag = 1");
244    
245                            rs = ps.executeQuery();
246    
247                            while (rs.next()) {
248                                    long userId = rs.getLong("userId");
249                                    long threadId = rs.getLong("threadId");
250                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
251    
252                                    addThreadFlag(increment(), userId, threadId, modifiedDate);
253                            }
254                    }
255                    finally {
256                            DataAccess.cleanUp(con, ps, rs);
257                    }
258    
259                    runSQL("drop table MBMessageFlag");
260            }
261    
262            protected void updateThreadQuestion(long threadId, boolean question)
263                    throws Exception {
264    
265                    Connection con = null;
266                    PreparedStatement ps = null;
267    
268                    try {
269                            con = DataAccess.getConnection();
270    
271                            ps = con.prepareStatement(
272                                    "update MBThread set question = ? where threadId =" +
273                                            threadId);
274    
275                            ps.setBoolean(1, question);
276    
277                            ps.executeUpdate();
278                    }
279                    finally {
280                            DataAccess.cleanUp(con, ps);
281                    }
282            }
283    
284    }