PageRenderTime 56ms CodeModel.GetById 44ms app.highlight 8ms RepoModel.GetById 1ms app.codeStats 0ms

/pear/PDB/tags/0.0.3/PDB/Common.php

http://digg.googlecode.com/
PHP | 501 lines | 133 code | 30 blank | 338 comment | 9 complexity | 6ed9f7c61299dc9ef4c36e1a9053126f MD5 | raw file
  1<?php
  2
  3/**
  4 * Base PDB class
  5 *
  6 * PHP version 5.2+
  7 *
  8 * Copyright (c) 2007, 2008, Digg, Inc.
  9 * 
 10 * All rights reserved.
 11 * 
 12 * Redistribution and use in source and binary forms, with or without 
 13 * modification, are permitted provided that the following conditions are met:
 14 *
 15 *  - Redistributions of source code must retain the above copyright notice,
 16 *    this list of conditions and the following disclaimer.
 17 *  - Redistributions in binary form must reproduce the above copyright notice,
 18 *    this list of conditions and the following disclaimer in the documentation
 19 *    and/or other materials provided with the distribution.
 20 *  - Neither the name of the Digg, INc. nor the names of its contributors 
 21 *    may be used to endorse or promote products derived from this software 
 22 *    without specific prior written permission.
 23 *
 24 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" 
 25 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 
 26 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 27 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE 
 28 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
 29 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF 
 30 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS 
 31 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN 
 32 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 33 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
 34 * POSSIBILITY OF SUCH DAMAGE.
 35 *
 36 * @category   DB
 37 * @package    PDB
 38 * @author     Joe Stump <joe@joestump.net> 
 39 * @copyright  2007-2008 (c) Digg.com 
 40 * @license    http://tinyurl.com/42zef New BSD License
 41 * @version    CVS: $Id:$
 42 * @link       http://www.php.net/pdo
 43 * @link       http://pear.php.net/package/PDB
 44 * @filesource
 45 */
 46
 47require_once 'PDB/Exception.php';
 48require_once 'PDB/Result.php';
 49
 50/**
 51 * Base PDB class
 52 *
 53 * @category   DB
 54 * @package    PDB
 55 * @author     Joe Stump <joe@joestump.net> 
 56 * @copyright  2007-2008 (c) Digg.com 
 57 * @license    http://tinyurl.com/42zef New BSD License
 58 * @version    Release: @package_version@
 59 * @link       http://pear.php.net/package/PDB
 60 */
 61abstract class PDB_Common 
 62{
 63    /**
 64     * The PDO connection
 65     * 
 66     * Due to various issues with PDO (e.g. the inability to disconnect)
 67     * we use the decorator pattern to envelope PDO with extra
 68     * functionality. 
 69     * 
 70     * @var object $pdo Instance of PDO
 71     * @link http://us.php.net/pdo
 72     * @see PDB_Common::__call()
 73     */
 74    protected $pdo = null;
 75
 76    /**
 77     * PDO DSN
 78     *
 79     * @access protected
 80     * @var string $dsn PDO DSN (e.g. mysql:host=127.0.0.1;dbname=foo)
 81     */
 82    protected $dsn = '';
 83
 84    /**
 85     * Username for DB connection
 86     *
 87     * @access protected
 88     * @var string $username DB username
 89     */
 90    protected $username = ''; 
 91
 92    /**
 93     * Password for DB connection
 94     *
 95     * @access protected
 96     * @var string $password DB password
 97     */
 98    protected $password = '';
 99
100    /**
101     * PDO/Driver options
102     *
103     * @access protected
104     * @var array $options PDO/Driver options
105     * @link http://us.php.net/manual/en/pdo.constants.php
106     * @link http://us.php.net/manual/en/pdo.drivers.php
107     */
108    protected $options = array();
109
110    /**
111     * Default fetch mode
112     *
113     * @access      private
114     * @var         int         $fetchMode
115     */
116    public $fetchMode = PDO::FETCH_NUM;
117
118    /**
119     * Constructor
120     *
121     * @param string $dsn      The PDO DSN
122     * @param string $username The DB's username
123     * @param string $password The DB's password
124     * @param array  $options  PDO/driver options array
125     *
126     * @return void
127     * @see PDB_Common::connect(), PDB_Common::$dsn, PDB_Common::$username
128     * @see PDB_Common::$password, PDB_Common::$options
129     */
130    public function __construct($dsn, 
131                                $username = '', 
132                                $password = '', 
133                                $options = array())
134    {
135        $this->dsn      = $dsn;
136        $this->username = $username;
137        $this->password = $password;
138        $this->options  = $options;
139        $this->connect();
140    }
141
142    /**
143     * Connect to the database
144     *
145     * @return void
146     * @see PDB_Common::$dsn, PDB_Common::$username
147     * @see PDB_Common::$password, PDB_Common::$options
148     * @see PDB_Common::setAttribute
149     */
150    public function connect()
151    {
152        $this->pdo = new PDO($this->dsn, 
153                             $this->username, 
154                             $this->password, 
155                             $this->options);
156
157        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
158    }
159
160    /**
161     * Reconnect to the database
162     *
163     * This reconnects to the database with the given parameters from
164     * before we either disconnected or lost the connection. This is useful
165     * for when MySQL (and others probably) servers "go away".
166     * 
167     * @see PDB_Common::disconnect(), PDB_Common::connect()
168     * @return void
169     */
170    public function reconnect()
171    {
172        $this->disconnect();
173        $this->connect();
174    }
175
176    /**
177     * Disconnect from the DB
178     *
179     * @return void
180     */
181    public function disconnect()
182    {
183        $this->pdo = null;
184    }
185
186    /**
187     * Implement decorator pattern
188     *
189     * Originally {@link PDB} was extended from PDO, but this kept us from
190     * implementing valid {@link PDB_Common::disconnect()} and 
191     * {@link PDB_Common::reconnect()} methods, which were needed for other
192     * nice functionality.
193     *
194     * As a result we use {@link PDB_Common::__call()} to implement the basic
195     * decorator pattern. Everything listed below should work without issues.
196     *
197     * @param string $function Name of function to run
198     * @param array  $args     Function's arguments
199     *
200     * @method bool beginTransaction()
201     * @method bool commit()
202     * @method string errorCode()
203     * @method array errorInfo()
204     * @method int exec(string $statement)
205     * @method mixed getAttribute(int $attribute)
206     * @method string lastInsertId([string $name])
207     * @method PDOStatement prepare(string $statement [, array $driver_options])
208     * @method string quote(string $string [, int $parameter_type])
209     * @method bool rollBack()
210     * @return mixed
211     */
212    public function __call($function, array $args = array()) 
213    {
214        if (is_null($this->pdo)) {
215            throw new PDB_Exception('Not connected to DB');
216        }
217
218        return call_user_func_array(array($this->pdo, $function), $args);
219    }
220
221    /**
222     * Query the database
223     *
224     * <code>
225     * <?php
226     * 
227     * require_once 'PDB.php';
228     *
229     * $db = PDB::connect('mysql:host=127.0.0.1;dbname=foo', 'user', 'pass'); 
230     * $db->setFetchMode(PDO::FETCH_OBJECT);
231     *
232     * $sql = 'SELECT * 
233     *         FROM items
234     *         WHERE promoted = ? AND
235     *               userid = ?';
236     * 
237     * $result = $db->query($sql, array(1, (int)$_GET['userid']));
238     *
239     * // Notice that {@link PDB_Result} supports object iteration just like
240     * // PDOStatement does since it extends from it.
241     * foreach ($result as $row) {
242     *     echo '<a href="' . $row->url . '">' . $row->title . '</a>' . "\n";
243     * }
244     *
245     * ?>
246     * </code> 
247     *
248     * @param string $sql  The query
249     * @param array  $args The query arguments
250     *
251     * @return object Instance of {@link PDB_Result}
252     * @throws {@link PDB_Exception} on failure
253     * @link http://us3.php.net/manual/en/class.pdostatement.php
254     * @link http://us3.php.net/manual/en/pdostatement.bindparam.php
255     */
256    public function query($sql, array $args = array())
257    {
258        try {
259            $stmt = $this->prepare($sql, array(
260                PDO::ATTR_STATEMENT_CLASS => array(
261                    'PDB_Result', array($this->pdo, $this->fetchMode)
262                )
263            ));
264
265            if (is_array($args)) {
266                $cnt = count($args);
267                if ($cnt > 0) {
268                    foreach ($args as $key => $value) {
269                        $param  = (is_int($key) ? ($key + 1) : $key);
270                        $result = $stmt->bindParam($param, $args[$key]);
271                    }
272                }
273            }
274
275            $stmt->execute();
276            return $stmt;
277        } catch (PDOException $error) {
278            throw new PDB_Exception($error->getMessage(), (int) $error->getCode());
279        }
280    }
281
282    /**
283     * Fetch a single row
284     *
285     * <code>
286     * <?php
287     * 
288     * require_once 'PDB.php';
289     *
290     * $db = PDB::connect('mysql:host=127.0.0.1;dbname=foo', 'user', 'pass'); 
291     * $db->setFetchMode(PDO::FETCH_OBJECT);
292     *
293     * $sql = 'SELECT * 
294     *         FROM users
295     *         WHERE userid = ?';
296     *
297     * $user = $db->getRow($sql, array((int)$_GET['userid']));
298     * echo 'Welcome back, ' . $user->username . '!';
299     *
300     * ?>
301     * </code>
302     * 
303     * @param string  $sql       The query to run
304     * @param array   $params    The query parameter values
305     * @param integer $fetchMode The fetch mode for query
306     *
307     * @see PDB_Common::query(), PDB_Result
308     * @return array
309     */
310    public function getRow($sql, 
311                           array $params = array(), 
312                           $fetchMode = null)
313    {
314        if (is_null($fetchMode)) {
315            $fetchMode = $this->fetchMode;
316        }
317
318        $result = $this->query($sql, $params);
319        return $result->fetchRow($fetchMode);
320    }
321
322    /**
323     * Fetch a single column
324     *
325     * <code>
326     * <?php
327     * 
328     * require_once 'PDB.php';
329     *
330     * $db  = PDB::connect('mysql:host=127.0.0.1;dbname=foo', 'user', 'pass'); 
331     * $sql = 'SELECT friendid 
332     *         FROM friends
333     *         WHERE userid = ?';
334     *
335     * $friends = $db->getCol($sql, 0, array((int)$_GET['userid']));
336     * if (in_array($_SESSION['userid'], $friends)) {
337     *    echo 'You are friends with this user!';
338     * }
339     *
340     * ?>
341     * </code>
342     * 
343     * @param string  $sql    The query to run
344     * @param integer $col    The column number to fetch (zero-based)
345     * @param array   $params The query parameter values
346     *
347     * @see PDB_Common::query(), PDB_Result
348     * @return array
349     */
350    public function getCol($sql, $col = 0, array $params = array())
351    {
352        $result = $this->query($sql, $params);
353        $ret    = array();
354        while ($row = $result->fetchRow(PDO::FETCH_NUM)) {
355            $ret[] = $row[$col];
356        }
357
358        return $ret;
359    }
360
361    /**
362     * Fetch all records in query as array
363     *
364     * This method will fetch all records from a given query into a 
365     * numerically indexed array (e.g. $result[0] is the first record).
366     *
367     * <code>
368     * <?php
369     * 
370     * require_once 'PDB.php';
371     *
372     * $db = PDB::connect('mysql:host=127.0.0.1;dbname=foo', 'user', 'pass'); 
373     * $db->setFetchMode(PDO::FETCH_OBJECT);
374     * 
375     * $sql = 'SELECT * 
376     *         FROM users
377     *         WHERE type = ?';
378     *
379     * $students = $db->getAll($sql, array('student'));
380     * foreach ($students as $student) {
381     *     echo $student->firstname . "\n";
382     * }
383     *
384     * ?>
385     * </code>
386     *
387     * @param string  $sql       The query to run
388     * @param array   $params    The query parameter values
389     * @param integer $fetchMode The fetch mode for query
390     *
391     * @return array
392     * @see PDB_Result, PDB_Common::query()
393     */
394    public function getAll($sql, 
395                           array $params = array(), 
396                           $fetchMode = null) 
397    {
398        if (is_null($fetchMode)) {
399            $fetchMode = $this->fetchMode;
400        }
401
402        $result = $this->query($sql, $params);
403        $ret    = array();
404        while ($row = $result->fetchRow($fetchMode)) {
405            $ret[] = $row;
406        }
407
408        return $ret;
409    }
410
411    /**
412     * Get a single field
413     *
414     * This will fetch a single value from the first row's first
415     * column. 
416     *
417     * <code>
418     * <?php
419     * 
420     * require_once 'PDB.php';
421     *
422     * $db  = PDB::connect('mysql:host=127.0.0.1;dbname=foo', 'user', 'pass'); 
423     * $sql = 'SELECT COUNT(*) AS total
424     *         FROM users
425     *         WHERE type = ?';
426     *
427     * $total = $db->getOne($sql, array('student'));
428     * if (!$total) {
429     *     echo 'No students!';
430     * }
431     *
432     * ?>
433     * </code>
434     *
435     * @param string $sql    The query to run
436     * @param array  $params The query parameter values
437     *
438     * @see PDB_Common::query(), PDB_Result::fetchRow()
439     * @return mixed The value of the first row/column
440     */
441    public function getOne($sql, array $params = array()) 
442    {
443        $result = $this->query($sql, $params);
444        $row    = $result->fetchRow(PDO::FETCH_NUM);
445        return $row[0];
446    }
447
448    /**
449     * Set the fetch mode for all queries
450     *
451     * This should be set to one of PDO's fetch modes. Valid values include:
452     *  - PDO::FETCH_LAZY
453     *  - PDO::FETCH_ASSOC
454     *  - PDO::FETCH_NAMED
455     *  - PDO::FETCH_NUM
456     *  - PDO::FETCH_BOTH
457     *  - PDO::FETCH_OBJ
458     *
459     * @param integer $mode The DB fetch mode
460     *
461     * @throws UnexpectedArgumentException on invalid modes
462     * @access public
463     * @return void
464     */
465    public function setFetchMode($mode)
466    {
467        switch ($mode) {
468        case PDO::FETCH_LAZY:
469        case PDO::FETCH_ASSOC:
470        case PDO::FETCH_NAMED:
471        case PDO::FETCH_NUM:
472        case PDO::FETCH_BOTH:
473        case PDO::FETCH_OBJ:
474            $this->fetchMode = $mode;
475            break;
476        default:
477            throw UnexpectedArgumentException('Invalid mode');
478        }
479    }
480
481    /**
482     * Set an attribute
483     *
484     * @param integer $attribute The attribute to set
485     * @param mixed   $value     The attribute's value
486     *
487     * @link http://us.php.net/manual/en/pdo.setattribute.php
488     * @return true False if something failed to set
489     */
490    public function setAttribute($attribute, $value)
491    {
492        if ($this->pdo->setAttribute($attribute, $value)) {
493            $this->options[$attribute] = $value;
494            return true;
495        }
496
497        return false;
498    }
499}
500
501?>