gmSQL {gmDatabase}

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

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.

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

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