Index: openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql,v diff -u -N -r1.3 -r1.4 --- openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql 11 Dec 2003 21:40:08 -0000 1.3 +++ openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql 26 Jan 2004 15:39:47 -0000 1.4 @@ -7,3 +7,407 @@ headers clob, body clob ); + + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright (C) 2000 MIT +-- +-- GNU GPL v2 +-- + + +-- The Notification Interval Package + +create or replace package notification_interval +as + function new ( + interval_id in notification_intervals.interval_id%TYPE default null, + name in notification_intervals.name%TYPE, + n_seconds in notification_intervals.n_seconds%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_intervals.interval_id%TYPE; + + procedure del ( + interval_id in notification_intervals.interval_id%TYPE + ); + +end notification_interval; +/ +show errors + + + +create or replace package body notification_interval +as + function new ( + interval_id in notification_intervals.interval_id%TYPE default null, + name in notification_intervals.name%TYPE, + n_seconds in notification_intervals.n_seconds%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_intervals.interval_id%TYPE + is + v_interval_id acs_objects.object_id%TYPE; + begin + v_interval_id:= acs_object.new ( + object_id => interval_id, + object_type => 'notification_interval', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notification_intervals + (interval_id, name, n_seconds) values + (v_interval_id, name, n_seconds); + + return v_interval_id; + end new; + + procedure del ( + interval_id in notification_intervals.interval_id%TYPE + ) + is + begin + acs_object.del(interval_id); + end del; + +end notification_interval; +/ +show errors + + +-- The notification delivery methods package + +create or replace package notification_delivery_method +as + function new ( + delivery_method_id in notification_delivery_methods.delivery_method_id%TYPE default null, + sc_impl_id in notification_delivery_methods.sc_impl_id%TYPE, + short_name in notification_delivery_methods.short_name%TYPE, + pretty_name in notification_delivery_methods.pretty_name%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_delivery_methods.delivery_method_id%TYPE; + + procedure del ( + delivery_method_id in notification_delivery_methods.delivery_method_id%TYPE + ); + +end notification_delivery_method; +/ +show errors + + + +create or replace package body notification_delivery_method +as + function new ( + delivery_method_id in notification_delivery_methods.delivery_method_id%TYPE default null, + sc_impl_id in notification_delivery_methods.sc_impl_id%TYPE, + short_name in notification_delivery_methods.short_name%TYPE, + pretty_name in notification_delivery_methods.pretty_name%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_delivery_methods.delivery_method_id%TYPE + is + v_delivery_method_id acs_objects.object_id%TYPE; + begin + v_delivery_method_id := acs_object.new ( + object_id => delivery_method_id, + object_type => 'notification_delivery_method', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notification_delivery_methods + (delivery_method_id, sc_impl_id, short_name, pretty_name) values + (v_delivery_method_id, sc_impl_id, short_name, pretty_name); + + return v_delivery_method_id; + end new; + + procedure del ( + delivery_method_id in notification_delivery_methods.delivery_method_id%TYPE + ) + is + begin + acs_object.del (delivery_method_id); + end del; + +end notification_delivery_method; +/ +show errors + + + +-- Notification Types Package +create or replace package notification_type +as + function new ( + type_id in notification_types.type_id%TYPE default null, + sc_impl_id in notification_types.sc_impl_id%TYPE, + short_name in notification_types.short_name%TYPE, + pretty_name in notification_types.pretty_name%TYPE, + description in notification_types.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_types.type_id%TYPE; + + procedure del ( + type_id in notification_types.type_id%TYPE default null + ); + +end notification_type; +/ +show errors + + + +create or replace package body notification_type +as + function new ( + type_id in notification_types.type_id%TYPE default null, + sc_impl_id in notification_types.sc_impl_id%TYPE, + short_name in notification_types.short_name%TYPE, + pretty_name in notification_types.pretty_name%TYPE, + description in notification_types.description%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_types.type_id%TYPE + is + v_type_id acs_objects.object_id%TYPE; + begin + v_type_id := acs_object.new ( + object_id => type_id, + object_type => 'notification_type', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notification_types + (type_id, sc_impl_id, short_name, pretty_name, description) values + (v_type_id, sc_impl_id, short_name, pretty_name, description); + + return v_type_id; + end new; + + procedure del ( + type_id in notification_types.type_id%TYPE default null + ) + is + begin + acs_object.del(type_id); + end del; + +end notification_type; +/ +show errors + + + +-- the notification request package + +create or replace package notification_request +as + function new ( + request_id in notification_requests.request_id%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'notification_request', + type_id in notification_requests.type_id%TYPE, + user_id in notification_requests.user_id%TYPE, + object_id in notification_requests.object_id%TYPE, + interval_id in notification_requests.interval_id%TYPE, + delivery_method_id in notification_requests.delivery_method_id%TYPE, + format in notification_requests.format%TYPE, + dynamic_p in notification_requests.dynamic_p%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_requests.request_id%TYPE; + + procedure del ( + request_id in notification_requests.request_id%TYPE default null + ); + + procedure delete_all ( + object_id in notification_requests.object_id%TYPE default null + ); +end notification_request; +/ +show errors + +create or replace package body notification_request +as + function new ( + request_id in notification_requests.request_id%TYPE default null, + object_type in acs_objects.object_type%TYPE default 'notification_request', + type_id in notification_requests.type_id%TYPE, + user_id in notification_requests.user_id%TYPE, + object_id in notification_requests.object_id%TYPE, + interval_id in notification_requests.interval_id%TYPE, + delivery_method_id in notification_requests.delivery_method_id%TYPE, + format in notification_requests.format%TYPE, + dynamic_p in notification_requests.dynamic_p%TYPE, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notification_requests.request_id%TYPE + is + v_request_id acs_objects.object_id%TYPE; + begin + v_request_id := acs_object.new ( + object_id => request_id, + object_type => object_type, + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notification_requests + (request_id, type_id, user_id, object_id, interval_id, delivery_method_id, format, dynamic_p) values + (v_request_id, type_id, user_id, object_id, interval_id, delivery_method_id, format, dynamic_p); + + return v_request_id; + end new; + + procedure del ( + request_id in notification_requests.request_id%TYPE default null + ) + is + begin + for v_notifications in (select notification_id + from notifications n, notification_requests nr + where n.response_id = nr.object_id + and nr.request_id = request_id) + loop + acs_object.del(v_notifications.notification_id); + end loop; + acs_object.del(request_id); + end del; + + procedure delete_all ( + object_id in notification_requests.object_id%TYPE default null + ) + is + v_request notification_requests%ROWTYPE; + begin + for v_request in + (select request_id from notification_requests where object_id= delete_all.object_id) + LOOP + notification_request.del(v_request.request_id); + END LOOP; + end delete_all; + +end notification_request; +/ +show errors + + + + + +-- the notifications package +create or replace package notification +as + + function new ( + notification_id in notifications.notification_id%TYPE default null, + type_id in notifications.type_id%TYPE, + object_id in notifications.object_id%TYPE, + notif_date in notifications.notif_date%TYPE default sysdate, + response_id in notifications.response_id%TYPE default null, + notif_user in notifications.notif_user%TYPE default null, + notif_subject in notifications.notif_subject%TYPE default null, + notif_text in varchar default null, + notif_html in varchar default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notifications.notification_id%TYPE; + + procedure del ( + notification_id in notifications.notification_id%TYPE default null + ); + +end notification; +/ +show errors + + + +create or replace package body notification +as + + function new ( + notification_id in notifications.notification_id%TYPE default null, + type_id in notifications.type_id%TYPE, + object_id in notifications.object_id%TYPE, + notif_date in notifications.notif_date%TYPE default sysdate, + response_id in notifications.response_id%TYPE default null, + notif_user in notifications.notif_user%TYPE default null, + notif_subject in notifications.notif_subject%TYPE default null, + notif_text in varchar default null, + notif_html in varchar default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_user in acs_objects.creation_user%TYPE, + creation_ip in acs_objects.creation_ip%TYPE, + context_id in acs_objects.context_id%TYPE default null + ) return notifications.notification_id%TYPE + is + v_notification_id acs_objects.object_id%TYPE; + begin + v_notification_id := acs_object.new ( + object_id => notification_id, + object_type => 'notification', + creation_date => creation_date, + creation_user => creation_user, + creation_ip => creation_ip, + context_id => context_id + ); + + insert into notifications + (notification_id, type_id, object_id, notif_date, response_id, notif_user, notif_subject, notif_text, notif_html) + values + (v_notification_id, type_id, object_id, notif_date, response_id, notif_user, notif_subject, notif_text, notif_html); + + return v_notification_id; + end new; + + procedure del ( + notification_id in notifications.notification_id%TYPE default null + ) + is + begin + delete from notifications where notification_id = notification.del.notification_id; + + acs_object.del (notification_id); + end del; + +end notification; +/ +show errors