functions/db_prefs.php

   1 <?php
   2
   3 /*
   4  * db_prefs.php
   5  *
   6  * Copyright (c) 1999-2003 The SquirrelMail Project Team
   7  * Licensed under the GNU GPL. For full terms see the file COPYING.
   8  *
   9  * This contains functions for manipulating user preferences
  10  * stored in a database, accessed though the Pear DB layer.
  11  *
  12  * Database:
  13  * ---------
  14  *
  15  * The preferences table should have three columns:
  16  *    user       char  \  primary
  17  *    prefkey    char  /  key
  18  *    prefval    blob
  19  *
  20  *   CREATE TABLE userprefs (user CHAR(128) NOT NULL DEFAULT '',
  21  *                           prefkey CHAR(64) NOT NULL DEFAULT '',
  22  *                           prefval BLOB NOT NULL DEFAULT '',
  23  *                           primary key (user,prefkey));
  24  *
  25  * Configuration of databasename, username and password is done
  26  * by using conf.pl or the administrator plugin
  27  *
  28  * $Id$
  29  */
  30
  31 define('SMDB_UNKNOWN', 0);
  32 define('SMDB_MYSQL', 1);
  33 define('SMDB_PGSQL', 2);
  34
  35 require_once('DB.php');
  36 require_once(SM_PATH . 'config/config.php');
  37
  38 global $prefs_are_cached, $prefs_cache;
  39
  40 function cachePrefValues($username) {
  41     global $prefs_are_cached, $prefs_cache;
  42
  43     if ($prefs_are_cached) {
  44         return;
  45     }
  46
  47     sqsession_unregister('prefs_cache');
  48     sqsession_unregister('prefs_are_cached');
  49
  50     $db = new dbPrefs;
  51     if(isset($db->error)) {
  52         printf( _("Preference database error (%s). Exiting abnormally"),
  53               $db->error);
  54         exit;
  55     }
  56
  57     $db->fillPrefsCache($username);
  58     if (isset($db->error)) {
  59         printf( _("Preference database error (%s). Exiting abnormally"),
  60               $db->error);
  61         exit;
  62     }
  63
  64     $prefs_are_cached = true;
  65
  66     sqsession_register($prefs_cache, 'prefs_cache');
  67     sqsession_register($prefs_are_cached, 'prefs_are_cached');
  68 }
  69
  70 class dbPrefs {
  71     var $table = 'userprefs';
  72     var $user_field = 'user';
  73     var $key_field = 'prefkey';
  74     var $val_field = 'prefval';
  75
  76     var $dbh   = NULL;
  77     var $error = NULL;
  78     var $db_type = SMDB_UNKNOWN;
  79
  80     var $default = Array('theme_default' => 0,
  81                          'show_html_default' => '0');
  82
  83     function open() {
  84         global $prefs_dsn, $prefs_table;
  85         global $prefs_user_field, $prefs_key_field, $prefs_val_field;
  86
  87         if(isset($this->dbh)) {
  88             return true;
  89         }
  90
  91         if (preg_match('/^mysql/', $prefs_dsn)) {
  92             $this->db_type = SMDB_MYSQL;
  93         } elseif (preg_match('/^pgsql/', $prefs_dsn)) {
  94             $this->db_type = SMDB_PGSQL;
  95         }
  96
  97         if (!empty($prefs_table)) {
  98             $this->table = $prefs_table;
  99         }
 100         if (!empty($prefs_user_field)) {
 101             $this->user_field = $prefs_user_field;
 102         }
 103         if (!empty($prefs_key_field)) {
 104             $this->key_field = $prefs_key_field;
 105         }
 106         if (!empty($prefs_val_field)) {
 107             $this->val_field = $prefs_val_field;
 108         }
 109         $dbh = DB::connect($prefs_dsn, true);
 110
 111         if(DB::isError($dbh)) {
 112             $this->error = DB::errorMessage($dbh);
 113             return false;
 114         }
 115
 116         $this->dbh = $dbh;
 117         return true;
 118     }
 119
 120     function failQuery($res = NULL) {
 121         if($res == NULL) {
 122             printf(_("Preference database error (%s). Exiting abnormally"),
 123                   $this->error);
 124         } else {
 125             printf(_("Preference database error (%s). Exiting abnormally"),
 126                   DB::errorMessage($res));
 127         }
 128         exit;
 129     }
 130
 131
 132     function getKey($user, $key, $default = '') {
 133         global $prefs_cache;
 134
 135         cachePrefValues($user);
 136
 137         if (isset($prefs_cache[$key])) {
 138             return $prefs_cache[$key];
 139         } else {
 140             if (isset($this->default[$key])) {
 141                 return $this->default[$key];
 142             } else {
 143                 return $default;
 144             }
 145         }
 146     }
 147
 148     function deleteKey($user, $key) {
 149         global $prefs_cache;
 150
 151         if (!$this->open()) {
 152             return false;
 153         }
 154         $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 155                          $this->table,
 156                          $this->user_field,
 157                          $this->dbh->quoteString($user),
 158                          $this->key_field,
 159                          $this->dbh->quoteString($key));
 160
 161         $res = $this->dbh->simpleQuery($query);
 162         if(DB::isError($res)) {
 163             $this->failQuery($res);
 164         }
 165
 166         unset($prefs_cache[$key]);
 167
 168         return true;
 169     }
 170
 171     function setKey($user, $key, $value) {
 172         if (!$this->open()) {
 173             return false;
 174         }
 175         if ($this->db_type == SMDB_MYSQL) {
 176             $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
 177                              "VALUES('%s','%s','%s')",
 178                              $this->table,
 179                              $this->user_field,
 180                              $this->key_field,
 181                              $this->val_field,
 182                              $this->dbh->quoteString($user),
 183                              $this->dbh->quoteString($key),
 184                              $this->dbh->quoteString($value));
 185
 186             $res = $this->dbh->simpleQuery($query);
 187             if(DB::isError($res)) {
 188                 $this->failQuery($res);
 189             }
 190         } elseif ($this->db_type == SMDB_PGSQL) {
 191             $this->dbh->simpleQuery("BEGIN TRANSACTION");
 192             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 193                              $this->table,
 194                              $this->user_field,
 195                              $this->dbh->quoteString($user),
 196                              $this->key_field,
 197                              $this->dbh->quoteString($key));
 198             $res = $this->dbh->simpleQuery($query);
 199             if (DB::isError($res)) {
 200                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 201                 $this->failQuery($res);
 202             }
 203             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 204                              $this->table,
 205                              $this->user_field,
 206                              $this->key_field,
 207                              $this->val_field,
 208                              $this->dbh->quoteString($user),
 209                              $this->dbh->quoteString($key),
 210                              $this->dbh->quoteString($value));
 211             $res = $this->dbh->simpleQuery($query);
 212             if (DB::isError($res)) {
 213                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 214                 $this->failQuery($res);
 215             }
 216             $this->dbh->simpleQuery("COMMIT TRANSACTION");
 217         } else {
 218             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 219                              $this->table,
 220                              $this->user_field,
 221                              $this->dbh->quoteString($user),
 222                              $this->key_field,
 223                              $this->dbh->quoteString($key));
 224             $res = $this->dbh->simpleQuery($query);
 225             if (DB::isError($res)) {
 226                 $this->failQuery($res);
 227             }
 228             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 229                              $this->table,
 230                              $this->user_field,
 231                              $this->key_field,
 232                              $this->val_field,
 233                              $this->dbh->quoteString($user),
 234                              $this->dbh->quoteString($key),
 235                              $this->dbh->quoteString($value));
 236             $res = $this->dbh->simpleQuery($query);
 237             if (DB::isError($res)) {
 238                 $this->failQuery($res);
 239             }
 240         }
 241
 242         return true;
 243     }
 244
 245     function fillPrefsCache($user) {
 246         global $prefs_cache;
 247
 248         if (!$this->open()) {
 249             return;
 250         }
 251
 252         $prefs_cache = array();
 253         $query = sprintf("SELECT %s as prefkey, %s as prefval FROM %s ".
 254                          "WHERE %s = '%s'",
 255                          $this->key_field,
 256                          $this->val_field,
 257                          $this->table,
 258                          $this->user_field,
 259                          $this->dbh->quoteString($user));
 260         $res = $this->dbh->query($query);
 261         if (DB::isError($res)) {
 262             $this->failQuery($res);
 263         }
 264
 265         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 266             $prefs_cache[$row['prefkey']] = $row['prefval'];
 267         }
 268     }
 269
 270 } /* end class dbPrefs */
 271
 272
 273 /* returns the value for the pref $string */
 274 function getPref($data_dir, $username, $string, $default = '') {
 275     $db = new dbPrefs;
 276     if(isset($db->error)) {
 277         printf( _("Preference database error (%s). Exiting abnormally"),
 278               $db->error);
 279         exit;
 280     }
 281
 282     return $db->getKey($username, $string, $default);
 283 }
 284
 285 /* Remove the pref $string */
 286 function removePref($data_dir, $username, $string) {
 287     $db = new dbPrefs;
 288     if(isset($db->error)) {
 289         $db->failQuery();
 290     }
 291
 292     $db->deleteKey($username, $string);
 293
 294     if (isset($prefs_cache[$string])) {
 295         unset($prefs_cache[$string]);
 296     }
 297
 298     sqsession_register($prefs_cache , 'prefs_cache');
 299     return;
 300 }
 301
 302 /* sets the pref, $string, to $set_to */
 303 function setPref($data_dir, $username, $string, $set_to) {
 304     global $prefs_cache;
 305
 306     if (isset($prefs_cache[$string]) && ($prefs_cache[$string] == $set_to)) {
 307         return;
 308     }
 309
 310     if ($set_to === '') {
 311         removePref($data_dir, $username, $string);
 312         return;
 313     }
 314
 315     $db = new dbPrefs;
 316     if(isset($db->error)) {
 317         $db->failQuery();
 318     }
 319
 320     $db->setKey($username, $string, $set_to);
 321     $prefs_cache[$string] = $set_to;
 322     assert_options(ASSERT_ACTIVE, 1);
 323     assert_options(ASSERT_BAIL, 1);
 324     assert ('$set_to == $prefs_cache[$string]');
 325     sqsession_register($prefs_cache , 'prefs_cache');
 326     return;
 327 }
 328
 329 /* This checks if the prefs are available */
 330 function checkForPrefs($data_dir, $username) {
 331     $db = new dbPrefs;
 332     if(isset($db->error)) {
 333         $db->failQuery();
 334     }
 335 }
 336
 337 /* Writes the Signature */
 338 function setSig($data_dir, $username, $number, $string) {
 339     if ($number == "g") {
 340         $key = '___signature___';
 341     } else {
 342         $key = sprintf('___sig%s___', $number);
 343     }
 344     setPref($data_dir, $username, $key, $string);
 345     return;
 346 }
 347
 348 /* Gets the signature */
 349 function getSig($data_dir, $username, $number) {
 350     if ($number == "g") {
 351         $key = '___signature___';
 352     } else {
 353         $key = sprintf('___sig%d___', $number);
 354     }
 355     return getPref($data_dir, $username, $key);
 356 }
 357
 358 ?>