Canadian db scripts

[Jim Busser]

Hi Eric,

I am now getting very confused since, while I thought I had the script solved, I see that you split some parts into a new file

canadian_db_processing.sql

I see that it reverts what I previously contributed (my method was to add a second key "DIN" to the Canadian tables) even though it seemed you had solved that.

I also see that the COMPOSITION which I packaged into 0.5.1 contains too many records (~41,000 instead of ~21,000)

Here is what I would like to do:

1) purge

canadian_db_processing

2) put back in what you had previously contributed as

canadian_db_finalize

except I would include a constraint (to avoid inserting the ingredients of no-longer available drugs)

3) I would like to rename

canadian_db_preparation

to one of

canadian_db_schema
canadian_db_tables

because preparation is too similar to creation (and will then adjust the line in canadian_db_creator)

Are we ok for me to do this?

-- Jim

[Eric MAEKER]

Le 12 déc. 2010 à 22:50, Jim Busser a écrit :

> Hi Eric,
>
> I am now getting very confused since, while I thought I had the script solved, I see that you split some parts into a new file
>
> canadian_db_processing.sql
>
> I see that it reverts what I previously contributed (my method was to add a second key "DIN" to the Canadian tables) even though it seemed you had solved that.
>
> I also see that the COMPOSITION which I packaged into 0.5.1 contains too many records (~41,000 instead of ~21,000)

Are you talking about your script result or mine ? Cause I went back to C++ till I'm really not comfortable with SQL complex writings.

> Here is what I would like to do:
>
> 1) purge
>
> canadian_db_processing

Nop, just consider that processing is the old finalize without these steps:
- populate INFORMATION
- drop tables
- vacuum

> 2) put back in what you had previously contributed as
>
> canadian_db_finalize

Nop, just send your work in the processing without the last 3 step

> except I would include a constraint (to avoid inserting the ingredients of no-longer available drugs)
>
> 3) I would like to rename
>
> canadian_db_preparation
>
> to one of
>
> canadian_db_schema
> canadian_db_tables

Ok, remember you need to modify the create script.

> because preparation is too similar to creation (and will then adjust the line in canadian_db_creator)
>
> Are we ok for me to do this?

Yes,

> -- Jim

Eric

[Jim Busser]

Joyeux Noel

The Canadian "processing" script is now further improved:

1) properly adjusts composition "strengths" even when "dosage_value" is blank

e.g. previously properly parsed
100 mg / 5 mL
but missed
100 mg / mL
and missed
100 mg / patch

(it left them as "100 mg")

--> fixed in UPDATE DRUGS set global_strength
(only now requires two queries instead of one)

--> fixed in INSERT INTO COMPOSITION
(WHERE statements)

2) patched defect resulting from Canadian source error

27 drugs lacked referential integrity in the source (missing ingredients)
--> I have emailed Canadian drug product directory
--> these drugs are now deleted from the 12783

Committed to svn

-- Jim

[Eric MAEKER]

Le 27 déc. 2010 à 06:36, Jim Busser a écrit :

> Joyeux Noel

Wawwww ;)
Merry Christmas ;)

> The Canadian "processing" script is now further improved:

> Committed to svn
>
> -- Jim

Ok I'll integrate this improvement later in FreeToolBox. I have delayed a bit the "private testing", but I'll work on it this week.

-----
Eric Maeker, MD (FR)
http://www.freemedforms.com/
http://www.ericmaeker.fr/