Fossil

Check-in [b302f893]
Login

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

Overview
Comment:Correct issue with open/closed tags by changing the closed column to a quasi-aggregate query, i.e. one which is a function of the argument to GROUP BY and not anything which varies within the group. This gives consistent results with the baseline branch ls command, but it introduces a MAJOR performance regression. Listing closed tags goes from 0.033s in the baseline to 1.882s, i.e. it takes 56 times longer.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | andygoth-branch-list
Files: files | file ages | folders
SHA1: b302f8935211984ec6e8801059f003cb959555ad
User & Date: andygoth 2016-11-19 05:59:13
Context
2016-11-19
05:59
Correct issue with open/closed tags by changing the closed column to a quasi-aggregate query, i.e. one which is a function of the argument to GROUP BY and not anything which varies within the group. This gives consistent results with the baseline branch ls command, but it introduces a MAJOR performance regression. Listing closed tags goes from 0.033s in the baseline to 1.882s, i.e. it takes 56 times longer. Leaf check-in: b302f893 user: andygoth tags: andygoth-branch-list
2016-11-18
21:45
Remove min() from closed expression. It doesn't solve the problem, and it confuses analysis of the issue. check-in: 4e7d2ce1 user: andygoth tags: andygoth-branch-list
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/branch.c.

196
197
198
199
200
201
202
203




204
205
206






207
208
209
210
211
212
213
214
...
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
...
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
/*
** Prepare a query that will list branches.
*/
void branch_prepare_list_query(Stmt *pQuery, int brFlags){
  Blob sql = BLOB_INITIALIZER;
  blob_zero(&sql);

  /* Begin the query. */




  blob_append_sql(&sql,
    "SELECT tagxref.value AS name"
         ", max(event.mtime) AS mtime"






         ", %z AS closed"
         ", (SELECT tagxref.value"
             " FROM plink CROSS JOIN tagxref"
            " WHERE plink.pid=event.objid"
              " AND tagxref.rid=plink.cid"
              " AND tagxref.tagid=%d"
              " AND tagtype>0) AS mergeto"
         ", count(*) AS cicount"
................................................................................
         ", (SELECT uuid FROM blob WHERE rid=tagxref.rid) AS latest"
         ", event.bgcolor AS bgcolor"
     " FROM tagxref, tag, event"
    " WHERE tagxref.tagid=tag.tagid"
      " AND tagxref.tagtype>0"
      " AND tag.tagname='branch'"
      " AND event.objid=tagxref.rid",
    leaf_is_closed_sql("tagxref.rid"), TAG_BRANCH);

  /* Group by name to implement the cicount column. */
  blob_append_sql(&sql, " GROUP BY name");

  /* Apply open/closed filtering if requested. */
  if( (brFlags & BRL_OPEN_CLOSED_MASK)==BRL_CLOSED_ONLY ){
    blob_append_sql(&sql, " HAVING closed");
................................................................................
  rNow = db_double(0.0, "SELECT julianday('now')");
  @ <div class="brlist"><table id="branchlisttable">
  @ <thead><tr>
  @ <th>Branch Name</th>
  @ <th>Age</th>
  @ <th>Check-ins</th>
  if( (flags & BRL_OPEN_CLOSED_MASK) == BRL_BOTH ){
  @ <th>Status</th>
  }
  @ <th>Resolution</th>
  @ </tr></thead><tbody>
  while( db_step(&q)==SQLITE_ROW ){
    const char *zBranch = db_column_text(&q, 0);
    double rMtime = db_column_double(&q, 1);
    int isClosed = db_column_int(&q, 2);







|
>
>
>
>



>
>
>
>
>
>
|







 







|







 







|







196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
...
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
...
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
/*
** Prepare a query that will list branches.
*/
void branch_prepare_list_query(Stmt *pQuery, int brFlags){
  Blob sql = BLOB_INITIALIZER;
  blob_zero(&sql);

  /* Begin the query.
   *
   * A branch is closed if it has no open leaves, where an open leaf is one not
   * referenced by a closed tag.  The design of the "closed" expression reflects
   * this double negative logical structure. */
  blob_append_sql(&sql,
    "SELECT tagxref.value AS name"
         ", max(event.mtime) AS mtime"
         ", NOT EXISTS"
          " (SELECT 1"
             " FROM tagxref AS tx1"
            " WHERE value=tagxref.value"
              " AND tagid=%d"
              " AND rid IN leaf"
              " AND NOT %z) AS closed"
         ", (SELECT tagxref.value"
             " FROM plink CROSS JOIN tagxref"
            " WHERE plink.pid=event.objid"
              " AND tagxref.rid=plink.cid"
              " AND tagxref.tagid=%d"
              " AND tagtype>0) AS mergeto"
         ", count(*) AS cicount"
................................................................................
         ", (SELECT uuid FROM blob WHERE rid=tagxref.rid) AS latest"
         ", event.bgcolor AS bgcolor"
     " FROM tagxref, tag, event"
    " WHERE tagxref.tagid=tag.tagid"
      " AND tagxref.tagtype>0"
      " AND tag.tagname='branch'"
      " AND event.objid=tagxref.rid",
    TAG_BRANCH, leaf_is_closed_sql("tx1.rid"), TAG_BRANCH);

  /* Group by name to implement the cicount column. */
  blob_append_sql(&sql, " GROUP BY name");

  /* Apply open/closed filtering if requested. */
  if( (brFlags & BRL_OPEN_CLOSED_MASK)==BRL_CLOSED_ONLY ){
    blob_append_sql(&sql, " HAVING closed");
................................................................................
  rNow = db_double(0.0, "SELECT julianday('now')");
  @ <div class="brlist"><table id="branchlisttable">
  @ <thead><tr>
  @ <th>Branch Name</th>
  @ <th>Age</th>
  @ <th>Check-ins</th>
  if( (flags & BRL_OPEN_CLOSED_MASK) == BRL_BOTH ){
    @ <th>Status</th>
  }
  @ <th>Resolution</th>
  @ </tr></thead><tbody>
  while( db_step(&q)==SQLITE_ROW ){
    const char *zBranch = db_column_text(&q, 0);
    double rMtime = db_column_double(&q, 1);
    int isClosed = db_column_int(&q, 2);