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  * To use this instead of the regular prefs.php, create a
  13  * database as described below, and replace prefs.php
  14  * with this file.
  15  *
  16  * Database:
  17  * ---------
  18  *
  19  * The preferences table should have three columns:
  20  *    user       char  \  primary
  21  *    prefkey    char  /  key
  22  *    prefval    blob
  23  *
  24  *   CREATE TABLE userprefs (user CHAR(128) NOT NULL DEFAULT '',
  25  *                           prefkey CHAR(64) NOT NULL DEFAULT '',
  26  *                           prefval BLOB NOT NULL DEFAULT '',
  27  *                           primary key (user,prefkey));
  28  *
  29  * Configuration of databasename, username and password is done
  30  * by using conf.pl or the administrator plugin
  31  *
  32  * $Id$
  33  */
  34
  35 define('SMDB_UNKNOWN', 0);
  36 define('SMDB_MYSQL', 1);
  37 define('SMDB_PGSQL', 2);
  38
  39 require_once('DB.php');
  40 require_once('../config/config.php');
  41
  42 global $prefs_are_cached, $prefs_cache;
  43
  44 function cachePrefValues($username) {
  45     global $prefs_are_cached, $prefs_cache;
  46
  47     if ($prefs_are_cached) {
  48         return;
  49     }
  50
  51     session_unregister('prefs_cache');
  52     session_unregister('prefs_are_cached');
  53
  54     $db = new dbPrefs;
  55     if(isset($db->error)) {
  56         printf( _("Preference database error (%s). Exiting abnormally"),
  57               $db->error);
  58         exit;
  59     }
  60
  61     $db->fillPrefsCache($username);
  62     if (isset($db->error)) {
  63         printf( _("Preference database error (%s). Exiting abnormally"),
  64               $db->error);
  65         exit;
  66     }
  67
  68     $prefs_are_cached = true;
  69
  70     session_register('prefs_cache');
  71     session_register('prefs_are_cached');
  72 }
  73
  74 class dbPrefs {
  75     var $table = 'userprefs';
  76     var $user_field = 'user';
  77     var $key_field = 'prefkey';
  78     var $val_field = 'prefval';
  79
  80     var $dbh   = NULL;
  81     var $error = NULL;
  82     var $db_type = SMDB_UNKNOWN;
  83
  84     var $default = Array('chosen_theme' => '../themes/default_theme.php',
  85                          'show_html_default' => '0');
  86
  87     function open() {
  88         global $prefs_dsn, $prefs_table;
  89         global $prefs_user_field, $prefs_key_field, $prefs_val_field;
  90
  91         if(isset($this->dbh)) {
  92             return true;
  93         }
  94
  95         if (preg_match('/^mysql/', $prefs_dsn)) {
  96             $this->db_type = SMDB_MYSQL;
  97         } elseif (preg_match('/^pgsql/', $prefs_dsn)) {
  98             $this->db_type = SMDB_PGSQL;
  99         }
 100
 101         if (!empty($prefs_table)) {
 102             $this->table = $prefs_table;
 103         }
 104         if (!empty($prefs_user_field)) {
 105             $this->user_field = $prefs_user_field;
 106         }
 107         if (!empty($prefs_key_field)) {
 108             $this->key_field = $prefs_key_field;
 109         }
 110         if (!empty($prefs_val_field)) {
 111             $this->val_field = $prefs_val_field;
 112         }
 113         $dbh = DB::connect($prefs_dsn, true);
 114
 115         if(DB::isError($dbh) || DB::isWarning($dbh)) {
 116             $this->error = DB::errorMessage($dbh);
 117             return false;
 118         }
 119
 120         $this->dbh = $dbh;
 121         return true;
 122     }
 123
 124     function failQuery($res = NULL) {
 125         if($res == NULL) {
 126             printf(_("Preference database error (%s). Exiting abnormally"),
 127                   $this->error);
 128         } else {
 129             printf(_("Preference database error (%s). Exiting abnormally"),
 130                   DB::errorMessage($res));
 131         }
 132         exit;
 133     }
 134
 135
 136     function getKey($user, $key, $default = '') {
 137         global $prefs_cache;
 138
 139         cachePrefValues($user);
 140
 141         if (isset($prefs_cache[$key])) {
 142             return $prefs_cache[$key];
 143         } else {
 144             if (isset($this->default[$key])) {
 145                 return $this->default[$key];
 146             } else {
 147                 return $default;
 148             }
 149         }
 150     }
 151
 152     function deleteKey($user, $key) {
 153         global $prefs_cache;
 154
 155         if (!$this->open()) {
 156             return false;
 157         }
 158         $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 159                          $this->table,
 160                          $this->user_field,
 161                          $this->dbh->quoteString($user),
 162                          $this->key_field,
 163                          $this->dbh->quoteString($key));
 164
 165         $res = $this->dbh->simpleQuery($query);
 166         if(DB::isError($res)) {
 167             $this->failQuery($res);
 168         }
 169
 170         unset($prefs_cache[$key]);
 171
 172         if(substr($key, 0, 9) == 'highlight') {
 173             $this->renumberHighlightList($user);
 174         }
 175
 176         return true;
 177     }
 178
 179     function setKey($user, $key, $value) {
 180         if (!$this->open()) {
 181             return false;
 182         }
 183         if ($this->db_type == SMDB_MYSQL) {
 184             $query = sprintf("REPLACE INTO %s (%s, %s, %s) ".
 185                              "VALUES('%s','%s','%s')",
 186                              $this->table,
 187                              $this->user_field,
 188                              $this->key_field,
 189                              $this->val_field,
 190                              $this->dbh->quoteString($user),
 191                              $this->dbh->quoteString($key),
 192                              $this->dbh->quoteString($value));
 193
 194             $res = $this->dbh->simpleQuery($query);
 195             if(DB::isError($res)) {
 196                 $this->failQuery($res);
 197             }
 198         } elseif ($this->db_type == SMDB_PGSQL) {
 199             $this->dbh->simpleQuery("BEGIN TRANSACTION");
 200             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 201                              $this->table,
 202                              $this->user_field,
 203                              $this->dbh->quoteString($user),
 204                              $this->key_field,
 205                              $this->dbh->quoteString($key));
 206             $res = $this->dbh->simpleQuery($query);
 207             if (DB::isError($res)) {
 208                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 209                 $this->failQuery($res);
 210             }
 211             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 212                              $this->table,
 213                              $this->user_field,
 214                              $this->key_field,
 215                              $this->val_field,
 216                              $this->dbh->quoteString($user),
 217                              $this->dbh->quoteString($key),
 218                              $this->dbh->quoteString($value));
 219             $res = $this->dbh->simpleQuery($query);
 220             if (DB::isError($res)) {
 221                 $this->dbh->simpleQuery("ROLLBACK TRANSACTION");
 222                 $this->failQuery($res);
 223             }
 224             $this->dbh->simpleQuery("COMMIT TRANSACTION");
 225         } else {
 226             $query = sprintf("DELETE FROM %s WHERE %s='%s' AND %s='%s'",
 227                              $this->table,
 228                              $this->user_field,
 229                              $this->dbh->quoteString($user),
 230                              $this->key_field,
 231                              $this->dbh->quoteString($key));
 232             $res = $this->dbh->simpleQuery($query);
 233             if (DB::isError($res)) {
 234                 $this->failQuery($res);
 235             }
 236             $query = sprintf("INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
 237                              $this->table,
 238                              $this->user_field,
 239                              $this->key_field,
 240                              $this->val_field,
 241                              $this->dbh->quoteString($user),
 242                              $this->dbh->quoteString($key),
 243                              $this->dbh->quoteString($value));
 244             $res = $this->dbh->simpleQuery($query);
 245             if (DB::isError($res)) {
 246                 $this->failQuery($res);
 247             }
 248         }
 249
 250         return true;
 251     }
 252
 253     function fillPrefsCache($user) {
 254         global $prefs_cache;
 255
 256         if (!$this->open()) {
 257             return;
 258         }
 259
 260         $prefs_cache = array();
 261         $query = sprintf("SELECT %s as prefkey, %s as prefval FROM %s ".
 262                          "WHERE %s = '%s'",
 263                          $this->key_field,
 264                          $this->val_field,
 265                          $this->table,
 266                          $this->user_field,
 267                          $this->dbh->quoteString($user));
 268         $res = $this->dbh->query($query);
 269         if (DB::isError($res)) {
 270             $this->failQuery($res);
 271         }
 272
 273         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 274             $prefs_cache[$row['prefkey']] = $row['prefval'];
 275         }
 276     }
 277
 278     /*
 279      * When a highlight option is deleted the preferences module
 280      * must renumber the list.  This should be done somewhere else,
 281      * but it is not, so....
 282      */
 283     function renumberHighlightList($user) {
 284         if (!$this->open()) {
 285             return;
 286         }
 287         $query = sprintf("SELECT %s, %s as prefkey, %s as prefval FROM %s WHERE %s='%s' ".
 288                          "AND %s LIKE 'highlight%%' ORDER BY %s",
 289                          $this->user_field,
 290                          $this->key_field,
 291                          $this->val_field,
 292                          $this->table,
 293                          $this->user_field,
 294                          $this->dbh->quoteString($user),
 295                          $this->key_field,
 296                          $this->key_field);
 297
 298         $res = $this->dbh->query($query);
 299         if(DB::isError($res)) {
 300             $this->failQuery($res);
 301         }
 302
 303         /* Store old data in array */
 304         $rows = Array();
 305         while($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 306             $rows[] = $row;
 307         }
 308
 309         /* Renumber keys of old data */
 310         $hilinum = 0;
 311         for($i = 0; $i < count($rows) ; $i++) {
 312             $oldkey = $rows[$i]['prefkey'];
 313             $newkey = substr($oldkey, 0, 9) . $hilinum;
 314             $hilinum++;
 315
 316             if($oldkey != $newkey) {
 317                 $query = sprintf("UPDATE %s SET %s='%s' ".
 318                                  "WHERE %s ='%s' AND %s='%s'",
 319                                  $this->table,
 320                                  $this->key_field,
 321                                  $this->dbh->quoteString($newkey),
 322                                  $this->user_field,
 323                                  $this->dbh->quoteString($user),
 324                                  $this->key_field,
 325                                  $this->dbh->quoteString($oldkey));
 326
 327                 $res = $this->dbh->simpleQuery($query);
 328                 if(DB::isError($res)) {
 329                     $this->failQuery($res);
 330                 }
 331             }
 332         }
 333
 334         return;
 335     }
 336
 337 } /* end class dbPrefs */
 338
 339
 340 /* returns the value for the pref $string */
 341 function getPref($data_dir, $username, $string, $default = '') {
 342     $db = new dbPrefs;
 343     if(isset($db->error)) {
 344         printf( _("Preference database error (%s). Exiting abnormally"),
 345               $db->error);
 346         exit;
 347     }
 348
 349     return $db->getKey($username, $string, $default);
 350 }
 351
 352 /* Remove the pref $string */
 353 function removePref($data_dir, $username, $string) {
 354     $db = new dbPrefs;
 355     if(isset($db->error)) {
 356         $db->failQuery();
 357     }
 358
 359     $db->deleteKey($username, $string);
 360     return;
 361 }
 362
 363 /* sets the pref, $string, to $set_to */
 364 function setPref($data_dir, $username, $string, $set_to) {
 365     global $prefs_cache;
 366
 367     if (isset($prefs_cache[$string]) && ($prefs_cache[$string] == $set_to)) {
 368         return;
 369     }
 370
 371     if ($set_to == '') {
 372         removePref($data_dir, $username, $string);
 373         return;
 374     }
 375
 376     $db = new dbPrefs;
 377     if(isset($db->error)) {
 378         $db->failQuery();
 379     }
 380
 381     $db->setKey($username, $string, $set_to);
 382     $prefs_cache[$string] = $set_to;
 383     assert_options(ASSERT_ACTIVE, 1);
 384     assert_options(ASSERT_BAIL, 1);
 385     assert ('$set_to == $prefs_cache[$string]');
 386
 387     return;
 388 }
 389
 390 /* This checks if the prefs are available */
 391 function checkForPrefs($data_dir, $username) {
 392     $db = new dbPrefs;
 393     if(isset($db->error)) {
 394         $db->failQuery();
 395     }
 396 }
 397
 398 /* Writes the Signature */
 399 function setSig($data_dir, $username, $number, $string) {
 400     $db = new dbPrefs;
 401     if(isset($db->error)) {
 402         $db->failQuery();
 403     }
 404
 405     if ($number == "g") {
 406         $key = '___signature___';
 407     } else {
 408         $key = sprintf('___sig%s___', $number);
 409     }
 410     $db->setKey($username, $key, $string);
 411     return;
 412 }
 413
 414 /* Gets the signature */
 415 function getSig($data_dir, $username, $number) {
 416     $db = new dbPrefs;
 417     if(isset($db->error)) {
 418         $db->failQuery();
 419     }
 420
 421     if ($number == "g") {
 422         $key = '___signature___';
 423     } else {
 424         $key = sprintf('___sig%d___', $number);
 425     }
 426     return $db->getKey($username, $key);
 427 }
 428
 429 ?>