Fixing `minvalue` in `Equation` and `wsp_flowby` and `valuetype` in DataMatrix

[rburghol]

Tasks

• Develop R code flow to fix individual instances of defaultval
• Change the om php code base to set default valuetype = 1 for dataMatrix (we never used the type 0 in practice, and have not develope a way to log it!)
• Update the model template for select instances.
• Fix bad varid and default value for drought_pct data matrices via SQL
• Insert checks for NA in plugin OpenMI exporter to set these to NULL or 0 (depending on the class)
• Do widespread updates to dataMatrix properties in the new database to insure that all matrices have valid valuetype settings.

Overview

FYI @COBrogan -- I got a fully working workflow of 1) Load model in R, 2) Make changes in R, save them, 3) push changes from the database drupal.dh03 to model within meta_model commands (so no need to jump around to directories). In the process of course, some of the fragility of these 2 databases came to light, and so I ended up doing some debugging. The highlights of this debugging are below. I actually fixed the model crashes by editing the data in the drupal.dh03 database, which is an excellent outcome IMO, but also, not really an efficient way of doing things, but nice proof of concept (I feel like I am valuing a lot of somewhat inefficient processes in the name of proof of concept lately, hopefully that will abate!).

• R will often set NA instead of NULL, and NA then comes in as a string (it's not really a thing in json) -- we probably should eliminate that in the plugin export, but for easier demonstration, the below approach works, and sometimes it is better to fix your data in the moment than overhaul your code base. Specific instances were for equations and DataMatrix objects:
  • default values for some equations were NA, as were minvalue, which, when combined with a setting of `nonnegative = 1`` caused "greater than zero" evaluation to fail on db insert since NA was a string.
  • default values for flowby type objects (also used for reservoir releases) suffered from this same trouble.
• Anyhow, so the fixes:
  • 1st code block is fixing loop for the template and the model I was working on. Fun fact: you can put a bunch of objects in a list, and loop through them (i.e. for (thismodel in list(template_model, fac_model_info))) applying the same code to multiple objects and saving them, this could come in handy
  • 2nd block is the debug output from the postgresql log for the model the column release_historic had value NA which was crushing the database because it was expecting a numeric field. Again, we can probably filter for NA and convert to NULL?

Code 1: Patching up individual equations and flowby objects minvalue setting.

fac_model_info <- ds$get_json_prop(fac_model$pid)
template_model <- ds$get_json_prop(4988636)
# iterate through multiple json exports if desired.
for (thismodel in list(template_model, fac_model_info)) {
  message(thismodel$id)
  sw_frac_mv <- RomProperty$new(ds,list(pid=thismodel$sw_frac$minvalue$id),TRUE)
  sw_frac_mv$propvalue = 0.0
  sw_frac_mv$save(TRUE)
  # gw_frac
  gw_frac_mv <- RomProperty$new(ds,list(pid=thismodel$gw_frac$minvalue$id),TRUE)
  gw_frac_mv$propvalue = 0.0
  gw_frac_mv$save(TRUE)
  # release historic (also release_current was bad, though now the template is fixed, new models should be OK)
  rmv <- RomProperty$new(ds,list(pid=thismodel$release_historic$minvalue$id),TRUE)
  rmv$propvalue = 0.0
  rmv$save(TRUE)
}

SQL 1: Example error from a model session database that shows what happens when NA is set as the default value, and php doesn't understand what NA means, so it treats it as a string, which ones cannot insert into a numeric field. This was obtained by sudo su - postgres and then doing tail -f logfile.model_scratch while the model ran.

insert into "tmp351476_083_datalog" ( "Qriver" , "wd_last_mgd" , "ps_last_mgd" , "lake_elev" , "drought_status" , "pct_full" , "rejected_demand_pct" , "impoundment_use_remain_mg" , "Qreach" , "Qriver_up" , "Runit" , "precip_in" , "et_in" , "reach_area_sqmi" , "impoundment_pct_use_remain" , "Qnextdown" , "impoundment_refill_full_mgd" , "flowby_current" , "release_proposed" , "thisdate" , "month" , "day" , "year" , "jday" , "week" , "timestamp" , "modays" , "run_mode" , "flow_mode" , "historic_annual" , "wd_mgd" , "Send to Parent" , "Listen on Parent" , "consumption" , "historic_monthly_pct" , "flowby" , "current_mgy" , "max_wd_annual" , "future_mgy" , "future_mgd" , "safeyield_mgy" , "safeyield_mgd" , "current_mgd" , "id1" , "wdtype" , "discharge_mgd" , "fac_demand_mgy" , "fac_demand_mgd" , "vahydro_hydroid" , "riverseg" , "fac_current_mgy" , "wsp2020_2020_mgy" , "wsp2020_2040_mgy" , "wsp2020_2030_mgy" , "riverseg_frac" , "vwp_exempt_mgd" , "ps_enabled" , "consumption_monthly" , "unaccounted_losses" , "consumption_monthly_cws_auto" , "fac_gw_frac" , "available_mgd" , "base_demand_mgd" , "max_mgd" , "adj_demand_mgd" , "drought_pct" , "drought_response_enabled" , "unmet_demand_mgd" , "Qintake" , "gw_frac" , "sw_frac" , "gw_sw_factor" , "gw_demand_mgd" , "discharge_from_gw_mgd" , "base_demand_pstatus_mgd" , "permit_status" , "vwp_prop_max_mgy" , "vwp_prop_max_mgd" , "vwp_prop_base_mgd" , "vwp_prop_demand_mgd" , "vwp_max_mgy" , "vwp_max_mgd" , "vwp_base_mgd" , "vwp_demand_mgd" , "flowby_proposed" , "flowby_historic" , "release" , "release_current" , "release_historic" , "refill_transfer_available_mgd" , "refill_transfer_max_mgd" , "Reservoir Operations" , "control_impoundment" , "impoundment_release_cfs" )  values ( '641.55467342517','0.089963999999998','0.0080297656960911','18','1088','1',0,'1894.0390879479',0,0,0,'0.144','0.039',0,0,0,0,'0','10','2002-12-31','12','31','2002','364','01','1041310800','31','13','3',0,'5.132344516129','','','0','0.0838','0','2696.84','0','0','82','0','82','7.2901674838709','-9999','SW','4.6191100645161','1898.6','5.132344516129','4823737','JB3_7053_0001','2329.64','1898.6','2263.7058','0','1','82','1','0','0.1','0','0','1894.0390879479','5.132344516129','5.132344516129','5.132344516129','0','1','0','641.55467342517','0','1','0','0','0','5.132344516129','exempt','0','0','0','0','0','0','0','0','0','0','10','10','NA','0','0','','0','0'

Fixing default valuetype in dataMatrix in om codebase

• There was also a bug in dataMatrix where the default return type for a dataMatrix in the om codebase was 0, which my comments referred to as normal, but it was anything but normal! In fact, it returned an entire array, instead of a numeric 1-d or 2-d lookup value.
  • This results in a database conflict during model runtime logging, sending the value Array to a database column that is a numeric field, which fails, and thus, the log for these objects is completely empty, which kills our summary analysis process.
  • While the normal setting was the default in the code base, somehow the old interface set a more appropriate default, or at least, when coding the templates I set the default to be a 1-d matrix, so things worked well.
  • It appears that when migrating objects from om to drupal that I missed this setting! And so there was nothing in the drupal database, but that was okay, as the old saving routines were fairly loose, and supplied a sensible default when missing.
  • This new method did not have that default, it's kinda complicated, but there it is. We have a little more coding to do in the plugins in order to facilitate reasonable defaults, but for now, these 1-time fixes for individual components can get us moving towards working models.

SQL based code fixes

Fix defaultval prop of Matrix drought_pct

• The defaultval prop of Matrix drought_pct has some percentage of vars with type Alpha, when they should be constant, and default should be 0 instead of null.

SQL 2: Apply a global fix to the drought_pct variables. Fix bad varid (no ide ow that happened), which resulted in an alphanumeric type, and an emoty string default.

update dh_properties_revision set varid = 1358 where pid in (select pid from (select a.pid,b.propname,a.varid,b.varid from dh_properties as a left outer join dh_properties as b on (a.featureid = b.pid and a.entity_type = 'dh_properties') where b.propname = 'drought_pct' and a.propname = 'defaultval' and b.varid = 1356 and a.varid = 1385 ) as foo);

update dh_properties set varid = 1358 where pid in (select pid from (select a.pid,b.propname,a.varid,b.varid from dh_properties as a left outer join dh_properties as b on (a.featureid = b.pid and a.entity_type = 'dh_properties') where b.propname = 'drought_pct' and a.propname = 'defaultval' and b.varid = 1356 and a.varid = 1385 ) as foo);


update dh_properties set propvalue = 0.0 where propname = 'defaultval' and propvalue is null and featureid in (select pid from dh_properties where propname = 'drought_pct' and varid = 1356 );

SQL 3: Find all combinations of variable type for the defaultval property.

select b.propname,a.varid,b.varid, count(a.*) from dh_properties as a left outer join dh_properties as b on (a.featureid = b.pid and a.entity_type = 'dh_properties') where a.propname = 'defaultval' and b.varid = 1356 group by a.varid, b.varid, b.propname order by b.propname; 

[rburghol]

Also occurs in Equation types.

ERROR:  invalid input syntax for type double precision: "NA" at character 1864

2025-04-11 10:51:05.365 UTC [2962777] STATEMENT:   
insert into "tmp303684_020_datalog" ( "Qriver" , "wd_last_mgd" , "ps_last_mgd" , "lake_elev" , "drought_status" , 
"pct_full" , "rejected_demand_pct" , "impoundment_use_remain_mg" , "Qreach" , "Qriver_up" , "flowby_current" , 
"thisdate" , "month" , "day" , "year" , "jday" , "week" , "timestamp" , "modays" , "run_mode" , "flow_mode" , 
"historic_annual" , "wd_mgd" , "Send to Parent" , "Listen on Parent" , "discharge_mgd" , "flowby" , "current_mgy" , 
"safe_yield" , "historic_monthly_pct" , "current_monthly_discharge" , "safe_yield_mgy" , "future_mgd" , "current_mgd" , 
"wsp2020_2020_mgy" , "wsp2020_2040_mgy" , "consumption_monthly" , "ps_enabled" , "fac_demand_mgy" , 
"unaccounted_losses" , "fac_demand_mgd" , "riverseg_frac" , "fac_current_mgy" , "vwp_exempt_mgd" , 
"wsp2020_2030_mgy" , "available_mgd" , "base_demand_mgd" , "max_mgd" , "adj_demand_mgd" , "drought_pct" , 
"drought_response_enabled" , "unmet_demand_mgd" , "Qintake" , "gw_frac" , "sw_frac" , "gw_sw_factor" , 
"gw_demand_mgd" , "discharge_from_gw_mgd" , "ustabe_future_mgd" , "safeyield_mgd" , "base_demand_pstatus_mgd" 
, "permit_status" , "vwp_prop_max_mgy" , "vwp_prop_max_mgd" , "vwp_prop_base_mgd" , "vwp_prop_demand_mgd" , 
"vwp_max_mgy" , "vwp_max_mgd" , "vwp_base_mgd" , "vwp_demand_mgd" , "flowby_proposed" , "flowby_historic" , 
"scenario" , "reports" , "ps_local_mgd" , "ps_nextdown_mgd" , "ps_other_mgd" , "discharge_local_mgd" , 
"datacenter_wd_mgd" , "consumption_dc" , "consumption" , "riverseg" )  values ( 
'19.004009537828',0,0,0,0,0,0,'561.56351791531','19.004009537828',0,'0','2014-12-31',
'12','31','2014','364','01','1420002000','31','13','1','0','3.2549306423111','','','2.92943757808','0','492.57','1.9018335483871',
'0.0778','0','757.8','6.75','1.2361918064516','835.22','2092.3113','0','1','2092.3113','0.1','3.2549306423111',
'0.61986559951676','794.64','6.75','1463.76565','NA','3.2549306423111','3.2549306423111','3.2549306423111','0','1',
'0','19.004009537828','NA','NA','0','0','0','1.2361918064516','6.75','3.2549306423111','exempt','0','0','0','0','0','0','0','0','0','0',
'vahydro-1.0','Custom information for automated assembly of CIA reports.','0','2.92943757808','0.0','0','0.0','0','0'
,'RU4_5642_5640')