Mail Adapter

<< Click to Display Table of Contents >>

Navigation:  Integrating Blaze with Other Systems > Appendix A: Legacy Adapters and Agents >

Mail Adapter

Previous pageReturn to chapter overviewNext page

Note: This topic describes a legacy adapter. See the topics Reading from Other Sources and Writing to Other Destinations for current solutions.

The Mail Adapter enables the use of streaming data for creating email. Figure 1 illustrates its architecture in handling a stream as a source from which this adapter generates mail messages, sent to an SMTP server.

The mail adapter allows SQLstream to send internet mail. With this adapter, a SERVER represents an external mail server (an SMTP server), and a FOREIGN STREAM represents a series of mail messages. Each foreign stream is associated with a single mail server. Such a stream is always write-only, a data sink for outgoing mail.

Sending mail is the result of query execution. Mail is sent by INSERTing into the foreign stream, an output mail stream, either directly or by using a PUMP. The definition of a stream defines a correspondence between the stream columns and the segments (headers and body) of the mail message. Mail servers and mail streams are set up by executing foreign stream DDL.

streams_email_Mail_Adapter

The mail adapter is pre-installed with the server, as if declared and loaded by the following DDL statement:

CREATE OR REPLACE FOREIGN DATA WRAPPER "Mail"

  LIBRARY 'class com.sqlstream.plugin.mail.MailStreamControlPlugin'

  LANGUAGE JAVA

  DESCRIPTION 'Plugin to send e-mail via SMTP';

 

Here is an example of defining a mail server and mail streams:

-- define an output server

CREATE OR REPLACE SERVER "mailer"

  TYPE 'SMTP'

  FOREIGN DATA WRAPPER "Mail"

  OPTIONS

     ( HOST 'mail.speakeasy.net'

     , USERNAME 'Bob'

     , PASSWORD 'ick'

     , SENDER 'Bob.White@sqlstream.com'

  );

-- Define a stream; the SMTP protocol implies it is an output stream

-- Explicitly list the columns for message fields.

CREATE OR REPLACE FOREIGN STREAM "outbox"

 (subject VARCHAR(40)   OPTIONS (header 'subject'), -- this column is the Subject: header

  recipient VARCHAR(40) OPTIONS (header 'to'),      -- this column, is the To: header

  body VARCHAR(40)      OPTIONS (body ''))          -- this column is the message body

 SERVER "mailer";

 

(Note that SQL requires all items in an OPTIONS list to have two parts, an option name and an option value. The option BODY doesn't need a value, but we have to provide a null string as a dummy value.)

Thus rows in the stream can include columns to be used as the subject, from, and to entries in the email message, with the body content defined through query selections and combinations.

Seeing Mail as Stream Data

Inserting the contents of a table into a mail stream has the effect of sending a batch of mail messages. But inserting a data stream into a mail stream sets up an on-going series of mail messages that can continue into the future. This latter capability is useful for applications that monitor a process or activity, enabling them to provide tailored notifications of specific SQL-defined events to conditional classes of recipients.

The mail adapter is basically a protocol bridge. It links a SQLstream system to external mail servers, such as SMTP, and converts data between stream format and email format, as defined by the email message standard.

The mail format is assimilated to the relational framework by treating each mail message as equivalent to a streaming row.

A row corresponds to an email message.
The columns correspond to message elements: the headers and the body parts.
The whole body (when a single part) is a single column.
At present SQLstream supports only a single-part message body, not multipart-MIME and such.
To insert a row to an output stream is to send an email message.

Note that the adapter is not a message formatter. It expects its input stream to contain complete formatted message bodies. Building a message by filling in a template can be done by a token expander; or simply by SQL string concatenation.

dynamicMapping_of_streamRows_to_emailHeaders

Header Fields

Headers present some complexity. Some header fields are set automatically by the mailer (like Date). Some are constant across the whole stream (like From), while others (like Subject, To, CC) may vary from message to message. Some headers are necessary (like To) or at least expected (like Subject), while others are optional (like Reply-to). Some headers are standard, and some are arbitrary extensions (such as X-Mailer or X-example-header).

The user will generally be interested in the values of the usual headers (From, To, Subject, CC), but may want to set the value of any header at all. SQLstream uses relatively short and simple SQL to remain flexible while enabling this complexity to be hidden.

Mapping Message Elements to Columns

A user can specify a set of message elements, and bind them to columns of the stream.(Message elements means the message body and the header fields.) When sending mail, these are the elements written (plus the automatically generated headers like Date and Message-ID). When an element has the same value for all messages in an output stream, this default value can be specified once, in the CREATE STREAM statement. The column options in the CREATE STREAM statement define the mapping between columns and message elements.

oThe option HEADER designates a message header.
oThe mandatory argument is the header name.
oThe option BODY designates the message body.
oA multi-part body is not supported in this version of the adapter
oHence only one column can be mapped to the message body, and the body type is always text/plain.

When inserting a row, if a column is null, the corresponding element is omitted from the message written.

To be flexible, we allow two columns to map to the same HEADER field. This can mean different things depending on the kind of header:

Some header fields are naturally single-valued, such as From, Subject, Date, MessageID.
Some other headers, like To or CC, can have as their value a list of values (usually addresses).
Other headers can simply be repeated in a message.

When several column values are applied to the same HEADER:

The new value replaces the old value for a naturally single-valued header
New address values are appended to an address list header
Otherwise, several successive headers are written with the same header name.

Use Cases

Some example uses of this adapter:

Send an email alert whenever a data stream has an erroneous or extreme value.
Send a periodic email summary of some data stream values.
Generate bulk email such as confirmations.

Sending Mail

Sending mail involves two steps:

Defining an output mail server and a mail stream
Writing data to the mail stream

The mail stream and the mail server are both SQL/MED catalog entries.

The server object corresponds to an actual external server or service. The SQLstream object has attributes that correspond to standard attributes of the external service: this allows the external server to be configured and managed to some degree from SQLstream, by DDL statements. Note that several SQLstream s-Server objects can be mapped to the same SMTP server (with different attributes): the effect is to multiplex the outgoing mail.

An output stream object represents a series of messages with a common format and sender. The stream is write-only. An INSERT statement sends one message for each row inserted. Several streams can write to the same server, interleaving their output.

Error Handling

Sending mail can produce three kinds of errors:

1.A SQL statement can be invalid, preventing its execution.
oExamples include trying to read from a write-only stream, or providing a malformed IP address.
oThe statement fails without any results.
2.A SQL statement can be valid and execute, but the external server fails to send (or to fetch) a mail message.
oThe query continues.
oThis is a local error, synchronized with query execution, and relates to the current row.
oIn JDBC, the execution of a simple INSERT statement can indicate the error by throwing an exception.
oBut if the execution of the faster INSERT EXPEDITED fails, it is not possible to throw an exception.
3.The SQL statement executes, and the server sends mail, but some messages fail to get through to their destinations.
oThe query continues.
oThis is a distant error, not synchronized, and relates to some row sent in the past.
oIt can be reported, but must include extra information that identifies the underlying message.
oThis implies a requirement that each row in an output mail stream have a unique identifier.

The current server has no mechanism to indicate an asynchronous error, or an error from executing INSERT EXPEDITED. The most natural way to report such errors would be as items in an "error stream", but this is a feature for a future release. At present, such errors are logged in the server log with the level SEVERE.

SQL Syntax

Defining the SQLstream code for mail handling uses the following pattern:

-- Adapter and External Server:

-- install the adapter

CREATE OR REPLACE FOREIGN DATA WRAPPER adapter-name

 LIBRARY 'jar-path' LANGUAGE JAVA

 [OPTIONS (plugin options)];

-- define the external server

CREATE OR REPLACE SERVER server-name TYPE 'server-type'

  [VERSION version]

  FOREIGN DATA WRAPPER adapter-name

  OPTIONS (server options);

 

The server-type is always 'SMTP', and server options are smtp-host, smtp-port, timeout, user, password, etc.

-- Output Stream: -

-- define a stream: the server protocol implies it is an output stream.

CREATE OR REPLACE FOREIGN STREAM stream-name

   SERVER server-name (column-defs)

   OPTIONS (stream-options);

-- Send One Hand-made Email Message:

INSERT INTO stream-name VALUES (...);

Example

Given a stream of stock quotes (quotes), with the following columns:

(ticker VARCHAR, ts TIMESTAMP, price DECMAL(6,2), shares_traded INTEGER)

 

a query can detect when AT&T exceeds $5. This query will emit rows for all trades above that limit:

SELECT (ticker, ts, price) FROM quotes WHERE (ticker = 'T' AND price >= 5);

 

The code that follows sets up a mail stream and creates email alerts for such trades:

-- Define foreign data wrapper:

CREATE OR REPLACE FOREIGN DATA WRAPPER "Mail"

 LIBRARY 'class com.sqlstream.plugin.mail.MailStreamControlPlugin'

 LANGUAGE JAVA;

-- Connect to an SMTP server, giving username and password, and

-- predefining the default "From" header for all mail on this server:

CREATE OR REPLACE SERVER mailer

   TYPE 'SMTP'

   FOREIGN DATA WRAPPER "Mail"

   OPTIONS (HOST 'smtp.example.com',

            USERNAME 'ab', PASSWORD 'jellybean',

            SENDER 'Ambrose.Bierce@example.com');

-- Define a stream; the SMTP protocol implies it is an output stream

-- Explicitly list the columns for message fields-:

CREATE OR REPLACE FOREIGN STREAM outbox

   (subject VARCHAR(40)   OPTIONS (header 'subject'),

    recipient VARCHAR(40) OPTIONS (header 'to'),

    body VARCHAR(40)      OPTIONS (body ''))

   SERVER mailer;

-- Send mail:

INSERT INTO outbox (recipient, subject, body)

   SELECT ('mb',

           'Stock Alert',

           ticker || ' sold at ' || price || ' on ' || ts)

       FROM quotes WHERE (ticker = 'T' AND price >= 5);

Server Types

Table 1 lists the mail adapter server type values with a description.

Server Types

Value

Description

SMTP

connect to an SMTP server, to send mail. (also for STARTTLS)

SMTPS

connect to an SMTP server over SSL, to send mail

Server Options

Table 2 lists the server options for an output (SMTP) server.

Name

Type

Req'd

Value

Description

HOST

string

no

location of the SMTP server

a DNS name or an IP address (defaults to localhost)

PORT

int

no

SMTP serverport

defaults to the usual 25 for SMTP, 465 for SMTPS

TIMEOUT

int

no

timeout

timeout in milliseconds (defaults to infinite)

USERNAME

string

no

user name

for authentication

PASSWORD

string

no

user password

for authentication

SENDER

string

no

mail-sender (From: header)

Defaults to USERNAME; may be overridden by stream options or from column values

Table 3 lists stream options for an output (SMTP) server.

Name

Type

Req'd

Value

Description

SENDER

string

no

the default mail-sender (From: header)

overrides server value

CONTENT_TYPE

string

no

content type

default content type of the message body for the stream (defaults to text/plain)

KEY

string

no

a column name

the value of this column identifies the row linked to an error

SMTP Mail Stream Column Options

Table 4 lists column options for an output mail stream (that is, the server has TYPE = SMTP or SMTPS). As always, the columns are specified in the rowtype clause of CREATE FOREIGN STREAM. These column options are used to bind columns to message elements.

Name

Type

Req'd

Value

Description

HEADER

string

no

a mail header field name

Binds this column to given header.

BODY

string

no

attachment filename

the value of this column becomes a message body

CONTENT_TYPE

string

no

content type

the content type of the message body; overrides the stream default

Header names are not case sensitive. Standard headers are: FROM, SUBJECT, REPLY-TO, TO, CC, BCC. Any nonstandard header can be specified (or invented): for example, X-Mailer, X-Virus-Scanned, etc.

The following conditions must be met when specifying column options for a SMTP mail stream:

Each column must be specified as either a HEADER or a BODY, but not both.
At least one of the columns must be specified as a recipient header (i.e., HEADER with the values 'TO', 'CC', or 'BCC')
The following headers cannot be bound to more than one column: 'FROM', 'SUBJECT', 'REPLY-TO'.
Only one column can have the BODY option.

Configuration Issues

The mail adapter can communicate with an arbitrary number of external mail servers. Each mail server corresponds to (at least one) SQL/MED "server" as defined by a CREATE SERVER statement.

Some configuration parameters of an external server are exposed in the CREATE SERVER DDL statement.

Because the adapter connects to an SMTP server using the standard java package javax.mail, it is also controlled by certain java properties:

Property

Type

Description

mail.debug

boolean

True means debug javax.mail initialization itself.

plugin.mail.smtp.debug

boolean

True means log the actual SMTP dialog between the adapter and the SMTP servers.

plugin.mail.smtp.debug.file

string

Names the file that gets the SMTP debug output. Each mail server has its own debug file: the file name is the value of this property, suffixed by the server name, and ".log". However if this property is null, and plugin.mail.smtp.debug is true, the output all goes to stdout.