Login
Changes To SQLSnippets
Login
Next Previous

Changes to "SQLSnippets" between 2014-01-27 22:37:57 and 2014-01-27 22:43:31

1
2
3
4
5
6
7
8
9
10
11




















12
13
14


15
16
17
18
19
20
21
22

23
24
25


1
2
3
4
5
6
7
8



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


30
31
32
33
34
35
36
37
38

39
40

41
42









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

-
-
+
+







-
+

-

+

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

<nowiki><pre>
SELECT
        bf.uuid,
        filename.name fname,
        bf.size
    bf.uuid,
    filename.name fname,
    bf.size
FROM mlink, filename,
    blob bf, -- FILE blob
    blob bm -- MANIFEST/checkin blob
WHERE 
bm.uuid glob '6b581c89f90c*'
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
    blob bf, -- FILE blob
    blob bm -- MANIFEST/checkin blob
WHERE 
bm.uuid glob '6b581c89f90c*'
AND filename.fnid=mlink.fnid
AND event.objid=mlink.mid
AND bf.rid=mlink.fid
AND bm.rid=mlink.mid
;
</pre></nowiki>


Hmmm. i think that "should" be possible without going through the event table, but i currently don't see how.

</pre></nowiki>