New Dialog: Import from Open App Builder

[lilyclements]

Can we a new dialog "Import from OpenAppBuilder" to import our app data. This should be pretty straight forward, I think. This is related to the PLH work and deliverables we have outlined.

I'm thinking it looks like this:

Establish Connection Option:

• If you check "Establish Connection..." then can the sub-dialog sdgImportFromClimSoft show. This is already created
• We need to make one minor change to this sub-dialog. Add at the top a new label "drv:" with an editable input drop-box in it. By default, display RMySQL::MySQL(), but have in the drop-down the option of RPostgres::Postgres()
• This then saves to the main dialog under plh_con, i.e., plh_con <- [code_run_in_sub_dialog]
• Once a connection is established, then can it say "Connected" like on dlgClimSoft, otherwise can it say "No Connection"

Main Dialog Code:

Control	Description	Parameter	Parameter Values
ucrCheckbox	Checkbox with text "Filter Data:"	filter	TRUE if checked; FALSE if unchecked
ucrInputComboBox	Has a label "Variable to Filter to:", both only visible if checkbox is checked, editable drop down box with one option so far app_deployment_name	filter_variable	Takes value as string, e.g., filter_variable = "app_deployment_name"
ucrInputComboBox	Has a label "Variable Value to Filter to:", both only visible if checkbox is checked, editable drop down box with a few options plh_za, wash, plh_tz, plh_global, early_family_math, ae_soils, plh_teens_tz, pfr, virtual_maths_camp, ae_app	filter_variable_value	Takes value as string, e.g., filter_variable_value= "virtual_maths_camp"
ucrSave	Save with default name "openappdata"
ucrBase	Only enabled if a connection is established like in dlgClimSoft

The code run on the main dialog is from the openappr R package.
So, if "filter" is not checked, then we run:

openappr::get_user_data(site = plh_con, filter = FALSE)

If filter is checked, then we run:

openappr::get_user_data(site = plh_con, filter = TRUE,
       filter_variable = "<<variable in first input>>**",
       filter_variable_value = "<<value in second input>>**")

@Vitalis95 I wonder if you would be okay to take this up? :)

[rdstern]

@lilyclements exciting. I have a query on terminology in relation to the filter part. That's because we do a lot of filtering within a data frame, to choose the rows. Is your's the same, so I could be saying filter on a Variable called Country, and choosing a value called Kenya?

[lilyclements]

@rdstern yes it would be like that, if a variable called country exists in the data! “app_deployment_name” is the name of the app to filter to and so this would be what is usually used to select which app you’re interested in - PLH Tanzania (plh_tz), maths app, etc

[Vitalis95]

@Patowhiz

Establish Connection Option:

• If you check "Establish Connection..." then can the sub-dialog sdgImportFromClimSoft show. This is already created
• We need to make one minor change to this sub-dialog. Add at the top a new label "drv:" with an editable input drop-box in it. By default, display RMySQL::MySQL(), but have in the drop-down the option of RPostgres::Postgres()
• This then saves to the main dialog under plh_con, i.e., plh_con <- [code_run_in_sub_dialog]
• Once a connection is established, then can it say "Connected" like on dlgClimSoft, otherwise can it say "No Connection"

When you run the sub-dialog it sends the following script to R

data_book$database_connect(dbname="mariadb_climsoft_test_r_db_v4", host="localhost", port=3306, user="root")

How can I save it to the main dialog under plh_con suggested above?

[Patowhiz]

@Vitalis95 the data_book_$database_connect R Function uses mysql connection driver by default. I'm not sure if the open app builder uses mysql as well. If it doesn't you may want to change the drv parameter and pass to it the correct database driver.

Regarding plh_con, you can get a database connection through data_book$get_database_connection().

You can examine the following R Functions to have an idea of working with databases. I can also send you the connection details to our sample climsoft database so that you can look at the scripts that the dialog produces as well.

has_database_connection, database_connect, get_database_connection, set_database_connection, database_disconnect, get_db_table_row_count, import_climsoft_metadata and import_climsoft_data

[lilyclements]

@Patowhiz thanks for outlining this.

So @Vitalis95 the code run on the main dialog is:

plh_con <- data_book$get_database_connection()

Then, if "filter" is not checked, then we run:

openappr::get_user_data(site = plh_con, filter = FALSE)

If filter is checked, then we run:

openappr::get_user_data(site = plh_con, filter = TRUE,
       filter_variable = "<<variable in first input>>**",
       filter_variable_value = "<<value in second input>>**")

I have checked this works as intended in R-Instat using the script window and it seems to work well! Thanks.

[Vitalis95]

@lilyclements with RPostgres::Postgres() I get the following error;

Also the database names mariadb_climsoft_db_v4, mariadb_climsoft_test_db_v4 says it does not exist