Zend Framework

SQLite: associative array result set contains correlation names in keys

Details

  • Type: Improvement Improvement
  • Status: Reopened Reopened
  • Priority: N/A N/A
  • Resolution: Unresolved
  • Affects Version/s: 0.7.0
  • Fix Version/s: 1.0.0
  • Component/s: Zend_Db
  • Labels:
    None

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://www.xaraya.com/documentation/phpxref/nav.html?xaradodb/drivers/adodb-sqlitepo.inc.php.source.html

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

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Bill Karwin added a comment -

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.

Show
Bill Karwin added a comment - 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.
Hide
Permalink
Bill Karwin added a comment -

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

Show
Bill Karwin added a comment - Resolved insofar as we can resolve it, in revision 4667.
Hide
Permalink
Bill Karwin added a comment -

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

Show
Bill Karwin added a comment - 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'.
Hide
Permalink
Bill Karwin added a comment -

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.

Show
Bill Karwin added a comment - 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.
Hide
Permalink
John Coggeshall added a comment -

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://www.sqlite.org/cvstrac/search?s=short_column_names&t=1&f=1

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.

Show
John Coggeshall added a comment - 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://www.sqlite.org/cvstrac/search?s=short_column_names&t=1&f=1 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.
Hide
Permalink
Dolf Schimmel (Freeaqingme) added a comment -

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 )

Show
Dolf Schimmel (Freeaqingme) added a comment - 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 )
Hide
Permalink
Adam Lundrigan added a comment -

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

Show
Adam Lundrigan added a comment - Does this issue still exist? If so, could John Coggeshall's suggestion (PRAGMA full_column_names=0) be safely implemented to resolve this issue?

People

Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.