-- Example queries to extract subsets of data from DrugCentral
-- Requires an local instance of DrugCentral loaded into a PostgreSQL database
-- to load the DrugCentral database dump assuming PostgreSQL is up and running and user has admin privileges, run in PostgreSQL 
-- #create database drugcentral;
-- and then run using the OS shell
-- $gunzip -c drugcentral.dump.06212018.sql.gz | psql drugcentral

-- select all FDA approved drugs including discontinued and withdrawn
select structures.* from structures,approval where structures.id = approval.struct_id and approval.type = 'FDA';

-- select all FDA approved drugs with 1 or more currently marketed formulation
select structures.* from structures where no_formulations > 0;

-- select drug - target complex PDB ids for mechanism of action targets only
select pdb.struct_id,structures.name,pdb.pdb, pdb.chain_id,pdb.ligand_id,pdb.accession,
      target_component.gene
from pdb,structures,act_table_full,td2tc,target_component where
  pdb.struct_id = structures.id and
  pdb.struct_id = act_table_full.struct_id
  and act_table_full.moa = 1
  and act_table_full.target_id = td2tc.target_id
  and td2tc.component_id = target_component.id
  and target_component.accession = pdb.accession;
  
-- select mechanism of action targets for human targets only
select struct_id,structures.name,target_name,target_class,accession,gene,swissprot,act_value,
	act_type,act_type,act_source,act_source_url,moa_source,moa_source_url from act_table_full,structures where
  	act_table_full.struct_id = structures.id
	and moa = 1 and organism = 'Homo sapiens';


-- select all pharmaceutical formulations containing acetylsalicylic acid (aspirin) from FDA Orange Book
select ob_product.* from ob_product,struct2obprod,structures where
    structures.name = 'acetylsalicylic acid' and structures.id = struct2obprod.struct_id
    and struct2obprod.prod_id = ob_product.id;
    
-- select all significant adverse events associated with Atorvastatin from FDA FAERS database based on likelihood ratio test
select meddra_name,meddra_code,llr,llr_threshold from faers,structures
        where structures.id = faers.struct_id and structures.name = 'atorvastatin'
        and llr > llr_threshold order by llr desc;