Monthly Archives: August 2015

EPrints Nth Fulltext Download

An EPrints repository administrator wanted to make a fuss over the author of the 50, information pills 000th fulltext download in their repository, meningitis and reached out.  After some time looking at the problem, women’s health I discovered that finding out the 50,000th fulltext download from an EPrints repository is not as simple as it sounds.  Ironically, if IRStats wasn’t installed, it would have been far easier.

The Simple Approach

EPrints stores every view of an abstract page or download of a fulltext document in its ‘access’ table.  A query on this will give us the 50,000th download:

SELECT referent_id FROM access WHERE service_type_id = '?fulltext=yes' LIMIT 50000,1

This will give you an answer that is in some way correct, but if you have IRStats, you can do better.

The Subtleties of IRStats

IRStats 2 starts with the access table discussed above and filters out spam and optimises the data for its visualisations, creating a new set of tables.  The most useful table that IRStats maintains is the ‘irstats2_downloads’ table.  To get the total number of downloads from this table, we need to run this query:

SELECT SUM(count) FROM irstats2_downloads

The reason for this is that this table has a granularity of a day.  Each row represents the number of downloads of a given eprint’s fulltext on any given day.  To find the 50,000th download I repeatedly ran queries like this:

SELECT SUM(count) FROM irstats2_downloads WHERE datestamp <= 20150413

…until I found the first day on which the sum of the count column was greater than 50,000.  Then, I checked the UIDs of the rows for that day, and repeated the process again using ascending UIDs until the sum was >= 50,000.

Differently Inaccurate

I wrapped both of these approaches into a script (, which produced the following output for the repository in question:

EPrint 3160 was download number 50000 on 20150711
This result was generated from the IRStats2 downloads table

EPrint 1064 was download number 50000 on 20141027
This result was generated from the EPrints access table

These are obviously very different numbers, but each is flawed in different ways.  The access table method doesn’t take spam into account (repeat downloaders, spiders, etc), but more importantly, it significantly disagrees with IRStats, which is the primary source of repository statistics.

The IRStats result is flawed in a far more subtle way.  There’s a margin for error which is related to the number of downloads on the day that the 50,000th download occurred.  The reason for this is that each download of an item is sequentially inserted into the irstats table with a count of 1, but if a second download happens for any given item, the count on the existing row is incremented. This means we have no way of knowing if it happened immediately after the first download, or whether it was the last download of the day.  The approach I’ve taken with the queries above means that I treat all downloads of the same item on the same day to have happened concurrently.

Here’s what the day in question actually looks like:

select `count`, COUNT(*) from irstats2_downloads where datestamp = '20150711' group by `count`;
| count | COUNT(*) |
|     1 |       82 |
|     2 |       17 |
|     3 |        2 |
3 rows in set (0.00 sec)

Around 19% of downloads were repeats, which will skew the result.  There’s quite a high chance we’ve chosen the wrong one (in fact, it’s quite unlikely that we have the right answer).

The final piece of data is the difference in size of the two sets of download data.  The access table has 88310 downloads, which is 58% more than the 51739 in the irstats downloads table.

So, we have one method that will guarantee the item is in the right place in the sequence of downloads, but the sequence is somewhat bloated.  We also have another method that uses a better sequence, but chooses with a random offset.

More Development Required

The way to solve this is to develop an IRStats plugin that explicitly stores Nth downloads for important values of N.  That, however, is a job for another day.

A day of fun

OR2015 was great.  I think I enjoyed myself more than I have at any other Open Repositories.  I was involved in a number of things that kept me really busy throughout the conference:

Developer Track

The conference committee had decided that a full developers track would replace the Developer Challenge of previous years, contagion with paper presentation sessions in the main conference.  The amazing and well-organised Claire Knowles co-chaired the track with me, herpes and we sent out our call with an emphasis on practical demonstrations and informal presentations.

We filled two sessions with submissions, remedy and we enjoyed a broad set of technology and process demonstrations.  Hardy Pottinger gets a special mention for really entering into the spirit of things; live source management, compilation and yo-yo demonstrations while dealing with  the inevitable hiccoughs with his live demonstration of DSpace development within Vagrant with aplomb.

Ideas Challenge

As the Developer Challenge had been replaced by the Developer Track, but that left a Developer Challenge size hole in the conference, which Claire and I thought was important to fill.  The Developer Challenge has, to my mind, been one of the crown jewels of Open Repositories.  It was where repository developers would form small teams and build prototypes of new features.  It’s provided a huge number of benefits to the community, among them:

  • A forum to talk about the current on-the-ground state and future of repository software
  • A networking opportunity for developers
  • A list of good ideas that might influence repository platform development
  • An event at which developers are the stars

So, as part of the Developer Track, we designed the Ideas Challenge, which involved producing a 4-slide powerpoint presentation outlining a piece of development that could be done.  A scoring system was designed to encourage working with new people and the participation of non-developers.  We ended up with 9 entries, and some very interesting ideas.  Full blogpost at


Vendors Table

EPrints Services had contributed as a supported of OR2015, which entitled use to a vendors table.  This worked really well as a walk-up point for EPrints, and Will and I staffed the desk during breaks.  A number of our customers and users made a point of seeking us our for a chat, and it was nice to connect with new and old faces.  We had some postcards highlighting some of our services, which are downloadable below.


EPrints Interest Group

Discussions with various community members that had begun at the EPrint Services Vendor Table continued through the EPrints Interest Group sessions.  In my “State of the Union” presentation, we talked about the future of the software and the community.  I showed off the community work I had been doing over the past six months, including the training videos.  I then asked the room what they wanted to see, and the most interesting idea that was raised was the possibility of a community steering group for EPrints.

I have, since returning to EPrints Services, been trying to find ways to promote better documentation.  My Community Development presentation was a report on work Tomasz Neugebaur and I had been doing over the past few months.  I helped Tomasz build a bazaar package by providing advice and debugging support, and he created a bazaar package with good documentation.  My presentation encouraged a fairly wide-ranging discussion of documentation, and Meg Eastwood from NAU volunteered to come up with some recommendation on how the wiki could be improved.


Best. Day. Ever!