ZF-1816: PostgreSQL: Incorrect sequence name quoting

Issue Type:
Bug
Created:
2007-08-06T04:50:07.000+0000
Last Updated:
2009-05-22T06:52:51.000+0000
Status:
Resolved
Fix version(s):
  • 1.8.2 (27/May/09)
Reporter:
Marcin Miko?ajczak (memfis)
Assignee:
Ralph Schindler (ralph)
Tags:
  • Zend_Db
Related issues:
Attachments:

Description

As per http://postgresql.org/docs/8.2/… quoting a mixed-case sequence name requires putting it also in " ", otherwise the name will be converted to lower-case. Zend_Db_Adapter_Pdo_Pgsql never adds the additional doublequotes mentioned, only the typical singlequtoes used for string constants. Column name quoting in SELECT statements made by the PostgreSQL adapter, however, seems to know about this requirement. As far as I can tell, the problem lies in the following functions:


    public function lastSequenceId($sequenceName)
    {
        $this->_connect();
        $value = $this->fetchOne("SELECT CURRVAL(".$this->quote($sequenceName).")");
        return $value;
    }

    public function nextSequenceId($sequenceName)
    {
        $this->_connect();
        $value = $this->fetchOne("SELECT NEXTVAL(".$this->quote($sequenceName).")");
        return $value;
    }

I would have prepared a patch if I knew how (in terms of design and abstracting this additional quoting process) this should be done.

Comments

Posted by Darby Felton (darby) on 2007-08-06T10:48:33.000+0000

Assigning to [~bkarwin] to initiate issue review.

Posted by Krasimir Yosifov (x3mer) on 2007-11-09T12:00:47.000+0000

I think that problem is here: Zend_Db_Table_Abstract.php from line 665


        if ($this->_sequence === true && $this->_db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
            $this->_sequence = "{$this->_name}_{$pkIdentity}_seq";
            if ($this->_schema) {
                $this->_sequence = $this->_schema . '.' . $this->_sequence;
            }
        }

I suggest this code


        if ($this->_sequence === true && $this->_db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
            $this->_sequence = $this->_db->quoteIdentifier("{$this->_name}_{$pkIdentity}_seq");
            if ($this->_schema) {
                $this->_sequence = $this->_db->quoteIdentifier($this->_schema) . '.' . $this->_sequence;
            }
        }

Posted by Krasimir Yosifov (x3mer) on 2007-11-09T14:45:44.000+0000

Sorry for reposting but I made a mistake in my first comment. So the fixed one is here:

I think that the problem is here: Zend_Db_Table_Abstract.php line 665


if ($this->_sequence === true && $this->_db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
    $this->_sequence = "{$this->_name}_{$pkIdentity}_seq";
    if ($this->_schema) {
        $this->_sequence = $this->_schema . '.' . $this->_sequence;
    }
}

I suggest this code


if ($this->_sequence === true && $this->_db instanceof Zend_Db_Adapter_Pdo_Pgsql) {
    $this->sequence = $this->_db->quoteIdentifier("{$this->_name}{$pkIdentity}_seq");
    if ($this->_schema) {
        $this->_sequence = $this->_db->quoteIdentifier($this->_schema) . '.' . $this->_sequence;
    }
}

Posted by Wil Sinclair (wil) on 2008-03-21T17:05:28.000+0000

This issue should have been fixed for the 1.5 release.

Posted by Wil Sinclair (wil) on 2008-03-25T20:43:52.000+0000

Please categorize/fix as needed.

Posted by Wil Sinclair (wil) on 2008-04-18T13:11:47.000+0000

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

Posted by Wil Sinclair (wil) on 2009-01-06T14:25:14.000+0000

This issue has gone unaddressed for too long. I'm re-assigning to Ralph for re-evaluation and categorization.

Posted by Ralph Schindler (ralph) on 2009-01-09T13:58:22.000+0000

Will evaluate within 2 weeks

Posted by Ralph Schindler (ralph) on 2009-05-22T06:52:51.000+0000

Fixed in r15676