All the ListManager tables are described in the following sections. Tables are broken down by their general function, to help with organization.
Administrators
Automatic actions
Configuration
Content
Clicktracking
* urls_
Charting
Deliverability
Logging
Mail Streams
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 ListManager 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 ListManager, 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 ListManager will drop the trailing underscores and will prefix all ListManager tables with 'lyr', like 'lyrWebDocs'.
Additional tables may be added to the database/tablespace holding the ListManager tables. As long as the names do not conflict there should be no problem with this.
ListManager will create the indexes it needs. Most tables have primary keys, and all tables that will benefit from indexes have them. Tables which have unique indexes on them rely on the uniqueness of the index, so they shouldn't be changed. Considerable effort is put towards finding the best mix of indexes for the different vendors and the different common usages of ListManager. While it may be possible to optimize the performance for reads for some tables by adding additional indexes, write performance will suffer. 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 ListManager. 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 ListManager.