Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Artifact ID:	3c1eecd4a5f595f94d9fe6137b8ba07043e863d7
Page Name:	SQLSnippets
Date:	2014-02-03 21:13:10
Original User:	stephan
Parent:	55f51528f1a458171a20e7b0a3eaf9d7dbbd2394 (diff)
Next	54f276a207251da12f022cdf20cb4c2a2fb3fbfd

SQL Snippets

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

List of files changed by a given commit UUID:

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

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

To get more info...

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
;

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

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
;

All ancestors of a given checkin

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

-- 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;
;

Example output:

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

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:

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