createCompDb: Create a CompDb database

View source: R/createCompDbPackage.R

createCompDbR Documentation

Create a CompDb database


CompDb databases can be created with the createCompDb or the emptyCompDb functions, the former creating and initializing (filling) the database with existing data, the latter creating an empty database that can be subsequently filled with insertCompound() or insertSpectra() calls.

emptyCompDb requires only the file name of the database that should be created as input and returns a CompDb representing the empty database.

createCompDb creates a SQLite-based CompDb object/database from a compound resource provided as a data.frame or tbl. Alternatively, the name(s) of the file(s) from which the annotation should be extracted can be provided. Supported are SDF files (such as those from the Human Metabolome Database HMDB) that can be read using the compound_tbl_sdf() or LipidBlast files (see compound_tbl_lipidblast().

An additional data.frame providing metadata information including the data source, date, version and organism is mandatory. By default, the function will define the name of the database based on the provided metadata, but it is also possible to define this manually with the dbFile parameter.

Optionally MS/MS (MS2) spectra for compounds can be also stored in the database. Currently only MS/MS spectra from HMDB are supported. These can be downloaded in XML format from HMDB (, loaded with the msms_spectra_hmdb() or msms_spectra_mona() function and passed to the function with the msms_spectra argument. See msms_spectra_hmdb() or msms_spectra_mona() for information on the expected columns and format.

Required columns for the data.frame providing the compound information ( parameter x) are:

  • "compound_id": the ID of the compound. Can be an integer or character. Duplicated IDs are supported (for compatibility reasons), but not suggested. No missing values allowed.

  • "name": the compound's name.

  • "inchi": the InChI of the compound.

  • "inchikey": the InChI key.

  • "formula": the chemical formula.

  • "exactmass": the compound's (exact) mass.

  • "synonyms": additional synonyms/aliases for the compound. Should be either a single character or a list of values for each compound.

Any additional columns in the provided data.frame (such as e.g. "smiles" providing the compound's SMILES) are also supported and will be inserted into the database table.

See e.g. compound_tbl_sdf() or compound_tbl_lipidblast() for functions creating such compound tables.

The table containing the MS2 spectra data should have the following format and columns:

  • "spectrum_id": an arbitrary ID for the spectrum. Has to be an integer.

  • "compound_id": the ID of the compound to which the spectrum can be associated with. This has to be present in the data.frame defining the compounds.

  • "polarity": the polarity (as an integer, 0 for negative, 1 for positive, NA for not set).

  • "collision_energy": the collision energy.

  • "predicted": whether the spectrum was predicted or measured.

  • "splash": the SPLASH of the spectrum.

  • "instrument_type": the instrument type.

  • "instrument": the name of the instrument.

  • "precursor_mz": the precursor m/z (as a numeric).

  • "mz": the m/z values.

  • "intensity": the intensity values.

Only for columns "spectrum_id", "compound_id", "mz" and "intensity" a value has to be provided in each row of the data.frame. The others are optional. Note that the data.frame can be either in the format as in the example below (i.e. each row being one spectrum and columns "mz" and "intensity" being of type list each element being the m/z or intensity values of one spectrum) or in a full form, in which each row represents one peak and all columns except "mz" and "intensity" containing redundant information of each spectrum (hence columns "mz" and "intensity" being of type numeric).

The metadata data.frame is supposed to have two columns named "name" and "value" providing the following minimal information as key-value pairs (see make_metadata for a utility function to create such a data.frame):

  • "source": the source from which the data was retrieved (e.g. "HMDB").

  • "url": the url from which the original data was retrieved.

  • "source_version": the version from the original data source (e.g. "v4").

  • "source_date": the date when the original data source was generated.

  • "organism": the organism. Should be in the form "Hsapiens" or "Mmusculus".

createCompDbPackage creates an R data package with the data from a CompDb object.

make_metadata helps generating a metadata data.frame in the correct format expected by the createCompDb function. The function returns a data.frame.


createCompDb(x, metadata, msms_spectra, path = ".", dbFile = character())

  path = ".",
  license = "Artistic-2.0"

  source = character(),
  url = character(),
  source_version = character(),
  source_date = character(),
  organism = NA_character_

emptyCompDb(dbFile = character())



For createCompDb: data.frame or tbl with the compound annotations or character with the file name(s) from which the compound annotations should be retrieved. See description for details.

For `createCompDbPackage`: `character(1)` with the file name of the
`CompDb` SQLite file (created by `createCompDb`).

For createCompDb: data.frame with metadata information. See description for details.


For createCompDb: data.frame with MS/MS spectrum data. See msms_spectra_hmdb() for the expected format and a function to import such data from spectrum xml files from HMDB.


character(1) with the path to the directory where the database file or package folder should be written. Defaults to the current directory.


character(1) to optionally provide the name of the SQLite database file. If not provided (the default) the database name is defined using information from the provided metadata.


For createCompDbPackage: character(1) with the version of the package (ideally in the format "x.y.z").


For createCompDbPackage: character(1) with the name and email address of the package maintainer (in the form "First Last <>".


For createCompDbPackage: character(1) with the name of the package author.


For createCompDbPackage: character(1) with the license of the package respectively the originating provider.


For make_metadata: character(1) with the name of the resource that provided the compound annotation.


For make_metadata: character(1) with the url to the original resource.


For make_metadata: character(1) with the version of the original resource providing the annotation.


For make_metadata: character(1) with the date of the resource's release.


For make_metadata: character(1) with the name of the organism. This should be in the format "Hsapiens" for human, "Mmusculus" for mouse etc. Leave to NA if not applicable.


Metadata information is also used to create the file name for the database file. The name starts with "CompDb", followed by the organism, the data source and its version. A compound database file for HMDB version 4 with human metabolites will thus be named: "CompDb.Hsapiens.HMDB.v4".

A single CompDb database is created from multiple SDF files (e.g. for PubChem) if all the file names are provided with parameter x. Parallel processing is currently not enabled because SQLite does not support it yet natively.


For createCompDb: a character(1) with the database name (invisibly).


Johannes Rainer

See Also

compound_tbl_sdf() and compound_tbl_lipidblast() for functions to extract compound annotations from files in SDF format, or files from LipidBlast.

import_mona_sdf() to import both the compound and spectrum data from a SDF file from MoNa (Massbank of North America) in one call.

msms_spectra_hmdb() and msms_spectra_mona() for functions to import MS/MS spectrum data from xml files from HMDB or an SDF file from MoNa.

CompDb() for how to use a compound database.


## Read compounds for a HMDB subset
fl <- system.file("sdf/HMDB_sub.sdf.gz", package = "CompoundDb")
cmps <- compound_tbl_sdf(fl)

## Create a metadata data.frame for the compounds.
metad <- data.frame(name = c("source", "url", "source_version",
    "source_date", "organism"), value = c("HMDB", "",
    "v4", "2017-08-27", "Hsapiens"))

## Alternatively use the make_metadata helper function
metad <- make_metadata(source = "HMDB", source_version = "v4",
    source_date = "2017-08", organism = "Hsapiens",
    url = "")
## Create a SQLite database in the temporary folder
db_f <- createCompDb(cmps, metadata = metad, path = tempdir())

## The database can be loaded and accessed with a CompDb object
db <- CompDb(db_f)

## Create a database for HMDB that includes also MS/MS spectrum data
metad2 <- make_metadata(source = "HMDB_with_spectra", source_version = "v4",
    source_date = "2017-08", organism = "Hsapiens",
    url = "")

## Import spectrum information from selected MS/MS xml files from HMDB
## that are provided in the package
xml_path <- system.file("xml", package = "CompoundDb")
spctra <- msms_spectra_hmdb(xml_path)

## Create a SQLite database in the temporary folder
db_f2 <- createCompDb(cmps, metadata = metad2, msms_spectra = spctra,
    path = tempdir())

## The database can be loaded and accessed with a CompDb object
db2 <- CompDb(db_f2)

## Does the database contain MS/MS spectrum data?

## Create a database for a ChEBI subset providing the file name of the
## corresponding SDF file
metad <- make_metadata(source = "ChEBI_sub", source_version = "2",
    source_date = NA, organism = "Hsapiens", url = "")
db_f <- createCompDb(system.file("sdf/ChEBI_sub.sdf.gz",
    package = "CompoundDb"), metadata = metad, path = tempdir())
db <- CompDb(db_f)


## connect to the database and query it's tables using RSQlite
con <- dbConnect(dbDriver("SQLite"), db_f)

dbGetQuery(con, "select * from metadata")
dbGetQuery(con, "select * from ms_compound")

## To create a CompDb R-package we could simply use the
## createCompDbPackage function on the SQLite database file name.

EuracBiomedicalResearch/CompoundDb documentation built on March 17, 2023, 3:47 p.m.