Threaded BBoard Design

Threaded BBoard Design Documentation

part of the ArsDigita Community System by Tzu-Mainn Chen

I. Essentials

User-Accessible Directory: /threaded-bboard/
Group Administrator Directory: /threaded-bboard/admin/
Site Administrator Directory: /admin/threaded-bboard/
Data Model: gc-bboard.sql
Tcl Procedures: gc-bboard-procs.tcl, gc-bboard-email-procs.tcl
Requirements Document: /arsdigita/doc/requirements/threadedbboard.html
Page Flow: /arsdigita/doc/page-flow/threadedbboard_interaction_admin and /arsdigita/doc/page-flow/threadedbboard_interaction_users
ER Diagram: /arsdigita/doc/er-diagrams/small_threaded_bboards_er

II. Introduction

At arsDigita, one of the more frequent requests is for a threaded bulletin board. ACS 4.0 will contain an updated bboard that will meet current requirements; however, it has not yet been released, and many clients want a threaded bboard now.

When this issue came up for USLaw, Michael Yoon proposed a quick-and-dirty solution: implement bboard functionality using the general-comments application (hence gc-bboard), thus making development simpler and more supportable by pre-existing ACS code.

III. Historical Considerations

The ACS 3.x bboard package was one of the first applications written for the ACS; as a result, its implementation is unwieldy and over-sized, containing items such as a permissions model within its code (more recent applications use general-permissions). In addition, the ACS 3.x bboard package has limited support for threaded discussions.

IV. Design Tradeoffs

The threaded-bboard package was developed in order to solve both of these problems. By using general-comments, the package is able to use pre-existing data models and code; general-comments also allows for a tree-like structure of comments, which makes it ideal for threading purposes.

V. Data Model Discussion

The data model is not extensive, as the major chunk is pre-written in general comments. The one addition to general_comments is a boolean intended to mark a comment as part of a bboard or not. This allows for easier querying.


alter table general_comments add (bboard_p char(1) default 'f' check(bboard_p in ('t','f')));

create index gc_comment_date_idx on general_comments(comment_date);
create index gc_show_p_idx on general_comments(bboard_p, approved_p);
create index gc_comment_on_what_ids_idx on general_comments(comment_id, on_what_id);

create or replace view gc_bboard_messages
as select * from general_comments where bboard_p = 't';

The major new table is gc_bboard_topics:


-- NEW TABLES

create sequence gc_bboard_topics_id_seq;

create table gc_bboard_topics (
	topic_id   	integer primary key,
	name		varchar(100) unique not null,
	active_p 	char(1) default 'f' check(active_p in ('t','f')),
	max_bboard_display_depth integer default 2,
	max_bboard_display_messages integer default 10,
	creation_user	references users,
	creation_date   date default sysdate,
	creation_ip	varchar(50),
	group_id	references user_groups,
	auto_subscribe_p char(1) default 'f' check (auto_subscribe_p in ('t', 'f'))
);

-- denormalization to make the topic of each comment easily accessible
create table gc_comment_topic_map (
	comment_id	references general_comments,
	topic_id	references gc_bboard_topics,
	primary key(comment_id, topic_id)
);

The few remaining tables are for alerts:



-- when a bboard entry is inserted, an insertion is done into this table as
-- well, so we know if a bboard entry needs to be processed for alerts
create sequence gc_bboard_alert_id_seq;
create table gc_bboard_alerts (
	alert_id	integer 
			constraint gc_bboard_alert_id_pk
			primary key,
	--didn't specify it to be unique since message might be edited
	comment_id	references general_comments not null,
	--if date_sent is null, that means not sent yet
	--check to see if approve before sending
	date_sent	date
);

--  shows who should get an email for that message; entries are deleted once
-- an email is sent
create table gc_comment_user_recipients (
	comment_id	references general_comments,
	user_id		references users
);

--gc_topic_user_map shows who is subscribed to a topic
create table gc_topic_user_map (
	topic_id	references gc_bboard_topics,
	user_id		references users,
	primary key (topic_id, user_id)
);

--gc_comment_user_map shows who is subscribed to a thread
create table gc_comment_user_map (
	comment_id	references general_comments,
	user_id		references users,
	primary key (comment_id, user_id)
);

--gc_contributor_user_map shows who is subscribed to an author
create table gc_contributor_user_map (
	contributor_id  references users,
	user_id		references users,
	primary key (contributor_id, user_id)
);

--gc_keyword_user_map shows who is subscribe to a keyword
create table gc_keyword_user_map (
	keyword         varchar(100) not null,
	user_id 	references users,
	primary key (keyword, user_id)
);

VI. Legal Transactions

Site Administrators

Site administrators may perform group administrator functions for every bulletin board topic.

Group Administrators

Group administrators can create public or private group bulletin boards. They can also edit messages, toggle message approval, delete message branches, manage various display and alert properties, and administer bulletin board permissions using general_permissions.

Users

Once they are registered, users can post messages in public bulletin boards, or in any group bulletin board of which they are members. They can also sign up for various alerts, based on topic, author, thread, or keyword.

VII. API

There are two essential procedures :

gc_bboard_message_add - gc_bboard_message_add inserts a message into the system, using ad_general_comment_add; it also takes the exact same arguments, plus topic_id and an optional subscribe_p (for alerts; more on that later).
gc_bboard_message_list - gc_bboard_message_list returns a Tcl list-of-lists of bboard message information in an order intended to allow for easy processing into HTML: comment_id, one_line_item_desc, c_date, gq.user_id, the_level, creation_user, email, approved_p, content, n_messages, n_new_messages. gc_bboard_message_list takes thes following arguments:
- on_which_table - a varchar that, along with on_what_id, will be matched in general_comments to determine which bboard node to start at. Typical values are gc_bboard_topics (to process an entire topic) and general_comments (to receive a subtree of a topic tree).
- on_what_id - used with on_which_table to select the starting point of the query.
- count_messages_p - a boolean that instructs the procedure to count messages and new messages (inserted within the last 24 hours) branching off from each selected node.
- approved_p - a boolean that instructs the procedure to select approved, unapproved, or all messages.
- max_bboard_display_depth - an integer that tells the procedure what depth of messages to look for; it defaults to returning the whole tree.
- max_bboard_display_messages - an integer that tells the procedure the maximum number of messages to return.
- reverse_p - a boolean; if false, then the procedure returns the typica message tree, starting from the selected node. If true, then the procedure takes the node and goes in reverse, returning a list of messages going back to the root of the message tree.

There are a few minor procedures and scheduled procs:

gc_send_bboard_alerts - sends alerts to users who are subscribed. A group bboard has to option to autosubscribe new members - this is toggled in the bboard admin page.
gc_bboard_email_process - located in gc_bboard_email_procs.tcl This procedures allows the feature where users can reply to a bboard posting by sending an email. The process is exactly like that which is used for the ticket tracker (in fact, gc_bboard_email_process is directly modified from ticket_email_process in ticket-email.tcl). Thus, to set this feature up, you need to:
- make sure that email-handler.tcl exists somewhere in a Tcl directory
- make sure that queue-message.pl and q.pl exist in /web/yourservice_name/bin/
- set up a qmail alias - for sloan, the alias file name was .qmail-sloanspace-bboard-robot-default, containing the line |/web/sloan/bin/queue-message.pl dbi:Oracle: sloan sloanrules gc_bboard
- add parameters in the .ini file:
```
[ns/server/yourservername/acs/email-queue]
.
.
.
DispatchPair=gc_bboard|gc_bboard_email_process

[ns/server/yoursevername/acs/gc-bboard]
.
.
.
AlertEmailDomain=some email domain
AlertEmailName=some email name (for sloan, it was sloanspace-bboard-robot; this needs to match your qmail alias)
```
Since there are likely to be many, many problems along the way, here are some useful links:
- email-handler - ACS documentation for the email handler.
- Cave Man's Guide to Understanding Qmail Administration - written by Jin Choi.

VIII. Configuration/Parameters

None other than those described in Section VII.

IX. Future Changes

Add more parameters.
Permissioning isn't always throughly checked on some smaller pages (subscription, filter, etc).
Make sure permissioning is done through general_permissions, and not through scope
Right now, filtering is done on subject only; get Intermedia to work so that it can be done on content as well.
Ability to rate bboard messages, on site or through email. Filter and feature messages based on rating.
Ability to subscribe to digest version of messages.
gc_bboard_email_process is simply a copy of ticket_email_process; a generic procedure for both would be nice.
When admins edit other people's posts, an email should be sent to the original author. Should have increased auditing of comments.

tzumainn@arsdigita.com