Fix prefs so they work under 4.2 as well as 4.1.x. This really needs to
[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)
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
82
83
84Configuring preferences in database
85-----------------------------------
86
3499f99f 87This is done in much the same way as it is for storing your address
88books in a database.
89
90The table structure should be similar to this (for MySQL):
6ff1c690 91
92 CREATE TABLE userprefs (
1e63b430 93 user varchar(128) DEFAULT '' NOT NULL,
6ff1c690 94 prefkey varchar(64) DEFAULT '' NOT NULL,
1e63b430 95 prefval BLOB DEFAULT '' NOT NULL,
6ff1c690 96 PRIMARY KEY (user,prefkey)
97 );
98
7ac2b372 99and for PostgreSQL:
100CREATE TABLE "userprefs" (
98749983 101 "username" varchar(128) NOT NULL,
7ac2b372 102 "prefkey" varchar(64) NOT NULL,
103 "prefval" text,
98749983 104 CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
7ac2b372 105);
106
3499f99f 107Next, edit your configuration so that the preferences DSN (Data Source
108Name) is specified, this can be done using either conf.pl or via the
109administration plugin. The DSN should look something like:
110
98749983 111 mysql://squirreluser:sqpassword@localhost/squirrelmail or
112 pgsql://squirreluser:sqpassword@localhost/squirrelmail
3499f99f 113
114From now on all users' personal preferences will be stored in a
115database.
6ff1c690 116
117Default preferences can be set by altering the $default array in
3499f99f 118db_prefs.php.