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() )