Login
Changes To SQLSnippets
Login
Next Previous

Changes to "SQLSnippets" between 2014-02-03 20:32:18 and 2014-02-03 21:13:10

︙
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

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
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
114
115
116
117


118
119
120
121
122
123








-
+


-
-
+
+

-
-
-
+
+
+
+

+
-
-
-
-
+
+
+
+
-
-
-



-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

-
-
+
+
+
+
+


;

</pre></nowiki>


<h2>All ancestors of a given checkin</h2>

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

<nowiki><pre>
-- The RID and UUID of all ancestors of the checkin
-- RID provided in the first SELECT statement...
-- All ancestors (direct or merged!) of the checkin
-- RID given in the first SELECT...
WITH RECURSIVE
  ancestors(rid,uuid) AS (
     -- checkin RID:
     SELECT b.rid, b.uuid FROM blob b where rid=3279
  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
     SELECT pid, b.uuid FROM plink, blob b, ancestors
        WHERE plink.cid=b.rid
        AND plink.cid=ancestors.rid

     FROM plink p, blob b, ancestors a, event e
        WHERE p.cid=b.rid
        AND p.cid=a.rid
        AND e.objid=p.pid
-- 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
     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 blob.rid, blob.uuid from blob, ancestors
WHERE blob.rid=ancestors.rid;
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>