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