[squirrelmail.git] / functions / abook_database.php

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