Index: openacs-4/contrib/obsolete-packages/bboard/sql/oracle/bboard-packages.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/oracle/bboard-packages.sql,v diff -u -N --- openacs-4/contrib/obsolete-packages/bboard/sql/oracle/bboard-packages.sql 9 Mar 2002 02:00:02 -0000 1.4 +++ /dev/null 1 Jan 1970 00:00:00 -0000 @@ -1,702 +0,0 @@ --- --- packages/bboard/sql/bboard-packages.sql --- --- @author Anukul Kapoor --- @author John Prevost --- @creation-date 2000-11-22 --- @cvs-id $Id: bboard-packages.sql,v 1.4 2002/03/09 02:00:02 donb Exp $ --- - -create or replace package bboard_forum -as - - function forum_p ( - forum_id in bboard_forums.forum_id%TYPE - ) return char; - - function new ( - forum_id in bboard_forums.forum_id%TYPE default null, - short_name in bboard_forums.short_name%TYPE, - charter in bboard_forums.charter%TYPE default null, - moderated_p in bboard_forums.moderated_p%TYPE default 'f', - bboard_id in bboard_forums.bboard_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_forum' - ) return acs_objects.object_id%TYPE; - - procedure delete ( - forum_id in bboard_forums.forum_id%TYPE - ); - - procedure set_attrs ( - forum_id in bboard_forums.forum_id%TYPE, - short_name in bboard_forums.short_name%TYPE default null, - charter in bboard_forums.charter%TYPE default null, - moderated_p in bboard_forums.moderated_p%TYPE default null, - bboard_id in acs_objects.context_id%TYPE default null - ); - - procedure subscribe ( - forum_id in bboard_forum_subscribers.forum_id%TYPE, - subscriber_id in bboard_forum_subscribers.subscriber_id%TYPE - ); - - function forum_containing_message ( - message_id in acs_messages.message_id%TYPE - ) return bboard_forums.forum_id%TYPE; - - function name ( - forum_id in acs_objects.object_id%TYPE - ) return varchar2; - -end bboard_forum; -/ -show errors - -create or replace package bboard_category -as - - function category_p ( - category_id in bboard_categories.category_id%TYPE - ) return char; - - function new ( - category_id in bboard_categories.category_id%TYPE default null, - short_name in bboard_categories.short_name%TYPE, - description in bboard_categories.description%TYPE default null, - forum_id in bboard_forums.forum_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_category' - ) return acs_objects.object_id%TYPE; - - procedure delete ( - category_id in bboard_categories.category_id%TYPE - ); - - procedure set_attrs ( - category_id in bboard_categories.category_id%TYPE, - short_name in bboard_categories.short_name%TYPE default null, - description in bboard_categories.description%TYPE default null, - forum_id in integer default null - ); - - procedure subscribe ( - category_id in bboard_category_subscribers.category_id%TYPE, - subscriber_id in bboard_category_subscribers.subscriber_id%TYPE - ); - - function name ( - category_id acs_objects.object_id%TYPE - ) return varchar2; - -end bboard_category; -/ -show errors - -create or replace package bboard_message -as - - function new ( - message_id in acs_messages.message_id%TYPE default null, - reply_to in acs_messages.message_id%TYPE default null, - sent_date in acs_messages.sent_date%TYPE default sysdate, - sender in acs_messages.sender%TYPE default null, - rfc822_id in acs_messages.rfc822_id%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - text in varchar2 default null, - data in cr_revisions.content%TYPE default null, - context_id in acs_objects.context_id%TYPE default 0, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_message' - ) return acs_objects.object_id%TYPE; - - function message_p ( - message_id in acs_messages.message_id%TYPE - ) return char; - - procedure set_attrs ( - message_id in acs_messages.message_id%TYPE, - reply_to in acs_messages.reply_to%TYPE default null, - sent_date in acs_messages.sent_date%TYPE default null, - sender in acs_messages.sender%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default null, - context_id in acs_objects.context_id%TYPE default null - ); - - procedure set_status ( - message_id in bboard_forum_message_map.message_id%TYPE, - forum_id in bboard_forum_message_map.forum_id%TYPE, - status in bboard_forum_message_map.status%TYPE - ); - - procedure add_category ( - message_id in bboard_category_message_map.message_id%TYPE, - category_id in bboard_category_message_map.category_id%TYPE - ); - - procedure remove_category ( - message_id in bboard_category_message_map.message_id%TYPE, - category_id in bboard_category_message_map.category_id%TYPE - ); - - procedure clear_categories ( - message_id in bboard_category_message_map.message_id%TYPE - ); - - procedure subscribe ( - thread_id in bboard_thread_subscribers.thread_id%TYPE, - subscriber_id in bboard_thread_subscribers.subscriber_id%TYPE - ); - - function name ( - message_id acs_objects.object_id%TYPE - ) return varchar2; - - procedure remove_thread ( - thread_id in bboard_messages_all.message_id%TYPE - ); - - procedure remove ( - message_id in bboard_messages_all.message_id%TYPE - ); - -end bboard_message; -/ -show errors - - - - -create or replace package body bboard_forum -as - - function forum_p ( - forum_id in bboard_forums.forum_id%TYPE - ) return char - is - v_check_forum_id integer; - begin - select count(forum_id) into v_check_forum_id - from bboard_forums - where forum_id = forum_p.forum_id; - if v_check_forum_id = 1 then - return 't'; - else - return 'f'; - end if; - end forum_p; - - function new ( - forum_id in bboard_forums.forum_id%TYPE default null, - short_name in bboard_forums.short_name%TYPE, - charter in bboard_forums.charter%TYPE default null, - moderated_p in bboard_forums.moderated_p%TYPE default 'f', - bboard_id in bboard_forums.bboard_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_forum' - ) return acs_objects.object_id%TYPE - is - v_context_id acs_objects.context_id%TYPE; - v_forum_id bboard_forums.forum_id%TYPE; - begin - v_context_id := nvl(context_id, bboard_id); - v_forum_id := acs_object.new ( - context_id => v_context_id, - object_id => forum_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - object_type => object_type - ); - insert into bboard_forums - (forum_id, short_name, charter, moderated_p, bboard_id) - values (v_forum_id, short_name, charter, moderated_p, bboard_id); - return v_forum_id; - end new; - - procedure delete ( - forum_id in bboard_forums.forum_id%TYPE - ) - is - begin - delete from bboard_forums - where forum_id = bboard_forum.delete.forum_id; - acs_object.delete(forum_id); - end delete; - - procedure set_attrs ( - forum_id in bboard_forums.forum_id%TYPE, - short_name in bboard_forums.short_name%TYPE default null, - charter in bboard_forums.charter%TYPE default null, - moderated_p in bboard_forums.moderated_p%TYPE default null, - bboard_id in acs_objects.context_id%TYPE default null - ) - is - v_check_forum_id integer; - begin - select count(forum_id) into v_check_forum_id - from bboard_forums - where forum_id = set_attrs.forum_id; - -- It's not a forum. Fail silently? - if v_check_forum_id <> 1 then - return; - end if; - if short_name is not null then - update bboard_forums set short_name = set_attrs.short_name - where forum_id = set_attrs.forum_id; - end if; - update bboard_forums set charter = set_attrs.charter - where forum_id = set_attrs.forum_id; - if moderated_p is not null then - update bboard_forums set moderated_p = set_attrs.moderated_p - where forum_id = set_attrs.forum_id; - end if; - if bboard_id is not null then - update bboard_forums set bboard_id = set_attrs.bboard_id - where forum_id = set_attrs.forum_id; - update acs_objects set context_id = set_attrs.bboard_id - where object_id = set_attrs.forum_id; - end if; - end set_attrs; - - procedure subscribe ( - forum_id in bboard_forum_subscribers.forum_id%TYPE, - subscriber_id in bboard_forum_subscribers.subscriber_id%TYPE - ) - is - begin - insert into bboard_forum_subscribers (forum_id, subscriber_id) - values (forum_id, subscriber_id); - end; - - function forum_containing_message ( - message_id in acs_messages.message_id%TYPE - ) return bboard_forums.forum_id%TYPE - is - v_forum_id bboard_forums.forum_id%TYPE; - begin - select max(forum_id) into v_forum_id - from bboard_forum_message_map - where message_id = forum_containing_message.message_id; - if v_forum_id is null then - return 0; - else - return v_forum_id; - end if; - end forum_containing_message; - - function name ( - forum_id in acs_objects.object_id%TYPE - ) return varchar2 - is - v_forum_name bboard_forums.short_name%TYPE; - begin - select short_name into v_forum_name - from bboard_forums - where forum_id = name.forum_id; - return v_forum_name; - end name; - -end bboard_forum; -/ -show errors - -create or replace package body bboard_category -as - - function category_p ( - category_id in bboard_categories.category_id%TYPE - ) return char - is - v_check_category_id integer; - begin - select count(category_id) into v_check_category_id - from bboard_categories - where category_id = category_p.category_id; - if v_check_category_id = 1 then - return 't'; - else - return 'f'; - end if; - end category_p; - - function new ( - category_id in bboard_categories.category_id%TYPE default null, - short_name in bboard_categories.short_name%TYPE, - description in bboard_categories.description%TYPE default null, - forum_id in bboard_forums.forum_id%TYPE, - context_id in acs_objects.context_id%TYPE default null, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_category' - ) return acs_objects.object_id%TYPE - is - v_category_id bboard_categories.category_id%TYPE; - v_context_id acs_objects.context_id%TYPE; - begin - v_context_id := nvl(context_id, forum_id); - v_category_id := acs_object.new ( - object_id => category_id, - context_id => v_context_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - object_type => object_type - ); - insert into bboard_categories - (category_id, short_name, description, forum_id) - values (v_category_id, short_name, description, forum_id); - return v_category_id; - end new; - - procedure delete ( - category_id in bboard_categories.category_id%TYPE - ) - is - begin - delete from bboard_categories - where category_id = bboard_category.delete.category_id; - acs_object.delete(category_id); - end delete; - - procedure set_attrs ( - category_id in bboard_categories.category_id%TYPE, - short_name in bboard_categories.short_name%TYPE default null, - description in bboard_categories.description%TYPE default null, - forum_id in integer default null - ) - is - v_check_category_id integer; - begin - select count(category_id) into v_check_category_id - from bboard_categories - where category_id = set_attrs.category_id; - -- It's not a category. Fail silently? - if v_check_category_id <> 1 then - return; - end if; - -- It's a category. Go for it. - if short_name is not null then - update bboard_categories set short_name = set_attrs.short_name - where category_id = set_attrs.category_id; - end if; - if description is not null then - update bboard_categories set description = set_attrs.description - where category_id = set_attrs.category_id; - end if; - if forum_id is not null then - update bboard_categories set forum_id = set_attrs.forum_id - where category_id = set_attrs.category_id; - update acs_objects set context_id = set_attrs.forum_id - where object_id = set_attrs.category_id; - end if; - end set_attrs; - - procedure subscribe ( - category_id in bboard_category_subscribers.category_id%TYPE, - subscriber_id in bboard_category_subscribers.subscriber_id%TYPE - ) - is - begin - insert into bboard_category_subscribers (category_id, subscriber_id) - values (category_id, subscriber_id); - end; - - function name ( - category_id in acs_objects.object_id%TYPE - ) return varchar2 - is - v_category_name bboard_categories.short_name%TYPE; - begin - select short_name into v_category_name - from bboard_categories - where category_id = name.category_id; - return v_category_name; - end name; - -end bboard_category; -/ -show errors - - -create or replace package body bboard_message -as - - function new ( - message_id in acs_messages.message_id%TYPE default null, - reply_to in acs_messages.message_id%TYPE default null, - sent_date in acs_messages.sent_date%TYPE default sysdate, - sender in acs_messages.sender%TYPE default null, - rfc822_id in acs_messages.rfc822_id%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default 'text/plain', - text in varchar2 default null, - data in cr_revisions.content%TYPE default null, - context_id in acs_objects.context_id%TYPE default 0, - creation_date in acs_objects.creation_date%TYPE default sysdate, - creation_user in acs_objects.creation_user%TYPE default null, - creation_ip in acs_objects.creation_ip%TYPE default null, - object_type in acs_objects.object_type%TYPE default 'bboard_message' - ) return acs_objects.object_id%TYPE - is - v_sent_date acs_messages.sent_date%TYPE; - v_message_id bboard_messages.message_id%TYPE; - begin - v_sent_date := nvl(sent_date, sysdate); - - v_message_id := acs_message.new ( - message_id => message_id, - reply_to => reply_to, - sent_date => v_sent_date, - sender => sender, - title => title, - mime_type => mime_type, - text => text, - data => data, - context_id => context_id, - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - object_type => object_type - ); - - insert into bboard_messages - (message_id) - values - (v_message_id); - - return v_message_id; - end new; - - function message_p ( - message_id in acs_messages.message_id%TYPE - ) return char - is - v_check_message_id integer; - begin - select count(message_id) into v_check_message_id - from bboard_messages - where message_id = message_p.message_id; - if v_check_message_id = 1 then - return 't'; - else - return 'f'; - end if; - end message_p; - - procedure set_attrs ( - message_id in acs_messages.message_id%TYPE, - reply_to in acs_messages.reply_to%TYPE default null, - sent_date in acs_messages.sent_date%TYPE default null, - sender in acs_messages.sender%TYPE default null, - title in cr_revisions.title%TYPE default null, - mime_type in cr_revisions.mime_type%TYPE default null, - context_id in acs_objects.context_id%TYPE default null - ) - is - v_check_message_id integer; - v_revision_id integer; - begin - if message_p(message_id) = 'f' then - return; - end if; - - -- modify the parts that are in acs_messages - - if reply_to is not null then - update acs_messages set reply_to = set_attrs.reply_to - where message_id = set_attrs.message_id; - end if; - if sent_date is not null then - update acs_messages set sent_date = set_attrs.sent_date - where message_id = set_attrs.message_id; - end if; - if sender is not null then - update acs_messages set sender = set_attrs.sender - where message_id = set_attrs.message_id; - end if; - - -- modify the parts that are in cr_revisions - if title is not null or mime_type is not null then - select live_revision into v_revision_id - from cr_items where item_id = set_attrs.message_id - for update; - if title is not null then - update cr_revisions set title = set_attrs.title - where revision_id = v_revision_id; - end if; - if mime_type is not null then - update cr_revisions set mime_type = set_attrs.mime_type - where revision_id = v_revision_id; - end if; - end if; - - -- modify the context_id is acs_objects - - if context_id is not null then - update acs_objects set context_id = set_attrs.context_id - where object_id = set_attrs.message_id; - end if; - - end set_attrs; - - procedure set_status ( - message_id in bboard_forum_message_map.message_id%TYPE, - forum_id in bboard_forum_message_map.forum_id%TYPE, - status in bboard_forum_message_map.status%TYPE - ) - is - begin - if message_p(message_id) = 'f' - or bboard_forum.forum_p(forum_id) = 'f' then - return; - end if; - delete from bboard_forum_message_map - where message_id = set_status.message_id - and forum_id = set_status.forum_id; - if status is not null then - insert into bboard_forum_message_map - (forum_id, message_id, status) - values (set_status.forum_id, set_status.message_id, - set_status.status); - end if; - end set_status; - - procedure add_category ( - message_id in bboard_category_message_map.message_id%TYPE, - category_id in bboard_category_message_map.category_id%TYPE - ) - is - begin - insert into bboard_category_message_map (message_id, category_id) - values (add_category.message_id, add_category.category_id); - end add_category; - - procedure remove_category ( - message_id in bboard_category_message_map.message_id%TYPE, - category_id in bboard_category_message_map.category_id%TYPE - ) - is - begin - delete from bboard_category_message_map - where category_id = remove_category.category_id - and message_id = remove_category.message_id; - end remove_category; - - procedure clear_categories ( - message_id in bboard_category_message_map.message_id%TYPE - ) - is - begin - delete from bboard_category_message_map - where message_id = clear_categories.message_id; - end clear_categories; - - procedure subscribe ( - thread_id in bboard_thread_subscribers.thread_id%TYPE, - subscriber_id in bboard_thread_subscribers.subscriber_id%TYPE - ) - is - begin - insert into bboard_thread_subscribers (thread_id, subscriber_id) - values (thread_id, subscriber_id); - end; - - function name ( - message_id in acs_objects.object_id%TYPE - ) return varchar2 - is - v_message_name bboard_messages_all.title%TYPE; - begin - select title into v_message_name - from bboard_messages_all - where message_id = name.message_id; - return v_message_name; - end name; - - procedure remove_thread ( - thread_id in bboard_messages_all.message_id%TYPE - ) - is - cursor messages_children is - select object_id as child_id, object_type - from acs_objects - where context_id in (select message_id - from acs_messages - connect by prior message_id = reply_to - start with message_id = thread_id); - - cursor messages is - select message_id - from acs_messages - connect by prior message_id = reply_to - start with message_id = thread_id; - - image_p number; - begin - for child_val in messages_children loop - if child_val.object_type = 'bboard_message' then - acs_message.delete(child_val.child_id); - elsif child_val.object_type = 'content_item' then - select count(*) into image_p - from images - where image_id = child_val.child_id; - if image_p = 1 then - acs_message.delete_image(child_val.child_id); - else - acs_message.delete_file(child_val.child_id); - end if; - end if; - end loop; - - for message_val in messages loop - acs_message.delete(message_val.message_id); - end loop; - - end remove_thread; - - procedure remove ( - message_id in bboard_messages_all.message_id%TYPE - ) - is - cursor messages_children is - select object_id as child_id, object_type - from acs_objects - where context_id = message_id; - image_p number; - begin - for child_val in messages_children loop - if child_val.object_type = 'bboard_message' then - acs_message.delete(child_val.child_id); - elsif child_val.object_type = 'content_item' then - select count(*) into image_p - from images - where image_id = child_val.child_id; - if image_p = 1 then - acs_message.delete_image(child_val.child_id); - else - acs_message.delete_file(child_val.child_id); - end if; - end if; - end loop; - - acs_message.delete(message_id); - end remove; - -end bboard_message; -/ -show errors