Enhance METdataio schema to handle changes to the ECNT, VL1L2, VAL1L2, and VCNT line types during the MET 12.0.0 beta4 development cycle

Describe the Enhancement

Issue dtcenter/MET#2583 and pull request dtcenter/MET#2838 adds new columns to the end of the ECNT line type.

Add 2 new ECNT columns: IGN_CONV_OERR and IGN_CORR_OERR

Issue dtcenter/MET#2395 and pull request dtcenter/MET#2820 add new columns to the existing .stat output lines for MET version 12.0.0, as described below:

Add 3 new VL1L2 columns: DIR_ME, DIR_MAE, and DIR_MSE
Add 3 new VAL1L2 columns: DIRA_ME, DIRA_MAE, DIRA_MSE
Add 12 new VCNT columns: DIR_ME, DIR_MAE, DIR_MSE, and DIR_RMSE plus _BCL and _BCU columns to store the bootstrap CI's.

This issue is to update the METdataio loader to parse data from these new columns.

Time Estimate

Estimate the amount of work required here. Issues should represent approximately 1 to 3 days of work.

Sub-Issues

Consider breaking the enhancement down into sub-issues. None needed.

Relevant Deadlines

List relevant project deadlines here or state NONE.

Funding Source

Define the source of funding and account keys here or state NONE.

Define the Metadata

Assignee

[x] Select engineer(s) or no engineer required
[ ] Select scientist(s) or no scientist required

Labels

[x] Select component(s)
[x] Select priority
[x] Select requestor(s)

Projects and Milestone

[x] Select Repository and/or Organization level Project(s) or add alert: NEED CYCLE ASSIGNMENT label
[x] Select Milestone as the next official version or Future Versions

Define Related Issue(s)

Consider the impact to the other METplus components.

[x] METplus, MET, METdataio, METviewer, METexpress, METcalcpy, METplotpy
[x] dtcenter/MET#2395
[x] dtcenter/METdataio#282
[x] dtcenter/METcalcpy#361
[x] dtcenter/METviewer#517

Enhancement Checklist

See the METplus Workflow for details.

[ ] Complete the issue definition above, including the Time Estimate and Funding Source.
[ ] Fork this repository or create a branch of develop. Branch name: feature_<Issue Number>_<Description>
[ ] Complete the development and test your changes.
[ ] Add/update log messages for easier debugging.
[ ] Add/update unit tests.
[ ] Add/update documentation.
[ ] Add any new Python packages to the METplus Components Python Requirements table.
[ ] Push local changes to GitHub.
[ ] Submit a pull request to merge into develop. Pull request: feature <Issue Number> <Description>
[ ] Define the pull request metadata, as permissions allow. Select: Reviewer(s) and Development issues

Select: Repository level development cycle Project for the next official release Select: Milestone as the next official version

[ ] Iterate until the reviewer(s) accept and merge your changes.
[ ] Delete your fork or branch.
[ ] Close this issue.

Outline of work to do:

Add the new columns

METdbLoad/ush/constants.py

[x] LINE_DATA_FIELDS[VCNT]
[x] LINE_DATA_FIELDS[VAL1L2]
[x] LINE_DATA_FIELDS[VL1L2]
[x] LINE_DATA_FIELDS[ECNT]
[x] LC_ECNT_SPECIFIC
[x] LC_VCNT_SPECIFIC
[x] LC_VCNT_STATISTICS_HEADERS
[x] NUM_STAT_VCNT_COLS (update value) from 87 to 99 (addition of 12 new columns total)
[x] NUM_STAT_VL1L2_COLS (update value) from 35 to 38
[x] NUM_STAT_ECNT_COLS from 50 to 52
[x] LC_VCNT_BOOTSTRAP_HEADERS
[x] LC_VL1L2_SPECIFIC
[x] VCNT_BCL_HEADERS
[x] VCNT_BCU_HEADERS

METdbLoad/updates

[x] create a new update_for_5_1_beta4.sql file
[x] add syntax like this to add new columns:

_ALTER TABLE line_data_val1l2 ADD COLUMN dirama DOUBLE |

[x] line_data_vcnt
[x] line_data_vl1l2
[x] line_data_val2l2
[x] line_data_ecnt

in METdbLoad/sql/mv_mysql.sql:

[x] update the line_data_vcnt table with the new columns and their data types (~ line 1632)
[x] update the line_data_ecnt table with the new columns and their data types (~ line 421)
[x] update the line_data_vl1l2 table with the new columns and their data types (~ line 889)
[x] update the line_data_val1l2 table with the new columns and their data types (~ line 927)

Test

[ ] update the Github workflow compare_db.yml
[ ] with new data (that contains the latest column additions) use data on 'seneca': d1/projects/MET/MET_regression/develop/NB20240401/MET-develop/test_output/point_stat
[ ] verify that new data is loading in the database
[x] skip the tests for VL1L2, ECNT, and VCNT in METdataio/METreformat/test/test_reformatting.py for now see https://github.com/dtcenter/METdataio/issues/288

Documentation:

[x] add more information in the Contributor's Guide in the Update the Database Schema section with sample syntax to
add a new column to an existing table.

dtcenter / METdataio