#' Set SQLite database location and name
#'
#' @md
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_start <- function (path = "~/OneDrive/Magnum Opus/project/data/db",
db_name = "db_qs.sqlite") {
options(mrsq_db_dir = path)
options(mrsq_db_name = db_name)
}
#' Initialize SQLite database
#'
#' @md
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_init <- function (path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
RSQLite::dbConnect(RSQLite::SQLite(), sprintf("%s/%s", path, db_name))
}
#' Read table from SQLite database
#'
#' @md
#' @param query query statement
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_query <- function (query,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
res <- RSQLite::dbGetQuery(con, query)
res <- tibble::as_tibble(res)
res
}
#' Get first 10 rows of table on SQLite database
#'
#' @md
#' @param tbl_name name of the table
#' @param n number of limits
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_glance <- function (tbl_name, n = 10,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
if (n == "all") {
res <- RSQLite::dbGetQuery(con, sprintf("select * from %s", tbl_name))
} else {
res <- RSQLite::dbGetQuery(con, sprintf("select * from %s limit %s",
tbl_name, n))
}
res <- tibble::as_tibble(res)
res
}
#' Show tables on SQLite database
#'
#' @md
#' @param pattern regex pattern
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_table <- function (pattern = NULL,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
res <- RSQLite::dbListTables(con)
if (!is.null(pattern)) {
res <- res[grepl(pattern, res)]
}
res
}
#' Write data into table on SQLite database
#'
#' @md
#' @param df data.frame to insert
#' @param tbl_name name of the table
#' @param append TRUE
#' @param overwrite FALSE
#' @param date TRUE date convert to character
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_write <- function (df, tbl_name, append = TRUE, overwrite = FALSE, date = TRUE,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
if (date == TRUE) {
inx <- sapply(df, function(x) inherits(x, "Date") || inherits(x, "POSIXt"))
df[inx] <- lapply(df[inx], as.character)
}
RSQLite::dbWriteTable(con, tbl_name, df,
row.names = FALSE,
append = append,
overwrite = overwrite)
return(TRUE)
}
#' Drop table on SQLite database
#'
#' @md
#' @param tbl_name name of the table
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_drop <- function (tbl_name, force = FALSE,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
if (!grepl("_", tbl_name) | force == TRUE) {
RSQLite::dbRemoveTable(con, tbl_name)
message(sprintf("%s is removed", tbl_name))
} else {
warning(sprintf("%s is not removed, use `force = TRUE`", tbl_name))
}
}
#' Execute query on SQLite database
#'
#' @md
#' @param query query statement
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_exec <- function (query,
path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
RSQLite::dbSendQuery(con, query)
}
#' Disconnect from SQLite database
#'
#' @md
#' @param path folder path of the database
#' @param db_name name of the database
#'
#' @export
sqlite_dc <- function (path = getOption("mrsq_db_dir"),
db_name = getOption("mrsq_db_name")) {
db_path <- sprintf("%s/%s", path, db_name)
con <- RSQLite::dbConnect(RSQLite::SQLite(), db_path)
RSQLite::dbDisconnect(con)
return(TRUE)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.