Index: openacs-4/packages/forums/sql/postgresql/forums-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-drop.sql 12 Jun 2002 15:24:02 -0000 1.1 @@ -0,0 +1,23 @@ +-- +-- 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. +-- + +-- notifications +\i forums-notifications-sanitize.sql + +-- tree stuff +\i forums-tree-drop.sql + +-- the messages +\i forums-messages-package-drop.sql +\i forums-messages-drop.sql + +-- The basic forum constructs +\i forums-forums-package-drop.sql +\i forums-forums-drop.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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-create.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -10,90 +10,109 @@ -- -- privileges +create function inline_0 () +returns integer as ' 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. + -- 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 + perform acs_privilege__create_privilege(''forum_create'',null,null); + perform acs_privilege__create_privilege(''forum_write'',null,null); + perform acs_privilege__create_privilege(''forum_delete'',null,null); + perform acs_privilege__create_privilege(''forum_read'',null,null); + perform acs_privilege__create_privilege(''forum_post'',null,null); + perform acs_privilege__create_privilege(''forum_moderate'',null,null); - drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. - -- 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. + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; - 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 (); + -- add children + perform acs_privilege__add_child(''create'',''forum_create''); + perform acs_privilege__add_child(''write'',''forum_write''); + perform acs_privilege__add_child(''delete'',''forum_delete''); + perform acs_privilege__add_child(''admin'',''forum_moderate''); + perform acs_privilege__add_child(''forum_moderate'',''forum_read''); + perform acs_privilege__add_child(''forum_moderate'',''forum_post''); + perform acs_privilege__add_child(''forum_write'',''forum_read''); + perform 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. - -- the last one that will cause all the updates - select acs_privilege__add_child('read','forum_read'); -end; + 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 + perform acs_privilege__add_child(''read'',''forum_read''); --- --- The Data Model --- + return null; +end;' language 'plpgsql'; +select inline_0(); +drop function inline_0 (); + 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 + forum_id integer + 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_ck + check (presentation_type in ('flat','threaded')), + posting_policy varchar(100) + constraint forum_policy_nn + not null + constraint forum_policy_ck + check (posting_policy in ('open','moderated','closed')), + max_child_sortkey varbit, + enabled_p char(1) default 't' + constraint forum_enabled_p_nn + not null + constraint forum_enabled_p_ck + check (enabled_p in ('t', 'f')), + package_id integer + constraint forum_package_id_fk + references apm_packages (package_id) + constraint forum_package_id_nn + not null ); create view forums_forums_enabled -as select * from forums_forums where enabled_p='t'; +as + select * + from forums_forums + where enabled_p = 't'; --- --- Object Type --- - +create function inline_0 () +returns integer as' 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; + perform acs_object_type__create_type( + ''forums_forum'', + ''Forums Forum'', + ''Forums Forums'', + ''acs_object'', + ''forums_forums'', + ''forum_id'', + ''forums_forum'', + ''f'', + null, + ''forums_forum__name'' + ); + + return null; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); 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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-drop.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -1,4 +1,3 @@ - -- -- The Forums Package -- @@ -10,50 +9,56 @@ -- -- privileges + +create function inline_0 () +returns integer as ' begin - -- temporarily drop this trigger to avoid a data-change violation - -- on acs_privilege_hierarchy_index while updating the child privileges. + -- 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; - drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + -- remove children + perform acs_privilege__remove_child(''read'',''forum_read''); + perform acs_privilege__remove_child(''create'',''forum_create''); + perform acs_privilege__remove_child(''write'',''forum_write''); + perform acs_privilege__remove_child(''delete'',''forum_delete''); + perform acs_privilege__remove_child(''admin'',''forum_moderate''); + perform acs_privilege__remove_child(''forum_moderate'',''forum_read''); + perform acs_privilege__remove_child(''forum_moderate'',''forum_post''); + perform acs_privilege__remove_child(''forum_write'',''forum_read''); - -- 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 (); - -- 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 (); + perform acs_privilege__remove_child(''forum_write'',''forum_post''); + + perform acs_privilege__drop_privilege(''forum_moderate''); + perform acs_privilege__drop_privilege(''forum_post''); + perform acs_privilege__drop_privilege(''forum_read''); + perform acs_privilege__drop_privilege(''forum_create''); + perform acs_privilege__drop_privilege(''forum_write''); + perform acs_privilege__drop_privilege(''forum_delete''); - 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; + return null; +end;' language 'plpgsql'; +select inline_0(); +drop function inline_0 (); --- --- The Data Model --- - +drop view forums_forums_enabled; drop table forums_forums; --- --- Object Type --- - +create function inline_0 () +returns integer as ' begin - select acs_object_type__drop_type ( - 'forums_forum', 'f' - ); -end; + perform acs_object_type__drop_type ( + ''forums_forum'', ''f'' + ); + + return null; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0 (); 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 -r1.3 -r1.4 --- openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 3 Jun 2002 23:58:45 -0000 1.3 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-create.sql 12 Jun 2002 15:24:02 -0000 1.4 @@ -17,7 +17,6 @@ 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) @@ -48,7 +47,7 @@ (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); - select acs_object__update_last_modified(p_context_id); + perform acs_object__update_last_modified(p_context_id); return v_forum_id; END; 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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-forums-package-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-forums-package-drop.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -11,8 +11,6 @@ -- 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); 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 -r1.2 -r1.3 --- openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 3 Jun 2002 23:58:45 -0000 1.2 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-create.sql 12 Jun 2002 15:24:02 -0000 1.3 @@ -18,65 +18,79 @@ -- 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) + message_id integer + 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_ck + 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_ck + 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' + constraint forum_mess_open_p_nn + not null + constraint forum_mess_open_p_ck + 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_approved as -select * from forums_messages where state='approved'; +create view forums_messages_pending +as + select * + from forums_messages + where state= 'pending'; -create view forums_messages_pending as -select * from forums_messages where state='pending'; +create function inline_0 () +returns integer as ' +begin + perform acs_object_type__create_type( + ''forums_message'', + ''Forums Message'', + ''Forums Messages'', + ''acs_object'', + ''forums_messages'', + ''message_id'', + ''forums_message'', + ''f'', + null, + ''forums_message__name'' + ); --- --- Object Type --- + return null; +end;' language 'plpgsql'; -begin - select acs_object_type__create_type ( - 'forums_message', - 'Forums Message', - 'Forums Messages', - 'acs_object', - 'forums_messages', - 'message_id', - 'forums_message', - 'f', - NULL, - 'forums_message__name' - ); -end; +select inline_0(); +drop function inline_0 (); 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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-drop.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -1,4 +1,3 @@ - -- -- The Forums Package -- @@ -9,22 +8,19 @@ -- lifted from Gilbert. Thanks Orchard Labs! -- --- privileges --- NO PRIVILEGES FOR MESSAGES --- we don't individually permission messages - --- --- The Data Model --- - +drop view forums_messages_pending; +drop view forums_messages_approved; drop table forums_messages; --- --- Object Type --- - +create function inline_0 () +returns integer as ' begin - select acs_object_type__drop_type ( - 'forums_message', 'f' - ); -end; + perform acs_object_type__drop_type ( + ''forums_message'', ''f'' + ); + + return null; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0 (); 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 -r1.3 -r1.4 --- openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 3 Jun 2002 23:58:45 -0000 1.3 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-create.sql 12 Jun 2002 15:24:02 -0000 1.4 @@ -74,7 +74,7 @@ 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); - select acs_object__update_last_modified(p_forum_id); + perform acs_object__update_last_modified(p_forum_id); return v_message_id; END; 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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-messages-package-drop.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-messages-package-drop.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -23,4 +23,4 @@ drop function forums_message__delete_thread (integer); - +drop function forums_message__name (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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-notifications-init.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -10,47 +10,63 @@ -- -- the integration with Notifications +create function inline_0 () +returns integer as ' declare - v_foo integer; + 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'); + v_foo := notification_type__new( + null, + ''forums_forum_notif'', + ''Forum Notification'', + ''Notifications for Entire Forums'', + null, + null, + null, + null + ); - 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'); + -- 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''); - 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 - ); + 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''); - -- 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'); + v_foo := notification_type__new( + null, + ''forums_message_notif'', + ''Message Notification'', + ''Notifications for Message Thread'', + null, + null, + null, + null + ); - 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'); + -- 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''); -end; -/ -show errors + 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''); + + return null; + +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0 (); Index: openacs-4/packages/forums/sql/postgresql/forums-notifications-sanitize.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/Attic/forums-notifications-sanitize.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-notifications-sanitize.sql 12 Jun 2002 15:24:02 -0000 1.1 @@ -0,0 +1,27 @@ +-- +-- 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. +-- + +create function inline_0 () +returns integer as ' +declare + row record; +begin + for row in select nt.type_id + from notification_types nt + where nt.short_name in (''forums_forum_notif'', ''forums_message_notif'') + loop + perform notification_type__delete(row.type_id); + end loop; + + return null; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0 (); 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 -r1.1 -r1.2 --- openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 1 Jun 2002 19:02:29 -0000 1.1 +++ openacs-4/packages/forums/sql/postgresql/forums-tree-create.sql 12 Jun 2002 15:24:02 -0000 1.2 @@ -13,48 +13,55 @@ -- This is the sortkey code -- - -create or replace trigger forums_mess_insert_tr -before insert on forums_messages -for each row +create function forums_mess_insert_tr () +returns opaque as ' declare - v_max_child_sortkey forums_messages.max_child_sortkey%TYPE; - v_parent_sortkey forums_messages.tree_sortkey%TYPE; + 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 + 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 + 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 + 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 + where message_id = new.parent_id for update of max_child_sortkey; - end if; + end if; - -- increment the sortkey - v_max_child_sortkey:= lpad(tree.increment_key(v_max_child_sortkey),6,'0'); + -- 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; + 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; + -- generate the current sortkey + new.tree_sortkey := v_parent_sortkey || v_max_child_sortkey; + + return new; -end forums_mess_insert_tr; -/ -show errors +end;' language 'plpgsql'; + +create trigger forums_mess_insert_tr +before insert on forums_messages +for each row +execute procedure forums_mess_insert_tr (); Index: openacs-4/packages/forums/sql/postgresql/forums-tree-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/forums-tree-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/postgresql/forums-tree-drop.sql 12 Jun 2002 15:24:02 -0000 1.1 @@ -0,0 +1,17 @@ + +-- +-- 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 +-- + +drop trigger forums_mess_insert_tr on forums_messages; +drop function forums_mess_insert_tr ();