Location: PHPKode > projects > SolarPHP > solar-system-1.1.1/solar/source/solar/Solar/Sql/Adapter.php
<?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 <hide@address.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 = "'";
        $quot = '"';
        
        // look for ', ", \', or \" in the string.
        // match closing quotes against the same number of opening quotes.
        $list = preg_split(
            "/(($apos+|$quot+|\\$apos+|\\$quot+).*?\\2)/",
            $spec,
            -1,
            PREG_SPLIT_DELIM_CAPTURE
        );
        
        // concat the pieces back together, quoting names as we go.
        $spec = null;
        $last = count($list) - 1;
        foreach ($list as $key => $val) {
            
            // skip elements 2, 5, 8, 11, etc. as artifacts of the back-
            // referenced split; these are the trailing/ending quote
            // portions, and already included in the previous element.
            // this is the same as every third element from zero.
            if (($key+1) % 3 == 0) {
                continue;
            }
            
            // is there an apos or quot anywhere in the part?
            $is_string = strpos($val, $apos) !== false ||
                         strpos($val, $quot) !== false;
            
            if ($is_string) {
                // string literal
                $spec .= $val;
            } else {
                // sql language.
                // look for an AS alias if this is the last element.
                if ($key == $last) {
                    // note the 'rr' in strripos
                    $pos = strripos($val, ' AS ');
                    if ($pos) {
                        // quote the alias name directly
                        $alias = $this->_quoteName(substr($val, $pos + 4));
                        $val = substr($val, 0, $pos) . " AS $alias";
                    }
                }
                
                // now quote names in the language.
                $spec .= $this->_quoteNamesIn($val);
            }
        }
        
        // done!
        return $spec;
    }
    
    /**
     * 
     * Quotes all fully-qualified identifier names ("table.col") in a string.
     * 
     * @param string|array $text 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()
     * 
     */
    protected function _quoteNamesIn($text)
    {
        $word = "[a-z_][a-z0-9_]+";
        
        $find = "/(\\b)($word)\\.($word)(\\b)/i";
        
        $repl = '$1'
              . $this->_ident_quote_prefix
              . '$2'
              . $this->_ident_quote_suffix
              . '.'
              . $this->_ident_quote_prefix
              . '$3'
              . $this->_ident_quote_suffix
              . '$4'
              ;
              
        $text = preg_replace($find, $repl, $text);
        
        return $text;
    }
    
    
    // -----------------------------------------------------------------
    // 
    // Auto-increment and sequence reading.
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Get the last auto-incremented insert ID from the database.
     * 
     * @param string $table The table name on which the auto-increment occurred.
     * 
     * @param string $col The name of the auto-increment column.
     * 
     * @return int The last auto-increment ID value inserted to the database.
     * 
     */
    public function lastInsertId($table = null, $col = null)
    {
        $this->connect();
        return $this->_pdo->lastInsertId();
    }
    
    /**
     * 
     * Gets the next number in a sequence; creates the sequence if it does not exist.
     * 
     * @param string $name The sequence name; this will be 
     * automatically suffixed with '__s' for portability reasons.
     * 
     * @return int The next number in the sequence.
     * 
     */
    public function nextSequence($name)
    {
        $name = $this->_modSequenceName($name);
        $result = $this->_nextSequence($name);
        return $result;
    }
    
    /**
     * 
     * Gets the next sequence number; creates the sequence if needed.
     * 
     * @param string $name The sequence name to increment.
     * 
     * @return int The next sequence number.
     * 
     */
    abstract protected function _nextSequence($name);
    
    
    // -----------------------------------------------------------------
    // 
    // Table and column information reading.
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Returns a list of database tables from the cache; if the cache entry
     * is not available, queries the database for the list of tables.
     * 
     * @param string $schema Fetch tbe list of tables in this database
     * schema; when empty, uses the current or default schema.
     * 
     * @return array A sequential array of table names in the database.
     * 
     */
    public function fetchTableList($schema = null)
    {
        if ($schema) {
            $key = $this->_getCacheKey("table_list/$schema");
        } else {
            $key = $this->_getCacheKey("table_list");
        }
        
        $result = $this->_cache->fetch($key);
        if (! $result) {
            $result = $this->_fetchTableList($schema);
            $this->_cache->add($key, $result);
        }
        return $result;
    }
    
    /**
     * 
     * Returns a list of database tables.
     * 
     * @param string $schema Fetch tbe list of tables in this database
     * schema; when empty, uses the current or default schema.
     * 
     * @return array A sequential array of table names in the database.
     * 
     */
    abstract protected function _fetchTableList($schema);
    
    /**
     * 
     * Returns an array describing table columns from the cache; if the cache
     * entry is not available, queries the database for the column
     * descriptions.
     * 
     * @param string $spec The table or schema.table to fetch columns for.
     * 
     * @return array An array of table columns.
     * 
     */
    public function fetchTableCols($spec)
    {
        $key = $this->_getCacheKey("table/$spec/cols");
        $result = $this->_cache->fetch($key);
        if (! $result) {
            list($schema, $table) = $this->_splitSchemaIdent($spec);
            $result = $this->_fetchTableCols($table, $schema);
            $this->_cache->add($key, $result);
        }
        return $result;
    }
    
    /**
     * 
     * Returns an array describing the columns in a table.
     * 
     * @param string $table The table name to fetch columns for.
     * 
     * @param string $schema The schema in which the table resides.
     * 
     * @return array An array of table columns.
     * 
     */
    abstract protected function _fetchTableCols($table, $schema);
    
    /**
     * 
     * Given a column specification, parse into datatype, size, and 
     * decimal scope.
     * 
     * @param string $spec The column specification; for example,
     * "VARCHAR(255)" or "NUMERIC(10,2)".
     * 
     * @return array A sequential array of the column type, size, and scope.
     * 
     */
    protected function _getTypeSizeScope($spec)
    {
        $spec  = strtolower($spec);
        $type  = null;
        $size  = null;
        $scope = null;
        
        // find the parens, if any
        $pos = strpos($spec, '(');
        if ($pos === false) {
            // no parens, so no size or scope
            $type = $spec;
        } else {
            // find the type first.
            $type = substr($spec, 0, $pos);
            
            // there were parens, so there's at least a size.
            // remove parens to get the size.
            $size = trim(substr($spec, $pos), '()');
            
            // a comma in the size indicates a scope.
            $pos = strpos($size, ',');
            if ($pos !== false) {
                $scope = substr($size, $pos + 1);
                $size  = substr($size, 0, $pos);
            }
        }
        
        foreach ($this->_native_solar as $native => $solar) {
            // $type is already lowered
            if ($type == strtolower($native)) {
                $type = strtolower($solar);
                break;
            }
        }
        
        return array($type, $size, $scope);
    }
    
    /**
     * 
     * Returns an array describing table indexes from the cache; if the cache
     * entry is not available, queries the database for the index information.
     * 
     * @param string $spec The table or schema.table name to fetch indexes
     * for.
     * 
     * @return array An array of table indexes.
     * 
     */
    public function fetchIndexInfo($spec)
    {
        $key = $this->_getCacheKey("table/$spec/index");
        $result = $this->_cache->fetch($key);
        if (! $result) {
            list($schema, $table) = $this->_splitSchemaIdent($spec);
            $result = $this->_fetchIndexInfo($table, $schema);
            $this->_cache->add($key, $result);
        }
        return $result;
    }
    
    /**
     * 
     * Returns an array of index information for a table.
     * 
     * @param string $table The table name to fetch indexes for.
     * 
     * @param string $schema The schema in which the table resides.
     * 
     * @return array An array of table indexes.
     * 
     */
    abstract protected function _fetchIndexInfo($table, $schema);
    
    // -----------------------------------------------------------------
    // 
    // Table, column, index, and sequence management.
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Creates a portable table.
     * 
     * The $cols parameter should be in this format ...
     * 
     * {{code: php
     *     $cols = array(
     *       'col_1' => array(
     *         'type'    => (string) bool, char, int, ...
     *         'size'    => (int) total length for char|varchar|numeric
     *         'scope'   => (int) decimal places for numeric
     *         'default' => (bool) the default value, if any
     *         'require' => (bool) is the value required to be NOT NULL?
     *         'primary' => (bool) is this a primary key column?
     *         'autoinc' => (bool) is this an auto-increment column?
     *       ),
     *       'col_2' => array(...)
     *     );
     * }}
     * 
     * For available field types, see Solar_Sql_Adapter::$_native.
     * 
     * @param string $table The name of the table to create.
     * 
     * @param array $cols Array of columns to create.
     * 
     * @return string An SQL string.
     * 
     */
    public function createTable($table, $cols)
    {
        $this->_cache->deleteAll();
        $stmt = $this->_sqlCreateTable($table, $cols);
        $this->query($stmt);
    }
    
    /**
     * 
     * Returns a CREATE TABLE command string for the adapter.
     * 
     * We use this so that certain adapters can append table types
     * to the creation statment (for example MySQL).
     * 
     * @param string $table The table name to create.
     * 
     * @param string $cols The column definitions.
     * 
     * @return string A CREATE TABLE command string.
     * 
     */
    protected function _sqlCreateTable($table, $cols)
    {
        // make sure the table name is a valid identifier
        $this->_checkIdentifier('table', $table);
        
        // array of column definitions
        $coldef = array();
        
        // use this to stack errors when creating definitions
        $err = array();
        
        // loop through each column and get its definition
        foreach ($cols as $name => $info) {
            try {
                $coldef[] = $this->_sqlColdef($name, $info);
            } catch (Solar_Sql_Exception $e) {
                throw $this->_exception('ERR_TABLE_NOT_CREATED', array(
                    'table' => $table,
                    'error' => $e->getMessage(),
                ));
                $err[$name] = array($e->getCode(), $e->getInfo());
            }
        }
        
        // no errors, build a return the CREATE statement
        $cols = implode(",\n    ", $coldef);
        $table = $this->quoteName($table);
        return "CREATE TABLE $table (\n    $cols\n)";
    }
    
    /**
     * 
     * Drops a table from the database, if it exists.
     * 
     * @param string $table The table name.
     * 
     * @return mixed
     * 
     */
    public function dropTable($table)
    {
        $this->_cache->deleteAll();
        $table = $this->quoteName($table);
        return $this->query("DROP TABLE IF EXISTS $table");
    }
    
    /**
     * 
     * Adds a portable column to a table in the database.
     * 
     * The $info parameter should be in this format ...
     * 
     * {{code: php
     *     $info = array(
     *         'type'    => (string) bool, char, int, ...
     *         'size'    => (int) total length for char|varchar|numeric
     *         'scope'   => (int) decimal places for numeric
     *         'default' => (bool) the default value, if any
     *         'require' => (bool) is the value required to be NOT NULL?
     *         'primary' => (bool) is this a primary key column?
     *         'autoinc' => (bool) is this an auto-increment column?
     *     );
     * }}
     * 
     * @param string $table The table name (1-30 chars).
     * 
     * @param string $name The column name to add (1-28 chars).
     * 
     * @param array $info Information about the column.
     * 
     * @return mixed
     * 
     */
    public function addColumn($table, $name, $info)
    {
        $this->_cache->deleteAll();
        $coldef = $this->_sqlColdef($name, $info);
        $table = $this->quoteName($table);
        $stmt = "ALTER TABLE $table ADD COLUMN $coldef";
        return $this->query($stmt);
    }
    
    /**
     * 
     * Drops a column from a table in the database.
     * 
     * @param string $table The table name.
     * 
     * @param string $name The column name to drop.
     * 
     * @return mixed
     * 
     */
    public function dropColumn($table, $name)
    {
        $this->_cache->deleteAll();
        $table = $this->quoteName($table);
        return $this->query("ALTER TABLE $table DROP COLUMN $name");
    }
    
    /**
     * 
     * Creates a portable index on a table.
     * 
     * @param string $table The name of the table for the index.
     * 
     * @param string $name The name of the index.
     * 
     * @param bool $unique Whether or not the index is unique.
     * 
     * @param array $cols The columns in the index.  If empty, uses the
     * $name parameters as the column name.
     * 
     * @return void
     * 
     */
    public function createIndex($table, $name, $unique = false, $cols = null)
    {
        // are there any columns for the index?
        if (empty($cols)) {
            // take the column name from the index name
            $cols = $name;
        }
        
        // check the table and index names
        $this->_checkIdentifier('table', $table);
        $this->_checkIdentifier('index', $name);
        
        // modify the index name as-needed
        $name = $this->_modIndexName($table, $name);
        
        // quote identifiers
        $name = $this->quoteName($name);
        $table = $this->quoteName($table);
        $cols = $this->quoteName($cols);
        
        // create a string of column names
        $cols = implode(', ', (array) $cols);
        
        // create index entry statement
        if ($unique) {
            $stmt = "CREATE UNIQUE INDEX $name ON $table ($cols)";
        } else {
            $stmt = "CREATE INDEX $name ON $table ($cols)";
        }
        return $this->query($stmt);
    }
    
    
    /**
     * 
     * Drops an index from a table in the database.
     * 
     * @param string $table The table name.
     * 
     * @param string $name The index name to drop.
     * 
     * @return mixed
     * 
     */
    public function dropIndex($table, $name)
    {
        $name = $this->_modIndexName($table, $name);
        return $this->_dropIndex($table, $name);
    }
    
    /**
     * 
     * Drops an index.
     * 
     * @param string $table The table of the index.
     * 
     * @param string $name The index name.
     * 
     * @return void
     * 
     */
    abstract protected function _dropIndex($table, $name);
    
    /**
     * 
     * Modifies an index name for adapters.
     * 
     * Most adapters don't need this, but some do (e.g. PostgreSQL and SQLite).
     * 
     * @param string $table The table on which the index occurs.
     * 
     * @param string $name The requested index name.
     * 
     * @return string The modified index name (most adapters do not modify the
     * name).
     * 
     */
    protected function _modIndexName($table, $name)
    {
        return $name;
    }
    
    /**
     * 
     * Creates a sequence in the database.
     * 
     * @param string $name The sequence name to create.
     * 
     * @param string $start The starting sequence number.
     * 
     * @return void
     * 
     * @todo Check name length.
     * 
     */
    public function createSequence($name, $start = 1)
    {
        $this->_cache->deleteAll();
        $name = $this->_modSequenceName($name);
        $result = $this->_createSequence($name, $start);
        return $result;
    }
    
    /**
     * 
     * Creates a sequence, optionally starting at a certain number.
     * 
     * @param string $name The sequence name to create.
     * 
     * @param int $start The first sequence number to return.
     * 
     * @return void
     * 
     */
    abstract protected function _createSequence($name, $start = 1);
    
    /**
     * 
     * Drops a sequence from the database.
     * 
     * @param string $name The sequence name to drop.
     * 
     * @return void
     * 
     */
    public function dropSequence($name)
    {
        $this->_cache->deleteAll();
        $name = $this->_modSequenceName($name);
        $result = $this->_dropSequence($name);
        return $result;
    }
    
    /**
     * 
     * Drops a sequence.
     * 
     * @param string $name The sequence name to drop.
     * 
     * @return void
     * 
     */
    abstract protected function _dropSequence($name);
    
    /**
     * 
     * Modifies a sequence name for adapters.
     * 
     * Most adapters don't need this, but some do (esp. MySQL and PostgreSQL).
     * 
     * @param string $name The requested sequence name.
     * 
     * @return string The modified sequence name (most adapters do not
     * modify the name).
     * 
     */
    protected function _modSequenceName($name)
    {
        return $name;
    }
    
    
    // -----------------------------------------------------------------
    // 
    // Support
    // 
    // -----------------------------------------------------------------
    
    /**
     * 
     * Returns a column definition string.
     * 
     * The $info parameter should be in this format ...
     * 
     * {{code: php
     *     $info = array(
     *         'type'    => (string) bool, char, int, ...
     *         'size'    => (int) total length for char|varchar|numeric
     *         'scope'   => (int) decimal places for numeric
     *         'default' => (bool) the default value, if any
     *         'require' => (bool) is the value required to be NOT NULL?
     *         'primary' => (bool) is this a primary key column?
     *         'autoinc' => (bool) is this an auto-increment column?
     *     );
     * }}
     * 
     * @param string $name The column name.
     * 
     * @param array $info The column information.
     * 
     * @return string The column definition string.
     * 
     */
    protected function _sqlColdef($name, $info)
    {
        // make sure the column name is a valid identifier
        $this->_checkIdentifier('column', $name);
        
        // short-form of definition
        if (is_string($info)) {
            $info = array('type' => $info);
        }
        
        // set default values for these variables
        $tmp = array(
            'type'    => null,
            'size'    => null,
            'scope'   => null,
            'default' => null,
            'require' => null,
            'primary' => false,
            'autoinc' => false,
        );
        
        $info = array_merge($tmp, $info);
        extract($info); // see array keys, above
        
        // force values
        $name    = trim(strtolower($name));
        $type    = strtolower(trim($type));
        $size    = (int) $size;
        $scope   = (int) $scope;
        $require = (bool) $require;
        
        // is it a recognized column type?
        if (! array_key_exists($type, $this->_solar_native)) {
            throw $this->_exception('ERR_COL_TYPE', array(
                'col' => $name,
                'type' => $type,
            ));
        }
        
        // basic declaration string
        switch ($type) {
        
        case 'char':
        case 'varchar':
            // does it have a valid size?
            if ($size < 1 || $size > 255) {
                throw $this->_exception('ERR_COL_SIZE', array(
                    'col' => $name,
                    'size' => $size,
                ));
            } else {
                // replace the 'size' placeholder
                $coldef = $this->_solar_native[$type] . "($size)";
            }
            break;
        
        case 'numeric':
        
            if ($size < 1 || $size > 255) {
                throw $this->_exception('ERR_COL_SIZE', array(
                    'col' => $name,
                    'size' => $size,
                    'scope' => $scope,
                ));
            }
            
            if ($scope < 0 || $scope > $size) {
                throw $this->_exception('ERR_COL_SCOPE', array(
                    'col' => $name,
                    'size' => $size,
                    'scope' => $scope,
                ));
            }
            
            // replace the 'size' and 'scope' placeholders
            $coldef = $this->_solar_native[$type] . "($size,$scope)";
            
            break;
        
        default:
            $coldef = $this->_solar_native[$type];
            break;
        
        }
        
        // set the "NULL"/"NOT NULL" portion
        $coldef .= ($require) ? ' NOT NULL' : ' NULL';
        
        // set the default value, if any.
        // use isset() to allow for '0' and '' values.
        if (isset($default)) {
            $coldef .= ' DEFAULT ' . $this->quote($default);
        }
        
        // modify with auto-increment and primary-key portions
        $this->_modAutoincPrimary($coldef, $autoinc, $primary);
        
        // done
        $name = $this->quoteName($name);
        return "$name $coldef";
    }
    
    /**
     * 
     * Given a column definition, modifies the auto-increment and primary-key
     * clauses in place.
     * 
     * @param string &$coldef The column definition as it is now.
     * 
     * @param bool $autoinc Whether or not this is an auto-increment column.
     * 
     * @param bool $primary Whether or not this is a primary-key column.
     * 
     * @return void
     * 
     */
    abstract protected function _modAutoincPrimary(&$coldef, $autoinc, $primary);
    
    /**
     * 
     * Check if a table, index, or column name is a valid portable identifier.
     * Throws an exception on failure.
     * 
     * @param string $type The indentifier type: table, index, sequence, etc.
     * 
     * @param string $name The identifier name to check.
     * 
     * @return void
     * 
     */
    protected function _checkIdentifier($type, $name)
    {
        if ($type == 'column') {
            $this->_checkIdentifierColumn($name);
        } else {
            list($schema, $ident) = $this->_splitSchemaIdent($name);
            if ($schema) {
                $this->_checkIdentifierPart($type, $name, $schema);
            }
            $this->_checkIdentifierPart($type, $name, $ident);
        }
    }
    
    /**
     * 
     * Checks one part of a dotted identifier (schema.table, database.table,
     * etc).  Throws an exception on failure.
     * 
     * @param string $type The identifier type (table, index, etc).
     * 
     * @param string $name The full identifier name (with dots, if any).
     * 
     * @param string $part The part of the name that we're checking.
     * 
     * @return void
     * 
     */
    protected function _checkIdentifierPart($type, $name, $part)
    {
        // validate identifier length
        $len = strlen($part);
        if ($len < 1 || $len > $this->_maxlen) {
            throw $this->_exception('ERR_IDENTIFIER_LENGTH', array(
                'type' => $type,
                'name' => $name,
                'part' => $part,
                'min'  => 1,
                'max'  => $this->_maxlen,
                'len'  => $len,
            ));
        }
        
        // only a-z, 0-9, and _ are allowed in words.
        // must start with a letter, not a number or underscore.
        $regex = '/^[a-z][a-z0-9_]*$/';
        if (! preg_match($regex, $name)) {
            throw $this->_exception('ERR_IDENTIFIER_CHARS', array(
                'type'  => $type,
                'name'  => $name,
                'part'  => $part,
                'regex' => $regex,
            ));
        }
    }
    
    /**
     * 
     * Checks a column name.
     * 
     * @param string $name The column name.
     * 
     * @return void
     * 
     */
    protected function _checkIdentifierColumn($name)
    {
        $this->_checkIdentifierPart('column', $name, $name);
        
        // also, must not have two or more underscores in a row
        if (strpos($name, '__') !== false) {
            throw $this->_exception('ERR_IDENTIFIER_UNDERSCORES', array(
                'type'  => 'column',
                'name'  => $name,
            ));
        }
    }
    
    /**
     * 
     * Splits a `schema.table` identifier into its component parts.
     * 
     * @param string $spec The `table` or `schema.table` identifier.
     * 
     * @return array A sequential array where element 0 is the schema and
     * element 1 is the table name.
     * 
     */
    protected function _splitSchemaIdent($spec)
    {
        $pos = strpos($spec, '.');
        if ($pos !== false) {
            $schema = substr($spec, 0, $pos);
            $ident  = substr($spec, $pos + 1);
        } else {
            $schema = null;
            $ident  = $spec;
        }
        return array($schema, $ident);
    }
}
Return current item: SolarPHP