Issue Type: Improvement Created: 2007-02-08T18:08:41.000+0000 Last Updated: 2013-03-14T09:32:16.000+0000 Status: Closed Fix version(s): - 1.0.0 (30/Jun/07)
Reporter: Bill Karwin (bkarwin) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db
Related issues: Attachments:
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:
<pre class="highlight"> 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
Posted by Bill Karwin (bkarwin) on 2007-05-02T12:18:56.000+0000
SQLite supports pragma statements to control this behavior. See http://www.sqlite.org/pragma.html
For example, the following statements should help:
<pre class="highlight"> 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.
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.
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.
Posted by Dolf Schimmel (Freeaqingme) (freak) on 2009-09-24T17:44:33.000+0000
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)
Posted by Adam Lundrigan (adamlundrigan) on 2012-02-26T18:50:03.000+0000
Does this issue still exist? If so, could [~coogle]'s suggestion (PRAGMA full_column_names=0) be safely implemented to resolve this issue?
Posted by Frank Brückner (frosch) on 2013-03-14T09:31:44.000+0000
No response for over a year. I close this issue.
Have you found an issue?
See the Overview section for more details.