Re: [Gnumed-devel] Getting Canadian drug data into FreeDiams

[Karsten Hilbert]

On Thu, Feb 18, 2010 at 01:55:44AM -0800, Jim Busser wrote:

> 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.

Nullability is a property of the data, not of the use
FreeDiams makes thereof.

> 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.

Such a need can be a hint towards needed factorization.

> 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.

The Canadian data is more normalized. One either needs to
denormalize that into the FreeDiams table(s) or else
normalize the Canadian data more.

However, the truth is that each country will provide
slightly different data and that some data won't be easily
reconcilable into a single unified structure. That was
exactly the concept of DrugRef: unify where possible but
then use data source specific drivers to access auxiliary
data (residing in their very own tables).

> 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?

Technically by INSERT INTO ... SELECT ... FROM ...

Conceptually it needs to be decided what goes where.

> 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
                and
        A1.DRUG_CODE = A3.DRUG_CODE
                and
        A1.DRUG_CODE = A4.DRUG_CODE
;

The exact syntax depends on what subset of SQL the sqlite
database engine supports but the above is likely to work on
PostgreSQL and is very likely very close to what SQLITE
needs.

Considerations:

- does SQLITE support schemata ?  (temp.*)
- does SQLITE support the SELECT clause for the VALUES part of the INSERT ?
- does temp.status.CURRENT_STATUS_FLAG need to be mapped ?

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

No. It would add speed but is not at all necessary
relationally.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346