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
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 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_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 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