## ----include = FALSE----------------------------------------------------------
source("common.R")
require_or_quit(c("DBI", "RSQLite", "pkgload"))

## ----include = FALSE----------------------------------------------------------
path_db <- tempfile()
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = path_db)
DBI::dbWriteTable(con, "mtcars", mtcars)
DBI::dbWriteTable(con, "iris", iris)
DBI::dbWriteTable(con, "npk", npk)
DBI::dbDisconnect(con)

path_root <- tempfile()
orderly::orderly_init(path_root)
fs::dir_create(path_root)

jsonlite::write_json(
  list(minimum_orderly_version = "1.99.90",
       plugins = list(
         example.db = list(
           path = path_db))),
  file.path(path_root, "orderly_config.json"),
  auto_unbox = TRUE,
  pretty = TRUE)

path_example <- file.path(path_root, "src", "example")
fs::dir_create(path_example)
writeLines(c(
  'dat <- example.db::query("SELECT * FROM mtcars WHERE cyl == 4")',
  'orderly::orderly_artefact(description = "Summary of data", "data.rds")',
  "",
  'saveRDS(summary(dat), "data.rds")'),
  file.path(path_example, "example.R"))

update_package <- function(key, path_pkg) {
  code <- unname(Filter(
    function(x) identical(attr(x, "chunk_opts")$export_to_package, key),
    knitr::knit_code$get()))
  writeLines(
    paste(vapply(code, paste, "", collapse = "\n"), collapse = "\n\n"),
    file.path(path_pkg, "R/plugin.R"))
}

## ----echo = FALSE-------------------------------------------------------------
withr::with_dir(path_root, fs::dir_tree("."))

## ----echo = FALSE, results = "asis"-------------------------------------------
json_output(readLines(file.path(path_root, "orderly_config.json")))

## ----echo = FALSE, results = "asis"-------------------------------------------
r_output(readLines(file.path(path_example, "example.R")))

## ----include = FALSE----------------------------------------------------------
path_pkg <- file.path(tempfile(), "example.db")
fs::dir_create(path_pkg)

writeLines(c(
  "Package: example.db",
  "Version: 0.0.1",
  "License: CC0",
  "Title: Orderly Database Example Plugin",
  "Description: Simple example of an orderly plugin.",
  "Authors@R: person('Orderly Authors', role = c('aut', 'cre'),",
  "    email = 'email@example.com')",
  "Imports: orderly"),
  file.path(path_pkg, "DESCRIPTION"))

fs::dir_create(file.path(path_pkg, "R"))
writeLines("export(query)", file.path(path_pkg, "NAMESPACE"))

update_package("minimal", path_pkg)

## ----echo = FALSE-------------------------------------------------------------
withr::with_dir(path_pkg, fs::dir_tree("."))

## ----echo = FALSE, results = "asis"-------------------------------------------
plain_output(readLines(file.path(path_pkg, "DESCRIPTION")))

## ----export_to_package = "minimal"--------------------------------------------
db_config <- function(data, filename) {
  data
}

## ----export_to_package = "minimal", eval = FALSE------------------------------
# query <- function(sql) {
#   ctx <- orderly::orderly_plugin_context("example.db")
#   dbname <- ctx$config$path
#   con <- DBI::dbConnect(RSQLite::SQLite(), dbname)
#   on.exit(DBI::dbDisconnect(con))
#   DBI::dbGetQuery(con, sql)
# }

## ----export_to_package = "minimal", eval = FALSE------------------------------
# .onLoad <- function(...) {
#   orderly::orderly_plugin_register(
#     name = "example.db",
#     config = db_config)
# }

## ----echo = FALSE, results = "asis"-------------------------------------------
r_output(readLines(file.path(path_pkg, "R/plugin.R")))

## ----echo = FALSE, results = "asis"-------------------------------------------
plain_output(readLines(file.path(path_pkg, "NAMESPACE")))

## ----eval = FALSE-------------------------------------------------------------
# pkgload::load_all()

## ----echo = FALSE-------------------------------------------------------------
pkgload::load_all(path_pkg)

## -----------------------------------------------------------------------------
library(orderly)
orderly_run("example", root = path_root)

## ----export_to_package = "full", eval = FALSE---------------------------------
# db_config <- function(data, filename) {
#   if (!is.list(data) || is.null(names(data)) || length(data) == 0) {
#     stop("Expected a JSON object for orderly_config.json:example.db")
#   }
#   if (length(data$path) != 1 || !is.character(data$path)) {
#     stop("Expected a string for orderly_config.json:example.db:path")
#   }
#   if (!file.exists(data$path)) {
#     stop(sprintf(
#       "The database '%s' does not exist (orderly_config:example.db:path)",
#       data$path))
#   }
#   data
# }

## ----export_to_package = "full", eval = FALSE---------------------------------
# query <- function(sql) {
#   ctx <- orderly::orderly_plugin_context("example.db")
#   dbname <- ctx$config$path
#   con <- DBI::dbConnect(RSQLite::SQLite(), dbname)
#   on.exit(DBI::dbDisconnect(con))
#   d <- DBI::dbGetQuery(con, sql)
#   info <- list(sql = sql, rows = nrow(d), cols = names(d))
#   orderly::orderly_plugin_add_metadata("example.db", "query", info)
#   d
# }

## -----------------------------------------------------------------------------
db_serialise <- function(data) {
  for (i in seq_along(data$query)) {
    # Always save cols as a vector, even if length 1:
    data$query[[i]]$cols <- I(data$query[[i]]$cols)
  }
  jsonlite::toJSON(data$query, auto_unbox = TRUE)
}

## ----echo = FALSE, results = "asis"-------------------------------------------
dir.create(file.path(path_pkg, "inst"), FALSE, TRUE)
path_schema <- file.path(path_pkg, "inst", "schema.json")
writeLines(c(
  "{",
  '    "$schema": "http://json-schema.org/draft-07/schema#",',
  "",
  '    "type": "array",',
  '    "items": {',
  '        "type": "object",',
  '        "properties": {',
  '            "sql": {',
  '                "type": "string"',
  "            },",
  '            "rows": {',
  '                "type": "number"',
  "            },",
  '            "cols": {',
  '                "type": "array",',
  '                "items": {',
  '                    "type": "string"',
  "                }",
  "            }",
  "        },",
  '        "required": ["sql", "rows", "cols"],',
  '        "additionalProperties": false',
  "    }",
  "}"),
  path_schema)
json_output(readLines(path_schema))

## ----echo = FALSE, results = "asis"-------------------------------------------
db_deserialise <- function(data) {
  data.frame(
    sql = vapply(data, "[[", character(1), "sql"),
    rows = vapply(data, "[[", numeric(1), "rows"),
    cols = I(lapply(data, "[[", "cols")))
}

## ----export_to_package = "full", eval = FALSE---------------------------------
# .onLoad <- function(...) {
#   orderly::orderly_plugin_register(
#     name = "example.db",
#     config = db_config,
#     serialise = db_serialise,
#     deserialise = db_deserialise,
#     schema = "schema.json")
# }

## ----echo = FALSE-------------------------------------------------------------
update_package("full", path_pkg)
withr::with_dir(path_root, fs::dir_tree("."))

## ----echo = FALSE, results = "asis"-------------------------------------------
r_output(readLines(file.path(path_pkg, "R/plugin.R")))

## ----include = FALSE----------------------------------------------------------
pkgload::load_all(path_pkg)

## -----------------------------------------------------------------------------
id <- orderly_run("example", root = path_root)
meta <- orderly_metadata(id, root = path_root)
meta$custom$example.db