Import flobs.

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
)

Arguments

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).

Value

An invisible named vector indicating file name and whether the file was successfully written to database.

Examples

conn <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
DBI::dbGetQuery(conn, "CREATE TABLE Table1 (CharColumn TEXT PRIMARY KEY NOT NULL)")
#> Warning: SQL statements must be issued with dbExecute() or dbSendStatement() instead of dbGetQuery() or dbSendQuery().
#> data frame with 0 columns and 0 rows
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)
#> Writing files to database
#> ✔ File 1: a.csv written to database
#> ✖ File 2: can't write filefd913aeced0 to database
#> ✖ File 3: can't write filefd9398bc3c8 to database
#> ✖ File 4: can't write filefd940c5d3e2 to database
DBI::dbDisconnect(conn)