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

[Prof Brian Ripley]

I presume this is about dbWriteTable in package DBI, and also RSQLite, 
but neither are mentioned and the 'example' fails to run as written.

On Tue, 29 Sep 2009, Hervé Pagès wrote:

> Gabor Grothendieck wrote:
>> end is a reserved SQL keyword.
>
> Ok, maybe so what?
>
> This works:
>
>  dbSendQuery(con, "CREATE TABLE mydata (start INTEGER, end INTEGER)")
>
> and if it didn't, I should get an error (or at least a warning).
> Then I could try again with:
>
>  dbSendQuery(con, "CREATE TABLE mydata (`start` INTEGER, `end` INTEGER)")
>
> Using backquotes allows me to use whatever name I want for my columns, even 
> SQL keywords. This is ANSI SQL.

Without telling us *which* ANSI SQL standard this is unclear, but I 
believe that it is a false claim.  It seems so do the authors of 
MySQL, who have an ANSI_QUOTE mode using " to quote identifiers.  As I 
know from RODBC, this is one of those areas of differences between 
actual SQL engines, and one which ODBC does not attempt to paper over.

See also 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg09583.html
-- so it looks like backquotes were added to SQLite in 2005, long 
after the design of DBI/RSQLite, and as a non-ANSI extension.

RODBC successfully uses double quotes for identifiers with SQLite, and 
the RODBC version of your example works as you expected.

> IMO, it would be much better if dbWriteTable() was:
>  - not trying to be too smart and just send the CREATE statement,
>    leaving the DB backend decide whether it's valid or not, and
>    eventually return an error,
>  - if dbWriteTable() really wants to be smart, then it could try
>    a little bit harder to create the table I want with the column
>    names I want by using backquotes.

But DBI::dbWriteTable() does neither of these: it calls the method 
in the backend, which in the case of RSQLite is sqliteWriteTable(). 
That calls RSQLite::dbBuildTableDefinition() to make the CREATE TABLE 
query, and that calls DBI::make.db.names(), which is a generic with a 
method in RSQLite.

So the correct subject of your scorn would seem to be 
RSQLite::dbBuildTableDefinition() and its lack of documentation. As I 
have argued earlier, I think that the solution has to be specific to 
the backend.

> Just my 2 c.


> Cheers,
> H.
>
>> 
>> 2009/9/29 Hervé Pagès <hpages using fhcrc.org>:
>>> Hi,
>>> 
>>> Consider the following data:
>>>
>>>  mydata <- data.frame(start=1:5, end=11:15)
>>> 
>>> Then storing and retrieving the data shows that the 'end'
>>> column was renamed:
>>>
>>>  drv <- dbDriver("SQLite")
>>>  con <- dbConnect(drv, dbname=":memory:")
>>>
>>>  > dbWriteTable(con, "mydata", mydata)
>>>  [1] TRUE
>>>  > dbReadTable(con, "mydata")
>>>    start end__1
>>>  1     1     11
>>>  2     2     12
>>>  3     3     13
>>>  4     4     14
>>>  5     5     15
>>> 
>>> The culprit doesn't seem to be dbReadTable():
>>>
>>>  > dbGetQuery(con, "SELECT * FROM mydata")
>>>    row_names start end__1
>>>  1         1     1     11
>>>  2         2     2     12
>>>  3         3     3     13
>>>  4         4     4     14
>>>  5         5     5     15
>>> 
>>> and using the sqlite3 client from outside R confirms this.
>>> 
>>> Looks like a bug in dbWriteTable().
>>> 
>>> Cheers,
>>> H.
>>> 
>>> --
>>> Hervé Pagès
>>> 
>>> Program in Computational Biology
>>> Division of Public Health Sciences
>>> Fred Hutchinson Cancer Research Center
>>> 1100 Fairview Ave. N, M2-B876
>>> P.O. Box 19024
>>> Seattle, WA 98109-1024
>>> 
>>> E-mail: hpages using fhcrc.org
>>> Phone:  (206) 667-5791
>>> Fax:    (206) 667-1319
>>> 
>>> _______________________________________________
>>> R-sig-DB mailing list -- R Special Interest Group
>>> R-sig-DB using stat.math.ethz.ch
>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>> 
>
> -- 
> Hervé Pagès
>
> Program in Computational Biology
> Division of Public Health Sciences
> Fred Hutchinson Cancer Research Center
> 1100 Fairview Ave. N, M2-B876
> P.O. Box 19024
> Seattle, WA 98109-1024
>
> E-mail: hpages using fhcrc.org
> Phone:  (206) 667-5791
> Fax:    (206) 667-1319
>
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group
> R-sig-DB using stat.math.ethz.ch
> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>

-- 
Brian D. Ripley,                  ripley using stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595