001    /**
002     * Copyright (c) 2000-present 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.upgrade.UpgradeProcess;
018    import com.liferay.portal.kernel.util.LoggingTimer;
019    import com.liferay.portal.kernel.util.StringBundler;
020    import com.liferay.portal.kernel.util.StringUtil;
021    
022    import java.sql.PreparedStatement;
023    import java.sql.ResultSet;
024    import java.sql.Timestamp;
025    
026    /**
027     * @author Shuyang Zhou
028     */
029    public class UpgradeMessageBoards extends UpgradeProcess {
030    
031            protected void addThreadFlag(
032                            long threadFlagId, long userId, long threadId,
033                            Timestamp modifiedDate)
034                    throws Exception {
035    
036                    try (PreparedStatement ps = connection.prepareStatement(
037                                    "insert into MBThreadFlag (threadFlagId, userId, " +
038                                            "modifiedDate, threadId) values (?, ?, ?, ?)")) {
039    
040                            ps.setLong(1, threadFlagId);
041                            ps.setLong(2, userId);
042                            ps.setTimestamp(3, modifiedDate);
043                            ps.setLong(4, threadId);
044    
045                            ps.executeUpdate();
046                    }
047            }
048    
049            @Override
050            protected void doUpgrade() throws Exception {
051                    updateMessage();
052                    updateThread();
053                    updateThreadFlag();
054            }
055    
056            protected void updateMessage() throws Exception {
057                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
058                            try (PreparedStatement ps = connection.prepareStatement(
059                                            "select messageId, body from MBMessage where (body like " +
060                                                    "'%<3%') or (body like '%>_>%') or (body like '%<_<%'" +
061                                                            ")");
062                                    ResultSet rs = ps.executeQuery()) {
063    
064                                    while (rs.next()) {
065                                            long messageId = rs.getLong("messageId");
066                                            String body = rs.getString("body");
067    
068                                            body = StringUtil.replace(
069                                                    body, new String[] {"<3", ">_>", "<_<"},
070                                                    new String[] {":love:", ":glare:", ":dry:"});
071    
072                                            updateMessageBody(messageId, body);
073                                    }
074                            }
075    
076                            StringBundler sb = new StringBundler(4);
077    
078                            sb.append("select messageFlag.messageId as messageId from ");
079                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
080                            sb.append("message on messageFlag.messageId = message.messageId ");
081                            sb.append("where message.parentMessageId != 0 and flag = 3");
082    
083                            try (PreparedStatement ps = connection.prepareStatement(
084                                            sb.toString());
085                                    ResultSet rs = ps.executeQuery()) {
086    
087                                    while (rs.next()) {
088                                            long messageId = rs.getLong("messageId");
089    
090                                            updateMessageAnswer(messageId, true);
091                                    }
092                            }
093                    }
094            }
095    
096            protected void updateMessageAnswer(long messageId, boolean answer)
097                    throws Exception {
098    
099                    try (PreparedStatement ps = connection.prepareStatement(
100                                    "update MBMessage set answer = ? where messageId = " +
101                                            messageId)) {
102    
103                            ps.setBoolean(1, answer);
104    
105                            ps.executeUpdate();
106                    }
107            }
108    
109            protected void updateMessageBody(long messageId, String body)
110                    throws Exception {
111    
112                    try (PreparedStatement ps = connection.prepareStatement(
113                                    "update MBMessage set body = ? where messageId = " +
114                                            messageId)) {
115    
116                            ps.setString(1, body);
117    
118                            ps.executeUpdate();
119                    }
120            }
121    
122            protected void updateThread() throws Exception {
123                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
124                            try (PreparedStatement ps = connection.prepareStatement(
125                                            "select MBThread.threadId, MBMessage.companyId, " +
126                                                    "MBMessage.userId from MBThread inner join MBMessage " +
127                                                            "on MBThread.rootMessageId = MBMessage.messageId");
128                                    ResultSet rs = ps.executeQuery()) {
129    
130                                    while (rs.next()) {
131                                            long threadId = rs.getLong("threadId");
132                                            long companyId = rs.getLong("companyId");
133                                            long userId = rs.getLong("userId");
134    
135                                            runSQL(
136                                                    "update MBThread set companyId = " + companyId +
137                                                            ", rootMessageUserId = " + userId +
138                                                                    " where threadId = " + threadId);
139                                    }
140                            }
141    
142                            try (PreparedStatement ps = connection.prepareStatement(
143                                            "select threadId from MBMessageFlag where flag = 2");
144                                    ResultSet rs = ps.executeQuery()) {
145    
146                                    while (rs.next()) {
147                                            long threadId = rs.getLong("threadId");
148    
149                                            updateThreadQuestion(threadId, true);
150                                    }
151                            }
152    
153                            StringBundler sb = new StringBundler(4);
154    
155                            sb.append("select messageFlag.threadId as threadId from ");
156                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
157                            sb.append("message on messageFlag.messageId = message.messageId ");
158                            sb.append("where message.parentMessageId = 0 and flag = 3");
159    
160                            try (PreparedStatement ps = connection.prepareStatement(
161                                            sb.toString());
162                                    ResultSet rs = ps.executeQuery()) {
163    
164                                    while (rs.next()) {
165                                            long threadId = rs.getLong("threadId");
166    
167                                            updateThreadQuestion(threadId, true);
168                                    }
169                            }
170                    }
171            }
172    
173            protected void updateThreadFlag() throws Exception {
174                    try (LoggingTimer loggingTimer = new LoggingTimer();
175                            PreparedStatement ps = connection.prepareStatement(
176                                    "select userId, threadId, modifiedDate from MBMessageFlag " +
177                                            "where flag = 1");
178                            ResultSet rs = ps.executeQuery()) {
179    
180                            while (rs.next()) {
181                                    long userId = rs.getLong("userId");
182                                    long threadId = rs.getLong("threadId");
183                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
184    
185                                    addThreadFlag(increment(), userId, threadId, modifiedDate);
186                            }
187    
188                            runSQL("drop table MBMessageFlag");
189                    }
190            }
191    
192            protected void updateThreadQuestion(long threadId, boolean question)
193                    throws Exception {
194    
195                    try (PreparedStatement ps = connection.prepareStatement(
196                                    "update MBThread set question = ? where threadId = " +
197                                            threadId)) {
198    
199                            ps.setBoolean(1, question);
200    
201                            ps.executeUpdate();
202                    }
203            }
204    
205    }