6 * Copyright (c) 1999-2004 The SquirrelMail Project Team
7 * Licensed under the GNU GPL. For full terms see the file COPYING.
9 * Backend for personal addressbook stored in a database,
10 * accessed using the DB-classes in PEAR.
12 * IMPORTANT: The PEAR modules must be in the include path
13 * for this class to work.
15 * An array with the following elements must be passed to
16 * the class constructor (elements marked ? are optional):
18 * dsn => database DNS (see PEAR for syntax)
19 * table => table to store addresses in (must exist)
20 * owner => current user (owner of address data)
21 * ? writeable => set writeable flag (true/false)
23 * The table used should have the following columns:
24 * owner, nickname, firstname, lastname, email, label
25 * The pair (owner,nickname) should be unique (primary key).
27 * NOTE. This class should not be used directly. Use the
28 * "AddressBook" class instead.
31 * @package squirrelmail
32 * @subpackage addressbook
35 /** Needs the DB functions */
36 require_once('DB.php');
39 * Undocumented class - stores the addressbook in a sql database
40 * @package squirrelmail
42 class abook_database
extends addressbook_backend
{
44 var $bname = 'database';
51 var $writeable = true;
53 /* ========================== Private ======================= */
56 function abook_database($param) {
57 $this->sname
= _("Personal address book");
59 if (is_array($param)) {
60 if (empty($param['dsn']) ||
61 empty($param['table']) ||
62 empty($param['owner'])) {
63 return $this->set_error('Invalid parameters');
66 $this->dsn
= $param['dsn'];
67 $this->table
= $param['table'];
68 $this->owner
= $param['owner'];
70 if (!empty($param['name'])) {
71 $this->sname
= $param['name'];
74 if (isset($param['writeable'])) {
75 $this->writeable
= $param['writeable'];
78 if (isset($param['listing'])) {
79 $this->listing
= $param['listing'];
85 return $this->set_error('Invalid argument to constructor');
90 /* Open the database. New connection if $new is true */
91 function open($new = false) {
94 /* Return true is file is open and $new is unset */
95 if ($this->dbh
&& !$new) {
99 /* Close old file, if any */
104 $dbh = DB
::connect($this->dsn
, true);
106 if (DB
::isError($dbh)) {
107 return $this->set_error(sprintf(_("Database error: %s"),
108 DB
::errorMessage($dbh)));
115 /* Close the file and forget the filehandle */
117 $this->dbh
->disconnect();
121 /* ========================== Public ======================== */
123 /* Search the file */
124 function &search($expr) {
130 /* To be replaced by advanded search expression parsing */
131 if (is_array($expr)) {
135 /* Make regexp from glob'ed expression */
136 $expr = str_replace('?', '_', $expr);
137 $expr = str_replace('*', '%', $expr);
138 $expr = $this->dbh
->quoteString($expr);
141 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
142 "(firstname LIKE '%s' OR lastname LIKE '%s')",
143 $this->table
, $this->owner
, $expr, $expr);
144 $res = $this->dbh
->query($query);
146 if (DB
::isError($res)) {
147 return $this->set_error(sprintf(_("Database error: %s"),
148 DB
::errorMessage($res)));
151 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
152 array_push($ret, array('nickname' => $row['nickname'],
153 'name' => "$row[firstname] $row[lastname]",
154 'firstname' => $row['firstname'],
155 'lastname' => $row['lastname'],
156 'email' => $row['email'],
157 'label' => $row['label'],
158 'backend' => $this->bnum
,
159 'source' => &$this->sname
));
165 function &lookup($alias) {
170 $alias = strtolower($alias);
172 if (!$this->open()) {
176 $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'",
177 $this->table
, $this->owner
, $this->dbh
->quoteString($alias));
179 $res = $this->dbh
->query($query);
181 if (DB
::isError($res)) {
182 return $this->set_error(sprintf(_("Database error: %s"),
183 DB
::errorMessage($res)));
186 if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
187 return array('nickname' => $row['nickname'],
188 'name' => "$row[firstname] $row[lastname]",
189 'firstname' => $row['firstname'],
190 'lastname' => $row['lastname'],
191 'email' => $row['email'],
192 'label' => $row['label'],
193 'backend' => $this->bnum
,
194 'source' => &$this->sname
);
199 /* List all addresses */
200 function &list_addr() {
202 if (!$this->open()) {
206 if(isset($this->listing
) && !$this->listing
) {
211 $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
212 $this->table
, $this->owner
);
214 $res = $this->dbh
->query($query);
216 if (DB
::isError($res)) {
217 return $this->set_error(sprintf(_("Database error: %s"),
218 DB
::errorMessage($res)));
221 while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC
)) {
222 array_push($ret, array('nickname' => $row['nickname'],
223 'name' => "$row[firstname] $row[lastname]",
224 'firstname' => $row['firstname'],
225 'lastname' => $row['lastname'],
226 'email' => $row['email'],
227 'label' => $row['label'],
228 'backend' => $this->bnum
,
229 'source' => &$this->sname
));
235 function add($userdata) {
236 if (!$this->writeable
) {
237 return $this->set_error(_("Addressbook is read-only"));
240 if (!$this->open()) {
244 /* See if user exist already */
245 $ret = $this->lookup($userdata['nickname']);
247 return $this->set_error(sprintf(_("User '%s' already exist"),
252 $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
253 "lastname, email, label) VALUES('%s','%s','%s'," .
255 $this->table
, $this->owner
,
256 $this->dbh
->quoteString($userdata['nickname']),
257 $this->dbh
->quoteString($userdata['firstname']),
258 $this->dbh
->quoteString($userdata['lastname']),
259 $this->dbh
->quoteString($userdata['email']),
260 $this->dbh
->quoteString($userdata['label']) );
263 $r = $this->dbh
->simpleQuery($query);
269 return $this->set_error(sprintf(_("Database error: %s"),
270 DB
::errorMessage($r)));
274 function remove($alias) {
275 if (!$this->writeable
) {
276 return $this->set_error(_("Addressbook is read-only"));
279 if (!$this->open()) {
284 $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
285 $this->table
, $this->owner
);
288 while (list($undef, $nickname) = each($alias)) {
289 $query .= sprintf("%s nickname='%s' ", $sepstr,
290 $this->dbh
->quoteString($nickname));
296 $r = $this->dbh
->simpleQuery($query);
302 return $this->set_error(sprintf(_("Database error: %s"),
303 DB
::errorMessage($r)));
307 function modify($alias, $userdata) {
308 if (!$this->writeable
) {
309 return $this->set_error(_("Addressbook is read-only"));
312 if (!$this->open()) {
316 /* See if user exist */
317 $ret = $this->lookup($alias);
319 return $this->set_error(sprintf(_("User '%s' does not exist"),
324 $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
325 "lastname='%s', email='%s', label='%s' ".
326 "WHERE owner='%s' AND nickname='%s'",
328 $this->dbh
->quoteString($userdata['nickname']),
329 $this->dbh
->quoteString($userdata['firstname']),
330 $this->dbh
->quoteString($userdata['lastname']),
331 $this->dbh
->quoteString($userdata['email']),
332 $this->dbh
->quoteString($userdata['label']),
334 $this->dbh
->quoteString($alias) );
337 $r = $this->dbh
->simpleQuery($query);
343 return $this->set_error(sprintf(_("Database error: %s"),
344 DB
::errorMessage($r)));
346 } /* End of class abook_database */