Storing address books and preferences in a database
===================================================

On sites with many users you might want to store your user data in a database 
instead of in files. This document describes how to configure NaSMail to do 
this.

Methods for storing address books and user preferences in a database are 
included as a part of the distribution.


Configuring PEAR DB
-------------------

For this to work you must have the PEAR classes installed, these are part of 
PHP. Once these are installed you must have sure the directory containg them is
a part of your PHP include path. See the PHP documentation for information on 
how to do that. Under Mandrake Linux the PEAR classes are installed as part of 
the php-devel package and under FreeBSD they are installed as part of the 
mod_php4 or php4 port/package. In Debian, you can install the php4-pear 
package. I'm afraid I have no information on other systems at the present time.


Configuring address books in database
-------------------------------------

First you need to create a database and a table to store the data in.
Create a database user with access to read and write in that table.

For MySQL you would normally do something like:

 (from the command line)
 # mysqladmin create nasmail

 (from the mysql client)
 mysql> GRANT select,insert,update,delete ON nasmail.* 
              TO nasmailuser@localhost IDENTIFIED BY 'somepassword';

The table structure should be similar to this (for MySQL):

   CREATE TABLE address (
     owner varchar(128) DEFAULT '' NOT NULL,
     nickname varchar(16) DEFAULT '' NOT NULL,
     firstname varchar(128) DEFAULT '' NOT NULL,
     lastname varchar(128) DEFAULT '' NOT NULL,
     email varchar(128) DEFAULT '' NOT NULL,
     label varchar(255),
     PRIMARY KEY (owner,nickname),
     KEY firstname (firstname,lastname)
   );

and similar to this for PostgreSQL:
CREATE TABLE "address" (
   "owner" varchar(128) NOT NULL,
   "nickname" varchar(16) NOT NULL,
   "firstname" varchar(128) NOT NULL,
   "lastname" varchar(128) NOT NULL,
   "email" varchar(128) NOT NULL,
   "label" varchar(255) NOT NULL,
   CONSTRAINT "address_pkey" PRIMARY KEY ("nickname", "owner")
);
CREATE  UNIQUE INDEX "address_firstname_key" ON "address"
   ("firstname", "lastname");


Next, edit your configuration so that the address book DSN (Data Source
Name) is specified, this can be done using either conf.pl or via the
administration plugin. The DSN should look something like:

 mysql://nasmailuser:somepassword@localhost/nasmail or
 pgsql://nasmailuser:somepassword@localhost/nasmail

From now on all users' personal address books will be stored in a database.

Global address book uses same table format as the one used for personal address
book. All global address book entries have owner field set to 'global'. You can
store personal and global address books in same table, if you don't have user 
named 'global'.


Configuring preferences in database
-----------------------------------

This is done in much the same way as it is for storing your address
books in a database.

The table structure should be similar to this (for MySQL):

  CREATE TABLE userprefs (
    user varchar(128) DEFAULT '' NOT NULL,
    prefkey varchar(64) DEFAULT '' NOT NULL,
    prefval BLOB DEFAULT '' NOT NULL,
    PRIMARY KEY (user,prefkey)
  );

and for PostgreSQL:
CREATE TABLE "userprefs" (
   "username" varchar(128) NOT NULL,
   "prefkey" varchar(64) NOT NULL,
   "prefval" text,
   CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
);

Next, edit your configuration so that the preferences DSN (Data Source
Name) is specified, this can be done using either conf.pl or via the
administration plugin. The DSN should look something like:

 mysql://nasmailuser:somepassword@localhost/nasmail or
 pgsql://nasmailuser:somepassword@localhost/nasmail

Note that when using the above PostgreSQL schema, you also need to change
the prefs_user_field variable in config.php from the default 'user' to
'username'.

From now on all users' personal preferences will be stored in a
database.

Default preferences can be set by altering the $default array in
db_prefs.php.
