From f914dc21577253f293b50b890ea0ac4bd2fd5d1b Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Mon, 21 Feb 2022 09:07:31 +0300
Subject: Test partial corrs API with mix of existing and non-existing control
 traits

Test that the partial correlations endpoint handles a mix of existing and
non-existing control traits gracefully and issues a warning to the user.

Summary of changes:
* gn3/computations/partial_correlations.py: Issue a warning for all
  non-existing control traits
* gn3/db/partial_correlations.py: update queries - use `INNER JOIN` for tables
  instead of comma-separated list of tables
* tests/integration/conftest.py: Add `db_conn` fixture to provide a database
  connection to the tests. This will probably be changed in the future to
  connect to a temporary database for tests.
* tests/integration/test_partial_correlations.py: Add test to check for
  correct behaviour with a mix of existing and non-existing control traits
---
 gn3/db/partial_correlations.py | 18 +++++++++---------
 1 file changed, 9 insertions(+), 9 deletions(-)

(limited to 'gn3/db')

diff --git a/gn3/db/partial_correlations.py b/gn3/db/partial_correlations.py
index 0931f09..0075cad 100644
--- a/gn3/db/partial_correlations.py
+++ b/gn3/db/partial_correlations.py
@@ -347,10 +347,11 @@ def probeset_traits_info(
         "probe_set_note_by_rw", "flag")
     query = (
         "SELECT ProbeSet.Name AS trait_name, {columns} "
-        "FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef "
-        "WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id "
-        "AND ProbeSetXRef.ProbeSetId = ProbeSet.Id "
-        "AND ProbeSetFreeze.Name IN ({dataset_names}) "
+        "FROM ProbeSet INNER JOIN ProbeSetXRef "
+        "ON ProbeSetXRef.ProbeSetId = ProbeSet.Id "
+        "INNER JOIN ProbeSetFreeze "
+        "ON ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId "
+        "WHERE ProbeSetFreeze.Name IN ({dataset_names}) "
         "AND ProbeSet.Name IN ({trait_names})").format(
             columns=", ".join(["ProbeSet.{}".format(x) for x in keys]),
             dataset_names=", ".join(["%s"] * len(dataset_names)),
@@ -376,11 +377,10 @@ def geno_traits_info(
         "SELECT "
         "Geno.Name AS trait_name, {columns} "
         "FROM "
-        "Geno, GenoFreeze, GenoXRef "
-        "WHERE "
-        "GenoXRef.GenoFreezeId = GenoFreeze.Id AND GenoXRef.GenoId = Geno.Id AND "
-        "GenoFreeze.Name IN ({dataset_names}) AND "
-        "Geno.Name IN ({trait_names})").format(
+        "Geno INNER JOIN GenoXRef ON GenoXRef.GenoId = Geno.Id "
+        "INNER JOIN GenoFreeze ON GenoFreeze.Id = GenoXRef.GenoFreezeId "
+        "WHERE GenoFreeze.Name IN ({dataset_names}) "
+        "AND Geno.Name IN ({trait_names})").format(
             columns=", ".join(["Geno.{}".format(x) for x in keys]),
             dataset_names=", ".join(["%s"] * len(dataset_names)),
             trait_names=", ".join(["%s"] * len(traits)))
-- 
cgit v1.2.3