Fossil

Check-in [47bfea074b]
Login
Home Timeline Docs Branches Tickets Wiki DownloadMore...

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

Overview
Comment:Use the new multi-recursive-term capability of CTEs in SQLite to fix the /finfo clade calculation, and to make /finfo run about 5x faster.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 47bfea074b1226161c9cbe6489b742b7b8cdf3d3c2cd985718d6d504b1a5a37c
User & Date: drh 2020-10-19 14:37:26.684
Context
2020-10-19
18:24
Improvements to /finfo: Show all file rename events. The "rid" for each timeline entry must be a combination of the file-id "fid" and the filename-id "fnid" since either one of those two might change which should result in a new entry on the timeline. check-in: 8c598d7232 user: drh tags: trunk
14:37
Use the new multi-recursive-term capability of CTEs in SQLite to fix the /finfo clade calculation, and to make /finfo run about 5x faster. check-in: 47bfea074b user: drh tags: trunk
13:25
Update the built-in SQLite to the latest 3.34.0 alpha that includes the ability to have multiple recursive terms in a recursive CTE. That new capability is expected to be helpful in improving the /finfo page. check-in: 5328f82160 user: drh tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/finfo.c.
︙︙
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434










435
436
437
438
439
440
441
442
443

  }
  url_add_parameter(&url, "name", zFilename);
  blob_zero(&sql);
  if( ridCi ){
    /* If we will be tracking changes across renames, some extra temp
    ** tables (implemented as CTEs) are required */
    blob_append_sql(&sql,
      /* The fns(fnid) table holds the list of all filename-IDs that
      ** might possibly exist in the output.  This is an optimization
      ** used to reduce the size and computation efforts for subsequent
      ** CTEs.
      */
      "WITH RECURSIVE fns(fnid) AS (\n"
      "  SELECT %d\n"   /* <---- fnid */
      "  UNION\n"
      "  SELECT pfnid FROM mlink, fns\n"
      "   WHERE mlink.fnid=fns.fnid\n"
      "     AND pfnid>0\n"
      "),\n"

      /* The flink(fid,fnid,pfid,pfnid) table indicates that there
      ** is an edit and/or rename arc connecting two files (fid,fnid)
      ** and (pfid,pfnid).  This is similar to the built-in mlink
      ** table except that flink() is bidirectional.  Also the pfnid
      ** column is always set even no rename occurs.
      */
      "flink(fid,fnid,pfid,pfnid) AS (\n"
      "  SELECT fid, fnid, pid,\n"
      "    CASE WHEN pfnid>0 THEN pfnid ELSE fnid END\n"
      "    FROM mlink\n"
      "   WHERE NOT isaux AND fid>0 AND pid>0 AND fnid IN fns\n"
      "  UNION\n"
      "  SELECT pid,\n"
      "    CASE WHEN pfnid>0 THEN pfnid ELSE fnid END,\n"
      "    fid, fnid\n"
      "    FROM mlink\n"
      "   WHERE NOT isaux AND pid>0 AND fid>0 AND fnid IN fns\n"
      "),\n"

      /* The clade(fid,fnid) table is the set of all (fid,fnid) pairs
      ** that should participate in the output.  Clade is computed by
      ** walking the graph formed by the flink table.
      */
      "clade(fid,fnid) AS (\n"
      "  SELECT blob.rid, %d FROM blob\n"         /* %d is fnid */
      "   WHERE blob.uuid=(SELECT uuid FROM files_of_checkin(%Q)\n"
                         " WHERE filename=%Q)\n"  /* %Q is the filename */
      "   UNION\n"
      "  SELECT flink.fid, flink.fnid\n"
      "    FROM clade, flink\n"
      "   WHERE clade.fid=flink.pfid AND clade.fnid=flink.pfnid\n"










      ")\n",
      fnid, fnid, zCI, zFilename
    );
  }else{
    /* This is the case for all files with a given name.  We will still
    ** create a "clade(fid,fnid)" table that identifies all participates
    ** in the output graph, so that subsequent queries can all be the same,
    ** but in the case the clade table is much simplier, being just a
    ** single direct query against the mlink table.








<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<


|

|




|
|
|
>
>
>
>
>
>
>
>
>
>

|








384
385
386
387
388
389
390
































391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421

  }
  url_add_parameter(&url, "name", zFilename);
  blob_zero(&sql);
  if( ridCi ){
    /* If we will be tracking changes across renames, some extra temp
    ** tables (implemented as CTEs) are required */
    blob_append_sql(&sql,
































      /* The clade(fid,fnid) table is the set of all (fid,fnid) pairs
      ** that should participate in the output.  Clade is computed by
      ** walking the graph of mlink edges.
      */
      "WITH RECURSIVE clade(fid,fnid) AS (\n"
      "  SELECT blob.rid, %d FROM blob\n"         /* %d is fnid */
      "   WHERE blob.uuid=(SELECT uuid FROM files_of_checkin(%Q)\n"
                         " WHERE filename=%Q)\n"  /* %Q is the filename */
      "   UNION\n"
      "  SELECT mlink.fid, mlink.fnid\n"
      "    FROM clade, mlink\n"
      "   WHERE clade.fid=mlink.pid\n"
      "     AND ((mlink.pfnid=0 AND mlink.fnid=clade.fnid)\n"
      "          OR mlink.pfnid=clade.fnid)\n"
      "     AND mlink.fid>0"
      "   UNION\n"
      "  SELECT mlink.pid,"
              " CASE WHEN mlink.pfnid>0 THEN mlink.pfnid ELSE mlink.fnid END\n"
      "    FROM clade, mlink\n"
      "   WHERE mlink.pid>0\n"
      "     AND mlink.fid=clade.fid\n"
      "     AND mlink.fnid=clade.fnid\n"
      ")\n",
      fnid, zCI, zFilename
    );
  }else{
    /* This is the case for all files with a given name.  We will still
    ** create a "clade(fid,fnid)" table that identifies all participates
    ** in the output graph, so that subsequent queries can all be the same,
    ** but in the case the clade table is much simplier, being just a
    ** single direct query against the mlink table.
︙︙
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478

    "  (SELECT value FROM tagxref WHERE tagid=%d AND tagtype>0"
                             " AND tagxref.rid=mlink.mid),\n" /* Branchname */
    "  mlink.mid,\n"                                    /* check-in ID */
    "  mlink.pfnid,\n"                                  /* Previous filename */
    "  blob.size,\n"                                    /* File size */
    "  mlink.fnid,\n"                                   /* Current filename */
    "  filename.name\n"                                 /* Current filename */
    "FROM clade, mlink, event, blob, filename\n"
    "WHERE mlink.fnid=clade.fnid AND mlink.fid=clade.fid\n"
    "  AND event.objid=mlink.mid\n"
    "  AND blob.rid=clade.fid\n"
    "  AND filename.fnid=clade.fnid\n",
    TAG_BRANCH
  );
  if( (zA = P("a"))!=0 ){








|








442
443
444
445
446
447
448
449
450
451
452
453
454
455
456

    "  (SELECT value FROM tagxref WHERE tagid=%d AND tagtype>0"
                             " AND tagxref.rid=mlink.mid),\n" /* Branchname */
    "  mlink.mid,\n"                                    /* check-in ID */
    "  mlink.pfnid,\n"                                  /* Previous filename */
    "  blob.size,\n"                                    /* File size */
    "  mlink.fnid,\n"                                   /* Current filename */
    "  filename.name\n"                                 /* Current filename */
    "FROM clade CROSS JOIN mlink, event, blob, filename\n"
    "WHERE mlink.fnid=clade.fnid AND mlink.fid=clade.fid\n"
    "  AND event.objid=mlink.mid\n"
    "  AND blob.rid=clade.fid\n"
    "  AND filename.fnid=clade.fnid\n",
    TAG_BRANCH
  );
  if( (zA = P("a"))!=0 ){
︙︙
This page was generated in about 0.016s by Fossil 2.26 [1205ec86cb] 2025-04-30 16:57:32