-- -- Copyright (C) 2001, 2002 OpenForce, Inc. -- -- This file is part of dotLRN. -- -- dotLRN is free software; you can redistribute it and/or modify it under the -- terms of the GNU General Public License as published by the Free Software -- Foundation; either version 2 of the License, or (at your option) any later -- version. -- -- dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS -- FOR A PARTICULAR PURPOSE. See the GNU General Public License for more -- details. -- -- -- 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.5 2002/03/29 19:35:47 yon 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', forum_type in bboard_forums.forum_type%TYPE default 'q-and-a', 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 'acs_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 ); function new_p ( message_id in bboard_messages_all.message_id%TYPE, user_id in users.user_id%TYPE ) return char; procedure mark_read ( message_id in bboard_messages_all.message_id%TYPE, user_id in users.user_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', forum_type in bboard_forums.forum_type%TYPE default 'q-and-a', 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, forum_type, bboard_id) values (v_forum_id, short_name, charter, moderated_p, forum_type, 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 'acs_message' ) return acs_objects.object_id%TYPE is v_sent_date acs_messages.sent_date%TYPE; begin v_sent_date := nvl(sent_date, sysdate); return 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 ); 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 acs_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 = 'acs_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 = 'acs_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; function new_p ( message_id in bboard_messages_all.message_id%TYPE, user_id in users.user_id%TYPE ) return char is v_track_new char(1); v_user_has_viewed_p char(1); v_newness_threshhold date; v_message_date date; begin -- figure out if this forum tracks individual messages select track_new_postings_p into v_track_new from bboard_forums where forum_id = (select forum_id from bboard_forum_message_map where message_id= new_p.message_id); -- if it does, check if the user has viewed the message if v_track_new ='t' then v_user_has_viewed_p:= 'f'; select 't' into v_user_has_viewed_p from dual where exists (select 1 from bboard_message_user_map where message_id= new_p.message_id and user_id= new_p.user_id); return v_user_has_viewed_p; end if; -- otherwise, look up the user preference parameter for newness -- currently hacked, cause no preference mechanism select second_to_last_visit into v_newness_threshhold from users where user_id= new_p.user_id; -- calculate if the message is new according to the preference select sent_date into v_message_date from acs_messages where message_id= new_p.message_id; if (v_message_date > v_newness_threshhold) then return 't'; else return 'f'; end if; end new_p; procedure mark_read ( message_id in bboard_messages_all.message_id%TYPE, user_id in users.user_id%TYPE ) is v_track_new char(1); begin -- figure out if this forum tracks individual messages select track_new_postings_p into v_track_new from bboard_forums where forum_id = (select forum_id from bboard_forum_message_map where message_id= mark_read.message_id); -- if it doesn't, stop here if v_track_new = 't' then insert into bboard_message_user_map (message_id, user_id, view_date) values (message_id, user_id, sysdate); end if; end mark_read; end bboard_message; / show errors