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();
197 /* ========================== Public ======================== */
200 * Search the database
202 * Backend supports only * and ? wildcards. Complex eregs are not supported.
203 * Search is case insensitive.
204 * @param string $expr search expression
205 * @return array search results. boolean false on error
207 function search($expr) {
213 /* To be replaced by advanded search expression parsing */
214 if (is_array($expr)) {
218 // don't allow wide search when listing is disabled.
219 if ($expr=='*' && ! $this->listing
)
222 /* lowercase expression in order to make it case insensitive */
223 $expr = strtolower($expr);
225 /* escape SQL wildcards */
226 $expr = str_replace('_', '\\_', $expr);
227 $expr = str_replace('%', '\\%', $expr);
229 /* Convert wildcards to SQL syntax */
230 $expr = str_replace('?', '_', $expr);
231 $expr = str_replace('*', '%', $expr);
232 $expr = $this->dbh
->quoteString($expr);
235 /* create escape expression */
236 $escape = 'ESCAPE \'' . $this->dbh
->quoteString('\\') . '\'';
238 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
239 "(LOWER(firstname) LIKE '%s' %s OR LOWER(lastname) LIKE '%s' %s)",
240 $this->table
, $this->owner
, $expr, $escape, $expr, $escape);
242 $res = $this->dbh
->query($query);
244 if (DB
::isError($res)) {
245 return $this->set_error(sprintf(_("Database error: %s"),
246 DB
::errorMessage($res)));
249 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
250 array_push($ret, array('nickname' => $row['nickname'],
251 'name' => $this->fullname($row['firstname'], $row['lastname']),
252 'firstname' => $row['firstname'],
253 'lastname' => $row['lastname'],
254 'email' => $row['email'],
255 'label' => $row['label'],
256 'backend' => $this->bnum
,
257 'source' => &$this->sname
));
264 * @param string $alias alias
265 * @return array search results
267 function lookup($alias) {
272 $alias = strtolower($alias);
274 if (!$this->open()) {
278 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'",
279 $this->table
, $this->owner
, $this->dbh
->quoteString($alias));
281 $res = $this->dbh
->query($query);
283 if (DB
::isError($res)) {
284 return $this->set_error(sprintf(_("Database error: %s"),
285 DB
::errorMessage($res)));
288 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
289 return array('nickname' => $row['nickname'],
290 'name' => $this->fullname($row['firstname'], $row['lastname']),
291 'firstname' => $row['firstname'],
292 'lastname' => $row['lastname'],
293 'email' => $row['email'],
294 'label' => $row['label'],
295 'backend' => $this->bnum
,
296 'source' => &$this->sname
);
303 * @return array search results
305 function list_addr() {
307 if (!$this->open()) {
311 if(isset($this->listing
) && !$this->listing
) {
316 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
317 $this->table
, $this->owner
);
319 $res = $this->dbh
->query($query);
321 if (DB
::isError($res)) {
322 return $this->set_error(sprintf(_("Database error: %s"),
323 DB
::errorMessage($res)));
326 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
327 array_push($ret, array('nickname' => $row['nickname'],
328 'name' => $this->fullname($row['firstname'], $row['lastname']),
329 'firstname' => $row['firstname'],
330 'lastname' => $row['lastname'],
331 'email' => $row['email'],
332 'label' => $row['label'],
333 'backend' => $this->bnum
,
334 'source' => &$this->sname
));
341 * @param array $userdata added data
344 function add($userdata) {
345 if (!$this->writeable
) {
346 return $this->set_error(_("Address book is read-only"));
349 if (!$this->open()) {
353 /* See if user exist already */
354 $ret = $this->lookup($userdata['nickname']);
356 return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
360 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
361 "lastname, email, label) VALUES('%s','%s','%s'," .
363 $this->table
, $this->owner
,
364 $this->dbh
->quoteString($userdata['nickname']),
365 $this->dbh
->quoteString($userdata['firstname']),
366 $this->dbh
->quoteString((!empty($userdata['lastname'])?
$userdata['lastname']:'')),
367 $this->dbh
->quoteString($userdata['email']),
368 $this->dbh
->quoteString((!empty($userdata['label'])?
$userdata['label']:'')) );
371 $r = $this->dbh
->simpleQuery($query);
373 /* Check for errors */
374 if (DB
::isError($r)) {
375 return $this->set_error(sprintf(_("Database error: %s"),
376 DB
::errorMessage($r)));
382 * Deletes address book entries
383 * @param array $alias aliases that have to be deleted. numerical
384 * array with nickname values
387 function remove($alias) {
388 if (!$this->writeable
) {
389 return $this->set_error(_("Address book is read-only"));
392 if (!$this->open()) {
397 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
398 $this->table
, $this->owner
);
401 while (list($undef, $nickname) = each($alias)) {
402 $query .= sprintf("%s nickname='%s' ", $sepstr,
403 $this->dbh
->quoteString($nickname));
409 $r = $this->dbh
->simpleQuery($query);
411 /* Check for errors */
412 if (DB
::isError($r)) {
413 return $this->set_error(sprintf(_("Database error: %s"),
414 DB
::errorMessage($r)));
421 * @param string $alias modified alias
422 * @param array $userdata new data
425 function modify($alias, $userdata) {
426 if (!$this->writeable
) {
427 return $this->set_error(_("Address book is read-only"));
430 if (!$this->open()) {
434 /* See if user exist */
435 $ret = $this->lookup($alias);
437 return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
440 /* make sure that new nickname is not used */
441 if (strtolower($alias) != strtolower($userdata['nickname'])) {
442 /* same check as in add() */
443 $ret = $this->lookup($userdata['nickname']);
445 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
446 return $this->set_error($error);
451 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
452 "lastname='%s', email='%s', label='%s' ".
453 "WHERE owner='%s' AND nickname='%s'",
455 $this->dbh
->quoteString($userdata['nickname']),
456 $this->dbh
->quoteString($userdata['firstname']),
457 $this->dbh
->quoteString((!empty($userdata['lastname'])?
$userdata['lastname']:'')),
458 $this->dbh
->quoteString($userdata['email']),
459 $this->dbh
->quoteString((!empty($userdata['label'])?
$userdata['label']:'')),
461 $this->dbh
->quoteString($alias) );
464 $r = $this->dbh
->simpleQuery($query);
466 /* Check for errors */
467 if (DB
::isError($r)) {
468 return $this->set_error(sprintf(_("Database error: %s"),
469 DB
::errorMessage($r)));
473 } /* End of class abook_database */