ceeba5e9e4b14c86760e7f5bf5f89f53e041a1a5
[squirrelmail.git] / 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 &copy; 1999-2007 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 OR LOWER(lastname) LIKE '%s' %s)",
268 $this->table, $this->owner, $expr, $escape, $expr, $escape);
269
270 $res = $this->dbh->query($query);
271
272 if (DB::isError($res)) {
273 return $this->set_error(sprintf(_("Database error: %s"),
274 DB::errorMessage($res)));
275 }
276
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));
286 }
287 return $ret;
288 }
289
290 /**
291 * Lookup an address by the indicated field.
292 *
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
301 * is returned.
302 *
303 * @return array Array with lookup results when the value
304 * was found, an empty array if the value was
305 * not found.
306 *
307 */
308 function lookup($value, $field=SM_ABOOK_FIELD_NICKNAME) {
309 if (empty($value)) {
310 return array();
311 }
312
313 $value = strtolower($value);
314
315 if (!$this->open()) {
316 return false;
317 }
318
319 $query = sprintf("SELECT * FROM %s WHERE owner = '%s' AND LOWER(%s) = '%s'",
320 $this->table, $this->owner, $this->get_field_name($field),
321 $this->dbh->quoteString($value));
322
323 $res = $this->dbh->query($query);
324
325 if (DB::isError($res)) {
326 return $this->set_error(sprintf(_("Database error: %s"),
327 DB::errorMessage($res)));
328 }
329
330 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
331 return array('nickname' => $row['nickname'],
332 'name' => $this->fullname($row['firstname'], $row['lastname']),
333 'firstname' => $row['firstname'],
334 'lastname' => $row['lastname'],
335 'email' => $row['email'],
336 'label' => $row['label'],
337 'backend' => $this->bnum,
338 'source' => &$this->sname);
339 }
340 return array();
341 }
342
343 /**
344 * List all addresses
345 * @return array search results
346 */
347 function list_addr() {
348 $ret = array();
349 if (!$this->open()) {
350 return false;
351 }
352
353 if(isset($this->listing) && !$this->listing) {
354 return array();
355 }
356
357
358 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
359 $this->table, $this->owner);
360
361 $res = $this->dbh->query($query);
362
363 if (DB::isError($res)) {
364 return $this->set_error(sprintf(_("Database error: %s"),
365 DB::errorMessage($res)));
366 }
367
368 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
369 array_push($ret, array('nickname' => $row['nickname'],
370 'name' => $this->fullname($row['firstname'], $row['lastname']),
371 'firstname' => $row['firstname'],
372 'lastname' => $row['lastname'],
373 'email' => $row['email'],
374 'label' => $row['label'],
375 'backend' => $this->bnum,
376 'source' => &$this->sname));
377 }
378 return $ret;
379 }
380
381 /**
382 * Add address
383 * @param array $userdata added data
384 * @return bool
385 */
386 function add($userdata) {
387 if (!$this->writeable) {
388 return $this->set_error(_("Address book is read-only"));
389 }
390
391 if (!$this->open()) {
392 return false;
393 }
394
395 /* See if user exist already */
396 $ret = $this->lookup($userdata['nickname']);
397 if (!empty($ret)) {
398 return $this->set_error(sprintf(_("User \"%s\" already exists"),$ret['nickname']));
399 }
400
401 /* Create query */
402 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
403 "lastname, email, label) VALUES('%s','%s','%s'," .
404 "'%s','%s','%s')",
405 $this->table, $this->owner,
406 $this->dbh->quoteString($userdata['nickname']),
407 $this->dbh->quoteString($userdata['firstname']),
408 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
409 $this->dbh->quoteString($userdata['email']),
410 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')) );
411
412 /* Do the insert */
413 $r = $this->dbh->simpleQuery($query);
414
415 /* Check for errors */
416 if (DB::isError($r)) {
417 return $this->set_error(sprintf(_("Database error: %s"),
418 DB::errorMessage($r)));
419 }
420 return true;
421 }
422
423 /**
424 * Deletes address book entries
425 * @param array $alias aliases that have to be deleted. numerical
426 * array with nickname values
427 * @return bool
428 */
429 function remove($alias) {
430 if (!$this->writeable) {
431 return $this->set_error(_("Address book is read-only"));
432 }
433
434 if (!$this->open()) {
435 return false;
436 }
437
438 /* Create query */
439 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
440 $this->table, $this->owner);
441
442 $sepstr = '';
443 while (list($undef, $nickname) = each($alias)) {
444 $query .= sprintf("%s nickname='%s' ", $sepstr,
445 $this->dbh->quoteString($nickname));
446 $sepstr = 'OR';
447 }
448 $query .= ')';
449
450 /* Delete entry */
451 $r = $this->dbh->simpleQuery($query);
452
453 /* Check for errors */
454 if (DB::isError($r)) {
455 return $this->set_error(sprintf(_("Database error: %s"),
456 DB::errorMessage($r)));
457 }
458 return true;
459 }
460
461 /**
462 * Modify address
463 * @param string $alias modified alias
464 * @param array $userdata new data
465 * @return bool
466 */
467 function modify($alias, $userdata) {
468 if (!$this->writeable) {
469 return $this->set_error(_("Address book is read-only"));
470 }
471
472 if (!$this->open()) {
473 return false;
474 }
475
476 /* See if user exist */
477 $ret = $this->lookup($alias);
478 if (empty($ret)) {
479 return $this->set_error(sprintf(_("User \"%s\" does not exist"),$alias));
480 }
481
482 /* make sure that new nickname is not used */
483 if (strtolower($alias) != strtolower($userdata['nickname'])) {
484 /* same check as in add() */
485 $ret = $this->lookup($userdata['nickname']);
486 if (!empty($ret)) {
487 $error = sprintf(_("User '%s' already exist."), $ret['nickname']);
488 return $this->set_error($error);
489 }
490 }
491
492 /* Create query */
493 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
494 "lastname='%s', email='%s', label='%s' ".
495 "WHERE owner='%s' AND nickname='%s'",
496 $this->table,
497 $this->dbh->quoteString($userdata['nickname']),
498 $this->dbh->quoteString($userdata['firstname']),
499 $this->dbh->quoteString((!empty($userdata['lastname'])?$userdata['lastname']:'')),
500 $this->dbh->quoteString($userdata['email']),
501 $this->dbh->quoteString((!empty($userdata['label'])?$userdata['label']:'')),
502 $this->owner,
503 $this->dbh->quoteString($alias) );
504
505 /* Do the insert */
506 $r = $this->dbh->simpleQuery($query);
507
508 /* Check for errors */
509 if (DB::isError($r)) {
510 return $this->set_error(sprintf(_("Database error: %s"),
511 DB::errorMessage($r)));
512 }
513 return true;
514 }
515 } /* End of class abook_database */
516
517 // vim: et ts=4