knitr::opts_chunk$set( collapse = TRUE, comment = "#>", fig.path = "README-" )
An R package to facilitate working with SQLite databases.
The ps_connect_sqlite()
function can be used to connect to an sqlite3 database.
By default it switches foreign keys on.
The connection can be queried using the functions in the DBI
package (which is automatically loaded).
library(poissqlite) conn <- ps_connect_sqlite(dir = tempdir(), new = TRUE) print(class(conn))
Files and R objects can be added to SQLite databases as storage type BLOB.
The ps_blob_file()
function can be used to convert a file to a blob
while ps_deblob_file()
performs the inverse operation, i.e., saves a blob to a file of the original format. ps_blob_files()
and ps_deblob_files()
are the equivalent functions for working with multiple files.
In addition, the ps_blob_object()
and ps_deblob_object()
can be used to convert between an R object and a blob. This is achieved by reading or writing the object as an .rds
file which means that ps_deblob_file()
applied to a blob created using ps_blob_object()
produces the intermediate .rds
file.
It's important to realize that the ps_blob
family of functions embed the original file extension in the raw data to ensure the converted files are the original format. A consequence is that the ps_deblob
family of functions will only work on blobs created using the ps_blob
functions.
print(head(datasets::cars)) write.csv(datasets::cars, file.path(tempdir(), "cars.csv"), row.names = FALSE) dir.create(file.path(tempdir(), "sub")) write.csv(datasets::chickwts, file.path(tempdir(), "sub/chickwts.csv"), row.names = FALSE) blobs <- ps_blob_files(tempdir(), pattern = "[.]csv$", recursive = TRUE) print(blobs) blob_data <- data.frame(File = names(blobs), BLOB = blobs) dbWriteTable(conn, "blob_table", blob_data) blob_data_new <- dbReadTable(conn, "blob_table") blobs <- blob_data_new$BLOB names(blobs) <- blob_data_new$File dir.create(file.path(tempdir(), "new")) ps_deblob_files(blobs, dir = file.path(tempdir(), "new"), ask = FALSE) cars_new <- read.csv(file.path(tempdir(), "new", "cars.csv")) print(head(cars_new))
metadata <- ps_update_metadata(conn) print(metadata)
dbDisconnect(conn)
To install from GitHub
install.packages("devtools") devtools::install_github("poissonconsulting/poissqlite")
Please report any issues.
Pull requests are always welcome.
Please note that the poissqlite project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.