Index: openacs-4/contrib/packages/mailing-lists/mailing-lists.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/mailing-lists.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/mailing-lists.info 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,35 @@
+
+
+
+
+ Mailing List
+ Mailing Lists
+ f
+ f
+
+
+ Timo Hentschel
+ ACS 4 mailing list package.
+ 2003-02-27
+ Sussdorff-Roy
+ First version of mailing lists.
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-create.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,311 @@
+-- use package vars for bounce manager (#of bounced emails to mark user
+-- as bouncing, #of days of last mail sent unbounced so that bounce
+-- history of user is deleted) and upper limit of attachment size
+
+begin
+ -- create the object types
+
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'mailing_list',
+ pretty_name => 'Mailing List',
+ pretty_plural => 'Mailing Lists',
+ table_name => 'ml_mailing_lists',
+ id_column => 'list_id',
+ name_method => 'ml_mailing_list.name'
+ );
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'mail_class',
+ pretty_name => 'Mailing Class',
+ pretty_plural => 'Mailing Classes',
+ table_name => 'ml_mail_classes',
+ id_column => 'mail_class_id',
+ name_method => 'ml_mail_class.name'
+ );
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'mail_job',
+ pretty_name => 'Mailing Job',
+ pretty_plural => 'Mailing Jobs',
+ table_name => 'ml_mail_jobs',
+ id_column => 'mail_job_id',
+ name_method => 'ml_mail_job.name'
+ );
+end;
+/
+show errors
+
+begin
+ -- create the privileges
+ acs_privilege.create_privilege('mailing_list_admin', 'Mailing Lists Administrator');
+
+ acs_privilege.add_child('admin','mailing_list_admin');
+end;
+/
+show errors
+
+create table ml_mailing_lists (
+ list_id integer
+ constraint ml_mailing_lists_pk
+ primary key,
+ package_id integer
+ constraint ml_mailing_lists_pck_id_fk
+ references apm_packages,
+ name varchar2(1000)
+ constraint ml_mailing_lists_name_nn
+ not null,
+ locale varchar2(5)
+ constraint ml_mailing_lists_locale_nn
+ not null
+ constraint ml_mail_lists_locale_fk
+ references ad_locales,
+ teaser clob,
+ public_p char(1) default 't'
+ constraint ml_lists_public_p_ck
+ check (public_p in ('t','f')),
+ sender_email varchar2(1000),
+ confirm_subject varchar2(1000),
+ confirm_body clob,
+ confirm_mime_type varchar2(30) default 'text/plain',
+ welcome_subject varchar2(1000),
+ welcome_body clob,
+ welcome_mime_type varchar2(30) default 'text/plain',
+ remind_subject varchar2(1000),
+ remind_body clob,
+ remind_mime_type varchar2(30) default 'text/plain',
+ expiration_date date,
+ -- amount of days after sending registration email
+ first_reminder integer default 7,
+ -- 0 if no second reminder
+ second_reminder integer default 30,
+ comments clob
+);
+
+
+-- target, distribution and campaign: use categories (will be posted)
+
+create table ml_mailing_list_user_map (
+ list_id integer
+ constraint ml_m_l_user_map_list_fk
+ references ml_mailing_lists
+ on delete cascade,
+ user_id integer
+ constraint ml_m_l_user_map_user_fk
+ references users
+ on delete cascade,
+ subscription_date date default sysdate,
+ unsubscription_date date,
+ subscribed_p char(1) default 't'
+ constraint ml_user_map_subscr_p_ck
+ check (subscribed_p in ('t','f')),
+ confirmed_p char(1) default 't'
+ constraint ml_user_map_confirm_p_ck
+ check (confirmed_p in ('t','f')),
+ reminder_count integer default 0,
+ constraint ml_mailing_list_user_map_pk
+ primary key (list_id, user_id)
+);
+
+-- to speed up queries to get all lists for a user
+create unique index ml_mailing_list_users_ix on ml_mailing_list_user_map (user_id, list_id);
+create index ml_mail_list_users_subsc_p_ix on ml_mailing_list_user_map(subscribed_p);
+
+
+create table ml_mail_classes (
+ mail_class_id integer
+ constraint ml_mail_classes_pk
+ primary key,
+ package_id integer
+ constraint ml_mail_classes_pck_id_fk
+ references apm_packages,
+ name varchar2(1000)
+ constraint ml_mail_classes_name_nn
+ not null,
+ locale varchar2(5)
+ constraint ml_mail_classes_locale_nn
+ not null
+ constraint ml_mail_classes_locale_fk
+ references ad_locales,
+ public_p char(1) default 'f'
+ constraint ml_class_public_p_ck
+ check (public_p in ('t','f')),
+ sender_email varchar2(100),
+ subject varchar2(1000),
+ subject_change_p char(1) default 't'
+ constraint ml_class_subj_change_p_ck
+ check (subject_change_p in ('t','f')),
+ text_header clob,
+ text_header_change_p char(1) default 't'
+ constraint ml_class_t_head_change_p_ck
+ check (text_header_change_p in ('t','f')),
+ text_body clob,
+ text_body_change_p char(1) default 't'
+ constraint ml_class_t_body_change_p_ck
+ check (text_body_change_p in ('t','f')),
+ text_footer clob,
+ text_footer_change_p char(1) default 't'
+ constraint ml_class_t_foot_change_p_ck
+ check (text_footer_change_p in ('t','f')),
+ html_header clob,
+ html_header_change_p char(1) default 't'
+ constraint ml_class_h_head_change_p_ck
+ check (html_header_change_p in ('t','f')),
+ html_body clob,
+ html_body_change_p char(1) default 't'
+ constraint ml_class_h_body_change_p_ck
+ check (html_body_change_p in ('t','f')),
+ html_footer clob,
+ html_footer_change_p char(1) default 't'
+ constraint ml_class_h_foot_change_p_ck
+ check (html_footer_change_p in ('t','f')),
+ mime_type varchar2(30) default 'text/plain',
+ comments clob
+);
+
+create table ml_mail_jobs (
+ mail_job_id integer
+ constraint ml_mail_jobs_pk
+ primary key,
+ mail_class_id integer
+ constraint ml_mail_jobs_mail_class_id_fk
+ references acs_objects
+ on delete set null,
+ list_id integer
+ constraint ml_mail_jobs_list_id_fk
+ references acs_objects
+ on delete set null,
+ selection_id integer
+ constraint ml_mail_jobs_sel_id_fk
+ references acs_objects
+ on delete set null,
+ package_id integer
+ constraint ml_mail_jobs_package_id_fk
+ references apm_packages,
+ locale varchar2(5)
+ constraint ml_mail_jobs_locale_nn
+ not null
+ constraint ml_mail_jobs_locale_fk
+ references ad_locales,
+ sender_email varchar2(100),
+ track_links_p char(1) default 'f'
+ constraint ml_mail_jobs_track_p_ck
+ check (track_links_p in ('t','f')),
+ subject varchar2(1000),
+ text_header clob,
+ text_body clob,
+ text_footer clob,
+ html_header clob,
+ html_body clob,
+ html_footer clob,
+ template_p char(1) default 'f'
+ constraint ml_mail_jobs_template_p_ck
+ check (template_p in ('t','f')),
+ mime_type varchar2(30) default 'text/plain',
+ css_revision_id integer
+ constraint ml_mail_jobs_css_rev_id_fk
+ references cr_revisions on delete set null,
+ state varchar2(10),
+ scheduled_date date default sysdate,
+ execution_date date,
+ sql_query clob,
+ bind_vars varchar2(4000),
+ mails_sent integer default 0,
+ mails_bounced integer default 0
+);
+
+create index ml_jobs_scheduled_date_ix on ml_mail_jobs(scheduled_date);
+create index ml_jobs_execution_date_ix on ml_mail_jobs(execution_date);
+
+create table ml_mail_job_bind_vars (
+ mail_job_id integer
+ constraint ml_m_job_b_vars_job_id_fk
+ references ml_mail_jobs
+ on delete cascade,
+ name varchar2(30),
+ description varchar2(4000),
+ value varchar2(4000),
+ constraint ml_mail_job_bind_vars_pk
+ primary key (mail_job_id, name)
+);
+
+-- make sure that this table doesn't fill up too much
+-- i.e. delete all data older than 7 days
+
+create table ml_email_log (
+ user_id integer
+ constraint ml_email_log_user_id_fk
+ references users,
+ mail_job_id integer
+ constraint ml_email_log_mail_job_id_fk
+ references ml_mail_jobs,
+ send_date date default sysdate,
+ constraint ml_email_log_pk
+ primary key (user_id, mail_job_id)
+);
+
+create index ml_email_log_date_ix on ml_email_log(send_date);
+
+create table ml_user_email_log (
+ user_id integer primary key
+ constraint ml_user_email_log_id_fk
+ references users,
+ last_mail_date date default null
+);
+
+create index ml_user_email_log_date_ix on ml_user_email_log(last_mail_date);
+
+create table ml_bounce_log (
+ mail_job_id integer
+ constraint ml_bounce_log_mail_job_id_fk
+ references ml_mail_jobs
+ on delete cascade,
+ user_id integer
+ constraint ml_bounce_log_user_id_fk
+ references users
+ on delete cascade,
+ sending_time date,
+ bouncing_time date default sysdate,
+ constraint ml_bounce_log_pk
+ primary key (mail_job_id, user_id)
+);
+
+create index ml_bounce_log_bounce_time_ix on ml_bounce_log(bouncing_time);
+
+create table ml_category_trees_visible (
+ tree_id integer
+ constraint ml_cat_trees_vis_tree_id_fk
+ references acs_objects
+ on delete cascade,
+ package_id integer
+ constraint ml_cat_trees_vis_pck_id_fk
+ references apm_packages,
+ constraint ml_cat_trees_vis_pk
+ primary key (package_id, tree_id)
+);
+
+create table ml_country_category_tree (
+ tree_id integer
+ constraint ml_country_category_tree_id_fk
+ references acs_objects
+ on delete cascade,
+ package_id integer
+ constraint ml_country_cat_tree_pck_id_fk
+ references apm_packages,
+ constraint ml_country_category_tree_pk
+ primary key (package_id, tree_id)
+);
+
+create table ml_country_codes (
+ country_code varchar2(3)
+ constraint ml_country_codes_pk
+ primary key,
+ category_id integer
+ constraint ml_country_codes_cat_id_fk
+ references acs_objects
+ on delete cascade
+);
+
+
+@@mailing-lists-package.sql
+@@mailing-lists-init.sql
Index: openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-drop.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,55 @@
+drop package ml_mailing_list;
+drop package ml_mail_class;
+drop package ml_mail_job;
+
+drop table ml_country_codes;
+drop table ml_country_category_tree;
+drop table ml_category_trees_visible;
+drop table ml_bounce_log;
+drop table ml_user_email_log;
+drop table ml_email_log;
+drop table ml_mail_job_bind_vars;
+drop table ml_mail_jobs;
+drop table ml_mail_classes;
+drop table ml_mailing_list_user_map;
+drop table ml_mailing_lists;
+
+delete from acs_objects where object_type='mailing_list';
+delete from acs_objects where object_type='mail_class';
+delete from acs_objects where object_type='mail_job';
+
+delete from acs_permissions
+where privilege in ('mailing_list_admin');
+
+delete from acs_privilege_hierarchy
+where privilege in ('mailing_list_admin');
+
+delete from acs_privilege_hierarchy
+where child_privilege in ('mailing_list_admin');
+
+delete from acs_privileges
+where privilege in ('mailing_list_admin');
+
+begin
+ acs_object_type.drop_type('mailing_list');
+ acs_object_type.drop_type('mail_class');
+ acs_object_type.drop_type('mail_job');
+
+ acs_sc_binding.delete(
+ contract_name => 'AcsObject',
+ impl_name => 'mailing_list_idhandler'
+ );
+ acs_sc_impl.delete('AcsObject', 'mailing_list_idhandler');
+ acs_sc_binding.delete(
+ contract_name => 'AcsObject',
+ impl_name => 'mail_class_idhandler'
+ );
+ acs_sc_impl.delete('AcsObject', 'mail_class_idhandler');
+ acs_sc_binding.delete(
+ contract_name => 'AcsObject',
+ impl_name => 'mail_job_idhandler'
+ );
+ acs_sc_impl.delete('AcsObject', 'mail_job_idhandler');
+end;
+/
+show errors
Index: openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-init.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-init.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,56 @@
+declare
+ v_id integer;
+begin
+ v_id := acs_sc_impl.new (
+ 'AcsObject',
+ 'mailing_list_idhandler',
+ 'mailing-lists'
+ );
+ v_id := acs_sc_impl.new_alias (
+ 'AcsObject',
+ 'mailing_list_idhandler',
+ 'PageUrl',
+ 'ml_get_mailing_list_pageurl',
+ 'TCL'
+ );
+ acs_sc_binding.new (
+ contract_name => 'AcsObject',
+ impl_name => 'mailing_list_idhandler'
+ );
+
+ v_id := acs_sc_impl.new (
+ 'AcsObject',
+ 'mail_class_idhandler',
+ 'mailing-lists'
+ );
+ v_id := acs_sc_impl.new_alias (
+ 'AcsObject',
+ 'mail_class_idhandler',
+ 'PageUrl',
+ 'ml_get_mail_class_pageurl',
+ 'TCL'
+ );
+ acs_sc_binding.new (
+ contract_name => 'AcsObject',
+ impl_name => 'mail_class_idhandler'
+ );
+
+ v_id := acs_sc_impl.new (
+ 'AcsObject',
+ 'mail_job_idhandler',
+ 'mailing-lists'
+ );
+ v_id := acs_sc_impl.new_alias (
+ 'AcsObject',
+ 'mail_job_idhandler',
+ 'PageUrl',
+ 'ml_get_mail_job_pageurl',
+ 'TCL'
+ );
+ acs_sc_binding.new (
+ contract_name => 'AcsObject',
+ impl_name => 'mail_job_idhandler'
+ );
+end;
+/
+show errors
Index: openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-package.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-package.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/oracle/mailing-lists-package.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,633 @@
+create or replace package ml_mailing_list
+as
+ function new (
+ list_id in ml_mailing_lists.list_id%TYPE default null,
+ package_id in ml_mailing_lists.package_id%TYPE default null,
+ name in ml_mailing_lists.name%TYPE,
+ locale in ml_mailing_lists.locale%TYPE,
+ teaser in ml_mailing_lists.teaser%TYPE default null,
+ sender_email in ml_mailing_lists.sender_email%TYPE default null,
+ confirm_subject in ml_mailing_lists.confirm_subject%TYPE default null,
+ confirm_body in ml_mailing_lists.confirm_body%TYPE default null,
+ confirm_mime_type in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
+ welcome_subject in ml_mailing_lists.welcome_subject%TYPE default null,
+ welcome_body in ml_mailing_lists.welcome_body%TYPE default null,
+ welcome_mime_type in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
+ remind_subject in ml_mailing_lists.remind_subject%TYPE default null,
+ remind_body in ml_mailing_lists.remind_body%TYPE default null,
+ remind_mime_type in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
+ expiration_date in ml_mailing_lists.expiration_date%TYPE default sysdate,
+ first_reminder in ml_mailing_lists.first_reminder%TYPE default 7,
+ second_reminder in ml_mailing_lists.second_reminder%TYPE default 30,
+ comments in ml_mailing_lists.comments%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer;
+
+ procedure delete (
+ list_id in ml_mailing_lists.list_id%TYPE
+ );
+
+ procedure edit (
+ list_id in ml_mailing_lists.list_id%TYPE default null,
+ name in ml_mailing_lists.name%TYPE,
+ locale in ml_mailing_lists.locale%TYPE,
+ teaser in ml_mailing_lists.teaser%TYPE default null,
+ sender_email in ml_mailing_lists.sender_email%TYPE default null,
+ confirm_subject in ml_mailing_lists.confirm_subject%TYPE default null,
+ confirm_body in ml_mailing_lists.confirm_body%TYPE default null,
+ confirm_mime_type in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
+ welcome_subject in ml_mailing_lists.welcome_subject%TYPE default null,
+ welcome_body in ml_mailing_lists.welcome_body%TYPE default null,
+ welcome_mime_type in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
+ remind_subject in ml_mailing_lists.remind_subject%TYPE default null,
+ remind_body in ml_mailing_lists.remind_body%TYPE default null,
+ remind_mime_type in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
+ expiration_date in ml_mailing_lists.expiration_date%TYPE default sysdate,
+ first_reminder in ml_mailing_lists.first_reminder%TYPE default 7,
+ second_reminder in ml_mailing_lists.second_reminder%TYPE default 30,
+ comments in ml_mailing_lists.comments%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ );
+
+ function name (
+ list_id in ml_mailing_lists.list_id%TYPE
+ ) return varchar2;
+
+end ml_mailing_list;
+/
+show errors
+
+create or replace package body ml_mailing_list
+as
+ function new (
+ list_id in ml_mailing_lists.list_id%TYPE default null,
+ package_id in ml_mailing_lists.package_id%TYPE default null,
+ name in ml_mailing_lists.name%TYPE,
+ locale in ml_mailing_lists.locale%TYPE,
+ teaser in ml_mailing_lists.teaser%TYPE default null,
+ sender_email in ml_mailing_lists.sender_email%TYPE default null,
+ confirm_subject in ml_mailing_lists.confirm_subject%TYPE default null,
+ confirm_body in ml_mailing_lists.confirm_body%TYPE default null,
+ confirm_mime_type in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
+ welcome_subject in ml_mailing_lists.welcome_subject%TYPE default null,
+ welcome_body in ml_mailing_lists.welcome_body%TYPE default null,
+ welcome_mime_type in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
+ remind_subject in ml_mailing_lists.remind_subject%TYPE default null,
+ remind_body in ml_mailing_lists.remind_body%TYPE default null,
+ remind_mime_type in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
+ expiration_date in ml_mailing_lists.expiration_date%TYPE default sysdate,
+ first_reminder in ml_mailing_lists.first_reminder%TYPE default 7,
+ second_reminder in ml_mailing_lists.second_reminder%TYPE default 30,
+ comments in ml_mailing_lists.comments%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer
+ is
+ v_list_id ml_mailing_lists.list_id%TYPE;
+ begin
+ v_list_id := acs_object.new (
+ object_id => new.list_id,
+ object_type => 'mailing_list',
+ creation_date => new.creation_date,
+ creation_user => new.creation_user,
+ creation_ip => new.creation_ip,
+ context_id => new.context_id
+ );
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_list_id, name, package_id);
+
+ insert into ml_mailing_lists
+ (list_id, package_id, name, locale, teaser, sender_email,
+ welcome_subject, welcome_body, welcome_mime_type,
+ confirm_subject, confirm_body, confirm_mime_type,
+ remind_subject, remind_body, remind_mime_type,
+ expiration_date, first_reminder, second_reminder, comments)
+ values
+ (v_list_id, package_id, name, locale, teaser, sender_email,
+ welcome_subject, welcome_body, welcome_mime_type,
+ confirm_subject, confirm_body, confirm_mime_type,
+ remind_subject, remind_body, remind_mime_type,
+ expiration_date, first_reminder, second_reminder, comments);
+
+ return v_list_id;
+ end new;
+
+ procedure delete (
+ list_id in ml_mailing_lists.list_id%TYPE
+ ) is
+ begin
+ delete from ml_mailing_lists where list_id = ml_mailing_list.delete.list_id;
+ acs_object.delete(list_id);
+ end delete;
+
+ procedure edit (
+ list_id in ml_mailing_lists.list_id%TYPE default null,
+ name in ml_mailing_lists.name%TYPE,
+ locale in ml_mailing_lists.locale%TYPE,
+ teaser in ml_mailing_lists.teaser%TYPE default null,
+ sender_email in ml_mailing_lists.sender_email%TYPE default null,
+ confirm_subject in ml_mailing_lists.confirm_subject%TYPE default null,
+ confirm_body in ml_mailing_lists.confirm_body%TYPE default null,
+ confirm_mime_type in ml_mailing_lists.confirm_mime_type%TYPE default 'text/plain',
+ welcome_subject in ml_mailing_lists.welcome_subject%TYPE default null,
+ welcome_body in ml_mailing_lists.welcome_body%TYPE default null,
+ welcome_mime_type in ml_mailing_lists.welcome_mime_type%TYPE default 'text/plain',
+ remind_subject in ml_mailing_lists.remind_subject%TYPE default null,
+ remind_body in ml_mailing_lists.remind_body%TYPE default null,
+ remind_mime_type in ml_mailing_lists.remind_mime_type%TYPE default 'text/plain',
+ expiration_date in ml_mailing_lists.expiration_date%TYPE default sysdate,
+ first_reminder in ml_mailing_lists.first_reminder%TYPE default 7,
+ second_reminder in ml_mailing_lists.second_reminder%TYPE default 30,
+ comments in ml_mailing_lists.comments%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ ) is
+ begin
+ update ml_mailing_lists
+ set name = edit.name,
+ locale = edit.locale,
+ teaser = edit.teaser,
+ sender_email = edit.sender_email,
+ confirm_subject = edit.confirm_subject,
+ confirm_body = edit.confirm_body,
+ confirm_mime_type = edit.confirm_mime_type,
+ welcome_subject = edit.welcome_subject,
+ welcome_body = edit.welcome_body,
+ welcome_mime_type = edit.welcome_mime_type,
+ remind_subject = edit.remind_subject,
+ remind_body = edit.remind_body,
+ remind_mime_type = edit.remind_mime_type,
+ expiration_date = edit.expiration_date,
+ first_reminder = edit.first_reminder,
+ second_reminder = edit.second_reminder,
+ comments = edit.comments
+ where list_id = edit.list_id;
+
+ update acs_named_objects
+ set object_name = edit.name
+ where object_id = edit.list_id;
+
+ update acs_objects
+ set modifying_user = edit.modifying_user,
+ modifying_ip = edit.modifying_ip
+ where object_id = edit.list_id;
+ end edit;
+
+ function name (
+ list_id in ml_mailing_lists.list_id%TYPE
+ ) return varchar2
+ is
+ v_name ml_mailing_lists.name%TYPE;
+ begin
+ select name into v_name
+ from ml_mailing_lists
+ where list_id = name.list_id;
+
+ return v_name;
+ end name;
+end ml_mailing_list;
+/
+show errors
+
+
+
+
+
+create or replace package ml_mail_class
+as
+ function new (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE default null,
+ package_id in ml_mail_classes.package_id%TYPE default null,
+ name in ml_mail_classes.name%TYPE default null,
+ locale in ml_mail_classes.locale%TYPE default null,
+ sender_email in ml_mail_classes.sender_email%TYPE default null,
+ subject in ml_mail_classes.subject%TYPE default null,
+ subject_change_p in ml_mail_classes.subject_change_p%TYPE default 't',
+ text_header in ml_mail_classes.text_header%TYPE default null,
+ text_header_change_p in ml_mail_classes.text_header_change_p%TYPE default 't',
+ text_body in ml_mail_classes.text_body%TYPE default null,
+ text_body_change_p in ml_mail_classes.text_body_change_p%TYPE default 't',
+ text_footer in ml_mail_classes.text_footer%TYPE default null,
+ text_footer_change_p in ml_mail_classes.text_footer_change_p%TYPE default 't',
+ html_header in ml_mail_classes.html_header%TYPE default null,
+ html_header_change_p in ml_mail_classes.html_header_change_p%TYPE default 't',
+ html_body in ml_mail_classes.html_body%TYPE default null,
+ html_body_change_p in ml_mail_classes.html_body_change_p%TYPE default 't',
+ html_footer in ml_mail_classes.html_footer%TYPE default null,
+ html_footer_change_p in ml_mail_classes.html_footer_change_p%TYPE default 't',
+ mime_type in ml_mail_classes.mime_type%TYPE default 'text/plain',
+ comments in ml_mail_classes.comments%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer;
+
+ procedure delete (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE
+ );
+
+ procedure edit (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE default null,
+ name in ml_mail_classes.name%TYPE default null,
+ locale in ml_mail_classes.locale%TYPE default null,
+ sender_email in ml_mail_classes.sender_email%TYPE default null,
+ subject in ml_mail_classes.subject%TYPE default null,
+ subject_change_p in ml_mail_classes.subject_change_p%TYPE default 't',
+ text_header in ml_mail_classes.text_header%TYPE default null,
+ text_header_change_p in ml_mail_classes.text_header_change_p%TYPE default 't',
+ text_body in ml_mail_classes.text_body%TYPE default null,
+ text_body_change_p in ml_mail_classes.text_body_change_p%TYPE default 't',
+ text_footer in ml_mail_classes.text_footer%TYPE default null,
+ text_footer_change_p in ml_mail_classes.text_footer_change_p%TYPE default 't',
+ html_header in ml_mail_classes.html_header%TYPE default null,
+ html_header_change_p in ml_mail_classes.html_header_change_p%TYPE default 't',
+ html_body in ml_mail_classes.html_body%TYPE default null,
+ html_body_change_p in ml_mail_classes.html_body_change_p%TYPE default 't',
+ html_footer in ml_mail_classes.html_footer%TYPE default null,
+ html_footer_change_p in ml_mail_classes.html_footer_change_p%TYPE default 't',
+ mime_type in ml_mail_classes.mime_type%TYPE default 'text/plain',
+ comments in ml_mail_classes.comments%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ );
+
+ function name (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE
+ ) return varchar2;
+
+end ml_mail_class;
+/
+show errors
+
+create or replace package body ml_mail_class
+as
+ function new (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE default null,
+ package_id in ml_mail_classes.package_id%TYPE default null,
+ name in ml_mail_classes.name%TYPE default null,
+ locale in ml_mail_classes.locale%TYPE default null,
+ sender_email in ml_mail_classes.sender_email%TYPE default null,
+ subject in ml_mail_classes.subject%TYPE default null,
+ subject_change_p in ml_mail_classes.subject_change_p%TYPE default 't',
+ text_header in ml_mail_classes.text_header%TYPE default null,
+ text_header_change_p in ml_mail_classes.text_header_change_p%TYPE default 't',
+ text_body in ml_mail_classes.text_body%TYPE default null,
+ text_body_change_p in ml_mail_classes.text_body_change_p%TYPE default 't',
+ text_footer in ml_mail_classes.text_footer%TYPE default null,
+ text_footer_change_p in ml_mail_classes.text_footer_change_p%TYPE default 't',
+ html_header in ml_mail_classes.html_header%TYPE default null,
+ html_header_change_p in ml_mail_classes.html_header_change_p%TYPE default 't',
+ html_body in ml_mail_classes.html_body%TYPE default null,
+ html_body_change_p in ml_mail_classes.html_body_change_p%TYPE default 't',
+ html_footer in ml_mail_classes.html_footer%TYPE default null,
+ html_footer_change_p in ml_mail_classes.html_footer_change_p%TYPE default 't',
+ mime_type in ml_mail_classes.mime_type%TYPE default 'text/plain',
+ comments in ml_mail_classes.comments%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer
+ is
+ v_class_id ml_mail_classes.mail_class_id%TYPE;
+ begin
+ v_class_id := acs_object.new (
+ object_id => new.mail_class_id,
+ object_type => 'mail_class',
+ creation_date => new.creation_date,
+ creation_user => new.creation_user,
+ creation_ip => new.creation_ip,
+ context_id => new.context_id
+ );
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_class_id, name, package_id);
+
+ insert into ml_mail_classes
+ (mail_class_id, package_id, name, locale, sender_email,
+ subject, subject_change_p, text_header, text_header_change_p,
+ text_body, text_body_change_p, text_footer, text_footer_change_p,
+ html_header, html_header_change_p, html_body, html_body_change_p,
+ html_footer, html_footer_change_p, mime_type, comments)
+ values
+ (v_class_id, package_id, name, locale, sender_email,
+ subject, subject_change_p, text_header, text_header_change_p,
+ text_body, text_body_change_p, text_footer, text_footer_change_p,
+ html_header, html_header_change_p, html_body, html_body_change_p,
+ html_footer, html_footer_change_p, mime_type, comments);
+ return v_class_id;
+ end new;
+
+ procedure delete (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE
+ ) is
+ begin
+ for attachment in (select item_id from cr_items
+ where parent_id = mail_class_id) loop
+ content_item.delete(attachment.item_id);
+ end loop;
+
+ delete from ml_mail_classes where mail_class_id = ml_mail_class.delete.mail_class_id;
+ acs_object.delete(mail_class_id);
+ end delete;
+
+ procedure edit (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE default null,
+ name in ml_mail_classes.name%TYPE default null,
+ locale in ml_mail_classes.locale%TYPE default null,
+ sender_email in ml_mail_classes.sender_email%TYPE default null,
+ subject in ml_mail_classes.subject%TYPE default null,
+ subject_change_p in ml_mail_classes.subject_change_p%TYPE default 't',
+ text_header in ml_mail_classes.text_header%TYPE default null,
+ text_header_change_p in ml_mail_classes.text_header_change_p%TYPE default 't',
+ text_body in ml_mail_classes.text_body%TYPE default null,
+ text_body_change_p in ml_mail_classes.text_body_change_p%TYPE default 't',
+ text_footer in ml_mail_classes.text_footer%TYPE default null,
+ text_footer_change_p in ml_mail_classes.text_footer_change_p%TYPE default 't',
+ html_header in ml_mail_classes.html_header%TYPE default null,
+ html_header_change_p in ml_mail_classes.html_header_change_p%TYPE default 't',
+ html_body in ml_mail_classes.html_body%TYPE default null,
+ html_body_change_p in ml_mail_classes.html_body_change_p%TYPE default 't',
+ html_footer in ml_mail_classes.html_footer%TYPE default null,
+ html_footer_change_p in ml_mail_classes.html_footer_change_p%TYPE default 't',
+ mime_type in ml_mail_classes.mime_type%TYPE default 'text/plain',
+ comments in ml_mail_classes.comments%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ ) is
+ begin
+ update ml_mail_classes
+ set name = edit.name,
+ locale = edit.locale,
+ sender_email = edit.sender_email,
+ subject = edit.subject,
+ subject_change_p = edit.subject_change_p,
+ text_header = edit.text_header,
+ text_header_change_p = edit.text_header_change_p,
+ text_body = edit.text_body,
+ text_body_change_p = edit.text_body_change_p,
+ text_footer = edit.text_footer,
+ text_footer_change_p = edit.text_footer_change_p,
+ html_header = edit.html_header,
+ html_header_change_p = edit.html_header_change_p,
+ html_body = edit.html_body,
+ html_body_change_p = edit.html_body_change_p,
+ html_footer = edit.html_footer,
+ html_footer_change_p = edit.html_footer_change_p,
+ mime_type = edit.mime_type,
+ comments = edit.comments
+ where mail_class_id = edit.mail_class_id;
+
+ update acs_named_objects
+ set object_name = edit.name
+ where object_id = edit.mail_class_id;
+
+ update acs_objects
+ set modifying_user = edit.modifying_user,
+ modifying_ip = edit.modifying_ip
+ where object_id = edit.mail_class_id;
+ end edit;
+
+ function name (
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE
+ ) return varchar2
+ is
+ v_name ml_mail_classes.name%TYPE;
+ begin
+ select name into v_name
+ from ml_mail_classes
+ where mail_class_id = name.mail_class_id;
+
+ return v_name;
+ end name;
+end ml_mail_class;
+/
+show errors
+
+
+
+
+
+create or replace package ml_mail_job
+as
+ function new (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE default null,
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE,
+ list_id in ml_mail_jobs.list_id%TYPE default null,
+ selection_id in ml_mail_jobs.selection_id%TYPE default null,
+ package_id in ml_mail_jobs.package_id%TYPE default null,
+ locale in ml_mail_jobs.locale%TYPE,
+ sender_email in ml_mail_jobs.sender_email%TYPE default null,
+ track_links_p in ml_mail_jobs.track_links_p%TYPE default 'f',
+ subject in ml_mail_jobs.subject%TYPE default null,
+ text_header in ml_mail_jobs.text_header%TYPE default null,
+ text_body in ml_mail_jobs.text_body%TYPE default null,
+ text_footer in ml_mail_jobs.text_footer%TYPE default null,
+ html_header in ml_mail_jobs.html_header%TYPE default null,
+ html_body in ml_mail_jobs.html_body%TYPE default null,
+ html_footer in ml_mail_jobs.html_footer%TYPE default null,
+ template_p in ml_mail_jobs.template_p%TYPE default 'f',
+ mime_type in ml_mail_jobs.mime_type%TYPE default 'text/plain',
+ state in ml_mail_jobs.state%TYPE default 'active',
+ scheduled_date in ml_mail_jobs.scheduled_date%TYPE default sysdate,
+ bind_vars in ml_mail_jobs.bind_vars%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer;
+
+ procedure delete (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE
+ );
+
+ procedure edit (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE,
+ locale in ml_mail_jobs.locale%TYPE,
+ sender_email in ml_mail_jobs.sender_email%TYPE default null,
+ track_links_p in ml_mail_jobs.track_links_p%TYPE default 'f',
+ subject in ml_mail_jobs.subject%TYPE default null,
+ text_header in ml_mail_jobs.text_header%TYPE default null,
+ text_body in ml_mail_jobs.text_body%TYPE default null,
+ text_footer in ml_mail_jobs.text_footer%TYPE default null,
+ html_header in ml_mail_jobs.html_header%TYPE default null,
+ html_body in ml_mail_jobs.html_body%TYPE default null,
+ html_footer in ml_mail_jobs.html_footer%TYPE default null,
+ template_p in ml_mail_jobs.template_p%TYPE default 'f',
+ mime_type in ml_mail_jobs.mime_type%TYPE default 'text/plain',
+ state in ml_mail_jobs.state%TYPE default 'active',
+ scheduled_date in ml_mail_jobs.scheduled_date%TYPE default sysdate,
+ bind_vars in ml_mail_jobs.bind_vars%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ );
+
+ function name (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE
+ ) return varchar2;
+
+end ml_mail_job;
+/
+show errors
+
+create or replace package body ml_mail_job
+as
+ function new (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE default null,
+ mail_class_id in ml_mail_classes.mail_class_id%TYPE,
+ list_id in ml_mail_jobs.list_id%TYPE default null,
+ selection_id in ml_mail_jobs.selection_id%TYPE default null,
+ package_id in ml_mail_jobs.package_id%TYPE default null,
+ locale in ml_mail_jobs.locale%TYPE,
+ sender_email in ml_mail_jobs.sender_email%TYPE default null,
+ track_links_p in ml_mail_jobs.track_links_p%TYPE default 'f',
+ subject in ml_mail_jobs.subject%TYPE default null,
+ text_header in ml_mail_jobs.text_header%TYPE default null,
+ text_body in ml_mail_jobs.text_body%TYPE default null,
+ text_footer in ml_mail_jobs.text_footer%TYPE default null,
+ html_header in ml_mail_jobs.html_header%TYPE default null,
+ html_body in ml_mail_jobs.html_body%TYPE default null,
+ html_footer in ml_mail_jobs.html_footer%TYPE default null,
+ template_p in ml_mail_jobs.template_p%TYPE default 'f',
+ mime_type in ml_mail_jobs.mime_type%TYPE default 'text/plain',
+ state in ml_mail_jobs.state%TYPE default 'active',
+ scheduled_date in ml_mail_jobs.scheduled_date%TYPE default sysdate,
+ bind_vars in ml_mail_jobs.bind_vars%TYPE default null,
+ context_id in acs_objects.context_id%TYPE default null,
+ creation_user in acs_objects.creation_user%TYPE default null,
+ creation_date in acs_objects.creation_date%TYPE default sysdate,
+ creation_ip in acs_objects.creation_ip%TYPE default null
+ ) return integer
+ is
+ v_mail_job_id ml_mail_jobs.mail_job_id%TYPE;
+ v_revision_id cr_revisions.revision_id%TYPE;
+ begin
+ v_mail_job_id := acs_object.new (
+ object_id => new.mail_job_id,
+ object_type => 'mail_job',
+ creation_date => new.creation_date,
+ creation_user => new.creation_user,
+ creation_ip => new.creation_ip,
+ context_id => new.context_id
+ );
+
+ begin
+ select live_revision into v_revision_id
+ from cr_items
+ where parent_id = new.mail_class_id;
+ exception when no_data_found then
+ v_revision_id := null;
+ end;
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_mail_job_id, subject, package_id);
+
+ insert into ml_mail_jobs
+ (mail_job_id, list_id, selection_id, package_id, locale,
+ sender_email, track_links_p, subject, text_header, text_body,
+ text_footer, html_header, html_body, html_footer,
+ template_p, mime_type, state, scheduled_date,
+ css_revision_id, mail_class_id, bind_vars)
+ values
+ (v_mail_job_id, list_id, selection_id, package_id, locale,
+ sender_email, track_links_p, subject, text_header, text_body,
+ text_footer, html_header, html_body, html_footer,
+ template_p, mime_type, state, scheduled_date,
+ v_revision_id, mail_class_id, bind_vars);
+
+ if (new.selection_id is not null) then
+ insert into ml_mail_job_bind_vars
+ (select v_mail_job_id as mail_job_id, b.name, b.description,
+ b.default_value as value
+ from us_bind_vars b
+ where b.selection_id = new.selection_id);
+ end if;
+
+ return v_mail_job_id;
+ end new;
+
+ procedure delete (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE
+ ) is
+ begin
+ for attachment in (select item_id from cr_items
+ where parent_id = mail_job_id) loop
+ content_item.delete(attachment.item_id);
+ end loop;
+ delete from ml_mail_jobs where mail_job_id = ml_mail_job.delete.mail_job_id;
+ acs_object.delete(mail_job_id);
+ end delete;
+
+ procedure edit (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE,
+ locale in ml_mail_jobs.locale%TYPE,
+ sender_email in ml_mail_jobs.sender_email%TYPE default null,
+ track_links_p in ml_mail_jobs.track_links_p%TYPE default 'f',
+ subject in ml_mail_jobs.subject%TYPE default null,
+ text_header in ml_mail_jobs.text_header%TYPE default null,
+ text_body in ml_mail_jobs.text_body%TYPE default null,
+ text_footer in ml_mail_jobs.text_footer%TYPE default null,
+ html_header in ml_mail_jobs.html_header%TYPE default null,
+ html_body in ml_mail_jobs.html_body%TYPE default null,
+ html_footer in ml_mail_jobs.html_footer%TYPE default null,
+ template_p in ml_mail_jobs.template_p%TYPE default 'f',
+ mime_type in ml_mail_jobs.mime_type%TYPE default 'text/plain',
+ state in ml_mail_jobs.state%TYPE default 'active',
+ scheduled_date in ml_mail_jobs.scheduled_date%TYPE default sysdate,
+ bind_vars in ml_mail_jobs.bind_vars%TYPE default null,
+ modifying_user in acs_objects.modifying_user%TYPE default null,
+ modifying_ip in acs_objects.modifying_ip%TYPE default null
+ ) is
+ begin
+ update ml_mail_jobs
+ set locale = edit.locale,
+ sender_email = edit.sender_email,
+ track_links_p = edit.track_links_p,
+ subject = edit.subject,
+ text_header = edit.text_header,
+ text_body = edit.text_body,
+ text_footer = edit.text_footer,
+ html_header = edit.html_header,
+ html_body = edit.html_body,
+ html_footer = edit.html_footer,
+ template_p = edit.template_p,
+ mime_type = edit.mime_type,
+ state = edit.state,
+ scheduled_date = edit.scheduled_date,
+ bind_vars = edit.bind_vars
+ where mail_job_id = edit.mail_job_id
+ and state <> 'done';
+
+ update acs_named_objects
+ set object_name = edit.subject
+ where object_id = edit.mail_job_id;
+
+ update acs_objects
+ set modifying_user = edit.modifying_user,
+ modifying_ip = edit.modifying_ip
+ where object_id = edit.mail_job_id;
+ end edit;
+
+ function name (
+ mail_job_id in ml_mail_jobs.mail_job_id%TYPE
+ ) return varchar2
+ is
+ v_name ml_mail_jobs.subject%TYPE;
+ begin
+ select subject into v_name
+ from ml_mail_jobs
+ where mail_job_id = name.mail_job_id;
+
+ return v_name;
+ end name;
+end ml_mail_job;
+/
+show errors
Index: openacs-4/contrib/packages/mailing-lists/sql/oracle/upgrade.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/oracle/upgrade.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/oracle/upgrade.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,177 @@
+alter table ml_mail_classes drop constraint ml_class_subj_change_p_ck;
+
+alter table ml_mail_classes add (
+ subject varchar2(1000),
+ subject_change_p char(1) default 't' constraint ml_class_subj_change_p_ck check (subject_change_p in ('t','f')),
+ text_header varchar2(4000),
+ text_header_change_p char(1) default 't' constraint ml_class_t_head_change_p_ck check (text_header_change_p in ('t','f')),
+ text_body varchar2(4000),
+ text_body_change_p char(1) default 't' constraint ml_class_t_body_change_p_ck check (text_body_change_p in ('t','f')),
+ text_footer varchar2(4000),
+ text_footer_change_p char(1) default 't' constraint ml_class_t_foot_change_p_ck check (text_footer_change_p in ('t','f')),
+ html_header varchar2(4000),
+ html_header_change_p char(1) default 't' constraint ml_class_h_head_change_p_ck check (html_header_change_p in ('t','f')),
+ html_body varchar2(4000),
+ html_body_change_p char(1) default 't' constraint ml_class_h_body_change_p_ck check (html_body_change_p in ('t','f')),
+ html_footer varchar2(4000),
+ html_footer_change_p char(1) default 't' constraint ml_class_h_foot_change_p_ck check (html_footer_change_p in ('t','f'))
+);
+
+update ml_mail_classes
+set subject = default_subject,
+subject_change_p = subject_changeable_p,
+text_header = default_header,
+text_header_change_p = header_changeable_p,
+text_body = default_body,
+text_body_change_p = body_changeable_p,
+text_footer = default_footer,
+text_footer_change_p = footer_changeable_p
+where mime_type = 'text/plain';
+
+update ml_mail_classes
+set subject = default_subject,
+subject_change_p = subject_changeable_p,
+html_header = default_header,
+html_header_change_p = header_changeable_p,
+html_body = default_body,
+html_body_change_p = body_changeable_p,
+html_footer = default_footer,
+html_footer_change_p = footer_changeable_p
+where mime_type = 'text/html';
+
+alter table ml_mail_classes drop column default_subject;
+alter table ml_mail_classes drop column default_header;
+alter table ml_mail_classes drop column default_body;
+alter table ml_mail_classes drop column default_footer;
+alter table ml_mail_classes drop column subject_changeable_p;
+alter table ml_mail_classes drop column header_changeable_p;
+alter table ml_mail_classes drop column body_changeable_p;
+alter table ml_mail_classes drop column footer_changeable_p;
+alter table ml_mail_classes drop column x_field_p;
+
+
+alter table ml_mail_jobs add (
+ text_header varchar2(4000),
+ text_body clob,
+ text_footer varchar2(4000),
+ html_header varchar2(4000),
+ html_body clob,
+ html_footer varchar2(4000),
+ mails_bounced integer default 0
+);
+
+update ml_mail_jobs
+set text_header = header,
+text_footer = footer
+where mime_type = 'text/plain';
+
+update ml_mail_jobs
+set html_header = header,
+html_footer = footer
+where mime_type = 'text/html';
+
+declare
+ new_body clob;
+begin
+ for t in (select mail_job_id, body, dbms_lob.getlength(body) as lob_length from ml_mail_jobs where mime_type = 'text/plain') loop
+ new_body:=empty_clob();
+ dbms_lob.copy(new_body, t.body, t.lob_length);
+ update ml_mail_jobs set text_body = new_body where mail_job_id = t.mail_job_id;
+ end loop;
+end;
+/
+
+update ml_mail_jobs
+set text_body = body
+where mime_type = 'text/plain';
+
+update ml_mail_jobs
+set html_body = body, text_body = null
+where mime_type = 'text/html';
+
+alter table ml_mail_jobs drop column header;
+alter table ml_mail_jobs drop column body;
+alter table ml_mail_jobs drop column footer;
+
+alter table ml_mailing_lists add (
+ new_mime_type varchar2(30) default 'text/plain'
+);
+alter table ml_mail_classes add (
+ new_mime_type varchar2(30) default 'text/plain'
+);
+alter table ml_mail_jobs add (
+ new_mime_type varchar2(30) default 'text/plain'
+);
+
+update ml_mailing_lists
+set new_mime_type = mime_type;
+
+update ml_mail_classes
+set new_mime_type = mime_type;
+
+update ml_mail_jobs
+set new_mime_type = mime_type;
+
+alter table ml_mailing_lists drop column mime_type;
+alter table ml_mail_classes drop column mime_type;
+alter table ml_mail_jobs drop column mime_type;
+
+alter table ml_mailing_lists add (
+ mime_type varchar2(30) default 'text/plain'
+);
+alter table ml_mail_classes add (
+ mime_type varchar2(30) default 'text/plain'
+);
+alter table ml_mail_jobs add (
+ mime_type varchar2(30) default 'text/plain'
+);
+
+update ml_mailing_lists
+set mime_type = new_mime_type;
+
+update ml_mail_classes
+set mime_type = new_mime_type;
+
+update ml_mail_jobs
+set mime_type = new_mime_type;
+
+alter table ml_mailing_lists drop column new_mime_type;
+alter table ml_mail_classes drop column new_mime_type;
+alter table ml_mail_jobs drop column new_mime_type;
+
+declare
+ v_welcome clob;
+begin
+ for t in (select list_id, welcome_text, mime_type, dbms_lob.getlength(welcome_text) as lob_length from ml_mailing_lists where welcome_text is not null) loop
+ v_welcome:=empty_clob();
+ dbms_lob.copy(v_welcome, t.welcome_text, t.lob_length);
+ update ml_mailing_lists set welcome_body = v_welcome,
+ welcome_mime_type = t.mime_type where list_id = t.list_id;
+ end loop;
+end;
+/
+
+alter table ml_mailing_lists add (
+ remind_subject varchar2(1000),
+ remind_body clob,
+ remind_mime_type varchar2(30) default 'text/plain'
+);
+
+alter table ml_mailing_list_user_map add (
+ reminder_count integer default 0
+);
+
+create table ml_user_email_log (
+ user_id integer primary key
+ constraint ml_user_email_log_id_fk
+ references users,
+ last_mail_date date default null
+);
+
+create index ml_user_email_log_date_ix on ml_user_email_log(last_mail_date);
+
+insert into ml_user_email_log
+(select m.user_id, max(l.send_date) as last_mail_date
+from ml_email_log l, ml_mailing_list_user_map m
+where m.user_id = l.user_id(+)
+group by m.user_id);
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-create.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,294 @@
+-- $Id: mailing-lists-create.sql,v 1.1 2003/10/01 05:01:53 maltes Exp $
+
+
+-- use package vars for bounce manager (#of bounced emails to mark user
+-- as bouncing, #of days of last mail sent unbounced so that bounce
+-- history of user is deleted) and upper limit of attachment size
+
+
+-- create the object types
+
+ select acs_object_type__create_type (
+ 'mailing_list',
+ 'Mailing List',
+ 'Mailing Lists',
+ 'acs_object',
+ 'ml_mailing_lists',
+ 'list_id',
+ null,
+ 'f',
+ null,
+ 'ml_mailing_list__name'
+ );
+
+ select acs_object_type__create_type (
+ 'mail_class',
+ 'Mailing Class',
+ 'Mailing Classes',
+ 'acs_object',
+ 'ml_mail_classes',
+ 'mail_class_id',
+ null,
+ 'f',
+ null,
+ 'ml_mail_class__name'
+ );
+
+ select acs_object_type__create_type (
+ 'mail_job',
+ 'Mailing Job',
+ 'Mailing Jobs',
+ 'acs_object',
+ 'ml_mail_jobs',
+ 'mail_job_id',
+ null,
+ 'f',
+ null,
+ 'ml_mail_job__name'
+ );
+
+
+-- create the privileges
+
+ select acs_privilege__create_privilege('mailing_list_admin','Mailing Lists Administrator', null);
+
+ select acs_privilege__add_child('admin','mailing_list_admin');
+
+
+create table ml_mailing_lists (
+ list_id integer
+ constraint ml_mailing_lists_pk
+ primary key,
+ package_id integer
+ constraint ml_mailing_lists_pck_id_fk
+ references apm_packages,
+ name text
+ constraint ml_mailing_lists_name_nn
+ not null,
+ locale varchar(5)
+ constraint ml_mailing_lists_locale_nn
+ not null
+ constraint ml_mail_lists_locale_fk
+ references ad_locales,
+ teaser text,
+ public_p boolean,
+ sender_email text,
+ confirm_subject text,
+ confirm_body text,
+ confirm_mime_type varchar(30) default 'text/plain',
+ welcome_subject text,
+ welcome_body text,
+ welcome_mime_type varchar(30) default 'text/plain',
+ remind_subject text,
+ remind_body text,
+ remind_mime_type varchar(30) default 'text/plain',
+ expiration_date timestamptz,
+ -- amount of days after sending registration email
+ first_reminder integer default 7,
+ -- 0 if no second reminder
+ second_reminder integer default 30,
+ comments text
+);
+
+-- target, distribution and campaign: use categories (will be posted)
+
+create table ml_mailing_list_user_map (
+ list_id integer
+ constraint ml_ml_user_map_list_fk
+ references ml_mailing_lists
+ on delete cascade,
+ user_id integer
+ constraint ml_ml_user_map_user_fk
+ references users
+ on delete cascade,
+ subscription_date timestamptz default current_timestamp,
+ unsubscription_date timestamptz,
+ subscribed_p boolean,
+ confirmed_p boolean,
+ reminder_count integer default 0,
+ constraint ml_mailing_list_user_map_pk
+ primary key (list_id, user_id)
+);
+
+-- to speed up queries to get all lists for a user
+create unique index ml_mailing_list_users_ix on ml_mailing_list_user_map (user_id, list_id);
+create index ml_mail_list_users_subsc_p_ix on ml_mailing_list_user_map(subscribed_p);
+
+create table ml_mail_classes (
+ mail_class_id integer
+ constraint ml_mail_classes_pk
+ primary key,
+ package_id integer
+ constraint ml_mail_classes_pck_id_fk
+ references apm_packages,
+ name text
+ constraint ml_mail_classes_name_nn
+ not null,
+ locale varchar(5)
+ constraint ml_mail_classes_locale_nn
+ not null
+ constraint ml_mail_classes_locale_fk
+ references ad_locales,
+ public_p boolean,
+ sender_email varchar(100),
+ subject text,
+ subject_change_p boolean,
+ text_header text,
+ text_header_change_p boolean,
+ text_body text,
+ text_body_change_p boolean,
+ text_footer text,
+ text_footer_change_p boolean,
+ html_header text,
+ html_header_change_p boolean,
+ html_body text,
+ html_body_change_p boolean,
+ html_footer text,
+ html_footer_change_p boolean,
+ mime_type varchar(30) default 'text/plain',
+ comments text
+);
+
+create table ml_mail_jobs (
+ mail_job_id integer
+ constraint ml_mail_jobs_pk
+ primary key,
+ mail_class_id integer
+ constraint ml_mail_jobs_mail_class_id_fk
+ references acs_objects
+ on delete set null,
+ list_id integer
+ constraint ml_mail_jobs_list_id_fk
+ references acs_objects
+ on delete set null,
+ selection_id integer
+ constraint ml_mail_jobs_sel_id_fk
+ references acs_objects
+ on delete set null,
+ package_id integer
+ constraint ml_mail_jobs_package_id_fk
+ references apm_packages,
+ locale varchar(5)
+ constraint ml_mail_jobs_locale_nn
+ not null
+ constraint ml_mail_jobs_locale_fk
+ references ad_locales,
+ sender_email varchar(100),
+ track_links_p boolean,
+ subject text,
+ text_header text,
+ text_body text,
+ text_footer text,
+ html_header text,
+ html_body text,
+ html_footer text,
+ template_p boolean,
+ mime_type varchar(30) default 'text/plain',
+ css_revision_id integer
+ constraint ml_mail_jobs_css_rev_id_fk
+ references cr_revisions on delete set null,
+ state varchar(10),
+ scheduled_date timestamptz default current_timestamp,
+ execution_date timestamptz,
+ sql_query text,
+ bind_vars text,
+ mails_sent integer default 0,
+ mails_bounced integer default 0
+);
+
+create index ml_jobs_scheduled_date_ix on ml_mail_jobs(scheduled_date);
+create index ml_jobs_execution_date_ix on ml_mail_jobs(execution_date);
+
+
+create table ml_mail_job_bind_vars (
+ mail_job_id integer
+ constraint ml_m_job_b_vars_job_id_fk
+ references ml_mail_jobs
+ on delete cascade,
+ name varchar(30),
+ description text,
+ value text,
+ constraint ml_mail_job_bind_vars_pk
+ primary key (mail_job_id, name)
+);
+
+-- make sure that this table doesn't fill up too much
+-- i.e. delete all data older than 7 days
+
+create table ml_email_log (
+ user_id integer
+ constraint ml_email_log_user_id_fk
+ references users,
+ mail_job_id integer
+ constraint ml_email_log_mail_job_id_fk
+ references ml_mail_jobs,
+ send_date timestamptz default current_timestamp,
+ constraint ml_email_log_pk
+ primary key (user_id, mail_job_id)
+);
+
+create index ml_email_log_date_ix on ml_email_log(send_date);
+
+create table ml_user_email_log (
+ user_id integer primary key
+ constraint ml_user_email_log_id_fk
+ references users,
+ last_mail_date timestamptz default null
+);
+
+create index ml_user_email_log_date_ix on ml_user_email_log(last_mail_date);
+
+create table ml_bounce_log (
+ mail_job_id integer
+ constraint ml_bounce_log_mail_job_id_fk
+ references ml_mail_jobs
+ on delete cascade,
+ user_id integer
+ constraint ml_bounce_log_user_id_fk
+ references users
+ on delete cascade,
+ sending_time timestamptz,
+ bouncing_time timestamptz default current_timestamp,
+ constraint ml_bounce_log_pk
+ primary key (mail_job_id, user_id)
+);
+
+create index ml_bounce_log_bounce_time_ix on ml_bounce_log(bouncing_time);
+
+create table ml_category_trees_visible (
+ tree_id integer
+ constraint ml_cat_trees_vis_tree_id_fk
+ references acs_objects
+ on delete cascade,
+ package_id integer
+ constraint ml_cat_trees_vis_pck_id_fk
+ references apm_packages,
+ constraint ml_cat_trees_vis_pk
+ primary key (package_id, tree_id)
+);
+
+create table ml_country_category_tree (
+ tree_id integer
+ constraint ml_country_category_tree_id_fk
+ references acs_objects
+ on delete cascade,
+ package_id integer
+ constraint ml_country_cat_tree_pck_id_fk
+ references apm_packages,
+ constraint ml_country_category_tree_pk
+ primary key (package_id, tree_id)
+);
+
+create table ml_country_codes (
+ country_code varchar(3)
+ constraint ml_country_codes_pk
+ primary key,
+ category_id integer
+ constraint ml_country_codes_cat_id_fk
+ references acs_objects
+ on delete cascade
+);
+
+
+\i mailing-lists-package-create.sql
+\i mailing-lists-init.sql
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-drop.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,54 @@
+\i mailing-lists-package-drop.sql
+
+drop table ml_country_codes;
+drop table ml_country_category_tree;
+drop table ml_category_trees_visible;
+drop table ml_bounce_log;
+drop table ml_user_email_log;
+drop table ml_email_log;
+drop table ml_mail_job_bind_vars;
+drop table ml_mail_jobs;
+drop table ml_mail_classes;
+drop table ml_mailing_list_user_map;
+drop table ml_mailing_lists;
+
+delete from acs_objects where object_type='mailing_list';
+delete from acs_objects where object_type='mail_class';
+delete from acs_objects where object_type='mail_job';
+
+delete from acs_permissions
+where privilege in ('mailing_list_admin');
+
+delete from acs_privilege_hierarchy
+where privilege in ('mailing_list_admin');
+
+delete from acs_privilege_hierarchy
+where child_privilege in ('mailing_list_admin');
+
+delete from acs_privileges
+where privilege in ('mailing_list_admin');
+
+
+select acs_object_type__drop_type('mailing_list','f');
+select acs_object_type__drop_type('mail_class','f');
+select acs_object_type__drop_type('mail_job','f');
+
+select acs_sc_binding__delete(
+ 'AcsObject',
+ 'mailing_list_idhandler'
+);
+select acs_sc_impl__delete('AcsObject', 'mailing_list_idhandler');
+
+
+select acs_sc_binding__delete(
+ 'AcsObject',
+ 'mail_class_idhandler'
+);
+select acs_sc_impl__delete('AcsObject', 'mail_class_idhandler');
+
+
+select acs_sc_binding__delete(
+ 'AcsObject',
+ 'mail_job_idhandler'
+);
+select acs_sc_impl__delete('AcsObject', 'mail_job_idhandler');
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-init.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-init.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,54 @@
+ select acs_sc_impl__new (
+ 'AcsObject',
+ 'mailing_list_idhandler',
+ 'mailing-lists'
+ );
+ select acs_sc_impl_alias__new (
+ 'AcsObject',
+ 'mailing_list_idhandler',
+ 'PageUrl',
+ 'ml_get_mailing_list_pageurl',
+ 'TCL'
+ );
+ select acs_sc_binding__new (
+ 'AcsObject',
+ 'mailing_list_idhandler'
+ );
+
+
+
+ select acs_sc_impl__new (
+ 'AcsObject',
+ 'mail_class_idhandler',
+ 'mailing-lists'
+ );
+ select acs_sc_impl_alias__new (
+ 'AcsObject',
+ 'mail_class_idhandler',
+ 'PageUrl',
+ 'ml_get_mail_class_pageurl',
+ 'TCL'
+ );
+ select acs_sc_binding__new (
+ 'AcsObject',
+ 'mail_class_idhandler'
+ );
+
+
+
+ select acs_sc_impl__new (
+ 'AcsObject',
+ 'mail_job_idhandler',
+ 'mailing-lists'
+ );
+ select acs_sc_impl_alias__new (
+ 'AcsObject',
+ 'mail_job_idhandler',
+ 'PageUrl',
+ 'ml_get_mail_job_pageurl',
+ 'TCL'
+ );
+ select acs_sc_binding__new (
+ 'AcsObject',
+ 'mail_job_idhandler'
+ );
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-create.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,494 @@
+-- API for Mailing lists
+
+create or replace function ml_mailing_list__new (integer,integer,text,varchar,text,text,text,text,varchar,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,integer,timestamptz,varchar)
+returns integer as '
+declare
+ new__list_id alias for $1; -- default null
+ new__package_id alias for $2; -- default null
+ new__name alias for $3;
+ new__locale alias for $4;
+ new__teaser alias for $5;
+ new__sender_email alias for $6; -- default null
+ new__confirm_subject alias for $7; -- default null
+ new__confirm_body alias for $8; -- default null
+ new__confirm_mime_type alias for $9; -- default ''text/plain''
+ new__welcome_subject alias for $10; -- default null
+ new__welcome_body alias for $11; -- default null
+ new__welcome_mime_type alias for $12; -- default ''text/plain''
+ new__remind_subject alias for $13; -- default null
+ new__remind_body alias for $14; -- default null
+ new__remind_mime_type alias for $15; -- default ''text/plain''
+ new__expiration_date alias for $16; -- default sysdate
+ new__first_reminder alias for $17; -- default 7
+ new__second_reminder alias for $18; -- default 30
+ new__comments alias for $19; -- default null
+ new__context_id alias for $20; -- default null
+ new__creation_user alias for $21; -- default null
+ new__creation_date alias for $22; -- default sysdate
+ new__creation_ip alias for $23; -- default null
+
+ v_list_id ml_mailing_lists.list_id%TYPE;
+begin
+
+ v_list_id := acs_object__new (
+ new__list_id,
+ ''mailing_list'',
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_list_id, new__name, new__package_id);
+
+ insert into ml_mailing_lists
+ (list_id, package_id, name, locale, teaser, sender_email,
+ welcome_subject, welcome_body, welcome_mime_type,
+ confirm_subject, confirm_body, confirm_mime_type,
+ remind_subject, remind_body, remind_mime_type,
+ expiration_date, first_reminder, second_reminder, comments)
+ values
+ (v_list_id, new__package_id, new__name, new__locale, new__teaser, new__sender_email,
+ new__welcome_subject, new__welcome_body, new__welcome_mime_type,
+ new__confirm_subject, new__confirm_body, new__confirm_mime_type,
+ new__remind_subject, new__remind_body, new__remind_mime_type,
+ new__expiration_date, new__first_reminder, new__second_reminder, new__comments);
+
+ return v_list_id;
+
+end;' language 'plpgsql';
+
+
+
+
+create or replace function ml_mailing_list__delete (integer)
+returns integer as '
+declare
+ delete__list_id alias for $1;
+begin
+ delete from ml_mailing_lists where list_id = delete__list_id;
+ perform acs_object__delete(delete__list_id);
+
+ return 0;
+end;' language 'plpgsql';
+
+create or replace function ml_mailing_list__edit (integer,text,varchar,text,text,text,text,varchar,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,varchar)
+returns integer as '
+declare
+ edit__list_id alias for $1; -- default null
+ edit__name alias for $2;
+ edit__locale alias for $3;
+ edit__teaser alias for $4;
+ edit__sender_email alias for $5; -- default null
+ edit__confirm_subject alias for $6; -- default null
+ edit__confirm_body alias for $7; -- default null
+ edit__confirm_mime_type alias for $8; -- default ''text/plain''
+ edit__welcome_subject alias for $9; -- default null
+ edit__welcome_body alias for $10; -- default null,
+ edit__welcome_mime_type alias for $11; -- default ''text/plain''
+ edit__remind_subject alias for $12; -- default null
+ edit__remind_body alias for $13; -- default null,
+ edit__remind_mime_type alias for $14; -- default ''text/plain''
+ edit__expiration_date alias for $15; -- default sysdate
+ edit__first_reminder alias for $16; -- default 7
+ edit__second_reminder alias for $17; -- default 30
+ edit__comments alias for $18; -- default null
+ edit__modifying_user alias for $19; -- default null
+ edit__modifying_ip alias for $20; -- default null
+begin
+ update ml_mailing_lists
+ set name = edit__name,
+ locale = edit__locale,
+ teaser = edit__teaser,
+ sender_email = edit__sender_email,
+ confirm_subject = edit__confirm_subject,
+ confirm_body = edit__confirm_body,
+ confirm_mime_type = edit__confirm_mime_type,
+ welcome_subject = edit__welcome_subject,
+ welcome_body = edit__welcome_body,
+ welcome_mime_type = edit__welcome_mime_type,
+ remind_subject = edit__remind_subject,
+ remind_body = edit__remind_body,
+ remind_mime_type = edit__remind_mime_type,
+ expiration_date = edit__expiration_date,
+ first_reminder = edit__first_reminder,
+ second_reminder = edit__second_reminder,
+ comments = edit__comments
+ where list_id = edit__list_id;
+
+ update acs_named_objects
+ set object_name = edit__name
+ where object_id = edit__list_id;
+
+ update acs_objects
+ set modifying_user = edit__modifying_user,
+ modifying_ip = edit__modifying_ip
+ where object_id = edit__list_id;
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+create or replace function ml_mailing_list__name (integer)
+returns varchar as '
+declare
+ name__list_id alias for $1;
+ v_name ml_mailing_lists.name%TYPE;
+begin
+ select into v_name name
+ from ml_mailing_lists
+ where list_id = name__list_id;
+
+ return v_name;
+end;' language 'plpgsql';
+
+
+-- API for Mailing Classes
+
+
+create or replace function ml_mail_class__new (integer,integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,integer,timestamptz,varchar)
+returns integer as '
+declare
+ new__mail_class_id alias for $1; -- default null
+ new__package_id alias for $2; -- default null,
+ new__name alias for $3; -- default null,
+ new__locale alias for $4; -- default null,
+ new__sender_email alias for $5; -- default null,
+ new__subject alias for $6; -- default null,
+ new__subject_change_p alias for $7; -- default ''t'',
+ new__text_header alias for $8; -- default null,
+ new__text_header_change_p alias for $9; -- default ''t'',
+ new__text_body alias for $10; -- default null,
+ new__text_body_change_p alias for $11; -- default ''t'',
+ new__text_footer alias for $12; -- default null,
+ new__text_footer_change_p alias for $13; -- default ''t'',
+ new__html_header alias for $14; -- default null,
+ new__html_header_change_p alias for $15; -- default ''t'',
+ new__html_body alias for $16; -- default null,
+ new__html_body_change_p alias for $17; -- default ''t'',
+ new__html_footer alias for $18; -- default null,
+ new__html_footer_change_p alias for $19; -- default ''t'',
+ new__mime_type alias for $20; -- default ''text/plain'',
+ new__comments alias for $21; -- default null,
+ new__context_id alias for $22; -- default null,
+ new__creation_user alias for $23; -- default null,
+ new__creation_date alias for $24; -- default sysdate,
+ new__creation_ip alias for $25; -- default null
+
+ v_class_id ml_mail_classes.mail_class_id%TYPE;
+
+begin
+ v_class_id := acs_object__new (
+ new__mail_class_id,
+ ''mail_class'',
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_class_id, new__name, new__package_id);
+
+
+ insert into ml_mail_classes
+ (mail_class_id, package_id, name, locale, sender_email,
+ subject, subject_change_p, text_header, text_header_change_p,
+ text_body, text_body_change_p, text_footer, text_footer_change_p,
+ html_header, html_header_change_p, html_body, html_body_change_p,
+ html_footer, html_footer_change_p, mime_type, comments)
+ values
+ (v_class_id, new__package_id, new__name, new__locale, new__sender_email,
+ new__subject, new__subject_change_p, new__text_header, new__text_header_change_p,
+ new__text_body, new__text_body_change_p, new__text_footer, new__text_footer_change_p,
+ new__html_header, new__html_header_change_p, new__html_body, new__html_body_change_p,
+ new__html_footer, new__html_footer_change_p, new__mime_type, new__comments);
+
+ return v_class_id;
+
+end;' language 'plpgsql';
+
+
+create or replace function ml_mail_class__delete (integer)
+returns integer as '
+declare
+ delete__mail_class_id alias for $1;
+ v_attachment cr_items%ROWTYPE;
+ v_mail_job acs_objects%ROWTYPE;
+begin
+ for v_attachment in select item_id from cr_items
+ where parent_id = delete__mail_class_id
+ loop
+ perform content_item__delete(v_attachment.item_id);
+ end loop;
+
+ update ml_mail_jobs set mail_class_id = NULL where mail_class_id = delete__mail_class_id;
+
+ for v_mail_job in select object_id from acs_objects
+ where context_id = delete__mail_class_id and
+ object_type = ''mail_job''
+ loop
+ update acs_objects set context_id = (
+ select package_id from ml_mail_jobs
+ where mail_job_id = v_mail_job.object_id)
+ where object_id = v_mail_job.object_id;
+ end loop;
+
+ delete from ml_mail_classes where mail_class_id = delete__mail_class_id;
+ perform acs_object__delete(delete__mail_class_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+
+create or replace function ml_mail_class__edit (integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,varchar)
+returns integer as '
+declare
+ edit__mail_class_id alias for $1; -- default null
+ edit__name alias for $2; -- default null,
+ edit__locale alias for $3; -- default null,
+ edit__sender_email alias for $4; -- default null,
+ edit__subject alias for $5; -- default null,
+ edit__subject_change_p alias for $6; -- default ''t'',
+ edit__text_header alias for $7; -- default null,
+ edit__text_header_change_p alias for $8; -- default ''t'',
+ edit__text_body alias for $9; -- default null,
+ edit__text_body_change_p alias for $10; -- default ''t'',
+ edit__text_footer alias for $11; -- default null,
+ edit__text_footer_change_p alias for $12; -- default ''t'',
+ edit__html_header alias for $13; -- default null,
+ edit__html_header_change_p alias for $14; -- default ''t'',
+ edit__html_body alias for $15; -- default null,
+ edit__html_body_change_p alias for $16; -- default ''t'',
+ edit__html_footer alias for $17; -- default null,
+ edit__html_footer_change_p alias for $18; -- default ''t'',
+ edit__mime_type alias for $19; -- default ''text/plain'',
+ edit__comments alias for $20; -- default null,
+ edit__modifying_user alias for $21; -- default null,
+ edit__modifying_ip alias for $22; -- default null
+begin
+ update ml_mail_classes
+ set name = edit__name,
+ locale = edit__locale,
+ sender_email = edit__sender_email,
+ subject = edit__subject,
+ subject_change_p = edit__subject_change_p,
+ text_header = edit__text_header,
+ text_header_change_p = edit__text_header_change_p,
+ text_body = edit__text_body,
+ text_body_change_p = edit__text_body_change_p,
+ text_footer = edit__text_footer,
+ text_footer_change_p = edit__text_footer_change_p,
+ html_header = edit__html_header,
+ html_header_change_p = edit__html_header_change_p,
+ html_body = edit__html_body,
+ html_body_change_p = edit__html_body_change_p,
+ html_footer = edit__html_footer,
+ html_footer_change_p = edit__html_footer_change_p,
+ mime_type = edit__mime_type,
+ comments = edit__comments
+ where mail_class_id = edit__mail_class_id;
+
+ update acs_named_objects
+ set object_name = edit__name
+ where object_id = edit__mail_class_id;
+
+ update acs_objects
+ set modifying_user = edit__modifying_user,
+ modifying_ip = edit__modifying_ip
+ where object_id = edit__mail_class_id;
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+create or replace function ml_mail_class__name (integer)
+returns varchar as '
+declare
+ name__mail_class_id alias for $1;
+ v_name ml_mail_classes.name%TYPE;
+begin
+ select into v_name name
+ from ml_mail_classes
+ where mail_class_id = name__mail_class_id;
+
+ return v_name;
+
+end;' language 'plpgsql';
+
+
+
+-- API for Mailing List Jobs
+
+create or replace function ml_mail_job__new (integer,integer,integer,integer,integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,integer,timestamptz,varchar)
+returns integer as '
+declare
+ new__mail_job_id alias for $1; -- default null
+ new__mail_class_id alias for $2;
+ new__list_id alias for $3; -- default null
+ new__selection_id alias for $4; -- default null
+ new__package_id alias for $5; -- default null
+ new__locale alias for $6;
+ new__sender_email alias for $7; -- default null
+ new__track_links_p alias for $8; -- default ''f''
+ new__subject alias for $9; -- default null
+ new__text_header alias for $10; -- default null
+ new__text_body alias for $11; -- default null
+ new__text_footer alias for $12; -- default null
+ new__html_header alias for $13; -- default null
+ new__html_body alias for $14; -- default null
+ new__html_footer alias for $15; -- default null
+ new__template_p alias for $16; -- default ''f''
+ new__mime_type alias for $17; -- default ''text/plain''
+ new__state alias for $18; -- default ''active''
+ new__scheduled_date alias for $19; -- default sysdate
+ new__bind_vars alias for $20; -- default null
+ new__context_id alias for $21; -- default null
+ new__creation_user alias for $22; -- default null
+ new__creation_date alias for $23; -- default sysdate
+ new__creation_ip alias for $24; -- default null
+
+ v_mail_job_id ml_mail_jobs.mail_job_id%TYPE;
+ v_revision_id cr_revisions.revision_id%TYPE;
+begin
+ v_mail_job_id := acs_object__new (
+ new__mail_job_id,
+ ''mail_job'',
+ new__creation_date,
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ select into v_revision_id live_revision
+ from cr_items
+ where parent_id = new__mail_class_id;
+ IF NOT FOUND THEN
+ v_revision_id := null;
+ END IF;
+
+ insert into acs_named_objects (object_id, object_name, package_id)
+ values (v_mail_job_id, new__subject, new__package_id);
+
+ insert into ml_mail_jobs
+ (mail_job_id, list_id, selection_id, package_id, locale,
+ sender_email, track_links_p, subject, text_header, text_body,
+ text_footer, html_header, html_body, html_footer,
+ template_p, mime_type, state, scheduled_date,
+ css_revision_id, mail_class_id, bind_vars)
+ values
+ (v_mail_job_id, new__list_id, new__selection_id, new__package_id, new__locale,
+ new__sender_email, new__track_links_p, new__subject, new__text_header, new__text_body,
+ new__text_footer, new__html_header, new__html_body, new__html_footer,
+ new__template_p, new__mime_type, new__state, new__scheduled_date,
+ v_revision_id, new__mail_class_id, new__bind_vars);
+
+ if (new__selection_id is not null) then
+ insert into ml_mail_job_bind_vars
+ (select v_mail_job_id as mail_job_id, b.name, b.description,
+ b.default_value as value
+ from us_bind_vars b
+ where b.selection_id = new__selection_id);
+ end if;
+
+ return v_mail_job_id;
+
+end;' language 'plpgsql';
+
+
+
+
+create or replace function ml_mail_job__delete (integer)
+returns integer as '
+declare
+ delete__mail_job_id alias for $1;
+ v_attachment cr_items%ROWTYPE;
+begin
+ for v_attachment in select item_id from cr_items
+ where parent_id = delete__mail_job_id
+ loop
+ perform content_item__delete(item_id);
+ end loop;
+
+ delete from ml_mail_jobs where mail_job_id = delete__mail_job_id;
+ perform acs_object__delete(delete__mail_job_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+
+create or replace function ml_mail_job__edit (integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,varchar)
+returns integer as '
+declare
+ edit__mail_job_id alias for $1;
+ edit__locale alias for $2;
+ edit__sender_email alias for $3; -- default null
+ edit__track_links_p alias for $4; -- default ''f''
+ edit__subject alias for $5; -- default null
+ edit__text_header alias for $6; -- default null
+ edit__text_body alias for $7; -- default null
+ edit__text_footer alias for $8; -- default null
+ edit__html_header alias for $9; -- default null
+ edit__html_body alias for $10; -- default null
+ edit__html_footer alias for $11; -- default null
+ edit__template_p alias for $12; -- default ''f''
+ edit__mime_type alias for $13; -- default ''text/plain''
+ edit__state alias for $14; -- default ''active''
+ edit__scheduled_date alias for $15; -- default sysdate
+ edit__bind_vars alias for $16; -- default null
+ edit__modifying_user alias for $17; -- default null
+ edit__modifying_ip alias for $18; -- default null
+begin
+
+ update ml_mail_jobs
+ set locale = edit__locale,
+ sender_email = edit__sender_email,
+ track_links_p = edit__track_links_p,
+ subject = edit__subject,
+ text_header = edit__text_header,
+ text_body = edit__text_body,
+ text_footer = edit__text_footer,
+ html_header = edit__html_header,
+ html_body = edit__html_body,
+ html_footer = edit__html_footer,
+ template_p = edit__template_p,
+ mime_type = edit__mime_type,
+ state = edit__state,
+ scheduled_date = edit__scheduled_date,
+ bind_vars = edit__bind_vars
+ where mail_job_id = edit__mail_job_id
+ and state <> ''done'';
+
+ update acs_named_objects
+ set object_name = edit__subject
+ where object_id = edit__mail_job_id;
+
+ update acs_objects
+ set modifying_user = edit__modifying_user,
+ modifying_ip = edit__modifying_ip
+ where object_id = edit__mail_job_id;
+
+ return 0;
+
+end;' language 'plpgsql';
+
+create or replace function ml_mail_job__name (integer)
+returns varchar as '
+declare
+ name__mail_job_id alias for $1;
+ v_name ml_mail_jobs.subject%TYPE;
+begin
+ select into v_name subject
+ from ml_mail_jobs
+ where mail_job_id = name__mail_job_id;
+
+ return v_name;
+end;' language 'plpgsql';
+
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/mailing-lists-package-drop.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,25 @@
+drop function ml_mailing_list__new(integer,integer,text,varchar,text,text,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,integer,timestamptz,varchar);
+
+drop function ml_mailing_list__delete (integer);
+
+drop function ml_mailing_list__edit (integer,text,varchar,text,text,text,text,varchar,text,text,varchar,timestamptz,integer,integer,text,integer,varchar);
+
+drop function ml_mailing_list__name (integer);
+
+
+drop function ml_mail_class__new (integer,integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,integer,timestamptz,varchar);
+
+drop function ml_mail_class__delete (integer);
+
+drop function ml_mail_class__edit (integer,text,varchar,varchar,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,text,boolean,varchar,text,integer,varchar);
+
+drop function ml_mail_class__name (integer);
+
+
+drop function ml_mail_job__new (integer,integer,integer,integer,integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,integer,timestamptz,varchar);
+
+drop function ml_mail_job__delete (integer);
+
+drop function ml_mail_job__edit (integer,varchar,varchar,boolean,text,text,text,text,text,text,text,boolean,varchar,varchar,timestamptz,text,integer,varchar);
+
+drop function ml_mail_job__name (integer);
\ No newline at end of file
Index: openacs-4/contrib/packages/mailing-lists/sql/postgresql/upgrade.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/sql/postgresql/upgrade.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/sql/postgresql/upgrade.sql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,23 @@
+alter table ml_mailing_lists add remind_subject text;
+alter table ml_mailing_lists add remind_body text;
+alter table ml_mailing_lists add remind_mime_type varchar(30);
+alter table ml_mailing_lists alter column remind_mime_type set default 'text/plain';
+
+alter table ml_mailing_list_user_map add reminder_count integer;
+alter table ml_mailing_list_user_map alter column reminder_count set default 0;
+
+create table ml_user_email_log (
+ user_id integer primary key
+ constraint ml_user_email_log_id_fk
+ references users,
+ last_mail_date timestamptz default null
+);
+
+create index ml_user_email_log_date_ix on ml_user_email_log(last_mail_date);
+
+insert into ml_user_email_log
+(select m.user_id, max(l.send_date) as last_mail_date
+from ml_mailing_list_user_map m
+left outer join ml_email_log l
+on (m.user_id = l.user_id)
+group by m.user_id);
Index: openacs-4/contrib/packages/mailing-lists/tcl/email-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/email-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/email-procs-oracle.xql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,87 @@
+
+
+
+ oracle 8.1.6
+
+
+
+
+ select mail_job_id
+ from ml_mail_jobs
+ where state = 'active'
+ and scheduled_date <= sysdate
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set state = 'started',
+ execution_date = sysdate
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ select r.mime_type, r.title, r.filename
+ from cr_revisions r
+ where r.revision_id = :css_revision_id
+ union
+ select r.mime_type, r.title, r.filename
+ from cr_items i, cr_revisions r
+ where r.item_id = i.item_id
+ and i.parent_id = :mail_job_id
+
+
+
+
+
+
+
+
+ update ml_user_email_log
+ set last_mail_date = sysdate
+ where user_id = :user_id
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set state = 'finished',
+ mails_sent = :mail_count,
+ track_links_p = (select case when count(*) = 0 then 'f' else 't' end
+ from mail_link_mail_map m
+ where mail_id = :mail_job_id)
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ insert into ml_bounce_log (mail_job_id, user_id, sending_time,
+ bouncing_time)
+ (select mail_job_id, user_id, send_date as sending_time,
+ sysdate as bouncing_time
+ from ml_email_log
+ where mail_job_id = :job_id
+ and user_id = :user_id)
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/email-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/email-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/email-procs-postgresql.xql 1 Oct 2003 05:01:53 -0000 1.1
@@ -0,0 +1,85 @@
+
+
+
+ postgresql 7.1
+
+
+
+
+ select mail_job_id
+ from ml_mail_jobs
+ where state = 'active'
+ and scheduled_date <= current_timestamp
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set state = 'started',
+ execution_date = current_timestamp
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ select r.mime_type, r.title, r.content as filename
+ from cr_revisions r
+ where r.revision_id = :css_revision_id
+ union
+ select r.mime_type, r.title, r.content as filename
+ from cr_items i, cr_revisions r
+ where r.item_id = i.item_id
+ and i.parent_id = :mail_job_id
+
+
+
+
+
+
+
+ update ml_user_email_log
+ set last_mail_date = current_timestamp
+ where user_id = :user_id
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set state = 'finished',
+ mails_sent = :mail_count,
+ track_links_p = (select case when count(*) = 0 then FALSE else TRUE end
+ from mail_link_mail_map m
+ where mail_id = :mail_job_id)
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ insert into ml_bounce_log (mail_job_id, user_id, sending_time,
+ bouncing_time)
+ (select mail_job_id, user_id, send_date as sending_time,
+ current_timestamp as bouncing_time
+ from ml_email_log
+ where mail_job_id = :job_id
+ and user_id = :user_id)
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/email-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/email-procs.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/email-procs.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,441 @@
+namespace eval mail_job {
+
+ ad_proc -public send {
+ -mail_job_id:required
+ } {
+ Send email
+ } {
+ set boundary_string "-+-+-someboundarystring-+-+-"
+ set alternative_string "-+-+-somealternativestring-+-+-"
+ set system_url [ad_url]
+ regsub -all {http://} $system_url {} system_url
+
+ # get the mail job data
+ if {![db_0or1row get_data {}]} {
+ return
+ }
+
+ # mark mail job as started
+ db_dml start_mail_job {}
+
+ # glue body and footer together
+ if {$mail_mime_type != "text/html"} {
+ set text_body "$text_header\n$text_body\n$text_footer"
+ }
+ if {$mail_mime_type != "text/plain"} {
+ set html_body "$html_header\n$html_body\n$html_footer"
+ }
+
+ # get endofline characters right
+ set text_body [string map {\r ""} $text_body]
+ set html_body [string map {\r ""} $html_body]
+
+ # substitute links in mail if link tracking enabled
+ if {$track_links_p == "t"} {
+ if {$mail_mime_type != "text/html"} {
+ set text_body [mail_job::create_links -text $text_body -mime_type text/plain -mail_job_id $mail_job_id]
+ }
+ if {$mail_mime_type != "text/plain"} {
+ set html_body [mail_job::create_links -text $html_body -mime_type text/html -mail_job_id $mail_job_id]
+ }
+ set template_p t
+ }
+
+ set attachments ""
+ # encode all attachments in base64
+ db_foreach get_attachments {} {
+ set mail_filename "\"[ad_quotehtml $title]\""
+
+ # encode current attachment in base64 and wrap it in
+ # appropriate email-multipart form
+ set content [mail_job::base64_encode -filename "[cr_fs_path]$filename"]
+
+ append attachments "\n--$boundary_string
+Content-Type: $mime_type; name=$mail_filename
+Content-Transfer-Encoding: base64
+Content-Disposition: attachment; filename=$mail_filename
+
+$content\n"
+ }
+
+ set header [ns_set new]
+ if {![empty_string_p $attachments]} {
+ # set correct header and correct begin/end of multipart-mail
+
+ # finish multipart email
+ append attachments "\n--$boundary_string\--"
+
+ # begin multipart/mixed email.
+ ns_set put $header "Content-Type" "Multipart/Mixed; boundary=\"$boundary_string\""
+
+ set begin_body "This is a multi-part message in MIME format.\n\n--$boundary_string\n"
+ switch $mail_mime_type {
+ "text/plain" {
+ # quoted-printable encode plaintext mails
+ append begin_body "Content-Type: text/plain\nContent-Disposition: inline\n\n"
+######
+# append begin_body "Content-Type: text/plain\nContent-Transfer-Encoding: quoted-printable\nContent-Disposition: inline\n\n"
+######
+ }
+ "text/html" {
+ append begin_body "Content-Type: text/html; charset=$mime_charset\nContent-Disposition: inline\n\n"
+ }
+ "multipart/alternative" {
+ # begin multipart/alternative part inside the multipart/mixed mail
+ append begin_body "Content-Type: multipart/alternative; boundary=\"$alternative_string\"\n\n--$alternative_string\nContent-Type: text/plain"
+######
+# append begin_body "Content-Type: multipart/alternative; boundary=\"$alternative_string\"\n\n--$alternative_string\nContent-Type: text/plain\nContent-Transfer-Encoding: quoted-printable"
+######
+ set alternative_body "\n--$alternative_string\nContent-Type: text/html; charset=$mime_charset"
+ set attachments "\n--$alternative_string\--\n$attachments"
+ }
+ }
+
+ } else {
+ # no attachments, so set correct header
+ switch $mail_mime_type {
+ "text/plain" {
+ # quoted-printable encode plaintext mails
+ ns_set put $header "Content-Type" text/plain
+######
+# ns_set put $header "Content-Transfer-Encoding" quoted-printable
+######
+ set begin_body ""
+ }
+ "text/html" {
+ ns_set put $header "Content-Type" "text/html; charset=$mime_charset"
+ set begin_body ""
+ }
+ "multipart/alternative" {
+ # begin multipart/alternative email
+ ns_set put $header "Content-Type" "Multipart/Alternative; boundary=\"$boundary_string\""
+ # quoted-printable encode plaintext mails
+ set begin_body "This is a multi-part message in MIME format.\n\n--$boundary_string\nContent-Type: text/plain"
+######
+# set begin_body "This is a multi-part message in MIME format.\n\n--$boundary_string\nContent-Type: text/plain\nContent-Transfer-Encoding: quoted-printable"
+######
+ set alternative_body "\n--$boundary_string\nContent-Type: text/html; charset=$mime_charset"
+ set attachments "\n--$boundary_string\--"
+ }
+ }
+ }
+
+ set mail_text_body ""
+ set mail_html_body ""
+
+ if {$template_p == "t"} {
+ # mail is template so generate code to substitute variables in mail text
+
+ # we have to use noquote-substitution in mail subject
+ while {[regsub -all [template::adp_variable_regexp] $subject {\1@one_user.\2;noquote@} subject]} {}
+
+ if {$mail_mime_type != "text/html"} {
+ # we have to use noquote-substitution for plaintext emails
+ while {[regsub -all [template::adp_variable_regexp] $text_body {\1@one_user.\2;noquote@} text_body]} {}
+ }
+
+ if {$mail_mime_type != "text/plain"} {
+ # we have to correct for correct variable usage in html emails
+ # (already done for subject and plaintext emails)
+ while {[regsub -all [template::adp_variable_regexp] $html_body {\1@one_user.\2@} html_body]} {}
+ while {[regsub -all [template::adp_variable_regexp_noquote] $html_body {\1@one_user.\2;noquote@} html_body]} {}
+ }
+
+ ad_conn -set package_key mailing-lists
+ variable ::template::parse_level
+ lappend ::template::parse_level [info level]
+ switch $mail_mime_type {
+ "text/plain" {
+ # generate proc to substitute variables in plaintext mails
+ proc ::mailing_list::__substitute_mail {} "
+ uplevel {
+ [template::adp_compile -string $subject]
+ set mail_subject \$__adp_output
+ [template::adp_compile -string $text_body]
+ set mail_text_body \$__adp_output
+ }
+ "
+ }
+ "text/html" {
+ # generate proc to substitute variables in html mails
+ proc ::mailing_list::__substitute_mail {} "
+ uplevel {
+ [template::adp_compile -string $subject]
+ set mail_subject \$__adp_output
+ [template::adp_compile -string $html_body]
+ set mail_html_body \$__adp_output
+ }
+ "
+ }
+ "multipart/alternative" {
+ # generate proc to substitute variables in alternative mails
+ proc ::mailing_list::__substitute_mail {} "
+ uplevel {
+ [template::adp_compile -string $subject]
+ set mail_subject \$__adp_output
+ [template::adp_compile -string $text_body]
+ set mail_text_body \$__adp_output
+ [template::adp_compile -string $html_body]
+ set mail_html_body \$__adp_output
+ }
+ "
+ }
+ }
+ template::util::lpop ::template::parse_level
+ } else {
+ # mail is no template, so encode once and not for every user
+ set mail_subject $subject
+
+ # encode plain-text mails
+ if {$mail_mime_type != "text/html"} {
+ set mail_text_body $text_body
+######
+# set mail_text_body [mail_job::quoted_printable_encode -text $text_body]
+######
+ }
+ set mail_html_body $html_body
+ }
+
+ # get all users to spam from mailing-list / users-selection
+ if {[empty_string_p $list_id] && ![empty_string_p $bind_vars]} {
+ # get users from users-selection with bind-vars
+ set user_list [db_list_of_ns_sets get_users_selection_to_mail_bind $sql_query -bind $bind_vars]
+ } else {
+ if {![empty_string_p $list_id]} {
+ # get users from mailing-list
+ set user_list [db_list_of_ns_sets get_list_users_to_mail {}]
+ } else {
+ # get users from users-selection without bind-vars
+ set user_list [db_list_of_ns_sets get_users_selection_to_mail $sql_query]
+ }
+ }
+
+ set mail_count 0
+ # loop over all users to spam
+ foreach one_user_set $user_list {
+ incr mail_count
+ array set one_user [template::util::set_to_list $one_user_set]
+ set user_id $one_user(user_id)
+ set user_email $one_user(user_email)
+ set user_first_names $one_user(user_first_names)
+ set user_last_name $one_user(user_last_name)
+
+ if {$template_p == "t"} {
+ # now substitute variables in mailbody
+ ::mailing_list::__substitute_mail
+
+ # encode plain-text mails
+ if {$mail_mime_type != "text/html"} {
+######
+# set mail_text_body [mail_job::quoted_printable_encode -text $mail_text_body]
+######
+ }
+ }
+ ns_set update $header "X-Field" "$user_id-$mail_job_id"
+
+ # set the data for mail recipient
+ set to_addr(email) [list $user_email]
+ set to_addr(user_id) [list $user_id]
+ if {$user_first_names=="Unknown" && $user_last_name=="User"} {
+ set to_addr(name) " "
+ } else {
+ set to_addr(name) [list "$user_first_names $user_last_name"]
+ }
+
+ #actually send out the email
+ switch $mail_mime_type {
+ "text/plain" {
+ acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject $mail_subject -body "$begin_body\n$mail_text_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+# acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject [mail_job::quoted_printable_encode -text $mail_subject -charset $mime_charset] -body "$begin_body\n$mail_text_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+ }
+ "text/html" {
+ acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject $mail_subject -body "$begin_body\n$mail_html_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+# acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject [mail_job::quoted_printable_encode -text $mail_subject -charset $mime_charset] -body "$begin_body\n$mail_html_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+ }
+ "multipart/alternative" {
+ acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject $mail_subject -body "$begin_body\n$mail_text_body\n$alternative_body\n$mail_html_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+# acs_mail_lite::send -valid_email -to_addr [array get to_addr] -from_addr $sender_email -subject [mail_job::quoted_printable_encode -text $mail_subject -charset $mime_charset] -body "$begin_body\n$mail_text_body\n$alternative_body\n$mail_html_body\n$attachments" -extraheaders $header -package_id $package_id
+######
+ }
+ }
+ array unset to_addr
+
+ # record mail sending in logtables
+ db_dml log_mail_sending {}
+ db_dml log_users_latest_mail {}
+ }
+
+ # mark mail job as done and save number of mails sent out
+ # and set link tracking flag if mail containts tracked links
+ if {$mail_count>0} {
+ db_dml finish_mail_job {}
+
+ array unset one_user
+ }
+ }
+
+ ad_proc -private base64_encode {
+ -filename:required
+ } {
+ set fp [open "|/usr/bin/mmencode -b $filename" r]
+ set quoted [read $fp]
+ close $fp
+ return $quoted
+ }
+
+
+ ad_proc -private quoted_printable_encode {
+ -text:required
+ {-charset ""}
+ } {
+ Encode special characters, like german umlauts, in message headers
+ according to RFC 2047.
+ } {
+ # First check if there are any characters which need to be quoted at all.
+ set encode_p 0
+ for { set i 0 } { $i < [string length $text] } { incr i } {
+ if { ![string is ascii [string index $text $i]] } {
+ set encode_p 1
+ }
+ }
+ if { !$encode_p } { return $text }
+
+ set hex "0123456789ABCDEF"
+
+ if {[empty_string_p $charset]} {
+ set end_of_line "="
+ set begin_of_line ""
+ } else {
+ set end_of_line "?="
+ set begin_of_line "=?$charset?Q?"
+ }
+
+ set result ""
+ set line $begin_of_line
+ for { set i 0 } { $i < [string length $text] } { incr i } {
+ set current [string index $text $i]
+ if { ![string is ascii $current] || [string first $current " \t\r\n()<>@,;:/\[\]?.=\"\\"] != -1 } {
+ binary scan $current c x
+ append line "=[string index $hex [expr ($x & 0xf0) >> 4]][string index $hex [expr $x & 0x0f]]"
+ } else {
+ append line $current
+ }
+ if { [string length $line] > 70 } {
+ if { ![empty_string_p $result] } { append result "\n" }
+ append result "${line}$end_of_line"
+ set line $begin_of_line
+ }
+ }
+
+ if { ![string equal $line $begin_of_line] } {
+ if { ![empty_string_p $result] } { append result "\n" }
+ append result "${line}$end_of_line"
+ }
+
+ return $result
+ }
+
+ ad_proc -public send_mail {
+ -to_addr:required
+ -from_addr:required
+ -subject:required
+ -body:required
+ -mime_type:required
+ -charset:required
+ {-package_id ""}
+ } {
+ send one email
+ } {
+ if {[empty_string_p $package_id]} {
+ set package_id [ad_conn package_id]
+ }
+ set header [ns_set new]
+ switch $mime_type {
+ "text/plain" {
+ # quoted-printable encode plaintext mails
+ ns_set put $header "Content-Type" text/plain
+######
+# ns_set put $header "Content-Transfer-Encoding" quoted-printable
+# set body [mail_job::quoted_printable_encode -text $body]
+######
+ }
+ "text/html" {
+ ns_set put $header "Content-Type" "text/html; charset=$charset"
+ }
+ }
+
+ acs_mail_lite::send -to_addr $to_addr -from_addr $from_addr -subject $subject -body $body -extraheaders $header -package_id $package_id
+######
+# acs_mail_lite::send -to_addr $to_addr -from_addr $from_addr -subject [mail_job::quoted_printable_encode -text $subject -charset $charset] -body $body -extraheaders $header -package_id $package_id
+######
+ }
+
+
+ ad_proc -private mail_bounced { header body } {
+ Record bounced mail
+ } {
+ ns_log notice "mailing lists: try to record bounced mail"
+ catch {
+ if {[regexp {X-Field: ([0-9]*)-([0-9]*)} $body match user_id job_id]} {
+ db_transaction {
+ ns_log notice "mailing lists: bounce recorded for job $job_id and user $user_id"
+ db_dml record_bounce_in_log {}
+
+ if {[db_resultrows]} {
+ db_dml increment_bounce_count {}
+ }
+ }
+ }
+ }
+ }
+
+ ad_proc -private create_links {
+ -text:required
+ -mime_type:required
+ -mail_job_id:required
+ } {
+ Grab all the links in the given text, insert them into the db and replace
+ the links in the text with links to the link-tracking package
+ } {
+ if {$mime_type == "text/plain"} {
+ set expression {(http://[a-zA-Z0-9:;_/&=,\#\?\.\-]+)}
+ set server_url_substitution "[ad_url][mailing_list::util::get_sw_url -package_key mail-links]\\1"
+ } else {
+ set expression {href="(http://[^"]*)"}
+ set server_url_substitution "href=\"[ad_url][mailing_list::util::get_sw_url -package_key mail-links]\\1\""
+ }
+
+ while {[regexp $expression $text match url]} {
+ set link_id [mail_link::add -mail_id $mail_job_id -url $url]
+ regsub $expression $text "$link_id\.$mail_job_id\.@user_id@ " text
+ }
+
+ regsub -all {([^<]*) } $text $server_url_substitution text
+ return $text
+ }
+
+ ad_proc -private sweeper {} {
+ Periodically send out all waiting mail jobs
+ } {
+ # Make sure that only one thread is processing the queue at a time.
+ if {[nsv_incr mailing_lists send_mails_p] > 1} {
+ nsv_incr mailing_lists send_mails_p -1
+ return
+ }
+
+ acs_mail_lite::with_finally -code {
+ set jobs [db_list get_queued_mail_jobs {}]
+
+ foreach job_id $jobs {
+ send -mail_job_id $job_id
+ }
+ } -finally {
+ nsv_incr mailing_lists send_mails_p -1
+ }
+ }
+}
Index: openacs-4/contrib/packages/mailing-lists/tcl/email-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/email-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/email-procs.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,77 @@
+
+
+
+
+
+
+ select j.subject, j.text_body, j.text_header, j.text_footer,
+ j.html_body, j.html_header, j.html_footer, j.locale,
+ j.mime_type as mail_mime_type, l.mime_charset,
+ j.sender_email, j.track_links_p, template_p,
+ j.css_revision_id, j.list_id, j.selection_id,
+ j.sql_query, j.bind_vars, j.package_id
+ from ml_mail_jobs j, ad_locales l
+ where j.mail_job_id = :mail_job_id
+ and j.state = 'active'
+ and l.locale = j.locale
+
+
+
+
+
+
+
+
+ select u.user_id, p.first_names as user_first_names,
+ p.last_name as user_last_name, i.email as user_email
+ from ml_mailing_list_user_map m, users u, parties i, persons p
+ where u.user_id = m.user_id
+ and u.email_bouncing_p = 'f'
+ and m.list_id = :list_id
+ and m.subscribed_p = 't'
+ and i.party_id = u.user_id
+ and p.person_id = u.user_id
+ and u.email_verified_p = 't'
+ and u.email_bouncing_p = 'f'
+
+
+
+
+
+
+
+
+ insert into ml_email_log (user_id, mail_job_id)
+ values (:user_id, :mail_job_id)
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set state = 'finished',
+ mails_sent = :mail_count,
+ track_links_p = (select case when count(*) = 0 then 'f' else 't' end
+ from mail_link_mail_map m
+ where mail_id = :mail_job_id)
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set mails_bounced = mails_bounced + 1
+ where mail_job_id = :job_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-init.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-init.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-init.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,8 @@
+mailing_list::util::reset_cached_package_urls
+
+# check every 20 minutes for new mail jobs
+ad_schedule_proc -thread t 1200 mail_job::sweeper
+
+nsv_set mailing_lists send_mails_p 0
+
+ad_schedule_proc -thread t -schedule_proc ns_schedule_daily [list 1 32] mailing_list::send_reminder
Index: openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-oracle.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,190 @@
+
+
+
+ oracle 8.1.6
+
+
+
+ begin
+ :1 := ml_mailing_list.new (
+ list_id => :list_id,
+ package_id => :package_id,
+ name => :name,
+ locale => :locale,
+ sender_email => :sender_email,
+ confirm_subject => :confirm_subject,
+ confirm_mime_type => :confirm_mime_type,
+ welcome_subject => :welcome_subject,
+ welcome_mime_type => :welcome_mime_type,
+ remind_subject => :remind_subject,
+ remind_mime_type => :remind_mime_type,
+ expiration_date => $expiration_date,
+ first_reminder => :first_reminder,
+ second_reminder => :second_reminder,
+ context_id => :package_id,
+ creation_user => :user_id,
+ creation_ip => :creation_ip);
+ end;
+
+
+
+
+
+
+ begin
+ ml_mailing_list.edit (
+ list_id => :list_id,
+ name => :name,
+ locale => :locale,
+ sender_email => :sender_email,
+ confirm_subject => :confirm_subject,
+ confirm_mime_type => :confirm_mime_type,
+ welcome_subject => :welcome_subject,
+ welcome_mime_type => :welcome_mime_type,
+ remind_subject => :remind_subject,
+ remind_mime_type => :remind_mime_type,
+ expiration_date => $expiration_date,
+ first_reminder => :first_reminder,
+ second_reminder => :second_reminder,
+ modifying_user => :user_id,
+ modifying_ip => :modifying_ip);
+ end;
+
+
+
+
+
+
+
+ insert into ml_mailing_list_user_map
+ (list_id, user_id, confirmed_p, subscribed_p, subscription_date)
+ values (:list_id, :user_id, :confirmed_p, :confirmed_p, sysdate)
+
+
+
+
+
+
+
+
+ select m.user_id, ml.sender_email, ml.remind_subject,
+ ml.remind_body, ml.remind_mime_type, l.mime_charset,
+ ml.list_id, n.node_id
+ from ml_mailing_lists ml, ml_mailing_list_user_map_user_map m,
+ ad_locales l, site_nodes n
+ where m.list_id = ml.list_id
+ and ml.locale = l.locale
+ and m.confirmed_p = 'f'
+ and n.object_id = r.package_id
+ and ((m.reminder_count = 0
+ and ml.first_reminder > 0
+ and m.subscription_date < sysdate - ml.first_reminder)
+ or (m.reminder_count = 1
+ and ml.second_reminder > 0
+ and m.subscription_date < sysdate - ml.second_reminder))
+
+
+
+
+
+
+
+
+ begin
+ :1 := ml_mail_class.new (
+ mail_class_id => :mail_class_id,
+ package_id => :package_id,
+ name => :name,
+ locale => :locale,
+ sender_email => :sender_email,
+ subject => :subject,
+ subject_change_p => :subject_change_p,
+ text_header_change_p => :text_header_change_p,
+ text_body_change_p => :text_body_change_p,
+ text_footer_change_p => :text_footer_change_p,
+ html_header_change_p => :html_header_change_p,
+ html_body_change_p => :html_body_change_p,
+ html_footer_change_p => :html_footer_change_p,
+ mime_type => :mime_type,
+ context_id => :package_id,
+ creation_user => :user_id,
+ creation_ip => :creation_ip);
+ end;
+
+
+
+
+
+
+
+
+ begin
+ ml_mail_class.edit (
+ mail_class_id => :mail_class_id,
+ name => :name,
+ locale => :locale,
+ sender_email => :sender_email,
+ subject => :subject,
+ subject_change_p => :subject_change_p,
+ text_header_change_p => :text_header_change_p,
+ text_body_change_p => :text_body_change_p,
+ text_footer_change_p => :text_footer_change_p,
+ html_header_change_p => :html_header_change_p,
+ html_body_change_p => :html_body_change_p,
+ html_footer_change_p => :html_footer_change_p,
+ mime_type => :mime_type,
+ modifying_user => :user_id,
+ modifying_ip => :modifying_ip);
+ end;
+
+
+
+
+
+
+
+ begin
+ :1 := ml_mail_job.new (
+ mail_class_id => :class_id,
+ list_id => :list_id,
+ selection_id => :selection_id,
+ package_id => :package_id,
+ locale => :locale,
+ sender_email => :sender_email,
+ track_links_p => :track_links_p,
+ subject => :subject,
+ template_p => :template_p,
+ mime_type => :mime_type,
+ state => :state,
+ scheduled_date => $scheduled_date,
+ bind_vars => :bind_vars,
+ context_id => :class_id,
+ creation_user => :user_id,
+ creation_ip => :creation_ip);
+ end;
+
+
+
+
+
+
+ begin
+ ml_mail_job.edit (
+ mail_job_id => :mail_job_id,
+ locale => :locale,
+ sender_email => :sender_email,
+ track_links_p => :track_links_p,
+ subject => :subject,
+ template_p => :template_p,
+ mime_type => :mime_type,
+ state => :state,
+ scheduled_date => $scheduled_date,
+ bind_vars => :bind_vars,
+ modifying_user => :user_id,
+ modifying_ip => :modifying_ip);
+ end;
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs-postgresql.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,223 @@
+
+
+
+ postgresql 7.1
+
+
+
+ select ml_mailing_list__new (
+ :list_id,
+ :package_id,
+ :name,
+ :locale,
+ :teaser,
+ :sender_email,
+ :confirm_subject,
+ :confirm_body,
+ :confirm_mime_type,
+ :welcome_subject,
+ :welcome_body,
+ :welcome_mime_type,
+ :remind_subject,
+ :remind_body,
+ :remind_mime_type,
+ $expiration_date,
+ :first_reminder,
+ :second_reminder,
+ :comments,
+ :package_id,
+ :user_id,
+ current_timestamp,
+ :creation_ip
+ );
+
+
+
+
+
+
+ select ml_mailing_list__edit (
+ :list_id,
+ :name,
+ :locale,
+ :teaser,
+ :sender_email,
+ :confirm_subject,
+ :confirm_body,
+ :confirm_mime_type,
+ :welcome_subject,
+ :welcome_body,
+ :welcome_mime_type,
+ :remind_subject,
+ :remind_body,
+ :remind_mime_type,
+ $expiration_date,
+ :first_reminder,
+ :second_reminder,
+ :comments,
+ :user_id,
+ :modifying_ip
+ );
+
+
+
+
+
+
+
+ insert into ml_mailing_list_user_map
+ (list_id, user_id, confirmed_p, subscribed_p, subscription_date)
+ values (:list_id, :user_id, :confirmed_p, :confirmed_p, current_timestamp)
+
+
+
+
+
+
+
+
+ select m.user_id, ml.sender_email, ml.remind_subject,
+ ml.remind_body, ml.remind_mime_type, l.mime_charset,
+ ml.list_id, n.node_id
+ from ml_mailing_lists ml, ml_mailing_list_user_map_user_map m,
+ ad_locales l, site_nodes n
+ where m.list_id = ml.list_id
+ and ml.locale = l.locale
+ and m.confirmed_p = 'f'
+ and n.object_id = r.package_id
+ and ((m.reminder_count = 0
+ and ml.first_reminder > 0
+ and m.subscription_date < (current_timestamp -
+ (cast (ml.first_reminder || ' days' as interval))
+ ))
+ or (m.reminder_count = 1
+ and ml.second_reminder > 0
+ and m.subscription_date < (current_timestamp -
+ (cast (ml.second_reminder || ' days' as interval)))
+ )
+ )
+
+
+
+
+
+
+
+
+ select ml_mail_class__new (
+ :mail_class_id,
+ :package_id,
+ :name,
+ :locale,
+ :sender_email,
+ :subject,
+ :subject_change_p,
+ null,
+ :text_header_change_p,
+ null,
+ :text_body_change_p,
+ null,
+ :text_footer_change_p,
+ null,
+ :html_header_change_p,
+ null,
+ :html_body_change_p,
+ null,
+ :html_footer_change_p,
+ :mime_type,
+ null,
+ :package_id,
+ :user_id,
+ current_timestamp,
+ :creation_ip
+ );
+
+
+
+
+
+
+ select ml_mail_class__edit (
+ :mail_class_id,
+ :name,
+ :locale,
+ :sender_email,
+ :subject,
+ :subject_change_p,
+ null,
+ :text_header_change_p,
+ null,
+ :text_body_change_p,
+ null,
+ :text_footer_change_p,
+ null,
+ :html_header_change_p,
+ null,
+ :html_body_change_p,
+ null,
+ :html_footer_change_p,
+ :mime_type,
+ null,
+ :user_id,
+ :modifying_ip);
+
+
+
+
+
+
+ select ml_mail_job__new (
+ null,
+ :class_id,
+ :list_id,
+ :selection_id,
+ :package_id,
+ :locale,
+ :sender_email,
+ :track_links_p,
+ :subject,
+ null,
+ null,
+ null,
+ null,
+ null,
+ null,
+ :template_p,
+ :mime_type,
+ :state,
+ $scheduled_date,
+ :bind_vars,
+ :class_id,
+ :user_id,
+ current_timestamp,
+ :creation_ip);
+
+
+
+
+
+
+ select ml_mail_job__edit (
+ :mail_job_id,
+ :locale,
+ :sender_email,
+ :track_links_p,
+ :subject,
+ null,
+ null,
+ null,
+ null,
+ null,
+ null,
+ :template_p,
+ :mime_type,
+ :state,
+ $scheduled_date,
+ :bind_vars,
+ :user_id,
+ :modifying_ip);
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,322 @@
+ad_library {
+ Procs for the mailing list manager package.
+
+ @author Timo Hentschel (thentschel@sussdorff-roy.com)
+
+ @creation-date 16 April 2003
+ @cvs-id $Id:
+}
+
+namespace eval mailing_list {
+
+ ad_proc -public add {
+ {-list_id ""}
+ {-package_id ""}
+ {-name ""}
+ {-locale ""}
+ {-teaser ""}
+ {-sender_email ""}
+ {-confirm_subject ""}
+ {-confirm_body ""}
+ {-confirm_mime_type "text/plain"}
+ {-welcome_subject ""}
+ {-welcome_body ""}
+ {-welcome_mime_type "text/plain"}
+ {-remind_subject ""}
+ {-remind_body ""}
+ {-remind_mime_type "text/plain"}
+ {-expiration_date [db_null]}
+ {-first_reminder 7}
+ {-second_reminder 0}
+ {-comments ""}
+ } {
+ Creates a new mailing-list
+ } {
+ set creation_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+ if {[empty_string_p $package_id]} {
+ set package_id [ad_conn package_id]
+ }
+
+ db_transaction {
+ set list_id [db_exec_plsql insert_mailing_list {}]
+
+ db_dml set_mailing_list_clobs {}
+ }
+
+ return $list_id
+ }
+
+ ad_proc -public edit {
+ {-list_id:required}
+ {-name ""}
+ {-locale ""}
+ {-teaser ""}
+ {-sender_email ""}
+ {-confirm_subject ""}
+ {-confirm_body ""}
+ {-confirm_mime_type "text/plain"}
+ {-welcome_subject ""}
+ {-welcome_body ""}
+ {-welcome_mime_type "text/plain"}
+ {-remind_subject ""}
+ {-remind_body ""}
+ {-remind_mime_type "text/plain"}
+ {-expiration_date [db_null]}
+ {-first_reminder 7}
+ {-second_reminder 0}
+ {-comments ""}
+ } {
+ Edits mailing-list
+ } {
+ set modifying_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+
+ db_transaction {
+ db_exec_plsql update_mailing_list {}
+
+ db_dml set_mailing_list_clobs {}
+ }
+ }
+
+ ad_proc -public user_subscribed_p {
+ -list_id:required
+ -user_id:required
+ } {
+ Checks if a user is already subscribed to a list
+ } {
+ return [db_0or1row check_user_entry_exists {}]
+ }
+
+ ad_proc -public add_user {
+ -list_id:required
+ -user_id:required
+ {-confirmed_p t}
+ } {
+ Add a user to a mailing list
+ } {
+ # try to add new user map entry
+ # if it fails, the user is already subscribed or unsubscribed by his own choice
+ if {![user_subscribed_p -list_id $list_id -user_id $user_id]} {
+ db_dml add_user_entry {}
+ catch {
+ db_dml add_user_log_entry {}
+ }
+ return 1
+ } else {
+ return 0
+ }
+ }
+
+ ad_proc -private send_reminder {} {
+ Sends a reminder email to users not having confirmed the
+ mass subscribtion
+ } {
+ set reminders [db_list_of_lists get_unconfirmed_subscriptions {}]
+
+ # send reminder
+ foreach reminder $reminders {
+ util_unlist $reminder user_id sender_email remind_subject remind_body remind_mime_type mime_charset list_id node_id
+ set confirmation_link "[ad_url][site_node::get_url -node_id $node_id]confirm?[export_url_vars list_id]"
+
+ mailing_list::util::send_mail -user_id $user_id -from_email $sender_email -subject $remind_subject -body $remind_body -mime_type $remind_mime_type -charset $mime_charset -link $confirmation_link
+
+ ns_log Notice "List subscription reminder sent to user $user_id"
+
+ # log sending the reminder mail
+ db_dml log_reminder_sending {}
+ }
+ }
+}
+
+namespace eval mail_class {
+
+ ad_proc -public add {
+ {-mail_class_id ""}
+ {-package_id ""}
+ {-name ""}
+ {-locale ""}
+ {-sender_email ""}
+ {-subject ""}
+ {-subject_change_p t}
+ {-text_header ""}
+ {-text_header_change_p t}
+ {-text_body ""}
+ {-text_body_change_p t}
+ {-text_footer ""}
+ {-text_footer_change_p t}
+ {-html_header ""}
+ {-html_header_change_p t}
+ {-html_body ""}
+ {-html_body_change_p t}
+ {-html_footer ""}
+ {-html_footer_change_p t}
+ {-mime_type "text/plain"}
+ {-comments ""}
+ } {
+ Creates a new mail-class
+ } {
+ set creation_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+ if {[empty_string_p $package_id]} {
+ set package_id [ad_conn package_id]
+ }
+
+ db_transaction {
+ set mail_class_id [db_exec_plsql insert_mail_class {}]
+
+ db_dml set_mail_class_clobs {}
+ }
+
+ return $mail_class_id
+ }
+
+ ad_proc -public edit {
+ {-mail_class_id:required}
+ {-name ""}
+ {-locale ""}
+ {-sender_email ""}
+ {-subject ""}
+ {-subject_change_p t}
+ {-text_header ""}
+ {-text_header_change_p t}
+ {-text_body ""}
+ {-text_body_change_p t}
+ {-text_footer ""}
+ {-text_footer_change_p t}
+ {-html_header ""}
+ {-html_header_change_p t}
+ {-html_body ""}
+ {-html_body_change_p t}
+ {-html_footer ""}
+ {-html_footer_change_p t}
+ {-mime_type "text/plain"}
+ {-comments ""}
+ } {
+ Edits mail-class
+ } {
+ set modifying_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+
+ db_transaction {
+ db_exec_plsql update_mail_class {}
+
+ db_dml set_mail_class_clobs {}
+ }
+ }
+}
+
+namespace eval mail_job {
+
+ ad_proc -public add {
+ {-class_id:required}
+ {-list_id:required}
+ {-selection_id:required}
+ {-locale ""}
+ {-sender_email ""}
+ {-track_links_p f}
+ {-subject ""}
+ {-text_header ""}
+ {-text_body ""}
+ {-text_footer ""}
+ {-html_header ""}
+ {-html_body ""}
+ {-html_footer ""}
+ {-template_p f}
+ {-mime_type "text/plain"}
+ {-state active}
+ {-scheduled_date [db_null]}
+ {-bind_vars ""}
+ } {
+ Creates a new mail-job
+ } {
+ set creation_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+ set package_id [ad_conn package_id]
+
+ db_transaction {
+ set mail_job_id [db_exec_plsql insert_mail_job {}]
+
+ db_dml set_email_clobs {}
+
+ if {![empty_string_p $selection_id]} {
+ db_1row get_users_selection_query {}
+
+ db_dml set_selection_query {}
+ }
+ }
+
+ return $mail_job_id
+ }
+
+ ad_proc -public edit {
+ {-mail_job_id:required}
+ {-locale ""}
+ {-sender_email ""}
+ {-track_links_p f}
+ {-subject ""}
+ {-text_header ""}
+ {-text_body ""}
+ {-text_footer ""}
+ {-html_header ""}
+ {-html_body ""}
+ {-html_footer ""}
+ {-template_p f}
+ {-mime_type "text/plain"}
+ {-state active}
+ {-scheduled_date [db_null]}
+ {-bind_vars ""}
+ } {
+ Edits a mail-job
+ } {
+ set modifying_ip [ad_conn peeraddr]
+ set user_id [ad_conn user_id]
+
+ db_transaction {
+ db_exec_plsql update_mail_job {}
+
+ db_dml set_email_clobs {}
+ }
+ }
+
+ ad_proc -private after_install {} {
+ Callback to be called after package installation.
+ Adds the service contract implementations for bounce-management.
+
+ @author Timo Hentschel (thentschel@sussdorff-roy.com)
+ } {
+ acs_sc::impl::new -contract_name AcsMailLite -name mailing_lists -owner mailing_lists
+ acs_sc::impl::alias::new -contract_name AcsMailLite -impl_name mailing_lists -operation MailBounce -alias mail_job::mail_bounced
+ acs_sc::impl::binding::new -contract_name AcsMailLite -impl_name mailing_lists
+ }
+
+ ad_proc -private before_uninstall {} {
+ Callback to be called before package uninstallation.
+ Removes the service contract implementations for bounce-management.
+
+ @author Timo Hentschel (thentschel@sussdorff-roy.com)
+ } {
+ # shouldn't we first delete the bindings?
+ acs_sc::impl::delete -contract_name AcsMailLite -impl_name mailing_lists
+ }
+}
+
+
+# procs for acsobject service contract
+ad_proc -public ml_get_mailing_list_pageurl { object_id } {
+ Returns the page that displays a mailing-list
+} {
+ return "madmin/lists?list_id=$object_id"
+}
+
+ad_proc -public ml_get_mail_job_pageurl { object_id } {
+ Returns the page that displays a mail-job
+} {
+ return "madmin/jobs?mail_job_id=$object_id"
+}
+
+ad_proc -public ml_get_mail_class_pageurl { object_id } {
+ Returns the page that displays a mail-class
+} {
+ return "madmin/classes?class_id=$object_id"
+}
Index: openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/mailing-lists-procs.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,158 @@
+
+
+
+
+
+
+ update ml_mailing_lists
+ set teaser = :teaser,
+ confirm_body = :confirm_body,
+ welcome_body = :welcome_body,
+ remind_body = :remind_body,
+ comments = :comments
+ where list_id = :list_id
+
+
+
+
+
+
+
+
+ update ml_mailing_lists
+ set teaser = :teaser,
+ confirm_body = :confirm_body,
+ welcome_body = :welcome_body,
+ remind_body = :remind_body,
+ comments = :comments
+ where list_id = :list_id
+
+
+
+
+
+
+
+
+ insert into ml_user_email_log (user_id, last_mail_date)
+ (select :user_id as user_id, null as last_mail_date
+ from dual
+ where not exists (select 1 from ml_user_email_log
+ where user_id = :user_id))
+
+
+
+
+
+
+
+
+ update ml_mailing_list_user_map
+ set reminder_count = reminder_count + 1
+ where user_id = :user_id
+
+
+
+
+
+
+
+
+ select 1
+ from ml_mailing_list_user_map
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
+
+
+ update ml_mail_classes
+ set text_header = :text_header,
+ text_body = :text_body,
+ text_footer = :text_footer,
+ html_header = :html_header,
+ html_body = :html_body,
+ html_footer = :html_footer,
+ comments = :comments
+ where mail_class_id = :mail_class_id
+
+
+
+
+
+
+
+
+ update ml_mail_classes
+ set text_header = :text_header,
+ text_body = :text_body,
+ text_footer = :text_footer,
+ html_header = :html_header,
+ html_body = :html_body,
+ html_footer = :html_footer,
+ comments = :comments
+ where mail_class_id = :mail_class_id
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set text_header = :text_header,
+ text_body = :text_body,
+ text_footer = :text_footer,
+ html_header = :html_header,
+ html_body = :html_body,
+ html_footer = :html_footer
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ select full_sql
+ from us_selections
+ where selection_id = :selection_id
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set sql_query = :full_sql
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
+
+
+ update ml_mail_jobs
+ set text_header = :text_header,
+ text_body = :text_body,
+ text_footer = :text_footer,
+ html_header = :html_header,
+ html_body = :html_body,
+ html_footer = :html_footer
+ where mail_job_id = :mail_job_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/util-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/util-procs-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/util-procs-oracle.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle 8.1.6
+
+
+
+
+ select site_node.url(n.node_id) as package_url, p.package_key
+ from site_nodes n, site_nodes top, apm_packages p
+ where top.parent_id is null
+ and n.parent_id = top.node_id
+ and p.package_id = n.object_id
+ and p.package_key in ('users-selection', 'categories', 'mail-links', 'mailing-lists')
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/util-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/util-procs-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/util-procs-postgresql.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql 7.1
+
+
+
+
+ select site_node__url(n.node_id) as package_url, p.package_key
+ from site_nodes n, site_nodes top, apm_packages p
+ where top.parent_id is null
+ and n.parent_id = top.node_id
+ and p.package_id = n.object_id
+ and p.package_key in ('users-selection', 'categories', 'mail-links', 'mailing-lists')
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/tcl/util-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/util-procs.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/util-procs.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,200 @@
+namespace eval mailing_list::util {
+
+ ad_proc -private reset_cached_package_urls {} {
+ Caches the urls of the site-wide package instances of
+ categories and users-selection
+ } {
+ catch {nsv_unset ml_cached_urls}
+
+ db_foreach get_site_wide_package_urls {} {
+ nsv_set ml_cached_urls $package_key $package_url
+ }
+ }
+
+ ad_proc -public get_sw_url {
+ -package_key:required
+ } {
+ Returns the cached url of a site-wide package
+ } {
+ if {[catch {set url [nsv_get ml_cached_urls $package_key]}]} {
+ # no url found
+ return
+ }
+ return $url
+ }
+
+ ad_proc -public text_contains_tcl_code {
+ -text:required
+ } {
+ Checks if text contains malicious inline-tcl code (adp-style)
+ } {
+ if {[regexp {<%[^=](.*?)%>} $text match tcl_code] || [regexp {<%=.*?(\[.*?)%>} $text match tcl_code]} {
+ return 1
+ }
+ return 0
+ }
+
+ ad_proc -public check_valid_vars {
+ -text:required
+ -valid_vars:required
+ } {
+ Checks if the template vars used in a text are all valid
+ in the sense that they all can be found the provided list.
+ } {
+ # get list of used variables
+ set current_index 0
+ while {[regexp -indices -start $current_index [template::adp_variable_regexp] $text match match found_variable]} {
+ set current_index [expr [lindex $found_variable 1] + 1]
+ set user_variable [string range $text [lindex $found_variable 0] [lindex $found_variable 1]]
+ set used_vars($user_variable) 1
+ }
+
+ set errors ""
+ # check if used variables are all valid
+ foreach used_var [array names used_vars] {
+ if {[lsearch -exact $valid_vars $used_var] == -1} {
+ # used var not a valid one
+ lappend errors $used_var
+ }
+ }
+ return $errors
+ }
+
+ ad_proc -public send_mail {
+ -user_id:required
+ -from_email:required
+ -subject:required
+ -body:required
+ -mime_type:required
+ -charset:required
+ {-link ""}
+ {-new_password ""}
+ } {
+ Sends the confirmation/welcome mail to a user
+ } {
+ if {[empty_string_p $body]} {
+ # don't send any mail if message is empty
+ return
+ }
+
+ db_1row get_user_data {}
+
+ # generate the x-variable that's needed to verify the users identity
+ # when he clicks the link
+ set x [ns_sha1 "$user_id $user_password $user_salt"]
+ append link "&[export_url_vars x]"
+
+ # if user is already known to the system and not newly generated,
+ # leave password blank so that a in
+ # the mail text will not get executed (intended to provide
+ # the user_password in the email to new users)
+ set user_password $new_password
+
+ # we have to use noquote-substitution for plaintext emails
+ if {$mime_type == "text/plain"} {
+ while {[regsub -all [template::adp_variable_regexp] $body {\1@\2;noquote@} body]} {}
+ }
+
+ # substitute template variables in confirmation email
+ variable ::template::parse_level
+ lappend ::template::parse_level [info level]
+ set error_p 0
+ with_catch __errmsg {
+ set __result [template::adp_compile -string $body]
+ template::adp_eval __result
+ set body $__adp_output
+ } {
+ # substitution failed
+ set error_p 1
+ }
+ template::util::lpop ::template::parse_level
+
+ set to_addr(email) [list $user_email]
+ set to_addr(user_id) [list $user_id]
+ set to_addr(name) [list $user_name]
+
+ if {!$error_p} {
+ # now send the mail
+ ns_log notice "mime_type: $mime_type / charset: $charset"
+ mail_job::send_mail -to_addr [array get to_addr] -from_addr $from_email -subject $subject -body $body -mime_type $mime_type -charset $charset -package_id [ad_conn package_id]
+ }
+ return $error_p
+ }
+
+ ad_proc -public valid_x_field_p {
+ -user_id:required
+ -x_field:required
+ } {
+ Verifies the x-field in the users email
+ } {
+ db_1row get_user_data {}
+
+ # generate the expected x-variable
+ set expected_x [ns_sha1 "$user_id $user_password $user_salt"]
+
+ # Check if both values are the same and return t or f
+ return [string equal $x_field $expected_x]
+ }
+
+ ad_proc -public check_valid_country_codes {
+ -emails_and_countries:required
+ } {
+ Checks the csv-list of emails and countries for valid country codes
+ and returns list of emails with invalid country codes
+ } {
+ # get all mappings country_code -> country_id so that we can check
+ # a lot easier
+ db_foreach get_country_codes {} {
+ set country_id($country_code) $category_id
+ }
+
+ # generate tmp-file for csv-processing
+ set tmp_file [ns_tmpnam]
+ set fd [open $tmp_file w]
+ puts $fd $emails_and_countries
+ close $fd
+ set fd [open $tmp_file r]
+
+ set invalid_list ""
+
+ # process every line of the user/country list
+ while {[ns_getcsv $fd line] > 0} {
+ # grap the email and country of the current line
+ util_unlist $line email country
+
+ if {[empty_string_p $country] || ![exists_and_not_null country_id($country)]} {
+ # empty or invalid country code
+ lappend invalid_list $email
+ }
+ }
+ return $invalid_list
+ }
+
+ ad_proc -public check_valid_emails {
+ -emails_and_countries:required
+ } {
+ Checks the csv-list of emails and countries for valid email addresses
+ and returns list of invalid emails
+ } {
+ # generate tmp-file for csv-processing
+ set tmp_file [ns_tmpnam]
+ set fd [open $tmp_file w]
+ puts $fd $emails_and_countries
+ close $fd
+ set fd [open $tmp_file r]
+
+ set invalid_list ""
+
+ # process every line of the user/country list
+ while {[ns_getcsv $fd line] > 0} {
+ # grap the email and country of the current line
+ util_unlist $line email country
+
+ if {![util_email_valid_p $email]} {
+ # invalid email address
+ lappend invalid_list $email
+ }
+ }
+ return $invalid_list
+ }
+}
Index: openacs-4/contrib/packages/mailing-lists/tcl/util-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/tcl/util-procs.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/tcl/util-procs.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,44 @@
+
+
+
+
+
+
+ select u.password as user_password, u.salt as user_salt,
+ p.first_names || ' ' || p.last_name as user_name,
+ y.email as user_email
+ from users u, persons p, parties y
+ where u.user_id = :user_id
+ and p.person_id = u.user_id
+ and y.party_id = u.user_id
+
+
+
+
+
+
+
+
+ select u.password as user_password, u.salt as user_salt,
+ p.first_names || ' ' || p.last_name as user_name,
+ y.email as user_email
+ from users u, persons p, parties y
+ where u.user_id = :user_id
+ and p.person_id = u.user_id
+ and y.party_id = u.user_id
+
+
+
+
+
+
+
+
+ select country_code, category_id
+ from ml_country_codes
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/confirm-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/confirm-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/confirm-oracle.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ oracle 8.1.6
+
+
+
+
+ update ml_mailing_list_user_map
+ set confirmed_p = 't',
+ subscribed_p = 't',
+ subscription_date = sysdate
+ where user_id = :user_id
+ and list_id = :list_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/confirm-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/confirm-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/confirm-postgresql.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,20 @@
+
+
+
+ postgresql 7.1
+
+
+
+
+ update ml_mailing_list_user_map
+ set confirmed_p = 't',
+ subscribed_p = 't',
+ subscription_date = current_timestamp
+ where user_id = :user_id
+ and list_id = :list_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/confirm.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/confirm.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/confirm.adp 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,9 @@
+
+Mailing Lists Confirmation
+@context_bar;noquote@
+
+
+ Welcome to the "@name@" Mailing List!
+
+ Invalid secret key!
+
Index: openacs-4/contrib/packages/mailing-lists/www/confirm.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/confirm.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/confirm.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,41 @@
+ad_page_contract {
+ Confirms user to a mailing list
+} {
+ list_id:integer,notnull
+ x:notnull
+ user_id:optional
+}
+
+if {[exists_and_not_null user_id] && [exists_and_not_null x]} {
+ # user most likely clicked a link in an email
+ # verify the secret key and try to login the user
+ if {[mailing_list::util::valid_x_field_p -user_id $user_id -x_field $x]} {
+ # secret key ok, login user
+ ad_user_login -forever=0 $user_id
+ } else {
+ # could not verify the secret key for that user
+ ad_return_complaint 1 "Invalid secret key"
+ return
+ }
+} else {
+ set user_id [ad_maybe_redirect_for_registration]
+}
+
+set package_id [ad_conn package_id]
+
+set context_bar [list "Confirm"]
+set valid_x_p [mailing_list::util::valid_x_field_p -user_id $user_id -x_field $x]
+
+if {$valid_x_p} {
+ db_transaction {
+ db_dml confirm_email_address {}
+
+ db_dml confirm_list_subscribtion {}
+
+ db_1row get_list_name {}
+ }
+} else {
+ ns_log notice "Invalid secret key of user $user_id when confirming for list $list_id"
+}
+
+ad_return_template
Index: openacs-4/contrib/packages/mailing-lists/www/confirm.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/confirm.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/confirm.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,26 @@
+
+
+
+
+
+
+ update users
+ set email_verified_p = 't'
+ where user_id = :user_id
+
+
+
+
+
+
+
+
+ select name
+ from ml_mailing_lists
+ where list_id = :list_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/index-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/index-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/index-oracle.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+ oracle 8.1.6
+
+
+
+
+ select l.list_id, l.name
+ from ml_mailing_lists l, ml_mailing_list_user_map m
+ where l.package_id = :package_id
+ and l.list_id = m.list_id
+ and l.expiration_date > sysdate
+ and m.user_id = :user_id
+ and m.subscribed_p = 't'
+ order by lower(l.name)
+
+
+
+
+
+
+
+ select l.list_id, l.name, l.teaser, c.tree_id, c.category_id,
+ nvl(m.subscribed_p, 'f') as subscribed_p
+ from ml_mailing_lists l, ml_mailing_list_user_map m,
+ category_object_map cm, categories c, ml_category_trees_visible v
+ where l.package_id = :package_id
+ and l.list_id = m.list_id(+)
+ and m.user_id(+) = :user_id
+ and l.public_p = 't'
+ and l.locale = :locale
+ and l.expiration_date > sysdate
+ and cm.object_id = l.list_id
+ and cm.category_id = c.category_id
+ and v.package_id = :package_id
+ and v.tree_id = c.tree_id
+ order by c.tree_id, c.category_id, lower(l.name)
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/index-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/index-postgresql.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,44 @@
+
+
+
+ postgresql 7.1
+
+
+
+
+ select l.list_id, l.name
+ from ml_mailing_lists l, ml_mailing_list_user_map m
+ where l.package_id = :package_id
+ and l.list_id = m.list_id
+ and l.expiration_date > current_timestamp
+ and m.user_id = :user_id
+ and m.subscribed_p = 't'
+ order by lower(l.name)
+
+
+
+
+
+
+
+ select l.list_id, l.name, l.teaser, c.tree_id, c.category_id,
+ coalesce(m.subscribed_p, 'f') as subscribed_p
+ from ml_mailing_lists l
+ left outer join ml_mailing_list_user_map m
+ on (l.list_id = m.list_id and m.user_id = :user_id)
+ inner join category_object_map cm
+ on (cm.object_id = l.list_id)
+ inner join categories c using (category_id)
+ inner join ml_category_trees_visible v using (tree_id)
+ where l.package_id = :package_id
+ and l.public_p = 't'
+ and l.locale = :locale
+ and l.expiration_date > current_timestamp
+ and v.package_id = :package_id
+ order by c.tree_id, c.category_id, lower(l.name)
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/index.adp 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,51 @@
+
+Mailing Lists Index
+@context_bar;noquote@
+
+
+
+
+
+
+ Mailing Lists you are already subscribed to
+
+
+
+ >@languages.label@
+
+
+
+
+
+
Available Mailing Lists
+
+
+
+ No mailing lists available in this language.
+
Index: openacs-4/contrib/packages/mailing-lists/www/index.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/index.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/index.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,49 @@
+ad_page_contract {
+} {
+ locale:optional
+ user_id:optional
+ x:optional
+} -properties {
+ lists:multirow
+ jobs:multirow
+}
+
+if {[exists_and_not_null user_id] && [exists_and_not_null x]} {
+ # user most likely clicked a link in an email
+ # verify the secret key and try to login the user
+ if {[mailing_list::util::valid_x_field_p -user_id $user_id -x_field $x]} {
+ # secret key ok, login user
+ ad_user_login -forever=0 $user_id
+ } else {
+ # could not verify the secret key for that user
+ ad_return_complaint 1 "Invalid secret key"
+ return
+ }
+} else {
+ set user_id [ad_maybe_redirect_for_registration]
+}
+
+set package_id [ad_conn package_id]
+set admin_p [permission::permission_p -object_id $package_id -privilege mailing_list_admin]
+
+if {![info exists locale]} {
+ set locale [ad_conn locale]
+}
+
+db_multirow subscribed_lists get_lists_user_is_subscribed {}
+
+template::multirow create lists list_id name teaser tree_id tree_name category_id category_name
+
+db_foreach get_lists_by_language {} {
+ if {$subscribed_p == "f"} {
+ set category_name [category::get_name $category_id]
+ set tree_name [category_tree::get_name $tree_id]
+ template::multirow append lists $list_id $name $teaser $tree_id $tree_name $category_id $category_name
+ }
+}
+
+db_multirow languages get_languages {}
+
+set context_bar ""
+
+ad_return_template
Index: openacs-4/contrib/packages/mailing-lists/www/index.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/index.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/index.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,14 @@
+
+
+
+
+
+
+ select label, locale
+ from ad_locales
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-oracle.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-oracle.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+ oracle 8.1.6
+
+
+
+
+ update ml_mailing_list_user_map
+ set subscribed_p = 'f',
+ unsubscription_date = sysdate
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
+
+
+ insert into ml_mailing_list_user_map
+ (list_id, user_id, subscribed_p, subscription_date)
+ values (:list_id, :user_id, 't', sysdate)
+
+
+
+
+
+
+
+
+ update ml_mailing_list_user_map
+ set subscribed_p = 't',
+ subscription_date = sysdate
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-postgresql.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe-postgresql.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,43 @@
+
+
+
+ postgresql 7.1
+
+
+
+
+ update ml_mailing_list_user_map
+ set subscribed_p = 'f',
+ unsubscription_date = current_timestamp
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
+
+
+ insert into ml_mailing_list_user_map
+ (list_id, user_id, subscribed_p, subscription_date)
+ values (:list_id, :user_id, 't', current_timestamp)
+
+
+
+
+
+
+
+
+ update ml_mailing_list_user_map
+ set subscribed_p = 't',
+ subscription_date = current_timestamp
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,41 @@
+ad_page_contract {
+ Subscribes or unsubscribes a user to a mailing list
+} {
+ {list_ids:multiple,notnull ""}
+ unsubscribe:optional
+}
+
+set user_id [ad_maybe_redirect_for_registration]
+set package_id [ad_conn package_id]
+
+if {[info exists unsubscribe]} {
+ # unsubscribe user. i assume that this user currently is subscribed
+ db_transaction {
+ template::util::list_to_lookup $list_ids lists
+ foreach list_id [array names lists] {
+ db_dml unsubscribe_user {}
+ }
+ }
+} else {
+ #subscribe user. check if user already has a map entry
+ db_transaction {
+ template::util::list_to_lookup $list_ids lists
+ foreach list_id [array names lists] {
+ if {![db_0or1row check_user_entry_exists {}]} {
+ # add new user map entry
+ db_dml add_user_entry {}
+ } else {
+ # update old user map entry
+ db_dml subscribe_user {}
+ }
+ db_1row get_welcome_text {}
+
+ set unsubscribe_link "[ad_url][ad_conn package_url]?[export_url_vars user_id]"
+
+ # send welcome email
+ mailing_list::util::send_mail -user_id $user_id -from_email $sender_email -subject $welcome_subject -body $welcome_body -mime_type $welcome_mime_type -charset $mime_charset -link $unsubscribe_link
+ }
+ }
+}
+
+ad_returnredirect "."
Index: openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.xql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/list-toggle-subscribe.xql 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,30 @@
+
+
+
+
+
+
+ select 1
+ from ml_mailing_list_user_map
+ where list_id = :list_id
+ and user_id = :user_id
+
+
+
+
+
+
+
+
+ select l.sender_email, l.welcome_subject, l.welcome_body,
+ l.welcome_mime_type, p.email as user_email, o.mime_charset
+ from ml_mailing_lists l, ad_locales o, parties p
+ where l.list_id = :list_id
+ and o.locale = l.locale
+ and p.party_id = :user_id
+
+
+
+
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/master.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/master.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/master.adp 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,5 @@
+
+@title;noquote@
+@context_bar;noquote@
+
+
Index: openacs-4/contrib/packages/mailing-lists/www/master.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/master.tcl,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/master.tcl 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,4 @@
+# There seems to be no way to elegantly set default values here
+if { ![info exists context_bar] } {
+ set context_bar ""
+}
Index: openacs-4/contrib/packages/mailing-lists/www/doc/index.html
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mailing-lists/www/doc/index.html,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/mailing-lists/www/doc/index.html 1 Oct 2003 05:01:54 -0000 1.1
@@ -0,0 +1,207 @@
+User Documentation for Mailing List Manager
+
+User Documentation for Mailing List Manager
+
+After installing mailing-lists, categories, users-selection,
+acs-mail-lite, mail-links and acs-datetime, you should first
+deal with categories so that you can later categorize
+mailing-lists in several dimensions (called category trees).
+According to the spec, greenpeace will categorize mailing lists
+in the dimensions region (i.e. united states, germany...),
+campaign (i.e. rainforest, whales, war...) and target (i.e. press,
+cyberactivists...), but other dimensions (trees) can be added.
+
+
+
+To setup these categories, you use the admin interface of the
+categories package to add category trees - let's say 'regions'.
+Then you can add top-level nodes to this tree - like 'north america',
+'europe'. After that, you can add child nodes to these categories
+- like 'germany' and 'france' in europe and 'united states' in
+north america. Since the category package is multilingual,
+you can select a different language from the select box and
+start translating the categories by editing them. Later, a user
+will see then see the categories in the default language that he set
+in his settings.
+
+
+
+After you setup some category trees, you can use the context bar to
+go to 'Main Site' and then to the mailing list package. At the top
+of the admin interface, you can now use the link 'Administer
+Categories' to come back to the categories package to now mount
+some of your created category trees to the mailing list package
+so that they can be used to categorize lists. You can choose to use
+whole trees or only subtrees (you then have to select where the
+subtree should start), but you normally would use a whole tree.
+
+
+
+After you mapped your trees to mailing lists, use the context bar
+to come back to the mailing list package. By using the link
+'Change Visible Categories' you can select, which category trees
+should be seen by users when browsing to look for other interesting
+lists (for example, you may want to decide that the users can
+only look through the list of existing mailing lists by region
+and by campaign, not by target).
+
+
+
+The categorization system is also used to store the information
+which user is coming from which country. To tell the system
+which category tree to use for this user categorization
+(the regions tree), you have to use the link 'Change Country
+Category'.
+
+
+
+Since the mass subscribtion will be done by providing a list
+if emails and country codes, the system will have to know which
+country is meant by what country code. This mapping can be done
+by using the link 'Administer Country Codes'.
+
+
+
+The mailing list manager can not only be used to spam mailing lists
+for which users have signed up, but also to spam a list of users
+selected by a custom sql query. These sql queries can be managed
+by the users selections package. In this package, an admin can
+write sql queries containing bind variables for which he provides
+default values. These queries can be administered and their
+results tested for different bind variable data by using the link
+'Administer Users Selections'.
+
+
+
Mailing Lists
+
+When adding a new mailing list, the user has to provide a name,
+select the language the list will be in, enter some teaser text
+to be displayed in the users list of available mailing lists and
+categorize the list in the category trees mapped to this
+mailing list package. In addition to that, the user has to provide
+data for the confirmation-, welcome- and reminder-emails sent out by
+the system: The emails need a sender email address, a subject,
+a body and a mime-type (either plaintext or html). Since the user
+being mass subscribed to a mailing list is not yet known to the
+system, it is not possible to use dynamic variables like
+@user_first_names@ that will be replaced by the users first name
+(you can do that in the mail jobs or mail classes later).
+
+
+
+To include a link to confirm the subscribtion, you can use @link@
+in the confirmation- and reminder-emails. To tell a newly added
+user his password in the confirmation- or welcome-email (we can't
+figure out the password when sending reminder emails since they
+are stored encrypted) you should use code like
+
+
<if @user_password@ not nil> Your new password is: @user_password@ </if>
+
+Further data for mailing lists are the date when the mailinglist
+will expire (just set it to some date in the far future if no
+expiration date needed) and the number of days after a user
+received the first mail to confirm the subscribtion to a new
+mailing-list that a reminder email will be sent out. For example,
+if you set the 1st reminder to 7, then the user will be reminded
+after a week to confirm the subscribtion. If you want to remind
+only once, just enter 0 as 2nd reminder. After all that, you can
+enter some comment to that new mailing list to maybe explain
+some other admin what this is all about.
+
+
+
+After the creation of a mailing list, you will see it at the index
+page together with the information how many users are subscribed
+to it or still need to confirm the subscribtion and you will see
+links to delete that mailing list and manage the permissions to it.
+You can edit the mailing list data by clicking on the lists name.
+When managing permissions, you can make a list public to all
+mailing list admins (so they can use and edit it), or you can grant
+some other specific other admins the rights to use and/or edit it.
+The list of other admins will contain only those admins that are
+not general mailing-list package-admins, because they can always
+see and edit all mailing lists or mail classes.
+
+
+
+If you want to mass subscribe some users, you have to select to
+which list you want them to be subscribed and provide a comma
+seperated list (csv) of emails and contry codes (see administring
+country codes) and check if you want them to be asked for
+confirmation or if you just want them to be immediately subscribed.
+The users will then either receive a confirmation email or a
+welcome email. If an email is not known yet, a new user will be
+created.
+
+
+
Mail Classes
+
+A mail class is a template for a new mail to a mailing list or a
+users selection. When creating a new mail class, you have to
+choose if you want the mail class to be a template for a plaintext
+email, a html email or a mixed email with both a plaintext and
+a html part. After that, you will see the form asking to provide
+a name, a language the mail will be in, a sender email address,
+a subject, header, body and footer for the mail. The header and
+footer can be used to set a fixed beginning and end for a mail
+framing a mail-specific body. Please note that there will be no
+additional newline or anything inserted between these three parts.
+The user can also define in a mail class if the subject, header,
+body and footer can later be changed when actually using the mail
+class in a mail job or if they will be fixed. Further, a css-file
+can be uploaded and attached to a mail class and comments can be
+given to other editing admins.
+
+
+
+After creating a mail class, you can edit, delete or copy this
+class or manage the permissions for it - just as for mailing lists.
+
+
+
Mail Jobs
+
+When creating a new mail job, you will be asked which mail
+class you want to use and which mailing list or which users
+selection you want to spam. Then you will see the mail job
+form where you can set the language of this mail job, change
+the sender email and edit the mail subject, header, body and
+footer - if the used mail class lets you do that. When spamming
+a mailing list you can use the variables @user_id@, @user_email@,
+@user_first_names@ and @user_last_name@ which will be dynamically
+replaced with the appropriate data for every user. When spamming
+a user-selection, you can change the used bind vars of the query
+if some exist and you can use all query columns of the query in
+the form @column_name@ in the mail text. In addition to that,
+you can upload and delete mail attachments (the css-file of the
+mail class will be shown if it has one). To upload a new
+attachment, you have to use the ok-button of the form and you
+will get to the same form again and again until you don't upload
+a new attachment. After uploading all atachments, you can decide
+if you want links used in the mail text to be tracked so that
+every click of users to these links will be recorded by the system.
+
+
+
+If you use this feature, the system will look for links beginning
+with http:// in the plaintext part of the mail and for links like
+href="http:// in the html part. In the end, you can set the execution
+date (default: mail will be send in the next 20 minutes) and
+set the mail job's status as active or suspended. Please note that
+when uploading attachments, the status will be automatically set
+to suspended and you therefore need to set it to active again when
+finally submitting the mail job. Before the mail job gets created,
+you will see a confirmation page showing you the final email.
+
+
+
+You can see pending mail jobs by using the link 'Pending Mail Jobs'.
+Here you can edit the mail jobs, delete, suspend and activate them.
+Processed mail jobs can be found by using the 'Mail Job History'.
+By using the 'Mail Links' link for mail jobs found, you can then
+see how many users clicked which link in the mail.
+
+
+
+
+