reserved database words

[Richard]

hi,

I like to develop my web2py apps with sqlite and then migrate to mysql
or postgres later. I often then find that some of the table or field
names are reserved in the new database, which requires lots of
renames.

Does anyone have a strategy for dealing with this?
Does there perhaps exist a list of words that are reserved in at least
one database? If so, perhaps this could be somehow incorporated into
web2py to make switching between databases more seamless.

Richard

[Richard]

I came across a reserved word list for mysql:
http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
But it's not complete - the table name 'log' causes an error, but is
not in the list.

[samwyse]

There are two possibilities. First, use names that contain an
underscore. AFAIK, there aren't any reserved words that have them.
Second, SQL has some special quoting rules; character constants are
supposed to be enclosed in single quotes, names may be enclosed in
double quotes. In the latter case, the names may contain reserved
words, embedded spaces, even non-alphanumeric characters. I've seen
code generators that enclose all names in double quotes, whether
needed or not. I'm sure that web2py could be patched to do this as
well.

[mdipierro]

the new DAL, possibly out by the end of the month will take care of
these situations

[Richard]

using an '_' is a good idea - thanks Sam

On Jun 19, 1:37 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> the new DAL, possibly out by the end of the month will take care of
> these situations

I look forward to it!