001
014
015 package com.liferay.portal.verify;
016
017 import com.liferay.portal.kernel.dao.jdbc.DataAccess;
018 import com.liferay.portal.kernel.util.StringBundler;
019
020 import java.sql.Connection;
021 import java.sql.PreparedStatement;
022
023
026 public class VerifyRatings extends VerifyProcess {
027
028 @Override
029 protected void doVerify() throws Exception {
030 normalizeRatingStats();
031 }
032
033 protected void normalizeRatingStats() throws Exception {
034 Connection con = null;
035 PreparedStatement ps = null;
036
037 try {
038 con = DataAccess.getUpgradeOptimizedConnection();
039
040 StringBundler sb = new StringBundler(6);
041
042 sb.append("update RatingsStats set ");
043 sb.append(_SQL_UPDATE_AVERAGE_SCORE);
044 sb.append(", ");
045 sb.append(_SQL_UPDATE_TOTAL_ENTRIES);
046 sb.append(", ");
047 sb.append(_SQL_UPDATE_TOTAL_SCORE);
048
049 ps = con.prepareStatement(sb.toString());
050
051 ps.executeUpdate();
052 }
053 finally {
054 DataAccess.cleanUp(con, ps);
055 }
056 }
057
058 private static final String _SQL_FROM_WHERE_CLAUSE =
059 "from RatingsEntry where RatingsStats.classPK = RatingsEntry.classPK " +
060 "and RatingsStats.classNameId = RatingsEntry.classNameId " +
061 "group by classNameId, classPK";
062
063 private static final String _SQL_UPDATE_AVERAGE_SCORE =
064 "averageScore = coalesce((select sum(RatingsEntry.score) / count(1) " +
065 _SQL_FROM_WHERE_CLAUSE + "), 0)";
066
067 private static final String _SQL_UPDATE_TOTAL_ENTRIES =
068 "totalEntries = coalesce((select count(1) " +
069 _SQL_FROM_WHERE_CLAUSE + "), 0)";
070
071 private static final String _SQL_UPDATE_TOTAL_SCORE =
072 "totalScore = coalesce((select sum(RatingsEntry.score) " +
073 _SQL_FROM_WHERE_CLAUSE + "), 0)";
074
075 }