def setCacheData(self, key, value, conn=None):
"""Utility function to set cached data item in data_cache table"""
extConn = conn is not None
if not extConn:
conn = self.connFactory.connection()
# Clear any prior setting to make way for the new one
self.clearCacheData(key, conn=conn)
insertQuery = DBUtil.buildInsertQuery(
"data_cache", ("data_key", "data_value", "last_update"))
insertParams = (key, str(value), datetime.now())
DBUtil.execute(insertQuery, insertParams, conn=conn)
if not extConn:
conn.close()
def _executeCachedDBQuery(self, query):
# Only query if results not in cache.
cacheKey = str(query)
if cacheKey not in self.dbCache:
self.dbCache[cacheKey] = DBUtil.execute(query)
return self.dbCache[cacheKey]
def test_insertFile_escapeStrings(self):
# Create a test data file to insert, and verify no errors
DBUtil.runDBScript(
self.SCRIPT_FILE,
False) # Assume this works based on test_runDBScript method
tableName = "TestTypes"
columnNames = ["MyInteger", "MyText"]
dataFile = StringIO()
dataFile.write('''-1\t"A"\n''')
dataFile.write('''-2\t"B\xaeb"\n''')
dataFile.write('''-3\t"C"\n''')
dataFile.write('''-4\tD\n''')
dataFile = StringIO(dataFile.getvalue())
DBUtil.insertFile(dataFile, tableName, columnNames, escapeStrings=True)
verifyQuery = \
"""select MyInteger, MyText
from TestTypes
where MyInteger < 0
order by MyInteger desc
"""
expectedData = \
[ [ -1, "A"],
[ -2, "B\\xaeb"],
[ -3, "C"],
[ -4, "D"],
]
# Verify rows inserted with properly parsed dates
results = DBUtil.execute(verifyQuery)
self.assertEqual(expectedData, results)
def loadClinicalItemBaseCountByItemId(self, countPrefix=None, acceptCache=True, conn=None):
"""Helper query to get the baseline analyzed item counts for all of the clinical items
If countPrefix is provided, can use alternative total item counts instead of the default item_count,
such as patient_count or encounter_count to match the respective association query baselines used.
"""
extConn = True;
if conn is None:
conn = self.connFactory.connection();
extConn = False;
try:
# First ensure the denormalized count data is updated
self.updateClinicalItemCounts(acceptCache=acceptCache, conn=conn);
if countPrefix is None or countPrefix == "":
countPrefix = "item_"; # Default to general item counts, allowing for repeats per patient
baseCountQuery = SQLQuery();
baseCountQuery.addSelect("clinical_item_id");
baseCountQuery.addSelect("%scount" % countPrefix);
baseCountQuery.addFrom("clinical_item");
if acceptCache:
baseCountResultTable = self.executeCacheOption( baseCountQuery, conn=conn );
else:
baseCountResultTable = DBUtil.execute( baseCountQuery, conn=conn );
baseCountByItemId = dict();
for (itemId, baseCount) in baseCountResultTable:
baseCountByItemId[itemId] = baseCount;
return baseCountByItemId;
finally:
if not extConn:
conn.close();
def queryItems(self, options, outputFile):
"""Query for all clinical item records that fulfill the options criteria
and then send the results as tab-delimited output to the outputFile.
"""
pauseSeconds = float(options.pauseSeconds)
query = SQLQuery()
query.addSelect(
"cic.description, ci.clinical_item_id, ci.name, ci.description")
query.addFrom("clinical_item_category as cic")
query.addFrom("clinical_item as ci")
query.addWhere(
"cic.clinical_item_category_id = ci.clinical_item_category_id")
if options.itemPrefix:
query.addWhereOp("ci.description", "like",
options.itemPrefix + "%%")
# Add wildcard to enabe prefix search
if options.categoryNames:
query.addWhereIn("cic.description",
options.categoryNames.split(","))
query.addOrderBy(
"cic.description, ci.name, ci.description, ci.clinical_item_id")
formatter = TextResultsFormatter(outputFile)
prog = ProgressDots()
for row in DBUtil.execute(query,
includeColumnNames=True,
connFactory=self.connFactory):
formatter.formatTuple(row)
time.sleep(pauseSeconds)
prog.update()
prog.printStatus()
def _test_dataConversion_aggregate(self):
# Run the data conversion on the same data and look for expected records
log.debug("Run the conversion process...")
convOptions = ConversionOptions()
convOptions.startDate = TEST_START_DATE
convOptions.aggregate = True
self.converter.convertSourceItems(convOptions)
# Just query back for the same data, de-normalizing the data back to a general table
testQuery = \
"""
select
pi.external_id as pi_external_id,
pi.patient_id,
pi.encounter_id,
cic.description as cic_description,
ci.external_id as ci_external_id,
ci.name,
ci.description as ci_description,
pi.item_date
from
%s.patient_item as pi,
%s.clinical_item as ci,
%s.clinical_item_category as cic
where
pi.clinical_item_id = ci.clinical_item_id and
ci.clinical_item_category_id = cic.clinical_item_category_id and
cic.source_table = '%s'
order by
pi.external_id desc, ci.external_id desc
""" % (TEST_DEST_DATASET, TEST_DEST_DATASET, TEST_DEST_DATASET, TEST_SOURCE_TABLE)
expectedData = \
[]
actualData = DBUtil.execute(testQuery)
self.assertEqualTable(expectedData, actualData)
def action_default(self):
# Read checkboxes by presence or absence of field
self.requestData[
"incCols"] = "" # Checkboxes not passed if unchecked, so extra step to ensure uncheck is persisted
incCols = False
if "incCols" in self.mForm:
self.requestData["incCols"] = self.mForm["incCols"].value
incCols = True
# Point to the specified database
connFactory = self.connectionFactory()
timer = time.time()
# Just execute a normal query, possibly with a result set
results = DBUtil.execute(self.mForm["input"].value,
includeColumnNames=incCols,
connFactory=connFactory)
if type(results) == list: # Result set, format as table
formatter = TextResultsFormatter(StringIO())
formatter.formatResultSet(results)
self.requestData["resultsText"] = formatter.getOutFile().getvalue()
headerRowFormat = None
if incCols: headerRowFormat = "th"
formatter = HtmlResultsFormatter(StringIO(), headerRowFormat)
formatter.formatResultSet(results)
self.requestData["resultsHtml"] = formatter.getOutFile().getvalue()
self.requestData["resultsInfo"] = "(%d rows) " % len(results)
else:
self.requestData[
"resultsText"] = "%d rows affected (or other return code)" % results
timer = time.time() - timer
self.requestData["resultsInfo"] += "(%1.3f seconds)" % timer
def queryPatients(period, locations, rxCount):
log.info("Select patients fitting criteria in designated time period: (%s,%s)" % period);
query = SQLQuery();
query.addSelect("med.pat_id");
query.addSelect("count(order_med_id)");
query.addFrom("stride_mapped_meds as map");
query.addFrom("stride_order_med as med");
query.addFrom("stride_patient as pat");
query.addWhere("analysis_status = 1");
query.addWhere("map.medication_id = med.medication_id");
query.addWhere("med.pat_id = pat.pat_id");
query.addWhere("possible_oncology = 0");
query.addWhereIn("patient_location", locations );
query.addWhereOp("ordering_datetime",">", period[0] );
query.addWhereOp("ordering_datetime","<", period[-1] );
query.addGroupBy("med.pat_id");
query.addHaving("count(order_med_id) >2");
results = DBUtil.execute(query);
cols = ["patientId","nOpioidRx"];
patientDF = pd.DataFrame(results,columns=cols);
#patientDF.set_index("patientId",drop=False,inplace=True);
patientDF["periodStart"] = period[0]; # Identify this group of patient records
return patientDF;
def _get_average_orders_per_patient(self):
# Initialize DB cursor.
cursor = self._connection.cursor()
# Get average number of results for this lab test per patient.
query = SQLQuery()
if LocalEnv.DATASET_SOURCE_NAME == 'STRIDE': #TODO: add STRIDE component routine
query.addSelect('CAST(pat_id AS BIGINT) AS pat_id')
query.addSelect('COUNT(sop.order_proc_id) AS num_orders')
query.addFrom('stride_order_proc AS sop')
query.addFrom('stride_order_results AS sor')
query.addWhere('sop.order_proc_id = sor.order_proc_id')
query.addWhereIn("proc_code", [self._lab_panel])
components = self._get_components_in_lab_panel()
query.addWhereIn("base_name", components)
query.addGroupBy('pat_id')
elif LocalEnv.DATASET_SOURCE_NAME == 'UMich':
query.addSelect('CAST(pat_id AS BIGINT) AS pat_id')
query.addSelect('COUNT(order_proc_id) AS num_orders')
query.addFrom('labs')
query.addWhereIn(self._varTypeInTable, [self._lab_var])
components = self._get_components_in_lab_panel()
query.addWhereIn("base_name", components)
query.addGroupBy('pat_id')
log.debug('Querying median orders per patient...')
results = DBUtil.execute(query)
order_counts = [row[1] for row in results]
if len(order_counts) == 0:
error_msg = '0 orders for lab "%s."' % self._lab_var
log.critical(error_msg)
raise Exception(error_msg)
# sys.exit('[ERROR] %s' % error_msg) # sxu: sys.exit cannot be caught by Exception
else:
return numpy.median(order_counts)
def loadRespiratoryViralPanelItemIds(extractor):
# labCategoryId = 6;
labCategoryId = DBUtil.execute(
"select clinical_item_category_id from clinical_item_category where description like 'Lab'"
)[0][0]
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addFrom("clinical_item")
query.addWhereEqual("analysis_status", 1)
query.addWhereIn("clinical_item_category_id", (labCategoryId, ))
query.addWhere("description ~* '%s'" % 'Respiratory.*Panel')
respiratoryViralPanelItemIds = set()
for row in DBUtil.execute(query):
respiratoryViralPanelItemIds.add(row[0])
return respiratoryViralPanelItemIds
def queryClinicalItems(outputFile, clinicalItemIds, patientById):
log.info("Query Clinical Items: %s" % str(clinicalItemIds))
formatter = TextResultsFormatter(outputFile)
colNames = ["patient_id", "item_date"]
query = SQLQuery()
for col in colNames:
query.addSelect(col)
query.addFrom("patient_item")
query.addWhereIn("clinical_item_id", clinicalItemIds)
query.addWhereIn("patient_id", patientById.keys())
query.addOrderBy("patient_id")
query.addOrderBy("item_date")
DBUtil.execute(query, includeColumnNames=True, formatter=formatter)
def _get_random_patient_list(self):
# Initialize DB cursor.
cursor = self._connection.cursor()
# Get average number of results for this lab test per patient.
avg_orders_per_patient = self._get_average_orders_per_patient()
log.info('avg_orders_per_patient: %s' % avg_orders_per_patient)
# Based on average # of results, figure out how many patients we'd
# need to get for a feature matrix of requested size.
self._num_patients = int(numpy.max([self._num_requested_episodes / \
avg_orders_per_patient, 1]))
# Get numPatientsToQuery random patients who have gotten test.
# TODO(sbala): Have option to feed in a seed for the randomness.
query = SQLQuery()
query.addSelect('pat_id')
query.addFrom('stride_order_proc AS sop')
query.addWhereIn('proc_code', [self._lab_panel])
query.addOrderBy('RANDOM()')
query.setLimit(self._num_patients)
log.debug('Querying random patient list...')
results = DBUtil.execute(query)
# Get patient list.
random_patient_list = [row[0] for row in results]
return random_patient_list
def createPatient(self, patientData, initialStateId, conn=None):
"""Create a new patient record given the data dictionary.
Create a respective initial patient state record as well.
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
DBUtil.insertRow("sim_patient", patientData, conn=conn)
patientId = DBUtil.execute(DBUtil.identityQuery("sim_patient"),
conn=conn)[0][0]
patientStateData = {
"sim_patient_id": patientId,
"sim_state_id": initialStateId,
"relative_time_start": 0
}
DBUtil.insertRow("sim_patient_state", patientStateData, conn=conn)
conn.commit()
# Transactional commit for two step process
return patientId
finally:
if not extConn:
conn.close()
def executeCacheOption(self,
query,
parameters=None,
includeColumnNames=False,
incTypeCodes=False,
formatter=None,
conn=None,
connFactory=None,
autoCommit=True):
"""Wrap DBUtil.execute. If instance's dataCache is present, will check and store any results in there
to help reduce time for repeat queries.
Beware, bad idea to store lots of varied, huge results in this cache, otherwise memory leak explosion.
"""
if connFactory is None:
connFactory = self.connFactory
dataCache = self.dataCache
if dataCache is None:
dataCache = dict()
# Create a temporary holder
queryStr = DBUtil.parameterizeQueryString(query)
if queryStr not in dataCache:
dataCache[queryStr] = DBUtil.execute(query, parameters,
includeColumnNames,
incTypeCodes, formatter, conn,
connFactory, autoCommit)
self.queryCount += 1
dataCopy = list(dataCache[queryStr])
return dataCopy
def load_stride_to_psql():
# Build clean data files.
StrideLoader.build_clean_csv_files()
# Build psql schemata.
StrideLoader.build_stride_psql_schemata()
# Build paths to clean data files.
clean_data_dir = StrideLoader.fetch_clean_data_dir()
for raw_file in sorted(STRIDE_LOADER_PARAMS.keys()):
params = STRIDE_LOADER_PARAMS[raw_file]
# Build clean data file.
clean_file = params['clean_file'] % TABLE_PREFIX
log.info('loading %s...' % clean_file)
clean_path = os.path.join(clean_data_dir, clean_file)
# Uncompress data file.
unzipped_clean_path = clean_path[:-3]
with gzip.open(clean_path,
'rb') as f_in, open(unzipped_clean_path,
'wb') as f_out:
shutil.copyfileobj(f_in, f_out)
# psql COPY data from clean files into DB.
psql_table = params['psql_table'] % TABLE_PREFIX
log.debug('stride/data/clean/%s ==> %s' % (clean_file, psql_table))
# In some cases, two files going to the same table will have
# non-identical column names. Pass these explicitly so that
# psql knows which columns to try to fill from file.
# Strip the newline character.
with open(unzipped_clean_path, 'r') as f_in:
columns = f_in.readline()[:-1]
command = "COPY %s (%s) FROM '%s' WITH (FORMAT csv, HEADER);" % (
psql_table, columns, unzipped_clean_path)
DBUtil.execute(command)
# Delete unzipped_clean_path.
os.remove(unzipped_clean_path)
# Run any one-off postprocessing transformations which all users
# of the STRIDE database should receive. Defer any application-specific
# transformations to other modules.
StrideLoader.process_stride_psql_db()
# Build indices.
StrideLoader.build_stride_psql_indices()
def tearDown(self):
"""Restore state from any setUp or test steps"""
log.info("Purge test records from the database")
DBUtil.execute \
( """delete from patient_item_collection_link
where item_collection_item_id in
( select item_collection_item_id
from item_collection_item as ici, item_collection as ic
where ici.item_collection_id = ic.item_collection_id
and ic.external_id < 0
);
"""
)
DBUtil.execute \
( """delete from item_collection_item
where item_collection_id in
( select item_collection_id
from item_collection as ic
where ic.external_id < 0
);
"""
)
DBUtil.execute("delete from item_collection where external_id < 0;")
DBUtil.execute \
( """delete from patient_item
where clinical_item_id in
( select clinical_item_id
from clinical_item as ci, clinical_item_category as cic
where ci.clinical_item_category_id = cic.clinical_item_category_id
and cic.source_table = 'stride_order_proc'
);
"""
)
DBUtil.execute \
( """delete from clinical_item
where clinical_item_category_id in
( select clinical_item_category_id
from clinical_item_category
where source_table = 'stride_order_proc'
);
"""
)
DBUtil.execute(
"delete from clinical_item_category where source_table = 'stride_order_proc';"
)
DBUtil.execute(
"delete from stride_orderset_order_proc where order_proc_id in (%s)"
% str.join(",", self.orderProcIdStrList))
DBUtil.execute(
"delete from stride_order_proc where order_proc_id in (%s)" %
str.join(",", self.orderProcIdStrList))
DBTestCase.tearDown(self)
def loadPatientOrders(self,
patientId,
currentTime,
loadActive=True,
conn=None):
"""Load orders for the given patient that exist by the specified current time point.
loadActive - Specify whether to load active vs. inactive/completed orders. Set to None to load both
"""
extConn = True
if conn is None:
conn = self.connFactory.connection()
extConn = False
try:
query = SQLQuery()
query.addSelect("po.sim_patient_order_id")
query.addSelect("po.sim_user_id")
query.addSelect("po.sim_patient_id")
query.addSelect("po.sim_state_id")
query.addSelect("po.clinical_item_id")
query.addSelect("po.relative_time_start")
query.addSelect("po.relative_time_end")
query.addSelect("ci.name")
query.addSelect("ci.description")
query.addSelect("cic.source_table")
query.addSelect("cic.description as category_description")
query.addFrom("sim_patient_order as po")
query.addFrom("clinical_item as ci")
query.addFrom("clinical_item_category as cic")
query.addWhere("po.clinical_item_id = ci.clinical_item_id")
query.addWhere(
"ci.clinical_item_category_id = cic.clinical_item_category_id")
query.addWhereEqual("sim_patient_id", patientId)
query.addWhereOp("relative_time_start", "<=", currentTime)
if loadActive: # Filter out inactive orders here.
query.openWhereOrClause()
query.addWhere("relative_time_end is null")
query.addWhereOp("relative_time_end", ">", currentTime)
query.closeWhereOrClause()
#elif loadActive is not None: # Filter out active orders here.
# query.addWhereOp("relative_time_end","<=", currentTime)
if loadActive: # Organize currently active orders by category
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
query.addOrderBy("relative_time_start")
else: # Otherwise chronologic order
query.addOrderBy("relative_time_start")
query.addOrderBy("cic.description")
query.addOrderBy("ci.description")
dataTable = DBUtil.execute(query,
includeColumnNames=True,
conn=conn)
dataModels = modelListFromTable(dataTable)
return dataModels
finally:
if not extConn:
conn.close()
def querySourceItems(self, convOptions, progress=None, conn=None):
"""Query the database for list of all source clinical items (culture results, etc.)
and yield the results one at a time. If startDate provided, only return items whose
occurence date is on or after that date.
"""
extConn = conn is not None;
if not extConn:
conn = self.connFactory.connection();
# Column headers to query for that map to respective fields in analysis table
headers = ["order_proc_anon_id","pat_anon_id","pat_enc_csn_anon_id","proc_code","organism_name","antibiotic_name","suseptibility", "shifted_result_time"];
query = SQLQuery();
for header in headers:
query.addSelect( header );
query.addFrom("stride_culture_micro");
# TODO: FIGURE OUT WHY CAN"T DO >= OPERATION HERE
# if convOptions.startDate is not None:
# query.addWhereOp("shifted_result_time",">=", convOptions.startDate);
# if convOptions.endDate is not None:
# query.addWhereOp("shifted_result_time","<", convOptions.endDate); # Still use begin date as common filter value
# Query to get an estimate of how long the process will be
if progress is not None:
progress.total = DBUtil.execute(query.totalQuery(), conn=conn)[0][0];
cursor = conn.cursor();
# Do one massive query, but yield data for one item at a time.
cursor.execute( str(query), tuple(query.params) );
row = cursor.fetchone();
while row is not None:
rowModel = RowItemModel( row, headers );
if rowModel['shifted_result_time'] is None: # Don't add if no result time given
row = cursor.fetchone();
continue
if rowModel['organism_name'] is not None: # if positive culture but results uninterpretable, don't add feature
if rowModel['suseptibility'] is None or rowModel['antibiotic_name'] == 'Method' or rowModel['antibiotic_name'] is None:
row = cursor.fetchone();
continue
# So that we don't run into directory issues later when writing temp files
try:
rowModel['antibiotic_name'] = rowModel['antibiotic_name'].replace('/', '-')
except: # When antibiotic name is none
pass
yield rowModel; # Yield one row worth of data at a time to avoid having to keep the whole result set in memory
row = cursor.fetchone();
# Slight risk here. Normally DB connection closing should be in finally of a try block,
# but using the "yield" generator construct forbids us from using a try, finally construct.
cursor.close();
if not extConn:
conn.close();
def removeItemCollectionTablesAddedLines(self, source_table):
"""delete added records"""
log.info(
'Removing item_collection_item and item_collection added lines in PSQL DB'
)
DBUtil.execute("""delete from item_collection_item ici
using clinical_item ci, clinical_item_category cic
where ici.clinical_item_id = ci.clinical_item_id
and ci.clinical_item_category_id = cic.clinical_item_category_id
and cic.source_table = '{}';
""".format(source_table),
conn=self.pgConn)
# TODO should be using source_table also
DBUtil.execute("delete from item_collection where true;",
conn=self.pgConn)
def loadBloodCultureItemIds(extractor):
# microCategoryId = 15;
microCategoryId = DBUtil.execute(
"select clinical_item_category_id from clinical_item_category where description like 'Microbiology'"
)[0][0]
# Look for diagnostic tests indicating suspected infection / sepsis
query = SQLQuery()
query.addSelect("clinical_item_id")
query.addFrom("clinical_item")
query.addWhereEqual("analysis_status", 1)
query.addWhereIn("clinical_item_category_id", (microCategoryId, ))
query.addWhere("description ~* '%s'" % 'Blood Culture')
bloodCultureItemIds = set()
for row in DBUtil.execute(query):
bloodCultureItemIds.add(row[0])
return bloodCultureItemIds
def test_updateFromFile(self):
# Create a test data file to insert, and verify no errors
DBUtil.runDBScript( self.SCRIPT_FILE, False ) # Assume this works based on test_runDBScript method
# Insert some blank data first to update
for idValue in self.ID_DATA:
DBUtil.execute("insert into TestTypes ("+self.ID_COL+") values (%s)",(idValue,));
# Negative test case
results = DBUtil.execute( self.DATA_QUERY );
self.assertNotEqual( self.DATA_ROWS, results );
# Now do the actual update from the file
DBUtil.updateFromFile( self.DATA_FILE, self.DATA_TABLE, self.COL_NAMES, delim="\t" );
results = DBUtil.execute( self.DATA_QUERY );
self.assertEqual( self.DATA_ROWS, results );
def accessLogFromSourceItem(self, sourceItem, user, metric, metricLine, conn):
# Produce an access log for the given sourceItem with links to the lookup user and metric
# Only record once for multi-line descriptions, so check the line number
accessLog = None;
if metricLine == 1:
accessLog = \
RowItemModel \
( { "user_id": user["user_id"],
"de_pat_id": sourceItem["de_pat_id"],
"metric_id": metric["metric_id"],
"access_datetime": sourceItem["access_datetime"],
}
);
insertQuery = DBUtil.buildInsertQuery("access_log", accessLog.keys() );
insertParams= accessLog.values();
DBUtil.execute( insertQuery, insertParams, conn=conn );
return accessLog;
def compositeRelated(self, clinicalItemIds, itemName, itemDescription, categoryId, compositeId=None, conn=None):
"""A new clinical item will be created, with patient item records created to match every one currently
matching one of the specified clinical items.
Parameters specify new composite item name/code, description, and clinical item category to be created under.
Option to explicitly specify the composite clinical item Id value rather than taking a sequence number value (convenient for test cases)
Returns ID of newly created item
Depending on context, may wish to deactivateAnalysis of component items once this composite one is created
if they are no longer of interest.
Newly created composite item's default_recommend attribute will be reset to 0 since it presumably does not represent a
discrete order item.
Linking records will be created in clinical_item_link between the composite and and component clinical items
so that these relationships can be reconstructed
Examples this could be relevant for:
ICUVasopressors to include all vasopressor infusions (dopamine, norepinephrine, epinephrine, vasopressin, etc.)
All blood transfusion indexes, G vs J vs Feeding tube equivalent, Ear, Eyes med routes irrelevant which ear/eye.
Eventually lump together medication classes (e.g., any "PPI" same difference as choosing pantoprazole or omeprazole.
Eventually lump together diagnosis codes by major prefix to reduce granularity and improve general signal.
"""
extConn = True;
if conn is None:
conn = self.connFactory.connection();
extConn = False;
try:
# Build new composite item
compositeItem = RowItemModel();
compositeItem["name"] = itemName;
compositeItem["description"] = itemDescription;
compositeItem["clinical_item_category_id"] = categoryId;
compositeItem["default_recommend"] = 0;
if compositeId is not None:
compositeItem["clinical_item_id"] = compositeId;
insertQuery = DBUtil.buildInsertQuery("clinical_item", list(compositeItem.keys()) );
insertParams= list(compositeItem.values());
DBUtil.execute( insertQuery, insertParams, conn=conn);
if compositeId is None:
compositeId = DBUtil.execute( DBUtil.identityQuery("clinical_item"), conn=conn )[0][0]; # Retrieve the just inserted item's ID
self.generatePatientItemsForCompositeId(clinicalItemIds, compositeId, conn=conn);
return compositeId;
finally:
if not extConn:
conn.close();
def resetAssociationModel(self, conn=None):
extConn = True;
if conn is None:
conn = self.connFactory.connection();
extConn = False;
try:
log.debug("Connected to database for reseting purposes");
result = DBUtil.execute("DELETE FROM clinical_item_association;", conn=conn);
log.debug("Training table cleared items: %s" % result);
# Droppings constraints can greatly speed up the next step of updating analyze dates
#curs.execute("ALTER TABLE backup_link_patient_item drop constraint backup_link_patient_item_patient_item_fkey;")
#curs.execute("ALTER TABLE patient_item_collection_link drop constraint patient_item_collection_link_patient_fkey;")
#curs.execute("ALTER TABLE patient_item drop constraint patient_item_pkey;")
#curs.execute("ALTER TABLE patient_item drop constraint patient_item_clinical_item_fkey;")
#curs.execute("drop index index_patient_item_clinical_item_id_date;")
#curs.execute("drop index index_patient_item_patient_id_date;")
#curs.execute("drop index index_patient_item_external_id;")
#curs.execute("drop index index_patient_item_encounter_id_date;")
#curs.execute("ALTER TABLE patient_item drop constraint patient_item_composite;")
result = DBUtil.execute("UPDATE patient_item SET analyze_date = NULL where analyze_date is not NULL;", conn=conn);
log.debug("Analyze_date set to NULL: %s" % result);
# Add back constraints
#curs.execute("ALTER TABLE patient_item ADD CONSTRAINT patient_item_pkey PRIMARY KEY (patient_item_id);")
#curs.execute("ALTER TABLE patient_item ADD CONSTRAINT patient_item_clinical_item_fkey FOREIGN KEY (clinical_item_id) REFERENCES clinical_item(clinical_item_id);")
#curs.execute("CREATE INDEX index_patient_item_clinical_item_id_date ON patient_item(clinical_item_id, item_date);")
#curs.execute("CREATE INDEX index_patient_item_patient_id_date ON patient_item(patient_id, item_date);")
#curs.execute("CREATE INDEX index_patient_item_external_id ON patient_item(external_id, clinical_item_id);")
#curs.execute("CREATE INDEX index_patient_item_encounter_id_date ON patient_item(encounter_id, item_date);")
#curs.execute("ALTER TABLE patient_item ADD CONSTRAINT patient_item_composite UNIQUE (patient_id, clinical_item_id, item_date);")
#curs.execute("ALTER TABLE backup_link_patient_item ADD CONSTRAINT backup_link_patient_item_patient_item_fkey FOREIGN KEY (patient_item_id) REFERENCES patient_item(patient_item_id);")
#curs.execute("ALTER TABLE patient_item_collection_link ADD CONSTRAINT patient_item_collection_link_patient_fkey FOREIGN KEY (patient_item_id) REFERENCES patient_item(patient_item_id);")
# Flag that any cached association metrics will be out of date
self.clearCacheData("analyzedPatientCount",conn=conn);
# Reset clinical_item denormalized counts
self.updateClinicalItemCounts(conn=conn);
conn.commit();
log.debug("Connection committed");
finally:
if not extConn:
conn.close();
def test_runDBScript_commandline(self):
# Equivalent to test_runDBScript, but try higher level interface
# through command-line "main" method
origStdin = sys.stdin
sys.stdin = self.SCRIPT_FILE
argv = ["DBUtil.py", "--script", "-"]
DBUtil.main(argv)
sys.stdin = origStdin
# Run some other commands to see if scripts produced expected results
results = DBUtil.execute(
"select * from TestTypes where MyInteger > %s", (200, ))
self.assertEqual(2, len(results))
results = DBUtil.execute(
"select * from TestTypes where MyInteger < %s", (100, ))
self.assertEqual(0, len(results))
def tearDown(self):
"""Restore state from any setUp or test steps"""
log.info("Purge test records from the database")
DBUtil.execute("delete from clinical_item_association where clinical_item_id < 0");
DBUtil.execute("delete from patient_item where patient_item_id < 0");
DBUtil.execute("delete from clinical_item where clinical_item_id < 0");
DBUtil.execute("delete from clinical_item_category where clinical_item_category_id in (%s)" % str.join(",", self.clinicalItemCategoryIdStrList) );
DBTestCase.tearDown(self);
def test_copyPatientTemplate(self):
# Copy a patient template, including deep copy of notes, orders, states, but only up to relative time zero
newPatientData = {
"name": "Template Copy"
}
templatePatientId = -1
self.testPatientId = self.manager.copyPatientTemplate(
newPatientData, templatePatientId)
futureTime = 1000000
# Far future time to test that we still only copied the results up to time zero
# Verify basic patient information
patientCols = ["name", "age_years", "gender", "sim_state_id"]
patientModel = self.manager.loadPatientInfo([self.testPatientId])[0]
expectedPatientModel = RowItemModel(
["Template Copy", 60, "Female", -1], patientCols)
self.assertEqualDict(expectedPatientModel, patientModel, patientCols)
# Verify notes
dataCols = ["sim_patient_id", "content"]
sampleData = self.manager.loadNotes(self.testPatientId, futureTime)
verifyData = \
[ RowItemModel([self.testPatientId,"Initial Note"], dataCols),
RowItemModel([self.testPatientId,"Initial Note"], dataCols), # Second copy because another state initiation at time zero and negative onset time
]
self.assertEqualDictList(verifyData, sampleData, dataCols)
# Verify orders
dataCols = [
"sim_user_id", "sim_patient_id", "sim_state_id",
"clinical_item_id", "relative_time_start", "relative_time_end"
]
sampleData = self.manager.loadPatientOrders(self.testPatientId,
futureTime,
loadActive=None)
verifyData = \
[ RowItemModel([-1,self.testPatientId,-1,-15,0,None], dataCols),
]
self.assertEqualDictList(verifyData, sampleData, dataCols)
# Verify states
dataCols = [
"sim_patient_id", "sim_state_id", "relative_time_start",
"relative_time_end"
]
query = SQLQuery()
for dataCol in dataCols:
query.addSelect(dataCol)
query.addFrom("sim_patient_state")
query.addWhereEqual("sim_patient_id", self.testPatientId)
query.addOrderBy("relative_time_start")
sampleDataTable = DBUtil.execute(query, includeColumnNames=True)
sampleData = modelListFromTable(sampleDataTable)
verifyData = \
[ RowItemModel([self.testPatientId,-1,-7200,0], dataCols),
RowItemModel([self.testPatientId,-1,0,None], dataCols),
]
self.assertEqualDictList(verifyData, sampleData, dataCols)
def patientItemModelFromSourceItem(self, sourceItem, clinicalItem, conn):
# Produce a patient_item record model for the given sourceItem
patient_item = \
RowItemModel({
"external_id": None,
"patient_id": int(sourceItem["rit_uid"][2:], 16),
"encounter_id": None,
"clinical_item_id": clinicalItem["clinical_item_id"],
"item_date": str(sourceItem["itemDate"]) # without str(), the time is being converted in postgres
})
insert_query = DBUtil.buildInsertQuery("patient_item", patient_item.keys())
insert_params = patient_item.values()
try:
# Optimistic insert of a new unique item
DBUtil.execute(insert_query, insert_params, conn=conn)
except conn.IntegrityError, err:
# If turns out to be a duplicate, okay, just note it and continue to insert whatever else is possible
log.warn(err)
def removeClinicalTablesAddedLines(self, source_table):
"""delete added records"""
log.info(
'Removing clinical_item and clinical_item_category added lines in PSQL DB'
)
DBUtil.execute("""delete from clinical_item
where clinical_item_category_id in
( select clinical_item_category_id
from clinical_item_category
where source_table = '{}'
);
""".format(source_table),
conn=self.pgConn)
DBUtil.execute(
"delete from clinical_item_category where source_table = '{}';".
format(source_table),
conn=self.pgConn)
def removeClinicalTablesAddedLines(self):
"""delete added records"""
log.info(
'Removing clinical_item and clinical_item_category added lines in PSQL DB'
)
DBUtil.execute \
("""delete from clinical_item
where clinical_item_category_id in
( select clinical_item_category_id
from clinical_item_category
where source_table = '%s'
);
""" % SOURCE_TABLE
)
DBUtil.execute(
"delete from clinical_item_category where source_table = '%s';" %
SOURCE_TABLE)
