From 9874998335d5a8d6770e5b2591bcf3a74a2c5d40 Mon Sep 17 00:00:00 2001 From: simond Date: Sun, 3 Mar 2002 14:18:57 +0000 Subject: [PATCH] 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 --- doc/db-backend.txt | 15 +++---- functions/db_prefs.php | 90 ++++++++++++++++++++++++++++++++++++------ 2 files changed, 83 insertions(+), 22 deletions(-) diff --git a/doc/db-backend.txt b/doc/db-backend.txt index 917a4b37..10774509 100644 --- 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: - 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. @@ -97,25 +98,21 @@ The table structure should be similar to this (for MySQL): and for PostgreSQL: CREATE TABLE "userprefs" ( - "user" varchar(128) NOT NULL, + "username" varchar(128) NOT NULL, "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: - 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. - -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 c11b2c59..40c0b7c5 100644 --- a/functions/db_prefs.php +++ b/functions/db_prefs.php @@ -32,6 +32,10 @@ * $Id$ */ +define('SMDB_UNKNOWN', 0); +define('SMDB_MYSQL', 1); +define('SMDB_PGSQL', 2); + require_once('DB.php'); require_once('../config/config.php'); @@ -75,17 +79,25 @@ class dbPrefs { 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; + global $prefs_user_field, $prefs_key_field, $prefs_val_field; 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; } @@ -168,19 +180,71 @@ class dbPrefs { 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; -- 2.25.1