create_metadata_csv.R

#!/usr/bin/env Rscript
if (!require("remotes")) {
    install.packages("remotes")
    library(remotes)
}
if (!require("DBI")) {
    install.packages("DBI")
    library(DBI)
}
if (!require("RSQLite")) {
    install.packages("RSQLite")
    library(RSQLite)
}

args = commandArgs(trailingOnly=TRUE)
if (length(args) < 2) {
    stop("missing srgument(s).n", call.=FALSE)
} 

sqlite_path <- args[1]
outpath <- args[2]

con <- DBI::dbConnect(RSQLite::SQLite(), sqlite_path)
message(paste0("Connected to: ", sqlite_path))


df <- DBI::dbGetQuery(con, "SELECT * FROM VERSION")
if (df$IDBacVersion != "1.1.10") {
    stop("Different, database version, let Chase know")
}
    
df <- DBI::dbGetQuery(con, "SELECT * FROM metaData")
message(paste0("Read metadata from: ", sqlite_path))
write.csv(df, outpath, row.names = F)
message(paste0("Wrote to: ", outpath))
message("Finished")

modify_database.R

#!/usr/bin/env Rscript
if (!require("remotes")) {
    install.packages("remotes")
    library(remotes)
}
if (!require("DBI")) {
    install.packages("DBI")
    library(DBI)
}
if (!require("RSQLite")) {
    install.packages("RSQLite")
    library(RSQLite)
}

args = commandArgs(trailingOnly=TRUE)
if (length(args) < 2) {
    stop("missing srgument(s).", call.=FALSE)
} 

headers <- c("Strain_ID","Genbank_Accession","NCBI_TaxID","Kingdom","Phylum","Class","Order","Family","Genus","Species","MALDI_Matrix","DSM_Agar_Media","Cultivation_Temp_Celsius","Cultivation_Time_Days","Cultivation_Other","User","User_ORCID","PI_FirstName_LastName","PI_ORCID","dna_16S")

sqlite_path <- args[1]
csvpath <- args[2]

message(paste0("Reading input csv file: ", sqlite_path))
input_csv <- read.csv(csvpath, header = TRUE, sep = ",")
if (!identical(colnames(input_csv), headers)) {
    stop("Unexpected column names")
}
con <- DBI::dbConnect(RSQLite::SQLite(), sqlite_path)
message(paste0("Connected to: ", sqlite_path))

df <- DBI::dbGetQuery(con, "SELECT * FROM VERSION")
if (df$IDBacVersion != "1.1.10") {
    stop("Different, database version, let Chase know")
}
message("Overwriting old metadata table")
dbWriteTable(con, "metaData", input_csv, overwrite=TRUE)
message("Finished")