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
# mysqladmin create squirrelmail
(from the mysql client)
- mysql> GRANT select,insert,update,delete ON squirrelmail.*
- TO squrreluser@localhost IDENTIFIED BY 'sqpassword';
+ mysql> GRANT select,insert,update,delete ON squirrelmail.*
+ 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.
-
+Global address book uses same table format as the one used for personal
+address book. You can even use same table, if you don't have user named
+'global'.
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")
+);
+
+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
-prefs.php (db_prefs.php).
+db_prefs.php.
+
+Troubleshooting
+---------------
+1. Oversized field values. Preferences are not/can't be saved
+
+Database fields have size limits. Preference table example sets 128
+character limit to owner field, 64 character limit to preference key
+field and 64KB (database BLOB field size) limit to value field.
+
+If interface tries to insert data without checking field limits, it
+can cause data loss or database errors. Table information functions
+provided by Pear DB libraries are not accurate and some database
+backends don't support them. Since 1.5.1 SquirrelMail provides
+configuration options that set allowed field sizes.
+
+If you see oversized field errors in your error logs - check your
+database structure. Issue can be solved by increasing database field
+sizes.
+If you want to get more debugging information - check setKey() function
+in dbPrefs class. Class is stored in functions/db_prefs.php
projects / squirrelmail.git / blobdiff