The ListManager Tables
All the Aurea List Manager tables are described in the following sections. Tables are broken down by their general function, to help with organization.
Administrators
Automatic actions
Calendar
Configuration
Content
Clicktracking
* urls_
Charting
Deliverability
DomainKeys/DKIM
Logging
MailStreams
Mail Queue
Members
Message Archives
Permission Groups
Reporting
SQL queries
Internationalization
Referrals
Scheduled Tasks
Sessions
Split Tests
Surveys
Web Documents
EmailAdvisor
lyrPiperMail tables are generally defined using Microsoft SQL Server keywords, although these tables
work across all Aurea List Manager supported databases. The type BOOL listed in this document maps to a char(1)
with a check constraint in all platforms.
Here is a mapping of MSSQL keywords and their equivalent for other vendors, where the same type is not used for all vendors:
| MSSQL column | MSSQL description | Used for Oracle | Oracle description | ||
|---|---|---|---|---|---|
| tinyint |
A one byte value |
int |
A four byte value |
|
|
| smallint |
A two byte value |
int |
A four byte value |
|
|
| int |
A four byte value |
int |
A four byte value |
|
|
| bigint |
An eight byte value |
numeric(22, 0) |
Up to 22 digits of precision |
|
|
| numeric |
A numeric value |
numeric |
A numeric value |
|
|
| smalldatetime |
Time and date with accuracy to one minute |
date |
An 8-byte value with second precision |
|
|
| datetime |
Time and date with subsecond accuracy |
date |
An 8-byte value with second precision |
|
|
| text |
An unbound character column |
clob |
An unbound character column |
Microsoft SQL Server supports a data column modifier called an "Identity" which can be added to numeric fields. This is an auto-numbering field that automatically populates the next incrementing value.
Oracle implements this with a separate "sequence" table. For Aurea List Manager, a sequence table is created with the base table name and "_seq", so "docs_seq", for example. With Oracle, the next value is obtained from this sequence table via a 'before' trigger.
Previous versions of table and column names all ended with a trailing underscore, like 'lists_'. This was the legacy method to help avoid naming conflicts with other tables or objects in the database. Future versions of Aurea List Manager drops the trailing underscores and prefixs all Aurea List Manager tables with 'lyr', like 'lyrWebDocs'.
Additional tables may be added to the database/tablespace holding the Aurea List Manager tables. As long as the names do not conflict there should be no problem with this.
Aurea List Manager creates the indexes it needs. Most tables have primary keys, and all tables that benefits from indexes have them. Tables which have unique indexes on them rely on the uniqueness of the index, so they should not be changed.
Considerable effort is put towards finding the best mix of indexes for the different vendors and the different common usages of Aurea List Manager.
It may be possible to optimize the performance for reads for some tables by adding additional indexes but this can affect write performance adversely. Users are free to add indexes if desired, but any changes to the structure of the tables may make future upgrades impossible.
The tables involved in mail sending (the mail queue) are the most active, for the common use of Aurea List Manager. These include lyrActiveRecips, lyrCompletedRecips, and inmail_ and outmail_. If message searching is enabled the messages_table will store a copy of the message and will therefore grow and be somewhat active. Along with messages_, there are two tables that enable archives to be searched: wordmessage_and uniquewords_. The members_table is also one of the largest tables in Aurea List Manager.