def _prepare_staging_table(db_adapter: MssqlLmsOperations, df: pd.DataFrame,
table: str) -> None:
logger.info(f"Uploading {table} file ...")
db_adapter.disable_staging_natural_key_index(table)
db_adapter.truncate_staging_table(table)
db_adapter.insert_into_staging(df, table)
db_adapter.enable_staging_natural_key_index(table)
def it_raises_an_error():
with pytest.raises(AssertionError):
MssqlLmsOperations(
Mock()
).insert_new_records_to_production_for_attendance_events(
"something", []
)
def it_issues_insert_where_not_exists_statement(mocker) -> None:
columns = ["a", "b", "SourceSystem", "SourceSystemIdentifier"]
table = "Fake"
expected = """
UPDATE
t
SET
a = stg.a,
b = stg.b,
DeletedAt = NULL
FROM
lms.Fake as t
INNER JOIN
lms.stg_Fake as stg
ON
t.SourceSystem = stg.SourceSystem
AND
t.SourceSystemIdentifier = stg.SourceSystemIdentifier
AND
t.LastModifiedDate <> stg.LastModifiedDate
"""
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(Mock()).copy_updates_to_production(table, columns)
# Assert
exec_mock.assert_called_with(expected)
def _upload_files_from_paths(
db_adapter: MssqlLmsOperations,
file_paths: List[str],
resource_name: str,
read_file_callback: Callable[[str], DataFrame],
upload_function: Callable[[MssqlLmsOperations, DataFrame], None],
) -> None:
unprocessed_files: List[str] = _get_unprocessed_file_paths(
db_adapter, resource_name, file_paths)
for path in unprocessed_files:
data: DataFrame = read_file_callback(path)
rows = data.shape[0]
if rows != 0:
upload_function(db_adapter, data)
db_adapter.add_processed_file(path, resource_name, rows)
def _get_unprocessed_file_paths(
db_adapter: MssqlLmsOperations,
resource_name: str,
file_paths: List[str],
) -> List[str]:
processed_files = db_adapter.get_processed_files(resource_name)
all_paths = set(file_paths)
return sorted(all_paths - processed_files)
def it_should_build_a_valid_insert_statement(mocker) -> None:
# Arrange
expected = """
INSERT INTO
lms.Fake
(
AssignmentIdentifier,
LMSUserIdentifier,
SourceSystem,
SourceSystemIdentifier
)
SELECT
Assignment.AssignmentIdentifier,
LMSUser.LMSUserIdentifier,
stg.SourceSystem,
stg.SourceSystemIdentifier
FROM
lms.stg_Fake as stg
INNER JOIN
lms.Assignment
ON
stg.AssignmentSourceSystemIdentifier = Assignment.SourceSystemIdentifier
AND
stg.SourceSystem = Assignment.SourceSystem
INNER JOIN
lms.LMSUser
ON
stg.LMSUserSourceSystemIdentifier = LMSUser.SourceSystemIdentifier
AND
stg.SourceSystem = LMSUser.SourceSystem
WHERE NOT EXISTS (
SELECT
1
FROM
lms.Fake
WHERE
SourceSystemIdentifier = stg.SourceSystemIdentifier
AND
SourceSystem = stg.SourceSystem
)
"""
TABLE = "Fake"
COLUMNS = [
"AssignmentSourceSystemIdentifier",
"LMSUserSourceSystemIdentifier",
"SourceSystem",
"SourceSystemIdentifier",
]
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(
Mock()
).insert_new_records_to_production_for_assignment_and_user_relation(TABLE, COLUMNS)
# Assert
exec_mock.assert_called_with(expected)
def it_issues_alter__index_statement(mocker) -> None:
table = "user"
expected = "ALTER INDEX IX_stg_user_Natural_Key on lms.stg_user REBUILD;"
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(Mock()).enable_staging_natural_key_index(table)
# Assert
exec_mock.assert_called_with(expected)
def it_should_issue_truncate_statement(mocker) -> None:
table = "user"
expected = "TRUNCATE TABLE lms.stg_user;"
# Arrange
# Explanation: the raw SQLAlchemy execution call is wrapped in
# method `_exec`, which we can easily bypass here for unit testing.
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(Mock()).truncate_staging_table(table)
# Assert
exec_mock.assert_called_with(expected)
def upload_file(
db_adapter: MssqlLmsOperations,
df: pd.DataFrame,
table: str,
db_adapter_insert_method: Callable[[MssqlLmsOperations, str, List[str]],
None],
db_adapter_delete_method: Callable[[MssqlLmsOperations, str, str], None],
) -> None:
"""
Uploads a DataFrame to the designated LMS table.
Parameters
----------
db_adapter: MssqlLmsOperations
Database engine-specific adapter/wrapper for database operations.
df: pd.DataFrame
A DataFrame to upload.
table: str
The destination table.
db_adapter_insert_method: Callable[[MssqlLmsOperations, str, List[str]], None]
The MssqlLmsOperations insert method to use for the upload
db_adapter_delete_method: Callable[[MssqlLmsOperations, str, str], None],
The MssqlLmsOperations delete method to use for the upload
"""
if df.empty:
return
_prepare_staging_table(db_adapter, df, table)
columns = list(df.columns)
db_adapter_insert_method(db_adapter, table, columns)
db_adapter.copy_updates_to_production(table, columns)
db_adapter_delete_method(db_adapter, table, _get_source_system(df))
logger.info(f"Done with {table} file.")
def it_should_log_an_error_and_re_raise_the_exception(mocker):
def __raise(query, engine) -> None:
raise ProgrammingError("statement", [], "none", False)
mocker.patch.object(pd, "read_sql_query", side_effect=__raise)
# Typically we do not test the logging. In this case and another
# below, logging was a core business requirement for a task, and
# thus it makes sense to test that requirement.
logger = logging.getLogger("edfi_lms_ds_loader.mssql_lms_operations")
mock_exc_logger = mocker.patch.object(logger, "exception")
with pytest.raises(ProgrammingError):
MssqlLmsOperations(Mock()).get_processed_files("fake_resource_name")
mock_exc_logger.assert_called_once()
def it_should_log_an_error_and_re_raise_the_exception(mocker):
def __raise(_) -> None:
raise ProgrammingError("statement", [], "none", False)
resource_name = "fake_resource_name"
path = "fake_path/"
rows = 3
mocker.patch.object(MssqlLmsOperations, "_exec", side_effect=__raise)
logger = logging.getLogger("edfi_lms_ds_loader.mssql_lms_operations")
mock_exc_logger = mocker.patch.object(logger, "exception")
with pytest.raises(ProgrammingError):
MssqlLmsOperations(Mock()).add_processed_file(path, resource_name, rows)
mock_exc_logger.assert_called_once()
def test_mssql_db(
mssql_connection: Connection, request
) -> Tuple[MssqlLmsOperations, Connection]:
"""
Fixture that takes the set-up connection and wraps in a transaction. Transaction
will be rolled-back automatically after each test.
Returns both a plain transaction-wrapped Connection and a monkey-patched
MssqlLmsOperations that uses that Connection. They may be used interchangeably.
"""
# Wrap connection in transaction
transaction: Transaction = mssql_connection.begin()
# Rollback transaction in finalizer when test is done
request.addfinalizer(lambda: transaction.rollback())
# New version of _exec using our transaction
def replace_exec(self: MssqlLmsOperations, statement: str) -> int:
result = mssql_connection.execute(statement)
if result:
return int(result.rowcount)
return 0
# New version of insert_into_staging using our transaction
def replace_insert_into_staging(
self: MssqlLmsOperations, df: DataFrame, table: str
):
df.to_sql(
f"stg_{table}",
con=mssql_connection,
schema="lms",
if_exists="append",
index=False,
method="multi",
chunksize=120,
)
# Monkey-patch MssqlLmsOperations to use our transaction
MssqlLmsOperations._exec = replace_exec # type:ignore
MssqlLmsOperations.insert_into_staging = replace_insert_into_staging # type:ignore
# Initialize monkey-patched adapter with a dummy engine, doesn't need a real one now
adapter: MssqlLmsOperations = MssqlLmsOperations(MagicMock())
return (adapter, mssql_connection)
def _upload_assignment_submission_types(
db_adapter: MssqlLmsOperations,
submission_types_df: pd.DataFrame) -> None:
TABLE = Table.ASSIGNMENT_SUBMISSION_TYPES
_prepare_staging_table(db_adapter, submission_types_df, TABLE)
db_adapter.insert_new_submission_types()
source_system: str = _get_source_system(submission_types_df)
db_adapter.unsoft_delete_returned_submission_types(source_system)
db_adapter.soft_delete_removed_submission_types(source_system)
logger.info(f"Done with {TABLE} file.")
def it_then_use_pandas_to_load_into_the_db(mocker) -> None:
table = "aaa"
staging_table = "stg_aaa"
df = Mock(spec=pd.DataFrame)
engine_mock = Mock()
# Act
MssqlLmsOperations(engine_mock).insert_into_staging(df, table)
# Assert
# engine_mock.assert_called()
df.to_sql.assert_called_with(
staging_table,
engine_mock,
schema="lms",
if_exists="append",
index=False,
method="multi",
chunksize=120,
)
def it_should_build_the_correct_sql_query(mocker):
resource_name = "fake_resource_name"
expected_query = """
SELECT
FullPath
FROM
lms.ProcessedFiles
WHERE
ResourceName = 'fake_resource_name'
""".strip()
query_mock = mocker.patch.object(
pd, "read_sql_query", return_value=pd.DataFrame(columns=["FullPath"])
)
MssqlLmsOperations(Mock()).get_processed_files(resource_name)
call_args = query_mock.call_args
assert len(call_args) == 2
assert call_args[0][0] == expected_query
def it_issues_update_statement(mocker) -> None:
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
source_system = "Canvas"
expected = """
UPDATE
AssignmentSubmissionType
SET
DeletedAt = GETDATE()
FROM
lms.AssignmentSubmissionType
INNER JOIN
lms.Assignment
ON
AssignmentSubmissionType.AssignmentIdentifier = Assignment.AssignmentIdentifier
WHERE
SourceSystem = 'Canvas'
AND
NOT EXISTS (
SELECT
1
FROM
lms.stg_AssignmentSubmissionType
WHERE
stg_AssignmentSubmissionType.SourceSystem = Assignment.SourceSystem
AND
stg_AssignmentSubmissionType.SourceSystemIdentifier = Assignment.SourceSystemIdentifier
AND
stg_AssignmentSubmissionType.SubmissionType = AssignmentSubmissionType.SubmissionType
)
"""
# Act
MssqlLmsOperations(Mock()).soft_delete_removed_submission_types(
source_system
)
# Assert
exec_mock.assert_called_with(expected)
def it_issues_insert_where_not_exists_statement(mocker) -> None:
columns = ["a", "b"]
table = "Fake"
expected = """
INSERT INTO
lms.Fake
(
a,
b
)
SELECT
a,
b
FROM
lms.stg_Fake as stg
WHERE
NOT EXISTS (
SELECT
1
FROM
lms.Fake
WHERE
SourceSystemIdentifier = stg.SourceSystemIdentifier
AND
SourceSystem = stg.SourceSystem
)
"""
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(Mock()).insert_new_records_to_production(
table, columns
)
# Assert
exec_mock.assert_called_with(expected)
def it_issues_insert_statement(mocker) -> None:
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
expected = """
INSERT INTO lms.AssignmentSubmissionType (
AssignmentIdentifier,
SubmissionType
)
SELECT
Assignment.AssignmentIdentifier,
stg_AssignmentSubmissionType.SubmissionType
FROM
lms.stg_AssignmentSubmissionType
INNER JOIN
lms.Assignment
ON
stg_AssignmentSubmissionType.SourceSystem = Assignment.SourceSystem
AND
stg_AssignmentSubmissionType.SourceSystemIdentifier = Assignment.SourceSystemIdentifier
WHERE
NOT EXISTS (
SELECT
1
FROM
lms.AssignmentSubmissionType
WHERE
AssignmentIdentifier = Assignment.AssignmentIdentifier
AND
SubmissionType = stg_AssignmentSubmissionType.SubmissionType
)
"""
# Act
MssqlLmsOperations(Mock()).insert_new_submission_types()
# Assert
exec_mock.assert_called_with(expected)
def it_should_build_the_correct_sql_statement(mocker):
resource_name = "fake_resource_name"
path = "fake_path/"
rows = 3
expected_statement = """
INSERT INTO
lms.ProcessedFiles
(
FullPath,
ResourceName,
NumberOfRows
)
VALUES
(
'fake_path/',
'fake_resource_name',
3
)
""".strip()
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
MssqlLmsOperations(Mock()).add_processed_file(path, resource_name, rows)
exec_mock.assert_called_with(expected_statement)
def it_updates_records_that_are_not_in_the_staging_table(mocker) -> None:
source_system = "Schoology"
table = "Fake"
expected = """
UPDATE
t
SET
t.DeletedAt = getdate()
FROM
lms.Fake as t
WHERE
NOT EXISTS (
SELECT
1
FROM
lms.stg_Fake as stg
WHERE
t.SourceSystemIdentifier = stg.SourceSystemIdentifier
AND
t.SourceSystem = stg.SourceSystem
)
AND
t.DeletedAt IS NULL
AND
t.SourceSystem = 'Schoology'
"""
# Arrange
exec_mock = mocker.patch.object(MssqlLmsOperations, "_exec")
# Act
MssqlLmsOperations(Mock()).soft_delete_from_production(table, source_system)
# Assert
exec_mock.assert_called_with(expected)
def it_raises_an_error() -> None:
df = pd.DataFrame()
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).insert_into_staging(df, " ")
def get_db_operations_adapter(self) -> MssqlLmsOperations:
return MssqlLmsOperations(self.get_db_engine())
def it_raises_an_error() -> None:
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).copy_updates_to_production("t", list())
def it_raises_an_error() -> None:
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).truncate_staging_table(" ")
def it_should_build_the_correct_insert_statement(mocker):
# Arrange
row_count = 2
table = "fake"
columns = [
"LMSSectionSourceSystemIdentifier",
"LMSUserSourceSystemIdentifier",
"Another",
]
expected = """
INSERT INTO
lms.LMSUserAttendanceEvent
(
LMSSectionIdentifier,
LMSUserIdentifier,
LMSUserLMSSectionAssociationIdentifier,
Another
)
SELECT
LMSSection.LMSSectionIdentifier,
LMSUser.LMSUserIdentifier,
LMSUserLMSSectionAssociation.LMSUserLMSSectionAssociationIdentifier,
stg.Another
FROM
lms.stg_LMSUserAttendanceEvent as stg
INNER JOIN
lms.LMSSection
ON
stg.LMSSectionSourceSystemIdentifier = LMSSection.SourceSystemIdentifier
AND
stg.SourceSystem = LMSSection.SourceSystem
INNER JOIN
lms.LMSUser
ON
stg.LMSUserSourceSystemIdentifier = LMSUser.SourceSystemIdentifier
AND
stg.SourceSystem = LMSUser.SourceSystem
INNER JOIN
lms.LMSUserLMSSectionAssociation
ON
LMSUser.LMSUserIdentifier = LMSUserLMSSectionAssociation.LMSUserIdentifier
AND
LMSSection.LMSSectionIdentifier = LMSUserLMSSectionAssociation.LMSSectionIdentifier
WHERE NOT EXISTS (
SELECT
1
FROM
lms.LMSUserAttendanceEvent
WHERE
SourceSystemIdentifier = stg.SourceSystemIdentifier
AND
SourceSystem = stg.SourceSystem
)
"""
system = MssqlLmsOperations(Mock())
mock = mocker.patch.object(system, "_exec", return_value=row_count)
# Act
system.insert_new_records_to_production_for_attendance_events(
table, columns
)
# Arrange
mock.assert_called_with(expected)
def it_raises_an_error() -> None:
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).soft_delete_from_production(" ", "a")
def it_raises_an_error() -> None:
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).enable_staging_natural_key_index(" ")
def it_raises_an_error() -> None:
with pytest.raises(AssertionError):
MssqlLmsOperations(Mock()).insert_new_records_to_production(
"table", list()
)