MailNews:Address Book SQLite design

This page is meant for planning out a redesign of the Address Book to use SQLite instead of MDB. See bug 11050 and bug 382876 for other details.

Requirements

These requirements are intended to be an aid to forming the schema for the SQLite and the implementation of it. For initial implementations, not all of the requirements need to be satisfied, but for a complete switch-over to SQLite as the default book, then they should all be satisfied.

Note, these are WIP and haven't been discussed at this time --Standard8 01:41, 24 November 2007 (PST)

1. The new AB/format shall supports all current address book fields
2. It shall be capable of being extended to support extra fields
   • If possible this will be in a backwards-compatible fashion
3. Mailing lists shall be supported
4. Nested lists shall be capable of being supported
   • Preferably the implementation shall support nested lists, however it is not a requirement that this is fully implemented in the address book before switching over to SQLite as the default book.
5. All functions shall be able to be accessed via nsIAbDirectory, without reference to the back end directory or the specific nsIAb*Directory
   • The only exception to this rule is for functions which really are address book specific, e.g. authDN in nsIAbLDAPDirectory.

Proposed schema

CREATE TABLE Cards (
  CardKey INT NOT NULL PRIMARY KEY AUTOINCREMENT,
  firstName CHAR,
  lastName CHAR,
  displayName CHAR,
  nickName CHAR,
  primaryEmail CHAR,
  secondaryEmail CHAR,
  popularityIndex INT NOT NULL,
  preferMailFormat INT NOT NULL,
  lastModifiedDate INT NOT NULL
);
CREATE TABLE CardProperties (
  CardKey INT NOT NULL,
  Property CHAR NOT NULL,
  Value CHAR
);
CREATE TABLE Directories (
  DirKey INT NOT NULL PRIMARY KEY AUTOINCREMENT,
  Flags INT NOT NULL, -- What flags might need to exist? Directory type?
  Name CHAR,
  Description CHAR
);
CREATE TABLE DirectoryMap (
  DirKey INT NOT NULL,
  CardKey INT NOT NULL
);
CREATE TABLE HierarchyMap {
  ParentDir INT NOT NULL,
  ChildDir INT NOT NULL
);