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