Issues

ZF-884: SQLite: associative array result set contains correlation names in keys

Description

SQLite is unlike other PDO drivers in that when the result set is returned from a SELECT statement as an associative array of columns => values, and the query contained correlation names, the correlation names are included in the array keys:


SELECT f.column1 FROM table1 AS f

Returns a result set where the key is "{{f.column1}}" whereas in other RDBMS's the key is "{{column1}}".

ADODB solves this by creating a distinct driver called "sqlitepo" that strips the leading prefix from the array key during the fetch operation. http://xaraya.com/documentation/phpxref/…

We need to do something similar, by extending the following functions in Zend_Db_Adapter_Pdo_Sqlite: - {{fetchAssoc()}} - {{fetchAll()}} when fetch mode is FETCH_ASSOC

Comments

SQLite supports pragma statements to control this behavior. See http://www.sqlite.org/pragma.html

For example, the following statements should help:


PRAGMA full_column_names=0
PRAGMA short_column_names=1

However, there are bug reports that these pragmas don't have the desired effect. And in fact that's what I'm seeing too in the unit tests.

I'm going to implement the pragma statements in the _connect() method of the Pdo_Sqlite adapter class, knowing that at least in the current version of SQLite, it does not work. Perhaps in a future version, it will be fixed.

I'm adding unit tests to tests/Zend/Db/Adapter/Pdo/SqliteTest.php for the cases that fail because they insist on returning result sets with keys like "table"."column".

We can't strip the table portion out of the keys as they do in ADODB, because this is a PDO Adapter. The result set is returned in a PDOStatement, not a Zend_Db_Statement.

Resolved insofar as we can resolve it, in revision 4667.

With ZF-1425, we do have a Zend_Db_Statement_Pdo class. In theory, we could now strip correlation names from the keys in result sets, if the driver's pdoType is 'sqlite'.

After upgrading to PHP 5.2.3 the PRAGMA commands are now working. So I presume that a more recent version of the PDO_SQLite driver is working better with respect to PRAGMA.

This bug still exists in the latest version of Zend Core using the latest version of Zend Framework (HEAD)...

It looks like SQLite has gone back and forth on this issue, where different versions of the library behave differently to these PRAGMAs -- we need to figure out which versions do what and correct it.

http://sqlite.org/cvstrac/search/…

As far as I can tell if you simply remove this line from the Pdo_Sqlite::_connect() method it works, at least for ZC:

$retval = $this->_connection->exec('PRAGMA short_column_names=1');

Based on the bug reports I read from sqlite.org we should be able to simply execute full_column_names=0 and achieve the result we are looking for... but I'm sure there is a BC issue here too.

I removed the fix version as for we obviously won't make fix version 1.0.0 (or is anybody in the house who can actually go back in time and fix the issue? If so, I got a few other ones laying around that I'd prefer having fixed a long time ago :D)

Does this issue still exist? If so, could [~coogle]'s suggestion (PRAGMA full_column_names=0) be safely implemented to resolve this issue?

No response for over a year. I close this issue.