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}}".

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.

Posted by Bill Karwin (bkarwin) on 2007-05-02T12:20:40.000+0000

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

Posted by Bill Karwin (bkarwin) on 2007-05-25T11:37:20.000+0000

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'.

Posted by Bill Karwin (bkarwin) on 2007-06-28T21:16:15.000+0000

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.

Posted by John Coggeshall (coogle) on 2009-06-22T12:19:59.000+0000

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.

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)