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

2 files changed, 15 insertions, 9 deletions

diff --git a/gn3/computations/partial_correlations.py b/gn3/computations/partial_correlations.py
index 1cc969c..3633a59 100644
--- a/gn3/computations/partial_correlations.py
+++ b/gn3/computations/partial_correlations.py
@@ -628,6 +628,12 @@ def partial_correlations_entry(# pylint: disable=[R0913, R0914, R0911]
         return {
             "status": "not-found",
             "message": "None of the requested control traits were found."}
+    for trait in cntrl_traits:
+        if trait["haveinfo"] == False:
+            warnings.warn(
+                (f"Control traits {trait['trait_fullname']} was not found "
+                 "- continuing without it."),
+                category=UserWarning)
 
     group = primary_trait["db"]["group"]
     primary_trait_data = all_traits_data[primary_trait["trait_name"]]
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)))