gmSQL {gmDatabase} | R Documentation |
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))
Arguments
. |
For |
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 |
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 formlist(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. inwhat
andwhere
clauses of aSELECT
.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.
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.
Author(s)
K. Gerald van den Boogaart, S. Matos Camacho
References
http://dev.mysql.com/doc/refman/5.7/en
See Also
Examples
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))