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) |
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 | |
82 | |
83 | |
84 | Configuring preferences in database |
85 | ----------------------------------- |
86 | |
3499f99f |
87 | This is done in much the same way as it is for storing your address |
88 | books in a database. |
89 | |
90 | The 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 |
99 | and for PostgreSQL: |
100 | CREATE 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 |
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: |
110 | |
98749983 |
111 | mysql://squirreluser:sqpassword@localhost/squirrelmail or |
112 | pgsql://squirreluser:sqpassword@localhost/squirrelmail |
3499f99f |
113 | |
114 | From now on all users' personal preferences will be stored in a |
115 | database. |
6ff1c690 |
116 | |
117 | Default preferences can be set by altering the $default array in |
3499f99f |
118 | db_prefs.php. |