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

Run this code
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))

Run the code above in your browser using DataCamp Workspace