(At this writing this update project is in progress; hence the workflow is not in final form. -JJYang)

  • ChEMBL v15 was released in Jan. 2013. Thus we will skip ahead and update directly to v15.
  • ChEMBL now (since v14) available as a PostgreSQL database, downloaded as a pg_dump file. This simplifies and improves importation to the c2b2r database. So the first step is to download the pg_dump file, chembl_15.pgdump.sql.gz, from ftp://ftp.ebi.ac.uk/pub/databases/chembl/ChEMBLdb/releases/chembl_15.
  • For testing and validation purposes we excerpt a sample of the dump file using a custom program pgdump_sample.py which copies all the schema but only a sample of each data (COPY) block.
  • This pgdump file needs table names and other names modified to comply with chem2bio2rdf conventions. All must be prefixed "chembl_15". This is accomplished with some perl commands as follows:
$ gunzip -c chembl_15_pgdump.sql.gz \
| perl -pe 's/CREATE\s+TABLE\s+([^\s]+)\s/CREATE TABLE chembl_15_$1 /' \
| perl -pe 's/COPY\s+([^\s]+)\s/COPY chembl_15_$1 /' \
| perl -pe 's/ALTER\s+TABLE\s+ONLY\s+([^\s]+)\s/ALTER TABLE ONLY chembl_15_$1 /' \
| perl -pe 's/CREATE\s+INDEX\s+([^\s]+)\s+ON\s+([^\s]+)\s/CREATE INDEX chembl_15_$1 ON chembl_15_$2 /' \
| perl -pe 's/CREATE\s+UNIQUE\s+INDEX\s+([^\s]+)\s+ON\s+([^\s]+)\s/CREATE UNIQUE INDEX chembl_15_$1 ON chembl_15_$2 /' \
> chembl_15_pgdump_fixed.sql
  • The resulting file can be loaded into the database using psql. Log messages to a file in case there are errors and for future reference.
$ cat chembl_15_pgdump_fixed.sql | psql -U ***** chord >& chembl_15_load.log
  • Now we can verify the data is loaded.
$ psql -U ***** chord
Welcome to psql 8.2.17, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
chord=> select table_name from information_schema.tables where table_schema='public' and table_name ilike 'chembl_14%' order by table_name ;
table_name
-----------------------------------
 chembl_15_activities
 chembl_15_activity_stds_lookup
 chembl_15_assays
 chembl_15_assay_type
 chembl_15_atc_classification
 chembl_15_binding_sites
 chembl_15_bio_component_sequences
 chembl_15_biotherapeutic_components
 chembl_15_biotherapeutics
 chembl_15_cell_dictionary
 chembl_15_chembl_id_lookup
 chembl_15_component_class
 chembl_15_component_domains
 chembl_15_component_sequences
 chembl_15_component_synonyms
 chembl_15_compound_properties
 chembl_15_compound_records
 chembl_15_compound_structures
 chembl_15_confidence_score_lookup
 chembl_15_curation_lookup
 chembl_15_data_validity_lookup
 chembl_15_defined_daily_dose
 chembl_15_docs
 chembl_15_domains
 chembl_15_formulations
 chembl_15_ligand_eff
 chembl_15_molecule_dictionary
 chembl_15_molecule_hierarchy
 chembl_15_molecule_synonyms
 chembl_15_organism_class
 chembl_15_predicted_binding_domains
 chembl_15_products
 chembl_15_protein_family_classification
 chembl_15_relationship_type
 chembl_15_research_companies
 chembl_15_research_stem
 chembl_15_site_components
 chembl_15_source
 chembl_15_target_components
 chembl_15_target_dictionary
 chembl_15_target_type
 chembl_15_usan_stems
 chembl_15_version
(43 rows)
 
 
chord=> \d+ public.chembl_15_compound_structures
              Table "public.chembl_15_compound_structures"
       Column       |          Type           | Modifiers | Description
--------------------+-------------------------+-----------+-------------
 molregno           | numeric(9,0)            | not null  |
 molfile            | text                    |           |
 standard_inchi     | character varying(4000) |           |
 standard_inchi_key | character varying(27)   | not null  |
 canonical_smiles   | character varying(4000) |           |
 molformula         | character varying(100)  |           |
Indexes:
    "chembl_15_compound_structures_pk" UNIQUE, btree (molregno)
    "uk_cmpdstr_stdinchkey" UNIQUE, btree (standard_inchi_key)
    "chembl_15_idx_cmpdstr_smiles" btree (canonical_smiles)
    "chembl_15_idx_cmpdstr_stdkey" btree (standard_inchi_key)
Has OIDs: no
  • We can automate SQL for describing the database.
gunzip -c chembl_15_pgdump_fixed.sql.gz \
| perl -ne 'print if /^\s*CREATE\s+TABLE\s+([^\s]+)\s/' \
| perl -pe 's/^\s*CREATE\s+TABLE\s+([^\s]+)\s.*$/\\d $1\nSELECT COUNT(*) AS "COUNT($1)" FROM $1 ;/' \
>describe_chembl_15.sql
  • And for dropping the tables when needed.
gunzip -c chembl_15_pgdump_fixed.sql.gz \
| perl -ne 'print if /^\s*CREATE\s+TABLE\s+([^\s]+)\s/' \
| perl -pe 's/^\s*CREATE\s+TABLE\s+([^\s]+)\s.*$/DROP TABLE $1 ;/' \
>drop_chembl_15.sql

  • In order to associate (join) chembl_15 compounds with others in Chem2Bio2RDF, the PubChem CID must be found for each compound. Then the database can be queried by joining with global table c2b2r_compound. The CID can be found in several ways. The c2b2r_compounds table itself can be used if the compound exists. This important table is as follows:
chord=> \d public.c2b2r_compound
Table "public.c2b2r_compound"
Column | Type | Modifiers
--------------------+-------------------+-----------
CID | integer | not null
bio2rdf_URI | character varying |
pubchem_URL | character varying |
openeye_can_smiles | text |
openeye_iso_smiles | text |
std_inchi | text |
Indexes:
"compound_pkey" PRIMARY KEY, btree ("CID")
The chembl_15_compound_records table contains compound IDs within ChEMBL_15. There are various types of IDs for various sources:
 src_id |                     src_description                     |  src_short_name  | cpd_count
--------+---------------------------------------------------------+------------------+-----------
      1 | Scientific Literature                                   | LITERATURE       |    897374
      2 | GSK Malaria Screening                                   | GSK_TCMDC        |     13533
      3 | Novartis Malaria Screening                              | NOVARTIS         |     10119
      4 | St Jude Malaria Screening                               | ST_JUDE          |      1524
      5 | Sanger Institute Genomics of Drug Sensitivity in Cancer | SANGER           |        17
      7 | PubChem BioAssays                                       | PUBCHEM_BIOASSAY |    482196
      8 | Clinical Candidates                                     | CANDIDATES       |       676
      9 | Orange Book                                             | ORANGE_BOOK      |      2003
     10 | Guide to Receptors and Channels                         | GRAC             |       570
     11 | Open TG-GATEs                                           | TG_GATES         |       524
     12 | Manually Added Drugs                                    | DRUGS            |       114
     13 | USP Dictionary of USAN and International Drug Names     | USP/USAN         |     10568
     14 | Drugs for Neglected Diseases Initiative (DNDi)          | DNDI             |      6820
     15 | DrugMatrix in vitro pharmacology assays                 | DRUGMATRIX       |       871
     16 | GSK Published Kinase Inhibitor Set                      | GSK_PKIS         |       734
     17 | MMV Malaria Box                                         | MMV_MBOX         |       799
     18 | TP-search Transporter Database                          | TP_TRANSPORTER   |      4383
     19 | Harvard Malaria Screening                               | HARVARD          |        37
     20 | WHO-TDR Malaria Screening                               | WHO_TDR          |       740
     21 | Deposited Supplementary Data                            | SUPPLEMENTARY    |        54
     22 | GSK Tuberculosis Screening                              | GSK_TB           |       776
(21 rows)