001
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
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 }