D 2014-02-02T16:44:33.158
L SQLSnippets
P 1872adef85a5e8f2f90b3f24648a28525507e7fc
U stephan
W 1184
<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>

Z b00bb8d2f799957d7d9631287464a951