Login
Update of ”SQLSnippets”
Login
History Page Raw

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

Overview

Artifact ID: 90f7556556184bca1a8b1cd41345f2f1b37b093d
Page Name:SQLSnippets
Date: 2014-02-04 18:09:58
Original User: stephan
Parent: fa6e2389c4640a56f18f880c337b83d5a5a6ea67 (diff)
Next f4cd8f40eaefcf92e9b90f1223844d8f06df147e
Content

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 -- checkin 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
  origin(rid) AS(
    SELECT 3285 -- origin RID
  ),
  ancestors(rid,uuid,tm,user,comment) AS (
     -- Change this checkin 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 ALL
     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 6
 )
SELECT a.rowid, b.rid,
       substr(b.uuid,0,8) uuid,
       datetime(a.tm,'localtime') time,
       user,
       substr(comment,0,20)||'...' comment
from blob b, ancestors a
WHERE b.rid=a.rid
;

Example output: 

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

(Sidebar: that same info is all available in the event table, so we could change the recursion to only fetch the RIDs, then join that data to the event table to get the same info.)

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

Here's a more refined variant of the above, applying what i learned during an evening of tinkering with it... 

-- All ancestors (direct or merged!) of the checkin
-- RID given in the first SELECT...
WITH RECURSIVE
-- Change this RID to the RID of the
-- origin for ancestry tracking
  theId(id) AS(
    SELECT 3285 as id -- origin RID
--    SELECT '8f89acc0f05df7bae1e7946efe5324f1e6905a9e' as id
  ),
  origin(rid, mtime, cutoffTime) AS(
    -- origin RID
    SELECT b.rid as rid,
           e.mtime as mtime,
           (e.mtime - 1.5) as cutoffTime -- Julian days
    FROM blob b, event e, theRid
    WHERE
    b.rid=theId.id
-- or use the UUID of the origin:
--  b.uuid=theId.id
    AND e.objid=b.rid
  ),
  ancestors(rid,uuid,tm,user,comment) AS (
     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 ALL
     SELECT p.pid, b.uuid, e.mtime, e.user,
            coalesce(e.ecomment,e.comment)
     FROM plink p, blob b,
          ancestors a, event e,
          origin
        WHERE p.cid=b.rid
        AND p.cid=a.rid
        AND e.objid=p.pid
-- Only trace back this far in time...
        AND e.mtime >= origin.cutoffTime
-- ^^^ if that is removed, also remove origin from the join!

-- Optionally limit it to the first N
-- ancestors (including the original checkin):
--    LIMIT 5
 )
SELECT a.rowid, b.rid,
       substr(b.uuid,0,8) uuid,
       datetime(a.tm,'localtime') time,
       user,
       substr(comment,0,20)||'...' comment
from blob b, ancestors a
WHERE b.rid=a.rid
;

Recursion Example

See also: http://www.sqlite.org/draft/lang_with.html

The following is adapted from a sqlite mailing list post by Petite Abeille on 20140203, not directly applicable to fossil but which is an interesting example nonetheless and which can certainly be used as a model for generating fossil-related data: 

with
DataSet(node,parent)
as
(
  select  'A', null union all
  select  'F', null union all
  select  'B', 'A' union all
  select  'C', 'B' union all
  select  'E', 'F' union all
  select  'H', 'E' union all
  select  'G', 'E' union all
  select  'D', 'C'
),
Hierarchy( node, parent, level, path, isHead, isTail )
as
(
  select  DataSet.node as node,
          'NULL' as parent, -- DataSet.parent,
          0 as level,
          ' → ' || DataSet.node as path,
          DataSet.parent IS NULL,
          NOT EXISTS (SELECT 1 FROM DataSet d WHERE d.parent=DataSet.node)
  from     DataSet
  where    DataSet.parent is null

  union all
  select  DataSet.node as node,
          DataSet.parent as parent,
          Hierarchy.level + 1 as level,
          Hierarchy.path || ' → ' || DataSet.node as path,
          DataSet.parent IS NULL,
          NOT EXISTS (SELECT 1 FROM DataSet d WHERE d.parent=DataSet.node)
  from    Hierarchy
  join    DataSet
  on      DataSet.parent = Hierarchy.node
  ORDER BY parent, node
)
select    *
from      Hierarchy
-- where level>1
order by  path;