Re: [Gnumed-devel] OpenSource drug database

[Sebastian Hilbert]

Am Freitag 12 Februar 2010 18:06:17 schrieb Jim Busser:
> On 2010-02-12, at 3:37 AM, Sebastian Hilbert wrote:
> > Hi all,
> > ...
> > a) does anyone know the current status of the drugref code ?
> > b) is there an interface which GNUmed can use to talk to drugref ?
> > c) is Oscar's version of GNUmed available for use with GNUmed, what does
> > it involve ?
> 
> While the online archive of oscar-devel contains related postings
> 
>       http://sourceforge.net/mailarchive/forum.php?forum_name=oscarmcmaster-
deve

This seems to be the data source for drugref2

http://www.hc-sc.gc.ca/dhp-mps/prodpharma/databasdon/

Oscar seems to  pull in data from there and fill the database table via 
Update.jsp

Information available in the database includes the following parameters:

   1. Brand Name
   2. Drug Identification Number (DIN)
   3. Company
   4. Active Ingredient(s)
   5. Route of Administration
   6. Product Monograph (PM)
   7. Pharmaceutical Form
   8. Package Sizes
   9. Therapeutic Classification (AHFS and ATC)
  10. Active Ingredient Group (AIG) Number
  11. Pharmaceutical Standard
  12. Veterinary Species

This page describes how drugref2 is set up for Oscar.

http://www.oscarmanual.org/-oscar-emr/3-0-clinical-functions/3-7-1

This site seems to indicate that drugref2 is a java webservice served by 
tomcat.

Interactions are gotten from holbrook-interactions.txt

Here comes the table structure:

CREATE TABLE  cd_drug_product  (id serial  PRIMARY KEY,drug_code  int default 
NULL,product_categorization  varchar(80) default NULL,   class  varchar(40) 
default NULL,   drug_identification_number  varchar(8) default NULL,   
brand_name  varchar(200) default NULL,   gp_flag  char(1) default NULL,   
accession_number  varchar(5) default NULL,   number_of_ais  varchar(10) 
default NULL,   last_update_date  date default NULL,ai_group_no  varchar(10) 
default NULL,company_code int);�CREATE TABLE  cd_companies  (id serial  
PRIMARY KEY,   drug_code   int default NULL,   mfr_code  varchar(5) default 
NULL,   company_code   int default NULL,   company_name  varchar(80) default 
NULL,   company_type  varchar(40) default NULL,   address_mailing_flag  
char(1) default NULL,   address_billing_flag  char(1) default NULL,   
address_notification_flag  char(1) default NULL,   address_other  varchar(20) 
default NULL,   suite_number  varchar(20) default NULL,   street_name  
varchar(80) default NULL,   city_name  varchar(60) default NULL,   province  
varchar(40) default NULL,   country  varchar(40) default NULL,   postal_code  
varchar(20) default NULL,   post_office_box  varchar(15) default NULL);�CREATE 
TABLE  cd_active_ingredients  ( id serial  PRIMARY KEY,  drug_code   int 
default NULL,   active_ingredient_code   int default NULL,   ingredient  
varchar(240) default NULL,   ingredient_supplied_ind  char(1) default NULL,   
strength  varchar(20) default NULL,   strength_unit  varchar(40) default NULL,  
 
strength_type  varchar(40) default NULL,   dosage_value  varchar(20) default 
NULL,   base  char(1) default NULL,   dosage_unit  varchar(40) default NULL,   
notes  text);�CREATE TABLE  cd_drug_status  (id serial  PRIMARY KEY,   
drug_code   int default NULL,   current_status_flag  char(1) default NULL,   
status  varchar(40) default NULL,   history_date  date default NULL);�CREATE 
TABLE  cd_form  (id serial  PRIMARY KEY,   drug_code   int default NULL,   
pharm_cd_form_code   int default NULL,   pharmaceutical_cd_form  varchar(40) 
default NULL);�CREATE TABLE  cd_inactive_products  (id serial  PRIMARY KEY,   
drug_code   int default NULL,   drug_identification_number  varchar(8) default 
NULL,   brand_name  varchar(200) default NULL,   history_date  date default 
NULL);(CREATE TABLE  cd_packaging  (id serial  PRIMARY KEY,   drug_code   int 
default NULL,   upc  varchar(12) default NULL,   package_size_unit  
varchar(40) default NULL,   package_type  varchar(40) default NULL,   
package_size  varchar(5) default NULL,   product_inforation  varchar(80) 
default NULL);�CREATE TABLE  cd_pharmaceutical_std  (id serial  PRIMARY KEY,   
drug_code   int default NULL,   pharmaceutical_std  varchar(40) default 
NULL);�CREATE TABLE  cd_route  (id serial  PRIMARY KEY,   drug_code   int 
default NULL,   route_of_administration_code   int default NULL,   
route_of_administration  varchar(40) default NULL);zCREATE TABLE  cd_schedule  
(id serial  PRIMARY KEY,   drug_code   int default NULL,   schedule  
varchar(40) default NULL);�CREATE TABLE  cd_therapeutic_class  (id serial  
PRIMARY KEY,   drug_code   int default NULL,   tc_atc_number  varchar(8) 
default NULL,   tc_atc  varchar(120) default NULL,   tc_ahfs_number  
varchar(20) default NULL,   tc_ahfs  varchar(80) default NULL);�CREATE TABLE  
cd_veterinary_species  (id serial  PRIMARY KEY,   drug_code   int default 
NULL,   vet_species  varchar(80) default NULL,   vet_sub_species  varchar(80) 
default NULL);sCREATE TABLE  interactions  (id serial PRIMARY KEY, 
affectingatc varchar(7), affectedatc varchar(7) default NULL, effect char(1) 
default NULL, significance char(1) default NULL, evidence char(1) default 
NULL, comment text default NULL, affectingdrug text default NULL, affecteddrug 
text default NULL, CONSTRAINT UNQ_ATC_EFFECT UNIQUE (affectingatc, 
affectedatc, effect));Xcreate index  cd_active_ingredients_drug_code_idx on   
cd_active_ingredients(drug_code);Jcreate index  cd_drug_status_drug_code_idx 
on   cd_drug_status(drug_code);=create index  cd_form_drug_code_idx on    
cd_form(drug_code);Wcreate index  cd_inactive_products_drug_code_idx on    
cd_inactive_products(drug_code);Fcreate index  cd_packaging_drug_code_idx on   
cd_packaging(drug_code);Xcreate index  cd_pharmaceutical_std_drug_code_idx on   
cd_pharmaceutical_std(drug_code);@create index  cd_route_drug_code_idx on     
cd_route(drug_code);Fcreate index  cd_schedule_drug_code_idx on     
cd_schedule(drug_code);Xcreate index  cd_therapeutic_class_drug_code_idx on     
cd_therapeutic_class(drug_code);Zcreate index  
cd_veterinary_species_drug_code_idx on     
cd_veterinary_species(drug_code);Acreate index cd_company_drug_code_idx on 
cd_companies(drug_code);<create index cd_drug_code_idx on 
cd_drug_product(drug_code);�update cd_drug_product set company_code=(select 
company_code from cd_companies where cd_companies.drug_code =  
cd_drug_product.drug_code);cd_drug_searchlink_generic_brand~CREATE TABLE  
cd_drug_search  (id serial  PRIMARY KEY,   drug_code  varchar(30),   category   
int,   name  text default NULL);eCREATE TABLE  link_generic_brand (pk_id 
serial  PRIMARY KEY,   id integer,    drug_code varchar(30));


http://www.hc-sc.gc.ca/dhp-mps/alt_formats/hpfb-
dgpsa/txt/prodpharma/dpdreadme_bdpplisezmoi-eng.txt

seems to describe how to make use of the data and

http://www.hc-sc.gc.ca/dhp-mps/prodpharma/databasdon/dpd_bdpp_data_extract-
eng.php

seems to be the page to get the data from.

Sebastian