functions/abook_database.php

   1 <?php
   2
   3 /**
   4  * abook_database.php
   5  *
   6  * Supported database schema
   7  * <pre>
   8  *  owner varchar(128) NOT NULL
   9  *  nickname varchar(16) NOT NULL
  10  *  firstname varchar(128)
  11  *  lastname varchar(128)
  12  *  email varchar(128) NOT NULL
  13  *  label varchar(255)
  14  *  PRIMARY KEY (owner,nickname)
  15  * </pre>
  16  *
  17  * @copyright 1999-2015 The SquirrelMail Project Team
  18  * @license http://opensource.org/licenses/gpl-license.php GNU Public License
  19  * @version $Id$
  20  * @package squirrelmail
  21  * @subpackage addressbook
  22  */
  23
  24 /**
  25  * Needs the DB functions
  26  * Don't display errors here. Error will be set in class constructor function.
  27  */
  28 @include_once('DB.php');
  29
  30 /**
  31  * Address book in a database backend
  32  *
  33  * Backend for personal/shared address book stored in a database,
  34  * accessed using the DB-classes in PEAR.
  35  *
  36  * IMPORTANT:  The PEAR modules must be in the include path
  37  * for this class to work.
  38  *
  39  * An array with the following elements must be passed to
  40  * the class constructor (elements marked ? are optional):
  41  * <pre>
  42  *   dsn       => database DNS (see PEAR for syntax)
  43  *   table     => table to store addresses in (must exist)
  44  *   owner     => current user (owner of address data)
  45  * ? name      => name of address book
  46  * ? writeable => set writeable flag (true/false)
  47  * ? listing   => enable/disable listing
  48  * </pre>
  49  * The table used should have the following columns:
  50  * owner, nickname, firstname, lastname, email, label
  51  * The pair (owner,nickname) should be unique (primary key).
  52  *
  53  *  NOTE. This class should not be used directly. Use the
  54  *        "AddressBook" class instead.
  55  * @package squirrelmail
  56  * @subpackage addressbook
  57  */
  58 class abook_database extends addressbook_backend {
  59     /**
  60      * Backend type
  61      * @var string
  62      */
  63     var $btype = 'local';
  64     /**
  65      * Backend name
  66      * @var string
  67      */
  68     var $bname = 'database';
  69
  70     /**
  71      * Data Source Name (connection description)
  72      * @var string
  73      */
  74     var $dsn       = '';
  75     /**
  76      * Table that stores addresses
  77      * @var string
  78      */
  79     var $table     = '';
  80     /**
  81      * Owner name
  82      *
  83      * Limits list of database entries visible to end user
  84      * @var string
  85      */
  86     var $owner     = '';
  87     /**
  88      * Database Handle
  89      * @var resource
  90      */
  91     var $dbh       = false;
  92     /**
  93      * Enable/disable writing into address book
  94      * @var bool
  95      */
  96     var $writeable = true;
  97     /**
  98      * Enable/disable address book listing
  99      * @var bool
 100      */
 101     var $listing = true;
 102
 103     /* ========================== Private ======================= */
 104
 105     /**
 106      * Constructor
 107      * @param array $param address book backend options
 108      */
 109     function abook_database($param) {
 110         $this->sname = _("Personal Address Book");
 111
 112         /* test if Pear DB class is available and freak out if it is not */
 113         if (! class_exists('DB')) {
 114             // same error also in db_prefs.php
 115             $error  = _("Could not include PEAR database functions required for the database backend.") . "\n";
 116             $error .= sprintf(_("Is PEAR installed, and is the include path set correctly to find %s?"),
 117                               'DB.php') . "\n";
 118             $error .= _("Please contact your system administrator and report this error.");
 119             return $this->set_error($error);
 120         }
 121
 122         if (is_array($param)) {
 123             if (empty($param['dsn']) ||
 124                 empty($param['table']) ||
 125                 empty($param['owner'])) {
 126                 return $this->set_error('Invalid parameters');
 127             }
 128
 129             $this->dsn   = $param['dsn'];
 130             $this->table = $param['table'];
 131             $this->owner = $param['owner'];
 132
 133             if (!empty($param['name'])) {
 134                $this->sname = $param['name'];
 135             }
 136
 137             if (isset($param['writeable'])) {
 138                $this->writeable = $param['writeable'];
 139             }
 140
 141             if (isset($param['listing'])) {
 142                $this->listing = $param['listing'];
 143             }
 144
 145             $this->open(true);
 146         }
 147         else {
 148             return $this->set_error('Invalid argument to constructor');
 149         }
 150     }
 151
 152
 153     /**
 154      * Open the database.
 155      * @param bool $new new connection if it is true
 156      * @return bool
 157      */
 158     function open($new = false) {
 159         $this->error = '';
 160
 161         /* Return true is file is open and $new is unset */
 162         if ($this->dbh && !$new) {
 163             return true;
 164         }
 165
 166         /* Close old file, if any */
 167         if ($this->dbh) {
 168             $this->close();
 169         }
 170
 171         $dbh = DB::connect($this->dsn, true);
 172
 173         if (DB::isError($dbh)) {
 174             return $this->set_error(sprintf(_("Database error: %s"),
 175                                             DB::errorMessage($dbh)));
 176         }
 177
 178         $this->dbh = $dbh;
 179
 180         /**
 181          * field names are lowercased.
 182          * We use unquoted identifiers and they use upper case in Oracle
 183          */
 184         $this->dbh->setOption('portability', DB_PORTABILITY_LOWERCASE);
 185
 186         return true;
 187     }
 188
 189     /**
 190      * Close the file and forget the filehandle
 191      */
 192     function close() {
 193         $this->dbh->disconnect();
 194         $this->dbh = false;
 195     }
 196
 197     /**
 198      * Determine internal database field name given one of
 199      * the SquirrelMail SM_ABOOK_FIELD_* constants
 200      *
 201      * @param integer $field The SM_ABOOK_FIELD_* contant to look up
 202      *
 203      * @return string The desired field name, or the string "ERROR"
 204      *                if the $field is not understood (the caller
 205      *                is responsible for handing errors)
 206      *
 207      */
 208     function get_field_name($field) {
 209         switch ($field) {
 210             case SM_ABOOK_FIELD_NICKNAME:
 211                 return 'nickname';
 212             case SM_ABOOK_FIELD_FIRSTNAME:
 213                 return 'firstname';
 214             case SM_ABOOK_FIELD_LASTNAME:
 215                 return 'lastname';
 216             case SM_ABOOK_FIELD_EMAIL:
 217                 return 'email';
 218             case SM_ABOOK_FIELD_LABEL:
 219                 return 'label';
 220             default:
 221                 return 'ERROR';
 222         }
 223     }
 224
 225     /* ========================== Public ======================== */
 226
 227     /**
 228      * Search the database
 229      *
 230      * Backend supports only * and ? wildcards. Complex eregs are not supported.
 231      * Search is case insensitive.
 232      * @param string $expr search expression
 233      * @return array search results. boolean false on error
 234      */
 235     function search($expr) {
 236         $ret = array();
 237         if(!$this->open()) {
 238             return false;
 239         }
 240
 241         /* To be replaced by advanded search expression parsing */
 242         if (is_array($expr)) {
 243             return;
 244         }
 245
 246         // don't allow wide search when listing is disabled.
 247         if ($expr=='*' && ! $this->listing)
 248             return array();
 249
 250         /* lowercase expression in order to make it case insensitive */
 251         $expr = strtolower($expr);
 252
 253         /* escape SQL wildcards */
 254         $expr = str_replace('_', '\\_', $expr);
 255         $expr = str_replace('%', '\\%', $expr);
 256
 257         /* Convert wildcards to SQL syntax  */
 258         $expr = str_replace('?', '_', $expr);
 259         $expr = str_replace('*', '%', $expr);
 260         $expr = $this->dbh->quoteString($expr);
 261         $expr = "%$expr%";
 262
 263         /* create escape expression */
 264         $escape = 'ESCAPE \'' . $this->dbh->quoteString('\\') . '\'';
 265
 266         $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
 267                          "(LOWER(firstname) LIKE '%s' %s " .
 268                          "OR LOWER(lastname) LIKE '%s' %s " .
 269                          "OR LOWER(email) LIKE '%s' %s " .
 270                          "OR LOWER(nickname) LIKE '%s' %s)",
 271                          $this->table, $this->owner, $expr, $escape, $expr, $escape,
 272                                                      $expr, $escape, $expr, $escape);
 273
 274         $res = $this->dbh->query($query);
 275
 276         if (DB::isError($res)) {
 277             return $this->set_error(sprintf(_("Database error: %s"),
 278                                             DB::errorMessage($res)));
 279         }
 280
 281         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 282             array_push($ret, array('nickname'  => $row['nickname'],
 283                                    'name'      => $this->fullname($row['firstname'], $row['lastname']),
 284                                    'firstname' => $row['firstname'],
 285                                    'lastname'  => $row['lastname'],
 286                                    'email'     => $row['email'],
 287                                    'label'     => $row['label'],
 288                                    'backend'   => $this->bnum,
 289                                    'source'    => &$this->sname));
 290         }
 291         return $ret;
 292     }
 293
 294     /**
 295      * Lookup an address by the indicated field.
 296      *
 297      * @param string  $value The value to look up
 298      * @param integer $field The field to look in, should be one
 299      *                       of the SM_ABOOK_FIELD_* constants
 300      *                       defined in include/constants.php
 301      *                       (OPTIONAL; defaults to nickname field)
 302      *                       NOTE: uniqueness is only guaranteed
 303      *                       when the nickname field is used here;
 304      *                       otherwise, the first matching address
 305      *                       is returned.
 306      *
 307      * @return array Array with lookup results when the value
 308      *               was found, an empty array if the value was
 309      *               not found.
 310      *
 311      */
 312     function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) {
 313         if (empty($value)) {
 314             return array();
 315         }
 316
 317         $value = strtolower($value);
 318
 319         if (!$this->open()) {
 320             return false;
 321         }
 322
 323         $db_field = $this->get_field_name($field);
 324         if ($db_field == 'ERROR') {
 325             return $this->set_error(sprintf(_("Unknown field name: %s"), $field));
 326         }
 327
 328         $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'",
 329                          $this->table, $this->owner, $db_field,
 330                          $this->dbh->quoteString($value));
 331
 332         $res = $this->dbh->query($query);
 333
 334         if (DB::isError($res)) {
 335             return $this->set_error(sprintf(_("Database error: %s"),
 336                                             DB::errorMessage($res)));
 337         }
 338
 339         if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 340             return array('nickname'  => $row['nickname'],
 341                          'name'      => $this->fullname($row['firstname'], $row['lastname']),
 342                          'firstname' => $row['firstname'],
 343                          'lastname'  => $row['lastname'],
 344                          'email'     => $row['email'],
 345                          'label'     => $row['label'],
 346                          'backend'   => $this->bnum,
 347                          'source'    => &$this->sname);
 348         }
 349         return array();
 350     }
 351
 352     /**
 353      * List all addresses
 354      * @return array search results
 355      */
 356     function list_addr() {
 357         $ret = array();
 358         if (!$this->open()) {
 359             return false;
 360         }
 361
 362         if(isset($this->listing) && !$this->listing) {
 363             return array();
 364         }
 365
 366
 367         $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
 368                          $this->table, $this->owner);
 369
 370         $res = $this->dbh->query($query);
 371
 372         if (DB::isError($res)) {
 373             return $this->set_error(sprintf(_("Database error: %s"),
 374                                             DB::errorMessage($res)));
 375         }
 376
 377         while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
 378             array_push($ret, array('nickname'  => $row['nickname'],
 379                                    'name'      => $this->fullname($row['firstname'], $row['lastname']),
 380                                    'firstname' => $row['firstname'],
 381                                    'lastname'  => $row['lastname'],
 382                                    'email'     => $row['email'],
 383                                    'label'     => $row['label'],
 384                                    'backend'   => $this->bnum,
 385                                    'source'    => &$this->sname));
 386         }
 387         return $ret;
 388     }
 389
 390     /**
 391      * Add address
 392      * @param array $userdata added data
 393      * @return bool
 394      */
 395     function add($userdata) {
 396         if (!$this->writeable) {
 397             return $this->set_error(_("Address book is read-only"));
 398         }
 399
 400         if (!$this->open()) {
 401             return false;
 402         }
 403
 404         /* See if user exist already */
 405         $ret = $this->lookup($userdata['nickname']);
 406         if (!empty($ret)) {
 407             return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
 408         }
 409
 410         /* Create query */
 411         $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
 412                          "lastname, email, label) VALUES('%s','%s','%s'," .
 413                          "'%s','%s','%s')",
 414                          $this->table, $this->owner,
 415                          $this->dbh->quoteString($userdata['nickname']),
 416                          $this->dbh->quoteString($userdata['firstname']),
 417                          $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
 418                          $this->dbh->quoteString($userdata['email']),
 419                          $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) );
 420
 421          /* Do the insert */
 422          $r = $this->dbh->simpleQuery($query);
 423
 424          /* Check for errors */
 425          if (DB::isError($r)) {
 426              return $this->set_error(sprintf(_("Database error: %s"),
 427                                              DB::errorMessage($r)));
 428          }
 429          return true;
 430     }
 431
 432     /**
 433      * Deletes address book entries
 434      * @param array $alias aliases that have to be deleted. numerical
 435      *  array with nickname values
 436      * @return bool
 437      */
 438     function remove($alias) {
 439         if (!$this->writeable) {
 440             return $this->set_error(_("Address book is read-only"));
 441         }
 442
 443         if (!$this->open()) {
 444             return false;
 445         }
 446
 447         /* Create query */
 448         $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
 449                          $this->table, $this->owner);
 450
 451         $sepstr = '';
 452         while (list($undef, $nickname) = each($alias)) {
 453             $query .= sprintf("%s nickname='%s' ", $sepstr,
 454                               $this->dbh->quoteString($nickname));
 455             $sepstr = 'OR';
 456         }
 457         $query .= ')';
 458
 459         /* Delete entry */
 460         $r = $this->dbh->simpleQuery($query);
 461
 462         /* Check for errors */
 463         if (DB::isError($r)) {
 464             return $this->set_error(sprintf(_("Database error: %s"),
 465                                             DB::errorMessage($r)));
 466         }
 467         return true;
 468     }
 469
 470     /**
 471      * Modify address
 472      * @param string $alias modified alias
 473      * @param array $userdata new data
 474      * @return bool
 475      */
 476     function modify($alias, $userdata) {
 477         if (!$this->writeable) {
 478             return $this->set_error(_("Address book is read-only"));
 479         }
 480
 481         if (!$this->open()) {
 482             return false;
 483         }
 484
 485          /* See if user exist */
 486         $ret = $this->lookup($alias);
 487         if (empty($ret)) {
 488             return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
 489         }
 490
 491         /* make sure that new nickname is not used */
 492         if (strtolower($alias) != strtolower($userdata['nickname'])) {
 493             /* same check as in add() */
 494             $ret = $this->lookup($userdata['nickname']);
 495             if (!empty($ret)) {
 496                 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
 497                 return $this->set_error($error);
 498             }
 499         }
 500
 501         /* Create query */
 502         $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
 503                          "lastname='%s', email='%s', label='%s' ".
 504                          "WHERE owner='%s' AND nickname='%s'",
 505                          $this->table,
 506                          $this->dbh->quoteString($userdata['nickname']),
 507                          $this->dbh->quoteString($userdata['firstname']),
 508                          $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
 509                          $this->dbh->quoteString($userdata['email']),
 510                          $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')),
 511                          $this->owner,
 512                          $this->dbh->quoteString($alias) );
 513
 514         /* Do the insert */
 515         $r = $this->dbh->simpleQuery($query);
 516
 517         /* Check for errors */
 518         if (DB::isError($r)) {
 519             return $this->set_error(sprintf(_("Database error: %s"),
 520                                             DB::errorMessage($r)));
 521         }
 522         return true;
 523     }
 524 } /* End of class abook_database */
 525
 526 // vim: et ts=4