This is a recurrent question/problem in the mailing list.
People went starting to work on identity will probably name their class Group. People that are probably testing TG will want to make a shopping cart or something similar and will probably come up with the Order class
We all know the problem is with SQLs "order by" and "group by" keywords but most people don't realise this until it has taken some time to find the answer.
So far I think the best way to go around this is with documentation, but I'm not sure where to put it so people can access it.
I think a couple of sidenotes in the docs could help.
So far I have think of the following places:
- In the "write your own identity" tutorial - in the TG main documentation, went first declaring SQLObject classes - Same as above in TG book (I'll make sure this gets there) - in a FAQ (if there is any) - Somewhere in trac/docudo - in this same post so those who search before ask can get this.
I think the text should be something like "Remenber even though SQLObject abstracts us from the DB we still can't use any if it's reserved keywords, the most common mistakes are Order and Group which colide with order by and group by. This Happens because of the way the table names are guess by SQLObject, but you can go around that like this http://www.sqlobject.org/SQLObject.html#changing-the-naming-style"
--- end of post --- adding this so a search on the errors will show up this post.
pysqlite2.dbapi2.OperationalError: near "group": syntax error pysqlite2.dbapi2.OperationalError: near "order": syntax error
I would say this should be appropriate in the TG docs re: using SQLObject and a reminder in "writing your own identity model" as part of the warning against doing the same thing with "User" as that is often reserved by databases as well.
Maybe there is a way to add this kind of checking to SQLObject somehow, where it throws an error or something when it detects you using a keyword for a table name. I would assume that this is not terribly difficult considering SQLObject already has a list of all the SQL keywords, but I haven't really looked at the code to check for certain.
Excellent suggestion. Thanks for pointing this out to me. Per the docs using sqlmeta allows "specifying metadata in a clearer way, without polluting the class namespace with more attributes".
So the example should read something like this:
class User(SQLObject): class sqlmeta: table = 'app_user'
As the manual states "if you're not using the sqlmeta class you're doing things in a deprecated way." Next time I do more research before I post.
Amazing. I've done backends for SQLServer, Access, MySQL, PG, SQLite, Firebird and Oracle and it was dead simple in all of them to escape table and column identifiers. SQLObject should be fixed on this point.
Robert Brewer System Architect Amor Ministries fuman...@amor.org
> This is a recurrent question/problem in the mailing list.
> People went starting to work on identity will probably name their class > Group. > People that are probably testing TG will want to make a shopping cart or > something similar and will probably come up with the Order class
> We all know the problem is with SQLs "order by" and "group by" keywords > but most people don't realise this until it has taken some time to find the > answer.
> So far I think the best way to go around this is with documentation, but > I'm not sure where to put it so people can access it.
> I think a couple of sidenotes in the docs could help.
> So far I have think of the following places:
> - In the "write your own identity" tutorial > - in the TG main documentation, went first declaring SQLObject classes > - Same as above in TG book (I'll make sure this gets there) > - in a FAQ (if there is any) > - Somewhere in trac/docudo > - in this same post so those who search before ask can get this.
> I think the text should be something like "Remenber even though SQLObject > abstracts us from the DB we still can't use any if it's reserved keywords, > the most common mistakes are Order and Group which colide with order by and > group by. This Happens because of the way the table names are guess by > SQLObject, but you can go around that like this > http://www.sqlobject.org/SQLObject.html#changing-the-naming-style"
> On 4/19/06, Jorge Vargas <jorge.var...@gmail.com> wrote:
> > This is a recurrent question/problem in the mailing list.
> > People went starting to work on identity will probably name their class > > Group. > > People that are probably testing TG will want to make a shopping cart or > > something similar and will probably come up with the Order class
> > We all know the problem is with SQLs "order by" and "group by" keywords > > but most people don't realise this until it has taken some time to find the > > answer.
> > So far I think the best way to go around this is with documentation, but > > I'm not sure where to put it so people can access it.
> > I think a couple of sidenotes in the docs could help.
> > So far I have think of the following places:
> > - In the "write your own identity" tutorial > > - in the TG main documentation, went first declaring SQLObject classes > > - Same as above in TG book (I'll make sure this gets there) > > - in a FAQ (if there is any) > > - Somewhere in trac/docudo > > - in this same post so those who search before ask can get this.
> > I think the text should be something like "Remenber even though > > SQLObject abstracts us from the DB we still can't use any if it's reserved > > keywords, the most common mistakes are Order and Group which colide with > > order by and group by. This Happens because of the way the table names are > > guess by SQLObject, but you can go around that like this > > http://www.sqlobject.org/SQLObject.html#changing-the-naming-style"
Jorge Vargas wrote: > This is a recurrent question/problem in the mailing list.
> People went starting to work on identity will probably name their class > Group. > People that are probably testing TG will want to make a shopping cart or > something similar and will probably come up with the Order class
This is not user error, this is a bug in SQLObject. Field names should be properly quoted (backticks in MySQL). I'm not aware of any SQL database which imposes restrictions on table and column names.
Table and column names should always be quoted in SQL on general principal.
On 4/20/06, Robin Haswell <r...@digital-crocus.com> wrote:
> Jorge Vargas wrote: > > This is a recurrent question/problem in the mailing list.
> > People went starting to work on identity will probably name their class > > Group. > > People that are probably testing TG will want to make a shopping cart or > > something similar and will probably come up with the Order class
> This is not user error, this is a bug in SQLObject. Field names should be > properly quoted (backticks > in MySQL).
I will not say it's a bug, it's more a design thing, if they didn't do it for any reason (performance may be one) we need to investigate. For now it's a user error, if it's get change in SQLObject we'll come back here and post this is not an issue anymore :)
I'm not aware of any SQL database which imposes restrictions on table and
> column names.
The fact they allow non quoted names means a restriction for keywords.
Table and column names should always be quoted in SQL on general principal.
Not really, it's a "good practice" but you know how those are
I will file a bug.
I was going to do that but the tracker was down so I posted to the mailing list.
Jorge Vargas wrote: > The fact they allow non quoted names means a restriction for keywords.
You say restriction, I say syntax error :-)
Fair enough though. All I'm saying is there shouldn't be a reason why this isn't possible, all that's required is a cleanup to SQLObject's query builder. If SO is written well this could be a two-line patch :-)
Anyway I'm still SQLAlchemy++, and I've never even used it. I'm wary of integrating projects at the core which have flatlined at 0.7.
Em Quinta 20 Abril 2006 19:47, Robin Haswell escreveu:
> builder. If SO is written well this could be a two-line patch :-)
It would be nice to see a 2 line patch to cover Oracle, PostgreSQL, Firebird, SQLite, MySQL, etc. on this subject. Specially because if it included tests (those can have more than 2 lines ;-)) they would probably be accepted.
Ah! Of course, one has to remember about case-folding differences and that it is relevant specially for already existing database ("fromDatabase = True").
Jorge Godoy wrote: > Em Quinta 20 Abril 2006 19:47, Robin Haswell escreveu:
>> builder. If SO is written well this could be a two-line patch :-)
> It would be nice to see a 2 line patch to cover Oracle, PostgreSQL, Firebird, > SQLite, MySQL, etc. on this subject. Specially because if it included tests > (those can have more than 2 lines ;-)) they would probably be accepted.
> Ah! Of course, one has to remember about case-folding differences and that it > is relevant specially for already existing database ("fromDatabase = True").
I'm not sure if case folding applies to this. All that should be necessary is adding backticks (or equivalents - maybe) around table/column names.
Em Quinta 20 Abril 2006 20:01, Robin Haswell escreveu:
> I'm not sure if case folding applies to this. All that should be necessary > is adding backticks (or equivalents - maybe) around table/column names.
In PostgreSQL if you use quotes you have to use the exact same case that was used to create the table. If you don't, names will be converted to lower case. In Oracle if you don't use quotes names will be converted to upper case.
If you create a table like this "Test" then you can't access it like "test" or "TEST" in PostgreSQL, for example. On the other hand, if you create it like Test, then it will be converted to test and you can access it as test, Test, TEST, teST, TeST, etc. All without quotes, of course. If you try accessing it as "Test" (with quotes), it will fail.
Now, imagine it in an database that is used by several applications in, lets say, 3 different languages. Several different teams coding, etc. If you enforce the use of quotes in one of these apps, *all* of them will have to use quotes to maintain compatibility and there will have to be some internal documentation on how to name tables and columns. If you don't use quotes, then it doesn't matter if team A writes "select ThisColumn from TableA" and team B writes "select thiscolumn from tablea" since both will succeed.
There is much more to consider than just the presence or absence of quotes, backticks, etc. here. It is easy to think in a new application used by only one team but it is a very narrow minded approach to a database that can be shared by several applications and several teams in different programming languages.
> Em Quinta 20 Abril 2006 20:01, Robin Haswell escreveu:
> > I'm not sure if case folding applies to this. All that should be necessary > > is adding backticks (or equivalents - maybe) around table/column names.
> In PostgreSQL if you use quotes you have to use the exact same case that was > used to create the table. If you don't, names will be converted to lower > case. In Oracle if you don't use quotes names will be converted to upper > case.
> If you create a table like this "Test" then you can't access it like "test" or > "TEST" in PostgreSQL, for example. On the other hand, if you create it like > Test, then it will be converted to test and you can access it as test, Test, > TEST, teST, TeST, etc. All without quotes, of course. If you try accessing > it as "Test" (with quotes), it will fail.
> Now, imagine it in an database that is used by several applications in, lets > say, 3 different languages. Several different teams coding, etc. If you > enforce the use of quotes in one of these apps, *all* of them will have to > use quotes to maintain compatibility and there will have to be some internal > documentation on how to name tables and columns. If you don't use quotes, > then it doesn't matter if team A writes "select ThisColumn from TableA" and > team B writes "select thiscolumn from tablea" since both will succeed.
> There is much more to consider than just the presence or absence of quotes, > backticks, etc. here. It is easy to think in a new application used by only > one team but it is a very narrow minded approach to a database that can be > shared by several applications and several teams in different programming > languages.
> In PostgreSQL if you use quotes you have to use > the exact same case that was used to create the > table. If you don't, names will be converted to lower > case. In Oracle if you don't use quotes names will > be converted to upper case.
You could offer an "application DB" mode that quotes everything and an "integration DB" mode that quotes nothing (and lowercases for PG). Then only those with integrated DB's would have to "pay the price" of avoiding reserved words.
<clickety-click> I just added that option to Dejavu's Postgres adapter, defaulting to "application DB" mode. Written, tested, and debugged in 15 minutes.
Robert Brewer System Architect Amor Ministries fuman...@amor.org
> You could offer an "application DB" mode that quotes everything and an > "integration DB" mode that quotes nothing (and lowercases for PG). Then > only those with integrated DB's would have to "pay the price" of > avoiding reserved words.
> <clickety-click> I just added that option to Dejavu's Postgres adapter, > defaulting to "application DB" mode. Written, tested, and debugged in > 15 minutes.
And then you'd have to test two different things all the time, document and explain two different approaches, maintain two different code paths, do it for all supported databases (PostgreSQL, Firebird, MySQL, Oracle, SQLite, ...), etc.
I don't see this with good eyes, but nothing is preventing you people from writing a patch for that -- with tests -- and submitting it to the SQLObject trac and mailing list. Remember to support everything that is in their code right now...
It looks like, from the core, there's "only" support for:
- PostgreSQL - SQLite - MAXDB - MySQL - Sybase - Firebird - MS SQL Server
Jorge Godoy wrote: > I don't see this with good eyes, but nothing is preventing you people from > writing a patch for that -- with tests -- and submitting it to the SQLObject > trac and mailing list. Remember to support everything that is in their code > right now...
I think TurboGears, as a RAD application for making your life easier, would benefit from not slapping people with tracebacks when they try to use obvious names in their models.
Em Sexta 21 Abril 2006 17:39, Robin Haswell escreveu:
> I think TurboGears, as a RAD application for making your life easier, would > benefit from not slapping people with tracebacks when they try to use > obvious names in their models.
So obvious that they were standardized as keywords for one of the involved layers. Unfortunately? I don't know...
But, still, nothing will prevent people from using these obvious names for their classes if they use different table names, what solves the problem in a different way (and is transparent everywhere except for a line or two in the model).
> Does SA have these problems?
You'd have to check. I've never used SQL Alchemy here.
In svn version of the identity documentation there is a note that warns users to not use 'Group' as a class name unless you override the table name by using sqlmeta. I didn't not mention 'Order' however it could easily be added. Thanks, Lateef On 4/20/06, Jorge Vargas <jorge.var...@gmail.com> wrote:
> This is a recurrent question/problem in the mailing list.
> People went starting to work on identity will probably name their class > Group. > People that are probably testing TG will want to make a shopping cart or > something similar and will probably come up with the Order class
> We all know the problem is with SQLs "order by" and "group by" keywords > but most people don't realise this until it has taken some time to find the > answer.
> So far I think the best way to go around this is with documentation, but > I'm not sure where to put it so people can access it.
> I think a couple of sidenotes in the docs could help.
> So far I have think of the following places:
> - In the "write your own identity" tutorial > - in the TG main documentation, went first declaring SQLObject classes > - Same as above in TG book (I'll make sure this gets there) > - in a FAQ (if there is any) > - Somewhere in trac/docudo > - in this same post so those who search before ask can get this.
> I think the text should be something like "Remenber even though SQLObject > abstracts us from the DB we still can't use any if it's reserved keywords, > the most common mistakes are Order and Group which colide with order by and > group by. This Happens because of the way the table names are guess by > SQLObject, but you can go around that like this > http://www.sqlobject.org/SQLObject.html#changing-the-naming-style"
> --- end of post --- > adding this so a search on the errors will show up this post.
> pysqlite2.dbapi2.OperationalError: near "group": syntax error > pysqlite2.dbapi2.OperationalError: near "order": syntax error
Em Terça 25 Abril 2006 14:00, lateef jackson escreveu:
> In svn version of the identity documentation there is a note that warns > users to not use 'Group' as a class name unless you override the table name > by using sqlmeta. I didn't not mention 'Order' however it could easily be > added.
Yep. I added this note there and closed a bug with it. There's no "order" table in Identity AFAIR and we can't document all reserved keywords for all RDBMS servers (they differ, unfortunately).
> Em Terça 25 Abril 2006 14:00, lateef jackson escreveu: > > In svn version of the identity documentation there is a note that warns > > users to not use 'Group' as a class name unless you override the table > name > > by using sqlmeta. I didn't not mention 'Order' however it could easily > be > > added.
> Yep. I added this note there and closed a bug with it. There's no > "order" > table in Identity AFAIR and we can't document all reserved keywords for > all > RDBMS servers (they differ, unfortunately).
Yea but we can document the issue to the most, someone posted it on trac, so I'm doing the circular reference :)
And I think we can document it, with links like it was done with mysql.
> Em Quinta 20 Abril 2006 20:01, Robin Haswell escreveu:
> > I'm not sure if case folding applies to this. All that should be > necessary > > is adding backticks (or equivalents - maybe) around table/column names.
> In PostgreSQL if you use quotes you have to use the exact same case that > was > used to create the table. If you don't, names will be converted to lower > case. In Oracle if you don't use quotes names will be converted to upper > case.
> If you create a table like this "Test" then you can't access it like > "test" or > "TEST" in PostgreSQL, for example. On the other hand, if you create it > like > Test, then it will be converted to test and you can access it as test, > Test, > TEST, teST, TeST, etc. All without quotes, of course. If you try > accessing > it as "Test" (with quotes), it will fail.
> Now, imagine it in an database that is used by several applications in, > lets > say, 3 different languages. Several different teams coding, etc. If you > enforce the use of quotes in one of these apps, *all* of them will have to > use quotes to maintain compatibility and there will have to be some > internal > documentation on how to name tables and columns. If you don't use quotes, > then it doesn't matter if team A writes "select ThisColumn from TableA" > and > team B writes "select thiscolumn from tablea" since both will succeed.
> There is much more to consider than just the presence or absence of > quotes, > backticks, etc. here. It is easy to think in a new application used by > only > one team but it is a very narrow minded approach to a database that can be > shared by several applications and several teams in different programming > languages.
"this" what? I was explaining about case folding. For reserved keywords one should check the manual of the RDBMS server he/she is using.
> I think it will be a good idea to have an explanation if why this is need > to be taken into account.
If you're going to use quotes then this *has* to be taken into account. If you're not using quotes, then all RDBMS servers should "do the right thing"(tm).
yes, what i was refering to is explain to some that is reading that wiki page why this is a "problem" it's not obvious that you couldn't use Group as a SQLObject, and why this can't be fix (at least not in a simple way).
> "this" what? I was explaining about case folding. For reserved keywords > one > should check the manual of the RDBMS server he/she is using.
> > I think it will be a good idea to have an explanation if why this is > need > > to be taken into account.
> If you're going to use quotes then this *has* to be taken into > account. If > you're not using quotes, then all RDBMS servers should "do the right > thing"(tm).
