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 |
26 | the mod_php4 or php4 port/package. I'm afraid I have no information on |
27 | other systems at the present time. |
6ff1c690 |
28 | |
29 | |
30 | Configuring addressbooks in database |
31 | ------------------------------------ |
32 | |
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. |
35 | |
36 | For 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 | |
45 | The 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 |
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") |
67 | ); |
68 | CREATE UNIQUE INDEX "address_firstname_key" ON "address" |
69 | ("firstname", "lastname"); |
70 | |
6ff1c690 |
71 | |
4f40a59d |
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: |
6ff1c690 |
75 | |
98749983 |
76 | mysql://squirreluser:sqpassword@localhost/squirrelmail or |
77 | pgsql://squirreluser:sqpassword@localhost/squirrelmail |
6ff1c690 |
78 | |
79 | From now on all users' personal addressbooks will be stored in a |
80 | database. |
81 | |
6e00b127 |
82 | Global address book uses same table format as the one used for personal |
83 | address book. You can even use same table, if you don't have user named |
84 | 'global'. |
6ff1c690 |
85 | |
86 | Configuring preferences in database |
87 | ----------------------------------- |
88 | |
3499f99f |
89 | This is done in much the same way as it is for storing your address |
90 | books in a database. |
91 | |
92 | The 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 |
101 | and for PostgreSQL: |
102 | CREATE 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 |
109 | Next, edit your configuration so that the preferences DSN (Data Source |
110 | Name) is specified, this can be done using either conf.pl or via the |
111 | administration plugin. The DSN should look something like: |
112 | |
98749983 |
113 | mysql://squirreluser:sqpassword@localhost/squirrelmail or |
114 | pgsql://squirreluser:sqpassword@localhost/squirrelmail |
3499f99f |
115 | |
116 | From now on all users' personal preferences will be stored in a |
117 | database. |
6ff1c690 |
118 | |
119 | Default preferences can be set by altering the $default array in |
3499f99f |
120 | db_prefs.php. |