ZF-7846: Add exception to describeTable() to identify missing metadata

Description

With Zend_Db_Table_Adapter_Oracle (and probably with the other adapters) it is possible for the SQL query used by describeTable() to work successfully, but return no data. For example, if the table does not exist, or the user does not have any privileges on the table then the query returns an empty array and describeTable() completes successfully.

If the database adapter cannot get any metadata successfully, then it is unlikely that the application is going to work correctly, even if all the necessary information is specified in the concrete Zend_Db_Table class, but the error will not be apparent immediately and will be harder to debug as a result.

In my case I was receiving:

`

The error message also includes no indication of which concrete class is causing the error. In my case it was because we had forgotten to grant SELECT privileges to the application user on the table in question.

Given that describeTable() is almost always used in conjunction with Zend_Db_Table, and that the Zend_Db_Table will likely fail if the database adapter can't work out the metadata, even if it isn't going to use it, I think that it would be better if describeTable() treated missing metadata as an exception. E.g. change
public function describeTable($tableName, $schemaName = null)
{
    <snip>
    $stmt = $this->query($sql, $bind);

    /**
     * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
     */
    $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);<pre class="highlight"><code>

to public function describeTable($tableName, $schemaName = null) { $stmt = $this->query($sql, $bind);

    /**
     * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
     */
    $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
    if ($result === array()) {
        $table = $schemaName !== null ? $schemaName . '.' . $tableName : $tableName;
        $message = 'Cannot identify columns or primary key for table ' . $table .
                   '; check that the table exists and that privileges ' .
                   'have been granted to user ' . $this->_config['username'];
        throw new Zend_Db_Adapter_Oracle_Exception($message);
    }```

Comments

This is a similar issue for Zend_Db_Adapter_Pdo_Pgsql. My solution would work for this issue to.

I have thought of one circumstance where describeTable() brings back an empty array, but that the application could work correctly anyway.

If the $_name for the table is actually a synonym, then the query will fail (because the table doesn't exist) but the application would continue to work correctly (assuming that the concrete Zend_Db_Table class has all the necessary metadata in it).

I have thought of two fixes for this:

describeTable() tries to find a public or private synonym with that name and then re-executes the describe table, before raising the error. It would be better to do this only when the query returns no rows, rather than all the time, to avoid an unnecessary database query in the usual use case. I don't know if this error and/or solution would apply to non-Oracle database adapters.

Expect the application developer to override Zend_Db_Table_Abstract::_setupMetadata() where it fails but the application developer knows the application will work anyway. This might be worth mentioning specifically in the user guide.

The synonym approach has the advantage that it saves the developer having to create the metadata manually for those tables. This code works for the synonym method:


        $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
        // Raise an exception if the database adapter cannot find the table
        // but check for a synonym first
        if ($result === array()) {
            $sql = "SELECT TABLE_NAME, TABLE_OWNER
                    FROM ALL_SYNONYMS
                    WHERE SYNONYM_NAME = UPPER(:TBNAME)";
            $bind[':TBNAME'] = $tableName;
            if ($schemaName) {
                $sql .= ' AND OWNER = UPPER(:SCNAME)';
                $bind[':SCNAME'] = $schemaName;
            }
            $stmt = $this->query($sql, $bind);
            foreach ($stmt->fetchAll(Zend_Db::FETCH_NUM) as $synonym) {
                $result = $this->describeTable($synonym[0], $synonym[1]);
                if ($result !== array()) {
                    break;
                }
            }
        }
        if ($result === array()) {
        $table = $schemaName !== null ? $schemaName . '.' . $tableName : $tableName;
            $message = 'Cannot identify columns or primary key for table ' . $table .
                       '; check that the table exists and that privileges ' .
                       'have been granted to user ' . $this->_config['username'];
            throw new Zend_Db_Adapter_Oracle_Exception($message);

        }

Well, I was looking for a way to check if a table exists in a certain database, and it looks like describeTable could do that now but not if this change goes in so I think I'll watch this issue and see what happens. I suppose I could use listTables instead.

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.