Index: openacs-4/packages/notifications/sql/postgresql/notifications-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-core-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-core-create.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,211 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + + +-- intervals should really be service contracts so other intervals can be +-- taken into account. For now we're going to make them just intervals +create table notification_intervals ( + interval_id integer not null + constraint notif_interv_id_pk primary key + constraint notif_interv_id_fk references acs_objects(object_id), + name varchar(200) not null + constraint notif_interv_name_un unique, + -- how to schedule this + n_seconds integer not null +); + + +-- delivery methods should be service contracts, too. +create table notification_delivery_methods ( + delivery_method_id integer not null + constraint notif_deliv_meth_pk primary key + constraint notif_deliv_meth_fk references acs_objects(object_id), + short_name varchar(100) not null + constraint notif_deliv_short_name_un unique, + pretty_name varchar(200) not null +); + + +create table notification_types ( + type_id integer not null + constraint notif_type_type_id_pk primary key + constraint notif_type_type_id_fk references acs_objects(object_id), + short_name varchar(100) not null + constraint notif_type_short_name_un unique, + pretty_name varchar(200) not null, + description varchar(2000) +); + + +-- what's allowed for a given notification type? +create table notification_types_intervals ( + type_id integer not null + constraint notif_type_int_type_id_fk + references notification_types(type_id), + interval_id integer not null + constraint notif_type_int_int_id_fk + references notification_intervals(interval_id), + constraint notif_type_int_pk + primary key (type_id, interval_id) +); + +-- allowed delivery methods +create table notification_types_del_methods ( + type_id integer not null + constraint notif_type_del_type_id_fk + references notification_types(type_id), + delivery_method_id integer not null + constraint notif_type_del_meth_id_fk + references notification_delivery_methods(delivery_method_id), + constraint notif_type_deliv_pk + primary key (type_id, delivery_method_id) +); + + +-- Requests for Notifications +create table notification_requests ( + request_id integer not null + constraint notif_request_id_pk primary key + constraint notif_request_id_fk references acs_objects(object_id), + type_id integer not null + constraint notif_request_type_id_fk + references notification_types(type_id), + user_id integer not null + constraint notif_request_user_id_fk + references users(user_id), + -- The object this request pertains to + object_id integer not null + constraint notif_request_object_id_fk + references acs_objects(object_id), + -- the interval must be allowed for this type + interval_id integer not null, + constraint notif_request_interv_fk + foreign key (type_id, interval_id) references notification_types_intervals(type_id,interval_id), + -- the delivery method must be allowed for this type + delivery_method_id integer not null, + constraint notif_request_deliv_fk + foreign key (type_id, delivery_method_id) references notification_types_del_methods(type_id,delivery_method_id), + -- the format of the notification should be... + format varchar(100) default 'text' + constraint notif_request_format_ch + check (format in ('text','html')) +); + + +-- preferences +-- +-- for preferences that apply to each request, we're using the +-- notification_requests table. For preferences that are notification-wide, +-- we use user-preferences + + +-- the actual stuff that has to go out +create table notifications ( + notification_id integer not null + constraint notif_notif_id_pk primary key + constraint notif_notif_id_fk references acs_objects(object_id), + type_id integer not null + constraint notif_type_id_fk references notification_types(type_id), + -- the object this notification pertains to + object_id integer not null + constraint notif_object_id_fk references acs_objects(object_id), + notif_date timestamp not null, + -- this is to allow responses to notifications + response_id integer + constraint notif_reponse_id_fk references acs_objects(object_id), + notif_subject varchar(100), + notif_text text, + notif_html text +); + + +-- who has received this notification? +create table notification_user_map ( + notification_id integer not null + constraint notif_user_map_notif_id_fk references notifications(notification_id), + user_id integer not null + constraint notif_user_map_user_id_fk references users(user_id), + constraint notif_user_map_pk + primary key (notification_id, user_id), + sent_date timestamp +); + + + +-- +-- Object Types +-- + +begin + select acs_object_type__create_type ( + 'notification_interval', + 'Notification Interval', + 'Notification Intervals', + 'acs_object', + 'notification_intervals', + 'interval_id', + 'notification_interval', + 'f', + NULL, + NULL + ); + + select acs_object_type__create_type ( + 'notification_delivery_method', + 'Notification Delivery Method', + 'Notification Delivery Methods', + 'acs_object', + 'notification_delivery_methods', + 'delivery_method_id', + 'notification_delivery_method', + 'f', + NULL, + NULL + ); + + select acs_object_type__create_type ( + 'notification_type', + 'Notification Type', + 'Notification Types', + 'acs_object', + 'notification_types', + 'type_id', + 'notification_type', + 'f', + NULL, + NULL + ); + + select acs_object_type__create_type ( + 'notification_request', + 'Notification Request', + 'Notification Requests', + 'acs_object', + 'notification_requests', + 'request_id', + 'notification_request', + 'f', + NULL, + NULL + ); + + select acs_object_type__create_type ( + 'notification', + 'Notification', + 'Notifications', + 'acs_object', + 'notifications', + 'notification_id', + 'notification', + 'f', + NULL, + NULL + ); +end; Index: openacs-4/packages/notifications/sql/postgresql/notifications-core-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-core-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-core-drop.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,57 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + +-- drop script + +drop table notification_user_map; + +drop table notifications; + +drop table notification_requests; + +drop table notification_types_del_methods; + +drop table notification_types_intervals; + +drop table notification_types; + +drop table notification_intervals; + +drop table notification_delivery_methods; + + + + +-- +-- Object Types +-- + +begin + + select acs_object_type__drop_type ( + 'notification_interval', 'f' + ); + + select acs_object_type__drop_type ( + 'notification_delivery_method', 'f' + ); + + select acs_object_type__drop_type ( + 'notification_type', 'f' + ); + + select acs_object_type__drop_type ( + 'notification_request', 'f' + ); + + select acs_object_type__drop_type ( + 'notification', 'f' + ); +end; Index: openacs-4/packages/notifications/sql/postgresql/notifications-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-create.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,18 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + +\i notifications-core-create.sql +\i notifications-package-create.sql + +-- the service contracts will eventually be created +-- @ notifications-interval-sc-create.sql +-- @ notifications-delivery-sc-create.sql + +\i notifications-init.sql Index: openacs-4/packages/notifications/sql/postgresql/notifications-init.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-init.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-init.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,53 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + +-- initialize some stuff +begin + select notification_interval__new ( + NULL, + 'daily', + 3600 * 24, + now(), + NULL, + NULL, + NULL + ); + + select notification_interval__new ( + NULL, + 'hourly', + 3600, + now(), + NULL, + NULL, + NULL + ); + + select notification_interval__new ( + NULL, + 'instant', + 0, + now(), + NULL, + NULL, + NULL + ); + + select notification_delivery_method__new ( + NULL, + 'email', + 'Email', + now(), + NULL, + NULL, + NULL + ); + +end; Index: openacs-4/packages/notifications/sql/postgresql/notifications-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-package-create.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,282 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + + +-- The Notification Interval Package + +select define_function_args ('notification_interval__new','interval_id,name,n_seconds,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('notification_interval__delete','interval_id'); + +-- implementation + +create function notification_interval__new (integer, varchar, integer, timestamp, integer, varchar, integer) +returns integer as ' +DECLARE + p_interval_id alias for $1; + p_name alias for $2; + p_n_seconds alias for $3; + p_creation_date alias for $4; + p_creation_user alias for $5; + p_creation_ip alias for $6; + p_context_id alias for $7; + v_interval_id integer; +BEGIN + v_interval_id:= acs_object__new ( + p_interval_id, + ''notification_interval'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into notification_intervals + (interval_id, name, n_seconds) values + (v_interval_id, p_name, p_n_seconds); + + return v_interval_id; +END; +' language 'plpgsql'; + + +create function notification_interval__delete(integer) +returns integer as ' +DECLARE + p_interval_id alias for $1; +BEGIN + perform acs_object__delete(p_interval_id); + return 0; +END; +' language 'plpgsql'; + + +-- The notification delivery methods package + +select define_function_args ('notification_delivery_method__new','delivery_method_id,short_name,pretty_name,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('notification_delivery_method__delete','delivery_method_id'); + + +create function notification_delivery_method__new (integer, varchar, varchar, timestamp, integer, varchar, integer) +returns integer as ' +DECLARE + p_delivery_method_id alias for $1; + p_short_name alias for $2; + p_pretty_name alias for $3; + p_creation_date alias for $4; + p_creation_user alias for $5; + p_creation_ip alias for $6; + p_context_id alias for $7; + v_delivery_method_id integer; +BEGIN + v_delivery_method_id:= acs_object__new ( + p_delivery_method_id, + ''notification_delivery_method'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into notification_delivery_methods + (delivery_method_id, short_name, pretty_name) values + (v_delivery_method_id, p_short_name, p_pretty_name); + + return v_delivery_method_id; +END; +' language 'plpgsql'; + +create function notification_delivery_method__delete(integer) +returns integer as ' +DECLARE + p_delivery_method_id alias for $1; +BEGIN + perform acs_object__delete(p_delivery_method_id); + return 0; +END; +' language 'plpgsql'; + + +-- Notification Types Package +select define_function_args ('notification_type__new','type_id,short_name,pretty_name,description,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('notification_type__delete','type_id'); + + +-- implementation + +create function notification_type__new (integer,varchar,varchar,varchar,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_type_id alias for $1; + p_short_name alias for $2; + p_pretty_name alias for $3; + p_description alias for $4; + p_creation_date alias for $5; + p_creation_user alias for $6; + p_creation_ip alias for $7; + p_context_id alias for $8; + v_type_id integer; +BEGIN + v_type_id:= acs_object__new ( + p_type_id, + ''notification_type'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into notification_types + (type_id, short_name, pretty_name, description) values + (v_type_id, p_short_name, p_pretty_name, p_description); + + return v_type_id; +END; +' language 'plpgsql'; + + +create function notification_type__delete(integer) +returns integer as ' +DECLARE + p_type_id alias for $1; +BEGIN + perform acs_object__delete(p_type_id); + return 0; +END; +' language 'plpgsql'; + + +-- the notification request package + +select define_function_args ('notification_request__new','request_id,object_type;notification_request,type_id,user_id,object_id,interval_id,delivery_method_id,format,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('notification_request__delete','request_id'); + +select define_function_args ('notification_request__delete_all', 'object_id'); + + +-- implementation + +create function notification_request__new (integer,varchar,integer,integer,integer,integer,varchar,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_request_id alias for $1; + p_object_type alias for $2; + p_type_id alias for $3; + p_user_id alias for $4; + p_object_id alias for $5; + p_interval_id alias for $6; + p_delivery_method_id alias for $7; + p_format alias for $8; + p_creation_date alias for $9; + p_creation_user alias for $10; + p_creation_ip alias for $11; + p_context_id alias for $12; + v_request_id integer; +BEGIN + v_request_id:= acs_object__new ( + p_request_id, + p_object_type, + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into notification_requests + (request_id, type_id, user_id, object_id, interval_id, delivery_method_id, format) values + (v_request_id, p_type_id, p_user_id, p_object_id, p_interval_id, p_delivery_method_id, p_format); + + return v_request_id; + +END; +' language 'plpgsql'; + + +create function notification_request__delete(integer) +returns integer as ' +DECLARE + p_request_id alias for $1; +BEGIN + perform acs_object__delete(p_request_id); + return 0; +END; +' language 'plpgsql'; + + +create function notification_request__delete_all(integer) +returns integer as ' +DECLARE + p_object_id alias for $1; + v_request RECORD; +BEGIN + for v_request in + (select request_id from notification_requests where object_id= p_object_id) + LOOP + PERFORM notification_request__delete(v_request.request_id); + END LOOP; + + return 0; +END; +' language 'plpgsql'; + + + +-- the notifications package +select define_function_args ('notification__new','notification_id,type_id,object_id,notif_date,response_id,notif_subject,notif_text,notif_html,creation_date,creation_user,creation_ip,context_id'); + +select define_function_args ('notification__delete','notification_id'); + +-- implementation + +create function notification__new(integer,integer,integer,timestamp,integer,varchar,text,text,timestamp,integer,varchar,integer) +returns integer as ' +DECLARE + p_notification_id alias for $1; + p_type_id alias for $2; + p_object_id alias for $3; + p_notif_date alias for $4; + p_response_id alias for $5; + p_notif_subject alias for $6; + p_notif_text alias for $7; + p_notif_html alias for $8; + p_creation_date alias for $9; + p_creation_user alias for $10; + p_creation_ip alias for $11; + p_context_id alias for $12; + v_notification_id integer; +BEGIN + v_notification_id:= acs_object__new ( + p_notification_id, + ''notification'', + p_creation_date, + p_creation_user, + p_creation_ip, + p_context_id); + + insert into notifications + (notification_id, type_id, object_id, notif_date, response_id, notif_subject, notif_text, notif_html) + values + (v_notification_id, p_type_id, p_object_id, p_notif_date, p_response_id, p_notif_subject, p_notif_text, p_notif_html); + + return v_notification_id; + +END; +' language 'plpgsql'; + + +create function notification__delete(integer) +returns integer as ' +DECLARE + p_notification_id alias for $1; +BEGIN + perform acs_object__delete(p_notification_id); + return 0; +END; +' language 'plpgsql'; + Index: openacs-4/packages/notifications/sql/postgresql/notifications-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/notifications/sql/postgresql/notifications-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/notifications/sql/postgresql/notifications-package-drop.sql 1 Jun 2002 19:04:45 -0000 1.1 @@ -0,0 +1,47 @@ + +-- +-- The Notifications Package +-- +-- ben@openforce.net +-- Copyright OpenForce, 2002. +-- +-- GNU GPL v2 +-- + + +-- The Notification Interval Package + +drop function notification_interval__new (integer, varchar, integer, timestamp, integer, varchar, integer); + +drop function notification_interval__delete(integer); + + +-- The notification delivery methods package + +drop function notification_delivery_method__new (integer, varchar, varchar, timestamp, integer, varchar, integer); + +drop function notification_delivery_method__delete(integer); + + +-- Notification Types Package + +drop function notification_type__new (integer,varchar,varchar,varchar,timestamp,integer,varchar,integer); + +drop function notification_type__delete(integer); + + +-- the notification request package + +drop function notification_request__new (integer,varchar,integer,integer,integer,integer,varchar,timestamp,integer,varchar,integer); + +drop function notification_request__delete(integer); + +drop function notification_request__delete_all(integer); + + +-- the notifications package + +drop function notification__new(integer,integer,integer,timestamp,integer,varchar,text,text,timestamp,integer,varchar,integer); + +drop function notification__delete(integer); +