root/trunk/system/libraries/Database.php

<?php
/**
 * Provides database access in a platform agnostic way, using simple query building blocks.
 *
 * $Id$
 *
 * @package    Core
 * @author     Kohana Team
 * @copyright  (c) 2007-2008 Kohana Team
 * @license    http://kohanaphp.com/license.html
 */
class Database_Core {

    // Database instances
    public static $instances = array();

    // Global benchmark
    public static $benchmarks = array();

    // Configuration
    protected $config = array
    (
        'benchmark'     => TRUE,
        'persistent'    => FALSE,
        'connection'    => '',
        'character_set' => 'utf8',
        'table_prefix'  => '',
        'object'        => TRUE,
        'cache'         => FALSE,
        'escape'        => TRUE,
    );

    // Database driver object
    protected $driver;
    protected $link;

    // Un-compiled parts of the SQL query
    protected $select     = array();
    protected $set        = array();
    protected $from       = array();
    protected $join       = array();
    protected $where      = array();
    protected $orderby    = array();
    protected $order      = array();
    protected $groupby    = array();
    protected $having     = array();
    protected $distinct   = FALSE;
    protected $limit      = FALSE;
    protected $offset     = FALSE;
    protected $last_query = '';

    /**
     * Returns a singleton instance of Database.
     *
     * @param   mixed   configuration array or DSN
     * @return  Database_Core
     */
    public static function & instance($name = 'default', $config = NULL)
    {
        if ( ! isset(Database::$instances[$name]))
        {
            // Create a new instance
            Database::$instances[$name] = new Database($config === NULL ? $name : $config);
        }

        return Database::$instances[$name];
    }

    /**
     * Returns the name of a given database instance.
     *
     * @param   Database  instance of Database
     * @return  string
     */
    public static function instance_name(Database $db)
    {
        return array_search($db, Database::$instances, TRUE);
    }

    /**
     * Sets up the database configuration, loads the Database_Driver.
     *
     * @throws  Kohana_Database_Exception
     */
    public function __construct($config = array())
    {
        if (empty($config))
        {
            // Load the default group
            $config = Kohana::config('database.default');
        }
        elseif (is_array($config) AND count($config) > 0)
        {
            if ( ! array_key_exists('connection', $config))
            {
                $config = array('connection' => $config);
            }
        }
        elseif (is_string($config))
        {
            // The config is a DSN string
            if (strpos($config, '://') !== FALSE)
            {
                $config = array('connection' => $config);
            }
            // The config is a group name
            else
            {
                $name = $config;

                // Test the config group name
                if (($config = Kohana::config('database.'.$config)) === NULL)
                    throw new Kohana_Database_Exception('database.undefined_group', $name);
            }
        }

        // Merge the default config with the passed config
        $this->config = array_merge($this->config, $config);

        if (is_string($this->config['connection']))
        {
            // Make sure the connection is valid
            if (strpos($this->config['connection'], '://') === FALSE)
                throw new Kohana_Database_Exception('database.invalid_dsn', $this->config['connection']);

            // Parse the DSN, creating an array to hold the connection parameters
            $db = array
            (
                'type'     => FALSE,
                'user'     => FALSE,
                'pass'     => FALSE,
                'host'     => FALSE,
                'port'     => FALSE,
                'socket'   => FALSE,
                'database' => FALSE
            );

            // Get the protocol and arguments
            list ($db['type'], $connection) = explode('://', $this->config['connection'], 2);

            if (strpos($connection, '@') !== FALSE)
            {
                // Get the username and password
                list ($db['pass'], $connection) = explode('@', $connection, 2);
                // Check if a password is supplied
                $logindata = explode(':', $db['pass'], 2);
                $db['pass'] = (count($logindata) > 1) ? $logindata[1] : '';
                $db['user'] = $logindata[0];

                // Prepare for finding the database
                $connection = explode('/', $connection);

                // Find the database name
                $db['database'] = array_pop($connection);

                // Reset connection string
                $connection = implode('/', $connection);

                // Find the socket
                if (preg_match('/^unix\([^)]++\)/', $connection))
                {
                    // This one is a little hairy: we explode based on the end of
                    // the socket, removing the 'unix(' from the connection string
                    list ($db['socket'], $connection) = explode(')', substr($connection, 5), 2);
                }
                elseif (strpos($connection, ':') !== FALSE)
                {
                    // Fetch the host and port name
                    list ($db['host'], $db['port']) = explode(':', $connection, 2);
                }
                else
                {
                    $db['host'] = $connection;
                }
            }
            else
            {
                // File connection
                $connection = explode('/', $connection);

                // Find database file name
                $db['database'] = array_pop($connection);

                // Find database directory name
                $db['socket'] = implode('/', $connection).'/';
            }

            // Reset the connection array to the database config
            $this->config['connection'] = $db;
        }
        // Set driver name
        $driver = 'Database_'.ucfirst($this->config['connection']['type']).'_Driver';

        // Load the driver
        if ( ! Kohana::auto_load($driver))
            throw new Kohana_Database_Exception('core.driver_not_found', $this->config['connection']['type'], get_class($this));

        // Initialize the driver
        $this->driver = new $driver($this->config);

        // Validate the driver
        if ( ! ($this->driver instanceof Database_Driver))
            throw new Kohana_Database_Exception('core.driver_implements', $this->config['connection']['type'], get_class($this), 'Database_Driver');

        Kohana::log('debug', 'Database Library initialized');
    }

    /**
     * Simple connect method to get the database queries up and running.
     *
     * @return  void
     */
    public function connect()
    {
        // A link can be a resource or an object
        if ( ! is_resource($this->link) AND ! is_object($this->link))
        {
            $this->link = $this->driver->connect();
            if ( ! is_resource($this->link) AND ! is_object($this->link))
                throw new Kohana_Database_Exception('database.connection', $this->driver->show_error());

            // Clear password after successful connect
            $this->config['connection']['pass'] = NULL;
        }
    }

    /**
     * Runs a query into the driver and returns the result.
     *
     * @param   string  SQL query to execute
     * @return  Database_Result
     */
    public function query($sql = '')
    {
        if ($sql == '') return FALSE;

        // No link? Connect!
        $this->link or $this->connect();

        // Start the benchmark
        $start = microtime(TRUE);

        if (func_num_args() > 1) //if we have more than one argument ($sql)
        {
            $argv = func_get_args();
            $binds = (is_array(next($argv))) ? current($argv) : array_slice($argv, 1);
        }

        // Compile binds if needed
        if (isset($binds))
        {
            $sql = $this->compile_binds($sql, $binds);
        }

        // Fetch the result
        $result = $this->driver->query($this->last_query = $sql);

        // Stop the benchmark
        $stop = microtime(TRUE);

        if ($this->config['benchmark'] == TRUE)
        {
            // Benchmark the query
            self::$benchmarks[] = array('query' => $sql, 'time' => $stop - $start, 'rows' => count($result));
        }

        return $result;
    }

    /**
     * Selects the column names for a database query.
     *
     * @param   string  string or array of column names to select
     * @return  Database_Core  This Database object.
     */
    public function select($sql = '*')
    {
        if (func_num_args() > 1)
        {
            $sql = func_get_args();
        }
        elseif (is_string($sql))
        {
            $sql = explode(',', $sql);
        }
        else
        {
            $sql = (array) $sql;
        }

        foreach ($sql as $val)
        {
            if (($val = trim($val)) === '') continue;

            if (strpos($val, '(') === FALSE AND $val !== '*')
            {
                if (preg_match('/^DISTINCT\s++(.+)$/i', $val, $matches))
                {
                    $val            = $this->config['table_prefix'].$matches[1];
                    $this->distinct = TRUE;
                }
                else
                {
                    $val = (strpos($val, '.') !== FALSE) ? $this->config['table_prefix'].$val : $val;
                }

                $val = $this->driver->escape_column($val);
            }

            $this->select[] = $val;
        }

        return $this;
    }

    /**
     * Selects the from table(s) for a database query.
     *
     * @param   string  string or array of tables to select
     * @return  Database_Core  This Database object.
     */
    public function from($sql)
    {
        if (func_num_args() > 1)
        {
            $sql = func_get_args();
        }
        elseif (is_string($sql))
        {
            $sql = explode(',', $sql);
        }
        else
        {
            $sql = (array) $sql;
        }

        foreach ($sql as $val)
        {
            if (($val = trim($val)) === '') continue;

            $this->from[] = $this->config['table_prefix'].$val;
        }

        return $this;
    }

    /**
     * Generates the JOIN portion of the query.
     *
     * @param   string        table name
     * @param   string|array  where key or array of key => value pairs
     * @param   string        where value
     * @param   string        type of join
     * @return  Database_Core        This Database object.
     */
    public function join($table, $key, $value = NULL, $type = '')
    {
        $join = array();

        if ( ! empty($type))
        {
            $type = strtoupper(trim($type));

            if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE))
            {
                $type = '';
            }
            else
            {
                $type .= ' ';
            }
        }

        $cond = array();
        $keys  = is_array($key) ? $key : array($key => $value);
        foreach ($keys as $key => $value)
        {
            $key    = (strpos($key, '.') !== FALSE) ? $this->config['table_prefix'].$key : $key;
            $cond[] = $this->driver->where($key, $this->driver->escape_column($this->config['table_prefix'].$value), 'AND ', count($cond), FALSE);
        }

        if( ! is_array($this->join)) { $this->join = array(); }

        foreach ((array) $table as $t)
        {
            $join['tables'][] = $this->driver->escape_column($this->config['table_prefix'].$t);
        }

        $join['conditions'] = '('.trim(implode(' ', $cond)).')';
        $join['type'] = $type;

        $this->join[] = $join;

        return $this;
    }


    /**
     * Selects the where(s) for a database query.
     *
     * @param   string|array  key name or array of key => value pairs
     * @param   string        value to match with key
     * @param   boolean       disable quoting of WHERE clause
     * @return  Database_Core        This Database object.
     */
    public function where($key, $value = NULL, $quote = TRUE)
    {
        $quote = (func_num_args() < 2 AND ! is_array($key)) ? -1 : $quote;
        $keys  = is_array($key) ? $key : array($key => $value);

        foreach ($keys as $key => $value)
        {
            $key           = (strpos($key, '.') !== FALSE) ? $this->config['table_prefix'].$key : $key;
            $this->where[] = $this->driver->where($key, $value, 'AND ', count($this->where), $quote);
        }

        return $this;
    }

    /**
     * Selects the or where(s) for a database query.
     *
     * @param   string|array  key name or array of key => value pairs
     * @param   string        value to match with key
     * @param   boolean       disable quoting of WHERE clause
     * @return  Database_Core        This Database object.
     */
    public function orwhere($key, $value = NULL, $quote = TRUE)
    {
        $quote = (func_num_args() < 2 AND ! is_array($key)) ? -1 : $quote;
        $keys  = is_array($key) ? $key : array($key => $value);

        foreach ($keys as $key => $value)
        {
            $key           = (strpos($key, '.') !== FALSE) ? $this->config['table_prefix'].$key : $key;
            $this->where[] = $this->driver->where($key, $value, 'OR ', count($this->where), $quote);
        }

        return $this;
    }

    /**
     * Selects the like(s) for a database query.
     *
     * @param   string|array  field name or array of field => match pairs
     * @param   string        like value to match with field
     * @param   boolean       automatically add starting and ending wildcards
     * @return  Database_Core        This Database object.
     */
    public function like($field, $match = '', $auto = TRUE)
    {
        $fields = is_array($field) ? $field : array($field => $match);

        foreach ($fields as $field => $match)
        {
            $field         = (strpos($field, '.') !== FALSE) ? $this->config['table_prefix'].$field : $field;
            $this->where[] = $this->driver->like($field, $match, $auto, 'AND ', count($this->where));
        }

        return $this;
    }

    /**
     * Selects the or like(s) for a database query.
     *
     * @param   string|array  field name or array of field => match pairs
     * @param   string        like value to match with field
     * @param   boolean       automatically add starting and ending wildcards
     * @return  Database_Core        This Database object.
     */
    public function orlike($field, $match = '', $auto = TRUE)
    {
        $fields = is_array($field) ? $field : array($field => $match);

        foreach ($fields as $field => $match)
        {
            $field         = (strpos($field, '.') !== FALSE) ? $this->config['table_prefix'].$field : $field;
            $this->where[] = $this->driver->like($field, $match, $auto, 'OR ', count($this->where));
        }

        return $this;
    }

    /**
     * Selects the not like(s) for a database query.
     *
     * @param   string|array  field name or array of field => match pairs
     * @param   string        like value to match with field
     * @param   boolean       automatically add starting and ending wildcards
     * @return  Database_Core        This Database object.
     */
    public function notlike($field, $match = '', $auto = TRUE)
    {
        $fields = is_array($field) ? $field : array($field => $match);

        foreach ($fields as $field => $match)
        {
            $field         = (strpos($field, '.') !== FALSE) ? $this->config['table_prefix'].$field : $field;
            $this->where[] = $this->driver->notlike($field, $match, $auto, 'AND ', count($this->where));
        }

        return $this;
    }

    /**
     * Selects the or not like(s) for a database query.
     *
     * @param   string|array  field name or array of field => match pairs
     * @param   string        like value to match with field
     * @return  Database_Core        This Database object.
     */
    public function ornotlike($field, $match = '', $auto = TRUE)
    {
        $fields = is_array($field) ? $field : array($field => $match);

        foreach ($fields as $field => $match)
        {
            $field         = (strpos($field, '.') !== FALSE) ? $this->config['table_prefix'].$field : $field;
            $this->where[] = $this->driver->notlike($field, $match, $auto, 'OR ', count($this->where));
        }

        return $this;
    }

    /**
     * Selects the like(s) for a database query.
     *
     * @param   string|array  field name or array of field => match pairs
     * @param   string        like value to match with field
     * @return  Database_Core        This Database object.
     */
    public function regex($field, $match = '')
    {