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

[Gabor Grothendieck]

On Wed, Sep 30, 2009 at 12:19 PM, Sean Davis <sdavis2 using mail.nih.gov> wrote:
> 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).

What would be nice would be

- a raw option which sends the sql string to the database unchanged and
- for any preprocessing options an ability to get the final string
that will be sent to the database prior to sending it