Forum OpenACS Development: 0.1d of a reminder package

I have implemented a 0.1d version of a reminder package.  This is a request for comments.

Package name: reminder
Table: reminders

To set a reminder, the user calls reminder__new (a Tcl entry point should probably be provided).

Here is the sql for creating reminder__new:

CREATE OR REPLACE FUNCTION reminder__new(
        integer,                -- reminder.object_id%TYPE
        integer,                -- reminder.reminder_from_id%TYPE
        integer,                -- reminder.reminder_to_id%TYPE
        timestamptz,            -- reminder.reminder_time%TYPE
        varchar                -- reminder.reminder_msg%TYPE
)
RETURNS integer
AS 'declare
        new__object_id          alias for $1;
        new__from_id            alias for $2;
        new__to_id              alias for $3;
        new__when              alias for $4;
        new__msg                alias for $5;
    begin
        insert into reminders
                (object_id, reminder_from_id, reminder_to_id, reminder_time, reminder_msg)
        values
                (new__object_id, new__from_id, new__to_id, new__when, new__msg);
            return 0;
    end;'
LANGUAGE 'plpgsql';

This works with the table defined below.  A reminder-init.tcl schedules reminder::task::email_reminder, which sends an email out at the specified time (using code shameless ripped from the Project Manager package -- thank you).  Currently it sweeps for reminders every 60 seconds, but this will become a package parameter.  As I've written this, I've thought of several other things that could be done better, but for now I'm resisting temptation and wanting to make sure I'm on the right track of providing what people want, and especially something that can be used by the Calendar package (Dirk?).

create table reminders (
        object_id              integer
                                constraint reminders_object_id_fk
                                references acs_objects(object_id)
                                on delete cascade,
        reminder_from_id        integer
                                constraint reminders_to_id_fk
                                references parties(party_id)
                                on delete cascade,
        reminder_to_id          integer
                                constraint reminders_from_id_fk
                                references parties(party_id)
                                on delete cascade,
        reminder_time          timestamptz, -- constraint > now
        reminder_msg            varchar(1000),
        constraint reminders_unique
        unique (object_id, reminder_to_id,reminder_time)
);

comment on table reminders is '
        Table reminders maps the many to many relationship between
        a reminder and a party and the owner of the reminder.
';

comment on column reminders.object_id is '
        The object that owns this reminder.
';

comment on column reminders.reminder_from_id is '
        The party_id from whom the message will appear to be sent.
        Defaults to the owner_id of the object if null.
';

comment on column reminders.reminder_to_id is '
        The party_id to whom to send the reminder.
';

comment on column reminders.reminder_time is '
        The time to send out the reminder.
';

comment on column reminders.reminder_msg is '
        Message to send/display.
';

The initial idea was that if somebody wanted multiple reminders, the requesting package would perform multiple reminder__new calls.  However, I think it would be nice to have that feature.  (This could get complicated - there are multiple reminders about a single event and reminders associated with a recurring event.)  Other ideas I have are:

- handle destination party ids that are groups of people and email everyone in the group.
- handle bounces and notify somebody if the email didn't make it.
- require positive feedback from the receiver and notify the caller if a confirmation isn't received within a certain period of time (perhaps leave it to the caller to handle the second part).
- add a sequence (perhaps reminder_id) that can be stored by the caller and used to update or delete the reminder (probably should do this one soon).
- maybe allow arbitrary actions other than email.  Perhaps this is going too far, i.e. at that point its like creating an 'at' command.
- provide some sort of message template to free packages of having to construct a message (not sure if this would really be useful).

Malte,

Is there a facility in place (in general) to set user preferences as you describe? Or, could the new ams service possibly be used to extend an object type for this?