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.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 PreparedStatement ps = null;
034
035 try {
036 StringBundler sb = new StringBundler(6);
037
038 sb.append("update RatingsStats set ");
039 sb.append(_SQL_UPDATE_AVERAGE_SCORE);
040 sb.append(", ");
041 sb.append(_SQL_UPDATE_TOTAL_ENTRIES);
042 sb.append(", ");
043 sb.append(_SQL_UPDATE_TOTAL_SCORE);
044
045 ps = connection.prepareStatement(sb.toString());
046
047 ps.executeUpdate();
048 }
049 finally {
050 DataAccess.cleanUp(ps);
051 }
052 }
053
054 private static final String _SQL_FROM_WHERE_CLAUSE =
055 "from RatingsEntry where RatingsStats.classPK = RatingsEntry.classPK " +
056 "and RatingsStats.classNameId = RatingsEntry.classNameId " +
057 "group by classNameId, classPK";
058
059 private static final String _SQL_UPDATE_AVERAGE_SCORE =
060 "averageScore = coalesce((select sum(RatingsEntry.score) / count(1) " +
061 _SQL_FROM_WHERE_CLAUSE + "), 0)";
062
063 private static final String _SQL_UPDATE_TOTAL_ENTRIES =
064 "totalEntries = coalesce((select count(1) " +
065 _SQL_FROM_WHERE_CLAUSE + "), 0)";
066
067 private static final String _SQL_UPDATE_TOTAL_SCORE =
068 "totalScore = coalesce((select sum(RatingsEntry.score) " +
069 _SQL_FROM_WHERE_CLAUSE + "), 0)";
070
071 }