Home » Database » DB_NestedSet » Bug #1221

[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);

[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