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.DataAccess;
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.PropsKeys;
023    import com.liferay.portal.kernel.util.StringBundler;
024    import com.liferay.portal.upgrade.AutoBatchPreparedStatementUtil;
025    import com.liferay.portal.util.PortalUtil;
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                    PreparedStatement ps = null;
045                    ResultSet rs = null;
046    
047                    try {
048                            ps = connection.prepareStatement(
049                                    "select distinct classNameId from RatingsEntry");
050    
051                            rs = ps.executeQuery();
052    
053                            while (rs.next()) {
054                                    upgradeRatingsEntry(rs.getLong("classNameId"));
055                            }
056                    }
057                    finally {
058                            DataAccess.cleanUp(ps, rs);
059                    }
060            }
061    
062            protected void upgradeRatingsEntry(long classNameId) throws Exception {
063                    String className = PortalUtil.getClassName(classNameId);
064    
065                    if (ArrayUtil.contains(
066                                    PropsValues.RATINGS_UPGRADE_THUMBS_CLASS_NAMES, className)) {
067    
068                            upgradeRatingsEntryThumbs(classNameId);
069                    }
070                    else {
071                            int defaultRatingsStarsNormalizationFactor = GetterUtil.getInteger(
072                                    PropsUtil.get(
073                                            PropsKeys.RATINGS_UPGRADE_STARS_NORMALIZATION_FACTOR,
074                                            new Filter("default")),
075                                    5);
076    
077                            int ratingsStarsNormalizationFactor = GetterUtil.getInteger(
078                                    PropsUtil.get(
079                                            PropsKeys.RATINGS_UPGRADE_STARS_NORMALIZATION_FACTOR,
080                                            new Filter(className)),
081                                    defaultRatingsStarsNormalizationFactor);
082    
083                            upgradeRatingsEntryStars(
084                                    classNameId, ratingsStarsNormalizationFactor);
085                    }
086            }
087    
088            protected void upgradeRatingsEntryStars(
089                            long classNameId, int normalizationFactor)
090                    throws Exception {
091    
092                    PreparedStatement ps = null;
093    
094                    try {
095                            ps = connection.prepareStatement(
096                                    "update RatingsEntry set score = score / ? where classNameId " +
097                                            "= ?");
098    
099                            ps.setInt(1, normalizationFactor);
100                            ps.setLong(2, classNameId);
101    
102                            ps.executeUpdate();
103                    }
104                    finally {
105                            DataAccess.cleanUp(ps);
106                    }
107            }
108    
109            protected void upgradeRatingsEntryThumbs(long classNameId)
110                    throws Exception {
111    
112                    PreparedStatement ps = null;
113    
114                    try {
115                            ps = connection.prepareStatement(
116                                    "update RatingsEntry set score = ? where score = ? and " +
117                                            "classNameId = ?");
118    
119                            ps.setDouble(1, 0);
120                            ps.setDouble(2, -1);
121                            ps.setLong(3, classNameId);
122    
123                            ps.executeUpdate();
124                    }
125                    finally {
126                            DataAccess.cleanUp(ps);
127                    }
128            }
129    
130            protected void upgradeRatingsStats() throws Exception {
131                    StringBundler sb = new StringBundler(4);
132    
133                    sb.append("select classNameId, classPK, count(1) as totalEntries, ");
134                    sb.append("sum(RatingsEntry.score) as totalScore, ");
135                    sb.append("sum(RatingsEntry.score) / count(1) as averageScore from ");
136                    sb.append("RatingsEntry group by classNameId, classPK");
137    
138                    String selectSQL = sb.toString();
139    
140                    String updateSQL =
141                            "update RatingsStats set totalEntries = ?, totalScore = ?, " +
142                                    "averageScore = ? where classNameId = ? and classPK = ?";
143    
144                    try (PreparedStatement ps1 = connection.prepareStatement(selectSQL);
145                                    ResultSet rs = ps1.executeQuery();
146                                            PreparedStatement ps2 =
147                                                    AutoBatchPreparedStatementUtil.autoBatch(
148                                                            connection.prepareStatement(updateSQL))) {
149    
150                            while (rs.next()) {
151                                    ps2.setInt(1, rs.getInt("totalEntries"));
152                                    ps2.setDouble(2, rs.getDouble("totalScore"));
153                                    ps2.setDouble(3, rs.getDouble("averageScore"));
154                                    ps2.setLong(4, rs.getLong("classNameId"));
155                                    ps2.setLong(5, rs.getLong("classPK"));
156    
157                                    ps2.addBatch();
158                            }
159    
160                            ps2.executeBatch();
161                    }
162            }
163    
164    }