[Docs] do NOT use Group or Order as SQLObject classes

[Jorge Vargas]

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

[ajones]

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.

[Nicky Ayoub]

Here is another reference http://www.sqlobject.org/SQLObject.html#irregular-naming that allows you to override the table name to
use.

IE.

class User(SQLObject):
    _table = 'app_user'

class Group(SQLObject):
    _table = 'app_group'

class Order(SQLObject):
    table = 'app_order'

--
--
Nicky Ayoub
G-Mail Account

[Jorge Godoy]

Em Quinta 20 Abril 2006 00:33, Nicky Ayoub escreveu:
> Here is another reference
> http://www.sqlobject.org/SQLObject.html#irregular-naming that allows you to
> override the table name to
> use.
>
> IE.
>
> class User(SQLObject):
> _table = 'app_user'

Please, don't use this obsolete syntax. Use the sqlmeta class for that. It's
in the same page above: http://www.sqlobject.org/SQLObject.html#class-sqlmeta

--
Jorge Godoy <jgo...@gmail.com>

[Nicky Ayoub]

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.

Nick

[fumanchu]

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
fuma...@amor.org

[Jorge Vargas]

On 4/19/06, Jorge Vargas <jorge....@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"

from http://tinyurl.com/o9yam Order is a reserved keyword in SQLObject.

[Jorge Vargas]

sry for double I made a mistake

On 4/20/06, Jorge Vargas <jorge....@gmail.com> wrote:

On 4/19/06, Jorge Vargas <jorge....@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"

from http://tinyurl.com/o9yam Option is a reserved keyword in SQLObject.

[Robin Haswell]

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.

I will file a bug.

-Rob

[Jorge Vargas]

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.

-Rob

[Robin Haswell]

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.

-Rob

[Jorge Godoy]

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 <jgo...@gmail.com>

[Robin Haswell]

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.

-Rob

[Jorge Godoy]

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.

--
Jorge Godoy <jgo...@gmail.com>

[Roger Demetrescu]

Jorge is right... I have seen (and suffered the consequences) it in
the past, using Oracle.

[]s
Roger

[fumanchu]

> 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.

[Jorge Godoy]

Em Sexta 21 Abril 2006 14:42, fumanchu escreveu:
>
> 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

Be seeing you,
--
Jorge Godoy <jgo...@gmail.com>

[Robin Haswell]

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.

Does SA have these problems?

-Rob

[Jorge Godoy]

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.

--
Jorge Godoy <jgo...@gmail.com>

[lateef jackson]

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....@gmail.com> wrote:

[Jorge Godoy]

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).

--
Jorge Godoy <jgo...@gmail.com>

[Jorge Vargas]

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.

http://trac.turbogears.org/turbogears/wiki/SQLObjectReservedWords

--
Jorge Godoy      <jgo...@gmail.com >

[Jorge Vargas]

Jorge could you add this to http://trac.turbogears.org/turbogears/wiki/SQLObjectReservedWords

I think it will be a good idea to have an explanation if why this is need to be taken into account.

On 4/20/06, Jorge Godoy < jgo...@gmail.com> wrote:

[Jorge Godoy]

Em Terça 25 Abril 2006 23:59, Jorge Vargas escreveu:
> Jorge could you add this to
> http://trac.turbogears.org/turbogears/wiki/SQLObjectReservedWords

"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).

This is what I was explaining on this message.

--
Jorge Godoy <jgo...@gmail.com>

[Jorge Vargas]

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).

On 4/25/06, Jorge Godoy <jgo...@gmail.com> wrote:

[Robin Haswell]

Maybe SO should throw an error/warning when people use these names?

-Rob

Jorge Vargas wrote:
> On 4/25/06, *Jorge Godoy* <jgo...@gmail.com <mailto:jgo...@gmail.com>>

> Jorge Godoy <jgo...@gmail.com <mailto:jgo...@gmail.com> >
>
>
>
> >

[Jorge Godoy]

Em Quarta 26 Abril 2006 05:55, Robin Haswell escreveu:
> Maybe SO should throw an error/warning when people use these names?

What names? Names for MySQL are not the same names as for PostgreSQL and
Oracle and Firebird and ...

If each driver had a list of reserved keywords, then it would be possible.
The problem is the performance impact since you should check for these on
table creation and every select (I believe that with sqlbuilder I can select
into a temporary table...).

Saying that, why not blaming it on the connection driver itself? :-) After
all, SQL Object uses them and they did nothing to prevent that as well and
they are the most dedicated part of the setup and more featureful than SQL
Object for one specific database server.

--
Jorge Godoy <jgo...@gmail.com>

[Jorge Vargas]

On 4/26/06, Jorge Godoy <jgo...@gmail.com> wrote:

Em Quarta 26 Abril 2006 05:55, Robin Haswell escreveu:
> Maybe SO should throw an error/warning when people use these names?

Robin As the other Jorge pointed out this is hard  to accomplish since each db vendor has it's own set of keywords

What names?  Names for MySQL are not the same names as for PostgreSQL and

Oracle and Firebird and ...If each driver had a list of reserved keywords, then it would be possible.

that's not that hard to ask, but it should be add to http://www.python.org/dev/peps/pep-0249/ or maybe make a 3.0 version. And I don't think all the effort will go on just to add those keywords.

The problem is the performance impact since you should check for these on
table creation and every select (I believe that with sqlbuilder I can select
into a temporary table...).

why? you will check on create only

Saying that, why not blaming it on the connection driver itself? :-)  After
all, SQL Object uses them and they did nothing to prevent that as well and
they are the most dedicated part of the setup and more featureful than SQL
Object for one specific database server.

yes that's why I think the best way to go at this is DOCS

--
Jorge Godoy      <jgo...@gmail.com>

[Ben Sizer]

Jorge Godoy wrote:
> Em Quarta 26 Abril 2006 05:55, Robin Haswell escreveu:
> > Maybe SO should throw an error/warning when people use these names?
>
> What names? Names for MySQL are not the same names as for PostgreSQL and
> Oracle and Firebird and ...
>
> If each driver had a list of reserved keywords, then it would be possible.
> The problem is the performance impact since you should check for these on
> table creation and every select (I believe that with sqlbuilder I can select
> into a temporary table...).

I'd think it would be very easy for SQLObject to maintain such a list
for each driver. Such lists are freely available, for a start.

As for performance, if you're worried about the efficiency of doing a
simple "if kw in kwDict", you shouldn't be using an object-relational
mapper in the first place ;)

--
Ben Sizer

[Jorge Godoy]

Em Quarta 26 Abril 2006 11:11, Jorge Vargas escreveu:
>
> why? you will check on create only

You can create temporary tables with select. What if one of those had a
reserved keyword as its name?

One example of that for PostgreSQL
(http://www.commandprompt.com/ppbook/r28390):

booktown=# SELECT * INTO TEMP TABLE old_emp
booktown-# FROM employees
booktown-# WHERE id < 105;
SELECT

--
Jorge Godoy <jgo...@gmail.com>

[Jorge Godoy]

Em Quarta 26 Abril 2006 11:33, Ben Sizer escreveu:

> As for performance, if you're worried about the efficiency of doing a
> simple "if kw in kwDict", you shouldn't be using an object-relational
> mapper in the first place ;)

Just because it doesn't fly it can't run? ;-) I don't believe that we have
to make things worse to protect the developer from shooting himself, after
all, as you said, such listings are freely available.

--
Jorge Godoy <jgo...@gmail.com>

[Max Ischenko]

> Maybe SO should throw an error/warning when people use these names?

SQLObject most probably should have hid different backend quoting rules
from the library user. But given its current development speed (read:
none) it's unlikely to happen anytime soon.

I doubt adding "names blacklist" would be accepted either.

I think the best approach is just to clearly document this SO'
deficiency and move on.

Regards,
Max.

[Jorge Vargas]

On 4/26/06, Ben Sizer <kyl...@gmail.com> wrote:

Jorge Godoy wrote:
> Em Quarta 26 Abril 2006 05:55, Robin Haswell escreveu:
> > Maybe SO should throw an error/warning when people use these names?
>
> What names?  Names for MySQL are not the same names as for PostgreSQL and
> Oracle and Firebird and ...
>
> If each driver had a list of reserved keywords, then it would be possible.
> The problem is the performance impact since you should check for these on
> table creation and every select (I believe that with sqlbuilder I can select
> into a temporary table...).

I'd think it would be very easy for SQLObject to maintain such a list
for each driver. Such lists are freely available, for a start.

Not really, if i recall correctly, each backend has a interface that it must follow, addding this will make all the *Connection classes break, which will have
a) maintain a list by hand
b) go to their respective backend code aka python bidings  (which are 6 diferent projects) and then change them to tell upstream which are the keywords.

Also As the Godoy pointed out

"I don't believe that we have to make things worse to protect the developer from shooting himself, after all, as you said, such listings are freely available."

--
Ben Sizer

[Robin Haswell]

If the doc pages on SO in TG and SO had big fat red messages at the top of them warning about this,
then yes, perhaps, but as it stands they don't. Also, an ex-manual SQL developer coming to TG would
not probably think about the problems associated with using reserved keywords, as he/she's used to
escaping them and would probably (and not unreasonably) assume that his ORM dealt with escaping
fields properly anyway.

IMO, if I were using SQL itself I wouldn't have a problem with reserved keywords. However TG
strongly recommends MVC with an ORM, and as such, should at least provide some contextual warning
about reserved keywords - if not at least on "tg-admin sql create". As has been pointed out - "if kw
in dict" is a very efficient operation in Python. In fact python has one of the most efficient
hashtable lookup algorithms in the known universe.

-Rob

[Jorge Vargas]

On 4/26/06, Robin Haswell <r...@digital-crocus.com> wrote:

If the doc pages on SO in TG and SO had big fat red messages at the top of them warning about this,
then yes, perhaps, but as it stands they don't.

We are planning on it, except the red thing that's annoying

Also, an ex-manual SQL developer coming to TG would not probably think about the problems associated with using reserved keywords, as he/she's used to escaping them and would probably (and not unreasonably) assume that his ORM dealt with escaping
fields properly anyway.

I don't agree with that. He will probably (since he is use to control to the SQL) run tg-admin sql sql, and finally end up in sqlmeta class.

This is more something for someone new who is naive or doesn't remenber that "group by" is actually 2 reserved keywords in SQL.

IMO, if I were using SQL itself I wouldn't have a problem with reserved keywords. However TG
strongly recommends MVC with an ORM, and as such, should at least provide some contextual warning
about reserved keywords - if not at least on "tg-admin sql create".

The problem is that tg-admin sql create is actually a wrapper of I forgot the name, the actual command you use with SQLObject so this should be in the core of sqlobject not on tg side.

As has been pointed out - "if kw
in dict" is a very efficient operation in Python. In fact python has one of the most efficient
hashtable lookup algorithms in the known universe.

as much efficient as it is why will you want to run every query into a check for something that you will find out ones and will never forgot. It's like the tuple ones you learn ( ) is not what is in other languajes you never have to remind you of it again.