aboutsummaryrefslogtreecommitdiff
path: root/gn3/db
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi2023-07-26 15:45:56 +0300
committerFrederick Muriuki Muriithi2023-07-26 15:46:34 +0300
commit2968433f800f2f33d454f3b170094d951be86541 (patch)
tree554ee59921fffcf939f8d105ccffd01fddc9a534 /gn3/db
parent495e581754acd2692e3db08cb1760f75b050d1d0 (diff)
downloadgenenetwork3-2968433f800f2f33d454f3b170094d951be86541.tar.gz
Fetch `metadata_audit` trail with direct query functions.
Diffstat (limited to 'gn3/db')
-rw-r--r--gn3/db/metadata_audit.py38
1 files changed, 38 insertions, 0 deletions
diff --git a/gn3/db/metadata_audit.py b/gn3/db/metadata_audit.py
index 707892b..1b6e8a9 100644
--- a/gn3/db/metadata_audit.py
+++ b/gn3/db/metadata_audit.py
@@ -3,6 +3,7 @@
table from the db
"""
+import json
from typing import Optional
from dataclasses import dataclass
@@ -36,3 +37,40 @@ def create_metadata_audit(conn, metadata: dict) -> int:
"VALUES (%(dataset_id)s, %(editor)s, %(json_data)s)",
metadata)
return cursor.rowcount
+
+def __parse_metadata_audit__(row) -> dict:
+ """Convert values in DB to expected Python values """
+ return {
+ **{key:val for key,val in row.items() if key not in ("json_diff_data")},
+ "id_": row["id"],
+ "json_data": json.loads(row["json_diff_data"])
+ }
+
+def fetch_phenotype_metadata_audit_by_dataset_id(conn, dataset_id) -> tuple[dict, ...]:
+ """Fetch phenotype a metadata audit trail by `dataset_id`."""
+ assert bool(dataset_id), "`dataset_id` MUST be provided."
+ with conn.cursor(cursorclass=DictCursor) as cursor:
+ cursor.execute(
+ "SELECT ma.* "
+ "FROM PublishXRef AS pxr LEFT JOIN metadata_audit AS ma "
+ "ON pxr.Id=ma.dataset_id "
+ "WHERE pxr.Id=%(dataset_id)s "
+ "AND ma.json_diff_data LIKE '%%phenotype%%' "
+ "ORDER BY time_stamp ASC",
+ {"dataset_id": dataset_id})
+ __pk__(cursor._executed, "THE RUN QUERY")
+ return tuple(__parse_metadata_audit__(row) for row in cursor.fetchall())
+
+def fetch_probeset_metadata_audit_by_trait_name(conn, trait_name) -> tuple[dict, ...]:
+ """Fetch a probeset metadata audit trail by `dataset_id`."""
+ assert bool(dataset_id), "`dataset_id` MUST be provided."
+ with conn.cursor(cursorclass=DictCursor) as cursor:
+ cursor.execute(
+ "SELECT ma.* "
+ "FROM ProbeSet AS ps LEFT JOIN metadata_audit AS ma "
+ "ON ps.Id=ma.dataset_id "
+ "WHERE ps.Name=%(trait_name)s "
+ "AND json_diff_data LIKE '%probeset%' "
+ "ORDER BY time_stamp ASCENDING",
+ {"trait_name": trait_name})
+ return tuple(__parse_metadata_audit__(row) for row in cursor.fetchall())