Index: openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-create.sql 29 Jun 2001 20:31:14 -0000 1.1
@@ -0,0 +1,337 @@
+--
+-- packages/bboard/sql/bboard-create.sql
+--
+-- @author Anukul Kapoor <akk@arsdigita.com>
+-- @author John Prevost <jmp@arsdigita.com>
+-- @creation-date 2000-08-29
+-- @cvs-id $Id: bboard-create.sql,v 1.1 2001/06/29 20:31:14 lukep Exp $
+--
+
+-- separate parts so that if one fails, the rest happens
+
+-- create the privileges
+
+begin;
+
+ select acs_privilege__create_privilege('bboard_create_forum',null,null);
+ select acs_privilege__create_privilege('bboard_create_category',null,null);
+ select acs_privilege__create_privilege('bboard_create_message',null,null);
+ select acs_privilege__create_privilege('bboard_write_forum',null,null);
+ select acs_privilege__create_privilege('bboard_write_category',null,null);
+ select acs_privilege__create_privilege('bboard_write_message',null,null);
+ select acs_privilege__create_privilege('bboard_read_forum',null,null);
+ select acs_privilege__create_privilege('bboard_read_category',null,null);
+ select acs_privilege__create_privilege('bboard_read_message',null,null);
+ select acs_privilege__create_privilege('bboard_delete_forum',null,null);
+ select acs_privilege__create_privilege('bboard_delete_category',null,null);
+ select acs_privilege__create_privilege('bboard_delete_message',null,null);
+ select acs_privilege__create_privilege('bboard_moderate_forum',null,null);
+
+
+ -- 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;
+
+ select acs_privilege__add_child('create','bboard_create_forum');
+ select acs_privilege__add_child('create','bboard_create_category');
+ select acs_privilege__add_child('create','bboard_create_message');
+ select acs_privilege__add_child('write','bboard_write_forum');
+ select acs_privilege__add_child('write','bboard_write_category');
+ select acs_privilege__add_child('write','bboard_write_message');
+ select acs_privilege__add_child('read','bboard_read_forum');
+ select acs_privilege__add_child('read','bboard_read_category');
+ select acs_privilege__add_child('read','bboard_read_message');
+ select acs_privilege__add_child('delete','bboard_delete_forum');
+ select acs_privilege__add_child('delete','bboard_delete_category');
+ select acs_privilege__add_child('delete','bboard_delete_message');
+
+ -- re-enable the trigger before the last insert to force the
+ -- acs_privilege_hierarchy_index table to be updated.
+
+ 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 ();
+
+select acs_privilege__add_child('admin','bboard_moderate_forum');
+end;
+
+
+create function inline_0 ()
+returns integer as '
+declare
+ default_context integer;
+ registered_users integer;
+ the_public integer;
+begin
+
+ default_context := acs__magic_object_id(''default_context'');
+ registered_users := acs__magic_object_id(''registered_users'');
+ the_public := acs__magic_object_id(''the_public'');
+
+ -- give registered users the power to post by default
+
+ perform acs_permission__grant_permission (
+ default_context,
+ registered_users,
+ ''bboard_create_message''
+ );
+
+ -- give the public the power to read by default
+
+ perform acs_permission__grant_permission (
+ default_context,
+ the_public,
+ ''bboard_read_message''
+ );
+
+ perform acs_permission__grant_permission (
+ default_context,
+ the_public,
+ ''bboard_read_category''
+ );
+
+ perform acs_permission__grant_permission (
+ default_context,
+ the_public,
+ ''bboard_read_forum''
+ );
+
+
+ return 0;
+end;
+' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+
+select acs_object_type__create_type (
+ 'bboard_forum',
+ 'BBoard Forum',
+ 'BBoard Forum',
+ 'acs_object',
+ 'BBOARD_FORUMS',
+ 'FORUM_ID',
+ null,
+ 'f',
+ null,
+ 'BBOARD_FORUM__NAME'
+);
+
+select acs_object_type__create_type (
+ 'bboard_category',
+ 'BBoard Category',
+ 'BBoard Categories',
+ 'acs_object',
+ 'BBOARD_CATEGORIES',
+ 'CATEGORY_ID',
+ null,
+ 'f',
+ null,
+ 'BBOARD_CATEGORY__NAME'
+);
+
+
+
+-- bboard forums
+--
+-- these act as primary containers for messages
+-- a message's context_id will point to its forum
+
+create table bboard_forums (
+ forum_id integer
+ constraint bboard_forums_forum_id_fk
+ references acs_objects (object_id)
+ constraint bboard_forums_pk
+ primary key,
+ short_name varchar(400)
+ constraint bboard_forums_short_name_nn
+ not null,
+ charter varchar(4000),
+ moderated_p char(1)
+ constraint bboard_forums_moderated_p_nn
+ not null
+ constraint bboard_forums_moderated_p_ck
+ check (moderated_p in ('t','f')),
+ bboard_id integer
+ constraint bboard_forums_bboard_id_nn
+ not null
+ constraint bboard_forums_bboard_id_fk
+ references apm_packages (package_id)
+ on delete cascade
+);
+
+create index bboard_forums_bboard_id_idx
+ on bboard_forums (bboard_id);
+
+create table bboard_forum_message_map (
+ forum_id integer
+ constraint bboard_fmm_forum_id_fk
+ references bboard_forums (forum_id)
+ on delete cascade,
+ message_id integer
+ constraint bboard_fmm_message_id_fk
+ references acs_messages (message_id)
+ on delete cascade,
+ status varchar(20)
+ constraint bboard_fmm_status_ck
+ check (status in ('unmoderated', 'approved', 'rejected'))
+ constraint bboard_fmm_status_nn
+ not null,
+ constraint bboard_forum_message_map_pk
+ primary key (forum_id, message_id)
+);
+
+create index bboard_fmm_message_id_idx
+ on bboard_forum_message_map (message_id);
+
+create index bboard_fmm_status_idx
+ on bboard_forum_message_map (status);
+
+-- bboard categories
+--
+-- these are for intra-forum categorization
+-- categories will be scoped to forums via their acs_object.context_id
+
+create table bboard_categories (
+ category_id integer
+ constraint bboard_c_category_id_fk
+ references acs_objects (object_id)
+ constraint bboard_c_category_id_pk
+ primary key,
+ short_name varchar(400)
+ constraint bboard_c_short_name_nn
+ not null,
+ forum_id integer
+ constraint bboard_c_forum_id_fk
+ references bboard_forums (forum_id)
+ on delete cascade
+ constraint bboard_c_forum_id_nn
+ not null,
+ description varchar(4000)
+);
+
+create index bboard_categories_forum_id_idx
+ on bboard_categories (forum_id);
+
+create table bboard_category_message_map (
+ category_id integer
+ constraint bboard_cmm_category_id_fk
+ references bboard_categories (category_id)
+ on delete cascade,
+ message_id integer
+ constraint bboard_cmm_message_id_fk
+ references acs_messages (message_id)
+ on delete cascade,
+ constraint bboard_category_message_map_pk
+ primary key (category_id, message_id)
+);
+
+create index bboard_cmm_message_id_idx
+ on bboard_category_message_map (message_id);
+
+-- @@ bboard-views
+
+-- Tables to track subscriptions
+
+create table bboard_forum_subscribers (
+ forum_id integer
+ constraint bboard_fs_forum_id_fk
+ references bboard_forums (forum_id)
+ on delete cascade,
+ subscriber_id integer
+ constraint bboard_fs_subscriber_id_fk
+ references parties (party_id)
+ on delete cascade,
+ constraint bboard_forum_subscribers_pk
+ primary key (forum_id, subscriber_id)
+);
+
+create index bboard_fs_subscriber_id_idx
+ on bboard_forum_subscribers (subscriber_id);
+
+create table bboard_category_subscribers (
+ category_id integer
+ constraint bboard_cs_category_id_fk
+ references bboard_categories (category_id)
+ on delete cascade,
+ subscriber_id integer
+ constraint bboard_cs_subscriber_id_fk
+ references parties (party_id)
+ on delete cascade,
+ constraint bboard_category_subscribers_pk
+ primary key (category_id, subscriber_id)
+);
+
+create index bboard_cs_subscriber_id_idx
+ on bboard_category_subscribers (subscriber_id);
+
+create table bboard_thread_subscribers (
+ thread_id integer
+ constraint bboard_ts_thread_id_fk
+ references acs_messages (message_id)
+ on delete cascade,
+ subscriber_id integer
+ constraint bboard_ts_subscriber_id_fk
+ references parties (party_id)
+ on delete cascade,
+ constraint bboard_thread_subscribers_pk
+ primary key (thread_id, subscriber_id)
+);
+
+create index bboard_ts_subscriber_id_idx
+ on bboard_thread_subscribers (subscriber_id);
+
+\i bboard-views
+\i bboard-packages
+
+insert into cr_mime_types (mime_type)
+ values ('text/plain; format=flowed');
+
+
+-- -- NOTE: this is only temporary until we figure out how
+-- -- packages will register child types to an acs-message
+-- declare
+-- v_exists integer;
+-- begin
+
+-- select decode(count(*),0,0,1) into v_exists
+-- from cr_type_children
+-- where parent_type = 'acs_message_revision'
+-- and child_type = 'content_revision';
+
+-- if v_exists = 0 then
+-- content_type.register_child_type (
+-- parent_type => 'acs_message_revision',
+-- child_type => 'content_revision'
+-- );
+-- end if;
+
+-- select decode(count(*),0,0,1) into v_exists
+-- from cr_type_children
+-- where parent_type = 'acs_message_revision'
+-- and child_type = 'content_revision';
+
+-- if v_exists = 0 then
+-- content_type.register_child_type (
+-- parent_type => 'acs_message_revision',
+-- child_type => 'image'
+-- );
+-- end if;
+
+-- select decode(count(*),0,0,1) into v_exists
+-- from cr_type_children
+-- where parent_type = 'acs_message_revision'
+-- and child_type = 'content_revision';
+
+-- if v_exists = 0 then
+-- content_type.register_child_type (
+-- parent_type => 'acs_message_revision',
+-- child_type => 'content_extlink'
+-- );
+-- end if;
+
+-- end;
+-- /
+-- show errors
Index: openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-drop.sql 29 Jun 2001 20:31:14 -0000 1.1
@@ -0,0 +1,136 @@
+--
+-- packages/message/sql/bboard-drop.sql
+--
+-- @author jmp@arsdigita.com
+-- @creation-date 2000-08-31
+-- @cvs-id $Id: bboard-drop.sql,v 1.1 2001/06/29 20:31:14 lukep Exp $
+--
+
+-- This drop script destroys all messages that belong to forums.
+-- It should handle any permission changes that have been done since
+-- install by removing any permissions having to do with the standard
+-- bboard permissions.
+
+-- We need to get rid of things that might be referring to
+-- bboard objects that would prevent removal. This includes:
+-- email-a-friend droppings
+-- attachments (files and images)
+
+-- if something else is pointing at one of our messages, this script
+-- will fail and removing bboard will be icky. this is sort of hairy
+-- problem with unknown potential intra-package references.
+
+
+create function inline_0 ()
+returns integer as '
+
+ raise NOTICE ''currently it is impossible to delete multiple threads within a transaction. please ensure you've removed all forums through the admin interface before you try to drop the package.''
+
+' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0();
+
+
+-- Delete all subscriptions
+delete from bboard_thread_subscribers;
+delete from bboard_category_subscribers;
+delete from bboard_forum_subscribers;
+
+-- Delete all categories
+delete from bboard_category_message_map;
+delete from bboard_categories;
+delete from acs_objects where object_type = 'bboard_category';
+
+-- Delete all forums
+delete from bboard_forum_message_map;
+delete from bboard_forums;
+delete from acs_objects where object_type = 'bboard_forum';
+
+-- Drop all schema objects
+
+drop function bboard_forum__forum_p (integer);
+drop function bboard_forum__new (integer, varchar, varchar, char, integer,
+ integer, timestamp, integer, varchar, varchar);
+drop function bboard_forum__delete (integer);
+drop function bboard_forum__set_attrs (integer, varchar, varchar, char, integer);
+drop function bboard_forum__subscribe (integer, integer);
+drop function bboard_forum__forum_containing_message (integer);
+drop function bboard_category__category_p (integer);
+drop function bboard_category__new (integer, varchar, varchar, integer,
+ integer, timestamp, integer, varchar, varchar);
+drop function bboard_category__delete (integer);
+drop function bboard_category__set_attrs (integer, varchar, varchar, integer);
+drop function bboard_category__subscribe (integer, integer);
+drop function bboard_message__new (integer, integer, timestamp, integer,
+ varchar, varchar, varchar, varchar, text,
+ integer, timestamp, integer, varchar, varchar);
+drop function bboard_message__message_p (integer);
+drop function bboard_message__set_attrs (integer, integer, timestamp,
+ integer, varchar, varchar, integer);
+drop function bboard_message__set_status (integer, integer, varchar);
+drop function bboard_message__add_category (integer, integer);
+drop function bboard_message__remove_category (integer, integer);
+drop function bboard_message__clear_categories (integer);
+drop function bboard_message__subscribe (integer, integer);
+
+
+
+drop view bboard_messages_by_category;
+drop view bboard_messages_all;
+drop table bboard_thread_subscribers;
+drop table bboard_category_subscribers;
+drop table bboard_forum_subscribers;
+drop table bboard_category_message_map;
+drop table bboard_categories;
+drop table bboard_forum_message_map;
+drop table bboard_forums;
+
+-- Drop object type metadata
+begin;
+ select acs_object_type__drop_type ('bboard_forum');
+ select acs_object_type__drop_type ('bboard_category');
+end;
+
+
+-- Drop permission metadata
+delete from acs_permissions
+ where privilege in
+ ('bboard_create_forum', 'bboard_create_category',
+ 'bboard_create_message', 'bboard_write_forum',
+ 'bboard_write_category', 'bboard_write_message',
+ 'bboard_read_forum', 'bboard_read_category',
+ 'bboard_read_message', 'bboard_delete_forum',
+ 'bboard_delete_category', 'bboard_delete_message',
+ 'bboard_moderate_forum');
+
+delete from acs_privilege_hierarchy
+ where privilege in
+ ('bboard_create_forum', 'bboard_create_category',
+ 'bboard_create_message', 'bboard_write_forum',
+ 'bboard_write_category', 'bboard_write_message',
+ 'bboard_read_forum', 'bboard_read_category',
+ 'bboard_read_message', 'bboard_delete_forum',
+ 'bboard_delete_category', 'bboard_delete_message',
+ 'bboard_moderate_forum');
+
+delete from acs_privilege_hierarchy
+ where child_privilege in
+ ('bboard_create_forum', 'bboard_create_category',
+ 'bboard_create_message', 'bboard_write_forum',
+ 'bboard_write_category', 'bboard_write_message',
+ 'bboard_read_forum', 'bboard_read_category',
+ 'bboard_read_message', 'bboard_delete_forum',
+ 'bboard_delete_category', 'bboard_delete_message',
+ 'bboard_moderate_forum');
+
+delete from acs_privileges
+ where privilege in
+ ('bboard_create_forum', 'bboard_create_category',
+ 'bboard_create_message', 'bboard_write_forum',
+ 'bboard_write_category', 'bboard_write_message',
+ 'bboard_read_forum', 'bboard_read_category',
+ 'bboard_read_message', 'bboard_delete_forum',
+ 'bboard_delete_category', 'bboard_delete_message',
+ 'bboard_moderate_forum');
+
Index: openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-packages.sql 29 Jun 2001 20:31:14 -0000 1.1
@@ -0,0 +1,572 @@
+--
+-- 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 2001/06/29 20:31:14 lukep Exp $
+--
+
+------------ bboard_forum package ---------------
+
+create function bboard_forum__forum_p (integer)
+returns char as '
+declare
+ p_forum_id alias for $1;
+ v_check_forum_id integer;
+begin
+ select count(forum_id) into v_check_forum_id
+ from bboard_forums
+ where forum_id = p_forum_id;
+ if v_check_forum_id = 1 then
+ return ''t'';
+ else
+ return ''f'';
+ end if;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__new (integer, varchar, varchar, char, integer,
+ integer, timestamp, integer, varchar, varchar)
+returns integer as '
+declare
+ p_forum_id alias for $1; -- default null
+ p_short_name alias for $2;
+ p_charter alias for $3; -- default null
+ p_moderated_p alias for $4; -- default ''f''
+ p_bboard_id alias for $5;
+ p_context_id alias for $6; -- default null
+ p_creation_date alias for $7; -- default now()
+ p_creation_user alias for $8; -- default null
+ p_creation_ip alias for $9; -- default null
+ p_object_type alias for $10; -- default ''bboard_forum''
+ v_context_id integer;
+ v_forum_id integer;
+begin
+ v_context_id := coalesce(p_context_id, p_bboard_id);
+ v_forum_id := acs_object__new (
+ p_forum_id,
+ p_object_type,
+ p_creation_date,
+ p_creation_user,
+ p_creation_ip,
+ v_context_id
+ );
+
+ insert into bboard_forums
+ (forum_id, short_name, charter, moderated_p, bboard_id)
+ values (v_forum_id, p_short_name, p_charter, p_moderated_p, p_bboard_id);
+ return v_forum_id;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__delete (integer)
+returns integer as '
+declare
+ p_forum_id alias for $1;
+begin
+ delete from bboard_forums
+ where forum_id = p_forum_id;
+ PERFORM acs_object__delete(p_forum_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__set_attrs (integer, varchar, varchar, char, integer)
+returns integer as '
+declare
+ p_forum_id alias for $1;
+ p_short_name alias for $2; -- default null
+ p_charter alias for $3; -- default null
+ p_moderated_p alias for $4; -- default null
+ p_bboard_id alias for $5; -- default null
+ v_check_forum_id integer;
+begin
+ select count(forum_id) into v_check_forum_id
+ from bboard_forums
+ where forum_id = p_forum_id;
+ -- Not a forum. Fail silently?
+ if v_check_forum_id <> 1 then
+ return -1;
+ end if;
+ if p_short_name is not null then
+ update bboard_forums set short_name = p_short_name
+ where forum_id = p_forum_id;
+ end if;
+ if p_charter is not null then
+ update bboard_forums set charter = p_charter
+ where forum_id = p_forum_id;
+ end if;
+ if p_moderated_p is not null then
+ update bboard_forums set moderated_p = p_moderated_p
+ where forum_id = p_forum_id;
+ end if;
+ if p_bboard_id is not null then
+ update bboard_forums set bboard_id = p_bboard_id
+ where forum_id = p_forum_id;
+ update acs_objects set context_id = p_bboard_id
+ where object_id = p_forum_id;
+ end if;
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__subscribe (integer, integer)
+returns integer as '
+declare
+ p_forum_id alias for $1;
+ p_subscriber_id alias for $2;
+begin
+ insert into bboard_forum_subscribers (forum_id, subscriber_id)
+ values (p_forum_id, p_subscriber_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__forum_containing_message (integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ v_forum_id integer;
+begin
+ select max(forum_id) into v_forum_id
+ from bboard_forum_message_map
+ where message_id = p_message_id;
+
+ if v_forum_id is null then
+ return 0;
+ else
+ return v_forum_id;
+ end if;
+end;
+' language 'plpgsql';
+
+
+create function bboard_forum__name (integer)
+returns varchar as '
+declare
+ p_forum_id alias for $1;
+ v_forum_name varchar;
+begin
+ select short_name into v_forum_name
+ from bboard_forums
+ where forum_id = p_forum_id;
+ return v_forum_name;
+end;
+' language 'plpgsql';
+
+
+---------- bboard_category package ---------------
+
+create function bboard_category__category_p (integer)
+returns char as '
+declare
+ p_category_id alias for $1;
+ v_check_category_id integer;
+begin
+ select count(category_id) into v_check_category_id
+ from bboard_categories
+ where category_id = p_category_id;
+ if v_check_category_id = 1 then
+ return ''t'';
+ else
+ return ''f'';
+ end if;
+end;
+' language 'plpgsql';
+
+
+create function bboard_category__new (integer, varchar, varchar, integer,
+ integer, timestamp, integer, varchar, varchar)
+returns integer as '
+declare
+ p_category_id alias for $1; -- default null
+ p_short_name alias for $2;
+ p_description alias for $3; -- default null
+ p_forum_id alias for $4;
+ p_context_id alias for $5; -- default null
+ p_creation_date alias for $6; -- default now()
+ p_creation_user alias for $7; -- default null
+ p_creation_ip alias for $8; -- default null
+ p_object_type alias for $9; -- default ''bboard_category''
+ v_category_id integer;
+ v_context_id integer;
+begin
+ v_context_id := coalesce(p_context_id, p_forum_id);
+ v_category_id := acs_object__new (
+ p_category_id,
+ p_object_type,
+ p_creation_date,
+ p_creation_user,
+ p_creation_ip,
+ v_context_id
+ );
+
+ insert into bboard_categories
+ (category_id, short_name, description, forum_id)
+ values (v_category_id, p_short_name, p_description, p_forum_id);
+
+ return v_category_id;
+end;
+' language 'plpgsql';
+
+
+create function bboard_category__delete (integer)
+returns integer as '
+declare
+ p_category_id alias for $1;
+begin
+ delete from bboard_categories
+ where category_id = p_category_id;
+ PERFORM acs_object__delete(p_category_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_category__set_attrs (integer, varchar, varchar, integer)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_short_name alias for $2; -- default null
+ p_description alias for $3; -- default null
+ p_forum_id alias for $4; -- default null
+ v_check_category_id integer;
+begin
+ select count(category_id) into v_check_category_id
+ from bboard_categories
+ where category_id = p_category_id;
+ -- Not a category. Fail silently?
+ if v_check_category_id <> 1 then
+ return -1;
+ end if;
+ -- It is a category. Go for it.
+ if p_short_name is not null then
+ update bboard_categories set short_name = p_short_name
+ where category_id = p_category_id;
+ end if;
+ if p_description is not null then
+ update bboard_categories set description = p_description
+ where category_id = p_category_id;
+ end if;
+ if p_forum_id is not null then
+ update bboard_categories set forum_id = p_forum_id
+ where category_id = p_category_id;
+ update acs_objects set context_id = p_forum_id
+ where object_id = p_category_id;
+ end if;
+ return 0;
+end;
+' language 'plpgsql';
+
+create function bboard_category__subscribe (integer, integer)
+returns integer as '
+declare
+ p_category_id alias for $1;
+ p_subscriber_id alias for $2;
+begin
+ insert into bboard_category_subscribers (category_id, subscriber_id)
+ values (p_category_id, p_subscriber_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function name (integer)
+returns varchar as '
+declare
+ category_id alias for $1;
+ v_category_name varchar;
+begin
+ select short_name into v_category_name
+ from bboard_categories
+ where category_id = name.category_id;
+ return v_category_name;
+end;
+' language 'plpgsql';
+
+
+--------------- bboard_message package ----------------
+
+create function bboard_message__new (integer, integer, timestamp, integer,
+ varchar, varchar, varchar, varchar, text,
+ integer, timestamp, integer, varchar, varchar)
+returns integer as '
+declare
+ p_message_id alias for $1; -- default null
+ p_reply_to alias for $2; -- default null
+ p_sent_date alias for $3; -- default sysdate
+ p_sender alias for $4; -- default null
+ p_rfc822_id alias for $5; -- default null
+ p_title alias for $6; -- default null
+ p_mime_type alias for $7; -- default ''text/plain''
+ p_text alias for $8; -- default null
+ p_data alias for $9; -- default null
+ p_context_id alias for $10; -- default 0
+ p_creation_date alias for $11; -- default sysdate
+ p_creation_user alias for $12; -- default null
+ p_creation_ip alias for $13; -- default null
+ p_object_type alias for $14; -- default ''acs_message''
+ v_sent_date timestamp;
+begin
+ v_sent_date := coalesce(p_sent_date, now());
+
+ return acs_message__new (
+ p_message_id,
+ p_reply_to,
+ v_sent_date,
+ p_sender,
+ p_rfc822_id,
+ p_title,
+ null,
+ p_mime_type,
+ p_text,
+ p_data,
+ 0,
+ p_context_id,
+ p_creation_user,
+ p_creation_ip,
+ p_object_type,
+ ''t''
+ );
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__message_p (integer)
+returns char as '
+declare
+ p_message_id alias for $1;
+ v_check_message_id integer;
+begin
+ select count(message_id) into v_check_message_id
+ from acs_messages
+ where message_id = p_message_id;
+ if v_check_message_id = 1 then
+ return ''t'';
+ else
+ return ''f'';
+ end if;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__set_attrs (integer, integer, timestamp,
+ integer, varchar, varchar, integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ p_reply_to alias for $2; -- default null
+ p_sent_date alias for $3; -- default null
+ p_sender alias for $4; -- default null
+ p_title alias for $5; -- default null
+ p_mime_type alias for $6; -- default null
+ p_context_id alias for $7; -- default null
+ v_check_message_id integer;
+ v_revision_id integer;
+begin
+ if bboard_message__message_p(p_message_id) = ''f'' then
+ return -1;
+ end if;
+
+ -- modify the parts that are in acs_messages
+
+ if p_reply_to is not null then
+ update acs_messages set reply_to = p_reply_to
+ where message_id = p_message_id;
+ end if;
+ if p_sent_date is not null then
+ update acs_messages set sent_date = p_sent_date
+ where message_id = p_message_id;
+ end if;
+ if p_sender is not null then
+ update acs_messages set sender = p_sender
+ where message_id = p_message_id;
+ end if;
+
+ -- modify the parts that are in cr_revisions
+ if p_title is not null or p_mime_type is not null then
+ select live_revision into v_revision_id
+ from cr_items where item_id = p_message_id
+ for update;
+ if p_title is not null then
+ update cr_revisions set title = p_title
+ where revision_id = v_revision_id;
+ end if;
+ if p_mime_type is not null then
+ update cr_revisions set mime_type = p_mime_type
+ where revision_id = v_revision_id;
+ end if;
+ end if;
+
+ -- modify the context_id in acs_objects
+
+ if p_context_id is not null then
+ update acs_objects set context_id = p_context_id
+ where object_id = p_message_id;
+ end if;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__set_status (integer, integer, varchar)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ p_forum_id alias for $2;
+ p_status alias for $3;
+begin
+ if bboard_message__message_p(p_message_id) = ''f''
+ or bboard_forum__forum_p(p_forum_id) = ''f'' then
+ return -1;
+ end if;
+ delete from bboard_forum_message_map
+ where message_id = p_message_id
+ and forum_id = p_forum_id;
+ if p_status is not null then
+ insert into bboard_forum_message_map (forum_id, message_id, status)
+ values (p_forum_id, p_message_id, p_status);
+ end if;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__add_category (integer, integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ p_category_id alias for $2;
+begin
+ insert into bboard_category_message_map (message_id, category_id)
+ values (p_message_id, p_category_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__remove_category (integer, integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+ p_category_id alias for $2;
+begin
+ delete from bboard_category_message_map
+ where category_id = p_category_id
+ and message_id = p_message_id;
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__clear_categories (integer)
+returns integer as '
+declare
+ p_message_id alias for $1;
+begin
+ delete from bboard_category_message_map
+ where message_id = p_message_id;
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__subscribe (integer, integer)
+returns integer as '
+declare
+ p_thread_id alias for $1;
+ p_subscriber_id alias for $2;
+begin
+ insert into bboard_thread_subscribers (thread_id, subscriber_id)
+ values (p_thread_id, p_subscriber_id);
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__remove_thread (integer)
+returns integer as '
+declare
+ thread_id alias for $1;
+ child_val record;
+ message_val record;
+ image_p integer;
+ v_search_key varchar;
+begin
+ select tree_sortkey || ''%'' into v_search_key
+ from acs_messages
+ where message_id = thread_id;
+
+ for child_val in
+ select object_id as child_id, object_type
+ from acs_objects
+ where context_id in (select message_id
+ from acs_messages
+ where tree_sortkey like v_search_key)
+ loop
+ if child_val.object_type = ''acs_message'' then
+ perform 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
+ perform acs_message__delete_image(child_val.child_id);
+ else
+ perform acs_message__delete_file(child_val.child_id);
+ end if;
+ end if;
+ end loop;
+
+ for message_val in
+ select message_id
+ from acs_messages
+ where tree_sortkey like v_search_key
+ loop
+ perform acs_message__delete(message_val.message_id);
+ end loop;
+
+ return 0;
+end;
+' language 'plpgsql';
+
+
+create function bboard_message__remove (integer)
+returns integer as '
+declare
+ message_id alias for $1;
+ child_val record;
+ image_p integer;
+begin
+ for child_val in
+ select object_id as child_id, object_type
+ from acs_objects
+ where context_id = message_id
+ loop
+ if child_val.object_type = ''acs_message'' then
+ perform 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
+ perform acs_message__delete_image(child_val.child_id);
+ else
+ perform acs_message__delete_file(child_val.child_id);
+ end if;
+ end if;
+ end loop;
+
+ perform acs_message__delete(message_id);
+ return 0;
+end;
+' language 'plpgsql';
\ No newline at end of file
Index: openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-views.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-views.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/obsolete-packages/bboard/sql/postgresql/bboard-views.sql 29 Jun 2001 20:31:14 -0000 1.1
@@ -0,0 +1,59 @@
+--
+-- packages/bboard/sql/bboard-views.sql
+--
+-- @author Anukul Kapoor <akk@arsdigita.com>
+-- @author John Prevost <jmp@arsdigita.com>
+-- @creation-date 2001-02-05
+-- @cvs-id $Id: bboard-views.sql,v 1.1 2001/06/29 20:31:14 lukep Exp $
+--
+
+create view bboard_messages_all as
+ select m.message_id, m.reply_to, m.sent_date, m.sender,
+ m.title, m.mime_type, m.content, f.forum_id, f.status,
+ (select count(1) from acs_messages m2
+ where m2.message_id in (select mf.message_id
+ from bboard_forum_message_map mf)
+ and m2.tree_sortkey like m.tree_sortkey || '%')
+ as num_replies,
+ (select max(sent_date) from acs_messages m2
+ where m2.message_id in (select mf.message_id
+ from bboard_forum_message_map mf)
+ and m2.tree_sortkey like m.tree_sortkey || '%')
+ as last_reply_date
+ from acs_messages_all m, bboard_forum_message_map f
+ where m.message_id = f.message_id;
+
+create view bboard_messages_by_category as
+ select msg.*, cat.category_id
+ from bboard_messages_all msg, bboard_category_message_map cat
+ where msg.message_id = cat.message_id
+ UNION
+ select msg.*, NULL as category_id
+ from bboard_messages_all msg
+ where 0 = (select count(1) from bboard_category_message_map cat
+ where msg.message_id = cat.message_id);
+
+
+-- create or replace view bboard_messages_all as
+-- select m.message_id, m.reply_to, m.sent_date, m.sender,
+-- m.title, m.mime_type, m.content, f.forum_id, f.status,
+-- (select count(1) from acs_messages m2
+-- where m2.message_id in (select mf.message_id
+-- from bboard_forum_message_map mf
+-- where mf.forum_id = f.forum_id)
+-- start with m2.message_id = m.message_id
+-- connect by m2.reply_to = prior m2.message_id) as num_replies,
+-- (select max(sent_date) from acs_messages m2
+-- where m2.message_id in (select mf.message_id
+-- from bboard_forum_message_map mf
+-- where mf.forum_id = f.forum_id)
+-- start with m2.message_id = m.message_id
+-- connect by m2.reply_to = prior m2.message_id) as last_reply_date
+-- from acs_messages_all m, bboard_forum_message_map f
+-- where m.message_id = f.message_id;
+
+-- create or replace view bboard_messages_by_category as
+-- select msg.*, cat.category_id
+-- from bboard_messages_all msg, bboard_category_message_map cat
+-- where msg.message_id = cat.message_id(+);
+