gmDatabase (version 0.5.0)

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))

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

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.

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.
• xx in SQL expressions.
• x>yRepresents x>y in SQL expressions.
• x<=yrepresents x<=y< code=""> 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

See Also

dbSendQuery

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))