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