Issues

ZF-1816: PostgreSQL: Incorrect sequence name quoting

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

Assigning to [~bkarwin] to initiate issue review.

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;
            }
        }

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;
    }
}

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

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

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

Will evaluate within 2 weeks

Fixed in r15676