Index: openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql,v diff -u -r1.1.2.2 -r1.1.2.3 --- openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql 30 Jun 2004 20:24:10 -0000 1.1.2.2 +++ openacs-4/packages/forums/sql/postgresql/upgrade/upgrade-1.0d4-1.0.1d2.sql 30 Jun 2004 20:43:10 -0000 1.1.2.3 @@ -11,22 +11,11 @@ set format = 'text/plain' where html_p = 'f'; --- forums-messages-package-create.sql --- --- 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! --- +-- taken from forums-messages-package-create.sql select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,format,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id'); -create function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) +create or replace function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer) returns integer as ' declare p_message_id alias for $1; @@ -94,156 +83,3 @@ end; ' language 'plpgsql'; - -select define_function_args ('forums_message__root_message_id', 'message_id'); - -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' with(isstrict,iscachable); - -select define_function_args ('forums_message__thread_open', 'message_id'); - -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'; - -select define_function_args ('forums_message__thread_close', 'message_id'); - -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'; - -select define_function_args ('forums_message__delete', 'message_id'); - -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'; - -select define_function_args ('forums_message__delete_thread', 'message_id'); - -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 v_forum_id is null - 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'; - -select define_function_args('forums_message__name','message_id'); - -create function forums_message__name (integer) -returns varchar as ' -declare - p_message_id alias for $1; -begin - return subject from forums_messages where message_id = p_message_id; -end; -' language 'plpgsql'; - -create or replace view forums_messages_approved as - select * - from forums_messages - where state = 'approved'; - -create or replace view forums_messages_pending as - select * - from forums_messages - where state = 'pending'; -