Package home | Report new bug | New search | Development Roadmap Status: Open | Feedback | All | Closed Since Version 1.4.1

Bug #1221 problems with quoted identifiers and Oracle
Submitted: 2004-04-16 14:39 UTC
From: priit at e-positive dot ee Assigned: datenpunk
Status: Wont fix Package: DB_NestedSet
PHP Version: 4.3.4 OS: Win
Roadmaps: (Not assigned)    
Subscription  


 [2004-04-16 14:39 UTC] priit at e-positive dot ee
Description: ------------ I think quotedIdentifiers should be used only in select clauses, because they are (at least) in Oracle case sensitive and cause problems. Usually the params look's like this: var $params = array( 'STRID' => 'id', 'ROOTID'=> 'rootid', 'l' => 'l', 'r' => 'r', 'STREH' => 'norder', 'STRLEVEL' => 'level', 'parent'=>'parent' ); Notice that f.e l & r are in lower, strid in upper case. Table generated in Oracle like this CREATE TABLE tb_nodes ( STRID number NOT NULL, ROOTID number NOT NULL, l number NOT NULL, r number NOT NULL, PARENT number NOT NULL, STREH number default 0, STRLEVEL number NOT NULL, df_name varchar2(255) not null, get's internally converted into upper case, so desc tb_nodes is like: Name Null? Type ----------------------------------------- -------- --------- STRID NOT NULL NUMBER ROOTID NOT NULL NUMBER L NOT NULL NUMBER R NOT NULL NUMBER PARENT NOT NULL NUMBER STREH NUMBER STRLEVEL NOT NULL NUMBER No problem unless using quoted identifiers. Inserting a new node the sql clause is something like this: INSERT INTO tb_nodes ("strna", "STRLEVEL", "PARENT", "STRID", "ROOTID", "l", "r") VALUES ('Database', '1', '0', '2', '2', '1', '2'); And that fails! INSERT INTO tb_nodes ("DF_NAME", "STRLEVEL", "PARENT", "STRID", "ROOTID", "L", "R") VALUES ('Database', '1', '0', '2', '2', '1', '2'); goes thru, also with INSERT INTO tb_nodes (df_name, STRLEVEL, parent, STRID, ROOTID, l, r) VALUES ('Database', '1', '0', '2', '2', '1', '2'); Same in UPDATE, The solution is to modify _values2UpdateQuery and _values2InsertQuery $k = strtoupper( $this->_quoteIdentifier(trim($key)) ); or $k = trim($key);

Comments

 [2004-04-16 17:09 UTC] dk at webcluster dot at
Well - I don't know how to tell Oracle to use the fields in a create query case sensitive but I'm sure there is a way. E.g. quote the fields of the create query - just a blind guess. But - why don't you simply use uppercase column names inside the param array. Then you shouldn't have any problem: ROOTID'=> 'rootid', 'L' => 'l', 'R' => 'r', The class doesn't care how you call the left part of the array. It only cares about the right part which is used to alias the internal used names for the fields to the fields in your table. -- Daniel Khan
 [2004-04-16 18:13 UTC] priit at e-positive dot ee
Sure I can use uppercase in $param, but it's correct to change them in original NestedSet - because quoting is (as I know now:-) ) always case-sensitive. Currently NestedSet will not work with any correct SQL engine supporting quoted identifiers. MySQL does not convert unquoted names and that is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. PostreSQL seems to convert unquoted to lower case, which is also incompatible...
 [2005-10-25 22:14 UTC] datenpunk at php dot net
Well - the whole quoting thing is quite complicated and handled differently by different rdbms. I will close this for now as there haven't been any more complaints about this. I will rethink the whole thing for new major releases. Thanks for your report -- Daniel Khan