Index: openacs-4/contrib/packages/general-comments/sql/oracle/general-comments-plsql-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/general-comments/sql/oracle/Attic/general-comments-plsql-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/contrib/packages/general-comments/sql/oracle/general-comments-plsql-create.sql 19 Jun 2003 18:04:03 -0000 1.1 +++ openacs-4/contrib/packages/general-comments/sql/oracle/general-comments-plsql-create.sql 12 Mar 2004 21:14:01 -0000 1.2 @@ -1,106 +1,170 @@ ---TODO: port to oracle!! - --- plpgsql functions for general-comments +-- plsql functions for general-comments -- Dave Bauer dave@thedesignexperience.org -- 2003-05-04 +-- Ported to Oracle by aegrumet@alum.mit.edu +-- 2004-03-12 -create or replace package general_comment as +create or replace package general_comment +is + function new ( + comment_id in general_comments.comment_id%TYPE default null, + object_id in acs_objects.object_id%TYPE default null, + package_id in apm_packages.package_id%TYPE default null, + parent_id in cr_items.parent_id%TYPE default null, + context_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + content in cr_revisions.content%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default null, + live_p in char + ) return general_comments.comment_id%TYPE; -end; -show errors + function revise ( + comment_id in general_comments.comment_id%TYPE default null, + title in cr_revisions.title%TYPE, + content in cr_revisions.content%TYPE, + mime_type in cr_revisions.mime_type%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + live_p in char + ) return cr_revisions.revision_id%TYPE; + + procedure del ( + comment_id in general_comments.comment_id%TYPE default null + ); + +end general_comment; / -create functions general_comment__new( - ) returns integer as ' -declare - p_comment_id alias for $1; - p_object_id alias for $2; - p_package_id alias for $3; - p_parent_id alias for $4; - p_context_id alias for $5; - p_title alias for $6; - p_description alias for $7 - p_content alias for $8; - p_creation_user alias for $9; - p_creation_date alias for $10; - p_creation_ip alias for $11; - p_mime_type alias for $12; - v_item_id cr_items.item_id%TYPE; -begin - v_item_id := select nextval(acs_object_id_seq); +show errors - PERFORM content_item__new( - ''general_comment_'' || p_parent_id || ''_'' || v_item_id, - p_parent_id, - v_item_id, - NULL, - p_creation_date, - p_creation_user, - p_context_id, - p_creation_ip, - ''content_item'', - ''general_comment'', - p_title, - p_description, - p_mime_type, - p_content, - ''text'' - ); - v_revision_id := select latest_revision - from cr_items - where item_id=v_item_id +create or replace package body general_comment +is + function new ( + comment_id in general_comments.comment_id%TYPE default null, + object_id in acs_objects.object_id%TYPE default null, + package_id in apm_packages.package_id%TYPE default null, + parent_id in cr_items.parent_id%TYPE default null, + context_id in acs_objects.object_id%TYPE, + title in cr_revisions.title%TYPE, + description in cr_revisions.description%TYPE, + content in cr_revisions.content%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + mime_type in cr_revisions.mime_type%TYPE default null, + live_p in char + ) return general_comments.comment_id%TYPE + is + v_item_id cr_items.item_id%TYPE; + v_revision_id cr_revisions.revision_id%TYPE; + begin - PERFORM content_revision__new( - - ); + if comment_id is null then + select acs_object_id_seq.nextval into v_item_id from dual; + else + v_item_id := general_comment.new.comment_id; + end if; - insert into general_comments - ( - comment_id, - object_id - ) - values - ( - p_comment_id, - p_object_id - ); + v_item_id := content_item.new( + name => 'general_comment_' || v_item_id, + parent_id => general_comment.new.parent_id, + item_id => v_item_id, + locale => NULL, + creation_date => general_comment.new.creation_date, + creation_user => general_comment.new.creation_user, + context_id => general_comment.new.context_id, + creation_ip => general_comment.new.creation_ip, + item_subtype => 'content_item', + content_type => 'general_comment', + title => general_comment.new.title, + description => general_comment.new.description, + mime_type => general_comment.new.mime_type, + nls_language => null, + data => general_comment.new.content + ); - perform acs_permission__grant_permission(p_comment_id, - p_creation_user, - ''read''); - perform acs_permission__grant_permission(p_comment_id, - p_creation_user, - ''write''); + insert into general_comments + ( + comment_id, + object_id + ) + values + ( + v_item_id, + general_comment.new.object_id + ); -return p_comment_id; -end;' language 'plpgsql'; + select latest_revision into v_revision_id + from cr_items + where item_id = v_item_id; -create or replace function general_comment__del( - integer - ) returns integer as ' -declare - p_comment_id alias for $1; -begin - -- remove permssions from the object - delete from acs_permissions where object_id=:p_comment_id; - -- handle attachments and child comments for threaded comments? + if general_comment.new.live_p = 't' then + content_item.set_live_revision(v_revision_id); + end if; - -- do we need to remove live_revision and latest_revision first? - perform content_item__delete(p_comment_id); + return v_item_id; + end; - delete from general_comments where comment_id=:p_comment_id; + function revise ( + comment_id in general_comments.comment_id%TYPE default null, + title in cr_revisions.title%TYPE, + content in cr_revisions.content%TYPE, + mime_type in cr_revisions.mime_type%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + live_p in char + ) return cr_revisions.revision_id%TYPE + is + v_revision_id cr_revisions.revision_id%TYPE; + begin + v_revision_id := content_revision.new ( + title => general_comment.revise.title, + description => NULL, + publish_date => sysdate, + mime_type => general_comment.revise.mime_type, + nls_language => NULL, + data => general_comment.revise.content, + item_id => general_comment.revise.comment_id, + revision_id => NULL, + creation_date => general_comment.revise.creation_date, + creation_user => general_comment.revise.creation_user, + creation_ip => general_comment.revise.creation_ip + ); -return NULL; -end;' language 'plpgsql'; + if general_comment.revise.live_p = 't' then + content_item.set_live_revision ( + general_comment.revise.comment_id, + v_revision_id + ); + end if; -create or replace function general_comment__revise( - integer - ) returns integer as ' -declare - p_comment_id alias for $1 -begin - -- create a new revision of a comment - -- does the UI even offer this option? -return NULL; -end;' language 'plpgsql'; + return v_revision_id; + end; + procedure del ( + comment_id in general_comments.comment_id%TYPE default null + ) + is + begin + -- remove permssions from the object + delete from acs_permissions where object_id = general_comment.del.comment_id; + + -- handle attachments and child comments for threaded comments? + + -- do we need to remove live_revision and latest_revision first? + content_item.del(general_comment.del.comment_id); + + delete from general_comments where comment_id = general_comment.del.comment_id; + + end; + +end general_comment; +/ +show errors +