6 * Supported database schema
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
14 * PRIMARY KEY (owner,nickname)
17 * @copyright © 1999-2007 The SquirrelMail Project Team
18 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
20 * @package squirrelmail
21 * @subpackage addressbook
25 * Needs the DB functions
26 * Don't display errors here. Error will be set in class constructor function.
28 @include_once
('DB.php');
31 * Address book in a database backend
33 * Backend for personal/shared address book stored in a database,
34 * accessed using the DB-classes in PEAR.
36 * IMPORTANT: The PEAR modules must be in the include path
37 * for this class to work.
39 * An array with the following elements must be passed to
40 * the class constructor (elements marked ? are optional):
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
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).
53 * NOTE. This class should not be used directly. Use the
54 * "AddressBook" class instead.
55 * @package squirrelmail
56 * @subpackage addressbook
58 class abook_database
extends addressbook_backend
{
68 var $bname = 'database';
71 * Data Source Name (connection description)
76 * Table that stores addresses
83 * Limits list of database entries visible to end user
93 * Enable/disable writing into address book
96 var $writeable = true;
98 * Enable/disable address book listing
103 /* ========================== Private ======================= */
107 * @param array $param address book backend options
109 function abook_database($param) {
110 $this->sname
= _("Personal Address Book");
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?"),
118 $error .= _("Please contact your system administrator and report this error.");
119 return $this->set_error($error);
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');
129 $this->dsn
= $param['dsn'];
130 $this->table
= $param['table'];
131 $this->owner
= $param['owner'];
133 if (!empty($param['name'])) {
134 $this->sname
= $param['name'];
137 if (isset($param['writeable'])) {
138 $this->writeable
= $param['writeable'];
141 if (isset($param['listing'])) {
142 $this->listing
= $param['listing'];
148 return $this->set_error('Invalid argument to constructor');
155 * @param bool $new new connection if it is true
158 function open($new = false) {
161 /* Return true is file is open and $new is unset */
162 if ($this->dbh
&& !$new) {
166 /* Close old file, if any */
171 $dbh = DB
::connect($this->dsn
, true);
173 if (DB
::isError($dbh)) {
174 return $this->set_error(sprintf(_("Database error: %s"),
175 DB
::errorMessage($dbh)));
181 * field names are lowercased.
182 * We use unquoted identifiers and they use upper case in Oracle
184 $this->dbh
->setOption('portability', DB_PORTABILITY_LOWERCASE
);
190 * Close the file and forget the filehandle
193 $this->dbh
->disconnect();
198 * Determine internal database field name given one of
199 * the SquirrelMail SM_ABOOK_FIELD_* constants
201 * @param integer $field The SM_ABOOK_FIELD_* contant to look up
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)
208 function get_field_name($field) {
210 case SM_ABOOK_FIELD_NICKNAME
:
212 case SM_ABOOK_FIELD_FIRSTNAME
:
214 case SM_ABOOK_FIELD_LASTNAME
:
216 case SM_ABOOK_FIELD_EMAIL
:
218 case SM_ABOOK_FIELD_LABEL
:
225 /* ========================== Public ======================== */
228 * Search the database
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
235 function search($expr) {
241 /* To be replaced by advanded search expression parsing */
242 if (is_array($expr)) {
246 // don't allow wide search when listing is disabled.
247 if ($expr=='*' && ! $this->listing
)
250 /* lowercase expression in order to make it case insensitive */
251 $expr = strtolower($expr);
253 /* escape SQL wildcards */
254 $expr = str_replace('_', '\\_', $expr);
255 $expr = str_replace('%', '\\%', $expr);
257 /* Convert wildcards to SQL syntax */
258 $expr = str_replace('?', '_', $expr);
259 $expr = str_replace('*', '%', $expr);
260 $expr = $this->dbh
->quoteString($expr);
263 /* create escape expression */
264 $escape = 'ESCAPE \'' . $this->dbh
->quoteString('\\') . '\'';
266 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
267 "(LOWER(firstname) LIKE '%s' %s OR LOWER(lastname) LIKE '%s' %s)",
268 $this->table
, $this->owner
, $expr, $escape, $expr, $escape);
270 $res = $this->dbh
->query($query);
272 if (DB
::isError($res)) {
273 return $this->set_error(sprintf(_("Database error: %s"),
274 DB
::errorMessage($res)));
277 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
278 array_push($ret, array('nickname' => $row['nickname'],
279 'name' => $this->fullname($row['firstname'], $row['lastname']),
280 'firstname' => $row['firstname'],
281 'lastname' => $row['lastname'],
282 'email' => $row['email'],
283 'label' => $row['label'],
284 'backend' => $this->bnum
,
285 'source' => &$this->sname
));
291 * Lookup an address by the indicated field.
293 * @param string $value The value to look up
294 * @param integer $field The field to look in, should be one
295 * of the SM_ABOOK_FIELD_* constants
296 * defined in include/constants.php
297 * (OPTIONAL; defaults to nickname field)
298 * NOTE: uniqueness is only guaranteed
299 * when the nickname field is used here;
300 * otherwise, the first matching address
303 * @return array Array with lookup results when the value
304 * was found, an empty array if the value was
308 function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME
) {
313 $value = strtolower($value);
315 if (!$this->open()) {
319 $db_field = $this->get_field_name($field);
320 if ($db_field == 'ERROR') {
321 return $this->set_error(sprintf(_("Unknown field name: %s"), $field));
324 $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'",
325 $this->table
, $this->owner
, $db_field,
326 $this->dbh
->quoteString($value));
328 $res = $this->dbh
->query($query);
330 if (DB
::isError($res)) {
331 return $this->set_error(sprintf(_("Database error: %s"),
332 DB
::errorMessage($res)));
335 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
336 return array('nickname' => $row['nickname'],
337 'name' => $this->fullname($row['firstname'], $row['lastname']),
338 'firstname' => $row['firstname'],
339 'lastname' => $row['lastname'],
340 'email' => $row['email'],
341 'label' => $row['label'],
342 'backend' => $this->bnum
,
343 'source' => &$this->sname
);
350 * @return array search results
352 function list_addr() {
354 if (!$this->open()) {
358 if(isset($this->listing
) && !$this->listing
) {
363 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
364 $this->table
, $this->owner
);
366 $res = $this->dbh
->query($query);
368 if (DB
::isError($res)) {
369 return $this->set_error(sprintf(_("Database error: %s"),
370 DB
::errorMessage($res)));
373 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
374 array_push($ret, array('nickname' => $row['nickname'],
375 'name' => $this->fullname($row['firstname'], $row['lastname']),
376 'firstname' => $row['firstname'],
377 'lastname' => $row['lastname'],
378 'email' => $row['email'],
379 'label' => $row['label'],
380 'backend' => $this->bnum
,
381 'source' => &$this->sname
));
388 * @param array $userdata added data
391 function add($userdata) {
392 if (!$this->writeable
) {
393 return $this->set_error(_("Address book is read-only"));
396 if (!$this->open()) {
400 /* See if user exist already */
401 $ret = $this->lookup($userdata['nickname']);
403 return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
407 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
408 "lastname, email, label) VALUES('%s','%s','%s'," .
410 $this->table
, $this->owner
,
411 $this->dbh
->quoteString($userdata['nickname']),
412 $this->dbh
->quoteString($userdata['firstname']),
413 $this->dbh
->quoteString((!empty($userdata['lastname'])?
$userdata['lastname']:'')),
414 $this->dbh
->quoteString($userdata['email']),
415 $this->dbh
->quoteString((!empty($userdata['label'])?
$userdata['label']:'')) );
418 $r = $this->dbh
->simpleQuery($query);
420 /* Check for errors */
421 if (DB
::isError($r)) {
422 return $this->set_error(sprintf(_("Database error: %s"),
423 DB
::errorMessage($r)));
429 * Deletes address book entries
430 * @param array $alias aliases that have to be deleted. numerical
431 * array with nickname values
434 function remove($alias) {
435 if (!$this->writeable
) {
436 return $this->set_error(_("Address book is read-only"));
439 if (!$this->open()) {
444 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
445 $this->table
, $this->owner
);
448 while (list($undef, $nickname) = each($alias)) {
449 $query .= sprintf("%s nickname='%s' ", $sepstr,
450 $this->dbh
->quoteString($nickname));
456 $r = $this->dbh
->simpleQuery($query);
458 /* Check for errors */
459 if (DB
::isError($r)) {
460 return $this->set_error(sprintf(_("Database error: %s"),
461 DB
::errorMessage($r)));
468 * @param string $alias modified alias
469 * @param array $userdata new data
472 function modify($alias, $userdata) {
473 if (!$this->writeable
) {
474 return $this->set_error(_("Address book is read-only"));
477 if (!$this->open()) {
481 /* See if user exist */
482 $ret = $this->lookup($alias);
484 return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
487 /* make sure that new nickname is not used */
488 if (strtolower($alias) != strtolower($userdata['nickname'])) {
489 /* same check as in add() */
490 $ret = $this->lookup($userdata['nickname']);
492 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
493 return $this->set_error($error);
498 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
499 "lastname='%s', email='%s', label='%s' ".
500 "WHERE owner='%s' AND nickname='%s'",
502 $this->dbh
->quoteString($userdata['nickname']),
503 $this->dbh
->quoteString($userdata['firstname']),
504 $this->dbh
->quoteString((!empty($userdata['lastname'])?
$userdata['lastname']:'')),
505 $this->dbh
->quoteString($userdata['email']),
506 $this->dbh
->quoteString((!empty($userdata['label'])?
$userdata['label']:'')),
508 $this->dbh
->quoteString($alias) );
511 $r = $this->dbh
->simpleQuery($query);
513 /* Check for errors */
514 if (DB
::isError($r)) {
515 return $this->set_error(sprintf(_("Database error: %s"),
516 DB
::errorMessage($r)));
520 } /* End of class abook_database */