Where to put SQL files in an R package?

Question

I am working on an R package that builds on a postgreSQL database. Hence there are some.sql files that contain a recommended table structure for the corresponding database.

I wonder where to put these files if I want to build a package (for CRAN)?

Hodden

Answer 1

maybe put them in folder "inst" (top level), like inst/sql

then the user or a function of yours could access files there with

base <- system.file('sql', package='bannertpackage')
sqls <- dir(base, "*sql", f=TRUE)

and execute them

http://cran.r-project.org/doc/manuals/R-exts.html#Package-subdirectories

The contents of the inst subdirectory will be copied recursively to the installation directory. Subdirectories of inst should not interfere with those used by R (currently, R, data, demo, exec, libs, man, help, html and Meta, and earlier versions used latex, R-ex). The copying of the inst happens after src is built so its Makefile can create files to be installed.

There will be a top folder in the library folder called sql which is nice, and you can access it from R, which is what you need.

Gabrielagabriele

Thx! could you explain why you feel inst is the right place? Btw: I use devtools / R Studio to build the package. – Hodden

@MattBannert inst is the right place because it's installed with your package. – Airbrush

what if my .sql files are simply querying data, which I pass into, say, RODBC::sqlQuery() to create a data.frame? Should I just create a sql/ directory in that case or what? – Memory

Whenever you need to make files accessible from R put them somewhere under inst. – Gabrielagabriele

qry <- paste(readLines('inst/sql/my_query.sql'), collapse = " ") works just like I had hoped – Gradatim

I am facing a similar problem, but for xlsx files. Could someone please explain how are we accessing these files? – Liquidity