X-Git-Url: https://vcs.fsf.org/?p=squirrelmail.git;a=blobdiff_plain;f=functions%2Fabook_database.php;h=1e69af3a0c3ddcd2bd47289826b6c420073c9a7b;hp=94a9180e7dcffe25d4f05e2624b93317c0981a00;hb=26bd38971ac8ea65405e7265654bef31b7594bc1;hpb=2c90a9f238ae50a870072398f0dbe32360018ccd diff --git a/functions/abook_database.php b/functions/abook_database.php index 94a9180e..1e69af3a 100644 --- a/functions/abook_database.php +++ b/functions/abook_database.php @@ -1,12 +1,36 @@ + * owner varchar(128) NOT NULL + * nickname varchar(16) NOT NULL + * firstname varchar(128) + * lastname varchar(128) + * email varchar(128) NOT NULL + * label varchar(255) + * PRIMARY KEY (owner,nickname) + * + * + * @copyright © 1999-2007 The SquirrelMail Project Team + * @license http://opensource.org/licenses/gpl-license.php GNU Public License + * @version $Id$ + * @package squirrelmail + * @subpackage addressbook + */ + +/** + * Needs the DB functions + * Don't display errors here. Error will be set in class constructor function. + */ +@include_once('DB.php'); + +/** + * Address book in a database backend * - * Backend for personal addressbook stored in a database, + * Backend for personal/shared address book stored in a database, * accessed using the DB-classes in PEAR. * * IMPORTANT: The PEAR modules must be in the include path @@ -14,59 +38,98 @@ * * An array with the following elements must be passed to * the class constructor (elements marked ? are optional): - * - * dsn => database DNS (see PEAR for syntax) - * table => table to store addresses in (must exist) - * owner => current user (owner of address data) - * ? writeable => set writeable flag (true/false) - * + * 
+ *   dsn       => database DNS (see PEAR for syntax)
+ *   table     => table to store addresses in (must exist)
+ *   owner     => current user (owner of address data)
+ * ? name      => name of address book
+ * ? writeable => set writeable flag (true/false)
+ * ? listing   => enable/disable listing
+ *
* The table used should have the following columns: * owner, nickname, firstname, lastname, email, label * The pair (owner,nickname) should be unique (primary key). * * NOTE. This class should not be used directly. Use the * "AddressBook" class instead. - * - * @version $Id$ * @package squirrelmail * @subpackage addressbook */ - -/** Needs the DB functions */ -require_once('DB.php'); - -/** - * Undocumented class - stores the addressbook in a sql database - * @package squirrelmail - */ class abook_database extends addressbook_backend { + /** + * Backend type + * @var string + */ var $btype = 'local'; + /** + * Backend name + * @var string + */ var $bname = 'database'; - + + /** + * Data Source Name (connection description) + * @var string + */ var $dsn = ''; + /** + * Table that stores addresses + * @var string + */ var $table = ''; + /** + * Owner name + * + * Limits list of database entries visible to end user + * @var string + */ var $owner = ''; + /** + * Database Handle + * @var resource + */ var $dbh = false; - + /** + * Enable/disable writing into address book + * @var bool + */ var $writeable = true; - + /** + * Enable/disable address book listing + * @var bool + */ + var $listing = true; + /* ========================== Private ======================= */ - - /* Constructor */ + + /** + * Constructor + * @param array $param address book backend options + */ function abook_database($param) { - $this->sname = _("Personal address book"); - + $this->sname = _("Personal Address Book"); + + /* test if Pear DB class is available and freak out if it is not */ + if (! class_exists('DB')) { + // same error also in db_prefs.php + $error = _("Could not include PEAR database functions required for the database backend.") . "\n"; + $error .= sprintf(_("Is PEAR installed, and is the include path set correctly to find %s?"), + 'DB.php') . "\n"; + $error .= _("Please contact your system administrator and report this error."); + return $this->set_error($error); + } + if (is_array($param)) { - if (empty($param['dsn']) || - empty($param['table']) || + if (empty($param['dsn']) || + empty($param['table']) || empty($param['owner'])) { return $this->set_error('Invalid parameters'); } - + $this->dsn = $param['dsn']; $this->table = $param['table']; $this->owner = $param['owner']; - + if (!empty($param['name'])) { $this->sname = $param['name']; } @@ -85,43 +148,91 @@ class abook_database extends addressbook_backend { return $this->set_error('Invalid argument to constructor'); } } - - - /* Open the database. New connection if $new is true */ + + + /** + * Open the database. + * @param bool $new new connection if it is true + * @return bool + */ function open($new = false) { $this->error = ''; - + /* Return true is file is open and $new is unset */ if ($this->dbh && !$new) { return true; } - + /* Close old file, if any */ if ($this->dbh) { $this->close(); } - + $dbh = DB::connect($this->dsn, true); - + if (DB::isError($dbh)) { return $this->set_error(sprintf(_("Database error: %s"), DB::errorMessage($dbh))); } - + $this->dbh = $dbh; + + /** + * field names are lowercased. + * We use unquoted identifiers and they use upper case in Oracle + */ + $this->dbh->setOption('portability', DB_PORTABILITY_LOWERCASE); + return true; } - /* Close the file and forget the filehandle */ + /** + * Close the file and forget the filehandle + */ function close() { $this->dbh->disconnect(); $this->dbh = false; } + /** + * Determine internal database field name given one of + * the SquirrelMail SM_ABOOK_FIELD_* constants + * + * @param integer $field The SM_ABOOK_FIELD_* contant to look up + * + * @return string The desired field name, or the string "ERROR" + * if the $field is not understood (the caller + * is responsible for handing errors) + * + */ + function get_field_name($field) { + switch ($field) { + case SM_ABOOK_FIELD_NICKNAME: + return 'nickname'; + case SM_ABOOK_FIELD_FIRSTNAME: + return 'firstname'; + case SM_ABOOK_FIELD_LASTNAME: + return 'lastname'; + case SM_ABOOK_FIELD_EMAIL: + return 'email'; + case SM_ABOOK_FIELD_LABEL: + return 'label'; + default: + return 'ERROR'; + } + } + /* ========================== Public ======================== */ - - /* Search the file */ - function &search($expr) { + + /** + * Search the database + * + * Backend supports only * and ? wildcards. Complex eregs are not supported. + * Search is case insensitive. + * @param string $expr search expression + * @return array search results. boolean false on error + */ + function search($expr) { $ret = array(); if(!$this->open()) { return false; @@ -132,15 +243,30 @@ class abook_database extends addressbook_backend { return; } - /* Make regexp from glob'ed expression */ + // don't allow wide search when listing is disabled. + if ($expr=='*' && ! $this->listing) + return array(); + + /* lowercase expression in order to make it case insensitive */ + $expr = strtolower($expr); + + /* escape SQL wildcards */ + $expr = str_replace('_', '\\_', $expr); + $expr = str_replace('%', '\\%', $expr); + + /* Convert wildcards to SQL syntax */ $expr = str_replace('?', '_', $expr); $expr = str_replace('*', '%', $expr); $expr = $this->dbh->quoteString($expr); $expr = "%$expr%"; + /* create escape expression */ + $escape = 'ESCAPE \'' . $this->dbh->quoteString('\\') . '\''; + $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " . - "(firstname LIKE '%s' OR lastname LIKE '%s')", - $this->table, $this->owner, $expr, $expr); + "(LOWER(firstname) LIKE '%s' %s OR LOWER(lastname) LIKE '%s' %s)", + $this->table, $this->owner, $expr, $escape, $expr, $escape); + $res = $this->dbh->query($query); if (DB::isError($res)) { @@ -150,7 +276,7 @@ class abook_database extends addressbook_backend { while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { array_push($ret, array('nickname' => $row['nickname'], - 'name' => "$row[firstname] $row[lastname]", + 'name' => $this->fullname($row['firstname'], $row['lastname']), 'firstname' => $row['firstname'], 'lastname' => $row['lastname'], 'email' => $row['email'], @@ -160,21 +286,44 @@ class abook_database extends addressbook_backend { } return $ret; } - - /* Lookup alias */ - function &lookup($alias) { - if (empty($alias)) { + + /** + * Lookup an address by the indicated field. + * + * @param string $value The value to look up + * @param integer $field The field to look in, should be one + * of the SM_ABOOK_FIELD_* constants + * defined in include/constants.php + * (OPTIONAL; defaults to nickname field) + * NOTE: uniqueness is only guaranteed + * when the nickname field is used here; + * otherwise, the first matching address + * is returned. + * + * @return array Array with lookup results when the value + * was found, an empty array if the value was + * not found. + * + */ + function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) { + if (empty($value)) { return array(); } - - $alias = strtolower($alias); + + $value = strtolower($value); if (!$this->open()) { return false; } - - $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'", - $this->table, $this->owner, $this->dbh->quoteString($alias)); + + $db_field = $this->get_field_name($field); + if ($db_field == 'ERROR') { + return $this->set_error(sprintf(_("Unknown field name: %s"), $field)); + } + + $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'", + $this->table, $this->owner, $db_field, + $this->dbh->quoteString($value)); $res = $this->dbh->query($query); @@ -185,7 +334,7 @@ class abook_database extends addressbook_backend { if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { return array('nickname' => $row['nickname'], - 'name' => "$row[firstname] $row[lastname]", + 'name' => $this->fullname($row['firstname'], $row['lastname']), 'firstname' => $row['firstname'], 'lastname' => $row['lastname'], 'email' => $row['email'], @@ -196,23 +345,26 @@ class abook_database extends addressbook_backend { return array(); } - /* List all addresses */ - function &list_addr() { + /** + * List all addresses + * @return array search results + */ + function list_addr() { $ret = array(); if (!$this->open()) { return false; } - - if(isset($this->listing) && !$this->listing) { - return array(); - } + + if(isset($this->listing) && !$this->listing) { + return array(); + } $query = sprintf("SELECT * FROM %s WHERE owner='%s'", $this->table, $this->owner); $res = $this->dbh->query($query); - + if (DB::isError($res)) { return $this->set_error(sprintf(_("Database error: %s"), DB::errorMessage($res))); @@ -220,7 +372,7 @@ class abook_database extends addressbook_backend { while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { array_push($ret, array('nickname' => $row['nickname'], - 'name' => "$row[firstname] $row[lastname]", + 'name' => $this->fullname($row['firstname'], $row['lastname']), 'firstname' => $row['firstname'], 'lastname' => $row['lastname'], 'email' => $row['email'], @@ -231,21 +383,24 @@ class abook_database extends addressbook_backend { return $ret; } - /* Add address */ + /** + * Add address + * @param array $userdata added data + * @return bool + */ function add($userdata) { if (!$this->writeable) { - return $this->set_error(_("Addressbook is read-only")); + return $this->set_error(_("Address book is read-only")); } if (!$this->open()) { return false; } - + /* See if user exist already */ $ret = $this->lookup($userdata['nickname']); if (!empty($ret)) { - return $this->set_error(sprintf(_("User '%s' already exist"), - $ret['nickname'])); + return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname'])); } /* Create query */ @@ -254,32 +409,37 @@ class abook_database extends addressbook_backend { "'%s','%s','%s')", $this->table, $this->owner, $this->dbh->quoteString($userdata['nickname']), - $this->dbh->quoteString($userdata['firstname']), - $this->dbh->quoteString($userdata['lastname']), - $this->dbh->quoteString($userdata['email']), - $this->dbh->quoteString($userdata['label']) ); + $this->dbh->quoteString($userdata['firstname']), + $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')), + $this->dbh->quoteString($userdata['email']), + $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) ); /* Do the insert */ $r = $this->dbh->simpleQuery($query); - if ($r == DB_OK) { - return true; + + /* Check for errors */ + if (DB::isError($r)) { + return $this->set_error(sprintf(_("Database error: %s"), + DB::errorMessage($r))); } - - /* Fail */ - return $this->set_error(sprintf(_("Database error: %s"), - DB::errorMessage($r))); + return true; } - /* Delete address */ + /** + * Deletes address book entries + * @param array $alias aliases that have to be deleted. numerical + * array with nickname values + * @return bool + */ function remove($alias) { if (!$this->writeable) { - return $this->set_error(_("Addressbook is read-only")); + return $this->set_error(_("Address book is read-only")); } if (!$this->open()) { return false; } - + /* Create query */ $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (", $this->table, $this->owner); @@ -294,55 +454,69 @@ class abook_database extends addressbook_backend { /* Delete entry */ $r = $this->dbh->simpleQuery($query); - if ($r == DB_OK) { - return true; - } - /* Fail */ - return $this->set_error(sprintf(_("Database error: %s"), - DB::errorMessage($r))); + /* Check for errors */ + if (DB::isError($r)) { + return $this->set_error(sprintf(_("Database error: %s"), + DB::errorMessage($r))); + } + return true; } - /* Modify address */ + /** + * Modify address + * @param string $alias modified alias + * @param array $userdata new data + * @return bool + */ function modify($alias, $userdata) { if (!$this->writeable) { - return $this->set_error(_("Addressbook is read-only")); + return $this->set_error(_("Address book is read-only")); } if (!$this->open()) { return false; } - + /* See if user exist */ $ret = $this->lookup($alias); if (empty($ret)) { - return $this->set_error(sprintf(_("User '%s' does not exist"), - $alias)); + return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias)); + } + + /* make sure that new nickname is not used */ + if (strtolower($alias) != strtolower($userdata['nickname'])) { + /* same check as in add() */ + $ret = $this->lookup($userdata['nickname']); + if (!empty($ret)) { + $error = sprintf(_("User '%s' already exist."), $ret['nickname']); + return $this->set_error($error); + } } /* Create query */ $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ". "lastname='%s', email='%s', label='%s' ". "WHERE owner='%s' AND nickname='%s'", - $this->table, + $this->table, $this->dbh->quoteString($userdata['nickname']), - $this->dbh->quoteString($userdata['firstname']), - $this->dbh->quoteString($userdata['lastname']), - $this->dbh->quoteString($userdata['email']), - $this->dbh->quoteString($userdata['label']), + $this->dbh->quoteString($userdata['firstname']), + $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')), + $this->dbh->quoteString($userdata['email']), + $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')), $this->owner, $this->dbh->quoteString($alias) ); /* Do the insert */ $r = $this->dbh->simpleQuery($query); - if ($r == DB_OK) { - return true; - } - /* Fail */ - return $this->set_error(sprintf(_("Database error: %s"), - DB::errorMessage($r))); + /* Check for errors */ + if (DB::isError($r)) { + return $this->set_error(sprintf(_("Database error: %s"), + DB::errorMessage($r))); + } + return true; } } /* End of class abook_database */ -?> +// vim: et ts=4