--
-- packages/bboard/sql/bboard-packages.sql
--
-- @author Anukul Kapoor <akk@arsdigita.com>
-- @author John Prevost <jmp@arsdigita.com>
-- @creation-date 2000-11-22
-- @cvs-id $Id: bboard-packages.sql,v 1.1.1.1 2002/07/09 17:35:01 rmello 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