Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive /

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

GROUP Problem


Reply


3 Posts   2215 Views

Avatar
xeraa

Google Summer of Code Hacker, 58 Posts

21 September 2007 at 9:53am

Edited: 21/09/2007 10:38am

After having some trouble (and posting a lengthy text which didn't really aid the question ;-) ) with GROUP (class / table), I wondered if it is very clever to have a table that is a reserved word (in MySQL, Postgres and MS SQL). For every query we use on that, we'll have to create a MySQL version (`Group`) and another version ("Group") - otherwise it will miserably fail :P.
On the other hand it would require quite a lot of rewriting code to change it, so not sure it that would be a better alternative...

To illustrate the problem, even simple queries will always require at least two versions, so instead of

SELECT MAX(ID) FROM mytable


This for MySQL

SELECT MAX(ID) FROM `thetable`


and for PostgreSQL and MS SQL

SELECT MAX(ID) FROM "thetable"

Avatar
Sigurd

Forum Moderator, 628 Posts

21 September 2007 at 12:01pm

Certainly tricky, as while you could change all the references inside SilverStripe easily, it might break people's personal code residing in their mysite folders.

Presumeably you're putting ticks on table names anyway, so its not too hard to put \" on when not using mysql ... ?

Avatar
xeraa

Google Summer of Code Hacker, 58 Posts

22 September 2007 at 4:37am

Edited: 22/09/2007 4:44am

Ok, I guess the issue is resolved :-)

You can use " on MySQL if you enable ANSI quotes via

SET SESSION sql_mode='ANSI_QUOTES'


It needs to be set for every session - to set it permanently you would need full rights on the database which some people probably won't have, so this method should be working for everyone.

I've also made that clear in the documentation and added all reserved words - so we'll be able to avoid them in the future ;-), see [url]http://doc.silverstripe.com/doku.php?id=sql-queries[/url]