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