Re: [Gnumed-devel] Re: GNUmed and FreeDiams

[Jim Busser]

On 2011-01-01, at 3:32 AM, Karsten Hilbert wrote:

> No. Users would run the script as needed and generate the
> text file.
> 
>>      (needs the string "table" to be replaced with "ref.branded_drug")
> 
> How about applying sed to the resulting text file ?

This will do it

        sed -e 's:table:ref.branded_drug:' fd2gm_brands_raw.txt 
>fd2gm_brands_fixedup.txt

However… FreeDiams has columns

        route
        global_strength

lacking in GNUmed ref.branded_drug. We should add them, yes? They can remain 
empty for sources that do not use them but at least these would give a way to 
distinguish among multiple values for a branded drug that (in GNUmed) are 
identical except for the source UID

Once we would have resolved the above question, setting aside that postgres 
will auto-assign values in pk_audit, what do I need for the import file (or 
script) to address the GNUmed columns

        row_version
        serial (is this auto-assigned?)
        is_fake
        fk_data_source (integer)

For the foreign key to data_source, I would propose one data_source record per 
distinct drug database within (e.g.) FreeDiams

        name_long = FreeMedForms FreeDiams DRUGS table
        name_short = FreeDiams
        version = CA_HCDPD
        description = BSD license (Health Canada DPD source is no-charge, 
royalty-free, non-exclusive, annually renewable)
        source = http://code.google.com/p/freemedforms/downloads/list 
http://www.freemedforms.com/en/manuals/freediams/start
        lang = (leave blank)

and then the value for ref.data_source.pk will have to feed the insertion, like 
maybe

        INSERT INTO … but does one need the "ref." part, or just "branded_drug" 
?

         (quoted column names, assuming we will not in this case be specifying 
all?)

        SELECT pk FROM ref.data_source.pk WHERE name_short = "FreeDiams" and 
version = "CA_HCDPD"

which, if yes, would need to be pre-inserted (maybe via sed) into every record 
in the source text file. Or, is it better that this pk is manually looked up, 
and inserted, as part of creating the output from FreeDIams?


-- Jim