[R-sig-DB] dbWriteTable() is renaming the 'end' column

[Sean Davis]

2009/9/30 Seth Falcon <seth using userprimary.net>:
> Hi Hervé,
>
> 2009/9/29 Hervé Pagès <hpages using fhcrc.org>:
>> You are right that in the ANSI SQL standard, an SQL delimited identifier
>> is delimited by double quotes, not backquotes, sorry.
>> Anyway my point is that, among other possibilities, dbWriteTable() could
>> send a CREATE statement with delimited identifiers, so SQL keywords are
>> not an issue anymore. This is only one possibility. There are others,
>> like just returning an error if the column name is a keyword, or
>> allowing the user disable the mangling, etc. Anything
>> sounds better than silently mangling the column names.
>
> I agree that the silent name mangling is not so helpful, nor is the
> inconsistency in behavior when SQL keywords are used in different
> RSQLite functions.
>
> The question to decide is what change should be made?  Such requests
> could become errors with a message telling the user how to quote
> problem strings.  Or the mangling could be changed to use the quoted
> form and a warning issued.
>
> I would be interested to hear any opinions from users of RSQLite.

I, personally, don't like name mangling as an option.  Is there room
for an option() like "RSQLite.quoteSQL" =TRUE to quote all table names
and column names in generated SQL and =FALSE to generate an error when
a keyword is used?  (Obviously, sql queries generated by the user
would not be subject to the quoting).  The reason to use an option()
to control quoting is that quoting anywhere in SQL generally means
that it is best to quote everywhere, so if there is a known default
(that can be changed), users can choose to stick with quoting
everywhere or nowhere (and deal with the occasional error due to
trying to use a SQL keyword).

Sean