Index: openacs-4/packages/faq/sql/oracle/faq-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/faq/sql/oracle/faq-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/faq/sql/oracle/faq-create.sql 30 Sep 2003 12:10:07 -0000 1.4 +++ openacs-4/packages/faq/sql/oracle/faq-create.sql 1 Apr 2004 22:52:45 -0000 1.5 @@ -1,6 +1,9 @@ --- @jennie.ybos.net --- @wirth.ybos.net - +-- Datamodel create for faq +-- +-- @author @jennie.ybos.net,@wirth.ybos.net,openacs port @samir.symphinity.com +-- +-- @cvs-id $Id$ +-- begin acs_object_type.create_type ( supertype => 'acs_object', @@ -57,209 +60,6 @@ -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; - begin - 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 - ); - 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 - ); - 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 - - declare default_context acs_objects.object_id%TYPE; registered_users acs_objects.object_id%TYPE; @@ -321,3 +121,5 @@ end; / + +@@ faq-package-create.sql \ No newline at end of file