Hi, all, while hacking on libfossil this evening i stumbled across a file i had stashed away and subsequently forgotten:
http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/artifact/2bb2f4898b882cbb6a97ecc3e97ad356132dd6ad the SQL in that file was taking from an off-list exchange where list member Andreas Kupries had asked how to get certain info out of a fossil db. While his query (as it were) was over my head, Richard provided the above notes, and i found them useful enough to stash away in libfossil's source repo for later reference ("later" being this evening, apparently). While going over it, and opening my conventional SQL scratch file to play with some of it, i came across the following also-forgotten query i put together, and thought it might be useful for some of you in reporting or automation contexts... In short, provide it a list of branch names and it returns the "tip" (most recent) version in each of those branches: WITH branch(name) AS ( SELECT 'dave' UNION ALL SELECT 'trunk' ), latestByBranch(name, rid, uuid, ts) AS( SELECT branch.name, event.objid, blob.uuid, datetime(max(event.mtime)) FROM tag, tagxref, event, branch, blob WHERE tag.tagname='sym-' || branch.name AND tagxref.tagid=tag.tagid AND tagxref.tagtype>0 AND event.objid=tagxref.rid AND blob.rid=event.objid AND event.type GLOB 'ci' GROUP BY branch.name ) SELECT * FROM latestByBranch ORDER BY ts DESC ; Achtung: the max() call seems to return the max() of the corresponding row (i.e. the one we really want), but to be honest i'm not sure if that's just coincidence or if that's well-defined sqlite behaviour wrt aggregate functions (from what i understand, the behaviour is not well defined in other SQL engines). Comments from those in the know are welcomed. Example: [stephan@host:~/cvs/fossil/libfossil]$ f sqlite < zz.sql trunk|6660|220da67a06ee577d4667718b2ffe2f94c48ca338|2014-11-03 19:05:38 dave|6609|a7902080460a15640b0fc40a46c4be272942bc4b|2014-10-29 04:42:27 or using libfossil: [stephan@host:~/cvs/fossil/libfossil]$ f-query -e zz.sql name rid uuid ts trunk 6660 220da67a06ee577d4667718b2ffe2f94c48ca338 2014-11-03 19:05:38 dave 6609 a7902080460a15640b0fc40a46c4be272942bc4b 2014-10-29 04:42:27 To the SQL experts in the crowd: i am most certainly _not_ an SQL expert. Feedback on improving this query is welcomed! -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________ fossil-users mailing list fossil-users@lists.fossil-scm.org http://lists.fossil-scm.org:8080/cgi-bin/mailman/listinfo/fossil-users
