-- Package create for faq -- -- @author @jennie.ybos.net,@wirth.ybos.net,openacs port @samir.symphinity.com -- -- @cvs-id $Id: faq-package-create.sql,v 1.2 2005/03/18 18:48:07 andrewg Exp $ -- create or replace package faq as function new_faq ( faq_id in faqs.faq_id%TYPE default null, faq_name in faqs.faq_name%TYPE, separate_p in faqs.separate_p%TYPE default 'f', object_type in acs_objects.object_type%TYPE default 'faq', 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, context_id in acs_objects.context_id%TYPE default null ) return acs_objects.object_id%TYPE; procedure delete_faq ( faq_id in faqs.faq_id%TYPE ); function new_q_and_a ( entry_id in faq_q_and_as.entry_id%TYPE default null, faq_id in faq_q_and_as.faq_id%TYPE, question in faq_q_and_as.question%TYPE, answer in faq_q_and_as.answer%TYPE, sort_key in faq_q_and_as.sort_key%TYPE, object_type in acs_objects.object_type%TYPE default 'faq_q_and_a', 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, context_id in acs_objects.context_id%TYPE default null ) return acs_objects.object_id%TYPE; procedure delete_q_and_a ( entry_id in faq_q_and_as.entry_id%TYPE ); procedure clone ( old_package_id in apm_packages.package_id%TYPE default NULL, new_package_id in apm_packages.package_id%TYPE default NULL ); end faq; / show errors create or replace package body faq as function new_q_and_a ( entry_id in faq_q_and_as.entry_id%TYPE default null, faq_id in faq_q_and_as.faq_id%TYPE, question in faq_q_and_as.question%TYPE, answer in faq_q_and_as.answer%TYPE, sort_key in faq_q_and_as.sort_key%TYPE, object_type in acs_objects.object_type%TYPE default 'faq_q_and_a', 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, context_id in acs_objects.context_id%TYPE default null ) return acs_objects.object_id%TYPE is v_entry_id faq_q_and_as.entry_id%TYPE; v_package_id acs_objects.package_id%TYPE; begin select package_id into v_package_id from acs_objects where object_id = new_q_and_a.faq_id; v_entry_id := acs_object.new ( object_id => entry_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id, package_id => v_package_id, title => question ); insert into faq_q_and_as (entry_id, faq_id, question, answer, sort_key) values (v_entry_id, new_q_and_a.faq_id, new_q_and_a.question, new_q_and_a.answer, new_q_and_a.sort_key); return v_entry_id; end new_q_and_a; procedure delete_q_and_a ( entry_id in faq_q_and_as.entry_id%TYPE ) is begin delete from faq_q_and_as where entry_id = faq.delete_q_and_a.entry_id; acs_object.del(entry_id); end delete_q_and_a; function new_faq ( faq_id in faqs.faq_id%TYPE default null, faq_name in faqs.faq_name%TYPE, separate_p in faqs.separate_p%TYPE default 'f', object_type in acs_objects.object_type%TYPE default 'faq', 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, context_id in acs_objects.context_id%TYPE default null ) return acs_objects.object_id%TYPE is v_faq_id faqs.faq_id%TYPE; begin v_faq_id := acs_object.new ( object_id => faq_id, object_type => object_type, creation_date => creation_date, creation_user => creation_user, creation_ip => creation_ip, context_id => context_id, package_id => context_id, title => faq_name ); insert into faqs (faq_id, faq_name,separate_p) values (v_faq_id, new_faq.faq_name,new_faq.separate_p); return v_faq_id; end new_faq; procedure delete_faq ( faq_id faqs.faq_id%TYPE ) is begin -- Because q_and_a's are objects, we need to -- loop through a list of them, and call an explicit -- delete function for each one. (i.e. each -- entry_id) declare cursor q_and_a_cur is select entry_id from faq_q_and_as where faq_id = faq.delete_faq.faq_id; begin for entry_list in q_and_a_cur loop delete_q_and_a(entry_list.entry_id); end loop; end; delete from faqs where faq_id=faq.delete_faq.faq_id; acs_object.del(faq_id); end delete_faq; procedure clone ( old_package_id in apm_packages.package_id%TYPE default null, new_package_id in apm_packages.package_id%TYPE default null ) is v_faq_id faqs.faq_id%TYPE; v_entry_id faq_q_and_as.entry_id%TYPE; begin -- get all the faqs belonging to the old package, -- and create new faqs for the new package for one_faq in (select * from acs_objects o, faqs f where o.object_id = f.faq_id and o.context_id = faq.clone.old_package_id) loop -- faq is "scoped" by using the acs_objects.context_id v_faq_id := faq.new_faq ( faq_name => one_faq.faq_name, separate_p => one_faq.separate_p, context_id => faq.clone.new_package_id ); for entry in (select * from faq_q_and_as f where faq_id = one_faq.faq_id) loop -- now (surprise!) copy all the entries of this faq v_entry_id := faq.new_q_and_a ( context_id => entry.faq_id, faq_id=> v_faq_id, question => entry.question, answer => entry.answer, sort_key => entry.sort_key ); end loop; end loop; end clone; end faq; / show errors