def append_SeriesUID(MRN: int, SeriesUID: List[str]):
"""
Append record with SeriesUID list. which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
existing_series_UID = get_SeriesUIDs(MRN)
if existing_series_UID is None:
total_series_UID = SeriesUID
else:
total_series_UID = existing_series_UID + SeriesUID
# JSON dumps.
json_seriesUID = json.dumps(total_series_UID)
# Update the MRN record with SeriesUID
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"SeriesUID",
json_seriesUID,
)
def append_StudyUID(MRN: int, StudyUID: str):
"""
Update record with proper completion status which STUDY has particular MRN
:param MRN:
:return:
"""
# convert new study UID to list.
list_StudyUID_single_new = [StudyUID]
database_path = config_get("LocalDatabasePath")
# obtain
list_StudyUID_existing_ones = get_StudyUIDs(MRN)
if list_StudyUID_existing_ones is None:
list_StudyUID_total = list_StudyUID_single_new
else:
list_StudyUID_total = list_StudyUID_existing_ones + list_StudyUID_single_new
# JSON dumps.
json_seriesUID = json.dumps(list_StudyUID_total)
# Update the MRN record with StudyUID
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"StudyUID",
json_seriesUID,
)
def findTimePointUpdateDatabase(token, DCCID, database_path, table_name):
"""
Find the timepoint of the subject, IF they exist, then update the given database by finding its relevant MRN.
:param token:
:param DCCID:
:param database_path:
:param table_name:
:return:
"""
MRN = -1
# @todo: permission must be checked to ensure we are not geting 401 error! which is an access issue.
# @todo: 2018-07-24 continue debug this code about entry creation.
time_point = LORIS_timepoint.findLatestTimePoint(token, DCCID)
# Timepoint Check
if time_point is None:
return False, "No timepoint found"
else:
logger.info(f"Timepoint retrieved okay: {time_point}")
# Validate table and schema congruency
success, reason = LocalDB_query.validateLocalTableAndSchema(
database_path, table_name, "DCCID")
if not success:
return False, reason
# Check local database for the rows with matching DCCID.
success, subject_rows = LocalDB_query.check_value(database_path,
table_name, "DCCID",
DCCID)
if not success:
return False, "No entries with this DCCID!"
# Recall each row WILL conform to schema.
for row in subject_rows:
# Get MRN:
DCCID_table_index = LocalDB_query.check_header_index(
database_path, table_name, "DCCID")
assert str(DCCID) == str(row[DCCID_table_index])
# Need to update these rows with the new value.
MRN = row[0] # the identifier of the record.
try:
LocalDB_query.update_entry(
database_path,
table_name,
CNBP_blueprint.keyfield,
MRN,
"Timepoint",
time_point,
)
except IOError:
return False, "Check the database is not read only. "
return True, "Timepoint successfully updated in the local SQLite database."
def get_CNNID(MRN: int) -> Optional[List[str]]:
"""
Assuming the MRN exist, get the CNNIDs of all scans that ever past through here.
:param MRN: the MRN to look for
:return: the CNBPID associated with that particular MRN number.
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
MRN_exist_in_database, KeyRecords = LocalDB_query.check_value(
database_path, CNBP_blueprint.table_name, CNBP_blueprint.keyfield, MRN)
if MRN_exist_in_database is False:
return None
# Only ONE record per MRN.
assert len(KeyRecords) == 1
CNNID_index = LocalDB_query.check_header_index(database_path,
CNBP_blueprint.table_name,
"CNNID")
# Load the json and return the variable structure
json_CNNIDs = KeyRecords[0][CNNID_index]
if json_CNNIDs is None:
logger.warning("No existing CNNID Data information found.")
return None
list_CNNIDs = json.loads(json_CNNIDs)
return list_CNNIDs
def test_LocalDBCreate_CNBP(self):
PathString = "TestCNBP.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a fiela nd exist.
if SQLPath.is_file():
logger.warning(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
# Create the database
assert LocalDB_createCNBP.database(PathString)
tableName = (CNBP_blueprint.table_name
) # All CNBP database should have this table name.
fetchallResult = LocalDB_query.check_header(PathString, tableName)
# remove test database
os.remove(PathString)
# must pappend DBKEY creaed as the indexer column.
for index in range(0, len(CNBP_blueprint.schema)):
print(fetchallResult[index][1])
assert fetchallResult[index][1] == CNBP_blueprint.schema[index]
print(fetchallResult[index][2])
assert fetchallResult[index][2] == CNBP_blueprint.schema_types[
index]
return True
def set_DCCID(MRN: int, DCCID: int):
"""
Update record with proper DCCID which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
# Update the MRN record with DCCID
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"DCCID",
DCCID,
)
def set_timepoint(MRN: int, Timepoint: str):
"""
Update record with proper Timepoint which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
# Update the MRN record with Timepoint
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"Timepoint",
Timepoint,
)
def test_CreateSubject(self):
PathString = "TestCNBPQuery.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a file and exist.
if SQLPath.is_file():
logger.info(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
# Create the database
assert LocalDB_createCNBP.database(PathString)
logger.info(
"Test SQLite database successfully created. Gonna mess with it!")
LocalDB_query.create_entry(PathString, CNBP_blueprint.table_name,
CNBP_blueprint.keyfield, 291033)
logger.info(
"Test SQLite database successfully inserted with mock records. Gonna check!"
)
# Populate the table with some fake records.
ConnectedDatabase = sqlite3.connect(PathString)
c = ConnectedDatabase.cursor()
c.execute(
'SELECT * FROM {tablename} WHERE {columnname}="{columnvalue}"'.
format(
tablename=CNBP_blueprint.table_name,
columnname=CNBP_blueprint.keyfield,
columnvalue=291033,
))
ResultRows = c.fetchall()
assert len(ResultRows) > 0
# Closing the connection to the database file
ConnectedDatabase.close()
# Remove test data base created
os.remove(PathString)
return True
def get_scan_date(MRN: int) -> Optional[str]:
"""
Assuming the MRN exist, get the MRNID.
:param MRN: the MRN to look for
:return: the CNBPID associated with that particular MRN number.
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
MRN_exist_in_database, KeyRecords = LocalDB_query.check_value(
database_path, CNBP_blueprint.table_name, CNBP_blueprint.keyfield, MRN)
if MRN_exist_in_database is False:
return None
# Only ONE record per MRN, even if there are multiple timepoint. We keep the latest one.
assert len(KeyRecords) == 1
date_header_index = LocalDB_query.check_header_index(
database_path, CNBP_blueprint.table_name, "Date")
scan_date = KeyRecords[0][date_header_index]
return scan_date
def get_DCCID(MRN: int) -> Optional[str]:
"""
Assuming the MRN exist, get the MRNID.
:param MRN: the MRN to look for
:return: the CNBPID associated with that particular MRN number.
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
MRN_exist_in_database, KeyRecords = LocalDB_query.check_value(
database_path, CNBP_blueprint.table_name, CNBP_blueprint.keyfield, MRN)
if MRN_exist_in_database is False:
return None
# Only ONE record per MRN.
assert len(KeyRecords) == 1
dcc_header_index = LocalDB_query.check_header_index(
database_path, CNBP_blueprint.table_name, "DCCID")
return KeyRecords[0][dcc_header_index]
def set_StudyUID(MRN: int, StudyUID: List[str]):
"""
Update record with SeriesUID list. which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
# JSON dumps.
json_seriesUID = json.dumps(StudyUID)
# Update the MRN record with SeriesUID
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"StudyUID",
json_seriesUID,
)
def set_CNNIDs(MRN: int, CaseIDs: List[int]):
"""
Update record with proper CNN which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
# JSON dumps.
json_CaseIDs = json.dumps(CaseIDs)
# Update the MRN record with DCCID
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"CNNID",
json_CaseIDs,
)
def set_CNBP(
MRN: int, CNBPID: str
): # fixme: all the SET RECORDS NEED TO CONSIDER THE MULTIROW possiblities.
"""
Update record with proper CNBPID which has particular MRN
:param MRN:
:return:
"""
database_path = config_get("LocalDatabasePath")
# Update the MRN record with CNBPID
LocalDB_query.create_entry(database_path, CNBP_blueprint.table_name,
CNBP_blueprint.keyfield, MRN)
LocalDB_query.update_entry(
database_path,
CNBP_blueprint.table_name,
CNBP_blueprint.keyfield,
MRN,
"CNBPID",
CNBPID,
)
def test_updateLocalTimepoint():
import os
database_path = "Test.sqlite"
DCCID = 768766
table_name = CNBP_blueprint.table_name
# Remove database if previously existed.
if os.path.exists(database_path):
os.remove(database_path)
# Create Database
LocalDB_createCNBP.database(database_path)
# Get login token:
success, token = LORIS_query.login()
# Create the entry with the right PSCID with proper DCCID on dev.cnbp.ca
LocalDB_query.create_entry(database_path, table_name,
CNBP_blueprint.keyfield, "8987888")
# Update above entry with the right mock PSCID.
LocalDB_query.update_entry(
database_path,
table_name,
CNBP_blueprint.keyfield,
"8987888",
"DCCID",
DCCID,
)
# Now, the big guy.
success, reason = findTimePointUpdateDatabase(token, DCCID,
database_path,
table_name)
# Clean up database.
os.remove(database_path)
assert success
def check_MRN(MRN: int) -> bool:
"""
Return true if the MRN exist within the current database
:return:
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
# Store MRN in database.
MRN_exist_in_database, _ = LocalDB_query.check_value(
database_path, CNBP_blueprint.table_name, CNBP_blueprint.keyfield, MRN)
if MRN_exist_in_database:
logger.info("MRN found to exist at local database")
return MRN_exist_in_database
def get_list_CNBPID() -> List[str]:
"""
Return a list of all CNBPID (Key) from the database.
:return:
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
list_CNBPID = []
success, result_rows = LocalDB_query.get_all(database_path,
CNBP_blueprint.table_name,
"CNBPID")
for row in result_rows:
list_CNBPID.append(row[0]) # MRN is the first variable requested.
return list_CNBPID
def check_status() -> bool:
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
if not os.path.isfile(database_path):
return False
# Get the name from the blueprint.
tableName = (CNBP_blueprint.table_name
) # All CNBP database should have this table name.
# do a quick header check.
fetchallResult = LocalDB_query.check_header(database_path, tableName)
# If the table is valid, it should have MORE than one header... I HOPE?
if len(fetchallResult) > 0:
return True
else:
return False
def get_list_MRN() -> List[int]:
"""
Return a list_return of all MRN from the database.
:return:
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
list_MRN = []
success, result_rows = LocalDB_query.get_all(database_path,
CNBP_blueprint.table_name,
"MRN")
for row in result_rows:
list_MRN.append(row[0]) # MRN is the first variable requested.
return (
list_MRN
) # @todo: verify this is in integer? or string as that has dire consequences.
def get_list_StudyUID() -> List[List[str]]:
"""
Return a list_return of all StudyUID listS from the database.
:return:
"""
# Load local database from .env file
database_path = config_get("LocalDatabasePath")
list_StudyUID = []
success, result_rows = LocalDB_query.get_all(database_path,
CNBP_blueprint.table_name,
"StudyUID")
for row in result_rows:
list_StudyUID.append(
row[0]) # Values are always the first variable requested.
return (
list_StudyUID
) # @todo: verify this is in integer? or string as that has dire consequences.
def propose_CNBPID(DICOM_protocol: str):
"""
This function takes in a string that is representative of the DICOM acquisition study protocol, and propose a CNBPID composed of two parts:
Institution_ID (from the .env configuration file)
Project_ID (inferred from the protocol and incremented
SubjectCount (kept track by localDB.
:param DICOM_protocol:
:return:
"""
# Get and retrieve institution_ID
InstitionID = config_get("institutionID")
import DICOMTransit.DICOM.API
# Check ProjectID string and then return the ProjectID;
ProjectID = DICOMTransit.DICOM.API.study_validation(DICOM_protocol)
# Use those two pieces of information to form a partial query pattern that can run on the SQLite
partial_search_input = InstitionID + ProjectID
DB_path = config_get("LocalDatabasePath")
# Partial match search records: (currently has SQL error).
success, matched_records = LocalDB_query.check_partial_value(
DB_path, CNBP_blueprint.table_name, "CNBPID", partial_search_input)
# Default subject ID when no match is found.
latest_subject_ID = "0000001"
if matched_records is None or len(matched_records) == 0:
# no previous subjects found. Use default value.
pass
else:
latest_subject_ID = check_all_existing_records(matched_records)
# Combined all the parts to return the proposed CNBPID
proposed_CNBPID = InstitionID + ProjectID + latest_subject_ID
return proposed_CNBPID
def test_LocalDBCreate(self):
PathString = "Test.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a fiel and exist.
if SQLPath.is_file():
logger.warning(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
table_name = ("whatchacallitid_table"
) # All CNBP database should have this table name.
KeyFieldString = "whatchacalitkeyfield"
# must pappend DBKEY creaed as the indexer column.
NewColumns = [
"whasdfasdfasdffsdf",
"123345sdb",
"CNvaasa31NID",
"CNFUasdfNID",
"Ha1234sh1",
"Hasha2",
"adsfas",
]
NewColumnsTypes = [
"TEXT", "BLOB", "REAL", "TEXT", "TEXT", "BLOB", "TEXT"
]
NewColumnSpec = zip(NewColumns, NewColumnsTypes)
NewColumnSpecList = list(NewColumnSpec)
# Create the database
assert LocalDB_create.database(PathString, table_name, KeyFieldString,
NewColumnSpecList)
table_header = LocalDB_query.check_header(PathString, table_name)
# Remove database before assertion that potentially fail.
os.remove(PathString)
NewColumns = [
KeyFieldString,
"whasdfasdfasdffsdf",
"123345sdb",
"CNvaasa31NID",
"CNFUasdfNID",
"Ha1234sh1",
"Hasha2",
"adsfas",
]
NewColumnsTypes = [
"INTEGER",
"TEXT",
"BLOB",
"REAL",
"TEXT",
"TEXT",
"BLOB",
"TEXT",
]
for index in range(0, len(NewColumns)):
print(table_header[index][1])
assert table_header[index][1] == NewColumns[index]
print(table_header[index][2])
assert table_header[index][2] == NewColumnsTypes[index]
return True
def test_CheckSubjectExist(self):
PathString = "TestCNBPQuery.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a file and exist.
if SQLPath.is_file():
logger.info(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
# Create the database
assert LocalDB_createCNBP.database(PathString)
logger.info(
"Test SQLite database successfully created. Gonna mess with it!")
# Populate the table with some fake records.
ConnectedDatabase = sqlite3.connect(PathString)
c = ConnectedDatabase.cursor()
c.execute(
"INSERT INTO {tn} ({mrn},{cnbpid}) VALUES (291010,'CNBP0010001')".
format(
tn=CNBP_blueprint.table_name,
mrn=CNBP_blueprint.keyfield,
cnbpid=CNBP_blueprint.fields[1],
))
c.execute(
"INSERT INTO {tn} ({mrn},{cnbpid}) VALUES (292010,'CNBP0020001')".
format(
tn=CNBP_blueprint.table_name,
mrn=CNBP_blueprint.keyfield,
cnbpid=CNBP_blueprint.fields[1],
))
c.execute(
"INSERT INTO {tn} ({mrn},{cnbpid}) VALUES (295010,'CNBP0010001')".
format(
tn=CNBP_blueprint.table_name,
mrn=CNBP_blueprint.keyfield,
cnbpid=CNBP_blueprint.fields[1],
))
c.execute(
"INSERT INTO {tn} ({mrn},{cnbpid}) VALUES (297120,'CNBP0030001')".
format(
tn=CNBP_blueprint.table_name,
mrn=CNBP_blueprint.keyfield,
cnbpid=CNBP_blueprint.fields[1],
))
c.execute(
"INSERT INTO {tn} ({mrn},{cnbpid}) VALUES (291310,'CNBP0510001')".
format(
tn=CNBP_blueprint.table_name,
mrn=CNBP_blueprint.keyfield,
cnbpid=CNBP_blueprint.fields[1],
))
ConnectedDatabase.commit()
ConnectedDatabase.close()
logger.info(
"Test SQLite database successfully inserted with mock records. Gonna mess with it!"
)
# Create on Connecting to the database file
assert LocalDB_query.check_value(PathString, CNBP_blueprint.table_name,
"MRN", 291010)
assert LocalDB_query.check_value(PathString, CNBP_blueprint.table_name,
"CNBPID", "CNBP0010001")
# Remove test data base created
os.remove(PathString)
return True
def test_SubjectUpdate(self):
PathString = "TestCNBPQuery.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a file and exist.
if SQLPath.is_file():
logger.info(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
# Create the database
assert LocalDB_createCNBP.database(PathString)
logger.info(
"Test SQLite database successfully created. Gonna mess with it!")
tableName = "id_table" # All CNBP database should have this table name.
MRNColumn = "MRN"
CNBPIDColumn = "CNBPID"
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 2918210)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 23452346)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 2345234)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 273411)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 364573)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 7424141)
Prefix = config_get("institutionID")
LocalDB_query.update_entry(PathString, tableName, MRNColumn, 7424141,
CNBPIDColumn, f"{Prefix}0010001")
LocalDB_query.update_entry(PathString, tableName, MRNColumn, 2345234,
CNBPIDColumn, f"{Prefix}0010002")
LocalDB_query.update_entry(PathString, tableName, MRNColumn, 2918210,
CNBPIDColumn, f"{Prefix}0010003")
LocalDB_query.update_entry(PathString, tableName, MRNColumn, 273411,
CNBPIDColumn, f"{Prefix}0010004")
success, _ = LocalDB_query.check_value(PathString, tableName,
CNBPIDColumn, "CNBPID0010006")
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
CNBPIDColumn,
f"{Prefix}0010001")
assert success
success, _ = LocalDB_query.check_value(PathString, tableName,
CNBPIDColumn, 55555)
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
CNBPIDColumn, 742)
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
CNBPIDColumn,
f"{Prefix}0010003")
assert success
logger.info("Tested SQLIte database entry. ")
# Remove test data base created
os.remove(PathString)
return True
def create_MRN(MRN: int):
database_path = config_get("LocalDatabasePath")
# Create the MRN record
LocalDB_query.create_entry(database_path, CNBP_blueprint.table_name,
CNBP_blueprint.keyfield, MRN)
def test_CreateSubjectCheckExist(self):
PathString = "TestCNBPQuery.sqlite"
# if SQL already exist, quit script.
SQLPath = Path(PathString)
# check if path is a file and exist.
if SQLPath.is_file():
logger.info(
"Test SQLite database file already exist. Gonna mess with it!")
"""'Delete current database! During testing only"""
os.remove(PathString)
# Create the database
assert LocalDB_createCNBP.database(PathString)
logger.info(
"Test SQLite database successfully created. Gonna mess with it!")
tableName = "id_table" # All CNBP database should have this table name.
MRNColumn = "MRN"
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 2918210)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 23452346)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 2345234)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 273411)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 364573)
LocalDB_query.create_entry(PathString, tableName, MRNColumn, 7424141)
success, _ = LocalDB_query.check_value(PathString, tableName,
MRNColumn, 7129112)
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
MRNColumn, 2918210)
assert success
success, _ = LocalDB_query.check_value(PathString, tableName,
MRNColumn, 712921)
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
MRNColumn, 742)
assert not success
success, _ = LocalDB_query.check_value(PathString, tableName,
MRNColumn, 364573)
assert success
logger.info("Tested SQLIte database entry. ")
# Remove test data base created
os.remove(PathString)
return True