,
Joe Thorley [aut] ,
Evan Amies-Galonski [aut, cre]
,
Poisson Consulting [cph, fnd]| Title: | Read and Write Files to SQLite Databases |
|---|---|
| Description: | Reads and writes files to SQLite databases <https://www.sqlite.org/index.html> as flobs (a flob is a blob that preserves the file extension). |
| Authors: | Sebastian Dalgarno [aut] ,
Joe Thorley [aut] ,
Evan Amies-Galonski [aut, cre]
,
Poisson Consulting [cph, fnd] |
| Maintainer: | Evan Amies-Galonski <[email protected]> |
| License: | MIT + file LICENSE |
| Version: | 0.2.2 |
| Built: | 2024-09-16 03:01:35 UTC |
| Source: | https://github.com/poissonconsulting/dbflobr |
Add named empty blob column to SQLite database
add_blob_column(column_name, table_name, conn)add_blob_column(column_name, table_name, conn)
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
conn |
A SQLite connection object. |
Modified SQLite database.
conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) DBI::dbReadTable(conn, "Table1") add_blob_column("BlobColumn", "Table1", conn) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) DBI::dbReadTable(conn, "Table1") add_blob_column("BlobColumn", "Table1", conn) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)
Delete a flob from a SQLite database.
delete_flob(column_name, table_name, key, conn)delete_flob(column_name, table_name, key, conn)
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
key |
A data.frame whose columns and values are used to filter the
table to a single row (this in combination with the |
conn |
A SQLite connection object. |
An invisible copy of the deleted flob.
flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) DBI::dbReadTable(conn, "Table1") delete_flob("BlobColumn", "Table1", key, conn) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) DBI::dbReadTable(conn, "Table1") delete_flob("BlobColumn", "Table1", key, conn) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)
Import flobs to SQLite database from directory.
Table and column names are matched to directory names within main directory.
Values in file names are matched to table primary key to determine where to write flob.
import_all_flobs( conn, dir = ".", sep = "_-_", pattern = ".*", sub = FALSE, exists = FALSE, replace = FALSE )import_all_flobs( conn, dir = ".", sep = "_-_", pattern = ".*", sub = FALSE, exists = FALSE, replace = FALSE )
conn |
A SQLite connection object. |
dir |
A string of the path to the directory to import the files from. Files need to be within nested folders like 'table1/column1/a.csv'. This structure is created automatically if save_all_flobs() function is used. |
sep |
A string of the separator between values in file names. |
pattern |
A regular expression specifying the pattern file names must match. |
sub |
A logical scalar specifying whether to import flobs based on their filename (sub = FALSE) or the name of their subdirectory (sub = TRUE) which must only contain 1 file. If sub = NA and replace = TRUE then the names of the subdirectories are used irrespective of whether they include files and existing flobs are deleted if the corresponding subdirectory is empty. If sub = TRUE or sub = NA then recursion is just one subfolder deep. |
exists |
A logical scalar specifying whether the column must (TRUE) or mustn't (FALSE) already exist or whether it doesn't matter (NA). IF FALSE, a new BLOB column is created. |
replace |
A flag indicating whether to replace existing flobs (TRUE) or not (FALSE). |
An invisible named list indicating directory path, file names and whether files were successfully written to database.
conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (CharColumn TEXT PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(CharColumn = c("a", "b")), append = TRUE) flob <- flobr::flob_obj write_flob(flob, "BlobColumn", "Table1", data.frame(CharColumn = "a"), conn) dir <- file.path(tempdir(), "import_all") save_all_flobs(conn = conn, dir = dir) import_all_flobs(conn, dir, exists = TRUE, replace = TRUE) DBI::dbDisconnect(conn)conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (CharColumn TEXT PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(CharColumn = c("a", "b")), append = TRUE) flob <- flobr::flob_obj write_flob(flob, "BlobColumn", "Table1", data.frame(CharColumn = "a"), conn) dir <- file.path(tempdir(), "import_all") save_all_flobs(conn = conn, dir = dir) import_all_flobs(conn, dir, exists = TRUE, replace = TRUE) DBI::dbDisconnect(conn)
Import flobs to SQLite database column from directory.
Values in file name are matched to table primary key to determine where to write flob.
import_flobs( column_name, table_name, conn, dir = ".", sep = "_-_", pattern = ".*", sub = FALSE, exists = FALSE, recursive = FALSE, replace = FALSE )import_flobs( column_name, table_name, conn, dir = ".", sep = "_-_", pattern = ".*", sub = FALSE, exists = FALSE, recursive = FALSE, replace = FALSE )
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
conn |
A SQLite connection object. |
dir |
A string of the path to the directory to import files from. |
sep |
A string of the separator between values in file names. |
pattern |
A regular expression specifying the pattern file names must match. |
sub |
A logical scalar specifying whether to import flobs based on their filename (sub = FALSE) or the name of their subdirectory (sub = TRUE) which must only contain 1 file. If sub = NA and replace = TRUE then the names of the subdirectories are used irrespective of whether they include files and existing flobs are deleted if the corresponding subdirectory is empty. If sub = TRUE or sub = NA then recursion is just one subfolder deep. |
exists |
A logical scalar specifying whether the column must (TRUE) or mustn't (FALSE) already exist or whether it doesn't matter (NA). IF FALSE, a new BLOB column is created. |
recursive |
A flag indicating whether to recurse into file directory (TRUE) or not (FALSE). |
replace |
A flag indicating whether to replace existing flobs (TRUE) or not (FALSE). |
An invisible named vector indicating file name and whether the file was successfully written to database.
conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (CharColumn TEXT PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(CharColumn = c("a", "b")), append = TRUE) key <- data.frame(CharColumn = "a", stringsAsFactors = FALSE)[0,,drop = FALSE] dir <- tempdir() write.csv(key, file.path(dir, "a.csv")) import_flobs("BlobColumn", "Table1", conn, dir) DBI::dbDisconnect(conn)conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (CharColumn TEXT PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(CharColumn = c("a", "b")), append = TRUE) key <- data.frame(CharColumn = "a", stringsAsFactors = FALSE)[0,,drop = FALSE] dir <- tempdir() write.csv(key, file.path(dir, "a.csv")) import_flobs("BlobColumn", "Table1", conn, dir) DBI::dbDisconnect(conn)
Read a flob from a SQLite database.
read_flob(column_name, table_name, key, conn, slob = FALSE)read_flob(column_name, table_name, key, conn, slob = FALSE)
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
key |
A data.frame whose columns and values are used to filter the
table to a single row (this in combination with the |
conn |
A SQLite connection object. |
slob |
A logical scalar specifying whether to process as slobs (serialized blobs) instead of flobs. If NA, the function will adapt accordingly. |
A flob or blob.
flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) read_flob("BlobColumn", "Table1", key, conn) DBI::dbDisconnect(conn)flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) read_flob("BlobColumn", "Table1", key, conn) DBI::dbDisconnect(conn)
Rename flobs from a SQLite database and save to directory.
save_all_flobs( table_name = NULL, conn, dir = ".", sep = "_-_", sub = FALSE, replace = FALSE, geometry = FALSE )save_all_flobs( table_name = NULL, conn, dir = ".", sep = "_-_", sub = FALSE, replace = FALSE, geometry = FALSE )
table_name |
A vector of character strings indicating names of tables to save flobs from. By default all tables are included. |
conn |
A SQLite connection object. |
dir |
A string of the path to the directory to save the files in. |
sep |
A string of the separator used to construct file names from values. |
sub |
A logical scalar specifying whether to save all existing files in a subdirectory of the same name (sub = TRUE) or all possible files in a subdirectory of the same name (sub = NA) or not nest files within a subdirectory (sub = FALSE). |
replace |
A flag specifying whether to replace existing files. If sub = TRUE (or sub = NA) and replace = TRUE then all existing files within a subdirectory are deleted. |
geometry |
A flag specifying whether to search columns named geometry for flobs. |
An invisible named list of named vectors of the file names and new file names saved.
flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (IntColumn INTEGER PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L)), append = TRUE) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) dir <- tempdir() save_all_flobs(conn = conn, dir = dir) DBI::dbDisconnect(conn)flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (IntColumn INTEGER PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L)), append = TRUE) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) dir <- tempdir() save_all_flobs(conn = conn, dir = dir) DBI::dbDisconnect(conn)
Rename flobs from a SQLite database BLOB column and save to directory.
save_flobs( column_name, table_name, conn, dir = ".", sep = "_-_", sub = FALSE, replace = FALSE, slob_ext = NULL )save_flobs( column_name, table_name, conn, dir = ".", sep = "_-_", sub = FALSE, replace = FALSE, slob_ext = NULL )
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
conn |
A SQLite connection object. |
dir |
A string of the path to the directory to save the files in. |
sep |
A string of the separator used to construct file names from values. |
sub |
A logical scalar specifying whether to save all existing files in a subdirectory of the same name (sub = TRUE) or all possible files in a subdirectory of the same name (sub = NA) or not nest files within a subdirectory (sub = FALSE). |
replace |
A flag specifying whether to replace existing files. If sub = TRUE (or sub = NA) and replace = TRUE then all existing files within a subdirectory are deleted. |
slob_ext |
A string of the file extension to use if slobs (serialized blobs) are encountered. If slob_ext = NULL slobs will be ignored. |
An invisible named vector of the file names and new file names saved.
flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (IntColumn INTEGER PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L)), append = TRUE) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) dir <- tempdir() save_flobs("BlobColumn", "Table1", conn, dir) DBI::dbDisconnect(conn)flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbGetQuery(conn, "CREATE TABLE Table1 (IntColumn INTEGER PRIMARY KEY NOT NULL)") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L)), append = TRUE) key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) dir <- tempdir() save_flobs("BlobColumn", "Table1", conn, dir) DBI::dbDisconnect(conn)
Write a flob to a SQLite database.
write_flob(flob, column_name, table_name, key, conn, exists = NA)write_flob(flob, column_name, table_name, key, conn, exists = NA)
flob |
A flob. |
column_name |
A string of the name of the BLOB column. |
table_name |
A string of the name of the existing table. |
key |
A data.frame whose columns and values are used to filter the
table to a single row (this in combination with the |
conn |
A SQLite connection object. |
exists |
A logical scalar specifying whether the column must (TRUE) or mustn't (FALSE) already exist or whether it doesn't matter (NA). IF FALSE, a new BLOB column is created. |
An invisible copy of flob.
flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) DBI::dbReadTable(conn, "Table1") key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)flob <- flobr::flob_obj conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") DBI::dbWriteTable(conn, "Table1", data.frame(IntColumn = c(1L, 2L))) DBI::dbReadTable(conn, "Table1") key <- data.frame(IntColumn = 2L) write_flob(flob, "BlobColumn", "Table1", key, conn, exists = FALSE) DBI::dbReadTable(conn, "Table1") DBI::dbDisconnect(conn)