Merge of Steve Brown's patch
[squirrelmail.git] / doc / db-backend.txt
CommitLineData
6ff1c690 1$Id$
2
3
4Storing private addressbooks and preferences in a database
5==========================================================
6
7
8On sites with many users you might want to store your user data in a
1e63b430 9database instead of in files. This document describes how to configure
6ff1c690 10SquirrelMail to do this.
11
12Methods for storing both personal addressbooks and user preferences in
1e63b430 13a database is included as a part of the distribution.
6ff1c690 14
15
16
17Configuring PEAR DB
18-------------------
19
4dccdc01 20For this to work you must have the PEAR classes installed, these are
21part of PHP. Once these are installed you must have sure the directory
22containg them is a part of your PHP include path. See the PHP
23documentation for information on how to do that.
24Under Mandrake Linux the PEAR classes are installed as part of the
25php-devel package and under FreeBSD they are installed as part of
26the mod_php4 or php4 port/package. I'm afraid I have no information on
27other systems at the present time.
6ff1c690 28
29
30Configuring addressbooks in database
31------------------------------------
32
33First you need to create a database and a table to store the data in.
34Create a database user with access to read and write in that table.
35
36For MySQL you would normally do something like:
37
38 (from the command line)
39 # mysqladmin create squirrelmail
40
41 (from the mysql client)
598294a7 42 mysql> GRANT select,insert,update,delete ON squirrelmail.*
588f1ca4 43 TO squirreluser@localhost IDENTIFIED BY 'sqpassword';
6ff1c690 44
45The table structure should be similar to this (for MySQL):
46
47 CREATE TABLE address (
1e63b430 48 owner varchar(128) DEFAULT '' NOT NULL,
6ff1c690 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,
53 label varchar(255),
54 PRIMARY KEY (owner,nickname),
55 KEY firstname (firstname,lastname)
56 );
57
7ac2b372 58and similar to this for PostgreSQL:
59CREATE 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")
67);
68CREATE UNIQUE INDEX "address_firstname_key" ON "address"
69 ("firstname", "lastname");
70
6ff1c690 71
4f40a59d 72Next, edit your configuration so that the address book DSN (Data Source
73Name) is specified, this can be done using either conf.pl or via the
74administration plugin. The DSN should look something like:
6ff1c690 75
98749983 76 mysql://squirreluser:sqpassword@localhost/squirrelmail or
77 pgsql://squirreluser:sqpassword@localhost/squirrelmail
6ff1c690 78
79From now on all users' personal addressbooks will be stored in a
80database.
81
6e00b127 82Global address book uses same table format as the one used for personal
83address book. You can even use same table, if you don't have user named
84'global'.
6ff1c690 85
86Configuring preferences in database
87-----------------------------------
88
3499f99f 89This is done in much the same way as it is for storing your address
90books in a database.
91
92The table structure should be similar to this (for MySQL):
6ff1c690 93
94 CREATE TABLE userprefs (
1e63b430 95 user varchar(128) DEFAULT '' NOT NULL,
6ff1c690 96 prefkey varchar(64) DEFAULT '' NOT NULL,
1e63b430 97 prefval BLOB DEFAULT '' NOT NULL,
6ff1c690 98 PRIMARY KEY (user,prefkey)
99 );
100
7ac2b372 101and for PostgreSQL:
102CREATE TABLE "userprefs" (
98749983 103 "username" varchar(128) NOT NULL,
7ac2b372 104 "prefkey" varchar(64) NOT NULL,
105 "prefval" text,
98749983 106 CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
7ac2b372 107);
108
3499f99f 109Next, edit your configuration so that the preferences DSN (Data Source
110Name) is specified, this can be done using either conf.pl or via the
111administration plugin. The DSN should look something like:
112
98749983 113 mysql://squirreluser:sqpassword@localhost/squirrelmail or
114 pgsql://squirreluser:sqpassword@localhost/squirrelmail
3499f99f 115
116From now on all users' personal preferences will be stored in a
117database.
6ff1c690 118
119Default preferences can be set by altering the $default array in
3499f99f 120db_prefs.php.
06316c07 121
122Troubleshooting
123---------------
1241. Oversized field values. Preferences are not/can't be saved
125
126Database fields have size limits. Preference table example sets 128
127character limit to owner field, 64 character limit to preference key
128field and 64KB (database BLOB field size) limit to value field.
129
130If interface tries to insert data without checking field limits, it
131can cause data loss or database errors. Table information functions
132provided by Pear DB libraries are not accurate and some database
133backends don't support them. Since 1.5.1 SquirrelMail provides
134configuration options that set allowed field sizes.
135
136If you see oversized field errors in your error logs - check your
137database structure. Issue can be solved by increasing database field
138sizes.
139
140If you want to get more debugging information - check setKey() function
141in dbPrefs class. Class is stored in functions/db_prefs.php