A collection of SQL snippets and notes regarding the fossil db schema...


The following few are taken from an off-list email exchange:
========================================================================

The queries below are untested.  They are off the top of my head.
They might not work exactly right. There may well be faster ways.  But
perhaps these can get you started.

The very latest Fossil trunk is needed for the WITH RECURSIVE queries.
Or the latest SQLite trunk if you are going directly from a TCL
script.

 
(1) Find the leaf for branch X (specifically: trunk)

SELECT blob.uuid FROM leaf, blob, tag, tagxref
WHERE blob.rid=leaf.rid
AND tag.tagname='sym-'||$branchname
AND tagxref.tagid=tag.tagid
AND tagxref.tagtype>0
AND tagxref.rid=leaf.rid;
 
(2) Find the youngest revision tagged with T (specifically: release)

SELECT blob.uuid FROM blob, tag, tagxref, event
WHERE tag.tagname='sym-'||$tagname
AND tagxref.tagid=tag.tagid
AND tagxref.tagtype>0
AND event.objid=tag.rid
AND event.type='ci'
ORDER BY event.mtime DESC LIMIT 1;

 
(3) Find all revisions reachable from (1) via parent-links (primary
and merge!) coming after (2) timewise.
    Note that this definition
    = includes revisions on merged branches/forks, and
    = (2) is excluded from the set, and not necessarily reachable from (1)

/* All ancestors of node $uuid that occur after time $cutoff */
WITH RECURSIVE
  ancestors(rid) AS (
     SELECT rid FROM blob WHERE uuid=$uuid
     UNION
     SELECT pid FROM plink, ancestors, event
        WHERE plink.cid=ancestors.rid
             AND event.objid=ancestors.rid
             AND event.mtime>$cutoff
 )
SELECT blob.uuid FROM blob, ancestors
WHERE blob.rid=ancestors.rid;

 
(4) Per revision in the set of (3) determine the commit message.

/* All ancestors of node $uuid that occur after time $cutoff */
WITH RECURSIVE
  ancestors(rid) AS (
     SELECT rid FROM blob WHERE uuid=$uuid
     UNION
     SELECT pid FROM plink, ancestors, event
        WHERE plink.cid=ancestors.rid
             AND event.objid=ancestors.rid
             AND event.mtime>$cutoff
 )
SELECT blob.uuid, coalesce(event.ecomment,event.comment)
 FROM blob, ancestors, event
WHERE blob.rid=ancestors.rid
AND event.objid=ancestors.rid
ORDER BY event.mtime DESC;

 
(5) Per revision in the set of (3) determine the set of changed files
(edited/added/removed)

WITH RECURSIVE
  ancestors(rid) AS (
     SELECT rid FROM blob WHERE uuid=$uuid
     UNION
     SELECT pid FROM plink, ancestors, event
        WHERE plink.cid=ancestors.rid
             AND event.objid=ancestors.rid
             AND event.mtime>$cutoff
 )
SELECT
    blob.uuid,
    filename.name,
    CASE WHEN nullif(mlink.pid,0) is null THEN 'added'
        WHEN nullif(mlink.fid,0) is null THEN 'deleted'
        ELSE 'edited' END
 FROM blob, ancestors, event, mlink, filename
WHERE blob.rid=ancestors.rid
AND event.objid=ancestors.rid
AND mlink.mid=ancestor.rid
AND mlink.fnid=filename.fnid
ORDER BY event.mtime DESC, filename.name;

= end of untested bits
========================================================================