Create species reference table & map species code

[aleksa-krolls]

To help record species data, WCS has requested that we help load data to the swm_species table.

1. Upload species data to swm_species to create a new reference table. This is because in the Kobo form, we only receive species code (i.e., 9792 which corresponds with Forest Hog). Please upload this data to swm_species.
2. Add new species_code column to tbl_wildmeat to link this table with swm_species. See my comments in row 40.
3. Add a new mapping to the job created for issue #3 to map Kobo species code. See my comments in row 40.

[aleksa-krolls]

@lakhassane @taylordowns2000 this is a new request from WCS related to our job, but involves a small data upload. Let me know if you have any questions!

[taylordowns2000]

@lakhassane , if this is done and tested please commit your changes and close this issue

[aleksa-krolls]

@lakhassane Still sometimes failing on OpenFn.org with error error: invalid input syntax for type double precision: "". Maybe we need to deploy the LP changes to prod? See run here. cc @taylordowns2000

[lakhassane]

@aleksa-krolls the double precision issue has been addressed in LP (and we made a bunch of changes). However, you might still stumble on an erro due to the column wildmeat_id that need to be not null.

[taylordowns2000]

I can confirm that the new adaptor version is on production now, but that we're getting this: "Executing insert many via: INSERT INTO tbl_wildmeat (kobo_submission_id, site_id, study_id, sample_id, taxon_id, wildmeat_category_1, wildmeat_category_2, wildmeat_group, unit, amount, massin_grams, price, aquisition, acquisition_other, origin_of_wildmeat, condition, consumption_frequency_unit) VALUES [--REDACTED--] error: null value in column "wildmeat_id" violates not-null constraint"

https://openfn.org/projects/1767/runs/9785979

What's next here?

On Mon, Sep 7, 2020 at 2:25 PM lakhassane notifications@github.com wrote:

@aleksa-krolls https://github.com/aleksa-krolls the double precision issue has been addressed in LP (and we made a bunch of changes). However, you might still stumble on an erro due to the column wildmeat_id that need to be not null.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/OpenFn/wcs-consocsci/issues/5#issuecomment-688465610, or unsubscribe https://github.com/notifications/unsubscribe-auth/ACCUBLN3DZRWFY4I3DMXDB3SEUQSNANCNFSM4QRLUA4Q .

-- Taylor Downs | Head of Product Open Function Group https://www.openfn.org/ | How Data Integration Makes ICT4D Work https://youtu.be/GVB9voGp6T4

[aleksa-krolls]

@taylordowns2000 @lakhassane I mapped Kobo _id to Postgres wildmeat_id for now. But now I'm hitting a JS error: RangeError: Invalid array length (see run here).

Also confirming we can map the full message payload to the instances column? https://github.com/OpenFn/wcs-consocsci/blob/master/ruralConsumptionToPostgres.js#L124-L125

[aleksa-krolls]

@lakhassane @taylordowns2000 Feedback from jobs testing so far.

1. I think the DB column origin_of_wildmeat has a character limit of 16. Is it possible to update the DB to accept longer string values? (There are currently no constraints in the Kobo form, but it's a short answer question - so I think it would be safe to allow up to 200 characters.)
2. RE: instances - can map the full message payload here (everything within the Message "data":{...}? https://github.com/OpenFn/wcs-consocsci/blob/master/ruralConsumptionToPostgres.js#L124-L125

[lakhassane]

@aleksa-krolls

1. I updated the column origin_of_wildmeat in the db.

2. I mapped the whole "data": {...} inside instances. You should be good to go once I make a point with Taylor and commit changes.

[taylordowns2000]

Hey @aleksa-krolls , this has been in review for a while. Is there anything you need from us here?

[aleksa-krolls]

I'm anticipating changes per client feedback, but waiting on WCS to confirm something - it's been lots of relaying to arrive at answers. Stay tuned.

[aleksa-krolls]

WCS is unable to find the master species list originally used to create the Kobo forms. We will map species_code only for now, and WCS will later map the code to the appropriate species, taxon, etc.