inst/doc/intro.R

In sqlq: 'SQL' Query Builder

## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## -----------------------------------------------------------------------------
author <- NULL
year_range <- list(min=2010, max=2020)
price_ranges <- list(list(min=5, max=9), list(min=20,max=29),
                     list(min=50,max=100))

## -----------------------------------------------------------------------------
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 (rng in price_ranges)
    or$add(make_between('price', rng$min, rng$max))
  expr$add(or)
}
where <- make_where(expr)

## -----------------------------------------------------------------------------
make_select_all("books", where = where)$toString()

## -----------------------------------------------------------------------------
make_select_all("books")$toString()

## -----------------------------------------------------------------------------
make_select_all("books", distinct = TRUE)$toString()

## -----------------------------------------------------------------------------
make_select("books", fields = c("title", "author"))$toString()

## -----------------------------------------------------------------------------
make_select_all("books", limit = 16)$toString()

## -----------------------------------------------------------------------------
where <- StmtWhere$new(ExprBinOp$new(
  ExprField$new("year"), ">",
  ExprValue$new(2015)
))
make_select_all("books", where = where)$toString()

## -----------------------------------------------------------------------------
year_cond <- ExprBinOp$new(ExprField$new("year"), ">", ExprValue$new(2015))
author_cond <- ExprBinOp$new(
  ExprField$new("author"), "like",
  ExprValue$new("A%")
)

## -----------------------------------------------------------------------------
where <- StmtWhere$new(ExprBinOp$new(year_cond, "and", author_cond))

## -----------------------------------------------------------------------------
make_select_all("books", where = where)$toString()

## -----------------------------------------------------------------------------
patterns <- c("A%", "D%", "Z%")

## -----------------------------------------------------------------------------
author <- ExprField$new("author")

## -----------------------------------------------------------------------------
expressions <- lapply(
  patterns,
  function(x) {
    ExprBinOp$new(
      author, "like",
      ExprValue$new(x)
    )
  }
)

## -----------------------------------------------------------------------------
cond <- ExprCommOp$new("or", expressions)

## -----------------------------------------------------------------------------
make_select_all("books", where = StmtWhere$new(cond))$toString()

## -----------------------------------------------------------------------------
fields <- list(ExprField$new("title"), ExprField$new("name", "authors"))

## -----------------------------------------------------------------------------
join <- make_join("id", "authors", "author_id", "books")

## -----------------------------------------------------------------------------
make_select(tabl = "books", fields = fields, join = join)$toString()

## -----------------------------------------------------------------------------
make_join("id", "authors", "author_id", "books", type = "LEFT")$toString()

## -----------------------------------------------------------------------------
x <- make_select_all(tabl = "books")
x$add(make_join("book_id", "bookcat", "id", "books"))
x$add(make_join("id", "categories", "cat_id", "bookcat"))
x$toString()

## -----------------------------------------------------------------------------
values <- list(list('John Smith', 'Memories', 1999),
               list('Barbara', 'My Life', 2010))
make_insert(tabl = 'books', fields = c('author', 'title', 'year'),
            values = values)$toString()

## -----------------------------------------------------------------------------
fields_def <- list(ExprFieldDef$new('id', 'integer', primary=TRUE),
                   ExprFieldDef$new('title', 'varchar(200)', nullable=FALSE),
                   ExprFieldDef$new('author', 'varchar(80)', nullable=FALSE))

## -----------------------------------------------------------------------------
make_create_table(tabl = 'books', fields_def = fields_def)$toString()

## -----------------------------------------------------------------------------
where <- StmtWhere$new(ExprBinOp$new(
  ExprField$new("year"), "<",
  ExprValue$new(2015)
))

## -----------------------------------------------------------------------------
make_delete(tabl = "books", where = where)$toString()

## -----------------------------------------------------------------------------
where <- StmtWhere$new(ExprBinOp$new(
  ExprField$new("year"), "<",
  ExprValue$new(2010)
))

## -----------------------------------------------------------------------------
set <- make_set(price = 9.50, old = TRUE)

## -----------------------------------------------------------------------------
make_update('books', set = set, where = where)$toString()

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

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

## -----------------------------------------------------------------------------
make_between('i', 1, 10)$toString()

## -----------------------------------------------------------------------------
StmtWhere$new(ExprIsNull$new(ExprField$new("name")))$toString()

## -----------------------------------------------------------------------------
StmtWhere$new(ExprIsNotNull$new(ExprField$new("name")))$toString()

## -----------------------------------------------------------------------------
mydb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")

## -----------------------------------------------------------------------------
options(sqlq_conn = mydb)

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

## ----echo=FALSE, results=FALSE------------------------------------------------
DBI::dbDisconnect(mydb)

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

## -----------------------------------------------------------------------------
where <- StmtWhere$new(ExprBinOp$new(
  ExprField$new("year"), ">",
  ExprValue$new(1994)
))
make_select_all("books", distinct = TRUE, where = where)$toString()

## -----------------------------------------------------------------------------
options(sqlq_spaces = FALSE)
where <- StmtWhere$new(ExprBinOp$new(
  ExprField$new("year"), ">",
  ExprValue$new(1994)
))
make_select_all("books", distinct = TRUE, where = where)$toString()