[#8713] - joomla+postgresql installation: error with uppercase(s) in table names
- Closed
- 8 Jan 2016
- Medium
- Build: master
- # 8713
Good day,
After many successful joomla+postgresql installations, I ran today with a "bug".
When table names have uppercases, in prefix for example, installation script fails to succeed.
It seems the problem could be fixed by double-quoting all table names.
Then instead of
SELECT * FROM #__extensions WHERE extension_id = '1';
SQL should be
SELECT * FROM "#__extensions" WHERE extension_id = '1';
By the way, sequences are also affected.
To fix this, all queries should use $db->quoteName() to write proper SQL.
$query->clear()
->select('*')
->from($db->quoteName('#__extensions'));
$db->setQuery($query);
A workaround could be to force lowercases on prefix.
Joomla 3.4.6 + CentOS 6.7 x68_64 + PSQL 3.9.10 + PHP 5.4.45
| Category | ⇒ | Postgresql |
| Status | New | ⇒ | Confirmed |
yes i confirm too, but considering the amount of work needed to let UPPERCASE table prefix work, i will be in favour to force lower case in the table prefix if postgresql was selected, any comments ?
Due to the low volume of pgsql users and the limited possibility of this issue occurring I would go for the quick option
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8713.
Agree with @brianteeman, so this issue can be closed!
There are not that many users implementing on pgsql, even fewer making the mistake to use uppercase in the prefix. So, just document it. Nothing to be done in the software!
When you want to change something in the software suggest to make sure the ->from method puts the quotes there when they are missing instead of only using lowercase prefix.
Obviously the bad code using ->quoteName or ->qn can be removed from any ->from after that.
Maybe those functions can even be deprecated or made internal. Would consider that a far better OOD implementation, removing yet another rookie mistake!
Disagree it can be closed if it can be fixed.
Care to explain how it's a mistake to use uppercase prefixes? From what I've read, it's acceptable on all database engines to use uppercase characters in the names of identifiers, but in the case of PostgreSQL since it defaults to treating identifiers as all lowercase values it requires quoting to use uppercase characters.
The fact you can do it, doesn't make it the right decision. Regardless of how you think about case sensitivity it is probably reason no 1 for problems. Sticking with lowercase saves you from a lot of problems in a case sensitive environment. Expect most implementations of Joomla on pgsql to be on the unix platform by people with above average IT knowledge. They probably know to avoid these situations. Probably the reason this reported only now.
Agree again with @brianteeman, it shouldn't be closed if you want to do the right thing. Quoting table names and attribute names should be enforced and something done by the appropriate driver. And not be left to an extension developer as it is done currently.
Care to explain the poor current implementation?
If I wrote the current implementation I might be able to explain it; alas, I'm just someone using it with all its nuisances 
I agree folks SHOULD be using lowercase identifiers (table and column names), but it's not a strict requirement in any database engine so I'd disagree with Joomla making it one in its DBAL. Could it be handled better? Absolutely. But frankly, in its current form I wouldn't want to trust the API to do any form of query manipulation beyond assembling the data it's given into an executable SQL query string. Even something like Doctrine doesn't automatically quote everything, but it does have ways to enforce quoting in different scenarios and I think handles some quoting of reserved platform keywords no matter what.
wilsonge
- | Status | Confirmed | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2016-01-08 14:35:34 |
| Closed_By | ⇒ | wilsonge |
| Labels |
Added:
?
|
||
I ca confirm that the current staging ill not install if a database prefix is selected that is uppercase
This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/8713.