[Solar-svn] Revision 2660
pmjones at solarphp.com
pmjones at solarphp.com
Mon Aug 6 11:00:45 CDT 2007
Branch Solar_Sql*: 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).
Solar_Sql_Adapter_Mysql
-----------------------
* [ADD] Method _modSequenceName() to modify the requested sequence name for
portability.
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.
Modified: branches/orm/Solar/Sql/Adapter/Mysql.php
===================================================================
--- branches/orm/Solar/Sql/Adapter/Mysql.php 2007-08-06 15:50:24 UTC (rev 2659)
+++ branches/orm/Solar/Sql/Adapter/Mysql.php 2007-08-06 16:00:45 UTC (rev 2660)
@@ -299,6 +299,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: branches/orm/Solar/Sql/Adapter/Pgsql.php
===================================================================
--- branches/orm/Solar/Sql/Adapter/Pgsql.php 2007-08-06 15:50:24 UTC (rev 2659)
+++ branches/orm/Solar/Sql/Adapter/Pgsql.php 2007-08-06 16:00:45 UTC (rev 2660)
@@ -340,4 +340,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: branches/orm/Solar/Sql/Adapter.php
===================================================================
--- branches/orm/Solar/Sql/Adapter.php 2007-08-06 15:50:24 UTC (rev 2659)
+++ branches/orm/Solar/Sql/Adapter.php 2007-08-06 16:00:45 UTC (rev 2660)
@@ -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
@@ -186,29 +191,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;
/**
*
@@ -353,9 +341,9 @@
// 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
@@ -767,6 +755,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.
*
@@ -917,66 +908,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.
*
@@ -1303,7 +1234,7 @@
*/
public function nextSequence($name)
{
- $name .= '__s'; // we do this to deconflict in PostgreSQL
+ $name = $this->_modSequenceName($name);
$result = $this->_nextSequence($name);
return $result;
}
@@ -1411,7 +1342,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 +1351,7 @@
* 'primary' => (bool) is this a primary key column?
* 'autoinc' => (bool) is this an auto-increment column?
* ),
- * 'fieldTwo' => array(...)
+ * 'col_2' => array(...)
* );
* }}
*
@@ -1457,24 +1388,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();
@@ -1490,9 +1406,12 @@
}
}
+ // 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
);
}
@@ -1570,13 +1489,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 +1509,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 +1542,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 +1561,28 @@
/**
*
+ * Modifies an index name for adapters.
+ *
+ * Most adapters don't need this, but some do (esp. PostgreSQL).
+ *
+ * @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 +1593,7 @@
*/
public function createSequence($name, $start = 1)
{
- $name .= '__s'; // we do this to deconflict in PostgreSQL
+ $name = $this->_modSequenceName($name);
$result = $this->_createSequence($name, $start);
return $result;
}
@@ -1699,15 +1615,14 @@
*
* 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
+ $name = $this->_modSequenceName($name);
$result = $this->_dropSequence($name);
return $result;
}
@@ -1723,7 +1638,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 +1689,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 +1721,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)
);
}
@@ -1882,35 +1800,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