--
-- A simple mail queue
--
-- @author <a href="mailto:eric@openforce.net">eric@openforce.net</a>
-- @version $Id: acs-mail-lite-create.sql,v 1.20.2.5 2020/12/14 09:31:11 antoniop Exp $
--
create sequence acs_mail_lite_id_seq;
CREATE TABLE acs_mail_lite_queue (
message_id integer
constraint acs_mail_lite_queue_pk
primary key,
creation_date text,
locking_server text,
to_addr text,
cc_addr text,
bcc_addr text,
from_addr text,
reply_to text,
subject text,
body text,
package_id integer
constraint amlq_package_id_fk
references apm_packages,
file_ids text,
filesystem_files text,
delete_filesystem_files_p boolean,
mime_type text,
object_id integer,
no_callback_p boolean,
extraheaders text,
use_sender_p boolean
);
create index acs_mail_lite_queue_package_id_idx on acs_mail_lite_queue(package_id);
create table acs_mail_lite_mail_log (
party_id integer
constraint amlml_party_id_fk
references parties (party_id)
on delete cascade
constraint acs_mail_lite_log_pk
primary key,
last_mail_date timestamptz default current_timestamp
);
create table acs_mail_lite_bounce (
party_id integer
constraint amlb_party_id_fk
references parties (party_id)
on delete cascade
constraint acs_mail_lite_bou_pk
primary key,
bounce_count integer default 1
);
create table acs_mail_lite_bounce_notif (
party_id integer
constraint amlbn_party_id_fk
references parties (party_id)
on delete cascade
constraint acs_mail_lite_bounce_notif_pk
primary key,
notification_time timestamptz default current_timestamp,
notification_count integer default 0
);
--
-- Add Incoming Mail Processing
--
create sequence acs_mail_lite_in_id_seq;
-- New tables
-- table tracking incoming email
create table acs_mail_lite_from_external (
aml_email_id integer primary key
not null
DEFAULT nextval ('acs_mail_lite_id_seq'),
-- Priority for processing incoming email in queue.
-- Lower number processed first.
priority integer,
-- using varchar instead of text for indexing
-- to and from email are defined according to headers.
-- See table acs_mail_lite_ie_headers
to_email_addrs varchar(1000),
from_email_addrs text,
subject text,
-- see acs_mail_lite_send_msg_id_map.msg_id
msg_id bigint,
-- used by prioritization calculations
-- For IMAP4 this is size defined by rfc822
size_chars numeric,
-- time email received from server in seconds since tcl epoch
received_cs bigint,
-- Answers question:
-- Has all ACS Mail Lite processes finished for this email?
-- Processes like parsing email, bounced email, input validation
processed_p boolean,
-- Answers question:
-- Have all callbacks related to this email finished processing?
-- Upon release, delete all components of aml_email_id also from
-- tables acs_mail_lite_ie_headers, acs_mail_lite_ie_body_parts, and
-- acs_mail_lite_ie_files.
-- Release essentially means its available to be deleted.
release_p boolean
);
create index acs_mail_lite_from_external_processed_p_idx
on acs_mail_lite_from_external (processed_p);
create index acs_mail_lite_from_external_release_p_idx
on acs_mail_lite_from_external (release_p);
-- Some services are offered between sessions of importing incoming email.
-- A unique ID provided by
-- acs_mail_lite_email_uid_id_map.uid_ext
-- is designed to
-- support UIDs for each email that are consistent between import sessions
-- from external source, such as specified by IMAP4 rfc3501
-- https://tools.ietf.org/html/rfc3501
-- It is also expected that each mailbox.host, mailbox and user are
-- consistent for duration of the service.
-- And yet, experience knows that sometimes email servers change
-- and UIDs for an email change with it.
-- Users switching email servers of an email account using a IMAP4 client
-- might hassle with moving email, but
-- in the process they generally know what is happening. They don't re-read
-- all the email.
-- We want to avoid this server re-reading and processing email
-- that has already been processed, when the UID of emails change.
-- The Questions become:
-- What scenarios might we run into?
-- Another user resetting flags.
-- A server migration or restore with some conflicting UIDs.
-- Can we recognize a change in server?
-- If so, can we signal ACS Mail Lite to ignore existing email
-- in a new environment?
-- Also, we should have a manual override to not ignore or ignore
-- in case of false positive and false negative triggers.
-- Can we recognize if another user accesses the same email account
-- and arbitrarily selects some prior messages to unread?
-- Yes. The purpose of acs_mail_lite_email_uid_id_map is to act as a log
-- of prior processed messages.
-- If total new messages is a significant percentage of all messages
-- and service has been working for a week or more,
-- use statistics to generate a reasonable trigger.
-- Weekly produce a revised distribution curve of weekly counts of incoming.
-- If percentile is over 200%.. ie twice the weekly maximum..
-- Also, test, for example, if new message count is less than
-- prior total, there's more of a chance that they are new messages;
-- Maybe check for one or two duplicates.
-- If new message count is over the total prior message count, flag a problem.
-- rfc3501 2.3.1.1. ..A client can only assume.. at the time
-- that it obtains the next unique identifier value.. that
-- messages arriving after that time will have a UID greater
-- than or equal to that value...
-- Can we recognize a change in server?
-- rfc3501 does not specify a unique server id
-- It specifies a unique id for a mailbox: UIDVALIDITY
-- UIDVALIDITY is optional, quite useful.
-- Rfc3501 specifies a unique id for each email: UID.
-- We can assign each email a more unique reference:
-- mailbox.host + mailbox.name + UIDVALIDITY (of mailbox) + UID.
-- We are more specific so that we detect more subtle cases of
-- server change, where checks by UID and UIDVALIDITY may not.
-- For example, when migrating email service and
-- and the new system initially restores the UIVALIDITY and message UID,
-- but references a different state of each email. The cause
-- of such cases are reasonable. For example, restoring
-- from backup to a new email host or restoring
-- before some batch event changed a bunch of things. So,
-- src_ext = mailbox.host + (user?) + mailbox.name + UIDVALIDITY
-- Leave user out for now..
-- Priority is to have a robust way to ignore
-- prior messages recognized as 'new' messages.
create table acs_mail_lite_email_uid_id_map (
-- unqique internal inbound email id
-- src_ext_id identifies source, but is redundant
-- for identifying a unique email.
aml_email_id integer not null,
--uisng varchar instead of text for indexing purposes
-- Each UID externally defined such as from imap4 server
uid_ext varchar(3000) not null,
-- Each external source may apply a different uid.
-- This is essentially an arbitrary constant frame reference between
-- connecting sessions with external server in most scenarios.
-- For IMAP4v1 rfc3501 2.3.1.1. item 4 ..combination of
-- mailbox.name, UIDVALIDITY, and UID must refer to a single
-- immutable message on that server forever.
-- default is:
-- ExternalSource parameter mailbox.name
-- and UIDVALIDITY with dash as delimiter
-- where ExternalSource parameter is
-- either blank or maybe mailbox.host for example.
-- external source reference id
-- see acs_mail_lite_email_src_ext_id_map.aml_src_id
src_ext_id integer
);
create index acs_mail_lite_email_uid_id_map_uid_ext_idx
on acs_mail_lite_email_uid_id_map (uid_ext);
create index acs_mail_lite_email_uid_id_map_src_ext_id_idx
on acs_mail_lite_email_uid_id_map (src_ext_id);
create table acs_mail_lite_email_src_ext_id_map (
aml_src_id integer not null,
src_ext varchar(1000) not null
);
-- Packages that are services, such as ACS Mail Lite, do not have a web UI.
-- Scheduled procs cannot read changes in values of package parameters
-- or get updates via web UI connections, or changes in tcl via apm.
-- Choices are updates via nsv variables and database value updates.
-- Choices via database have persistence across server restarts.
-- Defaults are set in acs_mail_lite::sched_parameters
-- These all are used in context of processing incoming email
-- unless stated otherwise.
-- Most specific flag takes precedence.
-- If an email is flagged high priority by package_id and
-- low priority by subject glob. It is assigned low priority.
-- Order of specificity.
-- medium default package_id party_id subject_id object_id
-- party_id can be group_id or user_id
-- If fast and low flag the same specificity for an email, low is chosen.
create table acs_mail_lite_ui (
-- scan_replies_est_dur_per_cycle_s_override see www/doc/analysis-notes
sredpcs_override integer,
-- Answers question: Reprocess old email?
reprocess_old_p boolean,
-- Max number of concurrent threads for high priority processing
max_concurrent integer,
-- Any incoming email body part over this size is stored in file
-- instead of database.
max_blob_chars integer,
-- Minimum threshold for default medium (standard) priority
mpri_min integer,
-- Maximum value for default medium (standard) priority
mpri_max integer,
--space delimited list of package_ids to process at fast/high priority
hpri_package_ids text,
--space delimited list of package_ids to process at low priority
lpri_package_ids text,
--space delimited list of party_ids to process at fast/high priority
hpri_party_ids text,
--space delimited list of party_ids to process at low priority
lpri_party_ids text,
-- a glob for searching subjects to flag for fast/high priority
hpri_subject_glob text,
-- a glob for searching subjects to flag for low priority
lpri_subject_glob text,
--space delimited list of object_ids to process at fast/high priority
hpri_object_ids text,
--space delimited list of object_ids to process at low priority
lpri_object_ids text,
--filters to reject input as early as possible in processing inbound
--Each filter is a name value in standard tcl list format
--where name is a header name
reject_on_hit text,
reject_on_miss text
);
-- This table has similar requirements to acs_mail_lite_ui
-- proc acs_mail_lite_imap_conn_* needs to be able to update values
-- within scheduled procs without restarting server.
-- Port is ignored. Added because it is a common requirement of connections
-- that might one day be useful here, too.
create table acs_mail_lite_imap_conn (
-- mailbox.host
ho text,
-- you guessed it
pa text,
-- port
po integer,
--timeout
ti integer,
-- user
us text,
-- mailbox.name See nsimap documentation for definition
na text,
-- space separated list of flags for imap related modifications
-- ssl means connect via ssl.
-- novalidatecert means accept a self-signed certificate
fl text
);
-- Following tables store parsed incoming email for processing by callbacks
-- defined in the rest of OpenACS
-- incoming email headers
-- There should be a size limit per unit time from each source
-- to prevent DDOS attacks and such (at least to the imap system).
--
create table acs_mail_lite_ie_headers (
-- incoming email
-- only includes headers useful in processing the queue
-- Such as
-- size
-- from
aml_email_id integer,
-- header name, one header per row
-- For all headers together, see acs_mail_lite_ie_parts.c_type=headers
-- Special case: h_name = struct means
-- h_value contains entire value returned from ns_imap struct
-- as a tcl list
h_name text,
h_value text
);
create index acs_mail_lite_ie_headers_aml_email_id_idx
on acs_mail_lite_ie_headers (aml_email_id);
-- incoming email body parts
-- including email file attachments and file content
-- A part may be a filename. The filename data model is added
-- to the parts table to reduce code complexity.
-- An attached or inline file is a kind of part.
create table acs_mail_lite_ie_parts (
aml_email_id integer,
section_id integer,
-- In addition to content_type, there is a special case:
-- headers, which contains all headers for email
-- content_type = c_type
c_type text,
-- If type has a filename, this is original filename.
filename text,
-- If c_type is multipart, content is blank. part_id is branched.
content text,
-- An alternate filepathname for large blob, or
-- A local absolute filepath location
c_filepathname text
);
create index acs_mail_lite_ie_parts_aml_email_id_idx
on acs_mail_lite_ie_parts (aml_email_id);
-- incoming email parts, name value pairs of
create table acs_mail_lite_ie_part_nv_pairs (
aml_email_id integer,
-- Usage is same as acs_mail_lite_ie_parts.section_id
section_id integer,
-- name value pair
p_name text,
p_value text
);
create index acs_mail_lite_ie_part_nv_pairs_aml_email_id_idx
on acs_mail_lite_ie_part_nv_pairs (aml_email_id);
create table acs_mail_lite_ie_section_ref_map (
-- 'Section' refers to usage with 'part' reference in 'ns_imap body'
-- Email parts can contain multiple parts.
-- Each multiple part can contain multiple parts.
-- Section_ref is an absolute reference of a part
-- including the parts it is contained in, and
-- delimited by period.
-- It is defined by:
-- ns_imap body #s msgno part
-- And yet, this reference system holds for any email
-- storage, so is adopted for generic use as well.
-- Default reference is value of 1.
-- A two part message has values 1 and 2.
-- Part 2 of a 3 part email (2/3) has reference '2'
-- If part 2 is also multiple parts, then
-- part 1 of part 2 of email has reference '2.1' and so on.
-- Mapping is constant for each case.
-- For example, '1.2.2.1' will always point to the same integer.
-- So do not alter values as they are likely used by
-- multiple emails.
section_ref varchar(300),
section_id integer
);
create index acs_mail_lite_ie_section_ref_map_section_ref_idx
on acs_mail_lite_ie_section_ref_map (section_ref);
create index acs_mail_lite_ie_section_ref_map_section_id_idx
on acs_mail_lite_ie_section_ref_map (section_id);
--
-- TODO: this table does not exists for Oracle.... Not needed there?
--
create table acs_mail_lite_send_msg_id_map (
-- a randomized number unique to this table
-- unique not null
msg_id text primary key,
package_id integer
constraint aml_package_id_fk
references apm_packages
on delete cascade,
party_id integer
constraint aml_from_external_party_id_fk
references parties (party_id)
on delete cascade,
object_id integer
constraint aml_from_external_obect_id_fk
references acs_objects (object_id)
on delete cascade,
-- Indicate approximate time when this email is created
datetime_cs integer,
-- other data or parameters to associate with email
other text
);
--
-- Speed up referential integrity
--
CREATE INDEX acs_mail_lite_send_msg_id_map_package_id_idx ON acs_mail_lite_send_msg_id_map(package_id);
CREATE INDEX acs_mail_lite_send_msg_id_map_party_id_idx ON acs_mail_lite_send_msg_id_map(party_id);
CREATE INDEX acs_mail_lite_send_msg_id_map_object_id_idx ON acs_mail_lite_send_msg_id_map(object_id);
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Mail Services Lite 5.5.1 : acs-mail-lite-create.sql
Begin main content
