/solar/source/solar/Solar/Sql/Adapter.php

Large files files are truncated, but you can click here to view the full file

<?php
/**
 * 
 * Abstract base class for specific RDBMS adapters.
 * 
 * When writing an adapter, you need to override these abstract methods:
 * 
 * {{code: php
 *     abstract protected function _fetchTableList();
 *     abstract protected function _fetchTableCols($table);
 *     abstract protected function _createSequence($name, $start = 1);
 *     abstract protected function _dropSequence($name);
 *     abstract protected function _nextSequence($name);
 *     abstract protected function _dropIndex($table, $name);
 *     abstract protected function _modAutoincPrimary(&$coldef, $autoinc, $primary);
 * }}
 * 
 * If the backend needs identifier deconfliction (e.g., PostgreSQL), you will
 * want to override _modIndexName() and _modSequenceName().  Most times this
 * will not be necessary.
 * 
 * If the backend does not have explicit "LIMIT ... OFFSET" support,
 * you will want to override _modSelect($stmt, $parts) to rewrite the query
 * in order to emulate limit/select behavior.  This is particularly necessary
 * for Microsoft SQL and Oracle.
 * 
 * @category Solar
 * 
 * @package Solar_Sql
 * 
 * @author Paul M. Jones <pmjones@solarphp.com>
 * 
 * @license http://opensource.org/licenses/bsd-license.php BSD
 * 
 * @version $Id: Adapter.php 4612 2010-06-19 13:45:49Z pmjones $
 * 
 */
abstract class Solar_Sql_Adapter extends Solar_Base {
    
    /**
     * 
     * Default configuration values.
     * 
     * @config string host Host specification (typically 'localhost').
     * 
     * @config string port Port number for the host name.
     * 
     * @config string sock The Unix socket for the connection. Should not be used with
     *   host and port.
     * 
     * @config string user Connect to the database as this username.
     * 
     * @config string pass Password associated with the username.
     * 
     * @config string name Database name (or file path, or TNS name).
     * 
     * @config bool profiling Turn on query profiling?
     * 
     * @config dependency cache The cache to use, if any, for the lists of
     * table names, table columns, etc.
     * 
     * @var array
     * 
     */
    protected $_Solar_Sql_Adapter = array(
        'host'      => null,
        'port'      => null,
        'sock'      => null,
        'user'      => null,
        'pass'      => null,
        'name'      => null,
        'profiling' => false,
        'cache'     => array('adapter' => 'Solar_Cache_Adapter_Var'),
    );
    
    /**
     * 
     * A cache object for keeping query results.
     * 
     * @var Solar_Cache_Adapter
     * 
     */
    protected $_cache;
    
    /**
     * 
     * Prefix all cache keys with this string.
     * 
     * @var string
     * 
     */
    protected $_cache_key_prefix;
    
    /**
     * 
     * Map of Solar generic types to RDBMS native types used when creating
     * portable tables.
     * 
     * See the individual adapters for specific mappings.
     * 
     * The available generic column types are ...
     * 
     * `char`
     * : A fixed-length string of 1-255 characters.
     * 
     * `varchar`
     * : A variable-length string of 1-255 characters.
     * 
     * `bool`
     * : A true/false boolean, generally stored as an integer 1 or 0.  May
     *   also be stored as null, allowing for ternary logic.
     * 
     * `smallint`
     * : A 2-byte integer in the range of -32767 ... +32768.
     * 
     * `int`
     * : A 4-byte integer in the range of -2,147,483,648 ... +2,147,483,647.
     * 
     * `bigint`
     * : An 8-byte integer, value range roughly (-9,223,372,036,854,780,000
     *   ... +9,223,372,036,854,779,999).
     * 
     * `numeric`
     * : A fixed-point decimal number of a specific size (total number of
     *   digits) and scope (the number of those digits to the right of the
     *   decimal point).
     * 
     * `float`
     * : A double-precision floating-point decimal number.
     * 
     * `clob`
     * : A character large object with a size of up to 2,147,483,647 bytes
     *   (about 2 GB).
     * 
     * `date`
     * : An ISO 8601 date; for example, '1979-11-07'.
     * 
     * `time`
     * : An ISO 8601 time; for example, '12:34:56'.
     * 
     * `timestamp`
     * : An ISO 8601 timestamp without a timezone offset; for example,
     *   '1979-11-07 12:34:56'.
     * 
     * @var array
     * 
     */
    protected $_solar_native = array(
        'bool'      => null,
        'char'      => null, 
        'varchar'   => null, 
        'smallint'  => null,
        'int'       => null,
        'bigint'    => null,
        'numeric'   => null,
        'float'     => null,
        'clob'      => null,
        'date'      => null,
        'time'      => null,
        'timestamp' => null,
    );
    
    /**
     * 
     * Map of native RDBMS types to Solar generic types used when reading 
     * table column information.
     * 
     * See the individual adapters for specific mappings.
     * 
     * @var array
     * 
     * @see fetchTableCols()
     * 
     */
    protected $_native_solar = array();
    
    /**
     * 
     * A PDO object for accessing the RDBMS.
     * 
     * @var object
     * 
     */
    protected $_pdo = null;
    
    /**
     * 
     * The PDO adapter DSN type.
     * 
     * This might not be the same as the Solar adapter type.
     * 
     * @var string
     * 
     */
    protected $_pdo_type = null;
    
    /**
     * 
     * Max identifier lengths for table, column, and index names used when
     * creating portable tables.
     * 
     * We use 30 characters to comply with Oracle maximums.
     * 
     * @var array
     * 
     */
    protected $_maxlen = 30;
    
    /**
     * 
     * A quick-and-dirty query profile array.
     * 
     * Each element is an array, where the first value is the query execution
     * time in microseconds, and the second value is the query string.
     * 
     * Only populated when the `profiling` config key is true.
     * 
     * @var array
     * 
     */
    protected $_profile = array();
    
    /**
     * 
     * Whether or not profiling is turned on.
     * 
     * @var bool
     * 
     */
    protected $_profiling = false;
    
    /**
     * 
     * A PDO-style DSN, for example, "mysql:host=127.0.0.1;dbname=test".
     * 
     * @var string
     * 
     */
    protected $_dsn;
    
    /**
     * 
     * The quote character before an identifier name (table, index, etc).
     * 
     * @var string
     * 
     */
    protected $_ident_quote_prefix = null;
    
    /**
     * 
     * The quote character after an identifier name (table, index, etc).
     * 
     * @var string
     * 
     */
    protected $_ident_quote_suffix = null;
    
    /**
     * 
     * Post-construction tasks to complete object construction.
     * 
     * @return void
     * 
     */
    protected function _postConstruct()
    {
        parent::_postConstruct();
        
        // turn on profiling?
        $this->setProfiling($this->_config['profiling']);
        
        // set a cache object
        $this->_cache = Solar::dependency(
            'Solar_Cache',
            $this->_config['cache']
        );
        
        // follow-on setup
        $this->_setup();
    }
    
    /**
     * 
     * Follow-on setup from the constructor; useful for extended classes.
     * 
     * @return void
     * 
     */
    protected function _setup()
    {
        // set the DSN from the config info
        $this->_setDsn();
        
        // set the cache-key prefix
        $this->setCacheKeyPrefix();
    }
    
    /**
     * 
     * Turns profiling on and off.
     * 
     * @param bool $flag True to turn profiling on, false to turn it off.
     * 
     * @return void
     * 
     */
    public function setProfiling($flag)
    {
        $this->_profiling = (bool) $flag;
    }
    
    /**
     * 
     * Returns the cache object.
     * 
     * @return Solar_Cache
     * 
     * @see $_cache
     * 
     */
    public function getCache()
    {
        return $this->_cache;
    }
    
    /**
     * 
     * Injects a cache dependency for `$_cache`.
     * 
     * @param mixed $spec A [[Solar::dependency()]] specification.
     * 
     * @return void
     * 
     * @see $_cache
     * 
     */
    public function setCache($spec)
    {
        $this->_cache = Solar::dependency('Solar_Cache', $spec);
    }
    
    /**
     * 
     * Sets the connection-specific cache key prefix.
     * 
     * @param string $prefix The cache-key prefix.  When null, defaults to
     * the class name, a slash, and the md5() of the DSN.
     * 
     * @return string
     * 
     */
    public function setCacheKeyPrefix($prefix = null)
    {
        if ($prefix === null) {
            $prefix = get_class($this) . '/' . md5($this->_dsn);
        }
        
        $this->_cache_key_prefix = $prefix;
    }
    
    /**
     * 
     * Gets the connection-specific cache key prefix.
     * 
     * @return string
     * 
     */
    public function getCacheKeyPrefix()
    {
        return $this->_cache_key_prefix;
    }
    
    /**
     * 
     * Get the query profile array.
     * 
     * @return array An array of queries executed by the adapter.
     * 
     */
    public function getProfile()
    {
        return $this->_profile;
    }
    
    /**
     * 
     * Get the PDO connection object (connects to the database if needed).
     * 
     * @return PDO
     * 
     */
    public function getPdo()
    {
        $this->connect();
        return $this->_pdo;
    }
    
    // -----------------------------------------------------------------
    // 
    // Connection and basic queries
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Sets the DSN value for the connection from the config info.
     * 
     * @return void
     * 
     */
    protected function _setDsn()
    {
        $this->_dsn = $this->_buildDsn($this->_config);
    }
    
    /**
     * 
     * Creates a PDO-style DSN.
     * 
     * For example, "mysql:host=127.0.0.1;dbname=test"
     * 
     * @param array $info An array with host, post, name, etc. keys.
     * 
     * @return string The DSN string.
     * 
     */
    protected function _buildDsn($info)
    {
        $dsn = array();
        
        if (! empty($info['host'])) {
            $dsn[] = 'host=' . $info['host'];
        }
        
        if (! empty($info['port'])) {
            $dsn[] = 'port=' . $info['port'];
        }
        
        if (! empty($info['name'])) {
            $dsn[] = 'dbname=' . $info['name'];
        }
        
        return $this->_pdo_type . ':' . implode(';', $dsn);
    }
    
    /**
     * 
     * Creates a PDO object and connects to the database.
     * 
     * Also sets the query-cache key prefix.
     * 
     * @return void
     * 
     */
    public function connect()
    {
        // if we already have a PDO object, no need to re-connect.
        if ($this->_pdo) {
            return;
        }
        
        // start profile time
        $time = microtime(true);
        
        // attempt the connection
        $this->_pdo = new PDO(
            $this->_dsn,
            $this->_config['user'],
            $this->_config['pass']
        );
        
        // retain connection info
        $this->_pdo->solar_conn = array(
            'dsn'  => $this->_dsn,
            'user' => $this->_config['user'],
            'pass' => $this->_config['pass'],
            'type' => 'single',
            'key'  => null,
        );
        
        // post-connection tasks
        $this->_postConnect();
        
        // retain the profile data?
        $this->_addProfile($time, '__CONNECT');
    }
    
    /**
     * 
     * After connection, set various connection attributes.
     * 
     * @return void
     * 
     */
    protected function _postConnect()
    {
        // always emulate prepared statements; this is faster, and works
        // better with CREATE, DROP, ALTER statements.  requires PHP 5.1.3
        // or later. note that we do this *first* (before using exceptions)
        // because not all adapters support it.
        $this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
        $this->_pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
        
        // always use exceptions
        $this->_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            
        // force names to lower case
        $this->_pdo->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
    }
    
    /**
     * 
     * Closes the database connection.
     * 
     * This isn't generally necessary as PHP will automatically close the
     * connection in the end of the script execution, but it can be useful
     * to free resources when a script needs to connect tomultiple databases
     * in sequence.
     * 
     * @return void
     * 
     */
    public function disconnect()
    {
        $this->_pdo = null;
    }
    
    /**
     * 
     * Gets a full cache key.
     * 
     * @param string $key The partial cache key.
     * 
     * @return string The full cache key.
     * 
     */
    protected function _getCacheKey($key)
    {
        return $this->_cache_key_prefix . "/$key";
    }
    
    /**
     * 
     * Prepares and executes an SQL statement, optionally binding values
     * to named parameters in the statement.
     * 
     * This is the most-direct way to interact with the database; you
     * pass an SQL statement to the method, then the adapter uses
     * [[php::PDO | ]] to execute the statement and return a result.
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     * 
     *     // $result is a PDOStatement
     *     $result = $sql->query('SELECT * FROM table');
     * }}
     * 
     * To help prevent SQL injection attacks, you should **always** quote
     * the values used in a direct query. Use [[Solar_Sql_Adapter::quote() | quote()]],
     * [[Solar_Sql_Adapter::quoteInto() | quoteInto()]], or 
     * [[Solar_Sql_Adapter::quoteMulti() | quoteMulti()]] to accomplish this.
     * Even easier, use the automated value binding provided by the query() 
     * method:
     * 
     * {{code: php
     *     // BAD AND SCARY:
     *     $result = $sql->query('SELECT * FROM table WHERE foo = $bar');
     *     
     *     // Much much better:
     *     $result = $sql->query(
     *         'SELECT * FROM table WHERE foo = :bar',
     *         array('bar' => $bar)
     *     );
     * }}
     * 
     * Note that adapters provide convenience methods to automatically quote
     * values on common operations:
     * 
     * - [[Solar_Sql_Adapter::insert()]]
     * - [[Solar_Sql_Adapter::update()]]
     * - [[Solar_Sql_Adapter::delete()]]
     * 
     * Additionally, the [[Solar_Sql_Select]] class is dedicated to
     * safely creating portable SELECT statements, so you may wish to use that
     * instead of writing literal SELECTs.
     * 
     * 
     * Automated Binding of Values in PHP 5.2.1 and Later
     * --------------------------------------------------
     * 
     * With PDO in PHP 5.2.1 and later, we can no longer just throw an array
     * of data at the statement for binding. We now need to bind values
     * specifically to their respective placeholders.
     * 
     * In addition, we can't bind one value to multiple identical named
     * placeholders; we need to bind that same value multiple times. So if
     * `:foo` is used three times, PDO uses `:foo` the first time, `:foo2` the
     * second time, and `:foo3` the third time.
     * 
     * This query() method examins the statement for all `:name` placeholders
     * and attempts to bind data from the `$data` array.  The regular-expression
     * it uses is a little braindead; it cannot tell if the :name placeholder
     * is literal text or really a place holder.
     * 
     * As such, you should *either* use the `$data` array for named-placeholder
     * value binding at query() time, *or* bind-as-you-go when building the 
     * statement, not both.  If you do, you are on your own to make sure
     * that nothing looking like a `:name` placeholder exists in the literal text.
     * 
     * Question-mark placeholders are not supported for automatic value
     * binding at query() time.
     * 
     * @param string $stmt The text of the SQL statement, optionally with
     * named placeholders.
     * 
     * @param array $data An associative array of data to bind to the named
     * placeholders.
     * 
     * @return PDOStatement
     * 
     */
    public function query($stmt, $data = array())
    {
        $this->connect();
        
        // begin the profile time
        $time = microtime(true);
        
        // prepre the statement and bind data to it
        $prep = $this->_prepare($stmt);
        $this->_bind($prep, $data);
        
        // now try to execute
        try {
            $prep->execute();
        } catch (PDOException $e) {
            throw $this->_exception('ERR_QUERY_FAILED', array(
                'pdo_code'  => $e->getCode(),
                'pdo_text'  => $e->getMessage(),
                'host'      => $this->_config['host'],
                'port'      => $this->_config['port'],
                'user'      => $this->_config['user'],
                'name'      => $this->_config['name'],
                'stmt'      => $stmt,
                'data'      => $data,
                'pdo_trace' => $e->getTraceAsString(),
            ));
        }
        
        // retain the profile data?
        $this->_addProfile($time, $prep, $data);
        
        // done!
        return $prep;
    }
    
    /**
     * 
     * Prepares an SQL query as a PDOStatement object.
     * 
     * @param string $stmt The text of the SQL statement, optionally with
     * named placeholders.
     * 
     * @return PDOStatement
     * 
     */
    protected function _prepare($stmt)
    {
        // prepare the statment
        try {
            $prep = $this->_pdo->prepare($stmt);
            $prep->solar_conn = $this->_pdo->solar_conn;
        } catch (PDOException $e) {
            throw $this->_exception('ERR_PREPARE_FAILED', array(
                'pdo_code'  => $e->getCode(),
                'pdo_text'  => $e->getMessage(),
                'host'      => $this->_config['host'],
                'port'      => $this->_config['port'],
                'sock'      => $this->_config['sock'],
                'user'      => $this->_config['user'],
                'name'      => $this->_config['name'],
                'stmt'      => $stmt,
                'pdo_trace' => $e->getTraceAsString(),
            ));
        }
        
        return $prep;
    }
    
    /**
     * 
     * Binds an array of scalars as values into a prepared PDOStatment.
     * 
     * Array element values that are themselves arrays will not be bound
     * correctly, because PDO expects scalar values only.
     * 
     * @param PDOStatement $prep The prepared PDOStatement.
     * 
     * @param array $data The scalar values to bind into the PDOStatement.
     * 
     * @return void
     * 
     */
    protected function _bind($prep, $data)
    {
        // was data passed for binding?
        if (! $data) {
            return;
        }
            
        // find all :placeholder matches.  note that this is a little
        // brain-dead; it will find placeholders in literal text, which
        // will cause errors later.  so in general, you should *either*
        // bind at query time *or* bind as you go, not both.
        preg_match_all(
            "/\W:([a-zA-Z_][a-zA-Z0-9_]*)/m",
            $prep->queryString . "\n",
            $matches
        );
        
        // bind values to placeholders, repeating as needed
        $repeat = array();
        foreach ($matches[1] as $key) {
            
            // only attempt to bind if the data key exists.
            // this allows for nulls and empty strings.
            if (! array_key_exists($key, $data)) {
                // skip it
                continue;
            }
        
            // what does PDO expect as the placeholder name?
            if (empty($repeat[$key])) {
                // first time is ":foo"
                $repeat[$key] = 1;
                $name = $key;
            } else {
                // repeated times of ":foo" are treated by PDO as
                // ":foo2", ":foo3", etc.
                $repeat[$key] ++;
                $name = $key . $repeat[$key];
            }
            
            // bind the value to the placeholder name
            $prep->bindValue($name, $data[$key]);
        }
    }
    
    /**
     * 
     * Adds an element to the profile array.
     * 
     * @param int $time The microtime when the profile element started.
     * 
     * @param string|PDOStatement $spec The SQL statement being profiled.
     * 
     * @param array $data Any data bound into the statement.
     * 
     * @return void
     * 
     */
    protected function _addProfile($time, $spec, $data = null)
    {
        if (! $this->_profiling) {
            return;
        }
        
        if ($spec instanceof PDOStatement) {
            $conn = $spec->solar_conn;
            $stmt = $spec->queryString;
        } else {
            $conn = null;
            $stmt = $spec;
        }
        
        $timespan = microtime(true) - $time;
        $e = new Exception();
        $this->_profile[] = array(
            'time'      => $timespan,
            'stmt'      => $stmt,
            'data'      => $data,
            'conn'      => $conn,
            'trace'     => $e->getTraceAsString(),
        );
    }
    
    // -----------------------------------------------------------------
    // 
    // Transactions
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Leave autocommit mode and begin a transaction.
     * 
     * @return void
     * 
     */
    public function begin()
    {
        $this->connect();
        $time = microtime(true);
        $result = $this->_pdo->beginTransaction();
        $this->_addProfile($time, '__BEGIN');
        return $result;
    }
    
    /**
     * 
     * Commit a transaction and return to autocommit mode.
     * 
     * @return void
     * 
     */
    public function commit()
    {
        $this->connect();
        $time = microtime(true);
        $result = $this->_pdo->commit();
        $this->_addProfile($time, '__COMMIT');
        return $result;
    }
    
    /**
     * 
     * Roll back a transaction and return to autocommit mode.
     * 
     * @return void
     * 
     */
    public function rollback()
    {
        $this->connect();
        $time = microtime(true);
        $result = $this->_pdo->rollBack();
        $this->_addProfile($time, '__ROLLBACK');
        return $result;
    }
    
    // -----------------------------------------------------------------
    // 
    // Manipulation
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Inserts a row of data into a table.
     * 
     * Automatically applies [[Solar_Sql_Adapter::quote() | ]] to the data 
     * values for you.
     * 
     * For example:
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     * 
     *     $table = 'invaders';
     *     $data = array(
     *         'foo' => 'bar',
     *         'baz' => 'dib',
     *         'zim' => 'gir'
     *     );
     * 
     *     $rows_affected = $sql->insert($table, $data);
     *     // calls 'INSERT INTO invaders (foo, baz, zim) VALUES ("bar", "dib", "gir")'
     * }}
     * 
     * @param string $table The table to insert data into.
     * 
     * @param array $data An associative array where the key is the column
     * name and the value is the value to insert for that column.
     * 
     * @return int The number of rows affected, typically 1.
     * 
     */
    public function insert($table, $data)
    {
        // the base statement
        $table = $this->quoteName($table);
        $stmt = "INSERT INTO $table ";
        
        // col names come from the array keys
        $keys = array_keys($data);
        
        // quote the col names
        $cols = array();
        foreach ($keys as $key) {
            $cols[] = $this->quoteName($key);
        }
        
        // add quoted col names
        $stmt .= '(' . implode(', ', $cols) . ') ';
        
        // add value placeholders (use unquoted key names)
        $stmt .= 'VALUES (:' . implode(', :', $keys) . ')';
        
        // execute the statement
        $result = $this->query($stmt, $data);
        return $result->rowCount();
    }
    
    /**
     * 
     * Updates a table with specified data based on a WHERE clause.
     * 
     * Automatically applies [[Solar_Sql_Adapter::quote() | ]] to the data 
     * values for you.
     * 
     * @param string $table The table to udpate.
     * 
     * @param array $data An associative array where the key is the column
     * name and the value is the value to use for that column.
     * 
     * @param string|array $where The SQL WHERE clause to limit which
     * rows are updated.
     * 
     * @return int The number of rows affected.
     * 
     */
    public function update($table, $data, $where)
    {
        // the base statement
        $table = $this->quoteName($table);
        $stmt = "UPDATE $table SET ";
        
        // add "col = :col" pairs to the statement
        $tmp = array();
        foreach ($data as $col => $val) {
            $tmp[] = $this->quoteName($col) . " = :$col";
        }
        $stmt .= implode(', ', $tmp);
        
        // add the where clause
        if ($where) {
            $where = $this->quoteMulti($where, ' AND ');
            $where = $this->quoteNamesIn($where);
            $stmt .= " WHERE $where";
        }
        
        // execute the statement
        $result = $this->query($stmt, $data);
        return $result->rowCount();
    }
    
    /**
     * 
     * Deletes rows from the table based on a WHERE clause.
     * 
     * For example ...
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     * 
     *     $table = 'events';
     *     $where = $sql->quoteInto('status = ?', 'cancelled');
     *     $rows_affected = $sql->delete($table, $where);
     * 
     *     // calls 'DELETE FROM events WHERE status = "cancelled"'
     * }}
     * 
     * For the $where parameter, you can also pass multiple WHERE conditions as
     * an array to be "AND"ed together.
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     * 
     *     $table = 'events';
     *     $where = array(
     *         "date >= ?"  => '2006-01-01',
     *         "date <= ?"  => '2006-01-31',
     *         "status = ?" => 'cancelled',
     *     );
     * 
     *     $rows_affected = $sql->delete($table, $where);
     * 
     *     // calls:
     *     // DELETE FROM events WHERE date >= "2006-01-01"
     *     // AND date <= "2006-01-31" AND status = "cancelled"
     * }}
     * 
     * @param string $table The table to delete from.
     * 
     * @param string|array $where The SQL WHERE clause to limit which
     * rows are deleted.
     * 
     * @return int The number of rows affected.
     * 
     */
    public function delete($table, $where)
    {
        if ($where) {
            $where = $this->quoteMulti($where, ' AND ');
            $where = $this->quoteNamesIn($where);
        }
        
        $table = $this->quoteName($table);
        $result = $this->query("DELETE FROM $table WHERE $where");
        return $result->rowCount();
    }
    
    // -----------------------------------------------------------------
    // 
    // Retrieval
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Fetches all rows from the database using sequential keys.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return array
     * 
     */
    public function fetchAll($spec, $data = array())
    {
        $result = $this->fetchPdo($spec, $data);
        return $result->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * 
     * Fetches all rows from the database using associative keys (defined by
     * the first column).
     * 
     * N.b.: if multiple rows have the same first column value, the last
     * row with that value will override earlier rows.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return array
     * 
     */
    public function fetchAssoc($spec, $data = array())
    {
        $result = $this->fetchPdo($spec, $data);
        
        $data = array();
        while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
            $key = current($row); // value of the first element
            $data[$key] = $row;
        }
        
        return $data;
    }
    
    /**
     * 
     * Fetches the first column of all rows as a sequential array.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return array
     * 
     */
    public function fetchCol($spec, $data = array())
    {
        $result = $this->fetchPdo($spec, $data);
        return $result->fetchAll(PDO::FETCH_COLUMN, 0);
    }
    
    /**
     * 
     * Fetches the very first value (i.e., first column of the first row).
     * 
     * When $spec is an array, automatically sets LIMIT 1 OFFSET 0 to limit
     * the results to a single row.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return mixed
     * 
     */
    public function fetchValue($spec, $data = array())
    {
        if (is_array($spec)) {
            // automatically limit to the first row only,
            // but leave the offset alone.
            $spec['limit']['count'] = 1;
        }
        $result = $this->fetchPdo($spec, $data);
        return $result->fetchColumn(0);
    }
    
    /**
     * 
     * Fetches an associative array of all rows as key-value pairs (first 
     * column is the key, second column is the value).
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return array
     * 
     */
    public function fetchPairs($spec, $data = array())
    {
        $result = $this->fetchPdo($spec, $data);
        
        $data = array();
        while ($row = $result->fetch(PDO::FETCH_NUM)) {
            $data[$row[0]] = $row[1];
        }
        
        return $data;
    }
    
    /**
     * 
     * Fetches a PDOStatement result object.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return PDOStatement
     * 
     */
    public function fetchPdo($spec, $data = array())
    {
        // build the statement from its component parts if needed
        if (is_array($spec)) {
            $stmt = $this->_select($spec);
        } else {
            $stmt = $spec;
        }
        
        // execute and get the PDOStatement result object
        return $this->query($stmt, $data);
    }
    
    /**
     * 
     * Fetches one row from the database.
     * 
     * When $spec is an array, automatically sets LIMIT 1 OFFSET 0 to limit
     * the results to a single row.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @param array $data An associative array of data to bind into the
     * SELECT statement.
     * 
     * @return array
     * 
     */
    public function fetchOne($spec, $data = array())
    {
        if (is_array($spec)) {
            // automatically limit to the first row only,
            // but leave the offset alone.
            $spec['limit']['count'] = 1;
        }
        
        $result = $this->fetchPdo($spec, $data);
        return $result->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * 
     * Builds the SQL statement and returns it as a string instead of 
     * executing it.  Useful for debugging.
     * 
     * @param array|string $spec An array of component parts for a
     * SELECT, or a literal query string.
     * 
     * @return string
     * 
     */
    public function fetchSql($spec)
    {
        // build the statement from its component parts if needed
        if (is_array($spec)) {
            return $this->_select($spec);
        } else {
            return $spec;
        }
    }
    
    /**
     * 
     * Returns a SELECT statement built from its component parts.
     * 
     * @param array $parts The component parts of the SELECT.
     * 
     * @return string The SELECT string.
     * 
     */
    protected function _select($parts)
    {
        // buid the statment
        if (empty($parts['compound'])) {
            $stmt = $this->_selectSingle($parts);
        } else {
            $stmt = $this->_selectCompound($parts);
        }
        
        // modify per adapter
        $this->_modSelect($stmt, $parts);
        
        // done!
        return $stmt;
    }
    
    /**
     * 
     * Builds a single SELECT command string from its component parts, 
     * without the LIMIT portions; those are left to the individual adapters.
     * 
     * @param array $parts The parts of the SELECT statement, generally
     * from a Solar_Sql_Select object.
     * 
     * @return string A SELECT command string.
     * 
     */
    protected function _selectSingle($parts)
    {
        $default = array(
            'distinct' => null,
            'cols'     => array(),
            'from'     => array(),
            'join'     => array(),
            'where'    => array(),
            'group'    => array(),
            'having'   => array(),
            'order'    => array(),
        );
        
        $parts = array_merge($default, $parts);
        
        // is this a SELECT or SELECT DISTINCT?
        if ($parts['distinct']) {
            $stmt = "SELECT DISTINCT\n    ";
        } else {
            $stmt = "SELECT\n    ";
        }
        
        // add columns
        $stmt .= implode(",\n    ", $parts['cols']) . "\n";
        
        // from these tables
        $stmt .= $this->_selectSingleFrom($parts['from']);
        
        // joined to these tables
        if ($parts['join']) {
            $list = array();
            foreach ($parts['join'] as $join) {
                $tmp = '';
                // add the type (LEFT, INNER, etc)
                if (! empty($join['type'])) {
                    $tmp .= $join['type'] . ' ';
                }
                // add the table name and condition
                $tmp .= 'JOIN ' . $join['name'];
                $tmp .= ' ON ' . $join['cond'];
                // add to the list
                $list[] = $tmp;
            }
            // add the list of all joins
            $stmt .= implode("\n", $list) . "\n";
        }
        
        // with these where conditions
        if ($parts['where']) {
            $stmt .= "WHERE\n    ";
            $stmt .= implode("\n    ", $parts['where']) . "\n";
        }
        
        // grouped by these columns
        if ($parts['group']) {
            $stmt .= "GROUP BY\n    ";
            $stmt .= implode(",\n    ", $parts['group']) . "\n";
        }
        
        // having these conditions
        if ($parts['having']) {
            $stmt .= "HAVING\n    ";
            $stmt .= implode("\n    ", $parts['having']) . "\n";
        }
        
        // ordered by these columns
        if ($parts['order']) {
            $stmt .= "ORDER BY\n    ";
            $stmt .= implode(",\n    ", $parts['order']) . "\n";
        }
        
        // done!
        return $stmt;
    }
    
    /**
     * 
     * Builds the FROM clause for a SELECT command.
     * 
     * @param array $from The array of FROM clause elements.
     * 
     * @return string The FROM clause.
     * 
     */
    protected function _selectSingleFrom($from)
    {
        return "FROM\n    "
             . implode(",\n    ", $from)
             . "\n";
    }
    
    /**
     * 
     * Builds a compound SELECT command string from its component parts,
     * without the LIMIT portions; those are left to the individual adapters.
     * 
     * @param array $parts The parts of the SELECT statement, generally
     * from a Solar_Sql_Select object.
     * 
     * @return string A SELECT command string.
     * 
     */
    protected function _selectCompound($parts)
    {
        // the select statement to build up
        $stmt = '';
        
        // default parts of each 'compound' element
        $default = array(
            'type' => null, // 'UNION', 'UNION ALL', etc.
            'spec' => null, // array or string for the SELECT statement
        );
        
        // combine the compound elements
        foreach ((array) $parts['compound'] as $compound) {
            
            // make sure we have the default elements
            $compound = array_merge($default, $compound);
            
            // is it an array of select parts?
            if (is_array($compound['spec'])) {
                // yes, build a select string from them
                $select = $this->_select($compound['spec']);
            } else {
                // no, assume it's already a select string
                $select = $compound['spec'];
            }
            
            // do we need to add the compound type?
            // note that the first compound type will be ignored.
            if ($stmt) {
                $stmt .= strtoupper($compound['type']) . "\n";
            }
            
            // now add the select itself
            $stmt .= "(" . $select . ")\n";
        }
        
        // add any overall order
        if (! empty($parts['order'])) {
            $stmt .= "ORDER BY\n    ";
            $stmt .= implode(",\n    ", $parts['order']) . "\n";
        }
        
        // done!
        return $stmt;
    }
    
    /**
     * 
     * Modifies a SELECT statement in place to add a LIMIT clause.
     * 
     * The default code adds a LIMIT for MySQL, PostgreSQL, and Sqlite, but
     * adapters can override as needed.
     * 
     * @param string &$stmt The SELECT statement.
     * 
     * @param array &$parts The orignal SELECT component parts, in case the
     * adapter needs them.
     * 
     * @return void
     * 
     */
    protected function _modSelect(&$stmt, &$parts)
    {
        // determine count
        $count = ! empty($parts['limit']['count'])
            ? (int) $parts['limit']['count']
            : 0;
        
        // determine offset
        $offset = ! empty($parts['limit']['offset'])
            ? (int) $parts['limit']['offset']
            : 0;
      
        // add the count and offset
        if ($count > 0) {
            $stmt .= "LIMIT $count";
            if ($offset > 0) {
                $stmt .= " OFFSET $offset";
            }
        }
    }
    
    
    // -----------------------------------------------------------------
    // 
    // Quoting
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Safely quotes a value for an SQL statement.
     * 
     * If an array is passed as the value, the array values are quoted
     * and then returned as a comma-separated string; this is useful 
     * for generating IN() lists.
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     *     
     *     $safe = $sql->quote('foo"bar"');
     *     // $safe == "'foo\"bar\"'"
     *     
     *     $safe = $sql->quote(array('one', 'two', 'three'));
     *     // $safe == "'one', 'two', 'three'"
     * }}
     * 
     * @param mixed $val The value to quote.
     * 
     * @return string An SQL-safe quoted value (or a string of 
     * separated-and-quoted values).
     * 
     */
    public function quote($val)
    {
        if (is_array($val)) {
            // quote array values, not keys, then combine with commas.
            foreach ($val as $k => $v) {
                $val[$k] = $this->quote($v);
            }
            return implode(', ', $val);
        } else {
            // quote all other scalars, including numerics
            $this->connect();
            return $this->_pdo->quote($val);
        }
    }
    
    /**
     * 
     * Quotes a value and places into a piece of text at a placeholder; the
     * placeholder is a question-mark.
     * 
     * {{code: php
     *      $sql = Solar::factory('Solar_Sql');
     *      
     *      // replace one placeholder
     *      $text = "WHERE date >= ?";
     *      $data = "2005-01-01";
     *      $safe = $sql->quoteInto($text, $data);
     *      // => "WHERE date >= '2005-01-02'"
     *      
     *      // replace multiple placeholders
     *      $text = "WHERE date BETWEEN ? AND ?";
     *      $data = array("2005-01-01", "2005-01-31");
     *      $safe = $sql->quoteInto($text, $data);
     *      // => "WHERE date BETWEEN '2005-01-01' AND '2005-01-31'"
     * 
     *      // single placeholder with array value
     *      $text = "WHERE foo IN (?)";
     *      $data = array('a', 'b', 'c');
     *      $safe = $sql->quoteInto($text, $data);
     *      // => "WHERE foo IN ('a', 'b', 'c')"
     *      
     *      // multiple placeholders and array values
     *      $text = "WHERE date >= ? AND foo IN (?)";
     *      $data = array('2005-01-01, array('a', 'b', 'c'));
     *      $safe = $sql->quoteInto($text, $data);
     *      // => "WHERE date >= '2005-01-01' AND foo IN ('a', 'b', 'c')"
     * }}
     * 
     * @param string $text The text with placeholder(s).
     * 
     * @param mixed $data The data value(s) to quote.
     * 
     * @return mixed An SQL-safe quoted value (or string of separated values)
     * placed into the orignal text.
     * 
     * @see quote()
     * 
     */
    public function quoteInto($text, $data)
    {
        // how many question marks are there?
        $count = substr_count($text, '?');
        if (! $count) {
            // no replacements needed
            return $text;
        }
        
        // only one replacement?
        if ($count == 1) {
            $data = $this->quote($data);
            $text = str_replace('?', $data, $text);
            return $text;
        }
        
        // more than one replacement; force values to be an array, then make 
        // sure we have enough values to replace all the placeholders.
        settype($data, 'array');
        if (count($data) < $count) {
            // more placeholders than values
            throw $this->_exception('ERR_NOT_ENOUGH_VALUES', array(
                'text'  => $text,
                'data'  => $data,
            ));
        }
        
        // replace each placeholder with a quoted value
        $offset = 0;
        foreach ($data as $val) {
            // find the next placeholder
            $pos = strpos($text, '?', $offset);
            if ($pos === false) {
                // no more placeholders, exit the data loop
                break;
            }
            
            // replace this question mark with a quoted value
            $val  = $this->quote($val);
            $text = substr_replace($text, $val, $pos, 1);
            
            // update the offset to move us past the quoted value
            $offset = $pos + strlen($val);
        }
        
        return $text;
    }
    
    /**
     * 
     * Quote multiple text-and-value pieces.
     * 
     * The placeholder is a question-mark; all placeholders will be replaced
     * with the quoted value.   For example ...
     * 
     * {{code: php
     *     $sql = Solar::factory('Solar_Sql');
     *     
     *     $list = array(
     *          "WHERE date > ?"   => '2005-01-01',
     *          "  AND date < ?"   => '2005-02-01',
     *          "  AND type IN(?)" => array('a', 'b', 'c'),
     *     );
     *     $safe = $sql->quoteMulti($list);
     *     
     *     // $safe = "WHERE date > '2005-01-02'
     *     //          AND date < 2005-02-01
     *     //          AND type IN('a','b','c')"
     * }}
     * 
     * @param array $list A series of key-value pairs where the key is
     * the placeholder text and the value is the value to be quoted into
     * it.  If the key is an integer, it is assumed that the value is
     * piece of literal text to be used and not quoted.
     * 
     * @param string $sep Return the list pieces separated with this string
     * (for example ' AND '), default null.
     * 
     * @return string An SQL-safe string composed of the list keys and
     * quoted values.
     * 
     */
    public function quoteMulti($list, $sep = null)
    {
        $text = array();
        foreach ((array) $list as $key => $val) {
            if (is_int($key)) {
                // integer $key means a literal phrase and no value to
                // be bound into it
                $text[] = $val;
            } else {
                // string $key means a phrase with a placeholder, and
                // $val should be bound into it.
                $text[] = $this->quoteInto($key, $val); 
            }
        }
        
        // return the condition list
        $result = implode($sep, $text);
        return $result;
    }
    
    /**
     * 
     * Quotes a single identifier name (table, table alias, table column, 
     * index, sequence).  Ignores empty values.
     * 
     * If the name contains ' AS ', this method will separately quote the
     * parts before and after the ' AS '.
     * 
     * If the name contains a space, this method will separately quote the
     * parts before and after the space.
     * 
     * If the name contains a dot, this method will separately quote the
     * parts before and after the dot.
     * 
     * @param string|array $spec The identifier name to quote.  If an array,
     * quotes each element in the array as an identifier name.
     * 
     * @return string|array The quoted identifier name (or array of names).
     * 
     * @see _quoteName()
     * 
     */
    public function quoteName($spec)
    {
        if (is_array($spec)) {
            foreach ($spec as $key => $val) {
                $spec[$key] = $this->quoteName($val);
            }
            return $spec;
        }
        
        // no extraneous spaces
        $spec = trim($spec);
        
        // `original` AS `alias` ... note the 'rr' in strripos
        $pos = strripos($spec, ' AS ');
        if ($pos) {
            // recurse to allow for "table.col"
            $orig  = $this->quoteName(substr($spec, 0, $pos));
            // use as-is
            $alias = $this->_quoteName(substr($spec, $pos + 4));
            return "$orig AS $alias";
        }
        
        // `original` `alias`
        $pos = strrpos($spec, ' ');
        if ($pos) {
            // recurse to allow for "table.col"
            $orig = $this->quoteName(substr($spec, 0, $pos));
            // use as-is
            $alias = $this->_quoteName(substr($spec, $pos + 1));
            return "$orig $alias";
        }
        
        // `table`.`column`
        $pos = strrpos($spec, '.');
        if ($pos) {
            // use both as-is
            $table = $this->_quoteName(substr($spec, 0, $pos));
            $col   = $this->_quoteName(substr($spec, $pos + 1));
            return "$table.$col";
        }
        
        // `name`
        return $this->_quoteName($spec);
    }
    
    /**
     * 
     * Quotes an identifier name (table, index, etc); ignores empty values and
     * values of '*'.
     * 
     * @param string $name The identifier name to quote.
     * 
     * @return string The quoted identifier name.
     * 
     * @see quoteName()
     * 
     */
    protected function _quoteName($name)
    {
        $name = trim($name);
        if ($name == '*') {
            return $name;
        } else {
            return $this->_ident_quote_prefix
                 . $name
                 . $this->_ident_quote_suffix;
        }
    }
    
    /**
     * 
     * Quotes all fully-qualified identifier names ("table.col") in a string,
     * typically an SQL snippet for a SELECT clause.
     * 
     * Does not quote identifier names that are string literals (i.e., inside
     * single or double quotes).
     * 
     * Looks for a trailing ' AS alias' and quotes the alias as well.
     * 
     * @param string|array $spec The string in which to quote fully-qualified
     * identifier names to quote.  If an array, quotes names in each element
     * in the array.
     * 
     * @return string|array The string (or array) with names quoted in it.
     * 
     * @see _quoteNamesIn()
     * 
     */
    public function quoteNamesIn($spec)
    {
        if (is_array($spec)) {
            foreach ($spec as $key => $val) {
                $spec[$key] = $this->quoteNamesIn($val);
            }
            return $spec;
        }
        
        // single and double quotes
        $apos =…

Large files files are truncated, but you can click here to view the full file