gmSQL: Provide an R representation of SQL

Description

gmSQL provides an R representation of SQL, which can be used to construct a complex hierachy of joins and select statements.

Usage

gmSQL(.,expr=substitute(.),env=SQLenv)
gmSQL2SQL(expr,env=SQL2SQLenv)
gmSQLTable(table,as=tick(table))

Value

For gmSQL and gmSQLTable, a call representing the intended SQL expression as R expression. For gmSQL2SQL a character string holding the corresponding SQL expression.

Arguments

.: For gmSQL an unquote expression to be interpreted as the R representation of SQL described under details.
expr: The quoted version of such an expression.
env: The environment holding the variables used in the expression.
table: a character string giving the name of the SQL table denoted.
as: the alias of the table in the SQL expression

Author

K. Gerald van den Boogaart, S. Matos Camacho

Details

These commands allow to construct a representation of a small subset of SQL statements by R language objects. The following (derived) table value statements are supported

join(x,y,on=NULL)Represent the x JOIN y ON on.
leftjoin(x,y,on=NULL)Represent the x LEFT OUTER JOIN y ON on.
select(what=NULL,from=NULL,where=NULL,as=tick()) Represents (SELECT w1=v1,... FROM from WHERE where) AS as, where what is a named list of the form list(w1=v1,...). If any of the terms is NULL it is logically omitted.
table(table,as)Represents table AS as in a FROM clause.
table$nameRepresents tableAlias.name anywhere in an SQL expression e.g. in what and where clauses of a SELECT.
Call(fun,...)Represents fun(...) in SQL expressions.
x==yRepresents x=y in SQL expressions.
x!=yRepresents x!=y in SQL expressions.
x<yRepresents x<y in SQL expressions.
x>yRepresents x>y in SQL expressions.
x<=yRepresents x<=y in SQL expressions.
x>=yRepresents x>=y in SQL expressions.
between(x,y,z)Represents x BETWEEN y AND z in SQL expressions.
x+yRepresents x+y in SQL expressions.
x-yRepresents x-y in SQL expressions.
x*yRepresents x*y in SQL expressions.
x/yRepresents x/y in SQL expressions.
in(x,y)Represents x IN y in SQL expressions.
"x %in% y"Substitute for in(x,y).
&Represents x AND y in SQL expressions.
|Represents x OR y in SQL expressions.
!Represents NOT x in SQL expressions.
ifelse(x,y,z)Represents IF x THEN y ELSE z in SQL expressions.
.(x)Evaluates its argument in env, i.e. it is used to quote calculation, which should be executed in R rather than SQL.
x %<<% yRepresents x << y in SQL expressions (Left shift).
x %>>% yRepresents x >> y in SQL expressions (Right shift).
xor(x,y)Represents x XOR y in SQL expressions.
x%&%yRepresents x & y in SQL expressions (bitwise and).
x%|%yRepresents x | y in SQL expressions (bitwise or).
x%<==>%yRepresents x<=>y in SQL expressions (null safe equality).
x%%yRepresents x % y in SQL expressions (remainder).
x&&yRepresents x && y in SQL expressions (logical AND).
x||yRepresents x || y in SQL expressions (logical OR).
c(...)Represents (...) in SQL expressions.
sum(x)Represents SUM(x) in SQL expressions (sum of values).
avg(x)Represents AVG(x) in SQl expressions (average of values).
min(...)Represents MIN(...) in SQL expressions (minimum of values).
max(...)Represents MAX(...) in SQL expressions (maximum of values).
count(x)Represents COUNT(x) in SQL expressions.

Special environments SQLenv and SQL2SQLenv are used in order to prevent code injection.

References

http://dev.mysql.com/doc/refman/5.7/en

Examples

Run this code

tabA <- gmSQLTable("A")
tabB <- gmSQLTable("B")
ennv <- new.env(parent=SQLenv)
assign("tabA", tabA, ennv)
assign("tabB", tabB, ennv)
AB <- gmSQL(join(tabA,tabB,on=tabA$id==tabB$refID), env=ennv)
AB
gmSQL2SQL(tabA)
gmSQL2SQL(tabB)
gmSQL2SQL(AB)
legalvalues <- 1:3
assign("AB", AB, ennv)
assign("legalvalues", legalvalues, ennv)
sAB <- gmSQL(select(what=list(x=1,y=tabA$y*tabB$y),
                    from=AB,
                    where=Call("log",tabB$othervalue)<=17 &&
                          IN(tabA$inte,c(legalvalues)),
                    as=NULL), env=ennv)

cat(gmSQL2SQL(sAB))

Run the code above in your browser using DataLab