[squirrelmail.git] / doc / db-backend.txt

$Id$


Storing private addressbooks 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
SquirrelMail to do this.

Methods for storing both personal addressbooks and user preferences in
a database is included as a part of the distribution.


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

To work you must install the PEAR classes that is a part of PHP. Make
sure the directory where the PEAR files are located is a part of your
include path. See the PHP documentation for info on how to do that.


Configuring addressbooks 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 squirrelmail

 (from the mysql client)
 mysql> GRANT select,insert,update,delete ON squirrelmail.* 
              TO squrreluser@localhost IDENTIFIED BY 'sqpassword';

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)
   );


Next, edit config/config.php and add a DSN (Data Source Name) for the
database. It should look something like:

 $addrbook_dsn = 'mysql://squirreluser:sqpassword@localhost/squirrelmail';

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


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

There is no easy way to do this yet. You will have to remove
functions/prefs.php and replace it with functions/db_prefs.php. Then
edit the new functions/prefs.php (db_prefs.php) and change the $DSN to
point to a database you create (can be the same you use for
addressbooks).  Create a table 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)
  );


Default preferences can be set by altering the $default array in
prefs.php (db_prefs.php).
Commit	Line	Data
	1	$Id$
	2
	3
	4	Storing private addressbooks and preferences in a database
	5	==========================================================
	6
	7
	8	On sites with many users you might want to store your user data in a
	9	database instead of in files. This document describes how to configure
	10	SquirrelMail to do this.
	11
	12	Methods for storing both personal addressbooks and user preferences in
	13	a database is included as a part of the distribution.
	14
	15
	16
	17	Configuring PEAR DB
	18	-------------------
	19
	20	To work you must install the PEAR classes that is a part of PHP. Make
	21	sure the directory where the PEAR files are located is a part of your
	22	include path. See the PHP documentation for info on how to do that.
	23
	24
	25
	26	Configuring addressbooks in database
	27	------------------------------------
	28
	29	First you need to create a database and a table to store the data in.
	30	Create a database user with access to read and write in that table.
	31
	32	For MySQL you would normally do something like:
	33
	34	(from the command line)
	35	# mysqladmin create squirrelmail
	36
	37	(from the mysql client)
	38	mysql> GRANT select,insert,update,delete ON squirrelmail.*
	39	TO squrreluser@localhost IDENTIFIED BY 'sqpassword';
	40
	41	The table structure should be similar to this (for MySQL):
	42
	43	CREATE TABLE address (
	44	owner varchar(128) DEFAULT '' NOT NULL,
	45	nickname varchar(16) DEFAULT '' NOT NULL,
	46	firstname varchar(128) DEFAULT '' NOT NULL,
	47	lastname varchar(128) DEFAULT '' NOT NULL,
	48	email varchar(128) DEFAULT '' NOT NULL,
	49	label varchar(255),
	50	PRIMARY KEY (owner,nickname),
	51	KEY firstname (firstname,lastname)
	52	);
	53
	54
	55	Next, edit config/config.php and add a DSN (Data Source Name) for the
	56	database. It should look something like:
	57
	58	$addrbook_dsn = 'mysql://squirreluser:sqpassword@localhost/squirrelmail';
	59
	60	From now on all users' personal addressbooks will be stored in a
	61	database.
	62
	63
	64
	65	Configuring preferences in database
	66	-----------------------------------
	67
	68	There is no easy way to do this yet. You will have to remove
	69	functions/prefs.php and replace it with functions/db_prefs.php. Then
	70	edit the new functions/prefs.php (db_prefs.php) and change the $DSN to
	71	point to a database you create (can be the same you use for
	72	addressbooks). Create a table similar to this (for MySQL):
	73
	74	CREATE TABLE userprefs (
	75	user varchar(128) DEFAULT '' NOT NULL,
	76	prefkey varchar(64) DEFAULT '' NOT NULL,
	77	prefval BLOB DEFAULT '' NOT NULL,
	78	PRIMARY KEY (user,prefkey)
	79	);
	80
	81
	82	Default preferences can be set by altering the $default array in
	83	prefs.php (db_prefs.php).
	84