package models;
import com.google.common.base.Strings;
import org.postgresql.util.PGobject;
import play.twirl.api.HtmlFormat;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
public class Report {
public long id;
public Timestamp createdAt;
public String json;
public String packageName;
public String appVersionName;
public String appVersionCode;
public String androidVersion;
public String phoneModel;
public String logcat;
public String stackTrace;
public String reason;
private static List<Report> fromResultSet(ResultSet set) throws SQLException {
List<Report> reports = new ArrayList<>();
while (set.next()) {
Report report = new Report();
report.id = set.getLong("id");
report.createdAt = set.getTimestamp("created_at");
report.json = set.getString("json");
report.packageName = Strings.nullToEmpty(set.getString("package_name")).replace("\"", "");
report.appVersionName = Strings.nullToEmpty(set.getString("app_version_name")).replace("\"", "");
report.appVersionCode = Strings.nullToEmpty(set.getString("app_version_code")).replace("\"", "");
report.androidVersion = Strings.nullToEmpty(set.getString("android_version")).replace("\"", "");
report.phoneModel = Strings.nullToEmpty(set.getString("phone_model")).replace("\"", "");
// logcat and stack trace are already escaped
report.logcat = HtmlFormat.escape(Strings.nullToEmpty(set.getString("logcat")).replace("\"", "")).text().replaceAll("\\\\t", " ").replaceAll("\\\\n", "<br />");
report.stackTrace = HtmlFormat.escape(Strings.nullToEmpty(set.getString("stack_trace")).replace("\"", "")).text().replaceAll("\\\\t", " ").replaceAll("\\\\n", "<br />");
report.reason = report.stackTrace.substring(0, report.stackTrace.indexOf("<br />"));
reports.add(report);
}
return reports;
}
private static String commonSelectReportString = "SELECT " +
"*, " +
"json->'PACKAGE_NAME' as package_name, " +
"json->'ANDROID_VERSION' as android_version, " +
"json->'APP_VERSION_NAME' as app_version_name, " +
"json->'APP_VERSION_CODE' as app_version_code, " +
"json->'PHONE_MODEL' as phone_model, " +
"json->'LOGCAT' as logcat, " +
"json->'STACK_TRACE' as stack_trace " +
"FROM reports "
;
private static String selectReportListString = commonSelectReportString +
"WHERE " +
"(CAST(json->'PACKAGE_NAME' as text) = ? OR ?) " +
"AND (CAST(json->'APP_VERSION_NAME' as text) = ? OR ?) " +
"AND (CAST(json->'ANDROID_VERSION' as text) = ? OR ?) " +
"AND (CAST(json->'LOGCAT' as text) ILIKE ? {escape '$'} OR CAST(json->'STACK_TRACE' as text) ILIKE ? {escape '$'} OR ?) " + // TODO pg_trgm index or full text search
"ORDER BY created_at DESC " +
"LIMIT ? OFFSET ?"
;
private static String selectReportString = commonSelectReportString +
"WHERE " +
"id = ?"
;
private static String selectCountString = "SELECT " +
"count(*) " +
"FROM reports " +
"WHERE " +
"(CAST(json->'PACKAGE_NAME' as text) = ? OR ?) " +
"AND (CAST(json->'APP_VERSION_NAME' as text) = ? OR ?) " +
"AND (CAST(json->'ANDROID_VERSION' as text) = ? OR ?) " +
"AND (CAST(json->'LOGCAT' as text) ILIKE ? {escape '$'} OR CAST(json->'STACK_TRACE' as text) ILIKE ? {escape '$'} OR ?) "
;
public static Page<Report> page(
Connection connection,
long page,
long pageSize,
String packageName,
String appVersionName,
String androidVersion,
String logcatStackTraceFilter
) throws SQLException {
final boolean isPackageNameFilterDisabled = packageName == null || packageName.isEmpty();
final boolean isAppVersionNameFilterDisabled = appVersionName == null || appVersionName.isEmpty();
final boolean isAndroidVersionFilterDisabled = androidVersion == null || androidVersion.isEmpty();
final boolean isLogcatStackTraceFilterDisabled = logcatStackTraceFilter == null || logcatStackTraceFilter.isEmpty();
final String filterPackageName = "\"" + Strings.nullToEmpty(packageName) + "\"";
final String filterAppVersionName = "\"" + Strings.nullToEmpty(appVersionName) + "\"";
final String filterAndroidVersion = "\"" + Strings.nullToEmpty(androidVersion) + "\"";
final String escapedLogcatStackTraceFilter = isLogcatStackTraceFilterDisabled ? "" : logcatStackTraceFilter.replace("$", "$$").replace("%", "$%").replace("_", "$_");
long total;
try (PreparedStatement countStatement = connection.prepareStatement(selectCountString)) {
countStatement.setString(1, filterPackageName);
countStatement.setBoolean(2, isPackageNameFilterDisabled);
countStatement.setString(3, filterAppVersionName);
countStatement.setBoolean(4, isAppVersionNameFilterDisabled);
countStatement.setString(5, filterAndroidVersion);
countStatement.setBoolean(6, isAndroidVersionFilterDisabled);
countStatement.setString(7, "%" + escapedLogcatStackTraceFilter + "%");
countStatement.setString(8, "%" + escapedLogcatStackTraceFilter + "%");
countStatement.setBoolean(9, isLogcatStackTraceFilterDisabled);
try (ResultSet countResultSet = countStatement.executeQuery()) {
countResultSet.next();
total = countResultSet.getLong(1);
}
}
try (PreparedStatement selectStatement = connection.prepareStatement(selectReportListString)) {
selectStatement.setString(1, filterPackageName);
selectStatement.setBoolean(2, isPackageNameFilterDisabled);
selectStatement.setString(3, filterAppVersionName);
selectStatement.setBoolean(4, isAppVersionNameFilterDisabled);
selectStatement.setString(5, filterAndroidVersion);
selectStatement.setBoolean(6, isAndroidVersionFilterDisabled);
selectStatement.setString(7, "%" + escapedLogcatStackTraceFilter + "%");
selectStatement.setString(8, "%" + escapedLogcatStackTraceFilter + "%");
selectStatement.setBoolean(9, isLogcatStackTraceFilterDisabled);
selectStatement.setLong(10, pageSize);
selectStatement.setLong(11, page * pageSize);
try (ResultSet selectResultSet = selectStatement.executeQuery()) {
return new Page<>(
fromResultSet(selectResultSet),
total,
page,
pageSize
);
}
}
}
public static Report findById(Connection connection, long id) throws SQLException {
try (PreparedStatement selectStatement = connection.prepareStatement(selectReportString)) {
selectStatement.setLong(1, id);
try (ResultSet selectResultSet = selectStatement.executeQuery()) {
List<Report> reports = fromResultSet(selectResultSet);
if (reports.size() > 0) {
return reports.get(0);
}
}
}
return null;
}
public static boolean save(Connection connection, String json) throws SQLException {
PGobject jsonbObject = new PGobject();
jsonbObject.setType("jsonb");
jsonbObject.setValue(json);
try (PreparedStatement insertStatement = connection.prepareStatement("INSERT INTO reports (created_at, json) VALUES(?, ?)")) {
insertStatement.setTimestamp(1, new Timestamp(Calendar.getInstance().getTimeInMillis()));
insertStatement.setObject(2, jsonbObject);
return insertStatement.executeUpdate() == 1;
}
}
// TODO experiment with jsonb gin indexes
public static List<String> getAppVersionNames(Connection connection) throws SQLException {
List<String> names = new ArrayList<>();
try (Statement namesStatement = connection.createStatement()) {
try (ResultSet resultSet = namesStatement.executeQuery("SELECT DISTINCT json->'APP_VERSION_NAME' AS name FROM reports")) {
while (resultSet.next()) {
String name = resultSet.getString("name");
if (name != null) {
names.add(name.replace("\"", ""));
}
}
}
}
return names;
}
public static List<String> getAndroidVersions(Connection connection) throws SQLException {
List<String> names = new ArrayList<>();
try (Statement namesStatement = connection.createStatement()) {
try (ResultSet resultSet = namesStatement.executeQuery("SELECT DISTINCT json->'ANDROID_VERSION' AS version FROM reports")) {
while (resultSet.next()) {
String version = resultSet.getString("version");
if (version != null) {
names.add(version.replace("\"", ""));
}
}
}
}
return names;
}
public static List<String> getPackageNames(Connection connection) throws SQLException {
List<String> names = new ArrayList<>();
try (Statement namesStatement = connection.createStatement()) {
try (ResultSet resultSet = namesStatement.executeQuery("SELECT DISTINCT json->'PACKAGE_NAME' AS name FROM reports")) {
while (resultSet.next()) {
String name = resultSet.getString("name");
if (name != null) {
names.add(name.replace("\"", ""));
}
}
}
}
return names;
}
}