functions/db_prefs.php

   1 <?php
   2
   3 /*
   4  * db_prefs.php
   5  *
   6  * Copyright (c) 1999-2002 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     session_unregister('prefs_cache');
  48     session_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     session_register('prefs_cache');
  67     session_register('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) || DB::isWarning($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         if(substr($key, 0, 9) == 'highlight') {
 169             $this->renumberHighlightList($user);
 170         }
 171
 172         return true;
 173     }
 174
 175     function setKey($user, $key, $value) {
 176         if (!$this->open()) {
 177             return false;
 178         }
 179         if ($this->db_type == SMDB_MYSQL) {
 180             $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
 181                              "VALUES('%s','%s','%s')",
 182                              $this->table,
 183                              $this->user_field,
 184                              $this->key_field,
 185                              $this->val_field,
 186                              $this->dbh->quoteString($user),
 187                              $this->dbh->quoteString($key),
 188                              $this->dbh->quoteString($value));
 189
 190             $res = $this->dbh->simpleQuery($query);
 191             if(DB::isError($res)) {
 192                 $this->failQuery($res);
 193             }
 194         } elseif ($this->db_type == SMDB_PGSQL) {
 195             $this->dbh->simpleQuery("BEGIN TRANSACTION");
 196             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 197                              $this->table,
 198                              $this->user_field,
 199                              $this->dbh->quoteString($user),
 200                              $this->key_field,
 201                              $this->dbh->quoteString($key));
 202             $res = $this->dbh->simpleQuery($query);
 203             if (DB::isError($res)) {
 204                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 205                 $this->failQuery($res);
 206             }
 207             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 208                              $this->table,
 209                              $this->user_field,
 210                              $this->key_field,
 211                              $this->val_field,
 212                              $this->dbh->quoteString($user),
 213                              $this->dbh->quoteString($key),
 214                              $this->dbh->quoteString($value));
 215             $res = $this->dbh->simpleQuery($query);
 216             if (DB::isError($res)) {
 217                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 218                 $this->failQuery($res);
 219             }
 220             $this->dbh->simpleQuery("COMMIT TRANSACTION");
 221         } else {
 222             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 223                              $this->table,
 224                              $this->user_field,
 225                              $this->dbh->quoteString($user),
 226                              $this->key_field,
 227                              $this->dbh->quoteString($key));
 228             $res = $this->dbh->simpleQuery($query);
 229             if (DB::isError($res)) {
 230                 $this->failQuery($res);
 231             }
 232             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 233                              $this->table,
 234                              $this->user_field,
 235                              $this->key_field,
 236                              $this->val_field,
 237                              $this->dbh->quoteString($user),
 238                              $this->dbh->quoteString($key),
 239                              $this->dbh->quoteString($value));
 240             $res = $this->dbh->simpleQuery($query);
 241             if (DB::isError($res)) {
 242                 $this->failQuery($res);
 243             }
 244         }
 245
 246         return true;
 247     }
 248
 249     function fillPrefsCache($user) {
 250         global $prefs_cache;
 251
 252         if (!$this->open()) {
 253             return;
 254         }
 255
 256         $prefs_cache = array();
 257         $query = sprintf("SELECT %s as prefkey, %s as prefval FROM %s ".
 258                          "WHERE %s = '%s'",
 259                          $this->key_field,
 260                          $this->val_field,
 261                          $this->table,
 262                          $this->user_field,
 263                          $this->dbh->quoteString($user));
 264         $res = $this->dbh->query($query);
 265         if (DB::isError($res)) {
 266             $this->failQuery($res);
 267         }
 268
 269         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 270             $prefs_cache[$row['prefkey']] = $row['prefval'];
 271         }
 272     }
 273
 274     /*
 275      * When a highlight option is deleted the preferences module
 276      * must renumber the list.  This should be done somewhere else,
 277      * but it is not, so....
 278      */
 279     function renumberHighlightList($user) {
 280         if (!$this->open()) {
 281             return;
 282         }
 283         $query = sprintf("SELECT %s, %s as prefkey, %s as prefval FROM %s WHERE %s='%s' ".
 284                          "AND %s LIKE 'highlight%%' ORDER BY %s",
 285                          $this->user_field,
 286                          $this->key_field,
 287                          $this->val_field,
 288                          $this->table,
 289                          $this->user_field,
 290                          $this->dbh->quoteString($user),
 291                          $this->key_field,
 292                          $this->key_field);
 293
 294         $res = $this->dbh->query($query);
 295         if(DB::isError($res)) {
 296             $this->failQuery($res);
 297         }
 298
 299         /* Store old data in array */
 300         $rows = Array();
 301         while($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 302             $rows[] = $row;
 303         }
 304
 305         /* Renumber keys of old data */
 306         $hilinum = 0;
 307         for($i = 0; $i < count($rows) ; $i++) {
 308             $oldkey = $rows[$i]['prefkey'];
 309             $newkey = substr($oldkey, 0, 9) . $hilinum;
 310             $hilinum++;
 311
 312             if($oldkey != $newkey) {
 313                 $query = sprintf("UPDATE %s SET %s='%s' ".
 314                                  "WHERE %s ='%s' AND %s='%s'",
 315                                  $this->table,
 316                                  $this->key_field,
 317                                  $this->dbh->quoteString($newkey),
 318                                  $this->user_field,
 319                                  $this->dbh->quoteString($user),
 320                                  $this->key_field,
 321                                  $this->dbh->quoteString($oldkey));
 322
 323                 $res = $this->dbh->simpleQuery($query);
 324                 if(DB::isError($res)) {
 325                     $this->failQuery($res);
 326                 }
 327             }
 328         }
 329
 330         return;
 331     }
 332
 333 } /* end class dbPrefs */
 334
 335
 336 /* returns the value for the pref $string */
 337 function getPref($data_dir, $username, $string, $default = '') {
 338     $db = new dbPrefs;
 339     if(isset($db->error)) {
 340         printf( _("Preference database error (%s). Exiting abnormally"),
 341               $db->error);
 342         exit;
 343     }
 344
 345     return $db->getKey($username, $string, $default);
 346 }
 347
 348 /* Remove the pref $string */
 349 function removePref($data_dir, $username, $string) {
 350     $db = new dbPrefs;
 351     if(isset($db->error)) {
 352         $db->failQuery();
 353     }
 354
 355     $db->deleteKey($username, $string);
 356     return;
 357 }
 358
 359 /* sets the pref, $string, to $set_to */
 360 function setPref($data_dir, $username, $string, $set_to) {
 361     global $prefs_cache;
 362
 363     if (isset($prefs_cache[$string]) && ($prefs_cache[$string] == $set_to)) {
 364         return;
 365     }
 366
 367     if ($set_to == '') {
 368         removePref($data_dir, $username, $string);
 369         return;
 370     }
 371
 372     $db = new dbPrefs;
 373     if(isset($db->error)) {
 374         $db->failQuery();
 375     }
 376
 377     $db->setKey($username, $string, $set_to);
 378     $prefs_cache[$string] = $set_to;
 379     assert_options(ASSERT_ACTIVE, 1);
 380     assert_options(ASSERT_BAIL, 1);
 381     assert ('$set_to == $prefs_cache[$string]');
 382
 383     return;
 384 }
 385
 386 /* This checks if the prefs are available */
 387 function checkForPrefs($data_dir, $username) {
 388     $db = new dbPrefs;
 389     if(isset($db->error)) {
 390         $db->failQuery();
 391     }
 392 }
 393
 394 /* Writes the Signature */
 395 function setSig($data_dir, $username, $number, $string) {
 396     if ($number == "g") {
 397         $key = '___signature___';
 398     } else {
 399         $key = sprintf('___sig%s___', $number);
 400     }
 401     setPref($data_dir, $username, $key, $string);
 402     return;
 403 }
 404
 405 /* Gets the signature */
 406 function getSig($data_dir, $username, $number) {
 407     if ($number == "g") {
 408         $key = '___signature___';
 409     } else {
 410         $key = sprintf('___sig%d___', $number);
 411     }
 412     return getPref($data_dir, $username, $key);
 413 }
 414
 415 ?>