Index: openacs-4/packages/forums/sql/postgresql/forums-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,24 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert's UBB forums. Thanks Orchard Labs. +-- + +-- The basic forum constructs +\i forums-forums-create.sql +\i forums-forums-package-create.sql + +-- The messages +\i forums-messages-create.sql +\i forums-messages-package-create.sql + +-- tree stuff +\i forums-tree-create.sql + +-- notifications +\i forums-notifications-init.sql Index: openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,99 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs. +-- + +-- privileges +begin + -- moderate and post are new privileges + -- the rest are obvious inheritance + -- forum creation on a package allows a user to create forums + -- forum creation on a forum allows a user to create new threads + select acs_privilege__create_privilege('forum_create',null,null); + select acs_privilege__create_privilege('forum_write',null,null); + select acs_privilege__create_privilege('forum_delete',null,null); + select acs_privilege__create_privilege('forum_read',null,null); + select acs_privilege__create_privilege('forum_post',null,null); + select acs_privilege__create_privilege('forum_moderate',null,null); + + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + + -- add children + select acs_privilege__add_child('create','forum_create'); + select acs_privilege__add_child('write','forum_write'); + select acs_privilege__add_child('delete','forum_delete'); + select acs_privilege__add_child('admin','forum_moderate'); + select acs_privilege__add_child('forum_moderate','forum_read'); + select acs_privilege__add_child('forum_moderate','forum_post'); + select acs_privilege__add_child('forum_write','forum_read'); + select acs_privilege__add_child('forum_write','forum_post'); + + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + + -- the last one that will cause all the updates + select acs_privilege__add_child('read','forum_read'); +end; + + +-- +-- The Data Model +-- + +create table forums_forums ( + forum_id integer not null + constraint forums_forum_id_fk + references acs_objects(object_id) + constraint forums_forum_id_pk + primary key, + name varchar(200) constraint forum_name_nn not null, + charter varchar(2000), + presentation_type varchar(100) + constraint forum_type_nn not null + constraint forum_type_ch + check (presentation_type in ('flat','threaded')), + posting_policy varchar(100) + constraint forum_policy_nn not null + constraint forum_policy_ch + check (posting_policy in ('open','moderated','closed')), + max_child_sortkey varbit, + enabled_p char(1) default 't' not null + constraint forum_enabled_p_ch check + (enabled_p in ('t','f')), + package_id integer constraint forum_package_id_nn not null +); + +create view forums_forums_enabled +as select * from forums_forums where enabled_p='t'; + +-- +-- Object Type +-- + +begin + select acs_object_type__create_type ( + 'forums_forum', + 'Forums Forum', + 'Forums Forums', + 'acs_object', + 'forums_forums', + 'forum_id', + 'forums_forum', + 'f', + NULL, + 'forums_forum__name' + ); +end; Index: openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,59 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs. +-- + +-- privileges +begin + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + + -- remove children + select acs_privilege__remove_child('read','forum_read'); + select acs_privilege__remove_child('create','forum_create'); + select acs_privilege__remove_child('write','forum_write'); + select acs_privilege__remove_child('delete','forum_delete'); + select acs_privilege__remove_child('admin','forum_moderate'); + select acs_privilege__remove_child('forum_moderate','forum_read'); + select acs_privilege__remove_child('forum_moderate','forum_post'); + select acs_privilege__remove_child('forum_write','forum_read'); + + -- reenable for trigger update + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + + select acs_privilege__remove_child('forum_write','forum_post'); + + select acs_privilege__drop_privilege('forum_moderate'); + select acs_privilege__drop_privilege('forum_post'); + select acs_privilege__drop_privilege('forum_read'); + select acs_privilege__drop_privilege('forum_create'); + select acs_privilege__drop_privilege('forum_write'); + select acs_privilege__drop_privilege('forum_delete'); +end; + + +-- +-- The Data Model +-- + +drop table forums_forums; + +-- +-- Object Type +-- + +begin + select acs_object_type__drop_type ( + 'forums_forum', 'f' + ); +end; Index: openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,74 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- The Package +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs! +-- + +select define_function_args('forums_forum__new','forum_id,object_type;forums_forum,name,charter,presentation_type,posting_policy,package_id,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args('forums_forum__name','forum_id'); + +select define_function_args('forums_forum__delete','forum_id'); + + +-- implementation + +create function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_forum_id alias for $1; + p_object_type alias for $2; + p_name alias for $3; + p_charter alias for $4; + p_presentation_type alias for $5; + p_posting_policy alias for $6; + p_package_id alias for $7; + p_creation_date alias for $8; + p_creation_user alias for $9; + p_creation_ip alias for $10; + p_context_id alias for $11; + v_forum_id integer; +BEGIN + v_forum_id:= acs_object__new ( + p_forum_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into forums_forums + (forum_id, name, charter, presentation_type, posting_policy, package_id) values + (v_forum_id, p_name, p_charter, p_presentation_type, p_posting_policy, p_package_id); + + return v_forum_id; +END; +' language 'plpgsql'; + + +create function forums_forum__name(integer) +returns integer as ' +DECLARE + p_forum_id alias for $1; +BEGIN + return name from forums_forums where forum_id= p_forum_id; +END; +' language 'plpgsql'; + + +create function forums_forum__delete(integer) +returns integer as ' +DECLARE + p_forum_id alias for $1; +BEGIN + perform acs_object__delete(p_forum_id); + return 0; +END; +' language 'plpgsql'; Index: openacs-4/packages/forums/sql/postgresql/forums-forums-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-forums-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,20 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- The Package +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs! +-- + + + +drop function forums_forum__new (integer,varchar,varchar,varchar,varchar,varchar,integer,timestamp,integer,varchar,integer); + +drop function forums_forum__name(integer); + +drop function forums_forum__delete(integer); Index: openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,82 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert. Thanks Orchard Labs! +-- + +-- privileges +-- NO PRIVILEGES FOR MESSAGES +-- we don't individually permission messages + +-- +-- The Data Model +-- + +create table forums_messages ( + message_id integer not null + constraint forums_message_id_fk + references acs_objects(object_id) + constraint forums_message_id_pk + primary key, + forum_id integer + constraint forums_mess_forum_id_fk + references forums_forums(forum_id), + subject varchar(200), + content text, + -- html_p only applies to the body. The subject is plaintext. + html_p char(1) default 'f' + constraint forums_mess_html_p_ch + check (html_p in ('t','f')) + constraint forums_mess_html_p_nn not null, + user_id integer + constraint forums_mess_user_id_fk + references users(user_id) + constraint forums_mess_user_id_nn + not null, + posting_date timestamp + constraint forum_mess_post_date_nn not null, + state varchar(100) + constraint forum_mess_state_ch check (state in ('pending','approved','rejected')), + -- Hierarchy of messages + parent_id integer + constraint forum_mess_parent_id_fk + references forums_messages(message_id), + open_p char(1) default 't' not null + constraint forum_mess_open_p_ch check (open_p in ('t','f')), + tree_sortkey varbit, + max_child_sortkey varbit, + constraint forums_mess_sk_forum_un + unique (tree_sortkey,forum_id) +); + +-- views + +create view forums_messages_approved as +select * from forums_messages where state='approved'; + +create view forums_messages_pending as +select * from forums_messages where state='pending'; + +-- +-- Object Type +-- + +begin + select acs_object_type__create_type ( + 'forums_message', + 'Forums Message', + 'Forums Messages', + 'acs_object', + 'forums_messages', + 'message_id', + 'forums_message', + 'f', + NULL, + NULL + ); +end; Index: openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,30 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert. Thanks Orchard Labs! +-- + +-- privileges +-- NO PRIVILEGES FOR MESSAGES +-- we don't individually permission messages + +-- +-- The Data Model +-- + +drop table forums_messages; + +-- +-- Object Type +-- + +begin + select acs_object_type__drop_type ( + 'forums_message', 'f' + ); +end; Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,190 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- The Package for Messages +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs! +-- + +select define_function_args ('forums_message_new', 'message_id,object_type;forums_message,forum_id,subject,content,html_p,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('forums_message__root_message_id', 'message_id'); + +select define_function_args ('forums_message__thread_open', 'message_id'); + +select define_function_args ('forums_message__thread_close', 'message_id'); + +select define_function_args ('forums_message__delete', 'message_id'); + +select define_function_args ('forums_message__delete_thread', 'message_id'); + +-- implementation + +create function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamp,varchar,integer,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_message_id alias for $1; + p_object_type alias for $2; + p_forum_id alias for $3; + p_subject alias for $4; + p_content alias for $5; + p_html_p alias for $6; + p_user_id alias for $7; + p_posting_date alias for $8; + p_state alias for $9; + p_parent_id alias for $10; + p_creation_date alias for $11; + p_creation_user alias for $12; + p_creation_ip alias for $13; + p_context_id alias for $14; + v_message_id integer; + v_forum_policy forums_forums.posting_policy%TYPE; + v_state forums_messages.state%TYPE; +BEGIN + v_message_id:= acs_object__new ( + p_message_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + IF p_state is NULL + then + select posting_policy into v_forum_policy from forums_forums + where forum_id= p_forum_id; + + if v_forum_policy = ''moderated'' + then v_state := ''pending''; + else v_state := ''approved''; + end if; + else + v_state := p_state; + end if; + + insert into forums_messages + (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state) + values + (v_message_id, p_forum_id, p_subject, p_content, p_html_p, p_user_id, p_posting_date, p_parent_id, v_state); + + return v_message_id; +END; +' language 'plpgsql'; + + +create function forums_message__root_message_id (integer) +returns integer as ' +DECLARE + p_message_id alias for $1; + v_message_id forums_messages.message_id%TYPE; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +BEGIN + select forum_id, tree_sortkey into v_forum_id, v_sortkey + from forums_messages where message_id= p_message_id; + + select message_id into v_message_id from forums_messages where forum_id= v_forum_id + and tree_sortkey= tree__ancestor_key(v_sortkey, 1); + + return v_message_id; +END; +' language 'plpgsql'; + + +create function forums_message__thread_open (integer) +returns integer as ' +DECLARE + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +BEGIN + select forum_id, tree_sortkey into v_forum_id, v_sortkey + from forums_messages where message_id= p_message_id; + + update forums_messages set open_p=''t'' + where tree_sortkey between tree__left(v_sortkey) and tree__right(v_sortkey) + and forum_id = v_forum_id; + + update forums_messages set open_p=''t'' + where message_id= p_message_id; + + return 0; +END; +' language 'plpgsql'; + + +create function forums_message__thread_close (integer) +returns integer as ' +DECLARE + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; +BEGIN + select forum_id, tree_sortkey into v_forum_id, v_sortkey + from forums_messages where message_id= p_message_id; + + update forums_messages set open_p=''f'' + where tree_sortkey between tree__left(v_sortkey) and tree__right(v_sortkey) + and forum_id = v_forum_id; + + update forums_messages set open_p=''f'' + where message_id= p_message_id; + + return 0; +END; +' language 'plpgsql'; + + +create function forums_message__delete (integer) +returns integer as ' +DECLARE + p_message_id alias for $1; +BEGIN + perform acs_object__delete(p_message_id); + return 0; +END; +' language 'plpgsql'; + + +create function forums_message__delete_thread (integer) +returns integer as ' +DECLARE + p_message_id alias for $1; + v_forum_id forums_messages.forum_id%TYPE; + v_sortkey forums_messages.tree_sortkey%TYPE; + v_message RECORD; +BEGIN + select forum_id, tree_sortkey into v_forum_id, v_sortkey + from forums_messages where message_id= p_message_id; + + -- if it is already deleted + if NOTFOUND + then return 0; + end if; + + -- delete all children + -- order by tree_sortkey desc to guarantee + -- that we never delete a parent before its child + -- sortkeys are beautiful + FOR v_message in + (select * from forums_messages + where forum_id = v_forum_id and + tree_sortkey between tree__left(v_sortkey) + and tree__right(v_sortkey) order by tree_sortkey desc) + LOOP + perform forums_message__delete(v_message.message_id); + END LOOP; + + -- delete the message itself + perform forums_message.delete(p_message_id); + + return 0; +END; +' language 'plpgsql'; + + Index: openacs-4/packages/forums/sql/postgresql/forums-messages-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-messages-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,26 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- The Package for Messages +-- +-- This code is newly concocted by Ben, but with heavy concepts and heavy code +-- chunks lifted from Gilbert. Thanks Orchard Labs! +-- + +drop function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamp,varchar,integer,timestamp,integer,varchar,integer); + +drop function forums_message__root_message_id (integer); + +drop function forums_message__thread_open (integer); + +drop function forums_message__thread_close (integer); + +drop function forums_message__delete (integer); + +drop function forums_message__delete_thread (integer); + + Index: openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/forums-notifications-init.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,56 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert's UBB forums. Thanks Orchard Labs. +-- + +-- the integration with Notifications +declare + v_foo integer; +begin + v_foo:= notification_type.new ( + short_name => 'forums_forum_notif', + pretty_name => 'Forum Notification', + description => 'Notifications for Entire Forums', + creation_user => NULL, + creation_ip => NULL + ); + + -- enable the various intervals and delivery methods + insert into notification_types_intervals + (type_id, interval_id) + select v_foo, interval_id + from notification_intervals where name in ('instant','hourly','daily'); + + insert into notification_types_del_methods + (type_id, delivery_method_id) + select v_foo, delivery_method_id + from notification_delivery_methods where short_name in ('email'); + + v_foo:= notification_type.new ( + short_name => 'forums_message_notif', + pretty_name => 'Message Notification', + description => 'Notifications for Message Thread', + creation_user => NULL, + creation_ip => NULL + ); + + -- enable the various intervals and delivery methods + insert into notification_types_intervals + (type_id, interval_id) + select v_foo, interval_id + from notification_intervals where name in ('instant','hourly','daily'); + + insert into notification_types_del_methods + (type_id, delivery_method_id) + select v_foo, delivery_method_id + from notification_delivery_methods where short_name in ('email'); + +end; +/ +show errors Index: openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 1 Jun 2002 19:02:29 -0000 1.1 @@ -0,0 +1,60 @@ + +-- +-- The Forums Package +-- +-- @author gwong@orchardlabs.com,ben@openforce.biz +-- @creation-date 2002-05-16 +-- +-- This code is newly concocted by Ben, but with significant concepts and code +-- lifted from Gilbert. Thanks Orchard Labs! +-- + +-- +-- This is the sortkey code +-- + + +create or replace trigger forums_mess_insert_tr +before insert on forums_messages +for each row +declare + v_max_child_sortkey forums_messages.max_child_sortkey%TYPE; + v_parent_sortkey forums_messages.tree_sortkey%TYPE; +begin + if :new.parent_id is NULL + then + -- get the max from the forum + select max_child_sortkey into v_max_child_sortkey + from forums_forums where forum_id= :new.forum_id + for update of max_child_sortkey; + + v_parent_sortkey:= NULL; + else + -- get the max child sortkey from parent + -- grab the lock + select tree_sortkey, max_child_sortkey + into v_parent_sortkey, v_max_child_sortkey + from forums_messages + where message_id= :new.parent_id + for update of max_child_sortkey; + end if; + + -- increment the sortkey + v_max_child_sortkey:= lpad(tree.increment_key(v_max_child_sortkey),6,'0'); + + if :new.parent_id is null + then + update forums_forums set max_child_sortkey= v_max_child_sortkey + where forum_id= :new.forum_id; + else + -- update the parent + update forums_messages set max_child_sortkey= v_max_child_sortkey + where message_id= :new.parent_id; + end if; + + -- generate the current sortkey + :new.tree_sortkey:= v_parent_sortkey || v_max_child_sortkey; + +end forums_mess_insert_tr; +/ +show errors