FVS: New FVSOut_BioSum.db with Access-friendly data types

[lbross]

Start creating an FVSOut_BioSum.db with Access-friendly data types. The SQLite TEXT datatypes comes across as MEMO in MS Access and does not work well with queries. We talked about adding this initially to the Create MDBs function, but instead I'd like to add it to the pick list on the FVS Out screen. The Create MDBs function takes a long time to run, so I'd prefer not to make it any longer. Also, it will be easier for debugging/testing to have it as a discrete task. We will need to think about how this will work with versioning. It would be nice to create the FVS PRE/POST tables from this .db (when we get there) since the data types will all be valid. But it's possible there could be a disconnect if an updated FVSOut.db is generated without regenerating this file. It's possible to do an incremental load base on variant/package but that could prove difficult if the updated FVSOut.db has some different tables/columns. It seems safest to have it be a complete rewrite. Is there any versioning in the FVSOut.db that we could look to to see if its changed since the FVSOut_BioSum.db creation? @jsfried to review FVS output and suggest data type mapping.

[lbross]

@jsfried reviewed FVS output and approved the following data type mapping except for FIASPECIES:

switch (dataTypeFrom)
{
  case "SYSTEM.INT32":
    convertedType = "LONG";
    break;
  case "SYSTEM.DOUBLE":
    convertedType = "DOUBLE";
    break;
  case "SYSTEM.STRING":
    convertedType = "VARCHAR(255)";
    break;
 }

@lbross reviewed a recent FVSOut.db and could not find the FIASPECIES column in the tree or cut list tables. There is a SpeciesFIA column that is a TEXT field. There is mapping in the MDB exporter that maps this column to the SPECIES column in the MS Access .mdbs. This column is a short text field in Access.

@jsfried indicated this column should migrated to an INTEGER column in the FVSOut_BioSum.db. Is the name of the column actually SpeciesFIA or am I looking in the wrong place?

[jsfried]

Yes it is SpeciesFIA, sorry about that. @lbross

[lbross]

@jsfried or @sebbusby Looking at the FVSOut.db for JaJoSi59. There are tables in here that start with Cmp, FVS, and Std. I know you want all of the ones with the FVS prefix. What about the other tables? The amount of effort isn't much different for me one way or the other but the process will take longer and the database will be larger if we include tables that won't be used.

[sebbusby]

@jsfried and @lbross The Cmp tables contain FVS run-level averages (average values across all stands contained in a single run).

There is only ever one Std table: StdStk

StdStk contains trees, basal area, total volume, merchantable volume, and board volume by diameter class and species, for before-thinning live trees, harvested trees, mortality trees during the cycle, and residual after-thinning live trees.

[jsfried]

Cmp tables do not look like they would be useful in a BioSum context. StdStk can be a very useful table. Is it organized such that there would be a pre and post record? If so, then it could be used in BioSum Optimizer. If not, the analyst could still access this table -- there would be one such table per FVS OUT file right? Then the only benefit of bringing it into the PREPOST tables is that the tables from multiple variants could be combined into one table for the project. @lbross @sebbusby

[sebbusby]

@jsfried I believe it is organized such that one can derive a pre and post record, but it is a species-level table; there is a record for all FVS cycles (years) for each individual species present in each individual condition. There is no StandID column in the table, but records can be linked from StdStk.CaseID to FVS_Cases.CaseID and then FVS_Cases.StandID.

[jsfried]

OK, so definitely not a good candidate for use in optimizer. On those grounds, I'd say leave it behind then. @sebbusby @lbross

[lbross]

Just to clarify. This request is to create a duplicate of the FVSOut.db (generated by FVS) but with the data types converted from SQLite data types to MS Access data types so that the tables can be more reliably queried from the MS Access client. The data will not be manipulated into pre/post format. BioSum itself may use them eventually when we switch the FVS Out processing from MS Access to SQLite. If you think these are tables that would or could be useful for queries outside of BioSum then I recommend leaving them in.

[jsfried]

Thanks for the clarification and revised advice. I can't seem to think straight today. Yes, those would be potentially valuable tables to access via Access.

[lbross]

This is enhancement is coming along nicely. However, I noticed today that there are many fields in some of the Cmp tables and the StdStk that don't have any data type at all! This is completely legal in SQLite but causes MS Access to treat these fields as the dreaded long text. Do we still want to bring these tables into the FVSOut_Biosum.db? Maybe Sebastian can check with FVS support to see if this is something they can fix? It does seem kind of sloppy.

[sebbusby]

@lbross @jsfried Well, ain't that something! I can certainly bring up the issue with FVS Support but I highly doubt there will be a quick resolution rolled out. We could include those tables and for now, inform users somewhere about the datatype limitations, with the hope that one day it will be resolved.

[lbross]

Look into adding RunTitle from FVS_Cases to the tables in the FVS_Out_BioSum.db. Move this step to the end of the steps on the FVS Out screen and append (opt) to the pick list entry so it is clear this step is not a prequisite to FVS Out.

[sebbusby]

I am getting an error when attempting to create the FVSOut_BioSum.db after running prior FVSOut steps first in the Douglas_510 project. Occurs when progress bar gets to "Inserting records into FVS_Cutlist". Error message: "Module - uc_fvs_output:RunCreateFVSOut_BioSum_Main Err Msg - Value of '30' is not valid for "Value". "Value" should be between 'minimum' and 'maximum'. Parameter name: Value". The error stops the process but the FVSOut_BioSum.db file is still populated with data. Attached is the log for that process from the fvs folder. FVSOut_BioSum_2023-02-02_11-35-46.txt

[sebbusby]

RE: FVS: Fix bug with progress indicator when post-processor audit runs prior to FVSOut_Biosum table creation step.

No longer getting the bug, appears to be fixed.

[lbross]

Thanks @sebbusby. Do the contents of the FVSOut_BioSum.db look good in MS Access? If so, then I vote for closing this one.

[jsfried]

I built my FVSOUT_BioSum.db March 2nd. If the code has changed since then, this may not apply, but that version had YEAR as LongText in several tables and many others in other tables. LONGTEXT is toxic to use in Access.

[lbross]

@jsfried: I see what you see with the current build. I re-used the code that translates SQLite data types to MS Access data types from the 'Create .MDB's' function. In that algorithm, INTEGER is mapped to LONG (aka LONG INTEGER) when creating an MS Access table. This has been working fine. However, it seems that the ODBC driver interprets the LONG datatype (from an SQLite table) as LONGTEXT, which as Jeremy pointed out, is a problem on the Access side. I will carve out an exception for this and it will be fixed in the next .msi.

[jsfried]

@lbross: yes, I am aware that long integer is fine (as long as data is meant to be an integer and not have a decimal portion). It is the long text that is a problem for Access (can't filter, sort, use in queries, etc.). Glad that will be straightened out in the final msi for 5.10.1.

[lbross]

From @jsfried: The FVSOUT_BIOSUM.DB is awesome! Every data type in every table made me happy!