[Solar-svn] Revision 2833
pmjones at solarphp.com
pmjones at solarphp.com
Sat Oct 6 11:14:23 CDT 2007
Solar_Sql_Adapter*
==================
Many BC breaks for portability.
The intent of these breaks is to prepare the way for more use of native date,
time, and timestamp column types (when available), and for new support of
Oracle in a portable manner.
Solar_Sql_Adapter
-----------------
* [CHG] All identifiers (table, column, and index) are now allowed 30
characters, and are checked in a common _checkIdentifier() method.
* [FIX] Method createTable() now throws the correct exception when the table
is not created.
* [BRK] Indexes are **no longer** universally forced to table__index__i
format; instead, the index name is determined by the _modIndexName() method.
* [BRK] Sequences are **no longer** universally forced to sequence__s format;
instead, the sequence name is determined by the _modSequenceName() method.
* [BRK] Removed deprecated fetchRow() and fetchRowset() methods.
* [BRK] Renamed ColTypeUnknown exception to ColType (consisten with ColSize
and ColScope).
* [ADD] Added method _postConnect() for post-connection tasks; portability PDO settings have moved here from _connect().
* [CHG] On table creation, returns slightly better error information.
* [FIX] Actually set default values when creating the table
* [CHG] Recent versions of Mysql, Sqlite, and Pgsql all support DROP TABLE IF EXISTS, so that's the dropTable() command now. Also, Pgsql supports DROP SEQUENCE IF EXISTS, so using that now too. These changes are to suppress warnings when the entity doesn't exist.
* [BRK] Method lastInsertId() now takes $table and $col, vice only $name, as params. This is because some DBs ("Postgres") need that information to construct the sequence name. The base method ignores $table and $name.
* [ADD] Added 'cache' config key and $_cache property. This is currently used
to cache fetchTableList() and fetchTableCols() queries in support of the new
Model/Record/Collection system. Use will expand later to cache query
results.
* [CHG] In the abstract adapter, methods fetchTableList() and fetchTableCols()
are now public and act as facades to the new _fetchTableList() and
_fetchTableCols() methods, respectively. This is to enable transparent
caching of results from the underlying adapters.
* [CHG] Methods createTable(), dropTable(), addColumn(), dropColumn(),
createSequence(), and dropSequence() now clear the internal query cache.
Solar_Sql_Adapter_Mssql
-----------------------
* [BRK] Use native "datetime" data type for timestamps
* [BRK] Method lastInsertId() now takes $table and $col, vice only $name, as params. This is in line with the updated Adapter parent.
Solar_Sql_Adapter_Mysql
-----------------------
* [ADD] Method _modSequenceName() to modify the requested sequence name for
portability.
* [FIX] Native "timestamp" type now maps to Solar "int" type (vice "integer")
* [FIX] On table column discover, all integer columns now get NULL for their size
* [CHG] Methods dropTable() and dropSequence() now use DROP TABLE IF EXISTS to avoid notices from MySQL
Solar_Sql_Adapter_Pgsql
-----------------------
* [ADD] Method _modSequenceName() to modify the requested sequence name for
portability.
* [ADD] Method _modIndexName() to modify the requested index name for
portability.
* [FIX] Method lastInsertId() now works, although it's a BC break because of the new signature ("$table, $col" vice "$name")
Solar_Sql_Adapter_Sqlite
------------------------
* [CHG] Method _nextSequence() now uses PDO lastInsertId(), not the class method
* [CHG] Index names are now modified the same as Postgres; i.e., {table}__{index}. Apparently SQLite has the same troubles PostgreSQL does.
Deleted: trunk/Solar/Sql/Adapter/Exception/ColNameLength.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/ColNameLength.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/ColNameLength.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: column name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: column name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_ColNameLength extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/ColNameReserved.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/ColNameReserved.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/ColNameReserved.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: column name is a reserved word.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: column name is a reserved word.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_ColNameReserved extends Solar_Sql_Adapter_Exception {}
Copied: trunk/Solar/Sql/Adapter/Exception/ColType.php (from rev 2801, branches/orm/Solar/Sql/Adapter/Exception/ColType.php)
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/ColType.php (rev 0)
+++ trunk/Solar/Sql/Adapter/Exception/ColType.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -0,0 +1,27 @@
+<?php
+/**
+ *
+ * Exception: column type is unknown.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ * @author Paul M. Jones <pmjones at solarphp.com>
+ *
+ * @license http://opensource.org/licenses/bsd-license.php BSD
+ *
+ * @version $Id$
+ *
+ */
+
+/**
+ *
+ * Exception: column type is unknown.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ */
+class Solar_Sql_Adapter_Exception_ColType extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/ColTypeUnknown.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/ColTypeUnknown.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/ColTypeUnknown.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: column type is unknown.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: column type is unknown.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_ColTypeUnknown extends Solar_Sql_Adapter_Exception {}
Copied: trunk/Solar/Sql/Adapter/Exception/IdentifierLength.php (from rev 2801, branches/orm/Solar/Sql/Adapter/Exception/IdentifierLength.php)
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/IdentifierLength.php (rev 0)
+++ trunk/Solar/Sql/Adapter/Exception/IdentifierLength.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -0,0 +1,27 @@
+<?php
+/**
+ *
+ * Exception: table/column/index identifier name is too long or too short.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ * @author Paul M. Jones <pmjones at solarphp.com>
+ *
+ * @license http://opensource.org/licenses/bsd-license.php BSD
+ *
+ * @version $Id$
+ *
+ */
+
+/**
+ *
+ * Exception: table/column/index identifier name is too long or too short.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ */
+class Solar_Sql_Adapter_Exception_IdentifierLength extends Solar_Sql_Adapter_Exception {}
Copied: trunk/Solar/Sql/Adapter/Exception/IdentifierReserved.php (from rev 2801, branches/orm/Solar/Sql/Adapter/Exception/IdentifierReserved.php)
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/IdentifierReserved.php (rev 0)
+++ trunk/Solar/Sql/Adapter/Exception/IdentifierReserved.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -0,0 +1,27 @@
+<?php
+/**
+ *
+ * Exception: table/column/index identifier name is a reserved word.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ * @author Paul M. Jones <pmjones at solarphp.com>
+ *
+ * @license http://opensource.org/licenses/bsd-license.php BSD
+ *
+ * @version $Id$
+ *
+ */
+
+/**
+ *
+ * Exception: table/column/index identifier name is a reserved word.
+ *
+ * @category Solar
+ *
+ * @package Solar_Sql
+ *
+ */
+class Solar_Sql_Adapter_Exception_IdentifierReserved extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/IdxNameLength.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/IdxNameLength.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/IdxNameLength.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: index name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: index name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_IdxNameLength extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/IdxNoColumns.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/IdxNoColumns.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/IdxNoColumns.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: index has no columns.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: index has no columns.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_IdxNoColumns extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/IdxTypeUnknown.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/IdxTypeUnknown.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/IdxTypeUnknown.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: index type is unknown (should be 'normal' or 'unique').
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: index type is unknown (should be 'normal' or 'unique').
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_IdxTypeUnknown extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/TableNameLength.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/TableNameLength.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/TableNameLength.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: table name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: table name is too long or too short.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_TableNameLength extends Solar_Sql_Adapter_Exception {}
Deleted: trunk/Solar/Sql/Adapter/Exception/TableNameReserved.php
===================================================================
--- trunk/Solar/Sql/Adapter/Exception/TableNameReserved.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Exception/TableNameReserved.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -1,27 +0,0 @@
-<?php
-/**
- *
- * Exception: table name is a reserved word.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- * @author Paul M. Jones <pmjones at solarphp.com>
- *
- * @license http://opensource.org/licenses/bsd-license.php BSD
- *
- * @version $Id$
- *
- */
-
-/**
- *
- * Exception: table name is a reserved word.
- *
- * @category Solar
- *
- * @package Solar_Sql
- *
- */
-class Solar_Sql_Adapter_Exception_TableNameReserved extends Solar_Sql_Adapter_Exception {}
Modified: trunk/Solar/Sql/Adapter/Mssql.php
===================================================================
--- trunk/Solar/Sql/Adapter/Mssql.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Mssql.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -55,7 +55,7 @@
'clob' => 'TEXT',
'date' => 'CHAR(10)',
'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
+ 'timestamp' => 'DATETIME'
);
/**
@@ -208,7 +208,7 @@
* @return array All table names in the database.
*
*/
- public function fetchTableList()
+ protected function _fetchTableList()
{
$cmd = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
$result = $this->query($cmd);
@@ -225,7 +225,7 @@
* @return array An array of table columns.
*
*/
- public function fetchTableCols($table)
+ protected function _fetchTableCols($table)
{
$cmd = "
SELECT
@@ -304,7 +304,7 @@
* @return int The last auto-increment ID value inserted to the database.
*
*/
- public function lastInsertId($name = null)
+ public function lastInsertId($table = null, $col = null)
{
$this->_connect();
return $this->fetchValue('SELECT @@IDENTITY AS id');
Modified: trunk/Solar/Sql/Adapter/Mysql.php
===================================================================
--- trunk/Solar/Sql/Adapter/Mysql.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Mysql.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -76,7 +76,7 @@
// date & time
'date' => 'date',
'datetime' => 'timestamp',
- 'timestamp' => 'integer',
+ 'timestamp' => 'int',
'time' => 'time',
// string
@@ -111,7 +111,7 @@
* @return array All table names in the database.
*
*/
- public function fetchTableList()
+ protected function _fetchTableList()
{
return $this->fetchCol('SHOW TABLES');
}
@@ -125,7 +125,7 @@
* @return array An array of table column information.
*
*/
- public function fetchTableCols($table)
+ protected function _fetchTableCols($table)
{
// mysql> DESCRIBE table_name;
// +--------------+--------------+------+-----+---------+-------+
@@ -172,6 +172,11 @@
'primary' => (bool) ($val['key'] == 'PRI'),
'autoinc' => (bool) (strpos($val['extra'], 'auto_increment') !== false),
);
+
+ // don't keep "size" for integers
+ if (substr($type, -3) == 'int') {
+ $descr[$name]['size'] = null;
+ }
}
// done!
@@ -265,7 +270,7 @@
*/
protected function _dropSequence($name)
{
- return $this->query("DROP TABLE $name");
+ return $this->query("DROP TABLE IF EXISTS $name");
}
/**
@@ -299,6 +304,24 @@
/**
*
+ * Modifies the sequence name.
+ *
+ * MySQL doesn't have sequences, so this adapter uses a table instead.
+ * This means we have to deconflict between "real" tables and tables being
+ * used for sequences, so this method appends "__s" to the sequnce name.
+ *
+ * @param string $name The requested sequence name.
+ *
+ * @return string The modified sequence name.
+ *
+ */
+ protected function _modSequenceName($name)
+ {
+ return $name . '__s';
+ }
+
+ /**
+ *
* Given a column definition, modifies the auto-increment and primary-key
* clauses in place.
*
Modified: trunk/Solar/Sql/Adapter/Pgsql.php
===================================================================
--- trunk/Solar/Sql/Adapter/Pgsql.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Pgsql.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -98,7 +98,7 @@
* @return array All table names in the database.
*
*/
- public function fetchTableList()
+ protected function _fetchTableList()
{
$cmd = "
SELECT DISTINCT table_name
@@ -118,7 +118,7 @@
* @return array
*
*/
- public function fetchTableCols($table)
+ protected function _fetchTableCols($table)
{
// name | type | require | primary | default
// ----------------------+-----------------------------+---------+---------+-------------------------------------------------------------
@@ -276,7 +276,7 @@
*/
protected function _dropSequence($name)
{
- return $this->query("DROP SEQUENCE $name");
+ return $this->query("DROP SEQUENCE IF EXISTS $name");
}
/**
@@ -311,6 +311,29 @@
/**
*
+ * Get the last auto-incremented insert ID from the database.
+ *
+ * Postgres SERIAL and BIGSERIAL types create sequences named in this
+ * fashion: `{$table}_{$col}_seq`.
+ *
+ * <http://www.postgresql.org/docs/7.4/interactive/datatype.html#DATATYPE-SERIAL>
+ *
+ * @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();
+ $name = "{$table}_{$col}_seq";
+ return $this->_pdo->lastInsertId($name);
+ }
+
+ /**
+ *
* Given a column definition, modifies the auto-increment and primary-key
* clauses in place.
*
@@ -340,4 +363,42 @@
$coldef .= ' PRIMARY KEY';
}
}
+
+ /**
+ *
+ * Modifies the sequence name.
+ *
+ * PostgreSQL won't allow a sequence with the same name as a table or
+ * index. This method modifies the name by appending '__s'.
+ *
+ * @param string $name The requested sequence name.
+ *
+ * @return string The modified sequence name.
+ *
+ */
+ protected function _modSequenceName($name)
+ {
+ return $name . '__s';
+ }
+
+ /**
+ *
+ * Modifies the index name.
+ *
+ * PostgreSQL won't allow two indexes of the same name, even if they are
+ * on different tables. This method modifies the name by prefixing with
+ * the table name and two underscores. Thus, for a index named 'foo' on
+ * a table named 'bar', the modified name will be 'foo__bar'.
+ *
+ * @param string $table The table on which the index occurs.
+ *
+ * @param string $name The requested index name.
+ *
+ * @return string The modified index name.
+ *
+ */
+ protected function _modIndexName($table, $name)
+ {
+ return $table . '__' . $name;
+ }
}
Modified: trunk/Solar/Sql/Adapter/Sqlite.php
===================================================================
--- trunk/Solar/Sql/Adapter/Sqlite.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter/Sqlite.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -113,7 +113,7 @@
* @return array All table names in the database.
*
*/
- public function fetchTableList()
+ protected function _fetchTableList()
{
// copied from PEAR DB
$cmd = "SELECT name FROM sqlite_master WHERE type='table' " .
@@ -132,7 +132,7 @@
* @return array
*
*/
- public function fetchTableCols($table)
+ protected function _fetchTableCols($table)
{
// sqlite> create table areas (id INTEGER PRIMARY KEY AUTOINCREMENT,
// name VARCHAR(32) NOT NULL);
@@ -169,12 +169,17 @@
$matches
);
+ // literal default values come back with single-quotes
+ $default = is_string($val['dflt_value'])
+ ? trim($val['dflt_value'], "'")
+ : $val['dflt_value'];
+
$descr[$name] = array(
'name' => $name,
'type' => $type,
'size' => ($size ? (int) $size : null),
'scope' => ($scope ? (int) $scope : null),
- 'default' => $val['dflt_value'],
+ 'default' => $default,
'require' => (bool) ($val['notnull']),
'primary' => (bool) ($val['pk'] == 1),
'autoinc' => (bool) $autoinc,
@@ -259,7 +264,7 @@
*/
protected function _dropSequence($name)
{
- return $this->query("DROP TABLE $name");
+ return $this->query("DROP TABLE IF EXISTS $name");
}
/**
@@ -280,6 +285,27 @@
/**
*
+ * Modifies the index name.
+ *
+ * SQLite won't allow two indexes of the same name, even if they are
+ * on different tables. This method modifies the name by prefixing with
+ * the table name and two underscores. Thus, for a index named 'foo' on
+ * a table named 'bar', the modified name will be 'foo__bar'.
+ *
+ * @param string $table The table on which the index occurs.
+ *
+ * @param string $name The requested index name.
+ *
+ * @return string The modified index name.
+ *
+ */
+ protected function _modIndexName($table, $name)
+ {
+ return $table . '__' . $name;
+ }
+
+ /**
+ *
* Gets a sequence number; creates the sequence if it does not exist.
*
* @param string $name The sequence name.
@@ -304,7 +330,7 @@
}
// get the sequence number
- return $this->lastInsertId();
+ return $this->_pdo->lastInsertId();
}
/**
Modified: trunk/Solar/Sql/Adapter.php
===================================================================
--- trunk/Solar/Sql/Adapter.php 2007-10-06 16:08:20 UTC (rev 2832)
+++ trunk/Solar/Sql/Adapter.php 2007-10-06 16:14:23 UTC (rev 2833)
@@ -19,7 +19,7 @@
*
* Abstract base class for specific RDBMS adapters.
*
- * WHen writing an adapter, you need to override these abstract methods:
+ * When writing an adapter, you need to override these abstract methods:
*
* {{code: php
* abstract public function fetchTableList();
@@ -31,10 +31,15 @@
* abstract protected function _modAutoincPrimary(&$coldef, $autoinc, $primary);
* }}
*
- * Additionally, if backend does not have explicit "LIMIT ... OFFSET" support,
- * you will want to override _limitSelect($stmt, $parts) to rewrite the query
- * in order to emulate limit/select behavior.
+ * 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
@@ -76,8 +81,13 @@
'pass' => null,
'name' => null,
'profiling' => false,
+ 'cache' => array('adapter' => 'Solar_Cache_Adapter_Var'),
);
+ protected $_cache;
+
+ protected $_cache_key_prefix;
+
/**
*
* Map of Solar generic types to RDBMS native types used when creating
@@ -186,29 +196,12 @@
* Max identifier lengths for table, column, and index names used when
* creating portable tables.
*
- * The reasoning behind these numbers is as follows:
+ * We use 30 characters to comply with Oracle maximums.
*
- * - The total length cannot exceed 63 (the Postgres limit).
- *
- * - Reserve 3 chars for suffixes ("__i" for indexes, "__s" for
- * sequences) because Postgres cannot have a table with the same name
- * as an index or sequence.
- *
- * - Reserve 2 chars for table__index separator, because Postgres needs
- * needs unique names for indexes even on different tables.
- *
- * This leaves 58 characters to split between table name and column/index
- * name. I figure table names need more "space", so they get 30 chars,
- * and tables/indexes get 28.
- *
* @var array
*
*/
- protected $_len = array(
- 'table' => 30,
- 'col' => 28,
- 'index' => 28
- );
+ protected $_maxlen = 30;
/**
*
@@ -243,6 +236,7 @@
public function __construct($config = null)
{
parent::__construct($config);
+ $this->_cache = Solar::dependency('Solar_Cache', $this->_config['cache']);
$this->setProfiling($this->_config['profiling']);
}
@@ -324,6 +318,8 @@
*
* Creates a PDO object and connects to the database.
*
+ * Also sets the query-cache key prefix.
+ *
* @return void
*
*/
@@ -340,7 +336,9 @@
// build a DSN
$dsn = $this->_dsn();
- // create PDO object
+ // save the cache-key prefix
+ $this->_cache_key_prefix = get_class($this) . '/' . md5($dsn);
+
// attempt the connection
$this->_pdo = new PDO(
$dsn,
@@ -348,28 +346,55 @@
$this->_config['pass']
);
+ // post-connection tasks
+ $this->_postConnect();
+
+ // retain the profile data?
+ if ($this->_profiling) {
+ $after = microtime(true);
+ $this->_profile[] = array($after - $before, '__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);
+ $this->_pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
- // always use exceptions.
+ // 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);
-
- // retain the profile data?
- if ($this->_profiling) {
- $after = microtime(true);
- $this->_profile[] = array($after - $before, '__CONNECT');
- }
}
/**
*
+ * Gets a full cache key.
+ *
+ * @var 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.
*
@@ -767,6 +792,9 @@
* 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.
*
@@ -860,7 +888,7 @@
return $data;
}
-
+
/**
*
* Fetches a PDOStatement result object.
@@ -917,66 +945,6 @@
/**
*
- * DEPRECATED: Fetch one row as a Solar_Sql_Row object.
- *
- * This method is provided as a transitional aid while moving from Table
- * to Model.
- *
- * @deprecated
- *
- * @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.
- *
- * @param string $class The class name of the object to return; default is
- * 'Solar_Sql_Row'.
- *
- * @return Solar_Sql_Row
- *
- */
- public function fetchRow($spec, $data = array(), $class = null)
- {
- if (! $class) {
- $class = 'Solar_Sql_Row';
- }
- $result = $this->fetchOne($spec, $data);
- return Solar::factory($class, array('data' => $result));
- }
-
- /**
- *
- * DEPRECATED: Fetch all rows as a Solar_Sql_Rowset object.
- *
- * This method is provided as a transitional aid while moving from Table
- * to Model.
- *
- * @deprecated
- *
- * @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.
- *
- * @param string $class The class name of the object to return; default is
- * 'Solar_Sql_Rowset'.
- *
- * @return Solar_Sql_Rowset
- *
- */
- public function fetchRowset($spec, $data = array(), $class = null)
- {
- if (! $class) {
- $class = 'Solar_Sql_Rowset';
- }
- $result = $this->fetchAll($spec, $data);
- return Solar::factory($class, array('data' => $result));
- }
-
- /**
- *
* Builds the SQL statement and returns it as a string instead of
* executing it. Useful for debugging.
*
@@ -1127,7 +1095,7 @@
// add the count and offset
if ($count > 0) {
- $stmt .= " LIMIT $count";
+ $stmt .= "LIMIT $count";
if ($offset > 0) {
$stmt .= " OFFSET $offset";
}
@@ -1279,16 +1247,17 @@
*
* Get the last auto-incremented insert ID from the database.
*
- * @param string $name The name of the auto-increment series; optional,
- * not normally required.
+ * @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($name = null)
+ public function lastInsertId($table = null, $col = null)
{
$this->_connect();
- return $this->_pdo->lastInsertId($name);
+ return $this->_pdo->lastInsertId();
}
/**
@@ -1303,7 +1272,7 @@
*/
public function nextSequence($name)
{
- $name .= '__s'; // we do this to deconflict in PostgreSQL
+ $name = $this->_modSequenceName($name);
$result = $this->_nextSequence($name);
return $result;
}
@@ -1333,8 +1302,19 @@
* @return array A sequential array of table names in the database.
*
*/
- abstract public function fetchTableList();
+ public function fetchTableList()
+ {
+ $key = $this->_getCacheKey(__FUNCTION__);
+ $result = $this->_cache->fetch($key);
+ if (! $result) {
+ $result = $this->_fetchTableList();
+ $this->_cache->add($key, $result);
+ }
+ return $result;
+ }
+ abstract protected function _fetchTableList();
+
/**
*
* Returns an array describing the columns in a table.
@@ -1344,8 +1324,19 @@
* @return array An array of table columns.
*
*/
- abstract public function fetchTableCols($table);
+ public function fetchTableCols($table)
+ {
+ $key = $this->_getCacheKey(__FUNCTION__ . "/$table");
+ $result = $this->_cache->fetch($key);
+ if (! $result) {
+ $result = $this->_fetchTableCols($table);
+ $this->_cache->add($key, $result);
+ }
+ return $result;
+ }
+ abstract protected function _fetchTableCols($table);
+
/**
*
* Given a column specification, parse into datatype, size, and
@@ -1411,7 +1402,7 @@
*
* {{code: php
* $cols = array(
- * 'fieldOne' => array(
+ * 'col_1' => array(
* 'type' => (string) bool, char, int, ...
* 'size' => (int) total length for char|varchar|numeric
* 'scope' => (int) decimal places for numeric
@@ -1420,7 +1411,7 @@
* 'primary' => (bool) is this a primary key column?
* 'autoinc' => (bool) is this an auto-increment column?
* ),
- * 'fieldTwo' => array(...)
+ * 'col_2' => array(...)
* );
* }}
*
@@ -1437,6 +1428,7 @@
*/
public function createTable($table, $cols)
{
+ $this->_cache->deleteAll();
$stmt = $this->_sqlCreateTable($table, $cols);
$this->query($stmt);
}
@@ -1457,24 +1449,9 @@
*/
protected function _sqlCreateTable($table, $cols)
{
- // table name can only be so many chars
- $len = strlen($table);
- if ($len < 1 || $len > $this->_len['table']) {
- throw $this->_exception(
- 'ERR_TABLE_NAME_LENGTH',
- array('table' => $table)
- );
- }
+ // make sure the table name is a valid identifier
+ $this->_checkIdentifier('table', $table);
- // table name must be a valid word, and cannot end in
- // "__s" (this is to prevent sequence table collisions)
- if (! $this->_validIdentifier($table) || substr($table, -3) == "__s") {
- throw $this->_exception(
- 'ERR_TABLE_NAME_RESERVED',
- array('table' => $table)
- );
- }
-
// array of column definitions
$coldef = array();
@@ -1486,13 +1463,16 @@
try {
$coldef[] = $this->_sqlColdef($name, $info);
} catch (Exception $e) {
- $err[$name] = $e->getInfo();
+ $err[$name] = array($e->getCode(), $e->getInfo());
}
}
+ // were there errors?
if ($err) {
+ // add the table name to the info and throw the exception
+ $err['__table'] = $table;
throw $this->_exception(
- 'ERR_CREATE_TABLE',
+ 'ERR_TABLE_NOT_CREATED',
$err
);
}
@@ -1504,7 +1484,7 @@
/**
*
- * Drops a table from the database.
+ * Drops a table from the database, if it exists.
*
* @param string $table The table name.
*
@@ -1513,7 +1493,8 @@
*/
public function dropTable($table)
{
- return $this->query("DROP TABLE $table");
+ $this->_cache->deleteAll();
+ return $this->query("DROP TABLE IF EXISTS $table");
}
/**
@@ -1545,6 +1526,7 @@
*/
public function addColumn($table, $name, $info)
{
+ $this->_cache->deleteAll();
$coldef = $this->_sqlColdef($name, $info);
$stmt = "ALTER TABLE $table ADD COLUMN $coldef";
return $this->query($stmt);
@@ -1563,6 +1545,7 @@
*/
public function dropColumn($table, $name)
{
+ $this->_cache->deleteAll();
return $this->query("ALTER TABLE $table DROP COLUMN $name");
}
@@ -1570,13 +1553,10 @@
*
* Creates a portable index on a table.
*
- * Indexes are automatically renamed to "tablename__indexname__i" for
- * portability reasons.
+ * @param string $table The name of the table for the index.
*
- * @param string $table The name of the table for the index (1-30 chars).
+ * @param string $name The name of the index.
*
- * @param string $name The name of the index (1-28 chars).
- *
* @param bool $unique Whether or not the index is unique.
*
* @param array $cols The columns in the index. If empty, uses the
@@ -1593,39 +1573,21 @@
$cols = $name;
}
- // check the table name length
- $len = strlen($table);
- if ($len < 1 || $len > $this->_len['table']) {
- throw $this->_exception(
- 'ERR_TABLE_NAME_LENGTH',
- array('table' => $table)
- );
- }
+ // check the table and index names
+ $this->_checkIdentifier('table', $table);
+ $this->_checkIdentifier('index', $name);
- // check the index name length
- $len = strlen($name);
- if ($len < 1 || $len > $this->_len['index']) {
- throw $this->_exception(
- 'ERR_IDX_NAME_LENGTH',
- array('table' => $table, 'index' => $name)
- );
- }
-
// create a string of column names
$cols = implode(', ', (array) $cols);
- // we prefix all index names with the table name,
- // and suffix all index names with '__i'. this
- // is to soothe PostgreSQL, which demands that index
- // names not collide, even when they indexes are on
- // different tables.
- $fullname = $table . '__' . $name . '__i';
+ // modify the index name as-needed
+ $name = $this->_modIndexName($table, $name);
// create index entry
if ($unique) {
- $cmd = "CREATE UNIQUE INDEX $fullname ON $table ($cols)";
+ $cmd = "CREATE UNIQUE INDEX $name ON $table ($cols)";
} else {
- $cmd = "CREATE INDEX $fullname ON $table ($cols)";
+ $cmd = "CREATE INDEX $name ON $table ($cols)";
}
return $this->query($cmd);
}
@@ -1644,8 +1606,8 @@
*/
public function dropIndex($table, $name)
{
- $fullname = $table . '__' . $name . '__i';
- return $this->_dropIndex($table, $fullname);
+ $name = $this->_modIndexName($table, $name);
+ return $this->_dropIndex($table, $name);
}
/**
@@ -1663,10 +1625,28 @@
/**
*
+ * 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; this will be
- * automatically suffixed with '__s' for portability reasons.
+ * @param string $name The sequence name to create.
*
* @param string $start The starting sequence number.
*
@@ -1677,7 +1657,8 @@
*/
public function createSequence($name, $start = 1)
{
- $name .= '__s'; // we do this to deconflict in PostgreSQL
+ $this->_cache->deleteAll();
+ $name = $this->_modSequenceName($name);
$result = $this->_createSequence($name, $start);
return $result;
}
@@ -1699,15 +1680,15 @@
*
* Drops a sequence from the database.
*
- * @param string $name The sequence name to drop; this will be
- * automatically suffixed with '__s' for portability reasons.
+ * @param string $name The sequence name to drop.
*
* @return void
*
*/
public function dropSequence($name)
{
- $name .= '__s'; // we do this to deconflict in PostgreSQL
+ $this->_cache->deleteAll();
+ $name = $this->_modSequenceName($name);
$result = $this->_dropSequence($name);
return $result;
}
@@ -1723,7 +1704,24 @@
*/
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
@@ -1757,23 +1755,9 @@
*/
protected function _sqlColdef($name, $info)
{
- // validate column name length
- $len = strlen($name);
- if ($len < 1 || $len > $this->_len['col']) {
- throw $this->_exception(
- 'ERR_COL_NAME_LENGTH',
- array('col' => $name)
- );
- }
+ // make sure the column name is a valid identifier
+ $this->_checkIdentifier('column', $name);
- // column name must be a valid word
- if (! $this->_validIdentifier($name)) {
- throw $this->_exception(
- 'ERR_COL_NAME_RESERVED',
- array('col' => $name)
- );
- }
-
// short-form of definition
if (is_string($info)) {
$info = array('type' => $info);
@@ -1803,7 +1787,7 @@
// is it a recognized column type?
if (! array_key_exists($type, $this->_solar_native)) {
throw $this->_exception(
- 'ERR_COL_TYPE_UNKNOWN',
+ 'ERR_COL_TYPE',
array('col' => $name, 'type' => $type)
);
}
@@ -1855,6 +1839,11 @@
// set the "NULL"/"NOT NULL" portion
$coldef .= ($require) ? ' NOT NULL' : ' NULL';
+ // set the default value, if any
+ if ($default) {
+ $coldef .= ' DEFAULT ' . $this->quote($default);
+ }
+
// modify with auto-increment and primary-key portions
$this->_modAutoincPrimary($coldef, $autoinc, $primary);
@@ -1882,35 +1871,70 @@
*
* Check if a table, column, or index name is a valid identifier.
*
- * @param string $word The word to check.
+ * @param string $type The indentifier type: table, index, sequence, etc.
*
+ * @param string $name The identifier name to check.
+ *
* @return bool True if valid, false if not.
*
*/
- protected function _validIdentifier($word)
+ protected function _checkIdentifier($type, $name)
{
+ // list of reserved words
static $reserved;
if (! isset($reserved)) {
$reserved = Solar::factory('Solar_Sql_Reserved');
}
- // is it a reserved word?
- if (in_array(strtoupper($word), $reserved->words)) {
- return false;
+ // identifier must not be a reserved word
+ if (in_array(strtoupper($name), $reserved->words)) {
+ throw $this->_exception(
+ 'ERR_IDENTIFIER_RESERVED',
+ array(
+ 'type' => $type,
+ 'name' => $name,
+ )
+ );
}
+ // validate identifier length
+ $len = strlen($name);
+ if ($len < 1 || $len > $this->_maxlen) {
+ throw $this->_exception(
+ 'ERR_IDENTIFIER_LENGTH',
+ array(
+ 'type' => $type,
+ 'name' => $name,
+ 'min' => 1,
+ 'max' => $this->_maxlen,
+ )
+ );
+ }
+
// only a-z, 0-9, and _ are allowed in words.
// must start with a letter, not a number or underscore.
- if (! preg_match('/^[a-z][a-z0-9_]*$/', $word)) {
- return false;
+ $regex = '/^[a-z][a-z0-9_]*$/';
+ if (! preg_match($regex, $name)) {
+ throw $this->_exception(
+ 'ERR_IDENTIFIER_CHARS',
+ array(
+ 'type' => $type,
+ 'name' => $name,
+ 'regex' => $regex,
+ )
+ );
}
// must not have two or more underscores in a row
- if (strpos($word, '__') !== false) {
- return false;
+ if (strpos($name, '__') !== false) {
+ throw $this->_exception(
+ 'ERR_IDENTIFIER_UNDERSCORES',
+ array(
+ 'type' => $type,
+ 'name' => $name,
+ 'regex' => $regex,
+ )
+ );
}
-
- // guess it's OK
- return true;
}
}
More information about the Solar-svn
mailing list