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(SqlLmsOperations, "_exec") SqlLmsOperations(Mock()).add_processed_file( path, resource_name, rows) exec_mock.assert_called_with(expected_statement)
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(SqlLmsOperations, "_exec") # Act SqlLmsOperations(Mock()).copy_updates_to_production(table, columns) # Assert exec_mock.assert_called_with(expected)
def _upload_files_from_paths( db_adapter: SqlLmsOperations, file_paths: List[str], resource_name: str, read_file_callback: Callable[[str], DataFrame], upload_function: Callable[[SqlLmsOperations, 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 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(SqlLmsOperations, "_exec") # Act SqlLmsOperations( Mock() ).insert_new_records_to_production_for_assignment_and_user_relation( TABLE, COLUMNS) # Assert exec_mock.assert_called_with(expected)
def _get_unprocessed_file_paths( db_adapter: SqlLmsOperations, 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_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(SqlLmsOperations, "_exec") # Act SqlLmsOperations(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(SqlLmsOperations, "_exec") # Act SqlLmsOperations(Mock()).truncate_staging_table(table) # Assert exec_mock.assert_called_with(expected)
def test_pgsql_db( pgsql_connection: Connection, request) -> Tuple[SqlLmsOperations, Connection, ConnectionSettings]: """ 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 SqlLmsOperations that uses that Connection. They may be used interchangeably. """ # Wrap connection in transaction transaction: Transaction = pgsql_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: SqlLmsOperations, statement: str) -> int: result = pgsql_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: SqlLmsOperations, df: DataFrame, table: str): copy = df.copy() copy.columns = copy.columns.str.lower() copy.to_sql( f"stg_{table}", con=pgsql_connection, schema="lms", if_exists="append", index=False, method="multi", chunksize=120, ) # Monkey-patch SqlLmsOperations to use our transaction SqlLmsOperations._exec = replace_exec # type:ignore SqlLmsOperations.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: SqlLmsOperations = SqlLmsOperations(MagicMock()) adapter.engine = DbEngine.POSTGRESQL return (adapter, pgsql_connection, GetSettings(request))
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.sql_lms_operations") mock_exc_logger = mocker.patch.object(logger, "exception") with pytest.raises(ProgrammingError): SqlLmsOperations( 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(SqlLmsOperations, "_exec", side_effect=__raise) logger = logging.getLogger("edfi_lms_ds_loader.sql_lms_operations") mock_exc_logger = mocker.patch.object(logger, "exception") with pytest.raises(ProgrammingError): SqlLmsOperations(Mock()).add_processed_file( path, resource_name, rows) mock_exc_logger.assert_called_once()
def it_then_use_pandas_to_load_into_the_db(mocker) -> None: table = "aaa" staging_table = "stg_aaa" df = Mock(spec=pd.DataFrame) adapter_mock = Mock(spec=sql_adapter) engine = adapter_mock.engine = Mock() # Act SqlLmsOperations(adapter_mock).insert_into_staging(df, table) # Assert # adapter_mock.assert_called() df.to_sql.assert_called_with( staging_table, engine, 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"])) SqlLmsOperations(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(SqlLmsOperations, "_exec") source_system = "Canvas" expected = """ UPDATE AssignmentSubmissionType SET DeletedAt = GETDATE() FROM lms.AssignmentSubmissionType INNER JOIN lms.Assignment ON lms.AssignmentSubmissionType.AssignmentIdentifier = lms.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 SqlLmsOperations( Mock()).soft_delete_removed_submission_types(source_system) # Assert exec_mock.assert_called_with(expected)
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(SqlLmsOperations, "_exec") # Act SqlLmsOperations(Mock()).soft_delete_from_production( table, source_system) # Assert exec_mock.assert_called_with(expected)
def it_issues_insert_statement(mocker) -> None: # Arrange exec_mock = mocker.patch.object(SqlLmsOperations, "_exec") expected = """ INSERT INTO lms.AssignmentSubmissionType ( AssignmentIdentifier, SubmissionType ) SELECT lms.Assignment.AssignmentIdentifier, lms.stg_AssignmentSubmissionType.SubmissionType FROM lms.stg_AssignmentSubmissionType INNER JOIN lms.Assignment ON lms.stg_AssignmentSubmissionType.SourceSystem = lms.Assignment.SourceSystem AND lms.stg_AssignmentSubmissionType.SourceSystemIdentifier = lms.Assignment.SourceSystemIdentifier WHERE NOT EXISTS ( SELECT 1 FROM lms.AssignmentSubmissionType WHERE AssignmentIdentifier = lms.Assignment.AssignmentIdentifier AND SubmissionType = lms.stg_AssignmentSubmissionType.SubmissionType ) """ # Act SqlLmsOperations(Mock()).insert_new_submission_types() # 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(SqlLmsOperations, "_exec") # Act SqlLmsOperations(Mock()).insert_new_records_to_production( table, columns) # Assert exec_mock.assert_called_with(expected)
def it_raises_an_error() -> None: with pytest.raises(AssertionError): SqlLmsOperations( Mock()).enable_staging_natural_key_index(" ")
def it_raises_an_error() -> None: with pytest.raises(AssertionError): SqlLmsOperations(Mock()).truncate_staging_table(" ")
def it_raises_an_error() -> None: df = pd.DataFrame() with pytest.raises(AssertionError): SqlLmsOperations(Mock()).insert_into_staging(df, " ")
def it_raises_an_error() -> None: with pytest.raises(AssertionError): SqlLmsOperations(Mock()).soft_delete_from_production( " ", "a")
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 = SqlLmsOperations(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 get_db_operations_adapter(self) -> SqlLmsOperations: return SqlLmsOperations(self.get_adapter(), self.engine)
def it_raises_an_error() -> None: with pytest.raises(AssertionError): SqlLmsOperations(Mock()).insert_new_records_to_production( "table", list())
def it_raises_an_error(): with pytest.raises(AssertionError): SqlLmsOperations(Mock( )).insert_new_records_to_production_for_attendance_events( "something", [])
def it_raises_an_error() -> None: with pytest.raises(AssertionError): SqlLmsOperations(Mock()).copy_updates_to_production( "t", list())