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.getUpgradeOptimizedConnection();
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.getUpgradeOptimizedConnection();
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, new String[] {"<3", ">_>", "<_<"},
087 new String[] {":love:", ":glare:", ":dry:"});
088
089 updateMessageBody(messageId, body);
090 }
091 }
092 finally {
093 DataAccess.cleanUp(con, ps, rs);
094 }
095
096 try {
097 con = DataAccess.getUpgradeOptimizedConnection();
098
099 StringBundler sb = new StringBundler(4);
100
101 sb.append("select messageFlag.messageId as messageId from ");
102 sb.append("MBMessageFlag messageFlag inner join MBMessage ");
103 sb.append("message on messageFlag.messageId = message.messageId ");
104 sb.append("where message.parentMessageId != 0 and flag = 3");
105
106 String sql = sb.toString();
107
108 ps = con.prepareStatement(sql);
109
110 rs = ps.executeQuery();
111
112 while (rs.next()) {
113 long messageId = rs.getLong("messageId");
114
115 updateMessageAnswer(messageId, true);
116 }
117 }
118 finally {
119 DataAccess.cleanUp(con, ps, rs);
120 }
121 }
122
123 protected void updateMessageAnswer(long messageId, boolean answer)
124 throws Exception {
125
126 Connection con = null;
127 PreparedStatement ps = null;
128
129 try {
130 con = DataAccess.getUpgradeOptimizedConnection();
131
132 ps = con.prepareStatement(
133 "update MBMessage set answer = ? where messageId = " +
134 messageId);
135
136 ps.setBoolean(1, answer);
137
138 ps.executeUpdate();
139 }
140 finally {
141 DataAccess.cleanUp(con, ps);
142 }
143 }
144
145 protected void updateMessageBody(long messageId, String body)
146 throws Exception {
147
148 Connection con = null;
149 PreparedStatement ps = null;
150
151 try {
152 con = DataAccess.getUpgradeOptimizedConnection();
153
154 ps = con.prepareStatement(
155 "update MBMessage set body = ? where messageId = " + messageId);
156
157 ps.setString(1, body);
158
159 ps.executeUpdate();
160 }
161 finally {
162 DataAccess.cleanUp(con, ps);
163 }
164 }
165
166 protected void updateThread() throws Exception {
167 Connection con = null;
168 PreparedStatement ps = null;
169 ResultSet rs = null;
170
171 try {
172 con = DataAccess.getUpgradeOptimizedConnection();
173
174 ps = con.prepareStatement(
175 "select MBThread.threadId, MBMessage.companyId, " +
176 "MBMessage.userId from MBThread inner join MBMessage on " +
177 "MBThread.rootMessageId = MBMessage.messageId");
178
179 rs = ps.executeQuery();
180
181 while (rs.next()) {
182 long threadId = rs.getLong("threadId");
183 long companyId = rs.getLong("companyId");
184 long userId = rs.getLong("userId");
185
186 runSQL(
187 "update MBThread set companyId = " + companyId +
188 ", rootMessageUserId = " + userId +
189 " where threadId = " + threadId);
190 }
191 }
192 finally {
193 DataAccess.cleanUp(con, ps, rs);
194 }
195
196 try {
197 con = DataAccess.getUpgradeOptimizedConnection();
198
199 ps = con.prepareStatement(
200 "select threadId from MBMessageFlag where flag = 2");
201
202 rs = ps.executeQuery();
203
204 while (rs.next()) {
205 long threadId = rs.getLong("threadId");
206
207 updateThreadQuestion(threadId, true);
208 }
209 }
210 finally {
211 DataAccess.cleanUp(con, ps, rs);
212 }
213
214 try {
215 con = DataAccess.getUpgradeOptimizedConnection();
216
217 StringBundler sb = new StringBundler(4);
218
219 sb.append("select messageFlag.threadId as threadId from ");
220 sb.append("MBMessageFlag messageFlag inner join MBMessage ");
221 sb.append("message on messageFlag.messageId = message.messageId ");
222 sb.append("where message.parentMessageId = 0 and flag = 3");
223
224 ps = con.prepareStatement(sb.toString());
225
226 rs = ps.executeQuery();
227
228 while (rs.next()) {
229 long threadId = rs.getLong("threadId");
230
231 updateThreadQuestion(threadId, true);
232 }
233 }
234 finally {
235 DataAccess.cleanUp(con, ps, rs);
236 }
237 }
238
239 protected void updateThreadFlag() throws Exception {
240 Connection con = null;
241 PreparedStatement ps = null;
242 ResultSet rs = null;
243
244 try {
245 con = DataAccess.getUpgradeOptimizedConnection();
246
247 ps = con.prepareStatement(
248 "select userId, threadId, modifiedDate from MBMessageFlag " +
249 "where flag = 1");
250
251 rs = ps.executeQuery();
252
253 while (rs.next()) {
254 long userId = rs.getLong("userId");
255 long threadId = rs.getLong("threadId");
256 Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
257
258 addThreadFlag(increment(), userId, threadId, modifiedDate);
259 }
260 }
261 finally {
262 DataAccess.cleanUp(con, ps, rs);
263 }
264
265 runSQL("drop table MBMessageFlag");
266 }
267
268 protected void updateThreadQuestion(long threadId, boolean question)
269 throws Exception {
270
271 Connection con = null;
272 PreparedStatement ps = null;
273
274 try {
275 con = DataAccess.getUpgradeOptimizedConnection();
276
277 ps = con.prepareStatement(
278 "update MBThread set question = ? where threadId =" + threadId);
279
280 ps.setBoolean(1, question);
281
282 ps.executeUpdate();
283 }
284 finally {
285 DataAccess.cleanUp(con, ps);
286 }
287 }
288
289 }