library(ryesql)
context("Parse SQL files")
test_that("split_queries splits named-queries", {
txt <- c(
"-- File description. ",
"",
"-- name: my-query",
"-- Queries for foo.",
"SELECT foo",
"FROM tbl",
"GROUP BY bar",
"",
"-- name: update!",
"-- Updates foo.",
"UPDATE tbl",
"SET foo=1",
"WHERE bar < 10",
"")
expected <- list(
c("-- name: my-query",
"-- Queries for foo.",
"SELECT foo",
"FROM tbl",
"GROUP BY bar"),
c("-- name: update!",
"-- Updates foo.",
"UPDATE tbl",
"SET foo=1",
"WHERE bar < 10"))
queries <- split_queries(txt)
expect_equal(queries, expected)
})
test_that("split_queries handles multiple newlines", {
txt <- c(
"-- name: my-query",
"-- Queries for foo.",
"SELECT foo",
"FROM tbl",
"GROUP BY bar",
"",
"",
"-- name: update!",
"-- Updates foo.",
"UPDATE tbl",
"SET foo=1",
"WHERE bar < 10"
)
expected <- list(
c("-- name: my-query",
"-- Queries for foo.",
"SELECT foo",
"FROM tbl",
"GROUP BY bar"),
c("-- name: update!",
"-- Updates foo.",
"UPDATE tbl",
"SET foo=1",
"WHERE bar < 10"))
queries <- split_queries(txt)
expect_equal(queries, expected)
})
test_that("split_queries throws for invalid queries", {
txt <- c("-- name: get-foos",
"-- Gets the foos.",
"",
"-- name: get-ids",
"-- Gets ids.",
"SELECT id",
"FROM tbl")
expect_error(split_queries(txt), "Invalid syntax")
})
test_that("is_name identifies valid names", {
expect_true( is_name("-- name: foo-bar") )
# Modifying query.
expect_true( is_name("-- name: foo-bar!") )
# Returning query.
expect_true( is_name("-- name: foo-bar<!") )
# Leading/trailing whitespace.
expect_true( is_name(" -- name: foo-bar ") )
# Only check for name identifier - don't validate names.
expect_true( is_name("-- name: foo bar") )
})
test_that("is_name identifies non-names", {
expect_false( is_name("-- foo-bar") )
expect_false( is_name("/* name: foo-bar */") )
})
test_that("extract_name gets name", {
expected <- "foo-bar"
out <- extract_name("-- name: foo-bar")
expect_equal(out, expected)
# Modifying query.
out <- extract_name("-- name: foo-bar!")
expect_equal(out, "foo-bar!")
# Returning query.
out <- extract_name("-- name: foo-bar<!")
expect_equal(out, "foo-bar<!")
# Leading/trailing whitespace.
out <- extract_name(" --name: foo-bar ")
expect_equal(out, expected)
# No space between name and name-identifier.
out <- extract_name("--name:foo-bar")
expect_equal(out, expected)
})
test_that("extract_name throws for invalid name", {
expected_msg_partial <- "does not contain a valid name"
expect_error(extract_name("-- name: foo bar"), expected_msg_partial)
expect_error(extract_name("-- name: foo, bar"), expected_msg_partial)
expect_error(extract_name("-- name: foo (does bar)"), expected_msg_partial)
expect_error(extract_name("-- name: foo!!bar"), expected_msg_partial)
})
test_that("extract_description gets description", {
txt <- c("-- Gets the foos.",
"-- bar : filter by",
"SELECT foo",
"FROM tbl",
"WHERE id = :bar")
expected <- c("Gets the foos.",
" bar : filter by")
expect_equal(extract_description(txt), expected)
# No description.
txt <- c("SELECT foo",
"FROM tbl",
"WHERE id = :bar")
expect_equal(extract_description(txt), NA)
})
test_that("extract_sql gets sql", {
txt <- c("-- Gets the foos.",
"-- bar : filter by",
"SELECT foo",
"FROM tbl",
"WHERE id = :bar")
expected <- "SELECT foo\nFROM tbl\nWHERE id = :bar"
expect_equal(extract_sql(txt), expected)
})
test_that("query_type identifies query from name", {
expect_equal(query_type("modify!"), "modifies")
expect_equal(query_type("get-results<!"), "returns")
expect_equal(query_type("read-tbl"), "reads")
})
test_that("is_prepared identifies parameterized queries", {
# `is_prepared` takes sql as a string, not vector of lines.
# Named parameter.
sql <- c("SELECT *",
"FROM tbl",
"WHERE id > :i")
sql <- paste0(sql, collapse = "\n")
expect_true(is_prepared(sql))
# Snake case.
sql <- c("SELECT *",
"FROM tbl",
"WHERE id > :foo_bar")
sql <- paste0(sql, collapse = "\n")
expect_true(is_prepared(sql))
# Anonymous parameter/placeholder.
sql <- c("SELECT *",
"FROM tbl",
"WHERE id > ?")
sql <- paste0(sql, collapse = "\n")
expect_true(is_prepared(sql))
# No parameter.
sql <- c("SELECT *",
"FROM tbl",
"WHERE id > 4")
sql <- paste0(sql, collapse = "\n")
expect_false(is_prepared(sql))
# Parameter in comment.
sql <- c("SELECT *",
"FROM tbl",
"-- should use placeholder eg ?",
"WHERE id > 10")
sql <- paste0(sql, collapse = "\n")
expect_false(is_prepared(sql))
sql <- c("SELECT *",
"FROM tbl",
"WHERE id > 10 /* ignore :i */")
sql <- paste0(sql, collapse = "\n")
expect_false(is_prepared(sql))
sql <- c("SELECT *",
"FROM tbl",
"/* date:",
" * why is this 10?",
" */",
"WHERE id > 10")
sql <- paste0(sql, collapse = "\n")
expect_false(is_prepared(sql))
})
test_that("parse_named_query parses named-query", {
txt <- c("-- name: add-event!",
"-- Add an event.",
"INSERT INTO events",
"VALUES (?, ?, ?)")
out <- parse_named_query(txt)
expect_is(out, "list")
expect_equal(out$name, "add-event!")
expect_equal(out$type, "modifies")
expect_equal(out$description, "Add an event.")
expect_equal(out$sql, "INSERT INTO events\nVALUES (?, ?, ?)") # no semicolon
expect_true(out$prepared)
})
test_that("parse_anon_query parses query", {
txt <- c("-- Add an event.",
"INSERT INTO events",
"VALUES (?, ?, ?)")
out <- parse_anon_query(txt)
expect_is(out, "list")
expect_equal(out$description, "Add an event.")
expect_equal(out$sql, "INSERT INTO events\nVALUES (?, ?, ?)") # no semicolon
expect_true(out$prepared)
})
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.