sqlq helps writing SQL queries, using factory functions oran explicit syntax tree. Withsqlq, identifiers and strings aresafely quoted when needed. Complex conditional expressions can becreated for WHERE clauses, and they can be built dynamically.
The original motivation for this package was to help building complexWHERE clauses, especially when the conditions are not known in advance.Here is a set of conditional parameters that may be used to construct aWHERE clause:
author<-NULLyear_range<-list(min=2010,max=2020)price_ranges<-list(list(min=5,max=9),list(min=20,max=29),list(min=50,max=100))In the following example the WHERE clause is built dynamically, notknowing the values of the different conditions in advance (the listprice ranges may be empty or contain 1, 10 or more elements, the authorname may be provided or not, etc.):
library(sqlq)expr<- ExprCommOp$new("and")if (!is.null(author)) expr$add(ExprBinOp$new(ExprField$new('author')),'=', ExprValue$new(author))if (!is.null(year_range)) expr$add(make_between('year', year_range$min, year_range$max))if (length(price_ranges)>0) { or<- ExprCommOp$new("or")for (rngin price_ranges) or$add(make_between('price', rng$min, rng$max)) expr$add(or)}where<-make_where(expr)The final WHERE expression constructed this way is then used to builda SELECT query:
make_select_all("books",where = where)$toString()#> [1] "SELECT * FROM books WHERE \"year\" BETWEEN 2010 AND 2020 AND (price BETWEEN 5 AND 9 OR price BETWEEN 20 AND 29 OR price BETWEEN 50 AND 100);"Another motivation was to automatically generate the correct quotingfor identifiers and strings, depending on the database management system(DBMS) used, but avoid quoting when unnecessary. Identifiers are onlyquoted when they contain special characters (spaces, punctuation, etc.)or when they are reserved keywords.
Using the factory functionmake_select_all(), we cancreate a simpleselect * query on a table:
We may add the DISTINCT keyword to remove duplicates:
Using themake_select() factory function, we choose thefields we want to retrieve:
We may add theLIMIT keyword by specifying the limitinside themake_select*() functions:
Themake_select*() functions accept also aWHERE clause:
Expression objects can be combined to form more complex expressions.In the following example we want to retrieve all books whose author’sname starts with an “A” and whose publication year is after 2015.
We first define the expressions to check author en year:
year_cond<- ExprBinOp$new(ExprField$new("year"),">", ExprValue$new(2015))author_cond<- ExprBinOp$new( ExprField$new("author"),"like", ExprValue$new("A%"))Then we build the WHERE statement:
Finally we construct the SELECT query:
Arbitrarily complex expressions can be built using the variousExpr* classes and used inside a WHERE clause. In thisexample we use a vector of patterns to build a condition that tests afield on the different patterns.
From the following list of patterns:
And the author field:
We build the list of expressions that checks all the patterns:
Then we link all these expressions with OR operators:
Finally, we build the SELECT query:
In this example, we make a join between tablesbooks andauthors.
First we define the fields we want to retrieve, from tablebooks:
Then we define the join statement, that operates onauthors.id andbooks.author_id, using themake_join() function:
Finally we create the SELECT query:
make_select(tabl ="books",fields = fields,join = join)$toString()#> [1] "SELECT \"title\", authors.name FROM books INNER JOIN authors ON authors.id = books.author_id;"By default, themake_join() function creates anINNER JOIN. If you want to use a different type of join,you can specify it using thetype argument:
You can also join multiple tables by using theadd()method of theSelectQuery class, which is returned by themake_select_all() ormake_select()functions:
To generate anINSERT INTO query, use themake_insert():
To generate aCREATE TABLE query, use themake_create_table() function.
We must first define the fields/columns of the table:
fields_def<-list(ExprFieldDef$new('id','integer',primary=TRUE), ExprFieldDef$new('title','varchar(200)',nullable=FALSE), ExprFieldDef$new('author','varchar(80)',nullable=FALSE))Then we can call themake_create_table() function:
To generate aDELETE FROM query, use themake_delete() function.
We first define the WHERE clause:
Then we call themake_delete() function:
To generate aUPDATE query, use themake_update() function, along with themake_set() function.
We first define the WHERE clause:
Then we create theSET statement using themake_set() function:
Finally we create the query using themake_update()function:
Binary operators are used to compare two expressions, such as fieldsor values. TheExprBinOp class is used to create suchexpressions. It accepts two operands and an operator as arguments. Theoperands can beExprField,ExprValue, or otherExpr instances.
comp1<- ExprBinOp$new(ExprField$new("year"),">", ExprValue$new(1994))comp2<- ExprBinOp$new(ExprField$new("author"),"=", ExprValue$new("John Doe"))where<- StmtWhere$new(ExprBinOp$new(comp1,"or", comp2))make_select_all("books",distinct =TRUE,where = where)$toString()#> [1] "SELECT DISTINCT * FROM books WHERE (\"year\" > 1994) OR (author = 'John Doe');"Commutative operators are used to combine multiple expressions withthe same operator, such asAND orOR. TheExprCommOp class is used to create such expressions. Itaccepts an operator and a list of expressions as arguments.
or<- ExprCommOp$new("or",list( ExprBinOp$new(ExprField$new("year"),">", ExprValue$new(1994)), ExprBinOp$new(ExprField$new("author"),"=", ExprValue$new("John Doe")), ExprBinOp$new(ExprField$new("title"),"like", ExprValue$new("A%"))))where<- StmtWhere$new(or)make_select_all("books",distinct =TRUE,where = where)$toString()#> [1] "SELECT DISTINCT * FROM books WHERE (\"year\" > 1994) OR (author = 'John Doe') OR (\"title\" LIKE 'A%');"Using themake_between() function, we can create aBETWEEN expression:
make_between() accepts both atomic values andExprValue instances for low and high limits.
To test if a field is NULL use theExprIsNull class:
To test if a field is NULL use theExprIsNotNullclass:
In order to generate correct SQL requests for a DBMS, it isrecommended to inform thesqlq package about the connector weare using. This is done with thesqlq_conn globaloption.
First we create the database connector (here a connector to anIn-Memory instance of SQLite DB):
Then we declare the connector tosqlq:
Finally we build the request:
fields<-c("The Title","author")where<- StmtWhere$new(ExprBinOp$new(ExprField$new("author"),"=", ExprValue$new("John Doe")))make_select("books",fields = fields,where = where)$toString()#> [1] "SELECT `The Title`, author FROM books WHERE author = 'John Doe';"We can see that now backticks (`) are used instead of regular quotes(“). This is indeed the official mean of quoting identifiers in SQLite.This is also the case for MariaDB.
By default keywords and alphabetical operators (OR, AND ,etc.) arewritten uppercase.
You can force lowercase by setting the global optionsqlq_uppercase toFALSE:
options(sqlq_uppercase =FALSE)comp1<- ExprBinOp$new(ExprField$new("year"),">", ExprValue$new(1994))comp2<- ExprBinOp$new(ExprField$new("author"),"=", ExprValue$new("John Doe"))where<- StmtWhere$new(ExprBinOp$new(comp1,"or", comp2))make_select_all("books",distinct =TRUE,where = where)$toString()#> [1] "select distinct * from books where (`year` > 1994) or (author = 'John Doe');"Unnecessary spaces may be removed by setting thesqlq_spaces global option toFALSE.
Without setting the option, spaces are set around non-alphabeticaloperators (here>):
where<- StmtWhere$new(ExprBinOp$new( ExprField$new("year"),">", ExprValue$new(1994)))make_select_all("books",distinct =TRUE,where = where)$toString()#> [1] "select distinct * from books where `year` > 1994;"When setting the option toFALSE, such space charactersare removed: