def validate_sql_connection(config=None, verbose=False):
"""
:param config: login credentials defining 'host', 'dbname', 'port' and optionally 'user' and 'password'
:type config: dict
:param verbose: indicates if cmd line printing should be performed
:type verbose: bool
:return: True if configuration is valid
:rtype: bool
"""
valid = True
if config:
try:
with DVH_SQL(config) as cnx:
pass
except:
valid = False
else:
try:
with DVH_SQL() as cnx:
pass
except:
valid = False
if verbose:
if valid:
print("SQL DB is alive!")
else:
print("Connection to SQL DB could not be established.")
return valid
def update_study_dates(self):
with DVH_SQL() as cnx:
choices = cnx.get_unique_values('Plans', 'sim_study_date', "mrn = '%s'" % self.mrn)
self.combo_box_study_date.SetItems(choices)
if choices:
self.combo_box_study_date.SetValue(choices[0])
self.update_uids()
def ignore_dvh(self):
unignore = self.button_uncategorized_ignored_ignore.GetLabelText(
) == 'Unignore DVH'
with DVH_SQL() as cnx:
for uid in self.dvh_uids:
cnx.ignore_dvh(self.dvh, uid, unignore=unignore)
self.update_uncategorized_ignored_choices()
def uid_has_ptvs(study_instance_uid):
with DVH_SQL() as cnx:
ans = cnx.query(
'DVHs', 'roi_type',
"study_instance_uid = '%s' and roi_type LIKE 'PTV%%'" %
study_instance_uid)
return bool(ans)
def update_db(self):
with DVH_SQL() as cnx:
try:
cnx.update(self.table, self.column, self.value, self.condition)
except SQLError as sql_exception:
SQLErrorDialog(self, sql_exception)
self.error = True
def update_columns(self):
table = self.combo_box_table.GetValue()
with DVH_SQL() as cnx:
columns = cnx.get_column_names(table)
self.combo_box_column.SetItems(columns)
if self.combo_box_column.GetValue() not in columns:
self.combo_box_column.SetValue(columns[0])
def update_range(self, evt):
key = self.combo_box_1.GetValue()
table = self.numerical_categories[key]['table']
col = self.numerical_categories[key]['var_name']
units = self.numerical_categories[key]['units']
with DVH_SQL() as cnx:
min_value = cnx.get_min_value(table, col)
max_value = cnx.get_max_value(table, col)
self.button_date_picker.Enable('date' in key.lower())
self.text_ctrl_min.Enable('date' not in key.lower())
self.text_ctrl_max.Enable('date' not in key.lower())
self.set_min_value(min_value)
self.set_max_value(max_value)
if 'date' in key.lower():
self.update_min_max_text('Start:', 'End:')
self.on_date_picker()
elif units:
self.update_min_max_text('Min (%s):' % units, 'Max (%s):' % units)
else:
self.update_min_max_text('Min:', 'Max:')
self.clean_numeric_input()
def get_plan_values(self, plan_column):
"""
Get values from the Plans table and store in order matching mrn / study_instance_uid
:param plan_column: name of the SQL column to be queried
:type plan_column: str
:return: values from the Plans table for the DVHs stored in this class
:rtype: list
"""
with DVH_SQL() as cnx:
condition = "study_instance_uid in ('%s')" % "','".join(
self.study_instance_uid)
data = cnx.query('Plans', 'study_instance_uid, %s' % plan_column,
condition)
force_date = cnx.is_sqlite_column_datetime(
'Plans', plan_column) # returns False for pgsql
uids = [row[0] for row in data]
values = [row[1] for row in data]
if force_date: # sqlite does not have date or time like variables
for i, value in enumerate(values):
try:
if type(value) is int:
values[i] = str(date_parser(str(value)))
else:
values[i] = str(date_parser(value))
except Exception:
values[i] = 'None'
return [values[uids.index(uid)] for uid in self.study_instance_uid]
def action(self):
old_id = self.text_ctrl_1.GetValue()
new_id = self.text_ctrl_2.GetValue()
with DVH_SQL() as cnx:
validation_func = [cnx.is_uid_imported,
cnx.is_mrn_imported][self.sql_column == 'mrn']
change_func = [cnx.change_uid,
cnx.change_mrn][self.sql_column == 'mrn']
if validation_func(old_id):
if self.sql_column == 'study_instance_uid' and validation_func(
new_id):
wx.MessageBox(
'This Study Instance UID is already in use.',
'%s Error' %
self.combo_box_patient_identifier.GetValue(),
wx.OK | wx.ICON_WARNING)
else:
change_func(old_id, new_id)
else:
wx.MessageBox(
'No studies found with this %s.' %
self.combo_box_patient_identifier.GetValue(),
'%s Error' % self.combo_box_patient_identifier.GetValue(),
wx.OK | wx.ICON_WARNING)
def update_ptv_data(tv, study_instance_uid):
"""
:param tv: treatment volume formatted as a "sets of points" object specified in tools.roi_geometry
:type tv: dict
:param study_instance_uid: study_instance_uid in SQL database
:type study_instance_uid: str
"""
ptv_cross_section = roi_geom.cross_section(tv)
ptv_spread = roi_geom.spread(tv)
condition = "study_instance_uid = '%s' and roi_type like 'PTV%%'" % study_instance_uid
with DVH_SQL() as cnx:
max_dose = cnx.get_max_value('dvhs', 'max_dose', condition=condition)
min_dose = cnx.get_min_value('dvhs', 'min_dose', condition=condition)
ptv_data = {'ptv_cross_section_max': ptv_cross_section['max'],
'ptv_cross_section_median': ptv_cross_section['median'],
'ptv_max_dose': max_dose,
'ptv_min_dose': min_dose,
'ptv_spread_x': ptv_spread[0],
'ptv_spread_y': ptv_spread[1],
'ptv_spread_z': ptv_spread[2],
'ptv_surface_area': roi_geom.surface_area(tv, coord_type='sets_of_points'),
'ptv_volume': roi_geom.volume(tv)}
for key, value in ptv_data.items():
cnx.update('Plans', key, value, "study_instance_uid = '%s'" % study_instance_uid)
def get_uncategorized_variations(physician, ignored_variations=False):
if echo_sql_db():
with DVH_SQL() as cnx:
physician = clean_name(physician).upper()
condition = "physician_roi = '%s'" % [
'uncategorized', 'ignored'
][ignored_variations]
cursor_rtn = cnx.query('dvhs', 'roi_name, study_instance_uid',
condition)
new_variations = {}
for row in cursor_rtn:
variation = str(row[0])
study_instance_uid = str(row[1])
physician_db = cnx.get_unique_values(
'Plans', 'physician',
"study_instance_uid = '%s'" % study_instance_uid)
if physician_db and physician_db[0] == physician:
if variation not in list(new_variations):
new_variations[variation] = {
'roi_name': variation,
'study_instance_uid': [study_instance_uid]
}
else:
new_variations[variation][
'study_instance_uid'].append(
study_instance_uid)
return new_variations
def query(table, column, condition):
"""
Automatically creates connection and for query
"""
with DVH_SQL() as cnx:
ans = cnx.query(table, column, condition)
return ans
def recalculate_plan_complexities_from_beams():
with DVH_SQL() as cnx:
uids = cnx.get_unique_values('Plans', 'study_instance_uid')
for uid in uids:
try:
condition = "study_instance_uid = '%s'" % uid
beam_complexities = cnx.query(
'Beams', 'fx_count, complexity, fx_grp_number', condition)
complexity = {}
fx_counts = {}
for row in beam_complexities:
fx_count, beam_complexity, fx_group_number = tuple(row)
if fx_group_number not in complexity:
complexity[fx_group_number] = 0.0
fx_counts[fx_group_number] = fx_count
complexity[fx_group_number] += beam_complexity
total_fx = float(sum([fx for fx in fx_counts.values()]))
plan_complexity = sum([
c * fx_counts[fx_grp] for fx_grp, c in complexity.items()
]) / total_fx
except Exception as e:
msg = "tools.utilities.recalculate_plan_complexities_from_beams: failed on uid = %s" % uid
push_to_log(e, msg=msg)
plan_complexity = None
if plan_complexity is not None:
cnx.update('Plans', 'complexity', plan_complexity, condition)
def on_query(self, evt):
self.update_selected_tree_items()
table = self.combo_box_query_table.GetValue()
columns = [
c for c, sel in self.selected_columns[table].items()
if sel and c not in {'mrn', 'study_instance_uid'}
]
columns.sort()
if not columns:
columns = [
c for c in self.db_tree[table]
if c not in {'mrn', 'study_instance_uid'}
]
columns.insert(0, 'study_instance_uid')
columns.insert(0, 'mrn')
condition = self.text_ctrl_condition.GetValue()
with DVH_SQL() as cnx:
try:
data = cnx.query(table,
','.join(columns),
condition,
bokeh_cds=True)
self.data_query_results.set_data(data, columns)
except SQLError as e:
SQLErrorDialog(self, e)
self.data_query_results.clear()
def remap_rois(self):
with DVH_SQL() as cnx:
for physician, variations in self.variations_to_update.items():
for variation in variations:
new_physician_roi = self.get_physician_roi(
physician, variation)
new_institutional_roi = self.get_institutional_roi(
physician, new_physician_roi)
condition = "REPLACE(REPLACE(LOWER(roi_name), '\'', '`'), '_', ' ') == '%s'" % variation
sql_query = "SELECT DISTINCT study_instance_uid, roi_name FROM DVHs WHERE %s;" % condition
uids_roi_names = cnx.query_generic(sql_query)
uids = [row[0] for row in uids_roi_names]
roi_names = [row[1] for row in uids_roi_names]
if uids:
for i, uid in enumerate(uids):
roi_name = roi_names[i]
condition = "roi_name = '%s' and study_instance_uid = '%s'" % (
roi_name, uid)
cnx.update('dvhs', 'physician_roi',
new_physician_roi, condition)
cnx.update('dvhs', 'institutional_roi',
new_institutional_roi, condition)
self.write_to_file()
def action(self):
with DVH_SQL() as cnx:
dicom_files = cnx.get_dicom_file_paths(uid=self.uid)
if self.delete_from_db:
cnx.delete_rows("study_instance_uid = '%s'" % self.uid)
move_imported_dicom_files(dicom_files, INBOX_DIR)
ImportDicomFrame(inbox=INBOX_DIR)
def update_uncategorized_rois_in_database():
roi_map = DatabaseROIs()
dvh_data = QuerySQL('DVHs', "physician_roi = 'uncategorized'")
with DVH_SQL() as cnx:
for i in range(len(dvh_data.roi_name)):
uid = dvh_data.study_instance_uid[i]
mrn = dvh_data.mrn[i]
physician = get_physician_from_uid(uid)
roi_name = dvh_data.roi_name[i]
new_physician_roi = roi_map.get_physician_roi(physician, roi_name)
new_institutional_roi = roi_map.get_institutional_roi(
physician, roi_name)
if new_physician_roi != 'uncategorized':
print(mrn,
physician,
new_institutional_roi,
new_physician_roi,
roi_name,
sep=' ')
condition = "study_instance_uid = '" + uid + "'" + "and roi_name = '" + roi_name + "'"
cnx.update('DVHs', 'physician_roi', new_physician_roi,
condition)
cnx.update('DVHs', 'institutional_roi', new_institutional_roi,
condition)
def __init__(self, table_name, condition_str, unique=False, columns=None):
"""
:param table_name: 'Beams', 'DVHs', 'Plans', or 'Rxs'
:type table_name: str
:param condition_str: condition in SQL syntax
:type condition_str: str
:param unique: If set to True, only unique values stored
:type unique: bool
"""
table_name = table_name.lower()
if table_name in {'beams', 'dvhs', 'plans', 'rxs'}:
self.table_name = table_name
self.condition_str = condition_str
with DVH_SQL() as cnx:
all_columns = cnx.get_column_names(table_name)
if columns is not None:
columns = set(all_columns).intersection(columns) # ensure provided columns exist in SQL table
else:
columns = all_columns
for column in columns:
if column not in {'roi_coord_string', 'distances_to_ptv'}: # ignored for memory since not used here
self.cursor = cnx.query(self.table_name,
column,
self.condition_str)
force_date = cnx.is_sqlite_column_datetime(self.table_name, column) # returns False for pgsql
rtn_list = self.cursor_to_list(force_date=force_date)
if unique:
rtn_list = get_unique_list(rtn_list)
setattr(self, column, rtn_list) # create property of QuerySQL based on SQL column name
else:
print('Table name in valid. Please select from Beams, DVHs, Plans, or Rxs.')
def action(self):
if self.text_ctrl_2.GetValue() == 'delete':
value = self.text_ctrl_1.GetValue()
with DVH_SQL() as cnx:
dicom_files = cnx.get_dicom_file_paths(**{self.sql_column: value})
cnx.delete_rows("%s = '%s'" % (self.sql_column, value))
DeleteFilesFromQuery(self, dicom_files)
def action(self):
with DVH_SQL() as cnx:
dicom_files = cnx.get_dicom_file_paths(uid=self.uid)
if self.delete_from_db:
cnx.delete_rows("study_instance_uid = '%s'" % self.uid)
inbox = self.options.INBOX_DIR
move_imported_dicom_files(dicom_files, inbox)
ImportDicomFrame(self.roi_map, self.options, inbox=inbox, auto_parse=True)
def action_yes(self):
with DVH_SQL() as cnx:
cnx.reinitialize_database()
ImportDicomFrame(self.roi_map,
self.options,
inbox=self.options.IMPORTED_DIR,
auto_parse=True)
def get_physician_from_uid(uid):
with DVH_SQL() as cnx:
results = cnx.query('Plans', 'physician', "study_instance_uid = '" + uid + "'")
if len(results) > 1:
print('Warning: multiple plans with this study_instance_uid exist')
return str(results[0][0])
def query(table, column, condition, unique=False):
"""
Automatically creates connection for query
"""
with DVH_SQL() as cnx:
func = cnx.get_unique_values if unique else cnx.query
ans = func(table, column, condition)
return ans
def get_physician_from_uid(uid):
with DVH_SQL() as cnx:
results = cnx.query('Plans', 'physician', "study_instance_uid = '" + uid + "'")
if len(results) > 1:
msg = 'roi_name_manager.get_physician_from_uid: multiple plans with this study_instance_uid exist: %s' % uid
push_to_log(msg=msg)
return str(results[0][0])
def get_database_tree():
"""
Query SQL to get all columns of each table
:return: column data sorted by table
:rtype: dict
"""
with DVH_SQL() as cnx:
tree = {table: cnx.get_column_names(table) for table in cnx.tables}
return tree
def mrn_ticker(self, evt):
with DVH_SQL() as cnx:
is_mrn_valid = cnx.is_mrn_imported(self.mrn)
if is_mrn_valid:
self.update_study_dates()
else:
self.combo_box_study_date.SetItems([])
self.combo_box_uid.SetItems([])
self.button_reimport.Disable()
def update_category_2(self, evt):
key = self.combo_box_1.GetValue()
table = self.selector_categories[key]['table']
col = self.selector_categories[key]['var_name']
with DVH_SQL() as cnx:
options = cnx.get_unique_values(table, col)
self.combo_box_2.Clear()
self.combo_box_2.Append(options)
if options:
self.combo_box_2.SetValue(options[0])
def run(self):
res = self.ShowModal()
if res == wx.ID_OK:
new_config = {key: self.input[key].GetValue() for key in self.keys if self.input[key].GetValue()}
if echo_sql_db(new_config, db_type=self.selected_db_type):
self.write_successful_cnf()
with DVH_SQL(group=self.group) as cnx:
cnx.initialize_database()
else:
dlg = wx.MessageDialog(self, 'Connection to database could not be established.', 'ERROR!',
wx.OK | wx.ICON_ERROR)
dlg.ShowModal()
self.Destroy()
def update_uids(self):
date = ['is NULL', "= '%s'::date" % self.sim_study_date
][self.sim_study_date != 'None']
condition = "mrn = '%s' and sim_study_date %s" % (self.mrn, date)
with DVH_SQL() as cnx:
choices = cnx.get_unique_values('Plans', 'study_instance_uid',
condition)
self.combo_box_uid.SetItems(choices)
if choices:
self.combo_box_uid.SetValue(choices[0])
else:
self.combo_box_uid.SetValue(None)
self.uid_ticker(None)
def update_dvhs_table(study_instance_uid, roi_name, column, value):
"""
Generic function to update a value in the DVHs table
:param study_instance_uid: study instance uid in the SQL table
:type study_instance_uid: str
:param roi_name: the roi name associated with the value to be updated
:type roi_name: str
:param column: the SQL column of the value to be updated
:type column: str
:param value: the value to be set, it's type should match the type as specified in the SQL table
"""
with DVH_SQL() as cnx:
cnx.update('dvhs', column, value,
"study_instance_uid = '%s' and roi_name = '%s'" % (study_instance_uid, roi_name))