adding database field size checks (#1233721)

[squirrelmail.git] / doc / db-backend.txt

diff --git a/doc/db-backend.txt b/doc/db-backend.txt
index 4b3a6d94eff973803c34b1a6b86eea6a2164c849..3942b40c7452dddb5d1ef1a72897b5f25df7395f 100644 (file)
--- a/doc/db-backend.txt
+++ b/doc/db-backend.txt
@@ -6,21 +6,25 @@ Storing private addressbooks and preferences in a database
 
 
 On sites with many users you might want to store your user data in a
 
 
 On sites with many users you might want to store your user data in a

-database instead of in files. This document describe how to configure
+database instead of in files. This document describes how to configure

 SquirrelMail to do this.
 
 Methods for storing both personal addressbooks and user preferences in
 SquirrelMail to do this.
 
 Methods for storing both personal addressbooks and user preferences in

-a database is as a part of the distribution.
+a database is included as a part of the distribution.

 
 
 
 Configuring PEAR DB
 -------------------
 
 
 
 
 Configuring PEAR DB
 -------------------
 

-To work you must install the PEAR classes that is a part of PHP. Make
-sure the directory where the PEAR files are located is a part of your
-include path. See the PHP documentation for info on how to do that.
-
+For this to work you must have the PEAR classes installed, these are
+part of PHP. Once these are installed you must have sure the directory
+containg them is a part of your PHP include path. See the PHP
+documentation for information on how to do that.
+Under Mandrake Linux the PEAR classes are installed as part of the
+php-devel package and under FreeBSD they are installed as part of
+the mod_php4 or php4 port/package. I'm afraid I have no information on
+other systems at the present time.

 
 
 Configuring addressbooks in database
 
 
 Configuring addressbooks in database


@@ -35,13 +39,13 @@ For MySQL you would normally do something like:
  # mysqladmin create squirrelmail
 
  (from the mysql client)
  # mysqladmin create squirrelmail
 
  (from the mysql client)

- mysql> GRANT select,insert,update,delete ON squirrelmail.* 
-              TO squrreluser@localhost IDENTIFIED BY 'sqpassword';
+ mysql> GRANT select,insert,update,delete ON squirrelmail.*
+              TO squirreluser@localhost IDENTIFIED BY 'sqpassword';

 
 The table structure should be similar to this (for MySQL):
 
    CREATE TABLE address (
 
 The table structure should be similar to this (for MySQL):
 
    CREATE TABLE address (

-     owner varchar(16) DEFAULT '' NOT NULL,
+     owner varchar(128) DEFAULT '' NOT NULL,

      nickname varchar(16) DEFAULT '' NOT NULL,
      firstname varchar(128) DEFAULT '' NOT NULL,
      lastname varchar(128) DEFAULT '' NOT NULL,
      nickname varchar(16) DEFAULT '' NOT NULL,
      firstname varchar(128) DEFAULT '' NOT NULL,
      lastname varchar(128) DEFAULT '' NOT NULL,


@@ -51,34 +55,87 @@ The table structure should be similar to this (for MySQL):
      KEY firstname (firstname,lastname)
    );
 
      KEY firstname (firstname,lastname)
    );
 

+and similar to this for PostgreSQL:
+CREATE TABLE "address" (
+   "owner" varchar(128) NOT NULL,
+   "nickname" varchar(16) NOT NULL,
+   "firstname" varchar(128) NOT NULL,
+   "lastname" varchar(128) NOT NULL,
+   "email" varchar(128) NOT NULL,
+   "label" varchar(255) NOT NULL,
+   CONSTRAINT "address_pkey" PRIMARY KEY ("nickname", "owner")
+);
+CREATE  UNIQUE INDEX "address_firstname_key" ON "address"
+   ("firstname", "lastname");
+

 
 

-Next, edit config/config.php and add a DSN (Data Source Name) for the
-database. It should look something like:
+Next, edit your configuration so that the address book DSN (Data Source
+Name) is specified, this can be done using either conf.pl or via the
+administration plugin. The DSN should look something like:

 
 

- $addrbook_dsn = 'mysql://squirreluser:sqpassword@localhost/squirrelmail';
+ mysql://squirreluser:sqpassword@localhost/squirrelmail or
+ pgsql://squirreluser:sqpassword@localhost/squirrelmail

 
 From now on all users' personal addressbooks will be stored in a
 database.
 
 
 From now on all users' personal addressbooks will be stored in a
 database.
 

-
+Global address book uses same table format as the one used for personal
+address book. You can even use same table, if you don't have user named
+'global'.

 
 Configuring preferences in database
 -----------------------------------
 
 
 Configuring preferences in database
 -----------------------------------
 

-There is no easy way to do this yet. You will have to remove
-functions/prefs.php and replace it with functions/db_prefs.php. Then
-edit the new functions/prefs.php (db_prefs.php) and change the $DSN to
-point to a database you create (can be the same you use for
-addressbooks).  Create a table similar to this (for MySQL):
+This is done in much the same way as it is for storing your address
+books in a database.
+
+The table structure should be similar to this (for MySQL):

 
   CREATE TABLE userprefs (
 
   CREATE TABLE userprefs (

-    user varchar(32) DEFAULT '' NOT NULL,
+    user varchar(128) DEFAULT '' NOT NULL,

     prefkey varchar(64) DEFAULT '' NOT NULL,
     prefkey varchar(64) DEFAULT '' NOT NULL,

-    prefval blob DEFAULT '' NOT NULL,
+    prefval BLOB DEFAULT '' NOT NULL,

     PRIMARY KEY (user,prefkey)
   );
 
     PRIMARY KEY (user,prefkey)
   );
 

+and for PostgreSQL:
+CREATE TABLE "userprefs" (
+   "username" varchar(128) NOT NULL,
+   "prefkey" varchar(64) NOT NULL,
+   "prefval" text,
+   CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
+);
+
+Next, edit your configuration so that the preferences DSN (Data Source
+Name) is specified, this can be done using either conf.pl or via the
+administration plugin. The DSN should look something like:
+
+ mysql://squirreluser:sqpassword@localhost/squirrelmail or
+ pgsql://squirreluser:sqpassword@localhost/squirrelmail
+
+From now on all users' personal preferences will be stored in a
+database.

 
 Default preferences can be set by altering the $default array in
 
 Default preferences can be set by altering the $default array in

-prefs.php (db_prefs.php).
+db_prefs.php.
+
+Troubleshooting
+---------------
+1. Oversized field values. Preferences are not/can't be saved
+
+Database fields have size limits. Preference table example sets 128 
+character limit to owner field, 64 character limit to preference key 
+field and 64KB (database BLOB field size) limit to value field.
+
+If interface tries to insert data without checking field limits, it
+can cause data loss or database errors. Table information functions
+provided by Pear DB libraries are not accurate and some database 
+backends don't support them. Since 1.5.1 SquirrelMail provides
+configuration options that set allowed field sizes.
+
+If you see oversized field errors in your error logs - check your 
+database structure. Issue can be solved by increasing database field 
+sizes.

 
 

+If you want to get more debugging information - check setKey() function 
+in dbPrefs class. Class is stored in functions/db_prefs.php