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$name
Represents
tableAlias.name
anywhere in an SQL expression e.g. inwhat
andwhere
clauses of aSELECT
.- Call(fun,...)
Represents
fun(...)
in SQL expressions.- x==y
Represents
x=y
in SQL expressions.- x!=y
Represents
x!=y
in SQL expressions.- x<y
Represents
x<y
in SQL expressions.- x>y
Represents
x>y
in SQL expressions.- x<=y
Represents
x<=y
in SQL expressions.- x>=y
Represents
x>=y
in SQL expressions.- between(x,y,z)
Represents
x BETWEEN y AND z
in SQL expressions.- x+y
Represents
x+y
in SQL expressions.- x-y
Represents
x-y
in SQL expressions.- x*y
Represents
x*y
in SQL expressions.- x/y
Represents
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 %<<% y
Represents
x << y
in SQL expressions (Left shift).- x %>>% y
Represents
x >> y
in SQL expressions (Right shift).- xor(x,y)
Represents
x XOR y
in SQL expressions.- x%&%y
Represents
x & y
in SQL expressions (bitwise and).- x%|%y
Represents
x | y
in SQL expressions (bitwise or).- x%<==>%y
Represents
x<=>y
in SQL expressions (null safe equality).- x%%y
Represents
x % y
in SQL expressions (remainder).- x&&y
Represents
x && y
in SQL expressions (logical AND).- x||y
Represents
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))