Appendix G.32
G.32 Automated Scripts (Listing and Calling Order)
- Tables
- D_BOREHOLE
- D_INTERVAL_FORM_ASSIGN
- D_INTERVAL_FORM_ASSIGN_FINAL
- D_INTERVAL_MONITOR
- D_INTERVAL_SUMMARY
- D_INTERVAL_TEMPORAL_2
- D_LOCATION_GEOM
- D_LOCATION_SUMMARY
- D_VERSION_STATUS
- W_GENERAL
- W_GENERAL_DOCUMENT
- W_GENERAL_GW_LEVEL
- W_GENERAL_LOC_MET
- W_GENERAL_LOC_SW
- W_GENERAL_OTHER
- W_GENERAL_SCREEN
- W_GEOLOGY_LAYER
- Recurrence Time: Weekly
A variety of automated updates are performed against the database on a weekly basis (beginning on Saturday and ending on Sunday; note that the start times, listed subsequently, are relative to midnight Saturday.) These can be broken into the following general groups (as listed in processing order)
- Update D_INTERVAL_FORM_ASSIGN (and related)
- Update D_LOCATION_SUMMARY (location by area)
- Update D_INTERVAL_SUMMARY
- Update D_LOCATION_SUMMARY
- Update D_LOCATION_GEOM
- Update D_VERSION_STATUS
- Miscellaneous updates
- Update all W_GENERAL_* tables
- Database backup (and restore)
- Miscellaneous daily updates
G.32.1 Update D_INTERVAL_FORM_ASSIGN (and related)
- Main Script: d_int_form_ass.bat
- Starting Time: -05:30
The following scripts are called (listed in order of processing)
- Removal of those INT_IDs not present in CM2004 (rem_from_difa_cm2004.bat)
- Add INT_IDs present in CM2004 (add_to_difa_cm2004.bat)
- Create a temporay table containing CM2004 information (tmp_cm2004_int.bat)
- Update D_INTERVAL_FORM_ASSIGN for the CM2004 model and remove the temporary table (upd_diffa_cm2004.bat)
This is repeated for each geologic model being evaluated (currently WB2018 and YT32011). This will have a similar naming structure as listed above.
The ASSIGNED_UNIT is then populated for each geologic model (upd_difa_au.bat).
The thicknesses for each aquifer unit for each geologic model is then determined for each interval/location using the following scripts
- Get the CM2004 ORAC thickness (tmp_gl_cm2004_orac.bat)
- Update the THICKNESS_M field for the applicable records (upd_difa_cm2004_orac.bat)
- Get the CM2004 Thorncliffe thickness (tmp_gl_cm2004_thorn.bat)
- Update the THICKNESS_M field for the applicable records (upd_difa_cm2004_thorn.bat)
- Get the CM2004 Scarborough thickness (tmp_gl_cm2004_scar.bat)
- Update the THICKNESS_M field for the applicable records (upd_difa_cm2004_scar.bat)
- Get the CM2004 Channel Sand thickness (tmp_gl_cm2004_chansa.bat)
- Update the THICKNESS_M field for the applicable records (upd_difa_cm2004_chansa.bat)
This is repeated for each geologic model and their associated aquifers (WB2018: ORAC, Thorncliffe and Scarborough; YT32011: ORAC, Thorncliffe, Scarborough and Channel Sands). These will have a similar naming scheme to the above.
The values of specific capacity (SC_LPMM), transmissivity (T) and hydraulic conductivity (K) can then be calculated (upd_difa_calc_tk.bat). The associated fields will be updated.
The D_INTERVAL_FORM_ASSIGN_FINAL table can be updated, including
- Removal of INT_IDs (for those not found in D_INTERVAL_FORM_ASSIGN and will NULL values for OVERRIDE_UNIT and MANUAL_UNIT; rem_from_difa_f.bat)
- Add missing INT_IDs (i.e. present in D_INTERVAL_FORM_ASSIGN and absent from D_INTERVAL_FORMA_ASSIGN_FINAL; add_to_difa_f.bat)
- Update the ASSIGNED_UNIT field (upd_au_difa_f.bat)
G.32.2 Update D_LOCATION_SUMMARY (location by area)
- Main Script: d_loc_summary_area.bat
- Starting Time: 00:00
Each partner conservation area and region is examined and the associated records have their CA_AREA_ID, REG_AREA_ID and SWP_AREA_ID fields updated in D_LOCATION_SUMMARY. This is determined using the following scripts (listed in order of processing)
- CLOCA (cloca_area_d_loc_sum.bat)
- CVC (cvc_area_d_loc_sum.bat)
- Durham Region (durham_area_d_loc_sum.bat)
- GRCA (grca_area_d_loc_sum.bat)
- Halton Region (halton_area_d_loc_sum.bat)
- KCA (kca_area_d_loc_sum.bat)
- LSRCA (lsrca_area_d_loc_sum.bat)
- LTRCA (ltrca_area_d_loc_sum.bat)
- NVCA (nvca_area_d_loc_sum.bat)
- ORVA (orca_area_d_loc_sum.bat)
- Peel Region (peel_area_d_loc_sum.bat)
- SWP CTC (swp_ctc_area_d_loc_sum.bat)
- SWP LS (swp_ls_area_d_loc_sum.bat)
- SWP Trent (swp_trent_area_d_loc_sum.bat)
- City of Toronto (Toronto_area_d_loc_sum.bat)
- TRCA (trca_area_d_loc_sum.bat)
- York Region (York_area_d_loc_sum.bat)
G.32.3 Update D_INTERVAL_SUMMARY
- Main Script: d_int_sum.bat
- Starting Time: 02:00
Records and fields found in D_INTERVAL_SUMMARY (DIS) are now updated (in order of processing)
- Add missing INT_IDs (add_to_d_int_sum.bat)
- Remove INT_IDs not found in D_INTERVAL (rem_from_d_int_sum.bat)
- Update water levels (update_d_int_sum_wl.bat)
- Update manual water levels (update_d_int_sum_wl_man.bat)
- Update logger water levels (update_d_int_sum_wl_log.bat)
- Update average water levels (update_d_int_sum_wl_avg.bat)
- Update water quality (update_d_int_sum_wa.bat)
- Update water quality samples (update_d_int_sum_wq_samp.bat)
- Update precipitation (update_d_int_sum_precip.bat)
- Update pumping readings (update_d_int_sum_pump.bat)
- Update pumping daily volumnes (update_d_int_sum_pump_daily_vol.bat)
- Update streamflow readings (update_d_int_sum_sflow.bat)
- Update specific capacity (update_d_int_sum_spec_cap.bat)
- Update air temperature readings (update_d_int_sum_temp_air.bat)
G.32.4 Update D_LOCATION_SUMMARY
- Main Script: d_loc.sum.bat
- Starting Time: 02:30
Records and fields found in D_LOCATION_SUMMARY (DLS) are now updated (in order of processing)
- Add missing LOC_IDs (add_to_d_loc_sum.bat)
- Remove LOC_IDs not found in D_LOCATION (rem_from_d_loc_sum.bat)
- Update deepest screen top elevation (update_d_loc_sum_deep_scr_top.bat)
- Update number of geologic layers (update_d_loc_sum_geol_lay_num.bat)
- Update number of monitors/screens present (update_d_loc_sum_mon_num.bat)
- Update total number of water levels (update_d_loc_sum_wl_total.bat)
- Update total number of water quality readings (update_d_loc_sum_wa_total.bat)
- Update total number of water quality samples (update_d_loc_sum_wq_total_samp.bat)
- Update total number of precipitation readings (update_d_loc_sum_precip_total.bat)
- Update total number of pump readings (update_d_loc_sum_pump_total.bat)
- Update total number of streamflow readings (update_d_loc_sum_sflow_total.bat)
- Update minimum, maximum and average streamflow readings (update_d_loc_sum_sflow_avgminmax.bat)
- Update soil readings (update_d_loc_sum_soil.bat)
- Update air temperature (update_d_loc_sum_temp_air.bat)
G.32.5 Update D_LOCATION_GEOM
- Main Script: d_loc_geom.bat
- Starting Time: 03:00
Records and fields found in D_LOCATION_GEOM (DLG) are now updated (in order of processing)
- Remove LOC_IDs missing from D_LOCATION (rem_from_d_loc_geom.bat)
- Check current coordinates (in D_LOCATION) against the current calculated geometry (tag them as necessary; coord_check_d_loc_geom.bat)
- Add any new LOC_IDs (add_to_d_loc_geom.bat)
- Update any blank GEOM fields (upd_d_loc_geom.bat)
- Update any blank GEOM_WKB fields (update_d_loc_geom_wkb.bat)
G.32.6 Miscellaneous updates
- Main Script: various.bat
- Starting Time: 03:30
A variety of checks and updates are performed here (in a number of tables, in order of processing)
- Update locations in D_BOREHOLE that no longer have a bedrock formation (rem_bed_elev.bat)
- Update locations in D_BOREHOLE that have a bedrock formation (upd_bed_elev.bat)
- Add new LOC_IDs (including coordinates and elevations) to D_LOCATION_SPATIAL_HIST (add_to_d_loc_spat_hist.bat)
- Update D_LOCATION_SPAT adding newly assigned coordinates and elevations (add_to_d_loc_spat.bat)
- Update the LOC_ACTIVE field for PTTW locations (upd_pttw_active.bat)
- Update the LOC_STATUS_CODE for climate stations (upd_climate_active.bat)
- Update the LOC_STATUS_CODE for spotflow locations (upd_sw_spotflow_active.bat)
- Update the LOC_STATUS_CODE for streamflow gauges (upd_sw_gauge_active.bat)
- Update MON_TOP_DEPTH_M and MON_BOT_DEPTH_M in D_INTERVAL_MONITOR (upd_dim_depths.bat)
- Update the reference elevations and stick-ups in D_INTERVAL_REF_ELEV based upon a change in REF_POINT (upd_dire_stick_up.bat)
G.32.7 Update all W_GENERAL_* tables
- Main Script: w_gen_all.bat
- Starting Time: 04:00
The contents of all the W_GENERAL_* tables are removed and the tables re-populated (in order of processing)
- Delete all rows in W_GENERAL (WG; rem_from_w_gen.bat)
- Add all rows to WG (add_to_w_gen.bat)
- Delete all rows in W_GENERAL_DOCUMENT (WGC; rem_from_w_gen_doc.bat)
- Add all rows to WGC (add_to_w_gen_doc.bat)
- Delete all rows in W_GENERAL_OTHER (WGO; rem_from_w_gen_other.bat)
- Add all rows to WGO (add_to_w_gen_other.bat)
- Modify STATUS of PTTW locations in WGO (update_w_gen_other_pttw_active.bat)
- Delete all rows in W_GENERAL_SCREEN (WGS; rem_from_w_gen_scr.bat)
- Add all rows to WGS (add_to_w_gen_scr.bat)
- Delete all rows in W_GENERAL_GW_LEVEL (WGGL; rem_from_w_gen_gw_level.bat)
- Add daily logger water levels to WGGL (add_to_w_gen_gw_level_log.bat)
- Add all manual water levels to WGGL (add_to_w_gen_gw_level_man.bat)
- Delete all rows in W_GENERAL_LOC_MET (WGLM; rem_from_w_gen_loc_met.bat)
- Add all rows to WGLM (add_to_w_gen_loc_met.bat)
- Delete all rows in W_GENERAL_LOC_SW (WGLS; rem_from_w_gen_loc_sw.bat)
- Add all rows to WGLS (add_to_w_gen_loc_sw.bat)
- Add URLs for non-MOE borehole PDFs (in W_GENERAL and W_GENERAL_SCREEN; upd_w_gen_nonmoe_bh_pdf.bat)
- Update those locations that can be used for Piper plots (using the field WQ_CB_PIPER; upd_wq_cb_piper.bat)
- Update SPEC_CAP_LPMM in W_GENERAL_SCREEN (upd_spec_cap_lpmm.bat)
- Delete all rows from W_GEOLOGY_LAYER (WGL; rem_from_w_geol_lay.bat)
- Add all rows to WGL (add_to_w_geol_lay.bat)
- Create a temporary table containing shallow water levels (wls_create_tmp.bat)
- Update SHALLOW_WL_MASL and SHALLOW_WL_DEPTH_M in W_GENERAL; drop the temporary table (wls_update.bat)
G.32.8 Update D_VERSION_STATUS
- Main Script: d_var_stat.bat
- Starting Time: 5:00
Records are added to the D_VERSION_STATUS table capturing the status of the database. This includes:
- Adding location type counts (add_loc_type_counts.bat)
- Adding interval type counts (add_int_type_counts.bat)
- Adding reading group type counts (add_group_type_counts.bat)
G.32.9 Database backup (and restore)
- Main Script: see below
- Starting Time: 12:00
Each of the following are processed (in turn) to backup the master database and make available the OAK_20160831_WEEKLY database. This includes
- Update the OAK_20160831_WEEKLY database from the backup of OAK_20160831_MASTER (db_restore.bat) [12:00]
- Move the OAK_20160831_MASTER backup file to the B: drive on SQLSERVER2k16 (i.e. the temporary backup location; db_backup.bat) [14:00]
- Modify the permissions of various users to allow full access to OAK_20160831_WEEKLY (db_restore_permissions.bat) [14:55]
- Split the backup of the master database to allow easy transfer off-site (db_backup_split.bat) [15:30; now disabled]
- Move the backup file of the master database to B:\backup (i.e. the final backup location; db_backup_final.bat)
G.32.10 Miscellaneous daily updates
- Main script: various_daily.bat
- Starting time (daily): 22:00 (10:00pm)
Each of the following is processed, in turn:
- Update those records in D_INTERVAL_TEMPORAL_2 that have ‘cmap’ or ‘map’ as their original RD_UNIT_OUOM value; converts these to ‘mbref’