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

[Seth Falcon]

Hello all,

I'm picking up this discussion from late September regarding the name 
mangling behavior of dbWriteTable as seen in RSQLite (and probably 
RMySQL too).

Here's a summary of the current situation:

The DBI package provides a make.db.names generic as well as a default 
function, make.db.names.default.  Both RSQLite and RMySQL define a 
method that simply calls this default function.  Both packages use the 
generic inside their dbWriteTable implementations.

The default function uses base::make.names and a translation of "." to 
"_" to make SQL identifiers.  Any strings that are quoted (either single 
or double) are left as-is.  I'm not sure the quote handling is correct. 
  Single quotes are used for string literals in SQL and are not valid 
identifiers, where as double quotes are used to create delimited 
identifiers.

When SQL keywords are encountered and the allow.keywords argument is 
FALSE, the result is the unfortunate name mangling.

Here are some possible changes:

1. Modify DBI::make.db.names.default such that if allow.keywords=FALSE 
any keywords encountered will be quoted using '"'.

2. Make a similar modification, but make it specific to the 
make.db.names method implemented by RSQLite.

3. Move away from name mangling entirely and rely more heavily on 
quoting.  To keep backwards compatibility, add a new argument, say 
'names.as.is' with default value FALSE.  When TRUE, data.frame column 
names will be quoted instead of mangled.  This could be done in addition 
to items 1 or 2.

4. Instead of a new argument to dbWriteTable, add an argument to 
SQLite() to make this configuration of the driver.

Comments?

Also, below, a note on Sean's suggestion for a quoting global option...

On 9/30/09 9:19 AM, Sean Davis wrote:
> 2009/9/30 Seth Falcon<seth using userprimary.net>:
>> 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).

I don't think there is enough SQL generated by RSQLite to make this 
particularly useful.  dbWriteTable is the main SQL generating function I 
think.  I'd prefer to avoid global options whenever possible and allow 
control via function args.  But something along these lines could be useful.

For example, the driver constructor, e.g. SQLite(), could accept an 
argument that set the desired name behavior for dbWriteTable.

In any case, thanks for the feedback.

+ seth