[squirrelmail.git] / functions / abook_database.php

<?php {
   
/**
 * abook_database.php
 *
 * Copyright (c) 1999-2001 The Squirrelmail Development 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.
 *
 * $Id$
 */
   
/*****************************************************************/
/*** THIS FILE NEEDS TO HAVE ITS FORMATTING FIXED!!!           ***/
/*** PLEASE DO SO AND REMOVE THIS COMMENT SECTION.             ***/
/***    + Base level indent should begin at left margin, as    ***/
/***      the require_once below looks.                        ***/
/***    + All identation should consist of four space blocks   ***/
/***    + Tab characters are evil.                             ***/
/***    + all comments should use "slash-star ... star-slash"  ***/
/***      style -- no pound characters, no slash-slash style   ***/
/***    + FLOW CONTROL STATEMENTS (if, while, etc) SHOULD      ***/
/***      ALWAYS USE { AND } CHARACTERS!!!                     ***/
/***    + Please use ' instead of ", when possible. Note "     ***/
/***      should always be used in _( ) function calls.        ***/
/*** Thank you for your help making the SM code more readable. ***/
/*****************************************************************/

require_once('DB.php');
   
   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'];

            $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) || DB::isWarning($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 nickname='%s'",
                          $this->table, $this->owner, $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;

         $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
7902aca2	1	<?php {
7902aca2	2
35586184	3	/**
	4	* abook_database.php
	5	*
	6	* Copyright (c) 1999-2001 The Squirrelmail Development Team
	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	*
	30	* $Id$
	31	*/
7902aca2	32
35586184	33	/*****************************************************************/
	34	/* THIS FILE NEEDS TO HAVE ITS FORMATTING FIXED!!! */
	35	/* PLEASE DO SO AND REMOVE THIS COMMENT SECTION. */
	36	/* + Base level indent should begin at left margin, as */
	37	/* the require_once below looks. */
	38	/* + All identation should consist of four space blocks */
	39	/* + Tab characters are evil. */
	40	/* + all comments should use "slash-star ... star-slash" */
	41	/* style -- no pound characters, no slash-slash style */
	42	/* + FLOW CONTROL STATEMENTS (if, while, etc) SHOULD */
	43	/* ALWAYS USE { AND } CHARACTERS!!! */
	44	/* + Please use ' instead of ", when possible. Note " */
	45	/* should always be used in _( ) function calls. */
	46	/* Thank you for your help making the SM code more readable. */
	47	/*****************************************************************/
	48
	49	require_once('DB.php');
7902aca2	50
7902aca2	51	class abook_database extends addressbook_backend {
b9bfd165	52	var $btype = 'local';
b9bfd165	53	var $bname = 'database';
7902aca2	54
b9bfd165	55	var $dsn = '';
	56	var $table = '';
	57	var $owner = '';
7902aca2	58	var $dbh = false;
	59
	60	var $writeable = true;
	61
	62	// ========================== Private =======================
	63
	64	// Constructor
	65	function abook_database($param) {
701c9c6b	66	$this->sname = _("Personal address book");
7902aca2	67
7902aca2	68	if(is_array($param)) {
91be2362	69	if(empty($param['dsn']) \|\|
	70	empty($param['table']) \|\|
	71	empty($param['owner']))
	72	return $this->set_error('Invalid parameters');
7902aca2	73
91be2362	74	$this->dsn = $param['dsn'];
	75	$this->table = $param['table'];
	76	$this->owner = $param['owner'];
7902aca2	77
91be2362	78	if(!empty($param['name']))
91be2362	79	$this->sname = $param['name'];
7902aca2	80
91be2362	81	if(isset($param['writeable']))
91be2362	82	$this->writeable = $param['writeable'];
7902aca2	83
	84	$this->open(true);
	85	} else {
91be2362	86	return $this->set_error('Invalid argument to constructor');
7902aca2	87	}
	88	}
	89
	90
	91	// Open the database. New connection if $new is true
	92	function open($new = false) {
91be2362	93	$this->error = '';
7902aca2	94
	95	// Return true is file is open and $new is unset
	96	if($this->dbh && !$new)
	97	return true;
	98
	99	// Close old file, if any
	100	if($this->dbh) $this->close();
	101
	102	$dbh = DB::connect($this->dsn, true);
	103
	104	if(DB::isError($dbh) \|\| DB::isWarning($dbh))
701c9c6b	105	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	106	DB::errorMessage($dbh)));
	107
	108	$this->dbh = $dbh;
	109	return true;
	110	}
	111
	112	// Close the file and forget the filehandle
	113	function close() {
	114	$this->dbh->disconnect();
	115	$this->dbh = false;
	116	}
	117
	118	// ========================== Public ========================
	119
	120	// Search the file
	121	function &search($expr) {
	122	$ret = array();
	123	if(!$this->open())
	124	return false;
	125
	126	// To be replaced by advanded search expression parsing
	127	if(is_array($expr)) return;
	128
	129	// Make regexp from glob'ed expression
16a973d7	130	$expr = str_replace('?', '_', $expr);
16a973d7	131	$expr = str_replace('*', '%', $expr);
7902aca2	132	$expr = $this->dbh->quoteString($expr);
	133	$expr = "%$expr%";
	134
16a973d7	135	$query = sprintf("SELECT * FROM %s WHERE owner='%s' AND " .
16a973d7	136	"(firstname LIKE '%s' OR lastname LIKE '%s')",
7902aca2	137	$this->table, $this->owner, $expr, $expr);
	138	$res = $this->dbh->query($query);
	139
	140	if(DB::isError($res))
701c9c6b	141	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	142	DB::errorMessage($res)));
	143
	144	while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	145	array_push($ret, array('nickname' => $row['nickname'],
	146	'name' => "$row[firstname] $row[lastname]",
	147	'firstname' => $row['firstname'],
	148	'lastname' => $row['lastname'],
	149	'email' => $row['email'],
	150	'label' => $row['label'],
	151	'backend' => $this->bnum,
	152	'source' => &$this->sname));
7902aca2	153	}
	154	return $ret;
	155	}
	156
	157	// Lookup alias
	158	function &lookup($alias) {
	159	if(empty($alias))
	160	return array();
	161
	162	$alias = strtolower($alias);
	163
	164	if(!$this->open())
	165	return false;
	166
16a973d7	167	$query = sprintf("SELECT * FROM %s WHERE owner='%s' AND nickname='%s'",
7902aca2	168	$this->table, $this->owner, $alias);
	169
	170	$res = $this->dbh->query($query);
	171
	172	if(DB::isError($res))
701c9c6b	173	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	174	DB::errorMessage($res)));
	175
	176	if ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	177	return array('nickname' => $row['nickname'],
	178	'name' => "$row[firstname] $row[lastname]",
	179	'firstname' => $row['firstname'],
	180	'lastname' => $row['lastname'],
	181	'email' => $row['email'],
	182	'label' => $row['label'],
	183	'backend' => $this->bnum,
	184	'source' => &$this->sname);
7902aca2	185	}
	186
	187	return array();
	188	}
	189
	190	// List all addresses
	191	function &list_addr() {
	192	$ret = array();
	193	if(!$this->open())
	194	return false;
	195
16a973d7	196	$query = sprintf("SELECT * FROM %s WHERE owner='%s'",
7902aca2	197	$this->table, $this->owner);
	198
	199	$res = $this->dbh->query($query);
	200
	201	if(DB::isError($res))
701c9c6b	202	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	203	DB::errorMessage($res)));
	204
	205	while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) {
91be2362	206	array_push($ret, array('nickname' => $row['nickname'],
	207	'name' => "$row[firstname] $row[lastname]",
	208	'firstname' => $row['firstname'],
	209	'lastname' => $row['lastname'],
	210	'email' => $row['email'],
	211	'label' => $row['label'],
	212	'backend' => $this->bnum,
	213	'source' => &$this->sname));
7902aca2	214	}
	215	return $ret;
	216	}
	217
	218	// Add address
	219	function add($userdata) {
91be2362	220	if(!$this->writeable)
701c9c6b	221	return $this->set_error(_("Addressbook is read-only"));
7902aca2	222
	223	if(!$this->open())
	224	return false;
	225
	226	// See if user exist already
91be2362	227	$ret = $this->lookup($userdata['nickname']);
7902aca2	228	if(!empty($ret))
b9bfd165	229	return $this->set_error(sprintf(_("User '%s' already exist"),
91be2362	230	$ret['nickname']));
7902aca2	231
7902aca2	232	// Create query
16a973d7	233	$query = sprintf("INSERT INTO %s (owner, nickname, firstname, " .
91be2362	234	"lastname, email, label) VALUES('%s','%s','%s'," .
7902aca2	235	"'%s','%s','%s')",
7902aca2	236	$this->table, $this->owner,
91be2362	237	$this->dbh->quoteString($userdata['nickname']),
	238	$this->dbh->quoteString($userdata['firstname']),
	239	$this->dbh->quoteString($userdata['lastname']),
	240	$this->dbh->quoteString($userdata['email']),
	241	$this->dbh->quoteString($userdata['label']) );
7902aca2	242
	243	// Do the insert
	244	$r = $this->dbh->simpleQuery($query);
	245	if($r == DB_OK) return true;
	246
	247	// Fail
701c9c6b	248	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	249	DB::errorMessage($r)));
	250	}
	251
	252	// Delete address
	253	function remove($alias) {
	254	if(!$this->writeable)
701c9c6b	255	return $this->set_error(_("Addressbook is read-only"));
7902aca2	256
	257	if(!$this->open())
	258	return false;
	259
	260	// Create query
16a973d7	261	$query = sprintf("DELETE FROM %s WHERE owner='%s' AND (",
7902aca2	262	$this->table, $this->owner);
7902aca2	263
91be2362	264	$sepstr = '';
7902aca2	265	while(list($undef, $nickname) = each($alias)) {
16a973d7	266	$query .= sprintf("%s nickname='%s' ", $sepstr,
7902aca2	267	$this->dbh->quoteString($nickname));
91be2362	268	$sepstr = 'OR';
7902aca2	269	}
91be2362	270	$query .= ')';
7902aca2	271
	272	// Delete entry
	273	$r = $this->dbh->simpleQuery($query);
	274	if($r == DB_OK) return true;
	275
	276	// Fail
701c9c6b	277	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	278	DB::errorMessage($r)));
	279	}
	280
	281	// Modify address
	282	function modify($alias, $userdata) {
	283	if(!$this->writeable)
701c9c6b	284	return $this->set_error(_("Addressbook is read-only"));
7902aca2	285
	286	if(!$this->open())
	287	return false;
	288
	289	// See if user exist
	290	$ret = $this->lookup($alias);
	291	if(empty($ret))
701c9c6b	292	return $this->set_error(sprintf(_("User '%s' does not exist"),
7902aca2	293	$alias));
	294
	295	// Create query
	296	$query = sprintf("UPDATE %s SET nickname='%s', firstname='%s', ".
	297	"lastname='%s', email='%s', label='%s' ".
	298	"WHERE owner='%s' AND nickname='%s'",
	299	$this->table,
91be2362	300	$this->dbh->quoteString($userdata['nickname']),
	301	$this->dbh->quoteString($userdata['firstname']),
	302	$this->dbh->quoteString($userdata['lastname']),
	303	$this->dbh->quoteString($userdata['email']),
	304	$this->dbh->quoteString($userdata['label']),
7902aca2	305	$this->owner,
	306	$this->dbh->quoteString($alias) );
	307
	308	// Do the insert
	309	$r = $this->dbh->simpleQuery($query);
	310	if($r == DB_OK) return true;
	311
	312	// Fail
701c9c6b	313	return $this->set_error(sprintf(_("Database error: %s"),
7902aca2	314	DB::errorMessage($r)));
	315	}
	316	} // End of class abook_database
	317
	318	} ?>