/solar/source/solar/Solar/Sql/Adapter.php
PHP | 2641 lines | 1471 code | 142 blank | 1028 comment | 55 complexity | 8d3a0a5c383d33c112c1d2fa1759e34c MD5 | raw file
Possible License(s): MIT
- <?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 = "'";
- $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);
- }
- }