[Gnumed-devel] Getting Canadian drug data into FreeDiams

[Jim Busser]

Having explored the FreeDiams database via Firefox plugin "SQLite Manager", 
this confirms that FreeDiams has been normalized (likely around the French 
data) differently than the form in which the Canadian data is available.

The first thing I need(ed) to do was to alter the table structure of the 
FreeDiams `CIS` table because it contains columns that are not even clearly 
used yet by FreeDiams despite which they are declared NON NULL.

The problem for me is that whereas France may provide "currently useless" data 
to fill these values, Canada does not. Therefore I removed this "NON NULL" 
constraint.

There are a few other clarifications I will await Eric to separately respond 
about FreeDiams table structures and field usage, even before we would look at 
ATC.

However, I am still left with resolving how to direct relational data that 
originates from four Canadian text files (drug, form, route, status) into 
single rows of a FreeDiams CIS table.

If I

- leave the FreeDiams tables inside the SQLite database "main"
- create a new SQlite database "temp"
- insert into temp.staging tables the Canadian data

how might I best insert into a main.table data from four temp. tables?

These do all share a value DRUG_CODE so my question is whether something like 
the following is supposed to be able to work and, if the Firefox plugin kept 
choking, maybe doing it from some sqlite command line interface?

INSERT INTO CIS (CIS, DENOMINATION, FORME, ADMINISTRATION, COMMERCIALISATION)
SELECT A1.DRUG_CODE, A1.BRAND_NAME, A2.PHARMACEUTICAL_FORM, 
A3.ROUTE_OF_ADMINISTRATION, A4.CURRENT_STATUS_FLAG
FROM temp.drug A1, temp.form A2, temp.route A3, temp.status A4
WHERE A1.DRUG_CODE = A2.DRUG_CODE = A3.DRUG_CODE = A4.DRUG_CODE;

-- do the tables need to first be indexed on the key?

-- Jim