Description Usage Arguments Details Value Author(s) References See Also Examples
gmSQL provides an R representation of SQL, which can be used to construct a complex hierachy of joins and select statements.
1 2 3 | gmSQL(.,expr=substitute(.),env=SQLenv)
gmSQL2SQL(expr,env=SQL2SQLenv)
gmSQLTable(table,as=tick(table))
|
. |
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 |
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.
For gmSQL and gmSQLTable, a call representing the intended SQL expression as R expression. For gmSQL2SQL a character string holding the corresponding SQL expression.
K. Gerald van den Boogaart, S. Matos Camacho
http://dev.mysql.com/doc/refman/5.7/en
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | 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))
|
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.