tests/testthat/test_510_select_query.R

In sqlq: 'SQL' Query Builder

testthat::context("Select Query class")

testthat::test_that("We can create a simple SELECT query", {
  x <- QuerySelect$new(
    select = StmtSelectAll$new(),
    from = StmtFrom$new("books")
  )
  testthat::expect_equal(x$toString(), "SELECT * FROM books;")

  x <- make_select_all(tabl = "books")
  testthat::expect_equal(x$toString(), "SELECT * FROM books;")

  x <- make_select(tabl = "books", fields = "title")
  testthat::expect_equal(x$toString(), 'SELECT "title" FROM books;')

  x <- make_select(tabl = "books", fields = c("title", "year"))
  testthat::expect_equal(x$toString(), 'SELECT "title", "year" FROM books;')
})

testthat::test_that("We get name quoting/escaping when needed", {
  x <- make_select(tabl = "books", fields = "book_title")
  testthat::expect_equal(x$toString(), 'SELECT book_title FROM books;')
  x <- make_select(tabl = "books", fields = "book title")
  testthat::expect_equal(x$toString(), 'SELECT "book title" FROM books;')
  x <- make_select(tabl = "books table", fields = "title")
  testthat::expect_equal(x$toString(), 'SELECT "title" FROM "books table";')
})

testthat::test_that("We can set two fields", {
  x <- make_select(tabl = "books", fields = c("title", "author"))
  testthat::expect_equal(x$toString(), 'SELECT "title", author FROM books;')
})

testthat::test_that("We can use a limit", {
  x <- make_select(tabl = "books", fields = "title", limit = 10)
  testthat::expect_equal(x$toString(), 'SELECT "title" FROM books LIMIT 10;')
  x <- make_select_all(tabl = "books", limit = 10)
  testthat::expect_equal(x$toString(), "SELECT * FROM books LIMIT 10;")
})

testthat::test_that("We can enable the distinct keyword", {
  x <- make_select(tabl = "books", fields = "title", distinct = TRUE)
  testthat::expect_equal(x$toString(), 'SELECT DISTINCT "title" FROM books;')
})

testthat::test_that("We can set a where clause", {
  where <- StmtWhere$new(ExprBinOp$new(
    ExprField$new("year"), "=",
    ExprValue$new(2015)
  ))
  x <- make_select(tabl = "books", fields = "title", where = where)
  testthat::expect_equal(
    x$toString(),
    'SELECT "title" FROM books WHERE "year" = 2015;'
  )
})

testthat::test_that("We can define a join", {
  fields <- list(ExprField$new("title"), ExprField$new("name", "authors"))
  join <- StmtJoin$new(
    ExprField$new("id", "authors"),
    ExprField$new("author_id", "books")
  )
  x <- make_select(tabl = "books", fields = fields, join = join)
  testthat::expect_equal(
    x$toString(),
    paste(
      'SELECT "title", authors.name',
      "FROM books INNER JOIN authors ON",
      'authors.id = books.author_id;'
    )
  )

  x <- make_select_all(tabl = "books", join = join)
  testthat::expect_equal(
    x$toString(),
    paste(
      "SELECT *",
      "FROM books INNER JOIN authors ON",
      'authors.id = books.author_id;'
    )
  )
})

testthat::test_that("The order of declaration of statements does not matter", {
  fields <- list(ExprField$new("title"), ExprField$new("year"))
  and <- sqlq::ExprCommOp$new('and')
  where <- sqlq::make_where(and)
  x <- make_select(tabl = "books", fields = fields, where = where)
  join <- make_join("book_id", "authors", "id")
  x$add(join)
  and$add(sqlq::ExprBinOp$new(sqlq::ExprField$new("name", "authors"),
                              "=", sqlq::ExprValue$new("John Smith")))
  testthat::expect_equal(x$toString(), paste(
    'SELECT "title", "year" FROM books',
    "INNER JOIN authors ON authors.book_id = id",
    "WHERE (authors.name = 'John Smith');"
  ))
})

testthat::test_that("We can define a double join", {
  x <- make_select_all(tabl = "books")
  x$add(make_join("book_id", "bookcat", "id", "books"))
  x$add(make_join("id", "categories", "cat_id", "bookcat"))
  testthat::expect_equal(x$toString(),
    paste("SELECT * FROM books INNER JOIN bookcat ON bookcat.book_id = books.id",
          "INNER JOIN categories ON categories.id = bookcat.cat_id;"))
})