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.v7_0_0;
016    
017    import com.liferay.portal.kernel.configuration.Filter;
018    import com.liferay.portal.kernel.dao.jdbc.AutoBatchPreparedStatementUtil;
019    import com.liferay.portal.kernel.upgrade.UpgradeProcess;
020    import com.liferay.portal.kernel.util.ArrayUtil;
021    import com.liferay.portal.kernel.util.GetterUtil;
022    import com.liferay.portal.kernel.util.LoggingTimer;
023    import com.liferay.portal.kernel.util.PortalUtil;
024    import com.liferay.portal.kernel.util.PropsKeys;
025    import com.liferay.portal.kernel.util.StringBundler;
026    import com.liferay.portal.util.PropsUtil;
027    import com.liferay.portal.util.PropsValues;
028    
029    import java.sql.PreparedStatement;
030    import java.sql.ResultSet;
031    
032    /**
033     * @author Sergio Gonz??lez
034     */
035    public class UpgradeRatings extends UpgradeProcess {
036    
037            @Override
038            protected void doUpgrade() throws Exception {
039                    upgradeRatingsEntry();
040                    upgradeRatingsStats();
041            }
042    
043            protected void upgradeRatingsEntry() throws Exception {
044                    try (LoggingTimer loggingTimer = new LoggingTimer();
045                            PreparedStatement ps = connection.prepareStatement(
046                                    "select distinct classNameId from RatingsEntry");
047                            ResultSet rs = ps.executeQuery()) {
048    
049                            while (rs.next()) {
050                                    upgradeRatingsEntry(rs.getLong("classNameId"));
051                            }
052                    }
053            }
054    
055            protected void upgradeRatingsEntry(long classNameId) throws Exception {
056                    String className = PortalUtil.getClassName(classNameId);
057    
058                    if (ArrayUtil.contains(
059                                    PropsValues.RATINGS_UPGRADE_THUMBS_CLASS_NAMES, className)) {
060    
061                            upgradeRatingsEntryThumbs(classNameId);
062                    }
063                    else {
064                            int defaultRatingsStarsNormalizationFactor = GetterUtil.getInteger(
065                                    PropsUtil.get(
066                                            PropsKeys.RATINGS_UPGRADE_STARS_NORMALIZATION_FACTOR,
067                                            new Filter("default")),
068                                    5);
069    
070                            int ratingsStarsNormalizationFactor = GetterUtil.getInteger(
071                                    PropsUtil.get(
072                                            PropsKeys.RATINGS_UPGRADE_STARS_NORMALIZATION_FACTOR,
073                                            new Filter(className)),
074                                    defaultRatingsStarsNormalizationFactor);
075    
076                            upgradeRatingsEntryStars(
077                                    classNameId, ratingsStarsNormalizationFactor);
078                    }
079            }
080    
081            protected void upgradeRatingsEntryStars(
082                            long classNameId, int normalizationFactor)
083                    throws Exception {
084    
085                    try (PreparedStatement ps = connection.prepareStatement(
086                                    "update RatingsEntry set score = score / ? where classNameId " +
087                                            "= ?")) {
088    
089                            ps.setInt(1, normalizationFactor);
090                            ps.setLong(2, classNameId);
091    
092                            ps.executeUpdate();
093                    }
094            }
095    
096            protected void upgradeRatingsEntryThumbs(long classNameId)
097                    throws Exception {
098    
099                    try (PreparedStatement ps = connection.prepareStatement(
100                                    "update RatingsEntry set score = ? where score = ? and " +
101                                            "classNameId = ?")) {
102    
103                            ps.setDouble(1, 0);
104                            ps.setDouble(2, -1);
105                            ps.setLong(3, classNameId);
106    
107                            ps.executeUpdate();
108                    }
109            }
110    
111            protected void upgradeRatingsStats() throws Exception {
112                    try (LoggingTimer loggingTimer = new LoggingTimer()) {
113                            StringBundler sb = new StringBundler(4);
114    
115                            sb.append("select classNameId, classPK, count(1) as ");
116                            sb.append("totalEntries, sum(RatingsEntry.score) as totalScore, ");
117                            sb.append("sum(RatingsEntry.score) / count(1) as averageScore ");
118                            sb.append("from RatingsEntry group by classNameId, classPK");
119    
120                            String selectSQL = sb.toString();
121    
122                            String updateSQL =
123                                    "update RatingsStats set totalEntries = ?, totalScore = ?, " +
124                                            "averageScore = ? where classNameId = ? and classPK = ?";
125    
126                            try (PreparedStatement ps1 = connection.prepareStatement(selectSQL);
127                                    ResultSet rs = ps1.executeQuery();
128                                    PreparedStatement ps2 =
129                                            AutoBatchPreparedStatementUtil.autoBatch(
130                                                    connection.prepareStatement(updateSQL))) {
131    
132                                    while (rs.next()) {
133                                            ps2.setInt(1, rs.getInt("totalEntries"));
134                                            ps2.setDouble(2, rs.getDouble("totalScore"));
135                                            ps2.setDouble(3, rs.getDouble("averageScore"));
136                                            ps2.setLong(4, rs.getLong("classNameId"));
137                                            ps2.setLong(5, rs.getLong("classPK"));
138    
139                                            ps2.addBatch();
140                                    }
141    
142                                    ps2.executeBatch();
143                            }
144                    }
145            }
146    
147    }