SourceForge Omphaloskepsis

Well, I’ve been intending to get some kind of nerd blog going again, and what better way than to complain about SourceForge moving to yet another issue tracking / source control solution.
Don’t get me wrong. I think SourceForge is great, open source software in general is great, and that people are willing to expend resources on this sort of thing is probably a good thing all in all.
This is not a problem that needed solving, and even if it did, creating a new web application was not the way of doing it. The whole writing-another-wiki-to-replace-hosted-mediawiki is particularly annoying.
I vaguely remember the last time this happened just before every project’s webpage starting to look like this.
Judging by SourceForge’s newfound fascination with Markdown as a formatting syntax, I think it’s got something to do with the competing site github embedding readme.md
files on their project pages. Because people who write open source software really can’t handle HTML.
If SF are running out of work to do, other than the kind of wheel reinvention that appears to warrant Apache incubator status, they could try bringing pageload times down from 10 seconds to something a bit more 21st century . I might even make a graph.

But that aside.
According to this this bug , all your stats are disappearing when you upgrade your project to the new, barely feature-equivalent platform.
So I wrote a Java program to store those one-hits-per-month into a MySQL database somewhere, because that’s the kind of person that I am.
You will probably want to change the constants defined at the top of the test class, or convert them to command-line arguments or IoC them or something.
JDBC_CONNECTION_STRING
– the JDBC connection string to the MySQL database ( currently "jdbc:mysql://filament/stats?zeroDateTimeBehavior=convertToNull&autoReconnect=true" )JDBC_USERNAME
– MySQL usernameJDBC_PASSWORD
– MySQL databasePROJECT_NAMES
– unix names of your Sourceforge projectsDOWNLOAD_STATS
– the subset of projects to retrieve download statisticsCVS_STATS
– the subset of projects to retrieve CVS statisticsSVN_STATS
– the subset of projects to retrieve SVN statistics
Here’s the relationship entity diagram… the SQL to create this table is in the code underneath.

The projectIds will line up with each of your sourceforge projects, and the different types of statistics (cvs or svn upload and download) each have their own statType. Since this is a daily summary, the combination of projectId, date and statType should be unique. If you were really keen you could set up foreign keys into as-yet-unimplemented project and statType tables, but hopefully you get the idea.
And here’s the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 | package com.randomnoun.common; /* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html) */ import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.commons.httpclient.HttpClient; import org.apache.commons.httpclient.methods.GetMethod; import org.apache.log4j.Logger; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import org.json.JSONTokener; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.jdbc.core.JdbcTemplate; /** Retrieve sourceforge statistics and store them in a database. * * <p>The following MySQL creation DDL should be run beforehand * (assuming a schema named `stats` has already been created) * * <pre> * CREATE TABLE `stats`.`daily` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `projectId` INTEGER UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL, `statType` INTEGER UNSIGNED NOT NULL, `value` INTEGER NOT NULL, PRIMARY KEY (`id`), INDEX `IDX_DAILY_PROJECT`(`projectId`), INDEX `IDX_DAILY_DATE`(`date`), UNIQUE INDEX `IDX_DAILY_PROJECT_DATE_TYPE`(`projectId`, `date`, `statType`); ) ENGINE = InnoDB; * < /pre> * * <h2>Limitations</h2> * * <p>Does not store operating system or geo (country) statistics for file download stats * <p>Does not store per-file stats (only per-project) * <p>{@link #getFileDownloadStats(String, String, String)} and * {@link #getScmStats(String, ScmType, String, String)} methods currently assume each * result set returned by sourceforge provides one statistic per day (i.e. start and * end dates do not span more than one calendar month) * <p>The last CVS stat in the current month may not have the normal 00:00:00 time, * associated with it, which may cause primary key violations on subsequent updates. * This might be resolved by running the SQL * <code>DELETE FROM `stats`.`daily` WHERE date_format(`date`, '%H:%i') <> '00:00'</code> * * @author knoxg * @blog http://www.randomnoun.com/wp/2012/09/23/sourceforge-omphaloskepsis/ * @version $Id: SourceforgeStats.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $ * */ public class SourceforgeStats { /** Logger instance for this class */ Logger logger = Logger.getLogger(SourceforgeStats.class); /** A revision marker to be used in exception stack traces. */ public static final String _revision = "$Id: SourceforgeStats.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $"; /** Types of source control management that SourceForge supports */ public enum ScmType { CVS, SVN, THE_OTHER_ONES }; /** Types of statistics that we will transfer. */ public enum StatType { FILE_DOWNLOAD(100), CVS_ANON_READ(200), CVS_DEV_READ(201), CVS_WRITE(202), SVN_READ(300), SVN_WRITE(301), SVN_WRITE_FILE(302); long databaseValue; private StatType(long databaseValue) { this.databaseValue = databaseValue; } private long toDatabaseValue() { return databaseValue; } } /** A class to store an individual statistic value */ public static class Stat { StatType statType; Date startDateRange, endDateRange; long value; public Stat(StatType statType, Date startDateRange, Date endDateRange, long value) { this.statType = statType; this.startDateRange = startDateRange; this.endDateRange = endDateRange; this.value = value; } } /** Request file download statistics from SourceForge * * @param project project unix name * @param start start date, in yyyy-MM-dd format * @param end end date, in yyyy-MM-dd format * @return * @throws IOException */ public List<Stat> getFileDownloadStats(String project, String start, String end) throws IOException { List<Stat> stats = new ArrayList<Stat>(); HttpClient client = new HttpClient(); // individual files have their own stats, I'm just grabbing whole-of-project stats String url = "http://sourceforge.net/projects/" + project + "/files/stats/json?start_date=" + start + "&end_date=" + end; logger.debug("Retrieving url '" + url + "'"); GetMethod gm = new GetMethod(url); client.executeMethod(gm); InputStream is = gm.getResponseBodyAsStream(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // it's in UTC, but let's ignore that JSONTokener tokener = new JSONTokener(new InputStreamReader(is)); try { JSONObject resultJs = new JSONObject(tokener); logger.debug(resultJs.toString()); JSONArray downloadsJs = resultJs.getJSONArray("downloads"); for (int i=0; i<downloadsJs.length(); i++) { JSONArray downloadJs = (JSONArray) downloadsJs.get(i); Date date = sdf.parse(downloadJs.getString(0)); long value = downloadJs.getLong(1); Stat stat = new Stat(StatType.FILE_DOWNLOAD, date, date, value); stats.add(stat); } } catch (JSONException e) { throw new IOException("Error parsing JSON", e); } catch (ParseException e) { throw new IOException("Error parsing date", e); } logger.debug("returning " + stats.size() + " stats"); return stats; } /** Perform multiple file download statistic requests from SourceForge using * a given calendar range. One request will be made per month. * * @param project project unix name * @param startCal start date * @param endCal end date * @return * @throws IOException */ public List<Stat> getFileDownloadStats(String project, Calendar startCal, Calendar endCal) throws IOException { List<Stat> stats = new ArrayList<Stat>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar startMonth = (Calendar) startCal.clone(); while (!startMonth.after(endCal)) { Calendar endMonth = (Calendar) startMonth.clone(); endMonth.add(Calendar.MONTH, 1); endMonth.add(Calendar.DAY_OF_YEAR, -1); stats.addAll(getFileDownloadStats(project, sdf.format(startMonth.getTime()), sdf.format(endMonth.getTime()) )); startMonth.add(Calendar.MONTH, 1); } return stats; } /** Request source control management statistics from SourceForge * * @param project project unix name * @param scmType the type of source control system in use for this project * @param start start date, in yyyy-MM-dd format * @param end end date, in yyyy-MM-dd format * @return * @throws IOException */ public List<Stat> getScmStats(String project, ScmType scmType, String start, String end) throws IOException { List<Stat> stats = new ArrayList<Stat>(); HttpClient client = new HttpClient(); String scmParam; String[] jsonKeys; StatType[] statTypes; if (scmType==ScmType.CVS) { scmParam="CVSRepository"; jsonKeys=new String[] { "write", "anon_read", "dev_read" }; statTypes=new StatType[] { StatType.CVS_WRITE, StatType.CVS_ANON_READ, StatType.CVS_DEV_READ }; } else if (scmType==ScmType.SVN){ scmParam="SVNRepository"; jsonKeys=new String[] { "write_txn", "read_txn", "write_files" }; statTypes=new StatType[] { StatType.SVN_WRITE, StatType.SVN_READ, StatType.SVN_WRITE_FILE }; } else { throw new IllegalArgumentException("Invalid scmType '" + scmType + "'"); } // e.g. https://sourceforge.net/projects/jvix/stats/scm?repo=CVSRepository&dates=2012-01-01+to+2012-01-31 // which performs an XHR request for // https://sourceforge.net/projects/jvix/stats/scm_data?repo=CVSRepository&begin=2012-01-01&end=2012-01-31 String url = "http://sourceforge.net/projects/" + project + "/stats/scm_data?repo=" + scmParam + "&begin=" + start + "&end=" + end; logger.debug("Retrieving url '" + url + "'"); GetMethod gm = new GetMethod(url); client.executeMethod(gm); String jsonText = gm.getResponseBodyAsString(); //InputStream is = gm.getResponseBodyAsStream(); JSONTokener tokener = new JSONTokener(jsonText); try { JSONObject resultJs = new JSONObject(tokener); logger.debug(resultJs.toString()); JSONObject dataJs = resultJs.getJSONObject("data"); for (int k=0; k<jsonKeys.length; k++) { String jsonKey = jsonKeys[k]; StatType statType = statTypes[k]; JSONArray writeJs = dataJs.getJSONArray(jsonKey); for (int i=0; i<writeJs.length(); i++) { JSONArray recordJs = (JSONArray) writeJs.get(i); Date date = new Date((long) recordJs.getDouble(0)); //logger.debug("date " + date); // NB: date should be displayed in UTC long value = recordJs.getLong(1); Stat stat = new Stat(statType, date, date, value); stats.add(stat); } } } catch (JSONException e) { throw new IOException("Error parsing JSON in '" + jsonText + "'", e); } catch (NumberFormatException e) { throw new IOException("Error parsing date", e); } logger.debug("returning " + stats.size() + " stats"); return stats; } /** Perform multiple source control management statistic requests from SourceForge * using a given calendar range. One request will be made per month. * * @param project project unix name * @param scmType the type of source control system in use for this project * @param startCal start date * @param endCal end date * @return * @throws IOException */ public List<Stat> getScmStats(String project, ScmType scmType, Calendar startCal, Calendar endCal) throws IOException { List<Stat> stats = new ArrayList<Stat>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar startMonth = (Calendar) startCal.clone(); while (!startMonth.after(endCal)) { Calendar endMonth = (Calendar) startMonth.clone(); endMonth.add(Calendar.MONTH, 1); endMonth.add(Calendar.DAY_OF_YEAR, -1); stats.addAll(getScmStats(project, scmType, sdf.format(startMonth.getTime()), sdf.format(endMonth.getTime()) )); startMonth.add(Calendar.MONTH, 1); } return stats; } /** Insert the supplied statistics into the database, or update it if * it already exists. * * @param jt JdbcTemplate containing connection to the database * @param projectId the project ID * @param stats the statistics */ public void updateDatabase(JdbcTemplate jt, long projectId, List<Stat> stats) { logger.debug("Storing " + stats.size() + " records in database..."); for (int i=0; i<stats.size(); i++) { Stat stat = stats.get(i); try { jt.update("INSERT INTO daily(projectId, date, statType, value) VALUES (?, ?, ?, ?)", new Object[] { projectId, new java.sql.Date(stat.startDateRange.getTime()), stat.statType.toDatabaseValue(), stat.value }); } catch (DataIntegrityViolationException dive) { // if this statistic exists, update it instead try { jt.update("UPDATE daily SET value = ? WHERE projectId = ? AND date = ? AND statType = ?", new Object[] { stat.value, projectId, new java.sql.Date(stat.startDateRange.getTime()), stat.statType.toDatabaseValue() }); } catch (DataIntegrityViolationException dive2) { logger.error("DataIntegrityViolationException on UPDATE", dive2); } } } } } |
package com.randomnoun.common; /* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html) */ import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.sql.SQLException; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import org.apache.commons.httpclient.HttpClient; import org.apache.commons.httpclient.methods.GetMethod; import org.apache.log4j.Logger; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import org.json.JSONTokener; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.jdbc.core.JdbcTemplate; /** Retrieve sourceforge statistics and store them in a database. * * <p>The following MySQL creation DDL should be run beforehand * (assuming a schema named `stats` has already been created) * * <pre> * CREATE TABLE `stats`.`daily` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `projectId` INTEGER UNSIGNED NOT NULL, `date` TIMESTAMP NOT NULL, `statType` INTEGER UNSIGNED NOT NULL, `value` INTEGER NOT NULL, PRIMARY KEY (`id`), INDEX `IDX_DAILY_PROJECT`(`projectId`), INDEX `IDX_DAILY_DATE`(`date`), UNIQUE INDEX `IDX_DAILY_PROJECT_DATE_TYPE`(`projectId`, `date`, `statType`); ) ENGINE = InnoDB; * < /pre> * * <h2>Limitations</h2> * * <p>Does not store operating system or geo (country) statistics for file download stats * <p>Does not store per-file stats (only per-project) * <p>{@link #getFileDownloadStats(String, String, String)} and * {@link #getScmStats(String, ScmType, String, String)} methods currently assume each * result set returned by sourceforge provides one statistic per day (i.e. start and * end dates do not span more than one calendar month) * <p>The last CVS stat in the current month may not have the normal 00:00:00 time, * associated with it, which may cause primary key violations on subsequent updates. * This might be resolved by running the SQL * <code>DELETE FROM `stats`.`daily` WHERE date_format(`date`, '%H:%i') <> '00:00'</code> * * @author knoxg * @blog http://www.randomnoun.com/wp/2012/09/23/sourceforge-omphaloskepsis/ * @version $Id: SourceforgeStats.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $ * */ public class SourceforgeStats { /** Logger instance for this class */ Logger logger = Logger.getLogger(SourceforgeStats.class); /** A revision marker to be used in exception stack traces. */ public static final String _revision = "$Id: SourceforgeStats.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $"; /** Types of source control management that SourceForge supports */ public enum ScmType { CVS, SVN, THE_OTHER_ONES }; /** Types of statistics that we will transfer. */ public enum StatType { FILE_DOWNLOAD(100), CVS_ANON_READ(200), CVS_DEV_READ(201), CVS_WRITE(202), SVN_READ(300), SVN_WRITE(301), SVN_WRITE_FILE(302); long databaseValue; private StatType(long databaseValue) { this.databaseValue = databaseValue; } private long toDatabaseValue() { return databaseValue; } } /** A class to store an individual statistic value */ public static class Stat { StatType statType; Date startDateRange, endDateRange; long value; public Stat(StatType statType, Date startDateRange, Date endDateRange, long value) { this.statType = statType; this.startDateRange = startDateRange; this.endDateRange = endDateRange; this.value = value; } } /** Request file download statistics from SourceForge * * @param project project unix name * @param start start date, in yyyy-MM-dd format * @param end end date, in yyyy-MM-dd format * @return * @throws IOException */ public List<Stat> getFileDownloadStats(String project, String start, String end) throws IOException { List<Stat> stats = new ArrayList<Stat>(); HttpClient client = new HttpClient(); // individual files have their own stats, I'm just grabbing whole-of-project stats String url = "http://sourceforge.net/projects/" + project + "/files/stats/json?start_date=" + start + "&end_date=" + end; logger.debug("Retrieving url '" + url + "'"); GetMethod gm = new GetMethod(url); client.executeMethod(gm); InputStream is = gm.getResponseBodyAsStream(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // it's in UTC, but let's ignore that JSONTokener tokener = new JSONTokener(new InputStreamReader(is)); try { JSONObject resultJs = new JSONObject(tokener); logger.debug(resultJs.toString()); JSONArray downloadsJs = resultJs.getJSONArray("downloads"); for (int i=0; i<downloadsJs.length(); i++) { JSONArray downloadJs = (JSONArray) downloadsJs.get(i); Date date = sdf.parse(downloadJs.getString(0)); long value = downloadJs.getLong(1); Stat stat = new Stat(StatType.FILE_DOWNLOAD, date, date, value); stats.add(stat); } } catch (JSONException e) { throw new IOException("Error parsing JSON", e); } catch (ParseException e) { throw new IOException("Error parsing date", e); } logger.debug("returning " + stats.size() + " stats"); return stats; } /** Perform multiple file download statistic requests from SourceForge using * a given calendar range. One request will be made per month. * * @param project project unix name * @param startCal start date * @param endCal end date * @return * @throws IOException */ public List<Stat> getFileDownloadStats(String project, Calendar startCal, Calendar endCal) throws IOException { List<Stat> stats = new ArrayList<Stat>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar startMonth = (Calendar) startCal.clone(); while (!startMonth.after(endCal)) { Calendar endMonth = (Calendar) startMonth.clone(); endMonth.add(Calendar.MONTH, 1); endMonth.add(Calendar.DAY_OF_YEAR, -1); stats.addAll(getFileDownloadStats(project, sdf.format(startMonth.getTime()), sdf.format(endMonth.getTime()) )); startMonth.add(Calendar.MONTH, 1); } return stats; } /** Request source control management statistics from SourceForge * * @param project project unix name * @param scmType the type of source control system in use for this project * @param start start date, in yyyy-MM-dd format * @param end end date, in yyyy-MM-dd format * @return * @throws IOException */ public List<Stat> getScmStats(String project, ScmType scmType, String start, String end) throws IOException { List<Stat> stats = new ArrayList<Stat>(); HttpClient client = new HttpClient(); String scmParam; String[] jsonKeys; StatType[] statTypes; if (scmType==ScmType.CVS) { scmParam="CVSRepository"; jsonKeys=new String[] { "write", "anon_read", "dev_read" }; statTypes=new StatType[] { StatType.CVS_WRITE, StatType.CVS_ANON_READ, StatType.CVS_DEV_READ }; } else if (scmType==ScmType.SVN){ scmParam="SVNRepository"; jsonKeys=new String[] { "write_txn", "read_txn", "write_files" }; statTypes=new StatType[] { StatType.SVN_WRITE, StatType.SVN_READ, StatType.SVN_WRITE_FILE }; } else { throw new IllegalArgumentException("Invalid scmType '" + scmType + "'"); } // e.g. https://sourceforge.net/projects/jvix/stats/scm?repo=CVSRepository&dates=2012-01-01+to+2012-01-31 // which performs an XHR request for // https://sourceforge.net/projects/jvix/stats/scm_data?repo=CVSRepository&begin=2012-01-01&end=2012-01-31 String url = "http://sourceforge.net/projects/" + project + "/stats/scm_data?repo=" + scmParam + "&begin=" + start + "&end=" + end; logger.debug("Retrieving url '" + url + "'"); GetMethod gm = new GetMethod(url); client.executeMethod(gm); String jsonText = gm.getResponseBodyAsString(); //InputStream is = gm.getResponseBodyAsStream(); JSONTokener tokener = new JSONTokener(jsonText); try { JSONObject resultJs = new JSONObject(tokener); logger.debug(resultJs.toString()); JSONObject dataJs = resultJs.getJSONObject("data"); for (int k=0; k<jsonKeys.length; k++) { String jsonKey = jsonKeys[k]; StatType statType = statTypes[k]; JSONArray writeJs = dataJs.getJSONArray(jsonKey); for (int i=0; i<writeJs.length(); i++) { JSONArray recordJs = (JSONArray) writeJs.get(i); Date date = new Date((long) recordJs.getDouble(0)); //logger.debug("date " + date); // NB: date should be displayed in UTC long value = recordJs.getLong(1); Stat stat = new Stat(statType, date, date, value); stats.add(stat); } } } catch (JSONException e) { throw new IOException("Error parsing JSON in '" + jsonText + "'", e); } catch (NumberFormatException e) { throw new IOException("Error parsing date", e); } logger.debug("returning " + stats.size() + " stats"); return stats; } /** Perform multiple source control management statistic requests from SourceForge * using a given calendar range. One request will be made per month. * * @param project project unix name * @param scmType the type of source control system in use for this project * @param startCal start date * @param endCal end date * @return * @throws IOException */ public List<Stat> getScmStats(String project, ScmType scmType, Calendar startCal, Calendar endCal) throws IOException { List<Stat> stats = new ArrayList<Stat>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Calendar startMonth = (Calendar) startCal.clone(); while (!startMonth.after(endCal)) { Calendar endMonth = (Calendar) startMonth.clone(); endMonth.add(Calendar.MONTH, 1); endMonth.add(Calendar.DAY_OF_YEAR, -1); stats.addAll(getScmStats(project, scmType, sdf.format(startMonth.getTime()), sdf.format(endMonth.getTime()) )); startMonth.add(Calendar.MONTH, 1); } return stats; } /** Insert the supplied statistics into the database, or update it if * it already exists. * * @param jt JdbcTemplate containing connection to the database * @param projectId the project ID * @param stats the statistics */ public void updateDatabase(JdbcTemplate jt, long projectId, List<Stat> stats) { logger.debug("Storing " + stats.size() + " records in database..."); for (int i=0; i<stats.size(); i++) { Stat stat = stats.get(i); try { jt.update("INSERT INTO daily(projectId, date, statType, value) VALUES (?, ?, ?, ?)", new Object[] { projectId, new java.sql.Date(stat.startDateRange.getTime()), stat.statType.toDatabaseValue(), stat.value }); } catch (DataIntegrityViolationException dive) { // if this statistic exists, update it instead try { jt.update("UPDATE daily SET value = ? WHERE projectId = ? AND date = ? AND statType = ?", new Object[] { stat.value, projectId, new java.sql.Date(stat.startDateRange.getTime()), stat.statType.toDatabaseValue() }); } catch (DataIntegrityViolationException dive2) { logger.error("DataIntegrityViolationException on UPDATE", dive2); } } } } }
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | package com.randomnoun.common; /* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html) */ import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Arrays; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.List; import java.util.Properties; import javax.sql.DataSource; import junit.framework.TestCase; import junit.framework.TestSuite; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.SingleConnectionDataSource; import com.randomnoun.common.SourceforgeStats.ScmType; import com.randomnoun.common.SourceforgeStats.Stat; import com.randomnoun.common.log4j.Log4jCliConfiguration; /** Test the SourceforgeStats retrieval class. * * <p>File download statistics are retrieved from Jan 2011 until Sep 2012 * * <P>Source control statistics are retrieved from Jan 2012 until Sep 2012 * * @author knoxg * @blog http://www.randomnoun.com/wp/2012/09/23/sourceforge-omphaloskepsis/ * @version $Id: SourceforgeStatsTest.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $ * */ public class SourceforgeStatsTest extends TestCase { /** Enable this to actually run the tests * (disabled since we don't want this to run during normal maven builds) */ private static boolean ENABLED = false; // ***** update these constants for your database / project settings private static String JDBC_CONNECTION_STRING = "jdbc:mysql://filament/stats?zeroDateTimeBehavior=convertToNull&autoReconnect=true"; private static String JDBC_USERNAME = "stats"; private static String JDBC_PASSWORD = "stats"; // the indexes into this array correspond to `daily`.`projectId` values in MySQL private static String PROJECT_NAMES[] = { "p7spy", "jvix", "packetmap", "timetube" }; // arrays containing which projects have each type of statistic private static String DOWNLOAD_STATS[] = { "p7spy", "jvix", "packetmap", "timetube" }; private static String CVS_STATS[] = { "jvix" }; private static String SVN_STATS[] = { "packetmap", "timetube" }; // ***** // test fixture instance variables Calendar start2011, start2012, until2012; List<String> projectList; JdbcTemplate jt; public void setUp() { if (!ENABLED) { return; } Log4jCliConfiguration lcc = new Log4jCliConfiguration(); Properties props = new Properties(); props.put("log4j.rootCategory", "INFO, CONSOLE"); // default to INFO threshold, send to CONSOLE logger props.put("log4j.logger.com.randomnoun.common.SourceforgeStats", "DEBUG"); // DEBUG threshold for this class lcc.init("[SourceforgeStats]", props); start2011 = new GregorianCalendar(); start2011.clear(); start2012 = new GregorianCalendar(); start2012.clear(); until2012 = new GregorianCalendar(); until2012.clear(); start2011.set(2011, 0, 1, 0, 0, 0); // Jan 2011 (0-based month) start2012.set(2012, 0, 1, 0, 0, 0); // Jan 2012 until2012.set(2012, 8, 1, 0, 0, 0); // Sep 2012 projectList = Arrays.asList(PROJECT_NAMES); try { Connection conn = DriverManager.getConnection( JDBC_CONNECTION_STRING, JDBC_USERNAME, JDBC_PASSWORD); DataSource ds = new SingleConnectionDataSource(conn, true); jt = new JdbcTemplate(ds); } catch (SQLException sqle) { throw new RuntimeException(sqle); } } public void testGetFileDownloadStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : DOWNLOAD_STATS) { List<Stat> stats = sfs.getFileDownloadStats(projectName, start2011, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public void testGetCvsStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : CVS_STATS) { List<Stat> stats = sfs.getScmStats(projectName, ScmType.CVS, start2012, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public void testGetSvnStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : SVN_STATS) { List<Stat> stats = sfs.getScmStats(projectName, ScmType.SVN, start2012, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public static void main(String[] args) { junit.textui.TestRunner.run(new TestSuite(SourceforgeStatsTest.class)); } } |
package com.randomnoun.common; /* (c) 2013 randomnoun. All Rights Reserved. This work is licensed under a * BSD Simplified License. (http://www.randomnoun.com/bsd-simplified.html) */ import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.Arrays; import java.util.Calendar; import java.util.GregorianCalendar; import java.util.List; import java.util.Properties; import javax.sql.DataSource; import junit.framework.TestCase; import junit.framework.TestSuite; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.datasource.SingleConnectionDataSource; import com.randomnoun.common.SourceforgeStats.ScmType; import com.randomnoun.common.SourceforgeStats.Stat; import com.randomnoun.common.log4j.Log4jCliConfiguration; /** Test the SourceforgeStats retrieval class. * * <p>File download statistics are retrieved from Jan 2011 until Sep 2012 * * <P>Source control statistics are retrieved from Jan 2012 until Sep 2012 * * @author knoxg * @blog http://www.randomnoun.com/wp/2012/09/23/sourceforge-omphaloskepsis/ * @version $Id: SourceforgeStatsTest.java,v 1.3 2013-09-24 02:37:09 knoxg Exp $ * */ public class SourceforgeStatsTest extends TestCase { /** Enable this to actually run the tests * (disabled since we don't want this to run during normal maven builds) */ private static boolean ENABLED = false; // ***** update these constants for your database / project settings private static String JDBC_CONNECTION_STRING = "jdbc:mysql://filament/stats?zeroDateTimeBehavior=convertToNull&autoReconnect=true"; private static String JDBC_USERNAME = "stats"; private static String JDBC_PASSWORD = "stats"; // the indexes into this array correspond to `daily`.`projectId` values in MySQL private static String PROJECT_NAMES[] = { "p7spy", "jvix", "packetmap", "timetube" }; // arrays containing which projects have each type of statistic private static String DOWNLOAD_STATS[] = { "p7spy", "jvix", "packetmap", "timetube" }; private static String CVS_STATS[] = { "jvix" }; private static String SVN_STATS[] = { "packetmap", "timetube" }; // ***** // test fixture instance variables Calendar start2011, start2012, until2012; List<String> projectList; JdbcTemplate jt; public void setUp() { if (!ENABLED) { return; } Log4jCliConfiguration lcc = new Log4jCliConfiguration(); Properties props = new Properties(); props.put("log4j.rootCategory", "INFO, CONSOLE"); // default to INFO threshold, send to CONSOLE logger props.put("log4j.logger.com.randomnoun.common.SourceforgeStats", "DEBUG"); // DEBUG threshold for this class lcc.init("[SourceforgeStats]", props); start2011 = new GregorianCalendar(); start2011.clear(); start2012 = new GregorianCalendar(); start2012.clear(); until2012 = new GregorianCalendar(); until2012.clear(); start2011.set(2011, 0, 1, 0, 0, 0); // Jan 2011 (0-based month) start2012.set(2012, 0, 1, 0, 0, 0); // Jan 2012 until2012.set(2012, 8, 1, 0, 0, 0); // Sep 2012 projectList = Arrays.asList(PROJECT_NAMES); try { Connection conn = DriverManager.getConnection( JDBC_CONNECTION_STRING, JDBC_USERNAME, JDBC_PASSWORD); DataSource ds = new SingleConnectionDataSource(conn, true); jt = new JdbcTemplate(ds); } catch (SQLException sqle) { throw new RuntimeException(sqle); } } public void testGetFileDownloadStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : DOWNLOAD_STATS) { List<Stat> stats = sfs.getFileDownloadStats(projectName, start2011, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public void testGetCvsStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : CVS_STATS) { List<Stat> stats = sfs.getScmStats(projectName, ScmType.CVS, start2012, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public void testGetSvnStats() throws IOException { if (!ENABLED) { return; } SourceforgeStats sfs = new SourceforgeStats(); for (String projectName : SVN_STATS) { List<Stat> stats = sfs.getScmStats(projectName, ScmType.SVN, start2012, until2012); sfs.updateDatabase(jt, projectList.indexOf(projectName), stats); } } public static void main(String[] args) { junit.textui.TestRunner.run(new TestSuite(SourceforgeStatsTest.class)); } }
pom.xml fragment
You’ll need to compile against the mysql, JSON, spring framework, log4j and Apache HttpClient JARs, which if you’re using maven will require the following dependencies:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.9</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring</artifactId> <version>2.5.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.11</version> </dependency> <dependency> <groupId>commons-httpclient</groupId> <artifactId>commons-httpclient</artifactId> <version>3.0.1</version> </dependency> |
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.9</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring</artifactId> <version>2.5.6</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.11</version> </dependency> <dependency> <groupId>commons-httpclient</groupId> <artifactId>commons-httpclient</artifactId> <version>3.0.1</version> </dependency>
Output
Then when you run it you should get output similar to the following:
1 2 3 4 5 6 | [SourceforgeStats] 11:33:48,484 DEBUG com.randomnoun.common.SourceforgeStats - Retrieving url 'http://sourceforge.net/projects/p7spy/files/stats/json?start_date=2011-01-01&end_date=2011-01-31' [SourceforgeStats] 11:33:54,718 DEBUG com.randomnoun.common.SourceforgeStats - {"total":2,"end_date":"2011-01-31 00:00:00","oses_by_country":{},"summaries":{"os":{"modifier_text":"","percent":100,"top":"Other"},"time":{"downloads":2},"geo":{"modifier_text":"","percent":100,"top":"France"}},"oses":[["Unknown",2]],"stats_updated":"2012-09-18 23:59:59","oses_with_downloads":[],"period":"daily","countries":[["France",2]],"start_date":"2011-01-01 00:00:00","downloads":[["2011-01-01 00:00:00",0],["2011-01-02 00:00:00",0],["2011-01-03 00:00:00",0],["2011-01-04 00:00:00",0],["2011-01-05 00:00:00",0],["2011-01-06 00:00:00",0],["2011-01-07 00:00:00",0],["2011-01-08 00:00:00",0],["2011-01-09 00:00:00",0],["2011-01-10 00:00:00",0],["2011-01-11 00:00:00",0],["2011-01-12 00:00:00",0],["2011-01-13 00:00:00",0],["2011-01-14 00:00:00",0],["2011-01-15 00:00:00",0],["2011-01-16 00:00:00",0],["2011-01-17 00:00:00",0],["2011-01-18 00:00:00",0],["2011-01-19 00:00:00",0],["2011-01-20 00:00:00",2],["2011-01-21 00:00:00",0],["2011-01-22 00:00:00",0],["2011-01-23 00:00:00",0],["2011-01-24 00:00:00",0],["2011-01-25 00:00:00",0],["2011-01-26 00:00:00",0],["2011-01-27 00:00:00",0],["2011-01-28 00:00:00",0],["2011-01-29 00:00:00",0],["2011-01-30 00:00:00",0],["2011-01-31 00:00:00",0]],"messages":[]} ... [SourceforgeStats] 11:42:01,593 DEBUG com.randomnoun.common.SourceforgeStats - returning 90 stats [SourceforgeStats] 11:42:01,593 DEBUG com.randomnoun.common.SourceforgeStats - Storing 822 records in database... |
[SourceforgeStats] 11:33:48,484 DEBUG com.randomnoun.common.SourceforgeStats - Retrieving url 'http://sourceforge.net/projects/p7spy/files/stats/json?start_date=2011-01-01&end_date=2011-01-31' [SourceforgeStats] 11:33:54,718 DEBUG com.randomnoun.common.SourceforgeStats - {"total":2,"end_date":"2011-01-31 00:00:00","oses_by_country":{},"summaries":{"os":{"modifier_text":"","percent":100,"top":"Other"},"time":{"downloads":2},"geo":{"modifier_text":"","percent":100,"top":"France"}},"oses":[["Unknown",2]],"stats_updated":"2012-09-18 23:59:59","oses_with_downloads":[],"period":"daily","countries":[["France",2]],"start_date":"2011-01-01 00:00:00","downloads":[["2011-01-01 00:00:00",0],["2011-01-02 00:00:00",0],["2011-01-03 00:00:00",0],["2011-01-04 00:00:00",0],["2011-01-05 00:00:00",0],["2011-01-06 00:00:00",0],["2011-01-07 00:00:00",0],["2011-01-08 00:00:00",0],["2011-01-09 00:00:00",0],["2011-01-10 00:00:00",0],["2011-01-11 00:00:00",0],["2011-01-12 00:00:00",0],["2011-01-13 00:00:00",0],["2011-01-14 00:00:00",0],["2011-01-15 00:00:00",0],["2011-01-16 00:00:00",0],["2011-01-17 00:00:00",0],["2011-01-18 00:00:00",0],["2011-01-19 00:00:00",0],["2011-01-20 00:00:00",2],["2011-01-21 00:00:00",0],["2011-01-22 00:00:00",0],["2011-01-23 00:00:00",0],["2011-01-24 00:00:00",0],["2011-01-25 00:00:00",0],["2011-01-26 00:00:00",0],["2011-01-27 00:00:00",0],["2011-01-28 00:00:00",0],["2011-01-29 00:00:00",0],["2011-01-30 00:00:00",0],["2011-01-31 00:00:00",0]],"messages":[]} ... [SourceforgeStats] 11:42:01,593 DEBUG com.randomnoun.common.SourceforgeStats - returning 90 stats [SourceforgeStats] 11:42:01,593 DEBUG com.randomnoun.common.SourceforgeStats - Storing 822 records in database...
There are a couple of other com.randomnoun.common.*
classes referenced above which I will put up here in due course, but you could probably comment them out for the time being.
I am aware that you could write this in one line of perl.
This class is now part of the com.randomnoun.common:common-public artifact, which can be directly referenced in your pom.xml
from the maven central repository.
Apologies for how hideous that button looks. I’m currently trying different styles as to how to represent these sorts of things. Graphic design is not my strong suit.
Update 24/9/2013: It’s in central now.
Update 28/9/2012: Tried to sound slightly less whiny about SourceForge’s development practices.
Update 1/10/2012: Added a screenshot showing the popups that appear whilst saving a page in SourceForge’s new wiki editor.
Update 17/10/2012: Added an entity relationship diagram.
Related Posts
-
Visualising statistics on Google Earth
No Comments | Feb 19, 2013 -
Pointless
No Comments | Nov 27, 2021 -
Intruder alert
5 Comments | Jan 28, 2013 -
Webapp versions v1.0
No Comments | Sep 24, 2013
Seeing if comments work.