For a report, I wanted to know the longest interval between to
check-ins (not necessarily on the same branch) for a project. In
other words, I wanted to know the longest period of inactivity for a
project. Turns out this is relatively easy to do with Fossil and some
SQL. This message is just to record my technique.
Here's what you do:
(1) Start up the SQL shell on the repository of interest: "fossil sql".
(2) Enter the following query:
SELECT
(SELECT min(mtime) FROM event AS x
WHERE type='ci' AND x.mtime>y.mtime) - y.mtime,
datetime(y.mtime),
(SELECT substr(uuid,1,10) FROM blob WHERE blob.rid=y.objid)
FROM event AS y
WHERE y.type='ci'
ORDER BY 1 DESC LIMIT 20;
The result table shows three columns which are the length of the
check-in hiatus, the start of the hiatus, and the last check-in before
the start of the hiatus. The 20 longest gaps are shown.
If you want to find the longest period of inactivity during 2016, you
can change the query to something like this:
SELECT
(SELECT min(mtime) FROM event AS x
WHERE type='ci' AND x.mtime>y.mtime) - y.mtime,
datetime(y.mtime),
(SELECT substr(uuid,1,10) FROM blob WHERE blob.rid=y.objid)
FROM event AS y
WHERE y.type='ci'
AND y.mtime BETWEEN julianday('2016-01-01') AND julianday('2017-01-01')
ORDER BY 1 DESC LIMIT 20;
The extra term on the outer WHERE clause limits attention to 2016.
For SQLite, the first few lines of result are this:
5.20565651590005,'2016-07-16 11:47:45','613c1ceaf4'
4.05957302125171,'2016-10-27 14:51:02','6374978e8f'
3.09464070573449,'2016-08-13 14:30:23','c7a9f26d11'
3.08603967586532,'2016-12-18 17:42:00','165c044686'
3.04145763907582,'2016-12-02 19:07:03','6e144735ed'
2.91312336781994,'2016-06-17 19:27:13','998095aba0'
2.8035011109896,'2016-08-29 14:18:18','6602974d17'
On the Tcl repository, looking over the entire lifetime of the
repository, we see:
33.903420810122,'2011-01-25 22:33:56',46969
33.1645138878375,'1998-03-26 14:56:55',886
20.9625462959521,'2000-12-14 22:24:45',9088
18.4955671289936,'1999-09-02 16:26:51',6397
16.7785532400012,'1998-04-29 17:10:32',1421
16.1128009259701,'1999-12-22 23:48:56',6930
Here we see some a few long gaps back in the 20th century, but at the
top of the list is the great sourceforge outage of 2011.
I don't know if the above has any practical use for most people (or I
would make it a new URI on the Fossil webserver) but it seems like fun
and so I thought I would share.
Now remind me again, how would you do this in Git? ;-)
--
D. Richard Hipp
[email protected]
_______________________________________________
fossil-users mailing list
[email protected]
http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
