functions/abook_database.php

   1 <?php
   2
   3 /**
   4  * abook_database.php
   5  *
   6  * @copyright &copy; 1999-2006 The SquirrelMail Project Team
   7  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8  * @version $Id$
   9  * @package squirrelmail
  10  * @subpackage addressbook
  11  */
  12
  13 /** Needs the DB functions */
  14 if (!include_once('DB.php')) {
  15     // same error also in db_prefs.php
  16     $error  = _("Could not include PEAR database functions required for the database backend.") . "<br />\n";
  17     $error .= sprintf(_("Is PEAR installed, and is the include path set correctly to find %s?"),
  18                         '<tt>DB.php</tt>') . "<br />\n";
  19     $error .= _("Please contact your system administrator and report this error.");
  20     error_box($error, $color);
  21     exit;
  22 }
  23
  24 /**
  25  * Address book in a database backend
  26  *
  27  * Backend for personal/shared address book stored in a database,
  28  * accessed using the DB-classes in PEAR.
  29  *
  30  * IMPORTANT:  The PEAR modules must be in the include path
  31  * for this class to work.
  32  *
  33  * An array with the following elements must be passed to
  34  * the class constructor (elements marked ? are optional):
  35  * <pre>
  36  *   dsn       => database DNS (see PEAR for syntax)
  37  *   table     => table to store addresses in (must exist)
  38  *   owner     => current user (owner of address data)
  39  * ? name      => name of address book
  40  * ? writeable => set writeable flag (true/false)
  41  * ? listing   => enable/disable listing
  42  * </pre>
  43  * The table used should have the following columns:
  44  * owner, nickname, firstname, lastname, email, label
  45  * The pair (owner,nickname) should be unique (primary key).
  46  *
  47  *  NOTE. This class should not be used directly. Use the
  48  *        "AddressBook" class instead.
  49  * @package squirrelmail
  50  * @subpackage addressbook
  51  */
  52 class abook_database extends addressbook_backend {
  53     /**
  54      * Backend type
  55      * @var string
  56      */
  57     var $btype = 'local';
  58     /**
  59      * Backend name
  60      * @var string
  61      */
  62     var $bname = 'database';
  63
  64     /**
  65      * Data Source Name (connection description)
  66      * @var string
  67      */
  68     var $dsn       = '';
  69     /**
  70      * Table that stores addresses
  71      * @var string
  72      */
  73     var $table     = '';
  74     /**
  75      * Owner name
  76      *
  77      * Limits list of database entries visible to end user
  78      * @var string
  79      */
  80     var $owner     = '';
  81     /**
  82      * Database Handle
  83      * @var resource
  84      */
  85     var $dbh       = false;
  86     /**
  87      * Enable/disable writing into address book
  88      * @var bool
  89      */
  90     var $writeable = true;
  91     /**
  92      * Enable/disable address book listing
  93      * @var bool
  94      */
  95     var $listing = true;
  96
  97     /* ========================== Private ======================= */
  98
  99     /**
 100      * Constructor
 101      * @param array $param address book backend options
 102      */
 103     function abook_database($param) {
 104         $this->sname = _("Personal address book");
 105
 106         if (is_array($param)) {
 107             if (empty($param['dsn']) ||
 108                 empty($param['table']) ||
 109                 empty($param['owner'])) {
 110                 return $this->set_error('Invalid parameters');
 111             }
 112
 113             $this->dsn   = $param['dsn'];
 114             $this->table = $param['table'];
 115             $this->owner = $param['owner'];
 116
 117             if (!empty($param['name'])) {
 118                $this->sname = $param['name'];
 119             }
 120
 121             if (isset($param['writeable'])) {
 122                $this->writeable = $param['writeable'];
 123             }
 124
 125             if (isset($param['listing'])) {
 126                $this->listing = $param['listing'];
 127             }
 128
 129             $this->open(true);
 130         }
 131         else {
 132             return $this->set_error('Invalid argument to constructor');
 133         }
 134     }
 135
 136
 137     /**
 138      * Open the database.
 139      * @param bool $new new connection if it is true
 140      * @return bool
 141      */
 142     function open($new = false) {
 143         $this->error = '';
 144
 145         /* Return true is file is open and $new is unset */
 146         if ($this->dbh && !$new) {
 147             return true;
 148         }
 149
 150         /* Close old file, if any */
 151         if ($this->dbh) {
 152             $this->close();
 153         }
 154
 155         $dbh = DB::connect($this->dsn, true);
 156
 157         if (DB::isError($dbh)) {
 158             return $this->set_error(sprintf(_("Database error: %s"),
 159                                             DB::errorMessage($dbh)));
 160         }
 161
 162         $this->dbh = $dbh;
 163         return true;
 164     }
 165
 166     /**
 167      * Close the file and forget the filehandle
 168      */
 169     function close() {
 170         $this->dbh->disconnect();
 171         $this->dbh = false;
 172     }
 173
 174     /* ========================== Public ======================== */
 175
 176     /**
 177      * Search the database
 178      * @param string $expr search expression
 179      * @return array search results
 180      */
 181     function search($expr) {
 182         $ret = array();
 183         if(!$this->open()) {
 184             return false;
 185         }
 186
 187         /* To be replaced by advanded search expression parsing */
 188         if (is_array($expr)) {
 189             return;
 190         }
 191
 192         // don't allow wide search when listing is disabled.
 193         if ($expr=='*' && ! $this->listing)
 194             return array();
 195
 196         /* Make regexp from glob'ed expression  */
 197         $expr = str_replace('?', '_', $expr);
 198         $expr = str_replace('*', '%', $expr);
 199         $expr = $this->dbh->quoteString($expr);
 200         $expr = "%$expr%";
 201
 202         $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
 203                          "(firstname LIKE '%s' OR lastname LIKE '%s')",
 204                          $this->table, $this->owner, $expr, $expr);
 205         $res = $this->dbh->query($query);
 206
 207         if (DB::isError($res)) {
 208             return $this->set_error(sprintf(_("Database error: %s"),
 209                                             DB::errorMessage($res)));
 210         }
 211
 212         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 213             array_push($ret, array('nickname'  => $row['nickname'],
 214                                    'name'      => "$row[firstname] $row[lastname]",
 215                                    'firstname' => $row['firstname'],
 216                                    'lastname'  => $row['lastname'],
 217                                    'email'     => $row['email'],
 218                                    'label'     => $row['label'],
 219                                    'backend'   => $this->bnum,
 220                                    'source'    => &$this->sname));
 221         }
 222         return $ret;
 223     }
 224
 225     /**
 226      * Lookup alias
 227      * @param string $alias alias
 228      * @return array search results
 229      */
 230     function lookup($alias) {
 231         if (empty($alias)) {
 232             return array();
 233         }
 234
 235         $alias = strtolower($alias);
 236
 237         if (!$this->open()) {
 238             return false;
 239         }
 240
 241         $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'",
 242                          $this->table, $this->owner, $this->dbh->quoteString($alias));
 243
 244         $res = $this->dbh->query($query);
 245
 246         if (DB::isError($res)) {
 247             return $this->set_error(sprintf(_("Database error: %s"),
 248                                             DB::errorMessage($res)));
 249         }
 250
 251         if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 252             return array('nickname'  => $row['nickname'],
 253                          'name'      => "$row[firstname] $row[lastname]",
 254                          'firstname' => $row['firstname'],
 255                          'lastname'  => $row['lastname'],
 256                          'email'     => $row['email'],
 257                          'label'     => $row['label'],
 258                          'backend'   => $this->bnum,
 259                          'source'    => &$this->sname);
 260         }
 261         return array();
 262     }
 263
 264     /**
 265      * List all addresses
 266      * @return array search results
 267      */
 268     function list_addr() {
 269         $ret = array();
 270         if (!$this->open()) {
 271             return false;
 272         }
 273
 274         if(isset($this->listing) && !$this->listing) {
 275             return array();
 276         }
 277
 278
 279         $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
 280                          $this->table, $this->owner);
 281
 282         $res = $this->dbh->query($query);
 283
 284         if (DB::isError($res)) {
 285             return $this->set_error(sprintf(_("Database error: %s"),
 286                                             DB::errorMessage($res)));
 287         }
 288
 289         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 290             array_push($ret, array('nickname'  => $row['nickname'],
 291                                    'name'      => "$row[firstname] $row[lastname]",
 292                                    'firstname' => $row['firstname'],
 293                                    'lastname'  => $row['lastname'],
 294                                    'email'     => $row['email'],
 295                                    'label'     => $row['label'],
 296                                    'backend'   => $this->bnum,
 297                                    'source'    => &$this->sname));
 298         }
 299         return $ret;
 300     }
 301
 302     /**
 303      * Add address
 304      * @param array $userdata added data
 305      * @return bool
 306      */
 307     function add($userdata) {
 308         if (!$this->writeable) {
 309             return $this->set_error(_("Addressbook is read-only"));
 310         }
 311
 312         if (!$this->open()) {
 313             return false;
 314         }
 315
 316         /* See if user exist already */
 317         $ret = $this->lookup($userdata['nickname']);
 318         if (!empty($ret)) {
 319             return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
 320         }
 321
 322         /* Create query */
 323         $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
 324                          "lastname, email, label) VALUES('%s','%s','%s'," .
 325                          "'%s','%s','%s')",
 326                          $this->table, $this->owner,
 327                          $this->dbh->quoteString($userdata['nickname']),
 328                          $this->dbh->quoteString($userdata['firstname']),
 329                          $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
 330                          $this->dbh->quoteString($userdata['email']),
 331                          $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) );
 332
 333          /* Do the insert */
 334          $r = $this->dbh->simpleQuery($query);
 335          if ($r == DB_OK) {
 336              return true;
 337          }
 338
 339          /* Fail */
 340          return $this->set_error(sprintf(_("Database error: %s"),
 341                                          DB::errorMessage($r)));
 342     }
 343
 344     /**
 345      * Delete address
 346      * @param string $alias alias that has to be deleted
 347      * @return bool
 348      */
 349     function remove($alias) {
 350         if (!$this->writeable) {
 351             return $this->set_error(_("Addressbook is read-only"));
 352         }
 353
 354         if (!$this->open()) {
 355             return false;
 356         }
 357
 358         /* Create query */
 359         $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
 360                          $this->table, $this->owner);
 361
 362         $sepstr = '';
 363         while (list($undef, $nickname) = each($alias)) {
 364             $query .= sprintf("%s nickname='%s' ", $sepstr,
 365                               $this->dbh->quoteString($nickname));
 366             $sepstr = 'OR';
 367         }
 368         $query .= ')';
 369
 370         /* Delete entry */
 371         $r = $this->dbh->simpleQuery($query);
 372         if ($r == DB_OK) {
 373             return true;
 374         }
 375
 376         /* Fail */
 377         return $this->set_error(sprintf(_("Database error: %s"),
 378                                          DB::errorMessage($r)));
 379     }
 380
 381     /**
 382      * Modify address
 383      * @param string $alias modified alias
 384      * @param array $userdata new data
 385      * @return bool
 386      */
 387     function modify($alias, $userdata) {
 388         if (!$this->writeable) {
 389             return $this->set_error(_("Addressbook is read-only"));
 390         }
 391
 392         if (!$this->open()) {
 393             return false;
 394         }
 395
 396          /* See if user exist */
 397         $ret = $this->lookup($alias);
 398         if (empty($ret)) {
 399             return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
 400         }
 401
 402         /* Create query */
 403         $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
 404                          "lastname='%s', email='%s', label='%s' ".
 405                          "WHERE owner='%s' AND nickname='%s'",
 406                          $this->table,
 407                          $this->dbh->quoteString($userdata['nickname']),
 408                          $this->dbh->quoteString($userdata['firstname']),
 409                          $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
 410                          $this->dbh->quoteString($userdata['email']),
 411                          $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')),
 412                          $this->owner,
 413                          $this->dbh->quoteString($alias) );
 414
 415         /* Do the insert */
 416         $r = $this->dbh->simpleQuery($query);
 417         if ($r == DB_OK) {
 418             return true;
 419         }
 420
 421         /* Fail */
 422         return $this->set_error(sprintf(_("Database error: %s"),
 423                                         DB::errorMessage($r)));
 424     }
 425 } /* End of class abook_database */
 426
 427 // vim: et ts=4
 428 ?>