doc/db-backend.txt

   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
   9 database instead of in files. This document describes how to configure
  10 SquirrelMail to do this.
  11
  12 Methods for storing both personal addressbooks and user preferences in
  13 a database is included as a part of the distribution.
  14
  15
  16
  17 Configuring PEAR DB
  18 -------------------
  19
  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. In Debian, you can install the
  27 php4-pear package. I'm afraid I have no information on
  28 other systems at the present time.
  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)
  43  mysql> GRANT select,insert,update,delete ON squirrelmail.*
  44               TO squirreluser@localhost IDENTIFIED BY 'sqpassword';
  45
  46 The table structure should be similar to this (for MySQL):
  47
  48    CREATE TABLE address (
  49      owner varchar(128) DEFAULT '' NOT NULL,
  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
  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
  72
  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:
  76
  77  mysql://squirreluser:sqpassword@localhost/squirrelmail or
  78  pgsql://squirreluser:sqpassword@localhost/squirrelmail
  79
  80 From now on all users' personal addressbooks will be stored in a
  81 database.
  82
  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'.
  86
  87 Configuring preferences in database
  88 -----------------------------------
  89
  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):
  94
  95   CREATE TABLE userprefs (
  96     user varchar(128) DEFAULT '' NOT NULL,
  97     prefkey varchar(64) DEFAULT '' NOT NULL,
  98     prefval BLOB DEFAULT '' NOT NULL,
  99     PRIMARY KEY (user,prefkey)
 100   );
 101
 102 and for PostgreSQL:
 103 CREATE TABLE "userprefs" (
 104    "username" varchar(128) NOT NULL,
 105    "prefkey" varchar(64) NOT NULL,
 106    "prefval" text,
 107    CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
 108 );
 109
 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
 114  mysql://squirreluser:sqpassword@localhost/squirrelmail or
 115  pgsql://squirreluser:sqpassword@localhost/squirrelmail
 116
 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
 121 From now on all users' personal preferences will be stored in a
 122 database.
 123
 124 Default preferences can be set by altering the $default array in
 125 db_prefs.php.
 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
 137 provided by Pear DB libraries are not accurate and some database
 138 backends don't support them. Since 1.5.1 SquirrelMail provides
 139 configuration options that set allowed field sizes.
 140
 141 If you see oversized field errors in your error logs - check your
 142 database structure. Issue can be solved by increasing database field
 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