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

Description

http://framework.zend.com/developer/ticket/73

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 cs@bitrockers.de

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 rob.nospam@akrabat.com

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

* priority changed from blocker to major.

{quote}

Comments

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

READ http://dev.mysql.com/doc/refman/… BEFORE COMMENTING ON THIS ISSUE.

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)?

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.


$params = array ('host'     => '127.0.0.1',
                 '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}

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.

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 <vbolshov@rbc.ru vbolshov@rbc.ru> 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
decision.

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.

Changing fix version to 0.8.0.

Recategorize as Zend_Db_Table component.

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

Reopening issue.

Changed case mapping mode to CASE_NATURAL in revision 4111.

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

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

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