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