Fix bad assumptions about parallel arrays - see it explode by removing folders withou...
[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 1999-2016 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