Managing Time Series Dissemination in Schema 22.2.1

Export Script

from hec.cwmsVue import CwmsListSelection
from javax.swing import JFileChooser
from javax.swing import JOptionPane
from javax.swing import UIManager
from javax.swing.filechooser import FileFilter
import datetime, DBAPI, hashlib, os, sys, threading

script_name = "Export TS Dissemenation"
output_filename = None
sql = '''
select 
	cwms_ts_id,
	cwms_data_dissem.get_dest(ts_code)
from
	cwms_v_ts_id
where
	db_office_code = cwms_util.get_db_office_code()
order by
	cwms_ts_id
'''

#
# FileFilter class to restrict choices to .txt  output file (for Excel Query)
#
class TextFileFilter(FileFilter) :
	def __init__(self) :
		pass

	def getDescription(self) :
		return "Text files (.txt)"

	def accept(self, f) :
		return os.path.splitext(f.getName())[1] == ".txt"
#
# routine to perform export (in a separate thread in CWMS-Vue)
#
def do_export(output_filename, main_window) :
	# open the output file
	output_file = open(output_filename, "w")
	#
	# get a database connection
	#
	print("\t%s: Connecting to database..." % str(datetime.datetime.now())[:19])
	if main_window :
		db = DBAPI.open()
	else :
		# command line - try credentials in environment
		dburl = os.getenv("dburl")
		dbusr = os.getenv("dbusr")
		dbpwd = os.getenv("dbpwd")
		dbofc = os.getenv("dbofc")
		if all((dburl, dbusr, dbpwd, dbofc)) :
			db = DBAPI.open(dburl, dbusr, dbpwd, dbofc)
		else :
			# no credentials in environment, use login dialog (slow....)
			db = DBAPI.open()
	#
	# excute the query to get the data
	#
	conn = db.getConnection()
	stmt = conn.prepareStatement(sql)
	print("\t%s: Running query..." % str(datetime.datetime.now())[:19])
	rs = stmt.executeQuery()
	count = 0
	#
	# write the data to the output file
	#
	print("\t%s: Exporting data..." % str(datetime.datetime.now())[:19])
	while rs.next() :
		count += 1
		ts_id = rs.getString(1)
		ts_dest = rs.getInt(2)
		h = hashlib.new("sha256")
		h.update(ts_id)
		ts_hash = h.hexdigest()[-8:]
		output_file.write("%s\t%s\t%s\n" % (ts_hash, "\t".join(ts_id.split(".")), ts_dest))
	output_file.close()
	print("\t%s: %d ts exported to %s" % (str(datetime.datetime.now())[:19], count, output_filename))
	if main_window :
		# pop up a message box when done if in CWMS-Vue
		JOptionPane.showMessageDialog(
			main_window,
			"Done\n%s records exported" % count,
			"TS Dissemination File",
			JOptionPane.INFORMATION_MESSAGE)
	#
	# clean up and exit script
	#
	rs.close()
	stmt.close()
	conn.close()
	if main_window :
		print("#\n# Script %s done\n#" % script_name)
	else :
		db.close()

#
# main script code
#
def main() :
	global output_filename
	if len(sys.argv) > 1 :
		output_filename = sys.argv[1]
	#
	# get the CWMS-Vue window (will be None if running from command line)
	#
	main_window = CwmsListSelection.getMainWindow()
	if main_window :
		print("#\n# Running script %s\n#" % script_name)
	else :
		# set the look and feel to Windows
		UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel")
	#
	# choose an output file
	#
	if not output_filename :
		dlg = JFileChooser()
		dlg.setDialogTitle("TS Dissemination File")
		dlg.setDialogType(JFileChooser.SAVE_DIALOG)
		dlg.setFileFilter(TextFileFilter())
		dlg.setAcceptAllFileFilterUsed(False)
		if dlg.showSaveDialog(main_window) == JFileChooser.APPROVE_OPTION :
			# file selected
			output_filename = dlg.getSelectedFile().getCanonicalPath()
			if os.path.splitext(output_filename)[1] != ".txt" :
				output_filename += ".txt"
		else :
			# Cancel button pressed
			if main_window is not None :
				JOptionPane.showMessageDialog(
					main_window,
					"Canceled. No ts exported",
					script_name,
					JOptionPane.INFORMATION_MESSAGE)
			return
	if os.path.exists(output_filename) :
		# confirm file overwrite
		answer = JOptionPane.showConfirmDialog(
			main_window,
			"The file %s already exists\nDo you want to replace the existing file?" % os.path.split(output_filename)[1],
			"TS Dissemination File",
			JOptionPane.OK_CANCEL_OPTION,
			JOptionPane.WARNING_MESSAGE)
		if answer != JOptionPane.OK_OPTION :
			return
	if main_window :
		threading.Thread(target=do_export, args=(output_filename, main_window)).start()
	else :
		do_export(output_filename, main_window)

if __name__ == "__main__" : main()
PY

Export_TS_Dissemenation.py

Import Script

from hec.cwmsVue import CwmsListSelection
from javax.swing import JFileChooser
from javax.swing import JOptionPane
from javax.swing import UIManager
from javax.swing.filechooser import FileFilter
import datetime, DBAPI, hashlib, os, sys, threading

script_name = "Import TS Dissemenation"
input_filename = None

#
# FileFilter class to restrict choices to .txt  input file (for Excel Query)
#
class TextFileFilter(FileFilter) :
	def __init__(self) :
		pass

	def getDescription(self) :
		return "Text files (.txt)"

	def accept(self, f) :
		return os.path.splitext(f.getName())[1] == ".txt"
#
# routine to remove quote characters 
#
def unquote(str) :
	if len(str) > 1 and str[0] in "\"'" and str[-1] == str[0] :
		return str[1:-1]
	return str
#
# routine to perform export (in a separate thread in CWMS-Vue)
#
def do_import(input_filename, main_window) :
	#
	# parse the input file
	#
	print("\t%s: Parsing input file..." % str(datetime.datetime.now())[:19])
	with open(input_filename) as f :
		lines = f.read().strip().split("\n")
	ts_ids    = []
	ts_dests  = []
	for i in range(len(lines)) :
		line = lines[i]
		line_number = i + 1
		parts = map(unquote, line.split("\t"))
		if line_number == 1 and parts[0] == "Column1" :
			continue
		try :
			assert len(parts) == 8
			ts_hash = parts[0]
			ts_id   = ".".join(parts[1:7])
			ts_dest = int(parts[7])
		except :
			raise Exception("Line %d is invalid: %s" % (line_number, line))
		h = hashlib.new("sha256")
		h.update(ts_id)
		if ts_hash != h.hexdigest()[-8:] :
			raise Exception("Time series ID on line %d has been modified since export: %s" % (line_number, line))
		if not 0 <= ts_dest <= 2 :
			raise Exception("Time series destination line %d is not 0, 1, or 2: %s" % (line_number_line))
		ts_ids.append(ts_id)
		ts_dests.append(ts_dest)
	#
	# determine destination filtering
	#
	if ts_dests == [2] * len(ts_dests) :
		filter_to_corpsnet = filter_to_dmz = 'F'
	elif 0 in ts_dests :
		filter_to_corpsnet = filter_to_dmz = 'T'
	else :
		filter_to_corpsnet = 'F'
		filter_to_dmz = 'T'
	#
	# get a database connection
	#
	print("\t%s: Connecting to database..." % str(datetime.datetime.now())[:19])
	if main_window :
		db = DBAPI.open()
	else :
		# command line - try credentials in environment
		dburl = os.getenv("dburl")
		dbusr = os.getenv("dbusr")
		dbpwd = os.getenv("dbpwd")
		dbofc = os.getenv("dbofc")
		if all((dburl, dbusr, dbpwd, dbofc)) :
			db = DBAPI.open(dburl, dbusr, dbpwd, dbofc)
		else :
			# no credentials in environment, use login dialog (slow....)
			db = DBAPI.open()
	conn = db.getConnection()
	#
	# set the destination filtering
	#
	print("\t%s: Setting destination filtering..." % str(datetime.datetime.now())[:19])
	stmt = conn.prepareCall('''
		begin
			cwms_data_dissem.set_ts_filtering(:1, :2, cwms_util.get_db_office_id);
		end;''')
	stmt.setString(1, filter_to_corpsnet)
	stmt.setString(2, filter_to_dmz)
	stmt.execute()
	stmt.close()
	#
	# clear the destination filters in the ts groups
	#
	print("\t%s: Clearing existing destination filters in ts groups..." % str(datetime.datetime.now())[:19])
	stmt = conn.prepareCall('''
		declare
			l_office_id cwms_v_ts_id.db_office_id%type := cwms_util.get_db_office_id;
			l_ts_groups cwms_t_str_tab := cwms_t_str_tab(
			                                  'CorpsNet Include List',
			                                  'CorpsNet Exclude List',
			                                  'DMZ Include List',
			                                  'DMZ Exclude List');
		begin
			for i in 1.. l_ts_groups.count loop
				cwms_ts.unassign_ts_group(
					p_ts_category_id => 'Data Dissemination',
					p_ts_group_id    => l_ts_groups(i),
					p_ts_id          => null,
					p_unassign_all   => 'T',
					p_db_office_id   => l_office_id);
			end loop;
		end;''')
	stmt.execute()
	stmt.close()
	#
	# create the new destination filters in the ts groups
	#
	print("\t%s: Setting new destination filters in ts groups..." % str(datetime.datetime.now())[:19])
	stmt = conn.prepareStatement('''
		declare
			l_office_id cwms_v_ts_id.db_office_id%type := cwms_util.get_db_office_id;
		begin
			cwms_ts.assign_ts_group (
				p_ts_category_id => 'Data Dissemination',
				p_ts_group_id    => :1,
				p_ts_id          => :2,
				p_db_office_id   => l_office_id);
		end;''')
	if filter_to_dmz == 'T' :
		for i in range(len(ts_ids)) :
			if ts_dests[i] == 2 :
				stmt.setString(1, "DMZ Include List")
				stmt.setString(2, ts_ids[i])
				stmt.execute()
			elif ts_dests[i] == 1 and filter_to_corpsnet == 'T' :
				stmt.setString(1, "CorpsNet Include List")
				stmt.setString(2, ts_ids[i])
				stmt.execute()
	stmt.close()
	conn.commit()
	print("\t%s: %d ts imported from %s" % (str(datetime.datetime.now())[:19], len(ts_ids), input_filename))
	if main_window :
		# pop up a message box when done if in CWMS-Vue
		JOptionPane.showMessageDialog(
			main_window,
			"Done\n%s records imported" % len(ts_ids),
			"TS Dissemination File",
			JOptionPane.INFORMATION_MESSAGE)
	#
	# clean up and exit script
	#
	conn.close()
	if main_window :
		print("#\n# Script %s done\n#" % script_name)
	else :
		db.close()

#
# main script code
#
def main() :
	global input_filename
	if len(sys.argv) > 1 :
		input_filename = sys.argv[1]
	#
	# get the CWMS-Vue window (will be None if running from command line)
	#
	main_window = CwmsListSelection.getMainWindow()
	if main_window :
		print("#\n# Running script %s\n#" % script_name)
	else :
		# set the look and feel to Windows
		UIManager.setLookAndFeel("com.sun.java.swing.plaf.windows.WindowsLookAndFeel")
	#
	# choose an input file
	#
	if not input_filename :
		dlg = JFileChooser()
		dlg.setDialogTitle("TS Dissemination File")
		dlg.setDialogType(JFileChooser.OPEN_DIALOG)
		dlg.setFileFilter(TextFileFilter())
		dlg.setAcceptAllFileFilterUsed(False)
		if dlg.showOpenDialog(main_window) == JFileChooser.APPROVE_OPTION :
			# file selected
			input_filename = dlg.getSelectedFile().getCanonicalPath()
			if os.path.splitext(input_filename)[1] != ".txt" :
				input_filename += ".txt"
		else :
			# Cancel button pressed
			if main_window is not None :
				JOptionPane.showMessageDialog(
					main_window,
					"Canceled. No ts imported",
					script_name,
					JOptionPane.INFORMATION_MESSAGE)
			return
	if main_window :
		threading.Thread(target=do_import, args=(input_filename, main_window)).start()
	else :
		do_import(input_filename, main_window)

if __name__ == "__main__" : main()
PY

Import_TS_Dissemination.py

Background

Local time series are possibly disseminated to one of the following destinations:

  • CorpsNet
  • DMZ

All Time series values are assigned a dissemination destination of one of three values:

  • 0 - Do not disseminate
  • 1 - Disseminate to CorpsNet only
  • 2 - Disseminate to (CorpsNet and) DMZ

There is no setting for disseminating to DMZ only.

Data dissemination is controlled by whether a time series is included in a materialized view named MV_TS_CODE_FILTER that is refreshed once per hour. The CMA application is used to set whether a time series is disseminated, and all disseminated time series have a destination of 2 (DMZ).

With database schema 22.2.1 control of dissemination is changed to:

  • Whether 0, 1, or 2 of dissemination filter switches are turned on:
    • Filter to CorpsNet
      • OFF - All time series are disseminated to CorpsNet, regardless of assignment to data dissemination time series groups
      • ON - Time series are disseminated based on their assignment to the various data dissemination time series groups
    • Filter to DMZ
      • OFF (Requires Filter to CorpsNet to also be OFF)  - All time series are disseminated to DMZ, regardless of assignment to data dissemination time series groups
      • ON - Time series are disseminated based on their assignment to the various data dissemination time series groups
  • Based, on the state of the dissemination filter switches, whether the time series is assigned to one or more of the data dissemination time series groups that are used as destination filters.
    • If Filter to CorpsNet is ON, time series are disseminated to CorpsNet only if both of the following are true:
      • The time series is assigned to the "CorpsNet Include List" data dissemination time series group
      • The time series is not assigned to the "CorpsNet Exclude List" data dissemination time series group
    • If Filter to DMZ is ON, the time series are disseminated to DMZ only if all of the following are true:
      • The time series is assigned to the "DMZ Include List" data dissemination time series group
      • The time series is not assigned to the "DMZ Exclude List" data dissemination time series group
      • The time series is not assigned to the "CorpsNet Exclude List" data dissemination time series group (it need not be assigned to the "CorpsNet Include LIst" dissemination time series group.

Before Schema Update

  • Dissemination is controlled by MV_TS_CODE_FILTER
  • No time series are assigned to the data dissemination time series groups

After Schema Update

  • Dissemination is controlled by the dissemination filter switches and assignment to data dissemination time series groups
  • Both dissemination filter switches are ON and all time series in MV_TS_CODE_FILTER are assigned to the "DMZ Include List" group. No time series are assigned to any other data dissemination group. (Caveat: If all your time series were in MV_TS_CODE_FILTER then both switches are ON and no time series will be assigned to any data dissemination group, though this situation is unexpected.)

While you technically can manage which time series are assigned to the data dissemination groups in CWMS-Vue, it isn't easy for groups with many assignments. The assigned time series are neither sortable nor filterable, and you cannot select multiple time series at once. Assigning time series to a group is a little more sophisticated as the catalog may be sorted and filtered by a single string, and multiple selections can be made.

Managing Time Series Dissemination with CWMS-Vue Scripts and Excel

Until CWMS-Vue is updated to more reasonably manage the time series dissemination, Excel with CWMS-Vue scripts to export and import the time series dissemination provides a workable alternative.

Running the Export Script

Just execute the script and select the name of the output file.

Editing the Dissemination Information in Excel

Import your output file with Data→From Text/CSV and select the default load option.

  • Don't edit data in any columns except for the destination column (H, labeled "Column8"). If the values in any other column are changed, the import script will fail to execute. Column A, labeled "Column1" is a short hash of the time series identifier that is used by the import script to ensure the time series identifier hasn't changed.
  • Don't add rows to the data. If a time series has been added to the database since you ran the export script, simply run it again to include the new time series.
  • Don't delete rows from the data. In most cases this would simply result in the deleted time series not being disseminated at all. However if no rows remain with destination values of 0 then the import script will off the Filter to DMZ and/or the Filter to CorpsNet filter switches, which is probably not what you want.
  • Re-run the export script to make corrections. If the data gets to be in an undesired state (even after the import script has been run), simply re-run the export script and start editing again.

Use the sorting and filtering to display desired time series. In the example below I'm moving everything with a version of Rev-SCADA and a parameter other than Elev or Elev-Tailwater from the DMZ destination (2) to the CorpsNet only destination (1).

First I filter the destination column to keep me from accidentally adding any time series to CorpsNet that were previously not disseminated.

In a similar manner I filter on the version and parameter columns to select my desired time series, then set the destination for all of them to 1

After making all the modifications, save the file using File→Save As. Be sure to select "Text (Tab delimited) (*.txt)" as the file type. I'm saving over the file I exported, but that is not necessary. Note that you will get a warning about not being able to save multiple sheets to the file type. Select OK.

Running the Import Script

Just run the script and select the name of the file you saved from Excel.

After the Import

You should see the changes you made reflected in CWMS-Vue after refreshing the catalog.

You can use CWMS-Vue to make changes in time series assignments to the data dissemination time series groups if you want. Any changes you make will be reflected in the data exported the next time the export script is run.