ZF-50: Name Filter: PDO::CASE_NATURAL lower case column names

Issue Type: Bug Created: 2006-06-20T01:04:02.000+0000 Last Updated: 2008-12-19T13:51:35.000+0000 Status: Closed Fix version(s): - 0.9.1 (23/Mar/07)

Reporter: Zend Framework (zend_framework) Assignee: Bill Karwin (bkarwin) Tags: - Zend_Db

Related issues: - ZF-301



In MySQL, table names are case-sensitive depending on the O/S but column-names are not. Our column names are in Hungarian notation (proper case) without underscores (eg: ZendFramework?) and all of our existing code is written dealing with these 'proper' case as data is populated into arrays as usual as it is pulled. But the following hard-coded line of code in the framework is converting the column names into lower-case, which is a BIG no-no as all of our existing code will FAIL: Class: Zend_Db_Adapter_Pdo_Abstract Line: 85

// force names to lower case $this->_connection->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);

This line of code should be made optional/configurable.

{quote} 05/22/06 07:35:32: Modified by

Yes, definitely very important. Please make this optional. 05/22/06 11:51:08: Modified by jayson

* component changed from Documentation to Zend_Db.

05/22/06 15:01:47: Modified by

This is a dupe of #3 I think ? 05/29/06 21:26:59: Modified by mike

* priority changed from blocker to major.



Posted by Gavin (gavin) on 2006-06-29T13:15:27.000+0000

More info here:

Posted by Gavin (gavin) on 2006-06-30T15:57:53.000+0000

I believe the default should be "PDO::CASE_NATURAL". I think we should leave column names as returned by the database driver, so that developers have access to this information (i.e. the case), if needed. Simple principle of preserving data. If data is not captured and preserved, it is not available. If data is available, it can always be ignored, or normalized (e.g. strtolower).

However, we need to make certain that all existing code expecting lower-cased column names won't break (i.e. strtolower).

Posted by Gavin (gavin) on 2006-07-25T14:21:53.000+0000


We can not use either PDO_CASE_LOWER or PDO_CASE_UPPER and remain "case-preserving". If the ZF does not support preserving the case, then integration with existing database would become much more difficult, and also precludes new projects from using camelCase (e.g. camelCase with Hungarian notation).

Someone needs to review all Zend_Db* code and determine the extent of effort required to preserve case, without forcing all developers using lowercase table and column names to change their existing application code. Note, it is possible for some databases (including MySQL) to allow interaction with the database using lowercase column and table names, while the actual names in the database are mixed case.

Thus, a forward and reverse mapping hash table presents one possible approach, where keys and values are lowercased and natural-cased table/column names. Performance issues must be carefully considered. For example, when and where should names be dynamically normalized (to lowercase)?

Posted by Gavin (gavin) on 2006-08-08T21:55:29.000+0000

Copied from Simon's ZF-301 issue: {quote} Apart from providing a more code-friendly appearance, I feel that the camelcaps transform on field names creates another unnecessary layer of abstraction. If my database has user_list_id and member_status, it is far more readable and understandable for those looking through the code to refer to the field names as-is. Especially as the insert/update and where criteria code still relies on the 'uncamelised' id, this treatment seems a bit inconsistent and redundant, considering the inflector that is needed to re-transform them back to the original field name.

I'm not saying to ditch this feature (as I'm sure some people do quite like this), however wish it to be a configurable option. Ideally you would enable/disable when you instantiate the Db object.

<pre class="highlight">
$params = array ('host'     => '',
                 'username' => 'malory',
                 'password' => '******',
                 'dbname'   => 'camelot',
                 'transform' => Zend_Db::CASE_DEFAULT);

$db = Zend_Db::factory('PDO_MYSQL', $params);

CASE_DEFAULT = Unchanged CASE_MIXED = Uppercase words (Camel style - strip underscores) CASE_LOWER = Lowercase (retain underscores) CASE_UPPER = Uppercase (retain underscores) {quote}

Posted by Gavin (gavin) on 2006-08-16T20:19:06.000+0000

I'm renaming this with a suffix of "Name Filter", since I expect some sort of filter plugin solution will be used to provide a degree of flexibility in how DB adapters address variances between the names of databases, tables, and columns and the names used by developers in their code.

Posted by Gavin (gavin) on 2006-09-05T22:11:08.000+0000

André Hoffmann wrote:

In my opinion it also lacks of prefix support for tables. I myself extended it, but I don't think that I'm the only one using table prefixes. In other words: it would definitely meet the 80-20 rule.

On 9/5/06, Victor Bolshov <> wrote:

I think "getName()" should be implemented in Zend_Db_Table. Recently,
I've had a situation where I needed to know the name of the table.
All I
could do was strtolower(get_class($table)) - which is a poor

Regards, Victor.

Prefixing table names fits in the category of filtering table and column names (the subject of this issue).

Also, if any name filtering is used, that same code should also contain the logic to retrieve the original table name - the equivalent of getName() in the email above.

Posted by Bill Karwin (bkarwin) on 2006-11-13T15:14:32.000+0000

Changing fix version to 0.8.0.

Posted by Gavin (gavin) on 2006-11-13T20:10:47.000+0000

See this more thorough problem description and suggested solution

Posted by Bill Karwin (bkarwin) on 2007-01-05T17:06:26.000+0000

Recategorize as Zend_Db_Table component.

Posted by Bill Karwin (bkarwin) on 2007-03-15T23:38:55.000+0000

As of revision 3995, the use of inflectors has been eliminated from Zend_Db_Table.

Posted by Bill Karwin (bkarwin) on 2007-03-16T16:16:36.000+0000

Reopening issue.

Posted by Bill Karwin (bkarwin) on 2007-03-19T11:51:05.000+0000

Changed case mapping mode to CASE_NATURAL in revision 4111.

Posted by Bill Karwin (bkarwin) on 2007-03-29T13:32:28.000+0000

Resolved in revision 4111, which was included in ZF 0.9.1, so I'm marking this issue fixed.

Posted by Bill Karwin (bkarwin) on 2007-04-04T15:40:38.000+0000

Changing component. This is not specific to Zend_Db_Table, but affects all of Zend_Db for PDO adapters.

Posted by Wil Sinclair (wil) on 2008-12-19T13:51:35.000+0000

Bookkeeping. Closing old issues and assigning them to the person who ultimately resolved the issue.

Have you found an issue?

See the Overview section for more details.


© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.