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.
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 Postgres |
Postgres 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 |
numeric(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 subsecond precision |
timestamp |
An 8-byte value with microsecond accuracy |
|
datetime |
Time and date with subsecond accuracy |
date |
An 8-byte value with subsecond precision |
timestamp |
An 8-byte value with microsecond accuracy |
|
text |
An unbound character column |
clob |
An unbound character column |
text |
An unbound character column |
Additionally, 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 Postgres 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 Postgres the next value is obtained from this sequence table via a default rule, where Oracle requires a 'before' trigger.
Note that the table and column names all end with a trailing underbar, like "lists_". This is to help avoid naming conflicts with other tables or objects in the database. The trailing underbar must be used for any direct SQL manipulation.
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.
More
Administrators
1. People_
Automatic Actioins
2. Respond_
3. Phrases_
Billing
4. Log_
Configuration
5. Config_
6. Site_
7. Lists_
8. Topics_
Content
9. Docs_
10. Doctypes_
11. Docparts_
12. Mimetypetoext_
13. Mimetypes_
14. Mimeexts_
Mail Queues
15. Inmail_
16. Moderate_
17. Outmail_
Member Information
18. Members_
Message Archives
19. Messages_
20. Uniquewords_
21. Wordmessage_
SQL Queries
22. Subsets_
|
|