In chasemc/ghosts: Protein and small molecule analysis of bacteria by MALDI-TOF MS

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

defb SQLite files were designed to be accessible outside of defb through R or even other programming languages. This vignette will reveal what is inside an defb database and some examples of how to work with the data it contains (using R).

Firstly, defb handles databases using the R pool package and for that reason that is what is used here, this is simply a style choice.

Let's make a pool to an defb database using the defb function createPool()

# Get the name and the directory of the defb SQLite file

dbPath <- system.file("extdata/sqlite/sample.sqlite", package = "ghosts")
#dbPath <-"C:\\Users\\CMC\\Documents\\GitHub\\defb_App\\example.sqlite"
dbname <- tools::file_path_sans_ext(basename(dbPath))
dbPath <- dirname(dbPath)

# Make the connection
connection <- ghosts::createPool(fileName = dbname,
                                   filePath = dbPath)

defb's function createPool() returns a list of pools (connections)

class(connection)

In this case we only made one pool so we will just take the first list element.

connection <- connection[[1]]

class(connection)

And checkout the database pool

con <- pool::poolCheckout(connection)

So, that only made the connection to the database, we still haven't read anything. Let's first take a look at what SQL tables are contained within an defb database.

DBI::dbListTables(connection)

The version table contains an semantic version number of the version of defb database used.

result <- DBI::dbSendQuery("SELECT * 
                    FROM version",
                    con = con)

DBI::dbFetch(result)
DBI::dbClearResult(result)

The metaData table contains user-input information about the samples

result <- DBI::dbSendQuery("SELECT * 
                    FROM metaData",
                    con = con)

DBI::dbFetch(result)
DBI::dbClearResult(result)

The xml table contains a compressed mzML or mzXML (if mzXML was provided as input to defb) file for each sample.

result <- DBI::dbSendQuery("SELECT * 
                    FROM XML",
                    con = con)

DBI::dbFetch(result)
DBI::dbClearResult(result)

The XML table contains a number of columns:

DBI::dbListFields(con, "XML")

mzMLSHA: The SHA of the mzML file is just a short string that uniquely identifies an XML file.

The XML file is read into R and compressed using "ZSTD":

serializeXML <- function(path) {

  path <- readChar(path, nchars = file.info(path)$size, useBytes = T)
  ghosts::chartoRawtoCompressed(input = path,
                                  compression = 0)

}

And then the hash is created using xxhash64

hashR <- function(input){
  digest::digest(input, 
                 algo = "xxhash64",
                 serialize = FALSE,
                 seed = 42)
}

XML: the character result of serializeXML() mentioned above manufacturer: instrument manufacturer model: instrument model ionisation: instrument ioinization analyzer: instrument analyzer detector: instrument detector Instrument_MetaFile: instrument information file (eg. Bruker's acqu file)