Index: openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/bboard/tcl/bboard-procs-postgresql.xql 28 Aug 2003 09:41:47 -0000 1.14 @@ -0,0 +1,455 @@ + + + + postgresql7.1 + + + + select bboard_forum__forum_p(:forum_id); + + + + + + + select bboard_forum__new ( + :forum_id, + :short_name, + :charter, + :moderated_p, + :bboard_id, + :context_id, + now(), + :creation_user, + :creation_ip, + 'bboard_forum' + ); + + + + + + + select bboard_forum__set_attrs ( + :forum_id, + :short_name, + :charter, + :moderated_p, + :bboard_id + ); + + + + + + + select bboard_category__category_p(:category_id); + + + + + + + select bboard_category__new ( + :category_id, + :short_name, + :description, + :forum_id, + null, + now(), + null, + null, + 'bboard_category' + ); + + + + + + + select bboard_category__set_attrs ( + :category_id, + :short_name, + :description, + :forum_id + ); + + + + + + + select bboard_message__new ( + :message_id, + :reply_to, + :sent_date, + :sender, + null, -- rfc822_id + :title, + :mime_type, + null, -- text + null, -- data + :context_id, + now(), + :creation_user, + :creation_ip, + 'bboard_message' + ); + + + + + + + + update cr_revisions + set content = :content + where revision_id = :revision_id + + + + + + + + + select bboard_message__set_attrs ( + :message_id, + :reply_to, + :sent_date, + :sender, + :title, + :mime_type, + :context_id + ); + + + + + + + + update cr_revisions + set content = :content + where revision_id = :revision_id + + + + + + + + select bboard_message__clear_categories ( :message_id ); + + + + + + + + + select bboard_message__add_category ( + :message_id, + :category_id + ); + + + + + + + + + select bboard_message__remove_category ( + :message_id, + :category_id + ); + + + + + + + + + select bboard_message__set_status ( + :message_id, + :forum_id, + :status + ); + + + + + + + + + select bboard_forum__subscribe ( + :forum_id, + :subscriber_id + ); + + + + + + + + + select bboard_category__subscribe ( + :category_id, + :subscriber_id + ); + + + + + + + + + select bboard_message__subscribe ( + :thread_id, + :subscriber_id + ); + + + + + + + + + select count(*) as subscribed_p + from bboard_thread_subscribers bs, acs_messages m, acs_messages m2 + where bs.subscriber_id = :user_id + and bs.thread_id = m.message_id + and m2.message_id = :message_id + and m.tree_sortkey between m2.tree_sortkey and tree_right(m2.tree_sortkey) + + + + + + + + + select bboard_forum__forum_containing_message(:message_id) as forum_id + + + + + + + + + + select acs_message__first_ancestor(:message_id) as ancestor_id + + + + + + + + + + select acs_message__new_image ( + :message_id, + :file_id, + :short_filename, + :title, + null, -- description + :mime_type, + null, -- data + :width, + :height, + now(), + :user_id, + :creation_ip, + 't', -- is_live + 'file' + ); + + + + + + + + + select content_item__get_latest_revision(:file_id) as revision_id + + + + + + + + + update cr_revisions + set content = :filename, + content_length = :file_size + where revision_id = :revision_id + + + + + + + select acs_message__new_file ( + :message_id, + :file_id, + :short_filename, + :title, + null, -- description + :mime_type, + null, -- content + now(), + :user_id, + :creation_ip, + 't', -- is_live + 'file' + ); + + + + + + + + + select content_item__get_latest_revision(:file_id) as revision_id + + + + + + + + + update cr_revisions + set content = :filename, + content_length = :file_size + where revision_id = :revision_id + + + + + + + + select image_id + from images + where image_id = content_item__get_latest_revision(:file_id) + + + + + + + + + select acs_message__delete_image(:file_id); + + + + + + + + + select acs_message__delete_file(:file_id); + + + + + + + + + + select object_id as message_id + from acs_objects o + where o.object_type = 'bboard_message' + and not exists (select 1 + from bboard_forum_message_map bfmm, acs_objects o2 + where o2.object_id = bfmm.message_id and + o.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey)) + + + + + + + + + update cr_items + set latest_revision=null, live_revision=null + where item_id = :message_id + + + + + + + + + select bboard_message__remove(:message_id); + + + + + + + + select s.subscriber_id as recipient_id, s.thread_id as grouping_id, + now() as wait_until + from bboard_thread_subscribers s, acs_messages m, + (select tree_ancestor_keys(acs_message_get_tree_sortkey(:message_id)) as tree_sortkey) parents + where s.thread_id = m.message_id + and m.tree_sortkey = parents.tree_sortkey + and exists (select 1 + from all_object_party_privilege_map map + where map.object_id = :message_id and + map.party_id = s.subscriber_id and + privilege = 'bboard_read_message') + + + + + + + + select s.subscriber_id as recipient_id, s.category_id as grouping_id, + now() as wait_until + from bboard_category_subscribers s, bboard_category_message_map m + where m.message_id = :message_id + and s.category_id = m.category_id + and exists (select 1 + from all_object_party_privilege_map map + where map.object_id = :message_id and + map.party_id = s.subscriber_id and + privilege = 'bboard_read_message') + + + + + + + + select s.subscriber_id as recipient_id, s.forum_id as grouping_id, + now() as wait_until + from bboard_forum_subscribers s, bboard_forum_message_map m + where m.message_id = :message_id + and s.forum_id = m.forum_id + and exists (select 1 + from all_object_party_privilege_map map + where map.object_id = :message_id and + map.party_id = s.subscriber_id and + privilege = 'bboard_read_message') + + + +