Fossil SCM

An attempt to get the /forum page running faster. Some functionality is removed in the process. Work in progress.

drh 2026-05-27 12:25 UTC forum-statuses
Commit 4235b68b93e2517f613a4ae7079f3732d252ae96e141c0869dc1a90df0907700
Parent d9b2025741fd828…
1 file changed +52 -73
~ src/forum.c
M src/forum.c
+52 -73
--- src/forum.c
+++ src/forum.c
@@ -99,24 +99,26 @@
9999
 ** usage may be slightly simplified if we always have a non-empty
100100
 ** list. A length-1 list is, for purposes of the UI, identical to
101101
 ** an empty one - status selection/filtering makes no sense if
102102
 ** there's only one choice. */
103103
 db_multi_exec(
104
- "CREATE TEMP TABLE forumstatus("
105
- " ord INTEGER PRIMARY KEY AUTOINCREMENT,"
104
+ "CREATE TEMP TABLE IF NOT EXISTS forumstatus("
105
+ " ord INTEGER PRIMARY KEY,"
106106
 " label, value, descr"
107107
 ");"
108
+ "DELETE FROM forumstatus;"
108109
 "INSERT INTO forumstatus(label,value,descr)"
109110
 " WITH setting(v) AS ("
110111
 " SELECT value v FROM config WHERE name='forum-statuses'"
111112
 " ),"
112113
 " room(r) AS ("
113114
 " SELECT e.value FROM setting s, jsonb_each(s.v) e"
114115
 " WHERE json_valid(s.v, 0x02)"
115116
 " )"
116117
 " SELECT r->>'label', r->>'value', r->>'description'"
117
- " FROM room"
118
+ " FROM room;"
119
+/* "INSERT OR IGNORE INTO forumstatus(1,'Open','open','Open');" */
118120
 );
119121
 fses.n = (unsigned)db_int(0, "SELECT count(*) FROM forumstatus");
120122
 if( fses.n ){
121123
 int i = 0;
122124
 Stmt q;
@@ -2462,14 +2464,10 @@
24622464
 isSearch ? " Search Results" : "");
24632465
 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
24642466
 if( g.perm.WrForum ){
24652467
 style_submenu_element("New Thread","%R/forumnew");
24662468
 }else{
2467
- /* Can't combine this with previous case using the ternary operator
2468
- * because that causes an error yelling about "non-constant format"
2469
- * with some compilers. I can't see it, since both expressions have
2470
- * the same format, but I'm no C spec lawyer. */
24712469
 style_submenu_element("New Thread","%R/login");
24722470
 }
24732471
 if( g.perm.ModForum && moderation_needed() ){
24742472
 style_submenu_element("Moderation Requests", "%R/modreq");
24752473
 }
@@ -2502,78 +2500,59 @@
25022500
 zStatusFilter = 0;
25032501
 }
25042502
 if( db_table_exists("repository","forumpost") ){
25052503
 db_prepare(&q,
25062504
 "WITH "
2507
- "root(id,pinned,status,statlbl) AS ("
2508
- /* FIXME: the status/statlbl columns are running the same query
2509
- ** to get two adjacent columns. Certainly this query can be
2510
- ** restructured to avoid the duplicated lookup? */
2511
- " SELECT froot id,"
2512
- " (SELECT 1 FROM tagxref ref, tag t"
2513
- " WHERE ref.rid=x.fpid AND ref.tagtype>0"
2514
- " AND ref.tagid=t.tagid"
2515
- " AND t.tagname='pinned') pinned,"
2516
- /* Status value: */
2517
- " CASE WHEN %d /*bHasStatus*/ THEN coalesce("
2518
- " (SELECT ref.value FROM tagxref ref, tag t, forumstatus fs"
2519
- " WHERE ref.rid=x.froot AND ref.tagtype>0"
2520
- " AND ref.tagid=t.tagid"
2521
- " AND t.tagname='status'"
2522
- " AND ref.value=fs.value"
2523
- " ORDER BY ref.mtime desc"
2524
- " ),"
2525
- " (SELECT value FROM forumstatus WHERE ord=1)"
2526
- " ) ELSE NULL END status,"
2527
- /* Status label: */
2528
- " CASE WHEN %d /*bHasStatus*/ THEN coalesce("
2529
- " (SELECT fs.label FROM tagxref ref, tag t, forumstatus fs"
2530
- " WHERE ref.rid=x.froot AND ref.tagtype>0"
2531
- " AND ref.tagid=t.tagid"
2532
- " AND t.tagname='status'"
2533
- " AND ref.value=fs.value"
2534
- " ORDER BY ref.mtime desc"
2535
- " ),"
2536
- " (SELECT label FROM forumstatus WHERE ord=1)"
2537
- " ) ELSE NULL END statlbl"
2538
- " FROM forumpost x WHERE firt IS NULL" /*??? AND fprev IS NULL*/
2539
- "),"
2540
- " thread(age,duration,cnt,root,last,pinned,status,statlbl)"
2541
- " AS ("
2542
- " SELECT"
2543
- " julianday('now') - max(fmtime),"
2544
- " max(fmtime) - min(fmtime),"
2545
- " sum(fprev IS NULL),"
2546
- " root.id,"
2547
- " (SELECT fpid FROM forumpost AS y"
2548
- " WHERE y.froot=root.id %s"
2549
- " ORDER BY y.fmtime DESC LIMIT 1),"
2550
- " root.pinned, root.status, root.statlbl"
2551
- " FROM forumpost, root"
2552
- " WHERE root.id=froot AND %s/*ModForum*/"
2553
- " AND CASE WHEN %d/*status filter*/ THEN root.status=%Q ELSE 1 END"
2554
- " GROUP BY froot"
2555
- " ORDER BY 6 DESC, 1 LIMIT %d OFFSET %d"
2556
- ")"
2557
- "SELECT"
2558
- " thread.age," /* 0 */
2559
- " thread.duration," /* 1 */
2560
- " thread.cnt," /* 2 */
2561
- " blob.uuid," /* 3 */
2562
- " substr(event.comment,instr(event.comment,':')+1)," /* 4 */
2563
- " thread.last," /* 5 */
2564
- " thread.pinned," /* 6 */
2565
- " thread.status," /* 7 */
2566
- " thread.statlbl" /* 8 */
2567
- " FROM thread, blob, event"
2568
- " WHERE blob.rid=thread.last"
2569
- " AND event.objid=thread.last"
2570
- " ORDER BY 7/*pinned*/ DESC, 1;",
2571
- bHasStatus, bHasStatus,
2505
+ "pinned(pinnedid) AS MATERIALIZED (\n"
2506
+ " SELECT DISTINCT tagxref.rid\n"
2507
+ " FROM tag, tagxref\n"
2508
+ " WHERE tag.tagname='pinned'\n"
2509
+ " AND tagxref.tagid=tag.tagid\n"
2510
+ " AND tagxref.tagtype>=1\n"
2511
+ "),\n"
2512
+ "thread(age,duration,cnt,root,last,pinned) AS (\n"
2513
+ " SELECT\n"
2514
+ " julianday('now') - max(fmtime),\n"
2515
+ " max(fmtime) - min(fmtime),\n"
2516
+ " sum(fprev IS NULL),\n"
2517
+ " froot,\n"
2518
+ " (SELECT fpid FROM forumpost AS y\n"
2519
+ " WHERE y.froot=x.froot %s\n"
2520
+ " ORDER BY y.fmtime DESC LIMIT 1),\n"
2521
+ " froot IN pinned\n"
2522
+ " FROM forumpost AS x\n"
2523
+ " WHERE firt IS NULL AND %s/*ModForum*/\n"
2524
+ " GROUP BY froot\n"
2525
+ " ORDER BY 6 DESC, 1 LIMIT %d OFFSET %d\n"
2526
+ ")\n"
2527
+ "SELECT\n"
2528
+ " thread.age,\n" /* 0 */
2529
+ " thread.duration,\n" /* 1 */
2530
+ " thread.cnt,\n" /* 2 */
2531
+ " blob.uuid,\n" /* 3 */
2532
+ " substr(event.comment,instr(event.comment,':')+1),\n" /* 4 */
2533
+ " thread.last,\n" /* 5 */
2534
+ " thread.pinned,\n" /* 6 */
2535
+ " (SELECT coalesce(fs.value,dfs.value)\n"
2536
+ " FROM tag, tagxref, forumstatus fs\n"
2537
+ " WHERE tag.tagname='status'\n"
2538
+ " AND tagxref.tagid=tag.tagid\n"
2539
+ " AND tagxref.tagtype>=1\n"
2540
+ " AND fs.value=tagxref.value)," /* 7 */
2541
+ " (SELECT coalesce(fs.label,dfs.label)\n"
2542
+ " FROM tag, tagxref, forumstatus fs\n"
2543
+ " WHERE tag.tagname='status'\n"
2544
+ " AND tagxref.tagid=tag.tagid\n"
2545
+ " AND tagxref.tagtype>=1\n"
2546
+ " AND fs.value=tagxref.value)" /* 8 */
2547
+ " FROM thread, blob, event\n"
2548
+ " LEFT JOIN forumstatus AS dfs ON (dfs.ord=1)\n"
2549
+ " WHERE blob.rid=thread.last\n"
2550
+ " AND event.objid=thread.last\n"
2551
+ " ORDER BY 7/*pinned*/ DESC, 1;",
25722552
 g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
25732553
 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
2574
- !!zStatusFilter, zStatusFilter,
25752554
 iLimit+1, iOfst
25762555
 );
25772556
 while( db_step(&q)==SQLITE_ROW ){
25782557
 char *zAge = human_readable_age(db_column_double(&q,0));
25792558
 int nMsg = db_column_int(&q, 2);
25802559
--- src/forum.c
+++ src/forum.c
@@ -99,24 +99,26 @@
99 ** usage may be slightly simplified if we always have a non-empty
100 ** list. A length-1 list is, for purposes of the UI, identical to
101 ** an empty one - status selection/filtering makes no sense if
102 ** there's only one choice. */
103 db_multi_exec(
104 "CREATE TEMP TABLE forumstatus("
105 " ord INTEGER PRIMARY KEY AUTOINCREMENT,"
106 " label, value, descr"
107 ");"
 
108 "INSERT INTO forumstatus(label,value,descr)"
109 " WITH setting(v) AS ("
110 " SELECT value v FROM config WHERE name='forum-statuses'"
111 " ),"
112 " room(r) AS ("
113 " SELECT e.value FROM setting s, jsonb_each(s.v) e"
114 " WHERE json_valid(s.v, 0x02)"
115 " )"
116 " SELECT r->>'label', r->>'value', r->>'description'"
117 " FROM room"
 
118 );
119 fses.n = (unsigned)db_int(0, "SELECT count(*) FROM forumstatus");
120 if( fses.n ){
121 int i = 0;
122 Stmt q;
@@ -2462,14 +2464,10 @@
2462 isSearch ? " Search Results" : "");
2463 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
2464 if( g.perm.WrForum ){
2465 style_submenu_element("New Thread","%R/forumnew");
2466 }else{
2467 /* Can't combine this with previous case using the ternary operator
2468 * because that causes an error yelling about "non-constant format"
2469 * with some compilers. I can't see it, since both expressions have
2470 * the same format, but I'm no C spec lawyer. */
2471 style_submenu_element("New Thread","%R/login");
2472 }
2473 if( g.perm.ModForum && moderation_needed() ){
2474 style_submenu_element("Moderation Requests", "%R/modreq");
2475 }
@@ -2502,78 +2500,59 @@
2502 zStatusFilter = 0;
2503 }
2504 if( db_table_exists("repository","forumpost") ){
2505 db_prepare(&q,
2506 "WITH "
2507 "root(id,pinned,status,statlbl) AS ("
2508 /* FIXME: the status/statlbl columns are running the same query
2509 ** to get two adjacent columns. Certainly this query can be
2510 ** restructured to avoid the duplicated lookup? */
2511 " SELECT froot id,"
2512 " (SELECT 1 FROM tagxref ref, tag t"
2513 " WHERE ref.rid=x.fpid AND ref.tagtype>0"
2514 " AND ref.tagid=t.tagid"
2515 " AND t.tagname='pinned') pinned,"
2516 /* Status value: */
2517 " CASE WHEN %d /*bHasStatus*/ THEN coalesce("
2518 " (SELECT ref.value FROM tagxref ref, tag t, forumstatus fs"
2519 " WHERE ref.rid=x.froot AND ref.tagtype>0"
2520 " AND ref.tagid=t.tagid"
2521 " AND t.tagname='status'"
2522 " AND ref.value=fs.value"
2523 " ORDER BY ref.mtime desc"
2524 " ),"
2525 " (SELECT value FROM forumstatus WHERE ord=1)"
2526 " ) ELSE NULL END status,"
2527 /* Status label: */
2528 " CASE WHEN %d /*bHasStatus*/ THEN coalesce("
2529 " (SELECT fs.label FROM tagxref ref, tag t, forumstatus fs"
2530 " WHERE ref.rid=x.froot AND ref.tagtype>0"
2531 " AND ref.tagid=t.tagid"
2532 " AND t.tagname='status'"
2533 " AND ref.value=fs.value"
2534 " ORDER BY ref.mtime desc"
2535 " ),"
2536 " (SELECT label FROM forumstatus WHERE ord=1)"
2537 " ) ELSE NULL END statlbl"
2538 " FROM forumpost x WHERE firt IS NULL" /*??? AND fprev IS NULL*/
2539 "),"
2540 " thread(age,duration,cnt,root,last,pinned,status,statlbl)"
2541 " AS ("
2542 " SELECT"
2543 " julianday('now') - max(fmtime),"
2544 " max(fmtime) - min(fmtime),"
2545 " sum(fprev IS NULL),"
2546 " root.id,"
2547 " (SELECT fpid FROM forumpost AS y"
2548 " WHERE y.froot=root.id %s"
2549 " ORDER BY y.fmtime DESC LIMIT 1),"
2550 " root.pinned, root.status, root.statlbl"
2551 " FROM forumpost, root"
2552 " WHERE root.id=froot AND %s/*ModForum*/"
2553 " AND CASE WHEN %d/*status filter*/ THEN root.status=%Q ELSE 1 END"
2554 " GROUP BY froot"
2555 " ORDER BY 6 DESC, 1 LIMIT %d OFFSET %d"
2556 ")"
2557 "SELECT"
2558 " thread.age," /* 0 */
2559 " thread.duration," /* 1 */
2560 " thread.cnt," /* 2 */
2561 " blob.uuid," /* 3 */
2562 " substr(event.comment,instr(event.comment,':')+1)," /* 4 */
2563 " thread.last," /* 5 */
2564 " thread.pinned," /* 6 */
2565 " thread.status," /* 7 */
2566 " thread.statlbl" /* 8 */
2567 " FROM thread, blob, event"
2568 " WHERE blob.rid=thread.last"
2569 " AND event.objid=thread.last"
2570 " ORDER BY 7/*pinned*/ DESC, 1;",
2571 bHasStatus, bHasStatus,
2572 g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
2573 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
2574 !!zStatusFilter, zStatusFilter,
2575 iLimit+1, iOfst
2576 );
2577 while( db_step(&q)==SQLITE_ROW ){
2578 char *zAge = human_readable_age(db_column_double(&q,0));
2579 int nMsg = db_column_int(&q, 2);
2580
--- src/forum.c
+++ src/forum.c
@@ -99,24 +99,26 @@
99 ** usage may be slightly simplified if we always have a non-empty
100 ** list. A length-1 list is, for purposes of the UI, identical to
101 ** an empty one - status selection/filtering makes no sense if
102 ** there's only one choice. */
103 db_multi_exec(
104 "CREATE TEMP TABLE IF NOT EXISTS forumstatus("
105 " ord INTEGER PRIMARY KEY,"
106 " label, value, descr"
107 ");"
108 "DELETE FROM forumstatus;"
109 "INSERT INTO forumstatus(label,value,descr)"
110 " WITH setting(v) AS ("
111 " SELECT value v FROM config WHERE name='forum-statuses'"
112 " ),"
113 " room(r) AS ("
114 " SELECT e.value FROM setting s, jsonb_each(s.v) e"
115 " WHERE json_valid(s.v, 0x02)"
116 " )"
117 " SELECT r->>'label', r->>'value', r->>'description'"
118 " FROM room;"
119 /* "INSERT OR IGNORE INTO forumstatus(1,'Open','open','Open');" */
120 );
121 fses.n = (unsigned)db_int(0, "SELECT count(*) FROM forumstatus");
122 if( fses.n ){
123 int i = 0;
124 Stmt q;
@@ -2462,14 +2464,10 @@
2464 isSearch ? " Search Results" : "");
2465 style_submenu_element("Timeline", "%R/timeline?ss=v&y=f&vfx");
2466 if( g.perm.WrForum ){
2467 style_submenu_element("New Thread","%R/forumnew");
2468 }else{
 
 
 
 
2469 style_submenu_element("New Thread","%R/login");
2470 }
2471 if( g.perm.ModForum && moderation_needed() ){
2472 style_submenu_element("Moderation Requests", "%R/modreq");
2473 }
@@ -2502,78 +2500,59 @@
2500 zStatusFilter = 0;
2501 }
2502 if( db_table_exists("repository","forumpost") ){
2503 db_prepare(&q,
2504 "WITH "
2505 "pinned(pinnedid) AS MATERIALIZED (\n"
2506 " SELECT DISTINCT tagxref.rid\n"
2507 " FROM tag, tagxref\n"
2508 " WHERE tag.tagname='pinned'\n"
2509 " AND tagxref.tagid=tag.tagid\n"
2510 " AND tagxref.tagtype>=1\n"
2511 "),\n"
2512 "thread(age,duration,cnt,root,last,pinned) AS (\n"
2513 " SELECT\n"
2514 " julianday('now') - max(fmtime),\n"
2515 " max(fmtime) - min(fmtime),\n"
2516 " sum(fprev IS NULL),\n"
2517 " froot,\n"
2518 " (SELECT fpid FROM forumpost AS y\n"
2519 " WHERE y.froot=x.froot %s\n"
2520 " ORDER BY y.fmtime DESC LIMIT 1),\n"
2521 " froot IN pinned\n"
2522 " FROM forumpost AS x\n"
2523 " WHERE firt IS NULL AND %s/*ModForum*/\n"
2524 " GROUP BY froot\n"
2525 " ORDER BY 6 DESC, 1 LIMIT %d OFFSET %d\n"
2526 ")\n"
2527 "SELECT\n"
2528 " thread.age,\n" /* 0 */
2529 " thread.duration,\n" /* 1 */
2530 " thread.cnt,\n" /* 2 */
2531 " blob.uuid,\n" /* 3 */
2532 " substr(event.comment,instr(event.comment,':')+1),\n" /* 4 */
2533 " thread.last,\n" /* 5 */
2534 " thread.pinned,\n" /* 6 */
2535 " (SELECT coalesce(fs.value,dfs.value)\n"
2536 " FROM tag, tagxref, forumstatus fs\n"
2537 " WHERE tag.tagname='status'\n"
2538 " AND tagxref.tagid=tag.tagid\n"
2539 " AND tagxref.tagtype>=1\n"
2540 " AND fs.value=tagxref.value)," /* 7 */
2541 " (SELECT coalesce(fs.label,dfs.label)\n"
2542 " FROM tag, tagxref, forumstatus fs\n"
2543 " WHERE tag.tagname='status'\n"
2544 " AND tagxref.tagid=tag.tagid\n"
2545 " AND tagxref.tagtype>=1\n"
2546 " AND fs.value=tagxref.value)" /* 8 */
2547 " FROM thread, blob, event\n"
2548 " LEFT JOIN forumstatus AS dfs ON (dfs.ord=1)\n"
2549 " WHERE blob.rid=thread.last\n"
2550 " AND event.objid=thread.last\n"
2551 " ORDER BY 7/*pinned*/ DESC, 1;",
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
2552 g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
2553 g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
 
2554 iLimit+1, iOfst
2555 );
2556 while( db_step(&q)==SQLITE_ROW ){
2557 char *zAge = human_readable_age(db_column_double(&q,0));
2558 int nMsg = db_column_int(&q, 2);
2559

Keyboard Shortcuts

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