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_0_12_to_6_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    
021    import java.sql.PreparedStatement;
022    import java.sql.ResultSet;
023    import java.sql.Timestamp;
024    
025    /**
026     * @author Shuyang Zhou
027     */
028    public class UpgradeMessageBoards extends UpgradeProcess {
029    
030            protected void addThreadFlag(
031                            long threadFlagId, long userId, long threadId,
032                            Timestamp modifiedDate)
033                    throws Exception {
034    
035                    try (PreparedStatement ps = connection.prepareStatement(
036                                    "insert into MBThreadFlag (threadFlagId, userId, " +
037                                            "modifiedDate, threadId) values (?, ?, ?, ?)")) {
038    
039                            ps.setLong(1, threadFlagId);
040                            ps.setLong(2, userId);
041                            ps.setTimestamp(3, modifiedDate);
042                            ps.setLong(4, threadId);
043    
044                            ps.executeUpdate();
045                    }
046            }
047    
048            @Override
049            protected void doUpgrade() throws Exception {
050                    updateMessage();
051                    updateThread();
052                    updateThreadFlag();
053            }
054    
055            protected void updateMessage() throws Exception {
056                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
057                            StringBundler sb = new StringBundler(4);
058    
059                            sb.append("select messageFlag.messageId as messageId from ");
060                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
061                            sb.append("message on messageFlag.messageId = message.messageId ");
062                            sb.append("where message.parentMessageId != 0 and flag = 3");
063    
064                            String sql = sb.toString();
065    
066                            try (PreparedStatement ps = connection.prepareStatement(sql);
067                                    ResultSet rs = ps.executeQuery()) {
068    
069                                    while (rs.next()) {
070                                            long messageId = rs.getLong("messageId");
071    
072                                            updateMessageAnswer(messageId, true);
073                                    }
074                            }
075                    }
076            }
077    
078            protected void updateMessageAnswer(long messageId, boolean answer)
079                    throws Exception {
080    
081                    try (PreparedStatement ps = connection.prepareStatement(
082                                    "update MBMessage set answer = ? where messageId = " +
083                                            messageId)) {
084    
085                            ps.setBoolean(1, answer);
086    
087                            ps.executeUpdate();
088                    }
089            }
090    
091            protected void updateThread() throws Exception {
092                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
093                            try (PreparedStatement ps = connection.prepareStatement(
094                                            "select threadId from MBMessageFlag where flag = 2");
095                                    ResultSet rs = ps.executeQuery()) {
096    
097                                    while (rs.next()) {
098                                            long threadId = rs.getLong("threadId");
099    
100                                            updateThreadQuestion(threadId, true);
101                                    }
102                            }
103    
104                            StringBundler sb = new StringBundler(4);
105    
106                            sb.append("select messageFlag.threadId as threadId from ");
107                            sb.append("MBMessageFlag messageFlag inner join MBMessage ");
108                            sb.append("message on messageFlag.messageId = message.messageId ");
109                            sb.append("where message.parentMessageId = 0 and flag = 3");
110    
111                            try (PreparedStatement ps = connection.prepareStatement(
112                                            sb.toString());
113                                    ResultSet rs = ps.executeQuery()) {
114    
115                                    while (rs.next()) {
116                                            long threadId = rs.getLong("threadId");
117    
118                                            updateThreadQuestion(threadId, true);
119                                    }
120                            }
121                    }
122            }
123    
124            protected void updateThreadFlag() throws Exception {
125                    try (LoggingTimer loggingTimer = new LoggingTimer();
126                            PreparedStatement ps = connection.prepareStatement(
127                                    "select userId, threadId, modifiedDate from MBMessageFlag " +
128                                            "where flag = 1");
129                            ResultSet rs = ps.executeQuery()) {
130    
131                            while (rs.next()) {
132                                    long userId = rs.getLong("userId");
133                                    long threadId = rs.getLong("threadId");
134                                    Timestamp modifiedDate = rs.getTimestamp("modifiedDate");
135    
136                                    addThreadFlag(increment(), userId, threadId, modifiedDate);
137                            }
138                    }
139    
140                    runSQL("drop table MBMessageFlag");
141            }
142    
143            protected void updateThreadQuestion(long threadId, boolean question)
144                    throws Exception {
145    
146                    try (PreparedStatement ps = connection.prepareStatement(
147                                    "update MBThread set question = ? where threadId = " +
148                                            threadId)) {
149    
150                            ps.setBoolean(1, question);
151    
152                            ps.executeUpdate();
153                    }
154            }
155    
156    }