spam_history
table
# insert a new spam message into the spam queue, returns the spam_id of the new message spam_post_new_spam_message {{ -db 0 -template_p "f" -from_address "" -title "" -body_plain "" -body_html "" -body_aol "" -target_users_description "" -target_users_query "" -send_date "sysdate" -creation_user "" }}The
target_users_description
should be an English description of
who the designated target users are, for administrative documentation purposes.
The target_users_query
should be a SQL query which returns the following
columns for each user you want to send email to:
Column Name | Meaning |
---|---|
user_id | user_id |
email | user's email address |
email_type [optional] | text/plain , text/html , or text/aol-html .If you don't return this column in your query, the email type default to plain text. |
If the message body is going to be evaluated as a Tcl template, include these columns as well: | |
first_names | User's first name |
last_name | User's last name |
order_by user_id
, so that the
query returns a list of users in increasing order by their user_id. This allows
the spam system to checkpoint itself and resume sending where it left off in case
the server is restarted in the middle of a long send job. See examples below:
users_spammable
view,
which will exclude users who have expressed a desire not to receive email
alerts from the systems.
A simple plain text spam to members of a specific user_group:select u.user_id, email, 'text/plain' as email_type from users_spammable u, user_group_map ugm where ugm.group_id = 12345 and ugm.user_id = u.user_id order by u.user_idA spam to members of a specific user_group, which uses either the plain-text or html alternative content for the message, depending on the users email-type preference:
select u.user_id, email, email_type from users_spammable u, user_group_map ugm, users_preferences up where ugm.group_id = 12345 and ugm.user_id = u.user_id and up.user_id = u.user_id order by u.user_id
If checked, then Tcl evaluator will be run at the time the message is actually sent on message subject and body, substituting variables or procedure calls wherever \$ and \[\] chars are found. This is specially useful for creating automatically generated templates, such as a daily report which runs a tcl procedure to query the database or create some other summary message.
Note: if you have the Template? option selected, make sure you use backslash to quote any $ or '[]' characters in your message, if you do not want them to undergo evaluation by the Tcl parser.
The following variables are guaranteed to be set in the environment, for use by your template.
db2
a database handle for any subqueries you want to make
user_id
the user_id of the current target user
email
the email address of the current target user
first_names
last_name
The param is an association-list, with SQL patterns on the left, and
pseudo-mime types on the right. Supported types right now are text/html,
text/html, and text/aol-html
.
EmailTypes={%@hotmail.com text/html} {%@aol.com text/aol-html}
Every time you create a new user group, a new user_class will be created to go along with it. You may want to designate a specific group type to be the "Newsletter" group type, and then you can make a user self-service subscriptions page, which simply adds or removes the user from the groups of type Newsletter. You can then configure the Daily Spam Locations admin page to look for content files which correspond to the different Newsletter groups. Here is an example of what the admin page might look like for sending three types of newsletters; Daily, Weekly, and Special Updates.
You can enter the following information for an automatic spam daily message:
DailySpamDirectory
:
Example:
DailySpamDirectory=/home/johnny/spamFor each spam defined in the Daily Spam list, the system will look for the following files:
file_prefix-MM-DD-YYYY file_prefixNote: Be sure to always use two-digits for Month and Day fields, i.e., 03-06-1999. Don't forget the leading zeros.
If a file whose name matches with the specified prefix and the current day's date is found, the contents are queued up to be sent as spam to the designated target user class.
The spam system will only send a dated spam once. It keeps a history of all spams sent, and will be robust across server restarts. If the server is restarted in the middle of sending a spam, the spam daemon will resume sending where it left off in the list of users.
select user_id from users where trunc(registration_date)=trunc(sysdate-1)which is carefully constructed to always select a mutually exclusive set of users each day, and never repeat the same user twice.
file_prefix-html-MM-DD-YYYY file_prefix-aol-MM-DD-YYYYThen content from these files will be sent preferentially to users who have their email types preferences set in the database.
[ns/server/yourserver/acs/spam] ; Pairs of {email_addr_pattern pseudo-mime-type} EmailTypes={%@hotmail.com text/html} {%@aol.com text/aol-html} DailySpamDirectory=/web/yourserver/spam SpamRobotFromAddress=email-robot@yourdomain.com
For more info on the configuration of bulkmail and qmail, see bulkmail and qmail configuration
send_email_attachment_from_file
allows you to send a file via ns_sendmail as a MIME encoded email attachment. The file
can have binary content such as a JPEG image or PDF document.
Other module writers who want to post spam, Please Read This:
This is the spam_history.user_class_query column, which should be a well formed SQL query which when run returns the following columns for each target user: user_id, email, email_type.
It is suggested that the query be done over the users_spammable table, in order to not annoy users who have expressed their preference not to get email notifications.
Example user_class_query SQL:
select user_id, email, email_type from users_spammable where (email like 'john%') and (last_visit < (sysdate - 20))Note that the email_type field comes from the users_preferences table, not the users table. The users_spammable view internally does a join of users and users_preferences, and selects the email_type column for you, but if you bypass the users_spammable table, you will need to supply email_type in your query.