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’