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