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