About ListManager tables

* lyrPiperMail tables are generally defined using Microsoft SQL Server keywords, athough 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	Used for PostgreSQL	PostgreSQL description
tinyint	A one byte value	int	A four byte value	int2	A two byte value
smallint	A two byte value	int	A four byte value	int2	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	number(22, 0) *	Up to 22 digits of precision
numeric	A numeric value	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	timestamp	An 8-byte value with microsecond accuracy
datetime	Time and date with subsecond accuracy	date	An 8-byte value with second precision	timestamp	An 8-byte value with microsecond accuracy
text	An unbound character column	clob	An unbound character column	text	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. Both Oracle and PostgreSQL implement 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 PostgreSQL the next value is obtained from this sequence table via a default rule, where Oracle requires 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 seaching 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