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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
|
+
+
+
-
+
-
-
+
+
+
+
-
+
+
-
+
-
+
-
+
+
+
-
-
-
-
-
-
-
+
+
+
+
+
+
+
|
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
origin(rid) AS(
SELECT 3285 -- origin RID
),
ancestors(rid,uuid,tm) AS (
ancestors(rid,uuid,tm,user,comment) 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
SELECT b.rid, b.uuid, e.mtime, e.user,
coalesce(e.ecomment,e.comment)
FROM blob b, event e, origin
WHERE b.rid=origin.rid and e.objid=b.rid
UNION
SELECT p.pid, b.uuid, e.mtime
SELECT p.pid, b.uuid, e.mtime, e.user,
coalesce(e.ecomment,e.comment)
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
LIMIT 6
)
SELECT b.rid,
SELECT a.rowid, b.rid,
substr(b.uuid,0,8) uuid,
datetime(a.tm,'localtime') time
datetime(a.tm,'localtime') time,
user,
substr(comment,0,20)||'...' comment
from blob b, ancestors a
WHERE b.rid=a.rid
--WHERE blob.rid IN ancestors;
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
rowid|rid|uuid|time|user|comment
1|3285|8f89acc|2014-02-03 22:36:17|stephan|pulled in sqlite3.{...
2|3279|5a477d1|2014-02-03 20:38:03|stephan|another round of mi...
3|3272|084c05a|2014-02-03 20:18:07|stephan|disabled auto-updat...
4|3267|782894e|2014-02-03 18:34:27|stephan|x64/i32 compatibili...
5|3161|4e9d087|2014-02-03 14:01:47|stephan|Removed check for s...
6|3144|249ad02|2014-02-03 11:33:29|stephan|minor build/portabi...
</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
|