Escaping odd table names in SQLite not working

[pschmied]

This report is reproduced from this Stack Overflow question:
http://stackoverflow.com/questions/28132697/bracket-escaped-table-names-with-dplyr?noredirect=1#comment44719663_28132697

I'm programmatically fetching a bunch of datasets, many of them having silly names that begin with numbers and have special characters like minus signs in them. Because none of the datasets are particularly large, and I wanted the benefit R making its best guess about data types, I'm (ab)using dplyr to dump these tables into SQLite.

I am using square brackets to escape the horrible table names, but this doesn't seem to work. For example:

data(iris)
foo.db <- src_sqlite("foo.sqlite3", create = TRUE)
copy_to(foo.db, df=iris, name="[14m3-n4m3]")

This results in the error message:

Error in sqliteSendQuery(conn, statement, bind.data) : error in statement: no such table: 14m3-n4m3

This works if I choose a sensible name. However, due to a variety of reasons, I'd really like to keep the cumbersome names. I am also able to create such a badly-named table directly from sqlite:

sqlite> create table [14m3-n4m3](foo,bar,baz);
sqlite> .tables
14m3-n4m3

Without cracking into things too deeply, this looks like dplyr is handling the square brackets in some way that I cannot figure out. My suspicion is that this is a bug, but I wanted to check here first to make sure I wasn't missing something.

EDIT: I forgot to mention the case where I just pass the janky name directly to dplyr. This errors out as follows:

library(dplyr)

data(iris)
foo.db <- src_sqlite("foo.sqlite3", create = TRUE)
copy_to(foo.db, df=iris, name="14M3-N4M3")

Error in sqliteSendQuery(conn, statement, bind.data) : 
  error in statement: unrecognized token: "14M3"

[pschmied]

I've started trying to dig into this myself a bit. Just for fun, I decided to install / test against a Postgres database. This appears to work correctly without escaping:

library(dplyr)
library("RPostgreSQL")

foo.db <- src_postgres("foo")

foo <- copy_to(foo.db, df=iris, name="14M3-N4M3")

[FvD]

Apparently the issue is with the first character being a number in "14M3". It tried to save a sequence of databases with a UUID and copy_to will fail as soon as there is a table name with a number as the first character (this is copying to a sqlite database).

Reports are added nicely as long as there is a letter in front:

> reportdb
src:  sqlite 3.8.6 [reportdata.sqlite3]
tbls: a325db39, a54088fb, b25fdcd4, b45adc99, b68946ab, d135c554, e59bdbe7, ef2ae5ff, result_view, sqlite_stat1

Error as soon as there is a number in front:

> resultsDB <- copy_to(reportdata, result_view, name="7b1ee1d2", temporary=FALSE)
Error in sqliteSendQuery(conn, statement, bind.data) : 
  error in statement: unrecognized token: "7b1ee1d2"

Fixed by adding a letter (to verify):

> resultsDB <- copy_to(reportdata, result_view, name="e7b1ee1d2", temporary=FALSE)
> reportdb
src:  sqlite 3.8.6 [reportdata.sqlite3]
tbls: a325db39, a54088fb, b25fdcd4, b45adc99, b68946ab, d135c554, e59bdbe7, e7b1ee1d2, ef2ae5ff, result_view,
  sqlite_stat1

[NikNakk]

I've just made a couple of changes to src-sqlite.r which I think fix the problem. I've put in a pull request (from NikNakk/dplyr).

[hadley]

This is fixed in the development version of RSQLite