tests/testthat/test_700_sqlite.R

In sqlq: 'SQL' Query Builder

testthat::context("Real SQLite requests")

testthat::test_that("Quoting works fine with SQLite connector", {
  mydb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  options(sqlq_conn = mydb)
  testthat::expect_equal(TokenIdentifier$new("a:b")$toString(), "`a:b`")
  DBI::dbDisconnect(mydb)
  options(sqlq_conn = DBI::ANSI())
})

testthat::test_that("We can create a simple table", {

  mydb <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  options(sqlq_conn = mydb)

  # Create table
  fields_def <- list(ExprFieldDef$new('id', 'integer', primary=TRUE),
                     ExprFieldDef$new('title', 'varchar(200)', nullable=FALSE),
                     ExprFieldDef$new('author', 'varchar(80)', nullable=FALSE))
  x <- make_create_table(tabl = 'books', fields_def = fields_def)
  res <- DBI::dbExecute(mydb, x$toString())
  testthat::expect_equal(res, 0L)

  # Insert
  fields <- ExprListFields$new(list(ExprField$new('author'),
                                    ExprField$new('title')))
  row1 <- ExprListValues$new(list(ExprValue$new('John Smith'),
                                  ExprValue$new('Memories')))
  values <- StmtValues$new(list(row1))
  x <- QueryInsert$new(
      insert = StmtInsert$new(tabl = 'books', fields = fields),
      values = values
    )
  res <- DBI::dbExecute(mydb, x$toString())
  testthat::expect_equal(res, 1L)

  # Select
  x <- QuerySelect$new(
    select = StmtSelectAll$new(),
    from = StmtFrom$new("books")
  )
  res <- DBI::dbGetQuery(mydb, x$toString())
  testthat::expect_equal(res, data.frame(id=1, title='Memories',
                                         author='John Smith'))

  DBI::dbDisconnect(mydb)
})