Details
Bug
Resolved
Major
Description
Problem with schema using Zend_Db_Adapter_Pdo_Pgsql
I have a model
class MyModel extends Zend_Db_Table { protected function _setupMetadata(){ $this->_schema = 'someSchema'; parent::_setupMetadata(); } protected function _setupTableName(){ $this->_name = 'someTable'; parent::_setupTableName(); } }
and when i try make
$obj = new MyModel();
$colect = $obj->fetchAll();
I receive an error of SQL. This occurs because Zend_Db_Select generates a wrong sql instruction, like this:
SELECT "someTable"."id", "someTable"."column1", "someTable"."column2" FROM "someTable"
when would be
SELECT "someTable"."id", "someTable"."column1", "someTable"."column2" FROM "someSchema"."someTable"
I suggest a form to correct this:
In Zend_Db_Table_Abstract
protected function _fetch($where = null, $order = null, $count = null, $offset = null) { // selection tool $select = $this->_db->select(); // the FROM clause - //$select->from($this->_name, $this->_cols); + $select->from($this->_name, $this->_cols, $this->_schema); ...
In Zend_Db_Select
public function from($name, $cols = '*', $schema = null, $schemaName = null) { return $this->joinInner($name, null, $cols, $schemaName); } public function join($name, $cond, $cols = '*', $schemaName = null) { return $this->joinInner($name, $cond, $cols, $schemaName); } public function joinInner($name, $cond, $cols = '*', $schemaName = null) { return $this->_join(self::INNER_JOIN, $name, $cond, $cols, $schemaName); }
make the same for other join methods (joinLeft, joinRight, joinFull, joinCross, joinNatural)
protected function _join($type, $name, $cond, $cols, $schemaName = null) { ... if (!empty($correlationName)) { if (array_key_exists($correlationName, $this->_parts[self::FROM])) { throw new Zend_Db_Select_Exception( "You cannot define a correlation name '$correlationName' more than once"); } $this->_parts[self::FROM][$correlationName] = array( 'joinType' => $type, + 'schemaName' => $schemaName, 'tableName' => $tableName, 'joinCondition' => $cond ); } ... }
and finally
public function __toString() { ... foreach ($this->_parts[self::FROM] as $correlationName => $table) { $tmp = ''; if (empty($from)) { + if ($table['schemaName'] != null) + $tmp .= $this->_adapter->quoteIdentifier($table['schemaName'])."."; // First table is named alone ignoring join information $tmp .= $this->_adapter->quoteTableAs($table['tableName'], $correlationName); } else { // Subsequent tables may have joins if (! empty($table['joinType'])) { $tmp .= ' ' . strtoupper($table['joinType']) . ' '; } + if ($table['schemaName'] != null) + $tmp .= $this->_adapter->quoteIdentifier($table['schemaName'])."."; $tmp .= $this->_adapter->quoteTableAs($table['tableName'], $correlationName); if (! empty($table['joinCondition'])) { $tmp .= ' ON ' . $table['joinCondition']; } } // add the table name and condition // add to the list $from[] = $tmp; } ... }
These alterations must work.
Schema qualified names are already supported.
Throughout Zend_Db classes, wherever a table name is required you can supply:
eg: "table" or "schema.table"
eg: array('schema', 'table')
eg. new Zend_Db_Expr('"odd.schema"."strange.table"')
So in your code above use:
$this->_name = 'someSchema.someTable';or
$this->_name = array('someSchema','someTable');This is all implemented in Zend_Db_Adapter_Abstract::quoteIdentifier(),
and fully supported by Zend_Db_Select, and by Zend_Db_Table.
- a single string containing a qualified or unqualified name.
eg: "table" or "schema.table"
- an array containing the schema as the first item, and name as the second.
eg: array('schema', 'table')
- a Zend_Db_Expr for the rare occasion that a name contains a dot.
eg. new Zend_Db_Expr('"odd.schema"."strange.table"')
So in your code above use:$this->_name = 'someSchema.someTable';$this->_name = array('someSchema','someTable');