Index: openacs-4/packages/acs-reference/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-reference/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-reference/sql/oracle/upgrade/upgrade-0.2d-0.3d.sql 8 Oct 2003 16:32:47 -0000 1.1 @@ -0,0 +1,149 @@ +-- +-- packages/acs-reference/sql/acs-reference-create.sql +-- +-- @author jon@arsdigita.com +-- @creation-date 2000-11-21 +-- @cvs-id $Id: upgrade-0.2d-0.3d.sql,v 1.1 2003/10/08 16:32:47 mohanp Exp $ + + +create or replace package acs_reference +as + function new ( + repository_id in acs_reference_repositories.repository_id%TYPE default null, + table_name in acs_reference_repositories.table_name%TYPE, + internal_data_p in acs_reference_repositories.internal_data_p%TYPE default 'f', + package_name in acs_reference_repositories.package_name%TYPE default null, + last_update in acs_reference_repositories.last_update%TYPE default sysdate, + source in acs_reference_repositories.source%TYPE default null, + source_url in acs_reference_repositories.source_url%TYPE default null, + effective_date in acs_reference_repositories.effective_date%TYPE default sysdate, + expiry_date in acs_reference_repositories.expiry_date%TYPE default null, + notes in acs_reference_repositories.notes%TYPE default empty_blob(), + 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, + object_type in acs_objects.object_type%TYPE default 'acs_reference_repository', + first_names in persons.first_names%TYPE default null, + last_name in persons.last_name%TYPE default null + ) return acs_objects.object_id%TYPE; + + procedure del ( + repository_id in acs_reference_repositories.repository_id%TYPE + ); + + function is_expired_p ( + repository_id integer + ) return char; + + end acs_reference; +/ +show errors + + +create or replace package body acs_reference +as + function new ( + repository_id in acs_reference_repositories.repository_id%TYPE default null, + table_name in acs_reference_repositories.table_name%TYPE, + internal_data_p in acs_reference_repositories.internal_data_p%TYPE default 'f', + package_name in acs_reference_repositories.package_name%TYPE default null, + last_update in acs_reference_repositories.last_update%TYPE default sysdate, + source in acs_reference_repositories.source%TYPE default null, + source_url in acs_reference_repositories.source_url%TYPE default null, + effective_date in acs_reference_repositories.effective_date%TYPE default sysdate, + expiry_date in acs_reference_repositories.expiry_date%TYPE default null, + notes in acs_reference_repositories.notes%TYPE default empty_blob(), + 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, + object_type in acs_objects.object_type%TYPE default 'acs_reference_repository', + first_names in persons.first_names%TYPE default null, + last_name in persons.last_name%TYPE default null + ) return acs_objects.object_id%TYPE + is + v_repository_id acs_reference_repositories.repository_id%TYPE; + v_maintainer_id persons.person_id%TYPE; + begin + v_repository_id := acs_object.new ( + object_id => repository_id, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + object_type => object_type + ); + + if first_names is not null and last_name is not null then + v_maintainer_id := person.new ( + first_names => first_names, + last_name => last_name, + email => null + ); + else + v_maintainer_id := null; + end if; + + insert into acs_reference_repositories + (repository_id, + table_name, + internal_data_p, + last_update, + package_name, + source, + source_url, + effective_date, + expiry_date, + maintainer_id, + notes) + values + (v_repository_id, + table_name, + internal_data_p, + last_update, + package_name, + source, + source_url, + effective_date, + expiry_date, + v_maintainer_id, + notes); + return v_repository_id; + end new; + + procedure del ( + repository_id in acs_reference_repositories.repository_id%TYPE + ) + is + v_maintainer_id integer; + begin + select maintainer_id into v_maintainer_id + from acs_reference_repositories + where repository_id = acs_reference.del.repository_id; + + delete from acs_reference_repositories + where repository_id = acs_reference.del.repository_id; + + acs_object.del(repository_id); + person.del(v_maintainer_id); + + end del; + + function is_expired_p ( + repository_id integer + ) return char + is + v_expiry_date date; + begin + select expiry_date into v_expiry_date + from acs_reference_repositories + where repository_id = is_expired_p.repository_id; + + if nvl(v_expiry_date,sysdate+1) < sysdate then + return 't'; + else + return 'f'; + end if; + end; + +end acs_reference; +/ +show errors Index: openacs-4/packages/address-book/sql/oracle/upgrade/upgrade-4.0a2-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/address-book/sql/oracle/upgrade/upgrade-4.0a2-5.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/address-book/sql/oracle/upgrade/upgrade-4.0a2-5.0d1.sql 8 Oct 2003 16:33:56 -0000 1.1 @@ -0,0 +1,622 @@ +-- +-- packages/address-book/sql/address-book-create.sql +-- +-- @author jmileham@arsdigita.com +-- @creation_date 2000-12-04 +-- @cvs-id $Id: upgrade-4.0a2-5.0d1.sql,v 1.1 2003/10/08 16:33:56 mohanp Exp $ +-- + +----------------------------------- +-- AB_CONTACT_ATTR_TYPES PACKAGE -- +----------------------------------- + +create or replace package ab_contact_attr_type +as + + function new ( + type_id in ab_contact_attr_types.type_id%TYPE + default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact_attr_type', + 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, + type_name in ab_contact_attr_types.type_name%TYPE, + type_key in ab_contact_attr_types.type_key%TYPE + ) return ab_contact_attr_types.type_id%TYPE; + + function name ( + type_id in ab_contact_attr_types.type_id%TYPE + ) return ab_contact_attr_types.type_name%TYPE; + + procedure del ( + type_id in ab_contact_attr_types.type_id%TYPE + ); + +end ab_contact_attr_type; +/ +show errors + + +create or replace package body ab_contact_attr_type +as + + function new ( + type_id in ab_contact_attr_types.type_id%TYPE + default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact_attr_type', + 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, + type_name in ab_contact_attr_types.type_name%TYPE, + type_key in ab_contact_attr_types.type_key%TYPE + ) return ab_contact_attr_types.type_id%TYPE + is + v_type_id ab_contact_attr_types.type_id%TYPE; + begin + v_type_id := + acs_object.new(object_id => type_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into ab_contact_attr_types + (type_id, type_name, type_key) + values + (v_type_id, new.type_name, new.type_key); + + return v_type_id; + end new; + + function name ( + type_id in ab_contact_attr_types.type_id%TYPE + ) return ab_contact_attr_types.type_name%TYPE + is + v_type_name ab_contact_attr_types.type_name%TYPE; + begin + select type_name + into v_type_name + from ab_contact_attr_types + where type_id = name.type_id; + return v_type_name; + end name; + + + procedure del ( + type_id in ab_contact_attr_types.type_id%TYPE + ) + is + begin + acs_object.del(type_id); + end del; + +end ab_contact_attr_type; +/ +show errors + + +----------------------------- +-- AB_CONTACT_ATTR PACKAGE -- +----------------------------- + + +create or replace package ab_contact_attr +as + + function new ( + attr_id in ab_contact_attrs.attr_id%TYPE + default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact_attr', + 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, + contact_id in ab_contact_attrs.contact_id%TYPE, + type_key in ab_contact_attrs.type_key%TYPE, + value in ab_contact_attrs.value%TYPE, + before_attr_id in ab_contact_attrs.attr_id%TYPE + default null + ) return ab_contact_attrs.attr_id%TYPE; + + procedure swap_sort ( + attr_id_one in ab_contact_attrs.attr_id%TYPE, + attr_id_two in ab_contact_attrs.attr_id%TYPE + ); + + procedure del ( + attr_id in ab_contact_attrs.attr_id%TYPE + ); + +end ab_contact_attr; +/ +show errors + + +create or replace package body ab_contact_attr +as + + function new ( + attr_id in ab_contact_attrs.attr_id%TYPE + default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact_attr', + 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, + contact_id in ab_contact_attrs.contact_id%TYPE, + type_key in ab_contact_attrs.type_key%TYPE, + value in ab_contact_attrs.value%TYPE, + before_attr_id in ab_contact_attrs.attr_id%TYPE + default null + ) return ab_contact_attrs.attr_id%TYPE + is + v_attr_id ab_contact_attrs.attr_id%TYPE; + begin + + v_attr_id := + acs_object.new ( + object_id => attr_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + if new.before_attr_id is not null then + + -- Shift the attributes down to accomidate if they specified + -- before_attr_id + + update ab_contact_attrs aca + set sort_key = sort_key + 1 + where sort_key >= (select sort_key + from ab_contact_attrs + where attr_id = new.before_attr_id) + and exists (select 1 + from ab_contact_attrs + where attr_id = aca.attr_id + and contact_id = new.contact_id); + + -- Insert into the newly create hole + + insert into ab_contact_attrs + (attr_id, contact_id, type_key, value, sort_key) + select new.v_attr_id, new.contact_id, new.type_key, new.value, (sort_key - 1) + from ab_contact_attrs + where attr_id = new.before_attr_id; + + else + + -- Otherwise, tack it on the end + + insert into ab_contact_attrs + (attr_id, contact_id, type_key, value, sort_key) + select new.v_attr_id, new.contact_id, new.type_key, new.value, nvl(max(sort_key) + 1, 1) + from ab_contact_attrs + where contact_id = new.contact_id; + + end if; + + return v_attr_id; + end new; + + + procedure swap_sort ( + attr_id_one in ab_contact_attrs.attr_id%TYPE, + attr_id_two in ab_contact_attrs.attr_id%TYPE + ) + is + begin + + -- The exists clause verifies that the application is trying + -- to perform a legal swap (one between two attributes of + -- the same contact_id). + + update ab_contact_attrs aca1 + set sort_key = (select sort_key + from ab_contact_attrs aca2 + where aca1.attr_id = decode(aca2.attr_id, + swap_sort.attr_id_one, swap_sort.attr_id_two, + swap_sort.attr_id_two, swap_sort.attr_id_one)) + where attr_id in (swap_sort.attr_id_one, swap_sort.attr_id_two) + and exists (select 1 from ab_contact_attrs aca3, ab_contact_attrs aca4 + where aca3.attr_id = swap_sort.attr_id_one + and aca4.attr_id = swap_sort.attr_id_two + and aca3.contact_id = aca4.contact_id); + + end swap_sort; + + + procedure del ( + attr_id in ab_contact_attrs.attr_id%TYPE + ) + is + begin + acs_object.del(attr_id); + end del; + +end ab_contact_attr; +/ +show errors + + + +create or replace package ab_contact_rel +as + + function new ( + rel_id in ab_contact_rels.rel_id%TYPE default null, + rel_type in acs_rels.rel_type%TYPE default 'ab_contact_rel', + object_id_one in acs_rels.object_id_one%TYPE, + object_id_two in acs_rels.object_id_two%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + category in ab_contact_rels.category%TYPE default null + ) return ab_contact_rels.rel_id%TYPE; + + procedure del ( + rel_id in ab_contact_rels.rel_id%TYPE + ); + +end ab_contact_rel; +/ +show errors + + + +create or replace package body ab_contact_rel +as + + function new ( + rel_id in ab_contact_rels.rel_id%TYPE default null, + rel_type in acs_rels.rel_type%TYPE default 'ab_contact_rel', + object_id_one in acs_rels.object_id_one%TYPE, + object_id_two in acs_rels.object_id_two%TYPE, + creation_user in acs_objects.creation_user%TYPE default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + category in ab_contact_rels.category%TYPE default null + ) return ab_contact_rels.rel_id%TYPE + is + v_rel_id ab_contact_rels.rel_id%TYPE; + begin + v_rel_id := + acs_rel.new ( + rel_id => rel_id, + rel_type => rel_type, + object_id_one => object_id_one, + object_id_two => object_id_two, + context_id => object_id_one, + creation_user => creation_user, + creation_ip => creation_ip + ); + + insert into ab_contact_rels + (rel_id, category) + values + (v_rel_id, new.category); + return v_rel_id; + end new; + + procedure del ( + rel_id in ab_contact_rels.rel_id%TYPE + ) + is + begin + acs_object.del(rel_id); + end del; + +end ab_contact_rel; +/ +show errors + + +------------------------ +-- AB_CONTACT PACKAGE -- +------------------------ + +create or replace package ab_contact +as + + function new ( + contact_id in ab_contacts.contact_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact', + 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, + first_names in ab_contacts.first_names%TYPE default null, + last_name in ab_contacts.last_name%TYPE default null, + title in ab_contacts.title%TYPE default null, + organization in ab_contacts.organization%TYPE default null, + object_id in acs_objects.object_id%TYPE default null, + category in ab_contact_rels.category%TYPE default null + ) return ab_contacts.contact_id%TYPE; + + function name ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + + procedure del ( + contact_id in ab_contacts.contact_id%TYPE, + delete_orphan_addresses_p in char default 't' + ); + + function work_phone ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + + function home_phone ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + + function fax ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + + function other ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + + function email ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2; + +end ab_contact; +/ +show errors + + + +create or replace package body ab_contact +as + + function new ( + contact_id in ab_contacts.contact_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'ab_contact', + 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, + first_names in ab_contacts.first_names%TYPE default null, + last_name in ab_contacts.last_name%TYPE default null, + title in ab_contacts.title%TYPE default null, + organization in ab_contacts.organization%TYPE default null, + object_id in acs_objects.object_id%TYPE default null, + category in ab_contact_rels.category%TYPE default null + ) return ab_contacts.contact_id%TYPE + is + v_contact_id ab_contacts.contact_id%TYPE; + v_rel_id ab_contact_rels.rel_id%TYPE; + begin + v_contact_id := + acs_object.new(object_id => contact_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id); + + -- If they supplied an object_id, relate it: + if object_id is not null then + v_rel_id := + ab_contact_rel.new(object_id_one => object_id, + object_id_two => v_contact_id, + creation_user => creation_user, + creation_ip => creation_ip, + category => category); + end if; + + insert into ab_contacts + (contact_id, first_names, last_name, + title, organization) + values + (v_contact_id, new.first_names, new.last_name, + new.title, new.organization); + + + return v_contact_id; + end new; + + function name ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_name varchar2(400); + begin + select first_names||' '||last_name + into v_name + from ab_contacts + where contact_id = name.contact_id; + + return v_name; + end name; + + procedure del ( + contact_id in ab_contacts.contact_id%TYPE, + delete_orphan_addresses_p in char default 't' + ) + is + v_ab_contact_attrs_row ab_contact_attrs%ROWTYPE; + v_addresses_located_row pl_addresses_located%ROWTYPE; + cursor address_is_orphan_p_cursor (address_id in pl_addresses.address_id%TYPE) is + select decode(count(*),0,'t','f') from place_element_map where place_id = address_is_orphan_p_cursor.address_id; + v_address_is_orphan_p char(1); + cursor subplace_rel_cursor (address_id in pl_addresses.address_id%TYPE) is + select sr.rel_id + from subplace_rels sr, + acs_rels ar + where ar.rel_id = sr.rel_id + and ar.object_id_two = subplace_rel_cursor.address_id; + v_rel_id subplace_rels.rel_id%TYPE; + begin + -- First blow away attributes + for v_ab_contact_attrs_row in (select attr_id from ab_contact_attrs where contact_id = ab_contact.del.contact_id) loop + ab_contact_attr.del(v_ab_contact_attrs_row.attr_id); + end loop; + + -- Then iterate through address location relations + + for v_addresses_located_row in (select * from pl_addresses_located where locatee_id = ab_contact.del.contact_id) loop + + -- Delete the rel + subplace_rel.del(v_addresses_located_row.rel_id); + + + -- If we're deleting orphans then + if delete_orphan_addresses_p = 't' then + + -- Check if the address is an orphan + open address_is_orphan_p_cursor(address_id => v_addresses_located_row.address_id); + fetch address_is_orphan_p_cursor into v_address_is_orphan_p; + close address_is_orphan_p_cursor; + if v_address_is_orphan_p = 't' then + + -- Delete the address's subplace_relation + open subplace_rel_cursor(address_id => v_addresses_located_row.address_id); + fetch subplace_rel_cursor into v_rel_id; + if not subplace_rel_cursor%NOTFOUND then + subplace_rel.del(v_rel_id); + end if; + close subplace_rel_cursor; + -- Delete the address itself + pl_address.del(v_addresses_located_row.address_id); + + end if; + + end if; + + end loop; + + acs_object.del(contact_id); + end del; + + function work_phone ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_value ab_contact_attrs.value%TYPE; + begin + select value + into v_value + from ab_contact_attrs + where contact_id = work_phone.contact_id + and sort_key = (select min(sort_key) + from ab_contact_attrs + where contact_id = work_phone.contact_id + and type_key = 'work_phone'); + return v_value; + end work_phone; + + + function home_phone ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_value ab_contact_attrs.value%TYPE; + begin + select value + into v_value + from ab_contact_attrs + where contact_id = home_phone.contact_id + and sort_key = (select min(sort_key) + from ab_contact_attrs + where contact_id = home_phone.contact_id + and type_key = 'home_phone'); + return v_value; + end home_phone; + + + function fax ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_value ab_contact_attrs.value%TYPE; + begin + select value + into v_value + from ab_contact_attrs + where contact_id = fax.contact_id + and sort_key = (select min(sort_key) + from ab_contact_attrs + where contact_id = fax.contact_id + and type_key = 'fax'); + return v_value; + end fax; + + + function other ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_value ab_contact_attrs.value%TYPE; + begin + select value + into v_value + from ab_contact_attrs + where contact_id = other.contact_id + and sort_key = (select min(sort_key) + from ab_contact_attrs + where contact_id = other.contact_id + and type_key = 'other'); + return v_value; + end other; + + function email ( + contact_id in ab_contacts.contact_id%TYPE + ) return varchar2 + is + v_value ab_contact_attrs.value%TYPE; + begin + select value + into v_value + from ab_contact_attrs + where contact_id = email.contact_id + and sort_key = (select min(sort_key) + from ab_contact_attrs + where contact_id = email.contact_id + and type_key = 'email'); + return v_value; + end email; + +end ab_contact; +/ +show errors + +-------------------------- +-- AB_CONTACTS_COMPLETE -- +-------------------------- Index: openacs-4/packages/bookmarks/sql/oracle/upgrade/upgrade-4.6.2-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/bookmarks/sql/oracle/upgrade/upgrade-4.6.2-5.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/bookmarks/sql/oracle/upgrade/upgrade-4.6.2-5.0d1.sql 8 Oct 2003 16:40:21 -0000 1.1 @@ -0,0 +1,607 @@ +-- +-- packages/bookmarks/sql/bookmarks-create.sql +-- + +create or replace package url +as + function new ( + url_id in bm_urls.url_id%TYPE, + url_title in bm_urls.url_title%TYPE, + host_url in bm_urls.host_url%TYPE default null, + complete_url in bm_urls.complete_url%TYPE, + meta_keywords in bm_urls.meta_keywords%TYPE default null, + meta_description in bm_urls.meta_description%TYPE default null, + + 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 bm_urls.url_id%TYPE; + + procedure del ( + url_id in bm_urls.url_id%TYPE + ); + + function insert_or_update ( + url_title in bm_urls.url_title%TYPE, + host_url in bm_urls.host_url%TYPE default null, + complete_url in bm_urls.complete_url%TYPE, + meta_keywords in bm_urls.meta_keywords%TYPE default null, + meta_description in bm_urls.meta_description%TYPE default null, + + 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 bm_urls.url_id%TYPE; +end url; +/ +show errors + + +create or replace package body url +as + function new ( + url_id in bm_urls.url_id%TYPE, + url_title in bm_urls.url_title%TYPE, + host_url in bm_urls.host_url%TYPE, + complete_url in bm_urls.complete_url%TYPE, + meta_keywords in bm_urls.meta_keywords%TYPE default null, + meta_description in bm_urls.meta_description%TYPE default null, + + 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 bm_urls.url_id%TYPE + is + v_url_id integer; + begin + v_url_id := acs_object.new ( + object_id => url_id, + object_type => 'url', + creation_date => sysdate, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into bm_urls + (url_id, url_title, host_url, complete_url, meta_keywords, meta_description) + values + (v_url_id, url_title, host_url, complete_url, meta_keywords, meta_description); + + return v_url_id; + end new; + + procedure del ( + url_id in bm_urls.url_id%TYPE + ) + is + begin + acs_object.del(url.del.url_id); + end del; + + function insert_or_update ( + url_title in bm_urls.url_title%TYPE, + host_url in bm_urls.host_url%TYPE default null, + complete_url in bm_urls.complete_url%TYPE, + meta_keywords in bm_urls.meta_keywords%TYPE default null, + meta_description in bm_urls.meta_description%TYPE default null, + + 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 bm_urls.url_id%TYPE + is + n_complete_urls integer; + return_id integer; + new_url_id integer; + begin + select count(*) into n_complete_urls + from bm_urls where bm_urls.complete_url = insert_or_update.complete_url; + + if n_complete_urls = 0 then + + select acs_object_id_seq.nextval into new_url_id from dual; + + return_id := url.new ( + url_id => new_url_id, + url_title => insert_or_update.url_title, + host_url => insert_or_update.host_url, + complete_url => insert_or_update.complete_url, + creation_user => insert_or_update.creation_user, + creation_ip => insert_or_update.creation_ip + ); + + return return_id; + else + select url_id into return_id from bm_urls where bm_urls.complete_url= insert_or_update.complete_url; + return return_id; + end if; + + + end insert_or_update; + +end url; +/ +show errors + + +create or replace package bookmark +as + function new ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + owner_id in bm_bookmarks.owner_id%TYPE, + url_id in bm_urls.url_id%TYPE default null, + local_title in bm_bookmarks.local_title%TYPE default null, + folder_p in bm_bookmarks.folder_p%TYPE default 'f', + parent_id in bm_bookmarks.parent_id%TYPE, + last_access_date in bm_bookmarks.last_access_date%TYPE default null, + + 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 bm_bookmarks.bookmark_id%TYPE; + + procedure del ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE + ); + + + function name ( + object_id in bm_bookmarks.bookmark_id%TYPE + ) return bm_bookmarks.local_title%TYPE; + + function get_in_closed_p ( + new_parent_id in bm_bookmarks.bookmark_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_in_closed_p.in_closed_p%TYPE; + + procedure update_in_closed_p_one_user ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + browsing_user_id in bm_bookmarks.owner_id%TYPE + ); + + procedure update_in_closed_p_all_users ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + new_parent_id in bm_bookmarks.bookmark_id%TYPE + ); + + procedure toggle_open_close ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + browsing_user_id in bm_bookmarks.owner_id%TYPE + ); + + procedure toggle_open_close_all ( + browsing_user_id in bm_bookmarks.owner_id%TYPE, + closed_p in bm_in_closed_p.closed_p%TYPE default 'f', + root_id in bm_bookmarks.parent_id%TYPE + ); + + function get_root_folder ( + package_id in apm_packages.package_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_bookmarks.bookmark_id%TYPE; + + + function new_root_folder ( + package_id in apm_packages.package_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_bookmarks.bookmark_id%TYPE; + + function private_p ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE + ) return bm_in_closed_p.closed_p%TYPE; + + procedure update_private_p ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + private_p in bm_in_closed_p.closed_p%TYPE + ); + + procedure initialize_in_closed_p ( + viewed_user_id in users.user_id%TYPE, + in_closed_p_id in users.user_id%TYPE + ); + +end bookmark; +/ +show errors + + +create or replace package body bookmark +as + function new ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + owner_id in bm_bookmarks.owner_id%TYPE, + url_id in bm_urls.url_id%TYPE default null, + local_title in bm_bookmarks.local_title%TYPE default null, + folder_p in bm_bookmarks.folder_p%TYPE default 'f', + parent_id in bm_bookmarks.parent_id%TYPE, + last_access_date in bm_bookmarks.last_access_date%TYPE default null, + + 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 bm_bookmarks.bookmark_id%TYPE + is + v_bookmark_id integer; + v_last_access_date bm_bookmarks.last_access_date%TYPE; + v_in_closed_p bm_in_closed_p.in_closed_p%TYPE; + + cursor c_viewing_in_closed_p_ids + is + select unique in_closed_p_id + from bm_in_closed_p + where bookmark_id = (select parent_id from bm_bookmarks + where bookmark_id = new.bookmark_id); + begin + v_bookmark_id := acs_object.new ( + object_id => bookmark_id, + object_type => 'bookmark', + creation_date => sysdate, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => parent_id + ); + + if last_access_date is null then + select sysdate into v_last_access_date from dual; + else + v_last_access_date := last_access_date; + end if; + + insert into bm_bookmarks + (bookmark_id, owner_id, url_id, local_title, + folder_p, parent_id, last_access_date) + values + (v_bookmark_id, owner_id, url_id, local_title, + folder_p, parent_id, v_last_access_date); + + + -- Now we have to set the in_closed_p information for this + -- bookmark for all users that are viewing this bookmark tree + for one_row in c_viewing_in_closed_p_ids + loop + -- For each user or session record the in_closed_p status of + -- the bookmark + select bookmark.get_in_closed_p(parent_id, one_row.in_closed_p_id) + into v_in_closed_p from dual; + + insert into bm_in_closed_p (bookmark_id, in_closed_p_id, in_closed_p, creation_date) + values (v_bookmark_id, one_row.in_closed_p_id, v_in_closed_p, sysdate); + + end loop; + + + return v_bookmark_id; + end new; + + + -- The reason this procedure is so terribly complex is that I wanted to enable + -- deleting of non empty folders. The problem is that we have to delete the bookmarks + -- in the right order not to violate any referential constraints. + procedure del ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE + ) + is + + -- This is the outer cursor that starts with the leaf bookmarks under the folder + -- to be deleted and walks up to the folder to be deleted. + cursor c_bookmark_id_tree + is + select bookmark_id + from bm_bookmarks + where bookmark_id not in (select bookmark_id from bm_bookmarks + start with bookmark_id = (select parent_id from bm_bookmarks + where bookmark_id = bookmark.del.bookmark_id) + connect by prior parent_id = bookmark_id) + start with bookmark_id in (select bookmark_id + from bm_bookmarks bm_outer where not exists + (select 1 from bm_bookmarks bm_inner where + bm_outer.bookmark_id = bm_inner.parent_id) + intersect + select bookmark_id from bm_bookmarks + start with bookmark_id = bookmark.del.bookmark_id + connect by prior bookmark_id = parent_id + ) + connect by prior parent_id = bookmark_id; + + + -- To avoid violating referential constraints we need also (at least no smarter way to + -- do this occured to me) to delete all bookmarks on the level of the outer cursor + -- that lie under the folder to be deleted. + cursor c_bookmark_id_one_level (tree_id in integer) + is + select bookmark_id + from bm_bookmarks bm_outer + where parent_id = (select parent_id from bm_bookmarks where bookmark_id = tree_id) + and not exists (select 1 from bm_bookmarks where parent_id = bm_outer.bookmark_id) + and bm_outer.bookmark_id in (select bookmark_id from bm_bookmarks + start with bookmark_id = bookmark.del.bookmark_id + connect by prior bookmark_id = parent_id); + + begin + + for tree_bookmark_id in c_bookmark_id_tree + loop + + for one_level_bookmark_id in c_bookmark_id_one_level(tree_bookmark_id.bookmark_id) + loop + delete from acs_permissions where object_id = one_level_bookmark_id.bookmark_id; + delete from bm_in_closed_p where bookmark_id = one_level_bookmark_id.bookmark_id; + delete from bm_bookmarks where bookmark_id = one_level_bookmark_id.bookmark_id; + acs_object.del(one_level_bookmark_id.bookmark_id); + end loop; + end loop; + + end del; + + function name ( + object_id in bm_bookmarks.bookmark_id%TYPE + ) return bm_bookmarks.local_title%TYPE + is + v_name bm_bookmarks.local_title%TYPE; + begin + select local_title into v_name + from bm_bookmarks + where bookmark_id = name.object_id; + + return v_name; + end name; + + function get_in_closed_p ( + new_parent_id in bm_bookmarks.bookmark_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_in_closed_p.in_closed_p%TYPE + is + return_value bm_in_closed_p.in_closed_p%TYPE; + begin + select decode(count(*), 0, 'f', 't') into return_value + from (select bookmark_id from bm_bookmarks + connect by prior parent_id = bookmark_id + start with bookmark_id = new_parent_id) bm, + bm_in_closed_p bic + where bm.bookmark_id = bic.bookmark_id (+) + and bic.closed_p = 't' + and bic.in_closed_p_id = get_in_closed_p.user_id; + + return return_value; + end get_in_closed_p; + + procedure update_in_closed_p_one_user ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + browsing_user_id in bm_bookmarks.owner_id%TYPE + ) + is + begin + -- Update the in_closed_p flag of bookmarks and folders that lie under + -- the toggled folder in the tree for one particular user/session. + + -- First set all in_closed_p flags to 'f' ... + update bm_in_closed_p set in_closed_p = 'f' + where bookmark_id in (select bookmark_id from bm_bookmarks + start with bookmark_id = update_in_closed_p_one_user.bookmark_id + connect by prior bookmark_id = parent_id) + and in_closed_p_id = update_in_closed_p_one_user.browsing_user_id; + + -- then set all in_closed_p flags to 't' that lie under a closed folder + update bm_in_closed_p set in_closed_p = 't' + where bookmark_id in (select bookmark_id from bm_bookmarks + start with parent_id in (select bm.bookmark_id from bm_bookmarks bm, + bm_in_closed_p bip + where bm.bookmark_id = bip.bookmark_id + and bm.folder_p = 't' + and bip.closed_p = 't' + and bip.in_closed_p_id = + update_in_closed_p_one_user.browsing_user_id ) + connect by prior bookmark_id = parent_id + intersect + select bookmark_id from bm_bookmarks + start with bookmark_id = update_in_closed_p_one_user.bookmark_id + connect by prior bookmark_id = parent_id) + and in_closed_p_id = update_in_closed_p_one_user.browsing_user_id; + + end update_in_closed_p_one_user; + + procedure update_in_closed_p_all_users ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + new_parent_id in bm_bookmarks.bookmark_id%TYPE + ) + is + -- We need a cursor to loop over all users viewing the tree + cursor c_viewing_in_closed_p_ids + is + select unique in_closed_p_id + from bm_in_closed_p + where bookmark_id = (select bookmark_id from bm_bookmarks + where bookmark_id = update_in_closed_p_all_users.bookmark_id); + + begin + for one_row in c_viewing_in_closed_p_ids + loop + -- Update the in_closed_p status for this user/session for all bookmarks + -- under the folder + update_in_closed_p_one_user (bookmark_id, one_row.in_closed_p_id); + end loop; + + end update_in_closed_p_all_users; + + procedure toggle_open_close ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + browsing_user_id in bm_bookmarks.owner_id%TYPE + ) + is + begin + + -- Toggle the closed_p flag + update bm_in_closed_p set closed_p = (select decode(closed_p, 't', 'f', 't') + from bm_in_closed_p where bookmark_id = toggle_open_close.bookmark_id + and in_closed_p_id = toggle_open_close.browsing_user_id) + where bookmark_id = bookmark.toggle_open_close.bookmark_id + and in_closed_p_id = toggle_open_close.browsing_user_id; + + -- Now update the in_closed_p status for this user for all bookmarks under + -- the toggled folder + update_in_closed_p_one_user (bookmark_id, browsing_user_id); + + end toggle_open_close; + + procedure toggle_open_close_all ( + browsing_user_id in bm_bookmarks.owner_id%TYPE, + closed_p in bm_in_closed_p.closed_p%TYPE default 'f', + root_id in bm_bookmarks.parent_id%TYPE + ) + is + begin + -- Change the value of closed_p for all folders belonging to the + -- user (except the root folder) + update bm_in_closed_p bm_outer set closed_p = bookmark.toggle_open_close_all.closed_p + where bookmark_id in (select bookmark_id from bm_bookmarks + start with parent_id = toggle_open_close_all.root_id + connect by prior bookmark_id = parent_id); + + -- Update the value of in_closed_p for all bookmarks belonging to + -- this user. We close/open all bookmarks except the top level ones. + update bm_in_closed_p set in_closed_p = bookmark.toggle_open_close_all.closed_p + where bookmark_id in (select bookmark_id from bm_bookmarks + start with parent_id in (select bookmark_id from bm_bookmarks + where parent_id = toggle_open_close_all.root_id) + connect by prior bookmark_id = parent_id) + and in_closed_p_id = toggle_open_close_all.browsing_user_id; + + end toggle_open_close_all; + + + function get_root_folder ( + package_id in apm_packages.package_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_bookmarks.bookmark_id%TYPE + is + v_folder_id bm_bookmarks.bookmark_id%TYPE; + v_count integer; + begin + select count(*) into v_count + from bm_bookmarks + where parent_id = get_root_folder.package_id + and owner_id = get_root_folder.user_id; + + if v_count > 0 then + select bookmark_id into v_folder_id + from bm_bookmarks + where parent_id = get_root_folder.package_id + and owner_id = get_root_folder.user_id; + else + -- must be a new instance. Gotta create a new root folder + v_folder_id := new_root_folder(package_id, user_id); + end if; + + return v_folder_id; + + end get_root_folder; + + function new_root_folder ( + package_id in apm_packages.package_id%TYPE, + user_id in users.user_id%TYPE + ) return bm_bookmarks.bookmark_id%TYPE + is + v_folder_id bm_bookmarks.bookmark_id%TYPE; + v_bookmark_id bm_bookmarks.bookmark_id%TYPE; + v_email parties.email%TYPE; + begin + + select acs_object_id_seq.nextval into v_bookmark_id + from dual; + + select email into v_email + from parties where party_id = new_root_folder.user_id; + + v_folder_id := bookmark.new ( + bookmark_id => v_bookmark_id, + owner_id => new_root_folder.user_id, + folder_p => 't', + local_title => ' Bookmarks Root Folder of ' || v_email, + parent_id => new_root_folder.package_id + ); + + -- set up default permissions + -- The owner may administer the bookmarks + -- Any other permissions will be inherited from the next higher + -- package instance in the site hierarchy + acs_permission.grant_permission ( + object_id => v_folder_id, + grantee_id => new_root_folder.user_id, + privilege => 'admin' + ); + + return v_folder_id; + + end new_root_folder; + + function private_p ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE + ) return bm_in_closed_p.closed_p%TYPE + is + v_private_p bm_in_closed_p.closed_p%TYPE; + begin + select decode(count(*), 0, 'f', 't') into v_private_p + from acs_objects, (select bookmark_id from bm_bookmarks + start with bookmark_id = private_p.bookmark_id + connect by prior parent_id = bookmark_id) b + where b.bookmark_id = acs_objects.object_id + and acs_objects.security_inherit_p = 'f'; + + return v_private_p; + end private_p; + + procedure update_private_p ( + bookmark_id in bm_bookmarks.bookmark_id%TYPE, + private_p in bm_in_closed_p.closed_p%TYPE + ) + is + v_owner_id bm_bookmarks.owner_id%TYPE; + v_admin_p bm_in_closed_p.closed_p%TYPE; + begin + + if private_p = 'f' then + -- Turn on security inheritance + update acs_objects set security_inherit_p = 't' where object_id = bookmark_id; + + else + -- Private case + -- turn off inheritance + update acs_objects set security_inherit_p = 'f' where object_id = bookmark_id; + + -- Grant admin rights to the owner + select owner_id into v_owner_id from bm_bookmarks where bookmark_id = update_private_p.bookmark_id; + acs_permission.grant_permission(bookmark_id, v_owner_id, 'admin'); + + end if; + + end update_private_p; + + + procedure initialize_in_closed_p ( + viewed_user_id in users.user_id%TYPE, + in_closed_p_id in users.user_id%TYPE + ) + is + v_count_in_closed_p integer; + v_count_bookmarks integer; + + cursor c_bookmark_ids( viewed_user_id in integer, in_closed_p_id in integer) + is + select bookmark_id + from bm_bookmarks + where owner_id = c_bookmark_ids.viewed_user_id + and bookmark_id not in (select bookmark_id from bm_in_closed_p + where in_closed_p_id = c_bookmark_ids.in_closed_p_id); + begin + for v_bookmark_id in c_bookmark_ids(initialize_in_closed_p.viewed_user_id, + initialize_in_closed_p.in_closed_p_id) + loop + insert into bm_in_closed_p (bookmark_id, in_closed_p_id, in_closed_p, creation_date) + values (v_bookmark_id.bookmark_id, initialize_in_closed_p.in_closed_p_id, 'f', sysdate); + end loop; + + end initialize_in_closed_p; +end bookmark; +/ +show errors Index: openacs-4/packages/chat/sql/oracle/upgrade/upgrade-4.0.0-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/chat/sql/oracle/upgrade/upgrade-4.0.0-5.0d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/chat/sql/oracle/upgrade/upgrade-4.0.0-5.0d1.sql 8 Oct 2003 16:41:42 -0000 1.1 @@ -0,0 +1,344 @@ +-- +-- packages/chat/sql/chat-create.sql +-- + + +create or replace package chat_room +as + function new ( + room_id in chat_rooms.room_id%TYPE default null, + pretty_name in chat_rooms.pretty_name%TYPE, + description in chat_rooms.description%TYPE default null, + moderated_p in chat_rooms.moderated_p%TYPE default 'f', + active_p in chat_rooms.active_p%TYPE default 't', + archive_p in chat_rooms.archive_p%TYPE default 'f', + context_id in acs_objects.context_id%TYPE default null, + 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, + object_type in acs_objects.object_type%TYPE default 'chat_room' + ) return acs_objects.object_id%TYPE; + + procedure del ( + room_id in chat_rooms.room_id%TYPE + ); + + procedure edit ( + room_id in chat_rooms.room_id%TYPE, + pretty_name in chat_rooms.pretty_name%TYPE, + description in chat_rooms.description%TYPE, + moderated_p in chat_rooms.moderated_p%TYPE, + active_p in chat_rooms.active_p%TYPE, + archive_p in chat_rooms.archive_p%TYPE + ); + + function name ( + room_id in chat_rooms.room_id%TYPE + ) return chat_rooms.pretty_name%TYPE; + + procedure message_post ( + room_id in chat_msgs.room_id%TYPE, + msg in chat_msgs.msg%TYPE default null, + html_p in chat_msgs.html_p%TYPE default 'f', + approved_p in chat_msgs.approved_p%TYPE default 't', + creation_user in chat_msgs.creation_user%TYPE, + creation_ip in chat_msgs.creation_ip%TYPE default null, + creation_date in chat_msgs.creation_date%TYPE default sysdate + ); + + function message_count ( + room_id in chat_rooms.room_id%TYPE + ) return integer; + + procedure delete_all_msgs ( + room_id in chat_rooms.room_id%TYPE + ); + +end chat_room; +/ +show errors + +create or replace package chat_transcript +as + function new ( + transcript_id in chat_transcripts.transcript_id%TYPE default null, + pretty_name in chat_transcripts.pretty_name%TYPE, + contents in chat_transcripts.contents%TYPE, + description in chat_transcripts.description%TYPE, + room_id in chat_transcripts.room_id%TYPE, + context_id in acs_objects.context_id%TYPE default null, + 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, + object_type in acs_objects.object_type%TYPE default 'chat_transcript' + ) return acs_objects.object_id%TYPE; + + procedure del ( + transcript_id in chat_transcripts.transcript_id%TYPE + ); + + procedure edit ( + transcript_id in chat_transcripts.transcript_id%TYPE, + pretty_name in chat_transcripts.pretty_name%TYPE, + contents in chat_transcripts.contents%TYPE, + description in chat_transcripts.description%TYPE + ); +end chat_transcript; +/ +show errors + +-- +-- End package definition +-- + +-- +-- Begin package body +-- + +create or replace package body chat_room +as + function new ( + room_id in chat_rooms.room_id%TYPE default null, + pretty_name in chat_rooms.pretty_name%TYPE, + description in chat_rooms.description%TYPE default null, + moderated_p in chat_rooms.moderated_p%TYPE default 'f', + active_p in chat_rooms.active_p%TYPE default 't', + archive_p in chat_rooms.archive_p%TYPE default 'f', + context_id in acs_objects.context_id%TYPE default null, + 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, + object_type in acs_objects.object_type%TYPE default 'chat_room' + ) return acs_objects.object_id%TYPE + is + v_room_id chat_rooms.room_id%TYPE; + begin + v_room_id := acs_object.new ( + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into chat_rooms ( + room_id, + pretty_name, + description, + moderated_p, + active_p, + archive_p) + values ( + v_room_id, + pretty_name, + description, + moderated_p, + active_p, + archive_p); + + return v_room_id; + end new; + + procedure del ( + room_id in chat_rooms.room_id%TYPE + ) + is + begin + -- First erase all the messages relate to this chat room. + delete from chat_msgs where room_id = chat_room.del.room_id; + + -- Delete all privileges associate with this room + delete from acs_permissions where object_id = chat_room.del.room_id; + + -- Now delete the chat room itself. + delete from chat_rooms where room_id = chat_room.del.room_id; + + acs_object.del(room_id); + end del; + + procedure edit ( + room_id in chat_rooms.room_id%TYPE, + pretty_name in chat_rooms.pretty_name%TYPE, + description in chat_rooms.description%TYPE, + moderated_p in chat_rooms.moderated_p%TYPE, + active_p in chat_rooms.active_p%TYPE, + archive_p in chat_rooms.archive_p%TYPE + ) + is + begin + update chat_rooms set + pretty_name = chat_room.edit.pretty_name, + description = chat_room.edit.description, + moderated_p = chat_room.edit.moderated_p, + active_p = chat_room.edit.active_p, + archive_p = chat_room.edit.archive_p + where + room_id = chat_room.edit.room_id; + end edit; + + function name ( + room_id in chat_rooms.room_id%TYPE + ) return chat_rooms.pretty_name%TYPE + is + v_room_name chat_rooms.pretty_name%TYPE; + begin + select pretty_name into v_room_name + from chat_rooms + where room_id = chat_room.name.room_id; + + return v_room_name; + end name; + + procedure message_post ( + room_id in chat_msgs.room_id%TYPE, + msg in chat_msgs.msg%TYPE default null, + html_p in chat_msgs.html_p%TYPE default 'f', + approved_p in chat_msgs.approved_p%TYPE default 't', + creation_user in chat_msgs.creation_user%TYPE, + creation_ip in chat_msgs.creation_ip%TYPE default null, + creation_date in chat_msgs.creation_date%TYPE default sysdate + ) + is + v_msg_id chat_msgs.msg_id%TYPE; + v_msg_archive_p chat_rooms.archive_p%TYPE; + v_msg chat_msgs.msg%TYPE; + begin + -- Get msg id from the global acs_object sequence. + select acs_object_id_seq.nextval into v_msg_id from dual; + + select archive_p into v_msg_archive_p + from chat_rooms + where room_id = chat_room.message_post.room_id; + + if v_msg_archive_p = 't' then + v_msg := msg; + else + v_msg := null; + end if; + + -- Insert into chat_msgs table. + insert into chat_msgs ( + msg_id, + room_id, + msg, + msg_len, + html_p, + approved_p, + creation_user, + creation_ip, + creation_date) + values ( + v_msg_id, + room_id, + v_msg, + nvl(length(msg), 0), + html_p, + approved_p, + creation_user, + creation_ip, + creation_date) ; + end message_post; + + + function message_count ( + room_id in chat_rooms.room_id%TYPE + ) return integer + is + v_count integer; + begin + select count(*) into v_count + from chat_msgs + where room_id = chat_room.message_count.room_id; + + return v_count; + end message_count; + + procedure delete_all_msgs ( + room_id in chat_rooms.room_id%TYPE + ) + is + begin + delete from chat_msgs where room_id = chat_room.delete_all_msgs.room_id; + end delete_all_msgs; + +end chat_room; +/ +show errors + + +create or replace package body chat_transcript +as + function new ( + transcript_id in chat_transcripts.transcript_id%TYPE default null, + pretty_name in chat_transcripts.pretty_name%TYPE, + contents in chat_transcripts.contents%TYPE, + description in chat_transcripts.description%TYPE, + room_id in chat_transcripts.room_id%TYPE, + context_id in acs_objects.context_id%TYPE default null, + 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, + object_type in acs_objects.object_type%TYPE default 'chat_transcript' + ) return acs_objects.object_id%TYPE + is + v_transcript_id chat_transcripts.transcript_id%TYPE; + begin + v_transcript_id := acs_object.new ( + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into chat_transcripts (transcript_id, pretty_name, contents, description, room_id) + values (v_transcript_id, pretty_name, empty_clob(), description, room_id); + + return v_transcript_id; + end new; + + procedure del ( + transcript_id in chat_transcripts.transcript_id%TYPE + ) + is + begin + + -- Delete all privileges associate with this transcript + delete from acs_permissions where object_id = chat_transcript.del.transcript_id; + + delete from chat_transcripts + where transcript_id = chat_transcript.del.transcript_id; + + acs_object.del(transcript_id); + end del; + + procedure edit ( + transcript_id in chat_transcripts.transcript_id%TYPE, + pretty_name in chat_transcripts.pretty_name%TYPE, + contents in chat_transcripts.contents%TYPE, + description in chat_transcripts.description%TYPE + ) + is + begin + update chat_transcripts + set pretty_name = chat_transcript.edit.pretty_name, + contents = chat_transcript.edit.contents, + description = chat_transcript.edit.description + where + transcript_id = chat_transcript.edit.transcript_id; + + end edit; + +end chat_transcript; +/ +show errors + + + + + + + + + +