From 538190e8467a555615fbaf1ada3eed44631e10b4 Mon Sep 17 00:00:00 2001 From: Chris Jaekl Date: Tue, 29 Sep 2015 22:23:09 +0900 Subject: [PATCH] Partial implemenation of load-Analysis code. Modified storage of TIMESTAMPTZ to ye olde workaround: store milliseconds since the Unix epoch. It seems that, even in 2015, we still haven't got a reliable mechanism to load TIMESTAMPTZ via JDBC, unless you roll your own converter from java.sql.Timestamp.toString(), and that is way too brittle (and more work than I feel like engaging in). --- prod/net/jaekl/cfb/analyze/Analysis.java | 15 +++-- prod/net/jaekl/cfb/db/Column.java | 2 +- prod/net/jaekl/cfb/db/Row.java | 8 +++ prod/net/jaekl/cfb/db/Sort.java | 16 +++++ prod/net/jaekl/cfb/db/driver/DbDriver.java | 70 +++++++++++++++++++--- prod/net/jaekl/cfb/store/DbStore.java | 61 +++++++++++++++++++ 6 files changed, 157 insertions(+), 15 deletions(-) create mode 100644 prod/net/jaekl/cfb/db/Sort.java diff --git a/prod/net/jaekl/cfb/analyze/Analysis.java b/prod/net/jaekl/cfb/analyze/Analysis.java index f61d44d..66c0b88 100644 --- a/prod/net/jaekl/cfb/analyze/Analysis.java +++ b/prod/net/jaekl/cfb/analyze/Analysis.java @@ -17,12 +17,14 @@ import org.xml.sax.XMLReader; import org.xml.sax.helpers.XMLReaderFactory; public class Analysis { + long m_id; BugCollection m_bugCollection; String m_buildNumber; Date m_start; // Date/time when analysis was started Date m_end; public Analysis(String buildNumber) { + m_id = (-1); m_bugCollection = null; m_buildNumber = buildNumber; m_start = new Date(); @@ -30,9 +32,15 @@ public class Analysis { } public BugCollection getBugCollection() { return m_bugCollection; } + public long getId() { return m_id; } public String getBuildNumber() { return m_buildNumber; } public Date getStart() { return m_start; } - public Date getEnd() { return m_end; } + public Date getEnd() { return m_end; } // the end time (when FindBugs was done analyzing) + + public void setBugCollection(BugCollection bugs) { m_bugCollection = bugs; } + public void setId(long id) { m_id = id; } + public void setStart(Date start) { m_start = start; } + public void setEnd(Date date) { m_end = date; } public void parse(InputSource xml) throws FileNotFoundException, IOException, SAXException { @@ -46,11 +54,6 @@ public class Analysis { reader.parse(xml); } - // Set the end time (when FindBugs was done analyzing) - public void setEnd(Date date) - { - m_end = date; - } public void dump(PrintWriter pw) { diff --git a/prod/net/jaekl/cfb/db/Column.java b/prod/net/jaekl/cfb/db/Column.java index 527ad7d..db748b2 100644 --- a/prod/net/jaekl/cfb/db/Column.java +++ b/prod/net/jaekl/cfb/db/Column.java @@ -4,7 +4,7 @@ package net.jaekl.cfb.db; public class Column { public enum Type { - CHAR, INTEGER, TIMESTAMP, TIMESTAMPTZ, VARCHAR + CHAR, INTEGER, TIMESTAMPTZ, VARCHAR }; public enum Null { NOT_NULL, NULL diff --git a/prod/net/jaekl/cfb/db/Row.java b/prod/net/jaekl/cfb/db/Row.java index c5d5478..f25b0ae 100644 --- a/prod/net/jaekl/cfb/db/Row.java +++ b/prod/net/jaekl/cfb/db/Row.java @@ -32,6 +32,14 @@ public class Row { return num.longValue(); } + public java.util.Date getDate(int index) throws TypeMismatchException + { + checkType(index, Column.Type.INTEGER); + long milliseconds = (Long)m_values[index]; + java.util.Date date = new java.util.Date(milliseconds); + return date; + } + protected void checkType(int index, Column.Type type) throws TypeMismatchException { Column column = m_columns[index]; if (column.getType().equals(type)) { diff --git a/prod/net/jaekl/cfb/db/Sort.java b/prod/net/jaekl/cfb/db/Sort.java new file mode 100644 index 0000000..f1d0deb --- /dev/null +++ b/prod/net/jaekl/cfb/db/Sort.java @@ -0,0 +1,16 @@ +package net.jaekl.cfb.db; + +public class Sort { + public static enum Direction { ASCENDING, DESCENDING }; + + private Direction m_dir; + private Column m_col; + + public Sort(Column col, Direction dir) { + m_col = col; + m_dir = dir; + } + + public Column getColumn() { return m_col; } + public Direction getDirection() { return m_dir; } +} diff --git a/prod/net/jaekl/cfb/db/driver/DbDriver.java b/prod/net/jaekl/cfb/db/driver/DbDriver.java index f85db06..eea3e22 100644 --- a/prod/net/jaekl/cfb/db/driver/DbDriver.java +++ b/prod/net/jaekl/cfb/db/driver/DbDriver.java @@ -2,13 +2,12 @@ package net.jaekl.cfb.db.driver; // Copyright (C) 2015 Christian Jaekl -import static net.jaekl.cfb.db.Column.Null.*; +import static net.jaekl.cfb.db.Column.Null.NOT_NULL; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; -import java.sql.Timestamp; import java.util.ArrayList; import java.util.Date; import java.util.List; @@ -18,6 +17,7 @@ import net.jaekl.cfb.db.Column.Type; import net.jaekl.cfb.db.Condition; import net.jaekl.cfb.db.Row; import net.jaekl.cfb.db.Sequence; +import net.jaekl.cfb.db.Sort; import net.jaekl.cfb.db.Table; public abstract class DbDriver { @@ -83,7 +83,16 @@ public abstract class DbDriver { public List select(Connection con, Column[] columns, Table[] tables, Condition[] conditions) throws SQLException { - String sql = selectSql(columns, tables, conditions); + Sort[] sorts = new Sort[0]; + int limit = (-1); // no limit + + return select(con, columns, tables, conditions, sorts, limit); + } + + public List select(Connection con, Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit) + throws SQLException + { + String sql = selectSql(columns, tables, conditions, sorts, limit); ArrayList result = new ArrayList(); try (PreparedStatement ps = con.prepareStatement(sql)) { @@ -99,7 +108,13 @@ public abstract class DbDriver { while (rs.next()) { Object[] values = new Object[columns.length]; for (index = 0; index < columns.length; ++index) { - values[index] = rs.getObject(index + 1); + if (columns[index].getType().equals(Type.TIMESTAMPTZ)) { + long milliseconds = rs.getLong(index + 1); + values[index] = new java.util.Date(milliseconds); + } + else { + values[index] = rs.getObject(index + 1); + } } Row row = new Row(columns, values); result.add(row); @@ -128,10 +143,13 @@ public abstract class DbDriver { for (int col = 0; col < data.length; ++col) { Object obj = data[col]; - if (obj instanceof java.util.Date) { + Column column = table.getColumn(col); + if (column.getType().equals(Type.TIMESTAMPTZ)) { + // Special case: because there's no good way to read a TIMESTAMPTZ from + // the database using JDBC, we store it as an integer (milliseconds since + // the epoch, 01.01.1970 00:00:00.000 UTC). Date date = (Date)obj; - Timestamp ts = new Timestamp(date.getTime()); - ps.setTimestamp(col + 1, ts); + ps.setLong(col + 1, date.getTime()); } else { ps.setObject(col + 1, data[col]); @@ -202,7 +220,7 @@ public abstract class DbDriver { return sb.toString(); } - protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions) + protected String selectSql(Column[] columns, Table[] tables, Condition[] conditions, Sort[] sorts, int limit) { StringBuilder sb = new StringBuilder("SELECT "); @@ -247,11 +265,47 @@ public abstract class DbDriver { .append(condition.getOperation().getSql()); } } + + if (null != sorts && sorts.length > 0) { + sb.append(" ORDER BY "); + + boolean firstSort = true; + + for (Sort sort : sorts) { + if (firstSort) { + firstSort = false; + } + else { + sb.append(", "); + } + + sb.append(sort.getColumn().getName()); + + if (sort.getDirection().equals(Sort.Direction.ASCENDING)) { + sb.append(" ASCENDING "); + } + else { + sb.append(" DESCENDING "); + } + } + } + + if (limit > 0) { + sb.append(" LIMIT " + limit + " "); + } return sb.toString(); } protected String typeName(Type type) { + // Special case: TIMESTAMPTZ stored as INTEGER (milliseconds since the epoch) + // Reading a TIMESTAMPTZ back from the DB, and converting it to a java.util.Date, + // is fraught with peril. The best way around this is to store the dates in + // milliseconds-since-the-epoch (01.01.1970 00:00:00.000 UTC). + if (Type.TIMESTAMPTZ.equals(type)) { + return Type.INTEGER.toString(); + } + return type.toString(); } diff --git a/prod/net/jaekl/cfb/store/DbStore.java b/prod/net/jaekl/cfb/store/DbStore.java index 315ade5..37a3898 100644 --- a/prod/net/jaekl/cfb/store/DbStore.java +++ b/prod/net/jaekl/cfb/store/DbStore.java @@ -11,10 +11,12 @@ import net.jaekl.cfb.db.Column; import net.jaekl.cfb.db.Condition; import net.jaekl.cfb.db.Operation; import net.jaekl.cfb.db.Row; +import net.jaekl.cfb.db.Sort; import net.jaekl.cfb.db.Table; import net.jaekl.cfb.db.TypeMismatchException; import net.jaekl.cfb.db.driver.DbDriver; import net.jaekl.cfb.xml.BugClass; +import net.jaekl.cfb.xml.BugCollection; import net.jaekl.cfb.xml.BugInstance; import net.jaekl.cfb.xml.BugMethod; import net.jaekl.cfb.xml.LocalVariable; @@ -31,6 +33,18 @@ public class DbStore { m_driver = driver; m_msgColl = msgColl; } + + public Analysis getPrior(Analysis analysis) throws SQLException, TypeMismatchException { + if (null == analysis) { + return null; + } + Long priorId = getPriorId(analysis); + if (null == priorId) { + return null; + } + + return getAnalysis(priorId); + } public boolean put(Analysis analysis) throws SQLException, TypeMismatchException { if (null == analysis) { @@ -261,4 +275,51 @@ public class DbStore { return Long.valueOf(varId); } + + Long getPriorId(Analysis analysis) throws SQLException, TypeMismatchException + { + Column[] columns = { CfbSchema.RUNID }; + Table[] tables = { CfbSchema.RUNS }; + Condition[] conditions = { new Condition( CfbSchema.STARTTIME, analysis.getStart(), Operation.LESS_THAN ) }; + Sort[] sorts = { new Sort( CfbSchema.STARTTIME, Sort.Direction.DESCENDING ) }; + int limit = 1; + + List rows = m_driver.select(m_con, columns, tables, conditions, sorts, limit); + if (rows.size() < 1) { + return null; + } + return rows.get(0).getLong(0); + } + + Analysis getAnalysis(Long priorId) throws SQLException, TypeMismatchException + { + Column[] columns = { CfbSchema.VERSION, CfbSchema.STARTTIME, CfbSchema.ENDTIME }; + Table[] tables = { CfbSchema.RUNS }; + Condition[] conditions = { new Condition( CfbSchema.RUNID, priorId, Operation.EQUAL ) }; + + List rows = m_driver.select(m_con, columns, tables, conditions); + if (rows.size() < 1) { + return null; + } + + Row row = rows.get(0); + + String version = row.getString(0); + java.util.Date start= row.getDate(1); + java.util.Date end = row.getDate(2); + + Analysis prior = new Analysis(version); + prior.setId(priorId.longValue()); + prior.setStart(start); + prior.setEnd(end); + + prior.setBugCollection(getBugCollection(priorId)); + + return prior; + } + + BugCollection getBugCollection(Long priorId) throws SQLException, TypeMismatchException + { + throw new UnsupportedOperationException("Not yet implemented"); + } } -- 2.30.2