49
50
51
52
53
54
55
|
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
|
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
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:
<nowiki><pre>
-- The RID and UUID of all ancestors of the checkin
-- RID provided in the first SELECT statement...
WITH RECURSIVE
ancestors(rid,uuid) AS (
-- checkin RID:
SELECT b.rid, b.uuid FROM blob b where rid=3279
UNION
SELECT pid, b.uuid FROM plink, blob b, ancestors
WHERE plink.cid=b.rid
AND plink.cid=ancestors.rid
-- To limit by time: add event table to JOIN, then:
-- AND event.objid=ancestors.rid
-- AND event.mtime>$cutoff
-- Optionally limit it to the first N
-- ancestors (including the original checkin):
-- LIMIT 5
)
SELECT blob.rid, blob.uuid from blob, ancestors
WHERE blob.rid=ancestors.rid;
</pre></nowiki>
|