Home » Database » DB_NestedSet » Bug #1187

[2004-04-12 15:06 UTC] priit at e-positive dot ee

Description:
------------
var $params = array('STRID' => 'id',
'ROOTID' => 'rootid',
'l' => 'l',
'r' => 'r',
'STREH' => 'norder',
'LEVEL' => 'level',
'parent'=>'parent', // Optional but very useful
'STRNA' => 'name'
);

level is reserved word in Oracle and cannot be used. I can derivate own class and override, BUT, 'level' is hardcoded into
all get... functions to be used as sort order parameter.

[2004-04-12 16:03 UTC] dk at webcluster dot at

Which version of DB_NestedSet are you using?
Do you use DB or MDB and which version do you use?

Level is 'hardcoded' as it is allready the alias for the userland column name.
This means that it wouldn't be a big deal to rename level to something different if I am sure that this isn't allready fixed/fixable by the quoteIdentifier() trick provided by DB.

Thanks for your report!

regards

--
Daniel Khan

[2004-04-12 17:54 UTC] priit at e-positive dot ee

I'm using the latest one and DB, but that doesn't matter.

Of course, it's not big deal to rename, but then I have to do it again and again after any new release.

May I suggest a change?

Well, as the aliases are used in three different places
1) var $param;
2) var $_requiredParams
3) building queries
would'nt it be reasonable to define them as constants
in the following way

define('NESE_ALIAS_LEVEL', 'slevel');

for all $_requiredParams members and then use those
constants in the code. This way they can be overriden
in a sane way? Although I don't expect any other reserved
words in params, but this kind of change won't break any existing mysql code.

[2004-04-12 18:20 UTC] dk at webcluster dot at

> I'm using the latest one and DB, but that doesn't matter.
It _does_ matter as the last 2 releases include some fixes for reserved words. The queries _should_ be properly quoted so that reserved words shouldn't be a problem.
I am quite sure that you have the latest package but _please_ use 'pear info db_nestedset' to verify that you are using 1.3.4. Also use 'pear info DB' and tell me the version. No offense but I allready spent some nights hunting allready fixed bugs reported by people with the latest version.

> Of course, it's not big deal to rename, but then I have to
> do it again and again after any new release.
I ment that I could do this in a new release if I find out that there is no other way to solve your problem - but I'm sure there is another way.

> define('NESE_ALIAS_LEVEL', 'slevel');
No this is exactly what the params mapping does.

'LEVEL' => 'level',
Somehow means define('NESE_ALIAS_LEVEL', 'level');
So adding a new level of abstraction wouldn't solve the real problem.

Please switch the package into debug mode and paste in the query which causes the problem.
Then we will hopefully see what's wrong with the quoting and I will swiftly fix the problem.

greetings

--
Daniel Khan

[2004-04-12 18:48 UTC] priit at e-positive dot ee

okee, dokee, now I have DB 1.6.2 and Nese 1.3.4, or whatever the latest are.

1)
var $params = array(
'STRID' => 'id',
'ROOTID'=> 'rootid',
'l' => 'l',
'r' => 'r',
'STREH' => 'norder',
'STRLEVEL' => 'level',
'parent'=>'parent',
'REL' => 'related',
'STRNA' => 'name'
);

var $_requiredParams = array('id', 'rootid', 'l', 'r', 'norder', 'level');

1084770907.2598::Debug:: DB_NestedSet()
1084770907.4925::Debug:: getAllNodes()
1084770907.5002::Debug:: _testFatalAbort($errobj, $file, $line)

Fatal error: SELECT tb_nodes.STRID id, tb_nodes.ROOTID rootid, tb_nodes.l l, tb_nodes.r r, tb_nodes.STREH norder, tb_nodes.STRLEVEL level, tb_nodes.parent parent, tb_nodes.REL related, NVL(tb_nodes.en_name, tb_nodes.df_name) name FROM tb_nodes ORDER BY tb_nodes.STRLEVEL, tb_nodes.STREH ASC [nativecode=ORA-00923: FROM keyword not found where expected] (-2) in file c:\seedwork\engine\kernel\NestedSet.php at line 1906 in

loaded to SQL+ i have better error message
SQL> SELECT tb_nodes.STRID id, tb_nodes.ROOTID rootid, tb_nodes.l l, tb_nodes.r r,
2 tb_nodes.STREH norder, tb_nodes.STRLEVEL level, tb_nodes.parent parent,
3 tb_nodes.REL related, NVL(tb_nodes.en_name, tb_nodes.df_name) name
4 FROM tb_nodes ORDER BY tb_nodes.STRLEVEL, tb_nodes.STREH ASC;
tb_nodes.STREH norder, tb_nodes.STRLEVEL level, tb_nodes.parent parent,
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected

2) let's change 'level' to 'slevel'

var $params = array(
'STRID' => 'id',
'ROOTID'=> 'rootid',
'l' => 'l',
'r' => 'r',
'STREH' => 'norder',
'STRLEVEL' => 'slevel',
'parent'=>'parent',
'REL' => 'related',
'STRNA' => 'name'
);

var $_requiredParams = array('id', 'rootid', 'l', 'r', 'norder', 'slevel');

1084771059.877::Debug:: DB_NestedSet()
1084771060.3168::Debug:: getAllNodes()
1084771060.44::Debug:: _testFatalAbort($errobj, $file, $line)

Fatal error: SELECT tb_nodes.STRID id, tb_nodes.ROOTID rootid, tb_nodes.l l, tb_nodes.r r, tb_nodes.STREH norder, tb_nodes.STRLEVEL slevel, tb_nodes.parent parent, tb_nodes.REL related, NVL(tb_nodes.en_name, tb_nodes.df_name) name FROM tb_nodes ORDER BY tb_nodes., tb_nodes.STREH ASC [nativecode=ORA-01747: invalid user.table.column, table.column, or column specification] (-1) in file c:\seedwork\engine\kernel\NestedSet.php at line 1906 in

as you notice the error is at ORDER BY tb_nodes.,

[2004-04-12 19:42 UTC] dk at webcluster dot at

Ok - this is quite odd.
Let's look at the query:

Fatal error: SELECT tb_nodes.STRID id, tb_nodes.ROOTID rootid,
tb_nodes.l l, tb_nodes.r r, tb_nodes.STREH norder, tb_nodes.STRLEVEL
level, tb_nodes.parent parent, tb_nodes.REL related,
NVL(tb_nodes.en_name, tb_nodes.df_name) name FROM tb_nodes

I don't know if oracle simply shortens the syntax but it has to read 'tb_nodes.STRID AS id' - in this query the 'AS' is missing.
The second thing:
The column selection is built using:
$this->_getSelectFields($aliasFields)
And _getSelectFields() does:

$tmp_field .= ' AS ' . $this->_quoteIdentifier($val);

So just to be sure please do an
'echo $sql' somewhere arround line 383.
It's in getAllNodes() between

$this->secondarySort);
and
} elseif ($this->_sortMode == NESE_SORT_PREORDER) {

Please send me the output. Please confirm that you haven't changed any methods within the class. I have to ask that dumb questions as I allready had such cases ;)

Your 2nd example with 'STRLEVEL' => 'slevel', won't work because the method looks up for the column name aliases by 'level' and can't find it so the resulting sql is invalid.

Thanks for your support!

--
Daniel Khan

[2004-04-13 06:43 UTC] priit at e-positive dot ee

In major engines AS is mandatory and i cut it off as i do have my own _getSelectFields to be used with my multilanguage tree:

function _getSelectFields($aliasFields) {
$queryFields = array();
$i = 1;
$this->lparam = count($this->params);
foreach ($this->params as $key => $val) {
$i++;
if ( $i <= $this->lparam ) {
$tmp_field = $this->node_table . '.' . $key;
if ($aliasFields) {
$tmp_field .= ' AS ' . $val;
# $tmp_field .= ' ' . $val;
}
$queryFields[] = $tmp_field;
} else {
$def_field = $this->node_table . '.df_name';
$lang_field = $this->node_table . '.' . opt_site_language . '_name';
# $tmp_field = 'COALESCE('.$lang_field.', '.$def_field.')';
$tmp_field = 'NVL('.$lang_field.', '.$def_field.')';
if ($aliasFields) {
$tmp_field .= ' AS ' . $val;
# $tmp_field .= ' ' . $val;
}
$queryFields[] = $tmp_field;
break;
}
}

$fields = implode(', ', $queryFields);
return $fields;
}

When I put AS back I got the same result:

1084774898.876::Debug:: DB_NestedSet()
1084774898.975::Debug:: getAllNodes()

SELECT tb_nodes.STRID AS id, tb_nodes.ROOTID AS rootid, tb_nodes.l AS l, tb_nodes.r AS r, tb_nodes.STREH AS norder, tb_nodes.STRLEVEL AS level, tb_nodes.parent AS parent, tb_nodes.REL AS related, NVL(tb_nodes.en_name, tb_nodes.df_name) AS name FROM tb_nodes ORDER BY tb_nodes.STRLEVEL, tb_nodes.STREH ASC

1084774899.0066::Debug:: _testFatalAbort($errobj, $file, $line)

Fatal error: SELECT tb_nodes.STRID AS id, tb_nodes.ROOTID AS rootid, tb_nodes.l AS l, tb_nodes.r AS r, tb_nodes.STREH AS norder, tb_nodes.STRLEVEL AS level, tb_nodes.parent AS parent, tb_nodes.REL AS related, NVL(tb_nodes.en_name, tb_nodes.df_name) AS name FROM tb_nodes ORDER BY tb_nodes.STRLEVEL, tb_nodes.STREH ASC [nativecode=ORA-00923: FROM keyword not found where expected] (-2) in file c:\seedwork\engine\kernel\NestedSet.php at line 1906 in c:\seedwork\engine\kernel\PEAR.php on line 593

[2004-04-13 20:33 UTC] dk at webcluster dot at

Well - the information about the replaced _getSelectFields() was pretty important :)

Look at the original:
$tmp_field .= ' AS ' . $this->_quoteIdentifier($val);

_quoteIdentifier() will quote your query and you won't have anymore errors with reserved words.

That's what I ment when saying:

> It _does_ matter as the last 2 releases include some
> fixes for reserved words.

Please try to fix your method and tell me if it worked for you.

--
Daniel Khan

[2004-04-15 07:27 UTC] dk at webcluster dot at

No feedback was given yet.
Most likely this is bogus as the current version deals with the problem and the bug reported is caused by a method change done by the user himself.

[2004-04-15 09:53 UTC] priit at e-positive dot ee

case closed, my mistake.

I just wasn't aware I can use this quoting thing in sql :-), never even heard of. Well, i rarely use AS in select anyway, only when using functions....