PostgreSQL should now work as a db prefs backend (and fix alterable field names)

git-svn-id: https://svn.code.sf.net/p/squirrelmail/code/trunk/squirrelmail@2539 7612ce4b-ef26-0410-bec9-ea0150e637f0

diff --git a/doc/db-backend.txt b/doc/db-backend.txt
index 917a4b3796ebe558f1337cb0b077f77e13dfeb4c..10774509d8b95fcca5009015ada1cb15b2d4ac26 100644 (file)
--- a/doc/db-backend.txt
+++ b/doc/db-backend.txt
@@ -73,7 +73,8 @@ 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:
 
 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
+ 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.


@@ -97,25 +98,21 @@ The table structure should be similar to this (for MySQL):
 
 and for PostgreSQL:
 CREATE TABLE "userprefs" (
 
 and for PostgreSQL:
 CREATE TABLE "userprefs" (

-   "user" varchar(128) NOT NULL,
+   "username" varchar(128) NOT NULL,

    "prefkey" varchar(64) NOT NULL,
    "prefval" text,
    "prefkey" varchar(64) NOT NULL,
    "prefval" text,

-   CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "user")
+   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:
 
 );
 
 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
+ 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
 db_prefs.php.
 
 From now on all users' personal preferences will be stored in a
 database.
 
 Default preferences can be set by altering the $default array in
 db_prefs.php.

-
-NB It seems that currently database backed preferences won't work under
-PostgreSQL at least due to using a column name of user and REPLACE which
-doesn't exist under PostgreSQL. This will be worked on and fixed as soon
-as possible.

diff --git a/functions/db_prefs.php b/functions/db_prefs.php
index c11b2c5983aeaf01696b637ae93ffea63c9cace2..40c0b7c5c3a27f63c3ff9f30b0e9268ae67a04ab 100644 (file)
--- a/functions/db_prefs.php
+++ b/functions/db_prefs.php
@@ -32,6 +32,10 @@
  * $Id$
  */
 
  * $Id$
  */
 

+define('SMDB_UNKNOWN', 0);
+define('SMDB_MYSQL', 1);
+define('SMDB_PGSQL', 2);
+

 require_once('DB.php');
 require_once('../config/config.php');
 
 require_once('DB.php');
 require_once('../config/config.php');
 


@@ -75,17 +79,25 @@ class dbPrefs {
 
     var $dbh   = NULL;
     var $error = NULL;
 
     var $dbh   = NULL;
     var $error = NULL;

+    var $db_type = SMDB_UNKNOWN;

 
     var $default = Array('chosen_theme' => '../themes/default_theme.php',
                          'show_html_default' => '0');
 
     function open() {
         global $prefs_dsn, $prefs_table;
 
     var $default = Array('chosen_theme' => '../themes/default_theme.php',
                          'show_html_default' => '0');
 
     function open() {
         global $prefs_dsn, $prefs_table;

+        global $prefs_user_field, $prefs_key_field, $prefs_val_field;

 
         if(isset($this->dbh)) {
             return true;
         }
 
 
         if(isset($this->dbh)) {
             return true;
         }
 

+        if (preg_match('/^mysql/', $prefs_dsn)) {
+            $this->db_type = SMDB_MYSQL;
+        } elseif (preg_match('/^pgsql/', $prefs_dsn)) {
+            $this->db_type = SMDB_PGSQL;
+        }
+

         if (!empty($prefs_table)) {
             $this->table = $prefs_table;
         }
         if (!empty($prefs_table)) {
             $this->table = $prefs_table;
         }


@@ -168,19 +180,71 @@ class dbPrefs {
         if (!$this->open()) {
             return false;
         }
         if (!$this->open()) {
             return false;
         }

-        $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
-                         "VALUES('%s','%s','%s')",
-                         $this->table,
-                         $this->user_field,
-                         $this->key_field,
-                         $this->val_field,
-                         $this->dbh->quoteString($user),
-                         $this->dbh->quoteString($key),
-                         $this->dbh->quoteString($value));
-
-        $res = $this->dbh->simpleQuery($query);
-        if(DB::isError($res)) {
-            $this->failQuery($res);
+        if ($this->db_type == SMDB_MYSQL) {
+            $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
+                             "VALUES('%s','%s','%s')",
+                             $this->table,
+                             $this->user_field,
+                             $this->key_field,
+                             $this->val_field,
+                             $this->dbh->quoteString($user),
+                             $this->dbh->quoteString($key),
+                             $this->dbh->quoteString($value));
+
+            $res = $this->dbh->simpleQuery($query);
+            if(DB::isError($res)) {
+                $this->failQuery($res);
+            }
+        } elseif ($this->db_type == SMDB_PGSQL) {
+            $this->dbh->simpleQuery("BEGIN TRANSACTION");
+            $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
+                             $this->table,
+                             $this->user_field,
+                             $this->dbh->quoteString($user),
+                             $this->key_field,
+                             $this->dbh->quoteString($key));
+            $res = $this->dbh->simpleQuery($query);
+            if (DB::isError($res)) {
+                $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
+                $this->failQuery($res);
+            }
+            $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
+                             $this->table,
+                             $this->user_field,
+                             $this->key_field,
+                             $this->val_field,
+                             $this->dbh->quoteString($user),
+                             $this->dbh->quoteString($key),
+                             $this->dbh->quoteString($value));
+            $res = $this->dbh->simpleQuery($query);
+            if (DB::isError($res)) {
+                $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
+                $this->failQuery($res);
+            }
+            $this->dbh->simpleQuery("COMMIT TRANSACTION");
+        } else {
+            $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
+                             $this->table,
+                             $this->user_field,
+                             $this->dbh->quoteString($user),
+                             $this->key_field,
+                             $this->dbh->quoteString($key));
+            $res = $this->dbh->simpleQuery($query);
+            if (DB::isError($res)) {
+                $this->failQuery($res);
+            }
+            $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
+                             $this->table,
+                             $this->user_field,
+                             $this->key_field,
+                             $this->val_field,
+                             $this->dbh->quoteString($user),
+                             $this->dbh->quoteString($key),
+                             $this->dbh->quoteString($value));
+            $res = $this->dbh->simpleQuery($query);
+            if (DB::isError($res)) {
+                $this->failQuery($res);
+            }

         }
 
         return true;
         }
 
         return true;