On sites with many users you might want to store your user data in a
-database instead of in files. This document describe how to configure
+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 as a part of the distribution.
+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.
-
+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. I'm afraid I have no information on
+other systems at the present time.
Configuring addressbooks in database
(from the mysql client)
mysql> GRANT select,insert,update,delete ON squirrelmail.*
- TO squrreluser@localhost IDENTIFIED BY 'sqpassword';
+ TO squirreluser@localhost IDENTIFIED BY 'sqpassword';
The table structure should be similar to this (for MySQL):
CREATE TABLE address (
- owner varchar(16) DEFAULT '' NOT NULL,
+ owner varchar(128) DEFAULT '' NOT NULL,
nickname varchar(16) DEFAULT '' NOT NULL,
firstname varchar(128) DEFAULT '' NOT NULL,
lastname varchar(128) DEFAULT '' NOT NULL,
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 config/config.php and add a DSN (Data Source Name) for the
-database. It should look something like:
+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:
- $addrbook_dsn = 'mysql://squirreluser:sqpassword@localhost/squirrelmail';
+ mysql://squirreluser:sqpassword@localhost/squirrelmail or
+ pgsql://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):
+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(32) DEFAULT '' NOT NULL,
+ user varchar(128) DEFAULT '' NOT NULL,
prefkey varchar(64) DEFAULT '' NOT NULL,
- prefval blob 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")
+);
-Default preferences can be set by altering the $default array in
-prefs.php (db_prefs.php).
+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://squirreluser:sqpassword@localhost/squirrelmail or
+ pgsql://squirreluser:sqpassword@localhost/squirrelmail
+
+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.