[squirrelmail.git] / functions / abook_database.php

<?php
   
/**
 * abook_database.php
 *
 * Copyright (c) 1999-2004 The SquirrelMail Project Team
 * Licensed under the GNU GPL. For full terms see the file COPYING.
 *
 * Backend for personal addressbook stored in a database,
 * accessed using the DB-classes in PEAR.
 *
 * IMPORTANT:  The PEAR modules must be in the include path
 * for this class to work.
 *
 * An array with the following elements must be passed to
 * the class constructor (elements marked ? are optional):
 *
 *    dsn       => database DNS (see PEAR for syntax)
 *    table     => table to store addresses in (must exist)
 *    owner     => current user (owner of address data)
 *  ? writeable => set writeable flag (true/false)
 *
 * The table used should have the following columns:
 * owner, nickname, firstname, lastname, email, label
 * The pair (owner,nickname) should be unique (primary key).
 *
 *  NOTE. This class should not be used directly. Use the
 *        "AddressBook" class instead.
 *
 * @version $Id$
 * @package squirrelmail
 * @subpackage addressbook
 */

/** Needs the DB functions */   
require_once('DB.php');

/**
 * Undocumented class - stores the addressbook in a sql database
 * @package squirrelmail
 */
class abook_database extends addressbook_backend {
    var $btype = 'local';
    var $bname = 'database';
      
    var $dsn       = '';
    var $table     = '';
    var $owner     = '';
    var $dbh       = false;
      
    var $writeable = true;
      
    /* ========================== Private ======================= */
      
    /* Constructor */
    function abook_database($param) {
        $this->sname = _("Personal address book");
         
        if (is_array($param)) {
            if (empty($param['dsn']) || 
                empty($param['table']) || 
                empty($param['owner'])) {
                return $this->set_error('Invalid parameters');
            }
            
            $this->dsn   = $param['dsn'];
            $this->table = $param['table'];
            $this->owner = $param['owner'];
            
            if (!empty($param['name'])) {
               $this->sname = $param['name'];
            }

            if (isset($param['writeable'])) {
               $this->writeable = $param['writeable'];
            }

            if (isset($param['listing'])) {
               $this->listing = $param['listing'];
            }

            $this->open(true);
        }
        else {
            return $this->set_error('Invalid argument to constructor');
        }
    }
      
      
    /* Open the database. New connection if $new is true */
    function open($new = false) {
        $this->error = '';
         
        /* Return true is file is open and $new is unset */
        if ($this->dbh && !$new) {
            return true;
        }
         
        /* Close old file, if any */
        if ($this->dbh) {
            $this->close();
        }
         
        $dbh = DB::connect($this->dsn, true);
         
        if (DB::isError($dbh)) {
            return $this->set_error(sprintf(_("Database error: %s"),
                                            DB::errorMessage($dbh)));
        }
         
        $this->dbh = $dbh;
        return true;
    }

    /* Close the file and forget the filehandle */
    function close() {
        $this->dbh->disconnect();
        $this->dbh = false;
    }

    /* ========================== Public ======================== */
     
    /* Search the file */
    function &search($expr) {
        $ret = array();
        if(!$this->open()) {
            return false;
        }

        /* To be replaced by advanded search expression parsing */
        if (is_array($expr)) {
            return;
        }

        /* Make regexp from glob'ed expression  */
        $expr = str_replace('?', '_', $expr);
        $expr = str_replace('*', '%', $expr);
        $expr = $this->dbh->quoteString($expr);
        $expr = "%$expr%";

        $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
                         "(firstname LIKE '%s' OR lastname LIKE '%s')",
                         $this->table, $this->owner, $expr, $expr);
        $res = $this->dbh->query($query);

        if (DB::isError($res)) {
            return $this->set_error(sprintf(_("Database error: %s"),
                                            DB::errorMessage($res)));
        }

        while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            array_push($ret, array('nickname'  => $row['nickname'],
                                   'name'      => "$row[firstname] $row[lastname]",
                                   'firstname' => $row['firstname'],
                                   'lastname'  => $row['lastname'],
                                   'email'     => $row['email'],
                                   'label'     => $row['label'],
                                   'backend'   => $this->bnum,
                                   'source'    => &$this->sname));
        }
        return $ret;
    }
     
    /* Lookup alias */
    function &lookup($alias) {
        if (empty($alias)) {
            return array();
        }
         
        $alias = strtolower($alias);

        if (!$this->open()) {
            return false;
        }
         
        $query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'",
                         $this->table, $this->owner, $this->dbh->quoteString($alias));

        $res = $this->dbh->query($query);

        if (DB::isError($res)) {
            return $this->set_error(sprintf(_("Database error: %s"),
                                            DB::errorMessage($res)));
        }

        if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            return array('nickname'  => $row['nickname'],
                         'name'      => "$row[firstname] $row[lastname]",
                         'firstname' => $row['firstname'],
                         'lastname'  => $row['lastname'],
                         'email'     => $row['email'],
                         'label'     => $row['label'],
                         'backend'   => $this->bnum,
                         'source'    => &$this->sname);
        }
        return array();
    }

    /* List all addresses */
    function &list_addr() {
        $ret = array();
        if (!$this->open()) {
            return false;
        }
	
	if(isset($this->listing) && !$this->listing) {
	    return array();
	}


        $query = sprintf("SELECT * FROM %s WHERE owner='%s'",
                         $this->table, $this->owner);

        $res = $this->dbh->query($query);
        
        if (DB::isError($res)) {
            return $this->set_error(sprintf(_("Database error: %s"),
                                            DB::errorMessage($res)));
        }

        while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
            array_push($ret, array('nickname'  => $row['nickname'],
                                   'name'      => "$row[firstname] $row[lastname]",
                                   'firstname' => $row['firstname'],
                                   'lastname'  => $row['lastname'],
                                   'email'     => $row['email'],
                                   'label'     => $row['label'],
                                   'backend'   => $this->bnum,
                                   'source'    => &$this->sname));
        }
        return $ret;
    }

    /* Add address */
    function add($userdata) {
        if (!$this->writeable) {
            return $this->set_error(_("Addressbook is read-only"));
        }

        if (!$this->open()) {
            return false;
        }
         
        /* See if user exist already */
        $ret = $this->lookup($userdata['nickname']);
        if (!empty($ret)) {
            return $this->set_error(sprintf(_("User '%s' already exist"),
                                            $ret['nickname']));
        }

        /* Create query */
        $query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
                         "lastname, email, label) VALUES('%s','%s','%s'," .
                         "'%s','%s','%s')",
                         $this->table, $this->owner,
                         $this->dbh->quoteString($userdata['nickname']),
                         $this->dbh->quoteString($userdata['firstname']), 
                         $this->dbh->quoteString($userdata['lastname']),
                         $this->dbh->quoteString($userdata['email']), 
                         $this->dbh->quoteString($userdata['label']) );

         /* Do the insert */
         $r = $this->dbh->simpleQuery($query);
         if ($r == DB_OK) {
             return true;
         }

         /* Fail */
         return $this->set_error(sprintf(_("Database error: %s"),
                                         DB::errorMessage($r)));
    }

    /* Delete address */
    function remove($alias) {
        if (!$this->writeable) {
            return $this->set_error(_("Addressbook is read-only"));
        }

        if (!$this->open()) {
            return false;
        }
         
        /* Create query */
        $query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
                         $this->table, $this->owner);

        $sepstr = '';
        while (list($undef, $nickname) = each($alias)) {
            $query .= sprintf("%s nickname='%s' ", $sepstr,
                              $this->dbh->quoteString($nickname));
            $sepstr = 'OR';
        }
        $query .= ')';

        /* Delete entry */
        $r = $this->dbh->simpleQuery($query);
        if ($r == DB_OK) {
            return true;
        }

        /* Fail */
        return $this->set_error(sprintf(_("Database error: %s"),
                                         DB::errorMessage($r)));
    }

    /* Modify address */
    function modify($alias, $userdata) {
        if (!$this->writeable) {
            return $this->set_error(_("Addressbook is read-only"));
        }

        if (!$this->open()) {
            return false;
        }
         
         /* See if user exist */
        $ret = $this->lookup($alias);
        if (empty($ret)) {
            return $this->set_error(sprintf(_("User '%s' does not exist"),
                                            $alias));
        }

        /* Create query */
        $query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
                         "lastname='%s', email='%s', label='%s' ".
                         "WHERE owner='%s' AND nickname='%s'",
                         $this->table, 
                         $this->dbh->quoteString($userdata['nickname']),
                         $this->dbh->quoteString($userdata['firstname']), 
                         $this->dbh->quoteString($userdata['lastname']),
                         $this->dbh->quoteString($userdata['email']), 
                         $this->dbh->quoteString($userdata['label']),
                         $this->owner,
                         $this->dbh->quoteString($alias) );

        /* Do the insert */
        $r = $this->dbh->simpleQuery($query);
        if ($r == DB_OK) {
            return true;
        }

        /* Fail */
        return $this->set_error(sprintf(_("Database error: %s"),
                                        DB::errorMessage($r)));
    }
} /* End of class abook_database */

?>
Commit	Line	Data
9b9474d6	1	<?php
7902aca2	2
35586184	3	/**
	4	* abook_database.php
	5	*
82d304a0	6	* Copyright (c) 1999-2004 The SquirrelMail Project Team
35586184	7	* Licensed under the GNU GPL. For full terms see the file COPYING.
	8	*
	9	* Backend for personal addressbook stored in a database,
	10	* accessed using the DB-classes in PEAR.
	11	*
	12	* IMPORTANT: The PEAR modules must be in the include path
	13	* for this class to work.
	14	*
	15	* An array with the following elements must be passed to
	16	* the class constructor (elements marked ? are optional):
	17	*
	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)
	22	*
	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).
	26	*
	27	* NOTE. This class should not be used directly. Use the
	28	* "AddressBook" class instead.
	29	*
a9d318b0	30	* @version $Id$
d6c32258	31	* @package squirrelmail
a9d318b0	32	* @subpackage addressbook
35586184	33	*/
d6c32258	34
d6c32258	35	/** Needs the DB functions */
35586184	36	require_once('DB.php');
d6c32258	37
	38	/**
	39	* Undocumented class - stores the addressbook in a sql database
	40	* @package squirrelmail
	41	*/
9b9474d6	42	class abook_database extends addressbook_backend {
	43	var $btype = 'local';
	44	var $bname = 'database';
7902aca2	45
9b9474d6	46	var $dsn = '';
	47	var $table = '';
	48	var $owner = '';
	49	var $dbh = false;
7902aca2	50
9b9474d6	51	var $writeable = true;
7902aca2	52
9b9474d6	53	/* ========================== Private ======================= */
7902aca2	54
9b9474d6	55	/* Constructor */
	56	function abook_database($param) {
	57	$this->sname = _("Personal address book");
7902aca2	58
9b9474d6	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');
	64	}
7902aca2	65
91be2362	66	$this->dsn = $param['dsn'];
	67	$this->table = $param['table'];
	68	$this->owner = $param['owner'];
7902aca2	69
9b9474d6	70	if (!empty($param['name'])) {
91be2362	71	$this->sname = $param['name'];
9b9474d6	72	}
7902aca2	73
9b9474d6	74	if (isset($param['writeable'])) {
91be2362	75	$this->writeable = $param['writeable'];
9b9474d6	76	}
7902aca2	77
30e9932c	78	if (isset($param['listing'])) {
	79	$this->listing = $param['listing'];
	80	}
	81
7902aca2	82	$this->open(true);
9b9474d6	83	}
9b9474d6	84	else {
91be2362	85	return $this->set_error('Invalid argument to constructor');
9b9474d6	86	}
9b9474d6	87	}
7902aca2	88
7902aca2	89
9b9474d6	90	/* Open the database. New connection if $new is true */
	91	function open($new = false) {
	92	$this->error = '';
7902aca2	93
9b9474d6	94	/* Return true is file is open and $new is unset */
9b9474d6	95	if ($this->dbh && !$new) {
7902aca2	96	return true;
9b9474d6	97	}
7902aca2	98
9b9474d6	99	/* Close old file, if any */
	100	if ($this->dbh) {
	101	$this->close();
	102	}
7902aca2	103
9b9474d6	104	$dbh = DB::connect($this->dsn, true);
7902aca2	105
286fe80b	106	if (DB::isError($dbh)) {
701c9c6b	107	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	108	DB::errorMessage($dbh)));
9b9474d6	109	}
7902aca2	110
9b9474d6	111	$this->dbh = $dbh;
	112	return true;
	113	}
7902aca2	114
9b9474d6	115	/* Close the file and forget the filehandle */
	116	function close() {
	117	$this->dbh->disconnect();
	118	$this->dbh = false;
	119	}
7902aca2	120
9b9474d6	121	/* ========================== Public ======================== */
7902aca2	122
9b9474d6	123	/* Search the file */
	124	function &search($expr) {
	125	$ret = array();
	126	if(!$this->open()) {
7902aca2	127	return false;
9b9474d6	128	}
30e9932c	129
9b9474d6	130	/* To be replaced by advanded search expression parsing */
	131	if (is_array($expr)) {
	132	return;
	133	}
	134
	135	/* Make regexp from glob'ed expression */
	136	$expr = str_replace('?', '_', $expr);
	137	$expr = str_replace('*', '%', $expr);
	138	$expr = $this->dbh->quoteString($expr);
	139	$expr = "%$expr%";
	140
	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);
	145
	146	if (DB::isError($res)) {
701c9c6b	147	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	148	DB::errorMessage($res)));
9b9474d6	149	}
7902aca2	150
9b9474d6	151	while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	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));
9b9474d6	160	}
	161	return $ret;
	162	}
7902aca2	163
9b9474d6	164	/* Lookup alias */
	165	function &lookup($alias) {
	166	if (empty($alias)) {
7902aca2	167	return array();
9b9474d6	168	}
7902aca2	169
9b9474d6	170	$alias = strtolower($alias);
7902aca2	171
9b9474d6	172	if (!$this->open()) {
7902aca2	173	return false;
9b9474d6	174	}
7902aca2	175
2c90a9f2	176	$query = sprintf("SELECT * FROM %s WHERE owner='%s' AND LOWER(nickname)='%s'",
11f2b6ba	177	$this->table, $this->owner, $this->dbh->quoteString($alias));
7902aca2	178
9b9474d6	179	$res = $this->dbh->query($query);
7902aca2	180
9b9474d6	181	if (DB::isError($res)) {
701c9c6b	182	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	183	DB::errorMessage($res)));
9b9474d6	184	}
7902aca2	185
9b9474d6	186	if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	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);
9b9474d6	195	}
	196	return array();
	197	}
	198
	199	/* List all addresses */
	200	function &list_addr() {
	201	$ret = array();
	202	if (!$this->open()) {
7902aca2	203	return false;
9b9474d6	204	}
30e9932c	205
168c8dd0	206	if(isset($this->listing) && !$this->listing) {
30e9932c	207	return array();
	208	}
	209
7902aca2	210
9b9474d6	211	$query = sprintf("SELECT * FROM %s WHERE owner='%s'",
9b9474d6	212	$this->table, $this->owner);
7902aca2	213
9b9474d6	214	$res = $this->dbh->query($query);
	215
	216	if (DB::isError($res)) {
701c9c6b	217	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	218	DB::errorMessage($res)));
9b9474d6	219	}
7902aca2	220
9b9474d6	221	while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	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));
9b9474d6	230	}
	231	return $ret;
	232	}
7902aca2	233
9b9474d6	234	/* Add address */
	235	function add($userdata) {
	236	if (!$this->writeable) {
701c9c6b	237	return $this->set_error(_("Addressbook is read-only"));
9b9474d6	238	}
7902aca2	239
9b9474d6	240	if (!$this->open()) {
7902aca2	241	return false;
9b9474d6	242	}
7902aca2	243
9b9474d6	244	/* See if user exist already */
	245	$ret = $this->lookup($userdata['nickname']);
	246	if (!empty($ret)) {
b9bfd165	247	return $this->set_error(sprintf(_("User '%s' already exist"),
91be2362	248	$ret['nickname']));
9b9474d6	249	}
	250
	251	/* Create query */
	252	$query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
	253	"lastname, email, label) VALUES('%s','%s','%s'," .
	254	"'%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']) );
	261
	262	/* Do the insert */
7902aca2	263	$r = $this->dbh->simpleQuery($query);
9b9474d6	264	if ($r == DB_OK) {
	265	return true;
	266	}
7902aca2	267
9b9474d6	268	/* Fail */
701c9c6b	269	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	270	DB::errorMessage($r)));
9b9474d6	271	}
7902aca2	272
9b9474d6	273	/* Delete address */
	274	function remove($alias) {
	275	if (!$this->writeable) {
701c9c6b	276	return $this->set_error(_("Addressbook is read-only"));
9b9474d6	277	}
7902aca2	278
9b9474d6	279	if (!$this->open()) {
7902aca2	280	return false;
9b9474d6	281	}
7902aca2	282
9b9474d6	283	/* Create query */
	284	$query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
	285	$this->table, $this->owner);
7902aca2	286
9b9474d6	287	$sepstr = '';
9b9474d6	288	while (list($undef, $nickname) = each($alias)) {
16a973d7	289	$query .= sprintf("%s nickname='%s' ", $sepstr,
7902aca2	290	$this->dbh->quoteString($nickname));
91be2362	291	$sepstr = 'OR';
9b9474d6	292	}
9b9474d6	293	$query .= ')';
7902aca2	294
9b9474d6	295	/* Delete entry */
	296	$r = $this->dbh->simpleQuery($query);
	297	if ($r == DB_OK) {
	298	return true;
	299	}
7902aca2	300
9b9474d6	301	/* Fail */
9b9474d6	302	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	303	DB::errorMessage($r)));
9b9474d6	304	}
7902aca2	305
9b9474d6	306	/* Modify address */
	307	function modify($alias, $userdata) {
	308	if (!$this->writeable) {
701c9c6b	309	return $this->set_error(_("Addressbook is read-only"));
9b9474d6	310	}
7902aca2	311
9b9474d6	312	if (!$this->open()) {
7902aca2	313	return false;
9b9474d6	314	}
7902aca2	315
9b9474d6	316	/* See if user exist */
	317	$ret = $this->lookup($alias);
	318	if (empty($ret)) {
701c9c6b	319	return $this->set_error(sprintf(_("User '%s' does not exist"),
7902aca2	320	$alias));
9b9474d6	321	}
	322
	323	/* Create query */
	324	$query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
	325	"lastname='%s', email='%s', label='%s' ".
	326	"WHERE owner='%s' AND nickname='%s'",
	327	$this->table,
	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']),
	333	$this->owner,
	334	$this->dbh->quoteString($alias) );
	335
	336	/* Do the insert */
	337	$r = $this->dbh->simpleQuery($query);
	338	if ($r == DB_OK) {
	339	return true;
	340	}
7902aca2	341
9b9474d6	342	/* Fail */
	343	return $this->set_error(sprintf(_("Database error: %s"),
	344	DB::errorMessage($r)));
	345	}
	346	} /* End of class abook_database */
7902aca2	347
9b9474d6	348	?>