Fossil SCM

Refactor the forum status display for performance.

drh 2026-05-27 17:31 UTC forum-statuses
Commit 0dae5f8bd93620fac77773b1559f69594252cbb7d3dcce5cfec1962dc42f6602
Parent 2d23c2e9cd5fbc3…
1 file changed +51 -34
~ src/forum.c
M src/forum.c
+51 -34
--- src/forum.c
+++ src/forum.c
@@ -2415,19 +2415,24 @@
24152415
 int iLimit = 0, iOfst, iCnt;
24162416
 int srchFlags;
24172417
 const int isSearch = P("s")!=0;
24182418
 const char *zStatusFilter = P("status");
24192419
 char const *zLimit = 0;
2420
- const int bHasStatus = forum_statuses()->n>1;
2420
+ int eStatusTag = 0;
2421
+ int bHasStatus = 0;
24212422
24222423
 login_check_credentials();
24232424
 srchFlags = search_restrict(SRCH_FORUM);
24242425
 if( !g.perm.RdForum ){
24252426
 login_needed(g.anon.RdForum);
24262427
 return;
24272428
 }
24282429
 cgi_check_for_malice();
2430
+ eStatusTag = db_int(0, "SELECT tagid FROM tag WHERE tagname='status'");
2431
+ if( eStatusTag && forum_statuses()->n>1 ){
2432
+ bHasStatus = 1;
2433
+ }
24292434
 style_set_current_feature("forum");
24302435
 style_header("%s%s", db_get("forum-title","Forum"),
24312436
 isSearch ? " Search Results" : "");
24322437
 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
24332438
 if( g.perm.WrForum ){
@@ -2465,64 +2470,72 @@
24652470
 if( zStatusFilter &&
24662471
 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
24672472
 zStatusFilter = 0;
24682473
 }
24692474
 if( db_table_exists("repository","forumpost") ){
2475
+ Stmt qStat;
2476
+ if( bHasStatus ){
2477
+ db_prepare(&qStat,
2478
+ "SELECT tagxref.value, forumstatus.label\n"
2479
+ " FROM forumstatus, tagxref\n"
2480
+ " WHERE tagid=%d AND tagtype>=1\n"
2481
+ " AND forumstatus.value=tagxref.value\n"
2482
+ " AND rid=:rid\n"
2483
+ " ORDER BY mtime DESC",
2484
+ db_int(0,"SELECT tagid FROM tag WHERE tagname='status'")
2485
+ );
2486
+ }
24702487
 db_prepare(&q,
2471
- "WITH "
2472
- "thread(age,duration,cnt,root,last) AS (\n"
2488
+ "WITH thread(root,endtime,lastrid) AS (\n"
24732489
 " SELECT\n"
2474
- " julianday('now') - max(fmtime),\n"
2475
- " max(fmtime) - min(fmtime),\n"
2476
- " sum(fprev IS NULL),\n"
24772490
 " froot,\n"
2478
- " (SELECT fpid FROM forumpost AS y\n"
2479
- " WHERE y.froot=x.froot %s\n"
2480
- " ORDER BY y.fmtime DESC LIMIT 1)\n"
2481
- " FROM forumpost AS x\n"
2491
+ " max(fmtime),\n"
2492
+ " fpid\n"
2493
+ " FROM forumpost\n"
24822494
 " WHERE firt IS NULL AND %s/*ModForum*/\n"
24832495
 " GROUP BY froot\n"
2484
- " ORDER BY 1\n"
2496
+ " ORDER BY 2 DESC\n"
24852497
 " LIMIT %d OFFSET %d\n"
24862498
 ")\n"
24872499
 "SELECT\n"
2488
- " thread.age,\n" /* 0 */
2489
- " thread.duration,\n" /* 1 */
2490
- " thread.cnt,\n" /* 2 */
2500
+ " julianday('now') - thread.endtime,\n" /* 0 */
2501
+ " (SELECT fmtime FROM forumpost WHERE fpid=root),\n" /* 1 */
2502
+ " (SELECT sum(fprev IS NULL) FROM forumpost"
2503
+ " WHERE froot=root),\n" /* 2 */
24912504
 " blob.uuid,\n" /* 3 */
24922505
 " substr(event.comment,instr(event.comment,':')+1),\n" /* 4 */
2493
- " thread.last,\n" /* 5 */
2494
- " (SELECT coalesce(fs.value,dfs.value)\n"
2495
- " FROM tag, tagxref, forumstatus fs\n"
2496
- " WHERE tag.tagname='status'\n"
2497
- " AND tagxref.tagid=tag.tagid\n"
2498
- " AND tagxref.tagtype>=1\n"
2499
- " AND fs.value=tagxref.value)," /* 6 */
2500
- " (SELECT coalesce(fs.label,dfs.label)\n"
2501
- " FROM tag, tagxref, forumstatus fs\n"
2502
- " WHERE tag.tagname='status'\n"
2503
- " AND tagxref.tagid=tag.tagid\n"
2504
- " AND tagxref.tagtype>=1\n"
2505
- " AND fs.value=tagxref.value)" /* 7 */
2506
+ " thread.lastrid,\n" /* 5 */
2507
+ " thread.root\n" /* 6 */
25062508
 " FROM thread, blob, event\n"
2507
- " LEFT JOIN forumstatus AS dfs ON (dfs.ord=1)\n"
2508
- " WHERE blob.rid=thread.last\n"
2509
- " AND event.objid=thread.last\n"
2509
+ " WHERE blob.rid=thread.lastrid\n"
2510
+ " AND event.objid=thread.lastrid\n"
25102511
 " ORDER BY 1;",
2511
- g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
25122512
 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
25132513
 iLimit+1, iOfst
25142514
 );
25152515
 while( db_step(&q)==SQLITE_ROW ){
25162516
 char *zAge = human_readable_age(db_column_double(&q,0));
25172517
 int nMsg = db_column_int(&q, 2);
25182518
 const char *zUuid = db_column_text(&q, 3);
25192519
 const char *zTitle = db_column_text(&q, 4);
2520
- const char *zStatus = bHasStatus ? db_column_text(&q, 6) : NULL;
2521
- const char *zStatusLbl = bHasStatus ? db_column_text(&q, 7) : NULL;
2520
+ const char *zStatus;
2521
+ const char *zStatusLbl;
25222522
 const int bShowStatus = bHasStatus && !zStatusFilter;
25232523
 const int nCols = bShowStatus ? 4 : 3;
2524
+ if( bHasStatus ){
2525
+ db_reset(&qStat);
2526
+ db_bind_int(&qStat, ":rid", db_column_int(&q,6));
2527
+ if( db_step(&qStat)==SQLITE_ROW ){
2528
+ zStatus = db_column_text(&qStat, 0);
2529
+ zStatusLbl = db_column_text(&qStat, 1);
2530
+ }else{
2531
+ zStatus = forum_statuses()->aStatus[0].zValue;
2532
+ zStatusLbl = forum_statuses()->aStatus[0].zLabel;
2533
+ }
2534
+ }else{
2535
+ zStatus = zStatusLbl = NULL;
2536
+ }
25242537
 if( iCnt==0 ){
25252538
 char * zTail = zStatusFilter
25262539
 ? mprintf(" with status=%Q", zStatusFilter)
25272540
 : 0;
25282541
 if( iOfst>0 ){
@@ -2573,25 +2586,29 @@
25732586
 @ Awaiting Moderator Approval</span><br>
25742587
 }
25752588
 if( nMsg<2 ){
25762589
 @ no replies\
25772590
 }else{
2578
- char *zDuration = human_readable_age(db_column_double(&q,1));
2591
+ char *zDuration = human_readable_age(db_column_double(&q,0));
25792592
 @ %d(nMsg) posts spanning %h(zDuration)\
25802593
 fossil_free(zDuration);
25812594
 }
25822595
 @ </td>\
25832596
 if( bShowStatus ){
25842597
 @ <td class='status'>%h(zStatusLbl)</td>\
25852598
 }
2599
+ if( bHasStatus ){
2600
+ db_reset(&qStat);
2601
+ }
25862602
 @</tr>
25872603
 fossil_free(zAge);
25882604
 }
25892605
 db_finalize(&q);
2606
+ if( bHasStatus ) db_finalize(&qStat);
25902607
 }
25912608
 if( iCnt>0 ){
25922609
 @ </table></div>
25932610
 }else{
25942611
 @ <h1>No forum posts found</h1>
25952612
 }
25962613
 style_finish_page();
25972614
 }
25982615
--- src/forum.c
+++ src/forum.c
@@ -2415,19 +2415,24 @@
2415 int iLimit = 0, iOfst, iCnt;
2416 int srchFlags;
2417 const int isSearch = P("s")!=0;
2418 const char *zStatusFilter = P("status");
2419 char const *zLimit = 0;
2420 const int bHasStatus = forum_statuses()->n>1;
 
2421
2422 login_check_credentials();
2423 srchFlags = search_restrict(SRCH_FORUM);
2424 if( !g.perm.RdForum ){
2425 login_needed(g.anon.RdForum);
2426 return;
2427 }
2428 cgi_check_for_malice();
 
 
 
 
2429 style_set_current_feature("forum");
2430 style_header("%s%s", db_get("forum-title","Forum"),
2431 isSearch ? " Search Results" : "");
2432 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
2433 if( g.perm.WrForum ){
@@ -2465,64 +2470,72 @@
2465 if( zStatusFilter &&
2466 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
2467 zStatusFilter = 0;
2468 }
2469 if( db_table_exists("repository","forumpost") ){
 
 
 
 
 
 
 
 
 
 
 
 
2470 db_prepare(&q,
2471 "WITH "
2472 "thread(age,duration,cnt,root,last) AS (\n"
2473 " SELECT\n"
2474 " julianday('now') - max(fmtime),\n"
2475 " max(fmtime) - min(fmtime),\n"
2476 " sum(fprev IS NULL),\n"
2477 " froot,\n"
2478 " (SELECT fpid FROM forumpost AS y\n"
2479 " WHERE y.froot=x.froot %s\n"
2480 " ORDER BY y.fmtime DESC LIMIT 1)\n"
2481 " FROM forumpost AS x\n"
2482 " WHERE firt IS NULL AND %s/*ModForum*/\n"
2483 " GROUP BY froot\n"
2484 " ORDER BY 1\n"
2485 " LIMIT %d OFFSET %d\n"
2486 ")\n"
2487 "SELECT\n"
2488 " thread.age,\n" /* 0 */
2489 " thread.duration,\n" /* 1 */
2490 " thread.cnt,\n" /* 2 */
 
2491 " blob.uuid,\n" /* 3 */
2492 " substr(event.comment,instr(event.comment,':')+1),\n" /* 4 */
2493 " thread.last,\n" /* 5 */
2494 " (SELECT coalesce(fs.value,dfs.value)\n"
2495 " FROM tag, tagxref, forumstatus fs\n"
2496 " WHERE tag.tagname='status'\n"
2497 " AND tagxref.tagid=tag.tagid\n"
2498 " AND tagxref.tagtype>=1\n"
2499 " AND fs.value=tagxref.value)," /* 6 */
2500 " (SELECT coalesce(fs.label,dfs.label)\n"
2501 " FROM tag, tagxref, forumstatus fs\n"
2502 " WHERE tag.tagname='status'\n"
2503 " AND tagxref.tagid=tag.tagid\n"
2504 " AND tagxref.tagtype>=1\n"
2505 " AND fs.value=tagxref.value)" /* 7 */
2506 " FROM thread, blob, event\n"
2507 " LEFT JOIN forumstatus AS dfs ON (dfs.ord=1)\n"
2508 " WHERE blob.rid=thread.last\n"
2509 " AND event.objid=thread.last\n"
2510 " ORDER BY 1;",
2511 g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
2512 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
2513 iLimit+1, iOfst
2514 );
2515 while( db_step(&q)==SQLITE_ROW ){
2516 char *zAge = human_readable_age(db_column_double(&q,0));
2517 int nMsg = db_column_int(&q, 2);
2518 const char *zUuid = db_column_text(&q, 3);
2519 const char *zTitle = db_column_text(&q, 4);
2520 const char *zStatus = bHasStatus ? db_column_text(&q, 6) : NULL;
2521 const char *zStatusLbl = bHasStatus ? db_column_text(&q, 7) : NULL;
2522 const int bShowStatus = bHasStatus && !zStatusFilter;
2523 const int nCols = bShowStatus ? 4 : 3;
 
 
 
 
 
 
 
 
 
 
 
 
 
2524 if( iCnt==0 ){
2525 char * zTail = zStatusFilter
2526 ? mprintf(" with status=%Q", zStatusFilter)
2527 : 0;
2528 if( iOfst>0 ){
@@ -2573,25 +2586,29 @@
2573 @ Awaiting Moderator Approval</span><br>
2574 }
2575 if( nMsg<2 ){
2576 @ no replies\
2577 }else{
2578 char *zDuration = human_readable_age(db_column_double(&q,1));
2579 @ %d(nMsg) posts spanning %h(zDuration)\
2580 fossil_free(zDuration);
2581 }
2582 @ </td>\
2583 if( bShowStatus ){
2584 @ <td class='status'>%h(zStatusLbl)</td>\
2585 }
 
 
 
2586 @</tr>
2587 fossil_free(zAge);
2588 }
2589 db_finalize(&q);
 
2590 }
2591 if( iCnt>0 ){
2592 @ </table></div>
2593 }else{
2594 @ <h1>No forum posts found</h1>
2595 }
2596 style_finish_page();
2597 }
2598
--- src/forum.c
+++ src/forum.c
@@ -2415,19 +2415,24 @@
2415 int iLimit = 0, iOfst, iCnt;
2416 int srchFlags;
2417 const int isSearch = P("s")!=0;
2418 const char *zStatusFilter = P("status");
2419 char const *zLimit = 0;
2420 int eStatusTag = 0;
2421 int bHasStatus = 0;
2422
2423 login_check_credentials();
2424 srchFlags = search_restrict(SRCH_FORUM);
2425 if( !g.perm.RdForum ){
2426 login_needed(g.anon.RdForum);
2427 return;
2428 }
2429 cgi_check_for_malice();
2430 eStatusTag = db_int(0, "SELECT tagid FROM tag WHERE tagname='status'");
2431 if( eStatusTag && forum_statuses()->n>1 ){
2432 bHasStatus = 1;
2433 }
2434 style_set_current_feature("forum");
2435 style_header("%s%s", db_get("forum-title","Forum"),
2436 isSearch ? " Search Results" : "");
2437 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
2438 if( g.perm.WrForum ){
@@ -2465,64 +2470,72 @@
2470 if( zStatusFilter &&
2471 (!*zStatusFilter || 0==fossil_strcmp("*",zStatusFilter)) ){
2472 zStatusFilter = 0;
2473 }
2474 if( db_table_exists("repository","forumpost") ){
2475 Stmt qStat;
2476 if( bHasStatus ){
2477 db_prepare(&qStat,
2478 "SELECT tagxref.value, forumstatus.label\n"
2479 " FROM forumstatus, tagxref\n"
2480 " WHERE tagid=%d AND tagtype>=1\n"
2481 " AND forumstatus.value=tagxref.value\n"
2482 " AND rid=:rid\n"
2483 " ORDER BY mtime DESC",
2484 db_int(0,"SELECT tagid FROM tag WHERE tagname='status'")
2485 );
2486 }
2487 db_prepare(&q,
2488 "WITH thread(root,endtime,lastrid) AS (\n"
 
2489 " SELECT\n"
 
 
 
2490 " froot,\n"
2491 " max(fmtime),\n"
2492 " fpid\n"
2493 " FROM forumpost\n"
 
2494 " WHERE firt IS NULL AND %s/*ModForum*/\n"
2495 " GROUP BY froot\n"
2496 " ORDER BY 2 DESC\n"
2497 " LIMIT %d OFFSET %d\n"
2498 ")\n"
2499 "SELECT\n"
2500 " julianday('now') - thread.endtime,\n" /* 0 */
2501 " (SELECT fmtime FROM forumpost WHERE fpid=root),\n" /* 1 */
2502 " (SELECT sum(fprev IS NULL) FROM forumpost"
2503 " WHERE froot=root),\n" /* 2 */
2504 " blob.uuid,\n" /* 3 */
2505 " substr(event.comment,instr(event.comment,':')+1),\n" /* 4 */
2506 " thread.lastrid,\n" /* 5 */
2507 " thread.root\n" /* 6 */
 
 
 
 
 
 
 
 
 
 
 
2508 " FROM thread, blob, event\n"
2509 " WHERE blob.rid=thread.lastrid\n"
2510 " AND event.objid=thread.lastrid\n"
 
2511 " ORDER BY 1;",
 
2512 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
2513 iLimit+1, iOfst
2514 );
2515 while( db_step(&q)==SQLITE_ROW ){
2516 char *zAge = human_readable_age(db_column_double(&q,0));
2517 int nMsg = db_column_int(&q, 2);
2518 const char *zUuid = db_column_text(&q, 3);
2519 const char *zTitle = db_column_text(&q, 4);
2520 const char *zStatus;
2521 const char *zStatusLbl;
2522 const int bShowStatus = bHasStatus && !zStatusFilter;
2523 const int nCols = bShowStatus ? 4 : 3;
2524 if( bHasStatus ){
2525 db_reset(&qStat);
2526 db_bind_int(&qStat, ":rid", db_column_int(&q,6));
2527 if( db_step(&qStat)==SQLITE_ROW ){
2528 zStatus = db_column_text(&qStat, 0);
2529 zStatusLbl = db_column_text(&qStat, 1);
2530 }else{
2531 zStatus = forum_statuses()->aStatus[0].zValue;
2532 zStatusLbl = forum_statuses()->aStatus[0].zLabel;
2533 }
2534 }else{
2535 zStatus = zStatusLbl = NULL;
2536 }
2537 if( iCnt==0 ){
2538 char * zTail = zStatusFilter
2539 ? mprintf(" with status=%Q", zStatusFilter)
2540 : 0;
2541 if( iOfst>0 ){
@@ -2573,25 +2586,29 @@
2586 @ Awaiting Moderator Approval</span><br>
2587 }
2588 if( nMsg<2 ){
2589 @ no replies\
2590 }else{
2591 char *zDuration = human_readable_age(db_column_double(&q,0));
2592 @ %d(nMsg) posts spanning %h(zDuration)\
2593 fossil_free(zDuration);
2594 }
2595 @ </td>\
2596 if( bShowStatus ){
2597 @ <td class='status'>%h(zStatusLbl)</td>\
2598 }
2599 if( bHasStatus ){
2600 db_reset(&qStat);
2601 }
2602 @</tr>
2603 fossil_free(zAge);
2604 }
2605 db_finalize(&q);
2606 if( bHasStatus ) db_finalize(&qStat);
2607 }
2608 if( iCnt>0 ){
2609 @ </table></div>
2610 }else{
2611 @ <h1>No forum posts found</h1>
2612 }
2613 style_finish_page();
2614 }
2615

Keyboard Shortcuts

Open search /
Next entry (timeline) j
Previous entry (timeline) k
Open focused entry Enter
Show this help ?
Toggle theme Top nav button