Fossil

Check-in [ef7f85ee]
Login

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

Overview
Comment:Adjust the /forum page so that it for users without forum moderator privilege, it pretends that unmoderated posts do not exist, and for user with moderator privilege it paints an "Awaiting Moderator Approval" message on posts that need moderation.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:ef7f85eecd9605921bd0aad9087022e87ab0936a92561a2dd19a737ed886dba7
User & Date: drh 2018-08-16 16:13:04
Context
2018-08-16
16:26
Update the built-in SQLite to the latest trunk version which includes the fix for EXPLAIN QUERY PLAN for the query that runs the /forum page. check-in: f8994f89 user: drh tags: trunk
16:13
Adjust the /forum page so that it for users without forum moderator privilege, it pretends that unmoderated posts do not exist, and for user with moderator privilege it paints an "Awaiting Moderator Approval" message on posts that need moderation. check-in: ef7f85ee user: drh tags: trunk
12:31
Fix the /forum page so that it links to the most recent post. check-in: b0ae37c0 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/forum.c.

933
934
935
936
937
938
939
940

941
942
943
944
945
946
947
948
949
950
951
952


953














954
955
956
957
958
959
960
...
982
983
984
985
986
987
988





989
990
991
992
993
994
995
996
997
998
999
1000
    style_footer();
    return;
  }
  iLimit = atoi(PD("n","25"));
  iOfst = atoi(PD("x","0"));
  iCnt = 0;
  if( db_table_exists("repository","forumpost") ){
     db_prepare(&q,

      "SELECT"
      "  julianday('now') - max(fmtime) AS a,"                       /* 0 */
      "  max(fmtime) - min(fmtime) AS b,"                            /* 1 */
      "  sum(fprev IS NULL) AS c,"                                   /* 2 */
      "  (SELECT substr(uuid,1,10) FROM blob, forumpost AS z"        /* 3 */
      "    WHERE rid=z.fpid AND z.froot=x.froot"
      "    ORDER BY z.fmtime DESC LIMIT 1),"
      "  (SELECT substr(comment,instr(comment,':')+2)"               /* 4 */
      "     FROM event WHERE objid=(SELECT fpid FROM forumpost AS y"
      "                              WHERE y.froot=x.froot"
      "                              ORDER BY fmtime DESC LIMIT 1))"
      " FROM forumpost AS x"


      " GROUP BY froot ORDER BY 1 LIMIT %d OFFSET %d;",














      iLimit+1, iOfst
    );
    while( db_step(&q)==SQLITE_ROW ){
      char *zAge = human_readable_age(db_column_double(&q,0));
      int nMsg = db_column_int(&q, 2);
      const char *zUuid = db_column_text(&q, 3);
      const char *zTitle = db_column_text(&q, 4);
................................................................................
        @ %z(href("%R/forum?x=%d&n=%d",iOfst+iLimit,iLimit))\
        @ &darr; Older...</a></td></tr>
        fossil_free(zAge);
        break;
      }
      @ <tr><td>%h(zAge) ago</td>
      @ <td>%z(href("%R/forumpost/%S",zUuid))%h(zTitle)</a></td>





      if( nMsg<2 ){
        @ <td>no replies</td>
      }else{
        char *zDuration = human_readable_age(db_column_double(&q,1));
        @ <td>%d(nMsg) posts spanning %h(zDuration)</td>
        fossil_free(zDuration);
      }
      @ </tr>
      fossil_free(zAge);
    }
    db_finalize(&q);
  }







|
>
|
|
|
|
<
|
<
<
|
|
|
|
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







>
>
>
>
>

|


|







933
934
935
936
937
938
939
940
941
942
943
944
945

946


947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
...
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
    style_footer();
    return;
  }
  iLimit = atoi(PD("n","25"));
  iOfst = atoi(PD("x","0"));
  iCnt = 0;
  if( db_table_exists("repository","forumpost") ){
    db_prepare(&q,
      "WITH thread(age,duration,cnt,root,last) AS ("
      "  SELECT"
      "    julianday('now') - max(fmtime),"
      "    max(fmtime) - min(fmtime),"
      "    sum(fprev IS NULL),"

      "    froot,"


      "    (SELECT fpid FROM forumpost AS y"
      "      WHERE y.froot=x.froot %s"
      "      ORDER BY y.fmtime DESC LIMIT 1)"
      "  FROM forumpost AS x"
      "  WHERE %s"
      "  GROUP BY froot"
      "  ORDER BY 1 LIMIT %d OFFSET %d"
      ")"
      "SELECT"
      "  thread.age,"                                         /* 0 */
      "  thread.duration,"                                    /* 1 */
      "  thread.cnt,"                                         /* 2 */
      "  blob.uuid,"                                          /* 3 */
      "  substr(event.comment,instr(event.comment,':')+1),"   /* 4 */
      "  thread.last"                                         /* 5 */
      " FROM thread, blob, event"
      " WHERE blob.rid=thread.last"
      "  AND event.objid=thread.last"
      " ORDER BY 1;",
      g.perm.ModForum ? "" : "AND y.fpid NOT IN private" /*safe-for-%s*/,
      g.perm.ModForum ? "true" : "fpid NOT IN private" /*safe-for-%s*/,
      iLimit+1, iOfst
    );
    while( db_step(&q)==SQLITE_ROW ){
      char *zAge = human_readable_age(db_column_double(&q,0));
      int nMsg = db_column_int(&q, 2);
      const char *zUuid = db_column_text(&q, 3);
      const char *zTitle = db_column_text(&q, 4);
................................................................................
        @ %z(href("%R/forum?x=%d&n=%d",iOfst+iLimit,iLimit))\
        @ &darr; Older...</a></td></tr>
        fossil_free(zAge);
        break;
      }
      @ <tr><td>%h(zAge) ago</td>
      @ <td>%z(href("%R/forumpost/%S",zUuid))%h(zTitle)</a></td>
      @ <td>\
      if( g.perm.ModForum && moderation_pending(db_column_int(&q,5)) ){
        @ <span class="modpending">\
        @ Awaiting Moderator Approval</span><br>
      }
      if( nMsg<2 ){
        @ no replies</td>
      }else{
        char *zDuration = human_readable_age(db_column_double(&q,1));
        @ %d(nMsg) posts spanning %h(zDuration)</td>
        fossil_free(zDuration);
      }
      @ </tr>
      fossil_free(zAge);
    }
    db_finalize(&q);
  }

Changes to src/schema.c.

560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
@ CREATE TABLE repository.forumpost(
@   fpid INTEGER PRIMARY KEY,  -- BLOB.rid for the artifact
@   froot INT,                 -- fpid of the thread root
@   fprev INT,                 -- Previous version of this same post
@   firt INT,                  -- This post is in-reply-to
@   fmtime REAL                -- When posted.  Julian day
@ );
@ CREATE INDEX repository.forumthread ON forumpost(froot);
;

/* Create the forum-post schema if it does not already exist */
void schema_forum(void){
  if( !db_table_exists("repository","forumpost") ){
    db_multi_exec("%s",zForumSchema/*safe-for-%s*/);
  }
}







|








560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
@ CREATE TABLE repository.forumpost(
@   fpid INTEGER PRIMARY KEY,  -- BLOB.rid for the artifact
@   froot INT,                 -- fpid of the thread root
@   fprev INT,                 -- Previous version of this same post
@   firt INT,                  -- This post is in-reply-to
@   fmtime REAL                -- When posted.  Julian day
@ );
@ CREATE INDEX repository.forumthread ON forumpost(froot,fmtime);
;

/* Create the forum-post schema if it does not already exist */
void schema_forum(void){
  if( !db_table_exists("repository","forumpost") ){
    db_multi_exec("%s",zForumSchema/*safe-for-%s*/);
  }
}