From 9fa398d57e26c8ece01bce7a4f67545449e94821 Mon Sep 17 00:00:00 2001 From: BonfaceKilz Date: Wed, 25 May 2022 21:36:03 +0300 Subject: Move sql for modifying case-attributes from gn2 to gn3 --- gn3/db/case_attributes.py | 132 ++++++++++++++++++++++ gn3/db/sample_data.py | 10 +- tests/unit/db/test_case_attributes.py | 205 ++++++++++++++++++++++++++++++++++ 3 files changed, 338 insertions(+), 9 deletions(-) create mode 100644 gn3/db/case_attributes.py create mode 100644 tests/unit/db/test_case_attributes.py diff --git a/gn3/db/case_attributes.py b/gn3/db/case_attributes.py new file mode 100644 index 0000000..8eb2bf6 --- /dev/null +++ b/gn3/db/case_attributes.py @@ -0,0 +1,132 @@ +"""Module that contains functions for editing case-attribute data""" +from typing import Any, Optional, Tuple + +import json +import MySQLdb + + +def get_case_attributes(conn) -> Optional[Tuple]: + """Get all the case attributes from the database.""" + with conn.cursor() as cursor: + cursor.execute("SELECT Id, Name, Description FROM CaseAttribute") + return cursor.fetchall() + + +def get_unreviewed_diffs(conn: Any) -> Optional[tuple]: + """Fetch all case attributes in GN""" + with conn.cursor() as cursor: + cursor.execute( + "SELECT id, editor, json_diff_data FROM " + "caseattributes_audit WHERE status = 'review'" + ) + return cursor.fetchall() + + +def insert_case_attribute_audit( + conn: Any, status: str, author: str, data: str +) -> int: + """Update the case_attribute_audit table""" + rowcount = 0 + try: + with conn.cursor() as cursor: + cursor.execute( + "INSERT INTO caseattributes_audit " + "(status, editor, json_diff_data) " + "VALUES (%s, %s, %s)", + (status, author, data), + ) + rowcount = cursor.rowcount + conn.commit() + except Exception as _e: + conn.rollback() + raise MySQLdb.Error(_e) from _e + return rowcount + + +def reject_case_attribute(conn: Any, case_attr_audit_id: int) -> int: + """Given the id of the json_diff in the case_attribute_audit table, reject + it""" + rowcount = 0 + try: + with conn.cursor() as cursor: + cursor.execute( + "UPDATE caseattributes_audit SET " + "status = 'rejected' WHERE id = %s", + (case_attr_audit_id,), + ) + rowcount = cursor.rowcount + conn.commit() + except Exception as _e: + conn.rollback() + raise MySQLdb.Error(_e) from _e + return rowcount + + +def approve_case_attribute(conn: Any, case_attr_audit_id: int) -> int: + """Given the id of the json_diff in the case_attribute_audit table, + approve it + + """ + rowcount = 0 + try: + with conn.cursor() as cursor: + cursor.execute( + "SELECT json_diff_data FROM caseattributes_audit " + "WHERE id = %s", + (case_attr_audit_id,), + ) + diff_data = cursor.fetchone() + if diff_data: + diff_data = json.loads(diff_data[0]) + # Insert (Most Important) + if diff_data.get("Insert"): + data = diff_data.get("Insert") + cursor.execute( + "INSERT INTO CaseAttribute " + "(Name, Description) VALUES " + "(%s, %s)", + ( + data.get("name").strip(), + data.get("description").strip(), + ), + ) + # Delete + elif diff_data.get("Deletion"): + data = diff_data.get("Deletion") + cursor.execute( + "DELETE FROM CaseAttribute " "WHERE Id = %s", + (data.get("id"),), + ) + # Modification + elif diff_data.get("Modification"): + data = diff_data.get("Modification") + if desc_ := data.get("description"): + cursor.execute( + "UPDATE CaseAttribute SET " + "Description = %s WHERE Id = %s", + ( + desc_.get("Current"), + diff_data.get("id"), + ), + ) + if name_ := data.get("name"): + cursor.execute( + "UPDATE CaseAttribute SET " + "Name = %s WHERE Id = %s", + ( + name_.get("Current"), + diff_data.get("id"), + ), + ) + if cursor.rowcount: + cursor.execute( + "UPDATE caseattributes_audit SET " + "status = 'approved' WHERE id = %s", + (case_attr_audit_id,), + ) + rowcount = cursor.rowcount + conn.commit() + except Exception as _e: + conn.rollback() + raise MySQLdb.Error(_e) from _e + return rowcount diff --git a/gn3/db/sample_data.py b/gn3/db/sample_data.py index f79ce97..d29f6c8 100644 --- a/gn3/db/sample_data.py +++ b/gn3/db/sample_data.py @@ -1,8 +1,7 @@ """Module containing functions that work with sample data""" -from typing import Any, Tuple, Dict, Callable, Optional +from typing import Any, Tuple, Dict, Callable import re -import collections import MySQLdb from gn3.csvcmp import extract_strain_name @@ -423,10 +422,3 @@ def insert_sample_data( except Exception as _e: conn.rollback() raise MySQLdb.Error(_e) from _e - - -def get_case_attributes(conn) -> Optional[Tuple]: - """Get all the case attributes from the database.""" - with conn.cursor() as cursor: - cursor.execute("SELECT Id, Name, Description FROM CaseAttribute") - return cursor.fetchall() diff --git a/tests/unit/db/test_case_attributes.py b/tests/unit/db/test_case_attributes.py new file mode 100644 index 0000000..175249b --- /dev/null +++ b/tests/unit/db/test_case_attributes.py @@ -0,0 +1,205 @@ +"""Test cases for gn3.db.case_attributes.py""" + +import pytest +from pytest_mock import MockFixture +from gn3.db.case_attributes import get_unreviewed_diffs +from gn3.db.case_attributes import get_case_attributes +from gn3.db.case_attributes import insert_case_attribute_audit +from gn3.db.case_attributes import approve_case_attribute +from gn3.db.case_attributes import reject_case_attribute + + +@pytest.mark.unit_test +def test_get_case_attributes(mocker: MockFixture) -> None: + """Test that all the case attributes are fetched correctly""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + cursor.fetchall.return_value = ( + (1, "Condition", None), + (2, "Tissue", None), + (3, "Age", "Cum sociis natoque penatibus et magnis dis"), + (4, "Condition", "Description A"), + (5, "Condition", "Description B"), + ) + results = get_case_attributes(mock_conn) + cursor.execute.assert_called_once_with( + "SELECT Id, Name, Description FROM CaseAttribute" + ) + assert results == ( + (1, "Condition", None), + (2, "Tissue", None), + (3, "Age", "Cum sociis natoque penatibus et magnis dis"), + (4, "Condition", "Description A"), + (5, "Condition", "Description B"), + ) + + +@pytest.mark.unit_test +def test_get_unreviewed_diffs(mocker: MockFixture) -> None: + """Test that the correct query is called when fetching unreviewed + case-attributes diff""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + _ = get_unreviewed_diffs(mock_conn) + cursor.fetchall.return_value = ((1, "editor", "diff_data_1"),) + cursor.execute.assert_called_once_with( + "SELECT id, editor, json_diff_data FROM " + "caseattributes_audit WHERE status = 'review'" + ) + + +@pytest.mark.unit_test +def test_insert_case_attribute_audit(mocker: MockFixture) -> None: + """Test that the updating case attributes uses the correct query""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + _ = insert_case_attribute_audit( + mock_conn, status="review", author="Author", data="diff_data" + ) + cursor.execute.assert_called_once_with( + "INSERT INTO caseattributes_audit " + "(status, editor, json_diff_data) " + "VALUES (%s, %s, %s)", + ("review", "Author", "diff_data"), + ) + + +@pytest.mark.unit_test +def test_reject_case_attribute(mocker: MockFixture) -> None: + """Test rejecting a case-attribute""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + _ = reject_case_attribute( + mock_conn, + case_attr_audit_id=1, + ) + cursor.execute.assert_called_once_with( + "UPDATE caseattributes_audit SET " + "status = 'rejected' WHERE id = %s", + (1,), + ) + + +@pytest.mark.unit_test +def test_approve_inserting_case_attribute(mocker: MockFixture) -> None: + """Test approving inserting a case-attribute""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + type(cursor).rowcount = 1 + cursor.fetchone.return_value = ( + """ + {"Insert": {"name": "test", "description": "Random Description"}} + """, + ) + _ = approve_case_attribute( + mock_conn, + case_attr_audit_id=3, + ) + calls = [ + mocker.call( + "SELECT json_diff_data FROM caseattributes_audit " + "WHERE id = %s", + (3,), + ), + mocker.call( + "INSERT INTO CaseAttribute " + "(Name, Description) VALUES " + "(%s, %s)", + ( + "test", + "Random Description", + ), + ), + mocker.call( + "UPDATE caseattributes_audit SET " + "status = 'approved' WHERE id = %s", + (3,), + ), + ] + cursor.execute.assert_has_calls(calls, any_order=False) + + +@pytest.mark.unit_test +def test_approve_deleting_case_attribute(mocker: MockFixture) -> None: + """Test deleting a case-attribute""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + type(cursor).rowcount = 1 + cursor.fetchone.return_value = ( + """ + {"Deletion": {"id": "12", "name": "test", "description": ""}} + """, + ) + _ = approve_case_attribute( + mock_conn, + case_attr_audit_id=3, + ) + calls = [ + mocker.call( + "SELECT json_diff_data FROM caseattributes_audit " + "WHERE id = %s", + (3,), + ), + mocker.call("DELETE FROM CaseAttribute " "WHERE Id = %s", ("12",)), + mocker.call( + "UPDATE caseattributes_audit SET " + "status = 'approved' WHERE id = %s", + (3,), + ), + ] + cursor.execute.assert_has_calls(calls, any_order=False) + + +@pytest.mark.unit_test +def test_approve_modifying_case_attribute(mocker: MockFixture) -> None: + """Test modifying a case-attribute""" + mock_conn = mocker.MagicMock() + with mock_conn.cursor() as cursor: + type(cursor).rowcount = 1 + cursor.fetchone.return_value = ( + """ +{ + "id": "12", + "Modification": { + "description": { + "Current": "Test", + "Original": "A" + }, + "name": { + "Current": "Height (A)", + "Original": "Height" + } + } +}""", + ) + _ = approve_case_attribute( + mock_conn, + case_attr_audit_id=3, + ) + calls = [ + mocker.call( + "SELECT json_diff_data FROM caseattributes_audit " + "WHERE id = %s", + (3,), + ), + mocker.call( + "UPDATE CaseAttribute SET " "Description = %s WHERE Id = %s", + ( + "Test", + "12", + ), + ), + mocker.call( + "UPDATE CaseAttribute SET " "Name = %s WHERE Id = %s", + ( + "Height (A)", + "12", + ), + ), + mocker.call( + "UPDATE caseattributes_audit SET " + "status = 'approved' WHERE id = %s", + (3,), + ), + ] + cursor.execute.assert_has_calls(calls, any_order=False) -- cgit v1.2.3