Implement `fetch_literature_correlations` and depedencies

Issue:
https://github.com/genenetwork/gn-gemtext-threads/blob/main/topics/gn1-migration-to-gn2/partial-correlations.gmi

* Migrate:

  * `web.webqtl.correlation.CorrelationPage.getTempLiteratureTable`
  * `web.webqtl.correlation.CorrelationPage.fetchLitCorrelations`

  from GeneNetwork1.

  The first function creates and populates a temporary table with the
  literature correlations data.

  The second function uses the data in the newly created temporary table to
  link the trait with the correlation value.

1 files changed, 113 insertions, 0 deletions

diff --git a/gn3/db/correlations.py b/gn3/db/correlations.py
index fa8e7ca..67cfef9 100644
--- a/gn3/db/correlations.py
+++ b/gn3/db/correlations.py
@@ -4,6 +4,10 @@ feature to access the database to retrieve data needed for computations.
 """
 
 from typing import Any
+
+from gn3.random import random_string
+from gn3.db.species import translate_to_mouse_gene_id
+
 def get_filename(target_db_name: str, conn: Any) -> str:
     """
     Retrieve the name of the reference database file with which correlations are
@@ -24,3 +28,112 @@ def get_filename(target_db_name: str, conn: Any) -> str:
                 fname=result[1].replace(' ', '_').replace('/', '_'))
 
     return ""
+
+def build_temporary_literature_table(
+        species: str, gene_id: int, return_number: int, conn: Any) -> str:
+    """
+    Build and populate a temporary table to hold the literature correlation data
+    to be used in computations.
+
+    "This is a migration of the
+    `web.webqtl.correlation.CorrelationPage.getTempLiteratureTable` function in
+    GeneNetwork1.
+    """
+    def __translated_species_id(row, cursor):
+        if species == "mouse":
+            return row[1]
+        query = {
+            "rat": "SELECT rat FROM GeneIDXRef WHERE mouse=%s",
+            "human": "SELECT human FROM GeneIDXRef WHERE mouse=%d"}
+        if species in query.keys():
+            cursor.execute(query[species], row[1])
+            record = cursor.fetchone()
+            if record:
+                return record[0]
+            return None
+        return None
+
+    temp_table_name = f"TOPLITERATURE{random_string(8)}"
+    with conn.cursor as cursor:
+        mouse_geneid = translate_to_mouse_gene_id(species, gene_id, conn)
+        data_query = (
+            "SELECT GeneId1, GeneId2, value FROM LCorrRamin3 "
+            "WHERE GeneId1 = %(mouse_gene_id)s "
+            "UNION ALL "
+            "SELECT GeneId2, GeneId1, value FROM LCorrRamin3 "
+            "WHERE GeneId2 = %(mouse_gene_id)s "
+            "AND GeneId1 != %(mouse_gene_id)s")
+        cursor.execute(
+            (f"CREATE TEMPORARY TABLE {temp_table_name} ("
+             "GeneId1 int(12) unsigned, "
+             "GeneId2 int(12) unsigned PRIMARY KEY, "
+             "value double)"))
+        cursor.execute(data_query, mouse_gene_id=mouse_geneid)
+        literature_data = [
+            {"GeneId1": row[0], "GeneId2": row[1], "value": row[2]}
+            for row in cursor.fetchall()
+            if __translated_species_id(row, cursor)]
+
+        cursor.execute(
+            (f"INSERT INTO {temp_table_name} "
+             "VALUES (%(GeneId1)s, %(GeneId2)s, %(value)s)"),
+            literature_data[0:(2 * return_number)])
+
+    return temp_table_name
+
+def fetch_geno_literature_correlations(temp_table: str) -> str:
+    """
+    Helper function for `fetch_literature_correlations` below, to build query
+    for `Geno*` tables.
+    """
+    return (
+        f"SELECT Geno.Name, {temp_table}.value "
+        "FROM Geno, GenoXRef, GenoFreeze "
+        f"LEFT JOIN {temp_table} ON {temp_table}.GeneId2=ProbeSet.GeneId "
+        "WHERE ProbeSet.GeneId IS NOT NULL "
+        f"AND {temp_table}.value IS NOT NULL "
+        "AND GenoXRef.GenoFreezeId = GenoFreeze.Id "
+        "AND GenoFreeze.Name = %(db_name)s "
+        "AND Geno.Id=GenoXRef.GenoId "
+        "ORDER BY Geno.Id")
+
+def fetch_probeset_literature_correlations(temp_table: str) -> str:
+    """
+    Helper function for `fetch_literature_correlations` below, to build query
+    for `ProbeSet*` tables.
+    """
+    return (
+        f"SELECT ProbeSet.Name, {temp_table}.value "
+        "FROM ProbeSet, ProbeSetXRef, ProbeSetFreeze "
+        "LEFT JOIN {temp_table} ON {temp_table}.GeneId2=ProbeSet.GeneId "
+        "WHERE ProbeSet.GeneId IS NOT NULL "
+        "AND {temp_table}.value IS NOT NULL "
+        "AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
+        "AND ProbeSetFreeze.Name = %(db_name)s "
+        "AND ProbeSet.Id=ProbeSetXRef.ProbeSetId "
+        "ORDER BY ProbeSet.Id")
+
+def fetch_literature_correlations(
+        species: str, gene_id: int, dataset: dict, return_number: int,
+        conn: Any) -> dict:
+    """
+    Gather the literature correlation data and pair it with trait id string(s).
+
+    This is a migration of the
+    `web.webqtl.correlation.CorrelationPage.fetchLitCorrelations` function in
+    GeneNetwork1.
+    """
+    temp_table = build_temporary_literature_table(
+        species, gene_id, return_number, conn)
+    query_fns = {
+        "Geno": fetch_geno_literature_correlations,
+        # "Temp": fetch_temp_literature_correlations,
+        # "Publish": fetch_publish_literature_correlations,
+        "ProbeSet": fetch_probeset_literature_correlations}
+    with conn.cursor as cursor:
+        cursor.execute(
+            query_fns[dataset["dataset_type"]](temp_table),
+            db_name=dataset["dataset_name"])
+        results = cursor.fetchall()
+        cursor.execute("DROP TEMPORARY TABLE %s", temp_table)
+        return dict(results) # {trait_name: lit_corr for trait_name, lit_corr in results}