D 2014-02-03T21:13:10.431
L SQLSnippets
P 55f51528f1a458171a20e7b0a3eaf9d7dbbd2394
U stephan
W 3140
<h1>SQL Snippets</h1>

A collection of SQL snippets which have proven useful when tinkering file fossil:

<h2>List of files changed by a given commit UUID:</h2>


To get just the names of files modified in a given RID:

<nowiki><pre>
SELECT name FROM filename f, mlink m
WHERE m.mid=3242 -- checking RID value
AND m.fnid=f.fnid;
</pre></nowiki>

To get more info...

<nowiki><pre>
SELECT
    bf.uuid,
    filename.name fname,
    bf.size
FROM mlink, filename,
    blob bf, -- FILE blob
    blob bm -- MANIFEST/checkin blob
WHERE 
bm.uuid glob '6b581c89f90c*'
-- resp: bm.rid=rid_of_checkin
AND filename.fnid=mlink.fnid
AND bf.rid=mlink.fid
AND bm.rid=mlink.mid
;
</pre></nowiki>

To get timestamp info (same value for all rows) we need the "event" table:

<nowiki><pre>
SELECT
    bf.uuid,
    filename.name fname,
    bf.size,
    datetime(event.mtime)
FROM mlink, filename, event,
    blob bf, -- FILE blob
    blob bm -- MANIFEST/checkin blob
WHERE 
bm.uuid glob '6b581c89f90c*'
-- resp: bm.rid=rid_of_checkin
AND filename.fnid=mlink.fnid
AND event.objid=mlink.mid
AND bf.rid=mlink.fid
AND bm.rid=mlink.mid
;

</pre></nowiki>


<h2>All ancestors of a given checkin</h2>

My first "WITH RECURSIVE" statement, adapted from one written by drh, lists information about the ancestry of a given commit...

<nowiki><pre>
-- All ancestors (direct or merged!) of the checkin
-- RID given in the first SELECT...
WITH RECURSIVE
  ancestors(rid,uuid,tm) AS (
     -- Change this checkin RID:
     SELECT b.rid, b.uuid, e.mtime FROM blob b, event e
        WHERE b.rid=3279 and e.objid=b.rid
     UNION
     SELECT p.pid, b.uuid, e.mtime
     FROM plink p, blob b, ancestors a, event e
        WHERE p.cid=b.rid
        AND p.cid=a.rid
        AND e.objid=p.pid

-- Optionally limit it to the first N
-- ancestors (including the original checkin):
     LIMIT 5
 )
SELECT b.rid,
       substr(b.uuid,0,8) uuid,
       datetime(a.tm,'localtime') time
from blob b, ancestors a
WHERE b.rid=a.rid
--WHERE blob.rid IN ancestors;
;
</pre></nowiki>


Example output:
<nowiki><pre>
rid|uuid|time
3279|5a477d1|2014-02-03 19:38:03
3272|084c05a|2014-02-03 19:18:07
3267|782894e|2014-02-03 17:34:27
3161|4e9d087|2014-02-03 13:01:47
3144|249ad02|2014-02-03 10:33:29
</pre></nowiki>


A very similar query, which could possibly be simplified by one wiser in the ways of SQL than myself, which uses a (Julian) time to limit how far back the ancestry check goes:

<nowiki><pre>
WITH RECURSIVE
  ancestors(rid,uuid,tm) AS (
     -- Change this checkin RID:
     SELECT b.rid, b.uuid, e.mtime FROM blob b, event e
        WHERE b.rid=3279 and e.objid=b.rid
     UNION
     SELECT p.pid, b.uuid, e.mtime
     FROM plink p, blob b, ancestors a, event e
        WHERE p.cid=b.rid
        AND p.cid=a.rid
        AND e.objid=p.pid
        AND e.mtime > 2456691.31809243 -- mtime of RID 3279 minus 1 day
 )
SELECT b.rid,
       substr(b.uuid,0,8) uuid,
       datetime(a.tm,'localtime') time
from blob b, ancestors a
WHERE b.rid=a.rid
</pre></nowiki>

Z c354e9f99710355adb7ebf3494aabc55