4 Storing private addressbooks and preferences in a database
5 ==========================================================
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.
12 Methods for storing both personal addressbooks and user preferences in
13 a database is included as a part of the distribution.
20 For this to work you must have the PEAR classes installed, these are
21 part of PHP. Once these are installed you must have sure the directory
22 containg them is a part of your PHP include path. See the PHP
23 documentation for information on how to do that.
24 Under Mandrake Linux the PEAR classes are installed as part of the
25 php-devel package and under FreeBSD they are installed as part of
26 the mod_php4 or php4 port/package. I'm afraid I have no information on
27 other systems at the present time.
30 Configuring addressbooks in database
31 ------------------------------------
33 First you need to create a database and a table to store the data in.
34 Create a database user with access to read and write in that table.
36 For MySQL you would normally do something like:
38 (from the command line)
39 # mysqladmin create squirrelmail
41 (from the mysql client)
42 mysql> GRANT select,insert,update,delete ON squirrelmail.*
43 TO squirreluser@localhost IDENTIFIED BY 'sqpassword';
45 The table structure should be similar to this (for MySQL):
47 CREATE TABLE address (
48 owner varchar(128) DEFAULT '' NOT NULL,
49 nickname varchar(16) DEFAULT '' NOT NULL,
50 firstname varchar(128) DEFAULT '' NOT NULL,
51 lastname varchar(128) DEFAULT '' NOT NULL,
52 email varchar(128) DEFAULT '' NOT NULL,
54 PRIMARY KEY (owner,nickname),
55 KEY firstname (firstname,lastname)
58 and similar to this for PostgreSQL:
59 CREATE TABLE "address" (
60 "owner" varchar(128) NOT NULL,
61 "nickname" varchar(16) NOT NULL,
62 "firstname" varchar(128) NOT NULL,
63 "lastname" varchar(128) NOT NULL,
64 "email" varchar(128) NOT NULL,
65 "label" varchar(255) NOT NULL,
66 CONSTRAINT "address_pkey" PRIMARY KEY ("nickname", "owner")
68 CREATE UNIQUE INDEX "address_firstname_key" ON "address"
69 ("firstname", "lastname");
72 Next, edit your configuration so that the address book DSN (Data Source
73 Name) is specified, this can be done using either conf.pl or via the
74 administration plugin. The DSN should look something like:
76 mysql://squirreluser:sqpassword@localhost/squirrelmail or
77 pgsql://squirreluser:sqpassword@localhost/squirrelmail
79 From now on all users' personal addressbooks will be stored in a
84 Configuring preferences in database
85 -----------------------------------
87 This is done in much the same way as it is for storing your address
90 The table structure should be similar to this (for MySQL):
92 CREATE TABLE userprefs (
93 user varchar(128) DEFAULT '' NOT NULL,
94 prefkey varchar(64) DEFAULT '' NOT NULL,
95 prefval BLOB DEFAULT '' NOT NULL,
96 PRIMARY KEY (user,prefkey)
100 CREATE TABLE "userprefs" (
101 "username" varchar(128) NOT NULL,
102 "prefkey" varchar(64) NOT NULL,
104 CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
107 Next, edit your configuration so that the preferences DSN (Data Source
108 Name) is specified, this can be done using either conf.pl or via the
109 administration plugin. The DSN should look something like:
111 mysql://squirreluser:sqpassword@localhost/squirrelmail or
112 pgsql://squirreluser:sqpassword@localhost/squirrelmail
114 From now on all users' personal preferences will be stored in a
117 Default preferences can be set by altering the $default array in