[R] SQL statements (directly) in R

[Wensui Liu]

chris,
if you could create a package similar to proc sql in SAs, that will be so sweet.


On 2/12/07, christian.ritter at shell.com <christian.ritter at shell.com> wrote:
> Hi R-users,
>
> This note will interest people who would like to use sql statements on R data frames (a bit like proc sql in SAS). Please reply to my only, unless you really want to keep the entire R-help list posted on this.
>
> I've been thinking about a packgage implementing sql queries in R. I'm almost about starting to write it in a very rudimentary version. What I have in mind is the following:
>
> Work in two ways:
> via a generic sql("..") wrapper which allows a generic query statement
> and via convenience functions, such as SELECT("..."), ...
> what would be needed is an "sqlTable" class extending the data frame. This class will have to have extra slots for indices and some other stuff. I would try to stay very basic in the beginning and also use relatively inefficient handling of the tables. Later-on, direct calls using the binary representations could  replace the high level handling.
>
> Now come my questions:
> - have others started working on this?
> - are others interested in this?
> - ideas on how to go about it?
>
> Chris
>
> P.S.:
> Here are a few ideas I was thinking about
> One way would be to incorporate a gpl or lgpl rdbms into the package, to push the data-frame to it, to execute the statement there and to get the result back. The advantage: fast to implement. The disadvantage: pushing the data is a bad idea (but then again, at the top level, R will make a copy of it anyway, most probably). The convenience wrappers would then construct sql statements and the db engine would evaluate them.
>
> The other idea is to stay in R and to link the wrappers to adequately composed calls to subset, cbind, rbind, etc. Here it would be more challenging to create the sql("..") interface since its string would have to be parsed.
>
> The political incorrect thing about these SQL functions is that they (UPDATE, INSERT) will have to modify objects within the function call. They would not work via the return object.
>
> As I said, comments welcome.
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>


-- 
WenSui Liu
A lousy statistician who happens to know a little programming
(http://spaces.msn.com/statcompute/blog)