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 -r1.1 -r1.2 --- openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql 8 Oct 2003 16:59:23 -0000 1.1 +++ openacs-4/packages/notifications/sql/oracle/upgrade/upgrade-4.6.4-5.0d1.sql 6 Nov 2003 19:23:02 -0000 1.2 @@ -1,486 +1,8 @@ - --- --- 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 - --- --- The Notifications Package --- --- ben@openforce.net --- Copyright (C) 2000 MIT --- --- GNU GPL v2 --- - - --- The Notification Replies Package - -create or replace package notification_reply -as - function new ( - reply_id in notification_replies.reply_id%TYPE default null, - object_id in notification_replies.object_id%TYPE, - type_id in notification_replies.type_id%TYPE, - from_user in notification_replies.from_user%TYPE, - subject in notification_replies.subject%TYPE, - content in varchar, - reply_date in notification_replies.reply_date%TYPE default sysdate, - 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 notification_replies.reply_id%TYPE; - - procedure del ( - reply_id in notification_replies.reply_id%TYPE - ); -end notification_reply; -/ -show errors - - -create or replace package body notification_reply -as - function new ( - reply_id in notification_replies.reply_id%TYPE default null, - object_id in notification_replies.object_id%TYPE, - type_id in notification_replies.type_id%TYPE, - from_user in notification_replies.from_user%TYPE, - subject in notification_replies.subject%TYPE, - content in varchar, - reply_date in notification_replies.reply_date%TYPE default sysdate, - 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 notification_replies.reply_id%TYPE - is - v_reply_id acs_objects.object_id%TYPE; - begin - v_reply_id:= acs_object.new ( - object_id => reply_id, - object_type => 'notification_reply', - creation_date => creation_date, - creation_user => creation_user, - creation_ip => creation_ip, - context_id => context_id - ); - - insert into notification_replies - (reply_id, object_id, type_id, from_user, subject, content, reply_date) - values - (v_reply_id, object_id, type_id, from_user, subject, content, reply_date); - - return v_reply_id; - end new; - - procedure del ( - reply_id in notification_replies.reply_id%TYPE - ) - is - begin - acs_object.del(object_id => reply_id); - end del; - -end notification_reply; -/ -show errors +create table notification_email_hold ( + reply_id integer + references notification_replies(reply_id) + primary key, + to_addr text, + header text, + body text +);