nameQuote and multipart names

[Elin Waring]

Currently nameQuote doesn't quote  names with dot notation (like 't.field').

https://github.com/joomla/joomla-platform/blob/staging/libraries/joomla/database/database.php#L1249

I'm not sure why that is but is there a reason we can't or shouldn't explode and generate 't'.'field'?  

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

Currently to achieve that I have to nameQuote both parts and concatenate them, which just seems like a lot of repetitive code if you are doing a lot. 

I know in general in MySQL you don't ever need to quote if you have a dot separated name, but is that true in all databases? I know I don't know enough about other databases, but based on some searching this morning I believe that, for example, in postgres you would usually want to quote everything because of case sensitivity issues.

Elin

[84.le0n]

2011/12/17 Elin Waring <elin....@gmail.com>:> Currently nameQuote

In PostgreSQL you've to quote an identifier if you want to handle it
in a case sensitive manner, otherwise it will be handled as lowercase.
In case you create a table with quote (so it's case sensitive) that
contains at least an uppercase character and then call this table
without quote, PostgreSQL will return an error of table not found.

Your proposal is really useful ;)
It can be done with explode and implode, adding quote char before and
after passed string.
I'll work to add this feature on my driver.

End. Gabriele Pongelli

[84.le0n]

Overridden nameQuote in PostgreSQL driver to allow calls like
$db->nameQuote('table.column');
to be translated to "table"."column"

Eng. Gabriele Pongelli.

[Andrew Eddie]

Done

https://github.com/joomla/joomla-platform/pull/658

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla 1.7 developers

[Elin Waring]

Thanks Andrew, that's fantastic :).

Elin

[Andrew Eddie]

This has been pulled. To summarize, you can now just use the one
quoteName call for a dotted string, e.g.:

$db->quoteName('a.foo')

will return `a`.`foo` (quotes adjusted for the db engine, of course).

There is probably a nice mop up job to do over the code base if people
are bored with their Christmas presents :)

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla 1.7 developers

[84.le0n]

@Andrew
Today I was modifying some queries inside CMS to let them work with
PostgreSQL and I've found some SELECT query with many arguments so
I've tried to add all that argument in an array and do only one
quoteName call (instead of one quoteName call per argument), but I've
found a strange (for me) behaviour.

These are my tests for your version of quoteName:

1) quoteName('column') will return "column" (in PostgreSQL, this is
with backtick in MySQL) --> that's CORRECT

2) quoteName('table.column') will return "table"."column" --> that's CORRECT

3) quoteName( array('table', 'column') ) will return "table"."column"
--> that's WRONG for me, same result as 2) , I think it's better that
returns an array ( 0 => "table", 1 => "column" ) as separate name

4) quoteName( array('table.col1', 'col2') ) will return
"table.col1"."col2" --> that's WRONG for me, first argument is not
quoted correctly, I think it's better that returns an array (
"table1"."col" , "col2 ), so it behave like 3) plus dot complexity.

When I've tried to quoteName with array I thought that array variable
were used to quote a list of name and not to concatenate them as they
were separated by a dot.
Have you designed this change to behave like this or it's a side effect ?
I think it's better improve that behaviour, don't you ?

Thank you,
Eng Gabriele Pongelli.

2011/12/22 Andrew Eddie <mamb...@gmail.com>:

[Andrew Eddie]

Hi Gabriele

Yes, that's a reasonable change. I'm happy for quoteName to take an
array of names to quote rather than an array of the parts of a single
name. Go ahead and make a pull request for that.

I've also been wondering whether quoteName should take an alias
argument, for example:

quoteName('a.foo', 'bar') could return " `a`.`foo` AS `bar` "

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla 1.7 developers

[Mark Dexter]

I have been thinking that it would be handy if all of the methods that
normally take a table (from(), innerJoin, insert(), join(),
leftJoin(), outerJoin(), rightJoin(), update()) could take an alias
as an optional second argument. Don't know if that makes sense to
others or not. Mark

[Andrew Eddie]

The joins are difficult because of the "ON" part of the clause. If
you add the alias to quoteName, FROM is automatically fixed, e.g.:

$query->from($query->qn('foo', 'a')

should translate to:

" FROM `foo` AS a "

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla 1.7 developers

[Mark Dexter]

How about making the ON an optional arg for the joins? So,

$query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out');

becomes

$query->join('LEFT', '#__users', 'uc', 'uc.id=a.checked_out');

or

$query->join('LEFT', '#__users', 'uc', $db->qn('uc.id') . '=' .
$db->qn('a.checked_out'));

Just seems like the more we can parse the query pieces, the easier it
is for the db methods to process the pieces as needed. But maybe it is
too complicated and not needed.

Mark

[Andrew Eddie]

I really don't know how I feel about that one because you should
technically quote the equality fields as well. I think joins are
messy no matter how you slice it.

Regards,
Andrew Eddie
http://learn.theartofjoomla.com - training videos for Joomla 1.7 developers

[84.le0n]

This is my changes
https://github.com/gpongelli/joomla-platform/blob/postgresql/libraries/joomla/database/database.php#L1297

it works with strings and arrays, with and without dot notation.

For "AS" changes I'll do it.

ps
Merry Christmas for those who believes.

[84.le0n]

Added AS part to quoteName, it can be string for string quoting and
array for array quoting.
It can be null too, to avoid "AS" query part to relative $name.
Added tests and pushed in my pull request, linked in last email.

Happy Holiday ;-)

[84.le0n]

Created a pull request containing only these changes
https://github.com/joomla/joomla-platform/pull/679

Bye!