The ListManager Tables

 

All the ListManager tables are described in the following sections. Tables are broken down by their general function, to help with organization.

 

Administrators

people_

lyrUIPrefs

 

Automatic actions

respond_

phrases_

 

Calendar

LyrEvents

 

Configuration

domainconnectionlimits_

dnsbypass_

bannedmembers_

lyrClusterMutexLock

lyrConfigClusterSettings

lyrClusterState

lyrConfigIPAddrs

lyrConfigNodeSettings

lyrDbConnections

lyrRelayHosts

lyrTimeZoneRules

sites_

lists_

topics_

lyrWACustomPackageParams

lyrWAPackageParamDefs

lyrWAPackages

lyrWAPackageSiteValues

lyrWASitePackages

 

Content

lyrMessageImages

lyrContentFields

lyrContentFieldParams

lyrContentFieldTypes

lyrImages

lyrImageFolders

mimeencodings_

mimecharsets_

messagetypes_

docs_

doctypes_

listdocs_

sitedocs_

docparts_

mimetypetoext_

mimetypes_

mimeexts_

 

Clicktracking

clickgroups_

clickstreamdata_

* urls_

clicktracking_

clicksummary_

lyrMailingLinks

 

Charting

usercolumninfo_

chartdescription_

lyrCampaigns

lyrCampaignParts

 

Deliverability

lyrDAODefinitions

lyrDeliverAttemptLog

lyrTopDomains

 

DomainKeys/DKIM

lyrDKSelectors

lyrSignableHeaders

lyrSiteDKDomains

lyrSiteHeaders

 

Logging

log_

lyrMemberBounceLog

lyrMemberComplaintLog

lyrMetricEvents

lyrPerformanceMetrics

lyrRecipientTracking

lyrWebAccessLog

 

MailStreams

lyrDAOMailStreamTracking

lyrMailStreams

lyrNodeAddresses

lyrStreamHasAddress

lyrTableFieldMappings

 

Mail Queue

inmail_

lyrActiveRecips

lyrCompletedRecips

lyrLookupCompletionStatus

lyrPendingDomains

lyrSpfLevelDescription

moderate_

outmail_

 

Members

members_

lyrMemberSubsets

 

Message Archives

listmessagesmapping_

messages_

uniquewords_

wordmessage_

 

Permission Groups

lyrPermissionGroups

lyrPermissionGuiAttribs

 

Reporting

lyrReportSummaryData 

lyrRecipientIPTracking

lyrUnsummarizedClicks

lyrUnsummarizedRecips

 

SQL queries

subsets_

 

Internationalization

translatekeys_

translatelang_

translatevalues_

 

Referrals

referrals_

messagerelationship_

 

Scheduled Tasks

lyrTaskScheduler

lyrTaskDescription

 

Sessions

lyrSessions

 

Split Tests

lyrSplitTests

lyrSplitTestMetricsRank

lyrSplitTestParts

 

Surveys

lyrSurveyResponseAnswers

lyrSurveyResponse

lyrSurveyQuestions

lyrSurveyAnswers

lyrSurveyAnswerCollections

 

Web Documents

lyrWebDocs

lyrLookupWebDocTypes

 

EmailAdvisor

* lyrPiperMail

lyrPiperSeeds

 

* 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.

 

Future direction