Move sql for modifying case-attributes from gn2 to gn3

3 files changed, 338 insertions, 9 deletions

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)