Introduce a master parent table `KoboDataset` in the Postgres automation

aleksa-krolls commented 3 years ago

Background

Currently the automation jobs output a Postgres table hierarchy where there are tables for form, repeatGroup, and nestedGroup. There should be 1 more static level introduced at the top of this hierarchy.

Request

Automatically create a static KoboDataset table in the PostgresDB that all Kobo OpenFn jobs will map to. This table should contain the following columns:

FormName: form.name
DatasetId: _xform_id_string (uuid)
LastUpdated: datetime column that is updated every time OpenFn writes to the DB --> Data.now()

See slide 4 for a visualization of this hierarchy.

state

Use openfn_kobo and WCS ConSoSci DB

expression

I believe adjustments need to be made across the automation jobs, especially job 01-getForms.js.

adaptor

language-http

output

For every 1 Kobo form submission, 1 KoboDataset record should be upserted using the form _xform_id_string. Using upserts will ensure that ultimately only 1 KoboDataset record will exist for every Kobo form/project.

aleksa-krolls commented 3 years ago

@ritazagoni Anything to add to this one based on the KoboData table used in BNS Surveys?

lakhassane commented 3 years ago

@aleksa-krolls @ritazagoni Please take a look at the created OpenFn jobs below. I am testing locally tonight and tomorrow. Your review on this output is welcomed.

╭───────────────────────────────────────────────────────────────╮
│ ◰ ◱ ◲ ◳  OpenFn/core ~ language-openfn#v1.0.2 (Node v12.18.3) │
╰───────────────────────────────────────────────────────────────╯
Authentication succeeded.
upsert('WCS__KoboDataset', 'GeneratedUuid', {
  FormName: 'Copie_de_Questionnaire_conso-ménage_29092020 derniere version',
  DatasetId: state.data._xform_id_string,
  Payload: state.data,
  GeneratedUuid: state.data._id-state.data._xform_id_string-0
});
upsert('WCS__FormGroup_CopieDeQuestionnaireConsoMénage29092020DerniereVersion', 'GeneratedUuid', {
  GeneratedUuid: state.data._id-state.data._xform_id_string-1,
  Version008: state.data._version__008,
  Version007: state.data._version__007,
  Version006: state.data._version__006,
  Version005: state.data._version__005,
  Version004: state.data._version__004,
  Version003: state.data._version__003,
  Version002: state.data._version__002,
  Version001: state.data._version__001,
  Version: state.data.__version__,
  Comments: state.data.comments,
  Coordinates: state.data.coordinates,
  Participant: state.data.participant,
  NbMeal: state.data.nb_meal,
  WhyRegulate: state.data.why_regulate,
  CharactSustMgt: state.data.charact_sust_mgt,
  WhyAbundance: state.data.why_abundance,
  PerceptionAbundance: state.data.perception_abundance,
  DansQuelCasEstCeRritoireDuVillage: state.data.Dans_quel_cas_est_ce_rritoire_du_village_,
  FrequencyPoaching: state.data.frequency_poaching,
  PressurePolice: state.data.pressure_police,
  LikePolice: state.data.like_police,
  FrequencyPolice: state.data.frequency_police,
  PressureEf: state.data.pressure_EF,
  LikeEf: state.data.like_EF,
  FrequencyEf: state.data.frequency_EF,
  FrequencySanction: state.data.frequency_sanction,
  WhyFairSanction2: state.data.why_fair_sanction2,
  FairnessSanction2: state.data.fairness_sanction2,
  WhyFairSanction: state.data.why_fair_sanction,
  FairnessSanction: state.data.fairness_sanction,
  OtherSanction: state.data.other_sanction,
  Sanctions: state.data.sanctions,
  PermitRifle: state.data.permit_rifle,
  OwnRifle: state.data.own_rifle,
  PermitHunt: state.data.permit_hunt,
  FrequencyProtectedsp: state.data.frequency_protectedsp,
  WhyAgreeProtectedsp: state.data.why_agree_protectedsp,
  AgreeProtectedsp: state.data.agree_protectedsp,
  OtherHuntProtectedsp: state.data.other_hunt_protectedsp,
  HuntProtectedsp: state.data.hunt_protectedsp,
  KnowHuntProtectedsp: state.data.know_hunt_protectedsp,
  FrequencyHuntQuota: state.data.frequency_hunt_quota,
  WhyAgreeQuotaHunt: state.data.why_agree_quota_hunt,
  AgreeQuotaHunt: state.data.agree_quota_hunt,
  OtherHuntQuota: state.data.other_hunt_quota,
  HuntQuota: state.data.hunt_quota,
  KnowHuntQuota: state.data.know_hunt_quota,
  FrequencyHuntTek: state.data.frequency_hunt_tek,
  WhyAgreeTekHunt: state.data.why_agree_tek_hunt,
  AgreeTekHunt: state.data.agree_tek_hunt,
  OtherHuntTek: state.data.other_hunt_tek,
  ForbidHuntTek: state.data.forbid_hunt_tek,
  KnowHuntTek: state.data.know_hunt_tek,
  FrequencyHuntNight: state.data.frequency_hunt_night,
  WhyAgreeNightHunt: state.data.why_agree_night_hunt,
  AgreeNightHunt: state.data.agree_night_hunt,
  OtherTimeClose: state.data.other_time_close,
  TimeCloseHunt: state.data.time_close_hunt,
  KnowHuntNight: state.data.know_hunt_night,
  FrequencyHuntClosed: state.data.frequency_hunt_closed,
  WhyAgreePeriodHunt: state.data.why_agree_period_hunt,
  AgreePeriodHunt: state.data.agree_period_hunt,
  DateCloseHunt: state.data.date_close_hunt,
  DateOpenHunt: state.data.date_open_hunt,
  KnowOpenHunt: state.data.know_open_hunt,
  KnowCustomHunt: state.data.know_custom_hunt,
  SatisfyIncomeHunt2: state.data.satisfy_income_hunt2,
  IncomeHunt2: state.data.income_hunt2,
  SatisfyIncomeHunt: state.data.satisfy_income_hunt,
  IncomeHunt: state.data.income_hunt,
  2ndDirection2: state.data._2nd_direction2,
  1stDirection2: state.data._1st_direction2,
  SoldVillage2: state.data.sold_village2,
  OtherSpSold24: state.data.other_sp_sold24,
  4thSpSold2: state.data._4th_sp_sold2,
  OtherSpSold23: state.data.other_sp_sold23,
  3rdSpSold2: state.data._3rd_sp_sold2,
  OtherSpSold22: state.data.other_sp_sold22,
  2ndSpSold2: state.data._2nd_sp_sold2,
  OtherSpSold21: state.data.other_sp_sold21,
  1stSpSold2: state.data._1st_sp_sold2,
  2ndDirection: state.data._2nd_direction,
  1stDirection: state.data._1st_direction,
  SoldVillage: state.data.sold_village,
  OtherSpSold4: state.data.other_sp_sold4,
  4thSpSold: state.data._4th_sp_sold,
  OtherSpSold3: state.data.other_sp_sold3,
  3rdSpSold: state.data._3rd_sp_sold,
  OtherSpSold2: state.data.other_sp_sold2,
  2ndSpSold: state.data._2nd_sp_sold,
  OtherSpSold1: state.data.other_sp_sold1,
  1stSpSold: state.data._1st_sp_sold,
  SumHunt2: state.data.sum_hunt2,
  AnimalGift2: state.data.animal_gift2,
  AnimalSold2: state.data.animal_sold2,
  AnimalConsumed2: state.data.animal_consumed2,
  SumHunt: state.data.sum_hunt,
  AnimalGift: state.data.animal_gift,
  AnimalSold: state.data.animal_sold,
  AnimalConsumed: state.data.animal_consumed,
  NbCarry2: state.data.nb_carry2,
  FrequencyCarry2: state.data.frequency_carry2,
  HuntCamp2: state.data.hunt_camp2,
  HuntNight2: state.data.hunt_night2,
  DurationSortie2: state.data.duration_sortie2,
  HDeparture2: state.data.h_departure2,
  NbHuntSortie2: state.data.nb_hunt_sortie2,
  TechnicHuntRifle2: state.data.technic_hunt_rifle2,
  NbSnare2: state.data.nb_snare2,
  TechnicHuntSnare2: state.data.technic_hunt_snare2,
  NbCarry: state.data.nb_carry,
  FrequencyCarry: state.data.frequency_carry,
  HuntCamp: state.data.hunt_camp,
  HuntNight: state.data.hunt_night,
  DurationSortie: state.data.duration_sortie,
  HDeparture: state.data.h_departure,
  NbHuntSortie: state.data.nb_hunt_sortie,
  TechnicHuntRifle: state.data.technic_hunt_rifle,
  NbSnare: state.data.nb_snare,
  TechnicHuntSnare: state.data.technic_hunt_snare,
  NbIndividualHunted2: state.data.nb_individual_hunted2,
  OtherSpHunt24: state.data.other_sp_hunt24,
  4thSpHunt2: state.data._4th_sp_hunt2,
  OtherSpHunt23: state.data.other_sp_hunt23,
  3rdSpHunt2: state.data._3rd_sp_hunt2,
  OtherSpHunt22: state.data.other_sp_hunt22,
  2ndSpHunt2: state.data._2nd_sp_hunt2,
  OtherSpHunt21: state.data.other_sp_hunt21,
  1stSpHunt2: state.data._1st_sp_hunt2,
  NbIndividualHunted: state.data.nb_individual_hunted,
  OtherSpHunt4: state.data.other_sp_hunt4,
  4thSpHunt: state.data._4th_sp_hunt,
  OtherSpHunt3: state.data.other_sp_hunt3,
  3rdSpHunt: state.data._3rd_sp_hunt,
  OtherSpHunt2: state.data.other_sp_hunt2,
  2ndSpHunt: state.data._2nd_sp_hunt,
  OtherSpHunt1: state.data.other_sp_hunt1,
  1stSpHunt: state.data._1st_sp_hunt,
  IdHunterSwm: state.data.id_hunter_SWM,
  HunterSwm: state.data.hunter_SWM,
  HunterAmountPension: state.data.hunter_amount_pension,
  HunterPensionYn: state.data.hunter_pension_YN,
  HunterAmountSalary: state.data.hunter_amount_salary,
  HunterSalaryYn: state.data.hunter_salary_YN,
  NbYearHunt: state.data.nb_year_hunt,
  EducationLevelCurrentHunter: state.data.education_level_current_hunter,
  EducationLevelStopHunter: state.data.education_level_stop_hunter,
  EndSchoolHunter: state.data.end_school_hunter,
  ChildhoodLivingPlaceHunter: state.data.childhood_living_place_hunter,
  ResidenceTimeHunter: state.data.residence_time_hunter,
  OtherReligionHunter: state.data.other_religion_hunter,
  ReligionHunter: state.data.religion_hunter,
  OtherEthnicityHunter: state.data.other_ethnicity_hunter,
  EthnicityHunter: state.data.ethnicity_hunter,
  OtherNationalityHunter: state.data.other_nationality_hunter,
  NationalityHunter: state.data.nationality_hunter,
  AgeHunter: state.data.age_hunter,
  OtherPersonStatusHunter: state.data.other_person_status_hunter,
  PersonStatusHunter: state.data.person_status_hunter,
  AttendHunter: state.data.attend_hunter,
  OtherMitigationDamage: state.data.other_mitigation_damage,
  MitigationStrategyHwc: state.data.mitigation_strategy_HWC,
  OtherPlaceDamage: state.data.other_place_damage,
  PlaceDamage: state.data.place_damage,
  LevelDamage: state.data.level_damage,
  FrequencyDamage: state.data.frequency_damage,
  OtherDamage: state.data.other_damage,
  TypeDamageHwc: state.data.type_damage_HWC,
  PeriodDamageHwc: state.data.period_damage_HWC,
  OtherSpDamage4: state.data.other_sp_damage4,
  4thSpDamage: state.data._4th_sp_damage,
  OtherSpDamage3: state.data.other_sp_damage3,
  3rdSpDamage: state.data._3rd_sp_damage,
  OtherSpDamage2: state.data.other_sp_damage2,
  2ndSpDamage: state.data._2nd_sp_damage,
  OtherSpDamage1: state.data.other_sp_damage1,
  1stSpDamage: state.data._1st_sp_damage,
  DamageWildlife: state.data.damage_wildlife,
  OtherWhyFishSpeciesAvoided: state.data.other_why_fish_species_avoided,
  WhyFishSpeciesAvoided: state.data.why_fish_species_avoided,
  Other2ndFishAvoided: state.data.other_2nd_fish_avoided,
  2ndFishAvoided: state.data._2nd_fish_avoided,
  Other1stFishAvoided: state.data.other_1st_fish_avoided,
  1stFishAvoided: state.data._1st_fish_avoided,
  OtherWhyFishSpeciesPrefered: state.data.other_why_fish_species_prefered,
  WhyFishSpeciesPrefered: state.data.why_fish_species_prefered,
  Other2ndFishPreference: state.data.other_2nd_fish_preference,
  2ndFishPreference: state.data._2nd_fish_preference,
  Other1stFishPreference: state.data.other_1st_fish_preference,
  1stFishPreference: state.data._1st_fish_preference,
  OtherWhyBushmeatSpeciesAvoided: state.data.other_why_bushmeat_species_avoided,
  WhyBushmeatSpeciesAvoided: state.data.why_bushmeat_species_avoided,
  Other2ndBushmeatAvoided: state.data.other_2nd_bushmeat_avoided,
  2ndBushmeatAvoided: state.data._2nd_bushmeat_avoided,
  Other1stBushmeatAvoided: state.data.other_1st_bushmeat_avoided,
  1stBushmeatAvoided: state.data._1st_bushmeat_avoided,
  OtherWhyBushmeatSpeciesPrefered: state.data.other_why_bushmeat_species_prefered,
  WhyBushmeatSpeciesPrefered: state.data.why_bushmeat_species_prefered,
  Other2ndBushmeatPreference: state.data.other_2nd_bushmeat_preference,
  2ndBushmeatPreference: state.data._2nd_bushmeat_preference,
  Other1stBushmeatPreference: state.data.other_1st_bushmeat_preference,
  1stBushmeatPreference: state.data._1st_bushmeat_preference,
  Other2ndWhy: state.data.other_2nd_why,
  2ndWhy: state.data._2nd_why,
  Other1stWhy: state.data.other_1st_why,
  1stWhy: state.data._1st_why,
  3thPreference: state.data._3th_preference,
  2ndPreference: state.data._2nd_preference,
  1stPreference: state.data._1st_preference,
  MoreLessEgg: state.data.more_less_egg,
  EggFrequency: state.data.egg_frequency,
  MoreLessDomesticmeat: state.data.more_less_domesticmeat,
  DomesticmeatFrequency: state.data.domesticmeat_frequency,
  MoreLessBushmeat: state.data.more_less_bushmeat,
  BushmeatFrequency: state.data.bushmeat_frequency,
  MoreLessProcessedFish: state.data.more_less_processed_fish,
  ProcessedFishFrequency: state.data.processed_fish_frequency,
  MoreLessFrozenFish: state.data.more_less_frozen_fish,
  FrozenFishFrequency: state.data.frozen_fish_frequency,
  MoreLessFreshFish: state.data.more_less_fresh_fish,
  FreshFishFrequency: state.data.fresh_fish_frequency,
  AccessTelevision: state.data.access_television,
  AccessRadio: state.data.access_radio,
  AccessTransport: state.data.access_transport,
  AccessDispensaire: state.data.access_dispensaire,
  AccessPhone: state.data.access_phone,
  OtherWaterSource: state.data.other_water_source,
  WaterSource: state.data.water_source,
  ElectricityTime: state.data.electricity_time,
  OtherElectricityAccess: state.data.other_electricity_access,
  ElectricityAccess: state.data.electricity_access,
  ElectricityYn: state.data.electricity_yn,
  BnsMatrixMoustiquairePossesCost: state.data.bns_matrix_moustiquaire_posses_cost,
  BnsMatrixMoustiquaireNumber: state.data.bns_matrix_moustiquaire_number,
  BnsMatrixMoustiquairePossess: state.data.bns_matrix_moustiquaire_possess,
  BnsMatrixMoustiquaireNecessary: state.data.bns_matrix_moustiquaire_necessary,
  BnsMatrixVoiturePriveePossesCost: state.data.bns_matrix_voiture_privee_posses_cost,
  BnsMatrixVoiturePriveeNumber: state.data.bns_matrix_voiture_privee_number,
  BnsMatrixVoiturePriveePossess: state.data.bns_matrix_voiture_privee_possess,
  BnsMatrixVoiturePriveeNecessary: state.data.bns_matrix_voiture_privee_necessary,
  BnsMatrixPiroguePossesCost: state.data.bns_matrix_pirogue_posses_cost,
  BnsMatrixPirogueNumber: state.data.bns_matrix_pirogue_number,
  BnsMatrixPiroguePossess: state.data.bns_matrix_pirogue_possess,
  BnsMatrixPirogueNecessary: state.data.bns_matrix_pirogue_necessary,
  BnsMatrixGroupeElectrogenePossesCost: state.data.bns_matrix_groupe_electrogene_posses_cost,
  BnsMatrixGroupeElectrogeneNumber: state.data.bns_matrix_groupe_electrogene_number,
  BnsMatrixGroupeElectrogenePossess: state.data.bns_matrix_groupe_electrogene_possess,
  BnsMatrixGroupeElectrogeneNecessary: state.data.bns_matrix_groupe_electrogene_necessary,
  BnsMatrixMatelasPossesCost: state.data.bns_matrix_matelas_posses_cost,
  BnsMatrixMatelasNumber: state.data.bns_matrix_matelas_number,
  BnsMatrixMatelasPossess: state.data.bns_matrix_matelas_possess,
  BnsMatrixMatelasNecessary: state.data.bns_matrix_matelas_necessary,
  BnsMatrixCanapePossesCost: state.data.bns_matrix_canape_posses_cost,
  BnsMatrixCanapeNumber: state.data.bns_matrix_canape_number,
  BnsMatrixCanapePossess: state.data.bns_matrix_canape_possess,
  BnsMatrixCanapeNecessary: state.data.bns_matrix_canape_necessary,
  BnsMatrixMarmitesPossesCost: state.data.bns_matrix_marmites_posses_cost,
  BnsMatrixMarmitesNumber: state.data.bns_matrix_marmites_number,
  BnsMatrixMarmitesPossess: state.data.bns_matrix_marmites_possess,
  BnsMatrixMarmitesNecessary: state.data.bns_matrix_marmites_necessary,
  BnsMatrixMoulinManiocPossesCost: state.data.bns_matrix_moulin_manioc_posses_cost,
  BnsMatrixMoulinManiocNumber: state.data.bns_matrix_moulin_manioc_number,
  BnsMatrixMoulinManiocPossess: state.data.bns_matrix_moulin_manioc_possess,
  BnsMatrixMoulinManiocNecessary: state.data.bns_matrix_moulin_manioc_necessary,
  BnsMatrixFrigoPossesCost: state.data.bns_matrix_frigo_posses_cost,
  BnsMatrixFrigoNumber: state.data.bns_matrix_frigo_number,
  BnsMatrixFrigoPossess: state.data.bns_matrix_frigo_possess,
  BnsMatrixFrigoNecessary: state.data.bns_matrix_frigo_necessary,
  BnsMatrixCongelateurPossesCost: state.data.bns_matrix_congelateur_posses_cost,
  BnsMatrixCongelateurNumber: state.data.bns_matrix_congelateur_number,
  BnsMatrixCongelateurPossess: state.data.bns_matrix_congelateur_possess,
  BnsMatrixCongelateurNecessary: state.data.bns_matrix_congelateur_necessary,
  BnsMatrixPellePossesCost: state.data.bns_matrix_pelle_posses_cost,
  BnsMatrixPelleNumber: state.data.bns_matrix_pelle_number,
  BnsMatrixPellePossess: state.data.bns_matrix_pelle_possess,
  BnsMatrixPelleNecessary: state.data.bns_matrix_pelle_necessary,
  BnsMatrixImpermeablePossesCost: state.data.bns_matrix_impermeable_posses_cost,
  BnsMatrixImpermeableNumber: state.data.bns_matrix_impermeable_number,
  BnsMatrixImpermeablePossess: state.data.bns_matrix_impermeable_possess,
  BnsMatrixImpermeableNecessary: state.data.bns_matrix_impermeable_necessary,
  BnsMatrixBottesPossesCost: state.data.bns_matrix_bottes_posses_cost,
  BnsMatrixBottesNumber: state.data.bns_matrix_bottes_number,
  BnsMatrixBottesPossess: state.data.bns_matrix_bottes_possess,
  BnsMatrixBottesNecessary: state.data.bns_matrix_bottes_necessary,
  BnsMatrixPiochePossesCost: state.data.bns_matrix_pioche_posses_cost,
  BnsMatrixPiocheNumber: state.data.bns_matrix_pioche_number,
  BnsMatrixPiochePossess: state.data.bns_matrix_pioche_possess,
  BnsMatrixPiocheNecessary: state.data.bns_matrix_pioche_necessary,
  BnsMatrixHouePossesCost: state.data.bns_matrix_houe_posses_cost,
  BnsMatrixHoueNumber: state.data.bns_matrix_houe_number,
  BnsMatrixHouePossess: state.data.bns_matrix_houe_possess,
  BnsMatrixHoueNecessary: state.data.bns_matrix_houe_necessary,
  BnsMatrixTelevisionPossesCost: state.data.bns_matrix_television_posses_cost,
  BnsMatrixTelevisionNumber: state.data.bns_matrix_television_number,
  BnsMatrixTelevisionPossess: state.data.bns_matrix_television_possess,
  BnsMatrixTelevisionNecessary: state.data.bns_matrix_television_necessary,
  BnsMatrixMoteurPossesCost: state.data.bns_matrix_moteur_posses_cost,
  BnsMatrixMoteurNumber: state.data.bns_matrix_moteur_number,
  BnsMatrixMoteurPossess: state.data.bns_matrix_moteur_possess,
  BnsMatrixMoteurNecessary: state.data.bns_matrix_moteur_necessary,
  BnsMatrixPortablePossesCost: state.data.bns_matrix_portable_posses_cost,
  BnsMatrixPortableNumber: state.data.bns_matrix_portable_number,
  BnsMatrixPortablePossess: state.data.bns_matrix_portable_possess,
  BnsMatrixPortableNecessary: state.data.bns_matrix_portable_necessary,
  BnsMatrixPendulePossesCost: state.data.bns_matrix_pendule_posses_cost,
  BnsMatrixPenduleNumber: state.data.bns_matrix_pendule_number,
  BnsMatrixPendulePossess: state.data.bns_matrix_pendule_possess,
  BnsMatrixPenduleNecessary: state.data.bns_matrix_pendule_necessary,
  BnsMatrixMontrePossesCost: state.data.bns_matrix_montre_posses_cost,
  BnsMatrixMontreNumber: state.data.bns_matrix_montre_number,
  BnsMatrixMontrePossess: state.data.bns_matrix_montre_possess,
  BnsMatrixMontreNecessary: state.data.bns_matrix_montre_necessary,
  BnsMatrixLampeTempetePossesCost: state.data.bns_matrix_lampe_tempete_posses_cost,
  BnsMatrixLampeTempeteNumber: state.data.bns_matrix_lampe_tempete_number,
  BnsMatrixLampeTempetePossess: state.data.bns_matrix_lampe_tempete_possess,
  BnsMatrixLampeTempeteNecessary: state.data.bns_matrix_lampe_tempete_necessary,
  BnsMatrixLampeTorchePossesCost: state.data.bns_matrix_lampe_torche_posses_cost,
  BnsMatrixLampeTorcheNumber: state.data.bns_matrix_lampe_torche_number,
  BnsMatrixLampeTorchePossess: state.data.bns_matrix_lampe_torche_possess,
  BnsMatrixLampeTorcheNecessary: state.data.bns_matrix_lampe_torche_necessary,
  BnsMatrixBrouettePossesCost: state.data.bns_matrix_brouette_posses_cost,
  BnsMatrixBrouetteNumber: state.data.bns_matrix_brouette_number,
  BnsMatrixBrouettePossess: state.data.bns_matrix_brouette_possess,
  BnsMatrixBrouetteNecessary: state.data.bns_matrix_brouette_necessary,
  BnsMatrixTronconeusePossesCost: state.data.bns_matrix_tronconeuse_posses_cost,
  BnsMatrixTronconeuseNumber: state.data.bns_matrix_tronconeuse_number,
  BnsMatrixTronconeusePossess: state.data.bns_matrix_tronconeuse_possess,
  BnsMatrixTronconeuseNecessary: state.data.bns_matrix_tronconeuse_necessary,
  BnsMatrixHachePossesCost: state.data.bns_matrix_hache_posses_cost,
  BnsMatrixHacheNumber: state.data.bns_matrix_hache_number,
  BnsMatrixHachePossess: state.data.bns_matrix_hache_possess,
  BnsMatrixHacheNecessary: state.data.bns_matrix_hache_necessary,
  BnsMatrixMachettePossesCost: state.data.bns_matrix_machette_posses_cost,
  BnsMatrixMachetteNumber: state.data.bns_matrix_machette_number,
  BnsMatrixMachettePossess: state.data.bns_matrix_machette_possess,
  BnsMatrixMachetteNecessary: state.data.bns_matrix_machette_necessary,
  BnsMatrixPagaisPossesCost: state.data.bns_matrix_pagais_posses_cost,
  BnsMatrixPagaisNumber: state.data.bns_matrix_pagais_number,
  BnsMatrixPagaisPossess: state.data.bns_matrix_pagais_possess,
  BnsMatrixPagaisNecessary: state.data.bns_matrix_pagais_necessary,
  BnsMatrixHameconPossesCost: state.data.bns_matrix_hamecon_posses_cost,
  BnsMatrixHameconNumber: state.data.bns_matrix_hamecon_number,
  BnsMatrixHameconPossess: state.data.bns_matrix_hamecon_possess,
  BnsMatrixHameconNecessary: state.data.bns_matrix_hamecon_necessary,
  BnsMatrixNassePossesCost: state.data.bns_matrix_nasse_posses_cost,
  BnsMatrixNasseNumber: state.data.bns_matrix_nasse_number,
  BnsMatrixNassePossess: state.data.bns_matrix_nasse_possess,
  BnsMatrixNasseNecessary: state.data.bns_matrix_nasse_necessary,
  BnsMatrixFiletDePechePossesCost: state.data.bns_matrix_filet_de_peche_posses_cost,
  BnsMatrixFiletDePecheNumber: state.data.bns_matrix_filet_de_peche_number,
  BnsMatrixFiletDePechePossess: state.data.bns_matrix_filet_de_peche_possess,
  BnsMatrixFiletDePecheNecessary: state.data.bns_matrix_filet_de_peche_necessary,
  BnsMatrixCartouchePossesCost: state.data.bns_matrix_cartouche_posses_cost,
  BnsMatrixCartoucheNumber: state.data.bns_matrix_cartouche_number,
  BnsMatrixCartouchePossess: state.data.bns_matrix_cartouche_possess,
  BnsMatrixCartoucheNecessary: state.data.bns_matrix_cartouche_necessary,
  BnsMatrixCableMetalliquePossesCost: state.data.bns_matrix_cable_metallique_posses_cost,
  BnsMatrixCableMetalliqueNumber: state.data.bns_matrix_cable_metallique_number,
  BnsMatrixCableMetalliquePossess: state.data.bns_matrix_cable_metallique_possess,
  BnsMatrixCableMetalliqueNecessary: state.data.bns_matrix_cable_metallique_necessary,
  BnsMatrixFusilPossesCost: state.data.bns_matrix_fusil_posses_cost,
  BnsMatrixFusilNumber: state.data.bns_matrix_fusil_number,
  BnsMatrixFusilPossess: state.data.bns_matrix_fusil_possess,
  BnsMatrixFusilNecessary: state.data.bns_matrix_fusil_necessary,
  BnsMatrixDebrousailleusePossesCost: state.data.bns_matrix_debrousailleuse_posses_cost,
  BnsMatrixDebrousailleuseNumber: state.data.bns_matrix_debrousailleuse_number,
  BnsMatrixDebrousailleusePossess: state.data.bns_matrix_debrousailleuse_possess,
  BnsMatrixDebrousailleuseNecessary: state.data.bns_matrix_debrousailleuse_necessary,
  BnsMatrixMoutonPossesCost: state.data.bns_matrix_mouton_posses_cost,
  BnsMatrixMoutonNumber: state.data.bns_matrix_mouton_number,
  BnsMatrixMoutonPossess: state.data.bns_matrix_mouton_possess,
  BnsMatrixMoutonNecessary: state.data.bns_matrix_mouton_necessary,
  BnsMatrixCochonPossesCost: state.data.bns_matrix_cochon_posses_cost,
  BnsMatrixCochonNumber: state.data.bns_matrix_cochon_number,
  BnsMatrixCochonPossess: state.data.bns_matrix_cochon_possess,
  BnsMatrixCochonNecessary: state.data.bns_matrix_cochon_necessary,
  Revenue6: state.data.revenue6,
  OtherL6: state.data.other_l6,
  L6: state.data.l6,
  Revenue5: state.data.revenue5,
  OtherL5: state.data.other_l5,
  L5: state.data.l5,
  Revenue4: state.data.revenue4,
  OtherL4: state.data.other_l4,
  L4: state.data.l4,
  OtherL3: state.data.other_l3,
  L3: state.data.l3,
  OtherL2: state.data.other_l2,
  L2: state.data.l2,
  OtherL1: state.data.other_l1,
  L1: state.data.l1,
  IncomeRetired: state.data.income_retired,
  Employer2: state.data.employer2,
  NbRetired: state.data.nb_retired,
  IncomeEmployed: state.data.income_employed,
  Employer1: state.data.employer1,
  NbEmployed: state.data.nb_employed,
  NbFisher: state.data.nb_fisher,
  NbHunt: state.data.nb_hunt,
  SchooledAdultNumber: state.data.schooled_adult_number,
  SchooledTeenagerNumber: state.data.schooled_teenager_number,
  SchooledChildNumber: state.data.schooled_child_number,
  AdultWomanNumber: state.data.adult_woman_number,
  TeenagerWomanNumber: state.data.teenager_woman_number,
  ChildWomanNumber: state.data.child_woman_number,
  WomanNumber: state.data.woman_number,
  AdultManNumber: state.data.adult_man_number,
  TeenagerManNumber: state.data.teenager_man_number,
  ChildManNumber: state.data.child_man_number,
  ManNumber: state.data.man_number,
  OtherRoofMaterial: state.data.other_roof_material,
  RoofMaterial: state.data.roof_material,
  OtherFloorMaterial: state.data.other_floor_material,
  FloorMaterial: state.data.floor_material,
  OtherWallMaterial: state.data.other_wall_material,
  WallMaterial: state.data.wall_material,
  Village: state.data.village,
  Kobogroup: state.data.group,
  Surveyor: state.data.surveyor,
  Deviceid: state.data.deviceid,
  Today: state.data.today,
  EndDate: state.data.end,
  Start: state.data.start,
  Payload: state.data
});
upsertMany('WCS__FormGroup_CopieDeQuestionnaireConsoMénage29092020DerniereVersionGroupIdentity', 'GeneratedUuid', state => state.data.group_identity.map(x => ({
  PositionLoophh: x['group_identity/position_loopHH'],
  PersonStatus: x['group_identity/person_status'],
  Gender: x['group_identity/gender'],
  Age: x['group_identity/age'],
  Nationality: x['group_identity/nationality'],
  OtherNationality: x['group_identity/other_nationality'],
  Ethnicity: x['group_identity/ethnicity'],
  OtherEthnicity: x['group_identity/other_ethnicity'],
  Religion: x['group_identity/religion'],
  OtherReligion: x['group_identity/other_religion'],
  ResidenceTime: x['group_identity/residence_time'],
  ChildhoodLivingPlace: x['group_identity/childhood_living_place'],
  EducationLevel: x['group_identity/education_level'],
  Degree: x['group_identity/degree'],
  EndSchool: x['group_identity/end_school'],
  HuntHead: x['group_identity/hunt_head'],
  FishHead: x['group_identity/fish_head'],
  Payload: state.data,
  GeneratedUuid: state.data._id-state.data._xform_id_string-2
})));
upsertMany('WCS__FormGroup_CopieDeQuestionnaireConsoMénage29092020DerniereVersionGroupDonation', 'GeneratedUuid', state => state.data.group_donation.map(x => ({
  TooManyRepeatDonation: x['group_donation/too_many_repeat_donation'],
  Donation: x['group_donation/donation'],
  DonationFrequency: x['group_donation/donation_frequency'],
  DonationSource: x['group_donation/donation_source'],
  OtherDonationSource: x['group_donation/other_donation-source'],
  Payload: state.data,
  GeneratedUuid: state.data._id-state.data._xform_id_string-3
})));
upsertMany('WCS__FormGroup_CopieDeQuestionnaireConsoMénage29092020DerniereVersionInfoConsumptionHistory', 'GeneratedUuid', state => state.data.info_consumption_history.map(x => ({
  MealNb: x['info_consumption_history/meal_nb'],
  Date: x['info_consumption_history/date'],
  DayPeriod: x['info_consumption_history/day_period'],
  CodeRepas: x['info_consumption_history/code_repas'],
  NbPeople: x['info_consumption_history/nb_people'],
  EatProteinYesno: x['info_consumption_history/eat_protein_yesno'],
  Payload: state.data,
  GeneratedUuid: state.data._id-state.data._xform_id_string-4
})));
upsertMany('WCS__FormGroup_CopieDeQuestionnaireConsoMénage29092020DerniereVersionInfoConsumptionHistoryProteinMeal', 'GeneratedUuid', state => state.data.info_consumption_history.protein_meal.map(x => ({
  Category1: x['info_consumption_history/protein_meal/category1'],
  Species: x['info_consumption_history/protein_meal/species'],
  OtherFood: x['info_consumption_history/protein_meal/other_food'],
  State: x['info_consumption_history/protein_meal/state'],
  OtherState: x['info_consumption_history/protein_meal/other_state'],
  Unit: x['info_consumption_history/protein_meal/unit'],
  OtherUnit: x['info_consumption_history/protein_meal/other_unit'],
  UnitNumber: x['info_consumption_history/protein_meal/unit_number'],
  UnitPrice: x['info_consumption_history/protein_meal/unit_price'],
  Obtention: x['info_consumption_history/protein_meal/obtention'],
  OtherObtention: x['info_consumption_history/protein_meal/other_obtention'],
  Payload: state.data,
  GeneratedUuid: state.data._id-state.data._xform_id_string-5
})));

lakhassane commented 3 years ago

@aleksa-krolls one additional question about the master parent table. Don't you think we should use insert instead of upsert?

We want every time OpenFn writes to the DB to keep track of it so we would not want to upsert existing data.

lakhassane commented 3 years ago

One final thought (or should I say 2),

We have some questions that have the same value as reserved keyword in postgres. For now we are addressing them using this: https://github.com/OpenFn/wcs/blob/master/automation/01-getForms.js#L65-L71 However at some point, we might want client's feedback on a proper/better renaming.
We also have some questions that are duplicated in the payload for the same form (we have twice Calculation and twice version). This brings failure to table creation on postgres. Is it a common behavior in the survey or just an error during form creation? (An example can be found in the form Form title is tag test with calculation)

aleksa-krolls commented 3 years ago

@lakhassane To answer your questions

Yes, we want to upsert KoboData table --> there should be 1 record for every form/project in Kobo. All form submissions for this form will be child to this KoboData record. However we want to upsert based on DatasetId ... you can drop GeneratedUuid for this table as this uuid is for submissions.
Noted re: the reserved column dates. These work for now, but will confirm with the client.
This was a form used for testing, so I don't think we need to handle for these scenarios. @ritazagoni can you please advise on Mamadou's last question here (see above comment)?

ritazagoni commented 3 years ago

@lakhassane Yes I can confirm 3. from Aleksa, no need to handle these, I will also clean up the project and delete these irrelevant test forms.

aleksa-krolls commented 3 years ago

@ritazagoni If all these wcs issues in the "Review" column look good after the last round of testing today, let's close these out.

OpenFn / ConSoSci