Initialise the "Expression Data" section.

1 files changed, 406 insertions, 0 deletions

diff --git a/uploader/expression_data/dbinsert.py b/uploader/expression_data/dbinsert.py
new file mode 100644
index 0000000..2116031
--- /dev/null
+++ b/uploader/expression_data/dbinsert.py
@@ -0,0 +1,406 @@
+"Handle inserting data into the database"
+import os
+import json
+from typing import Union
+from functools import reduce
+from datetime import datetime
+
+from redis import Redis
+from MySQLdb.cursors import DictCursor
+from flask import (
+    flash, request, url_for, Blueprint, redirect, render_template,
+    current_app as app)
+
+from uploader.authorisation import require_login
+from uploader.population.models import populations_by_species
+from uploader.db_utils import with_db_connection, database_connection
+from uploader.species.models import species_by_id, all_species as species
+
+from . import jobs
+
+dbinsertbp = Blueprint("dbinsert", __name__)
+
+def render_error(error_msg):
+    "Render the generic error page"
+    return render_template("dbupdate_error.html", error_message=error_msg), 400
+
+def make_menu_items_grouper(grouping_fn=lambda item: item):
+    "Build function to be used to group menu items."
+    def __grouper__(acc, row):
+        grouping = grouping_fn(row[2])
+        row_values = (row[0].strip(), row[1].strip())
+        if acc.get(grouping) is None:
+            return {**acc, grouping: (row_values,)}
+        return {**acc, grouping: (acc[grouping] + (row_values,))}
+    return __grouper__
+
+def genechips():
+    "Retrieve the genechip information from the database"
+    def __organise_by_species__(acc, chip):
+        speciesid = chip["SpeciesId"]
+        if acc.get(speciesid) is None:
+            return {**acc, speciesid: (chip,)}
+        return {**acc, speciesid: acc[speciesid] + (chip,)}
+
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute("SELECT * FROM GeneChip ORDER BY GeneChipName ASC")
+            return reduce(__organise_by_species__, cursor.fetchall(), {})
+
+    return {}
+
+def platform_by_id(genechipid:int) -> Union[dict, None]:
+    "Retrieve the gene platform by id"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                "SELECT * FROM GeneChip WHERE GeneChipId=%s",
+                (genechipid,))
+            return cursor.fetchone()
+
+def studies_by_species_and_platform(speciesid:int, genechipid:int) -> tuple:
+    "Retrieve the studies by the related species and gene platform"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            query = (
+                "SELECT Species.SpeciesId, ProbeFreeze.* "
+                "FROM Species INNER JOIN InbredSet "
+                "ON Species.SpeciesId=InbredSet.SpeciesId "
+                "INNER JOIN ProbeFreeze "
+                "ON InbredSet.InbredSetId=ProbeFreeze.InbredSetId "
+                "WHERE Species.SpeciesId = %s "
+                "AND ProbeFreeze.ChipId = %s")
+            cursor.execute(query, (speciesid, genechipid))
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
+def organise_groups_by_family(acc:dict, group:dict) -> dict:
+    "Organise the group (InbredSet) information by the group field"
+    family = group["Family"]
+    if acc.get(family):
+        return {**acc, family: acc[family] + (group,)}
+    return {**acc, family: (group,)}
+
+def tissues() -> tuple:
+    "Retrieve type (Tissue) information from the database."
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute("SELECT * FROM Tissue ORDER BY Name")
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
+@dbinsertbp.route("/platform", methods=["POST"])
+@require_login
+def select_platform():
+    "Select the platform (GeneChipId) used for the data."
+    job_id = request.form["job_id"]
+    with (Redis.from_url(app.config["REDIS_URL"], decode_responses=True) as rconn,
+          database_connection(app.config["SQL_URI"]) as conn):
+        job = jobs.job(rconn, jobs.jobsnamespace(), job_id)
+        if job:
+            filename = job["filename"]
+            filepath = f"{app.config['UPLOAD_FOLDER']}/{filename}"
+            if os.path.exists(filepath):
+                default_species = 1
+                gchips = genechips()
+                return render_template(
+                    "select_platform.html", filename=filename,
+                    filetype=job["filetype"], totallines=int(job["currentline"]),
+                    default_species=default_species, species=species(conn),
+                    genechips=gchips[default_species],
+                    genechips_data=json.dumps(gchips))
+            return render_error(f"File '{filename}' no longer exists.")
+        return render_error(f"Job '{job_id}' no longer exists.")
+    return render_error("Unknown error")
+
+@dbinsertbp.route("/study", methods=["POST"])
+@require_login
+def select_study():
+    "View to select/create the study (ProbeFreeze) associated with the data."
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+
+        speciesid = form["species"]
+        genechipid = form["genechipid"]
+
+        the_studies = studies_by_species_and_platform(speciesid, genechipid)
+        the_groups = reduce(
+            organise_groups_by_family,
+            with_db_connection(
+                lambda conn: populations_by_species(conn, speciesid)),
+            {})
+        return render_template(
+            "select_study.html", filename=form["filename"],
+            filetype=form["filetype"], totallines=form["totallines"],
+            species=speciesid, genechipid=genechipid, studies=the_studies,
+            groups=the_groups, tissues = tissues(),
+            selected_group=int(form.get("inbredsetid", -13)),
+            selected_tissue=int(form.get("tissueid", -13)))
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")
+
+@dbinsertbp.route("/create-study", methods=["POST"])
+@require_login
+def create_study():
+    "Create a new study (ProbeFreeze)."
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyname"), "study name"
+        assert form.get("inbredsetid"), "group"
+        assert form.get("tissueid"), "type/tissue"
+
+        with database_connection(app.config["SQL_URI"]) as conn:
+            with conn.cursor(cursorclass=DictCursor) as cursor:
+                values = (
+                    form["genechipid"],
+                    form["tissueid"],
+                    form["studyname"],
+                    form.get("studyfullname", ""),
+                    form.get("studyshortname", ""),
+                    datetime.now().date().strftime("%Y-%m-%d"),
+                    form["inbredsetid"])
+                query = (
+                    "INSERT INTO ProbeFreeze("
+                    "ChipId, TissueId, Name, FullName, ShortName, CreateTime, "
+                    "InbredSetId"
+                    ") VALUES (%s, %s, %s, %s, %s, %s, %s)")
+                cursor.execute(query, values)
+                new_studyid = cursor.lastrowid
+                cursor.execute(
+                    "UPDATE ProbeFreeze SET ProbeFreezeId=%s WHERE Id=%s",
+                    (new_studyid, new_studyid))
+                flash("Study created successfully", "alert-success")
+                return render_template(
+                    "continue_from_create_study.html",
+                    filename=form["filename"], filetype=form["filetype"],
+                    totallines=form["totallines"], species=form["species"],
+                    genechipid=form["genechipid"], studyid=new_studyid)
+    except AssertionError as aserr:
+        flash(f"Missing data: {aserr.args[0]}", "alert-error")
+        return redirect(url_for("dbinsert.select_study"), code=307)
+
+def datasets_by_study(studyid:int) -> tuple:
+    "Retrieve datasets associated with a study with the ID `studyid`."
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            query = "SELECT * FROM ProbeSetFreeze WHERE ProbeFreezeId=%s"
+            cursor.execute(query, (studyid,))
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
+def averaging_methods() -> tuple:
+    "Retrieve averaging methods from database"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute("SELECT * FROM AvgMethod")
+            return tuple(cursor.fetchall())
+
+    return tuple()
+
+def dataset_datascales() -> tuple:
+    "Retrieve datascales from database"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor() as cursor:
+            cursor.execute(
+                'SELECT DISTINCT DataScale FROM ProbeSetFreeze '
+                'WHERE DataScale IS NOT NULL AND DataScale != ""')
+            return tuple(
+                item for item in
+                (res[0].strip() for res in cursor.fetchall())
+                if (item is not None and item != ""))
+
+    return tuple()
+
+@dbinsertbp.route("/dataset", methods=["POST"])
+@require_login
+def select_dataset():
+    "Select the dataset to add the file contents against"
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyid"), "study"
+
+        studyid = form["studyid"]
+        datasets = datasets_by_study(studyid)
+        return render_template(
+            "select_dataset.html", **{**form, "studyid": studyid},
+            datasets=datasets, avgmethods=averaging_methods(),
+            datascales=dataset_datascales())
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")
+
+@dbinsertbp.route("/create-dataset", methods=["POST"])
+@require_login
+def create_dataset():
+    "Select the dataset to add the file contents against"
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyid"), "study"
+        assert form.get("avgid"), "averaging method"
+        assert form.get("datasetname2"), "Dataset Name 2"
+        assert form.get("datasetfullname"), "Dataset Full Name"
+        assert form.get("datasetshortname"), "Dataset Short Name"
+        assert form.get("datasetpublic"), "Dataset public specification"
+        assert form.get("datasetconfidentiality"), "Dataset confidentiality"
+        assert form.get("datasetdatascale"), "Dataset Datascale"
+
+        with database_connection(app.config["SQL_URI"]) as conn:
+            with conn.cursor(cursorclass=DictCursor) as cursor:
+                datasetname = form["datasetname"]
+                cursor.execute("SELECT * FROM ProbeSetFreeze WHERE Name=%s",
+                               (datasetname,))
+                results = cursor.fetchall()
+                if bool(results):
+                    flash("A dataset with that name already exists.",
+                          "alert-error")
+                    return redirect(url_for("dbinsert.select_dataset"), code=307)
+                values = (
+                    form["studyid"], form["avgid"],
+                    datasetname, form["datasetname2"],
+                    form["datasetfullname"], form["datasetshortname"],
+                    datetime.now().date().strftime("%Y-%m-%d"),
+                    form["datasetpublic"], form["datasetconfidentiality"],
+                    "williamslab", form["datasetdatascale"])
+                query = (
+                    "INSERT INTO ProbeSetFreeze("
+                    "ProbeFreezeId, AvgID, Name, Name2, FullName, "
+                    "ShortName, CreateTime, OrderList, public, "
+                    "confidentiality, AuthorisedUsers, DataScale) "
+                    "VALUES"
+                    "(%s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, %s, %s)")
+                cursor.execute(query, values)
+                new_datasetid = cursor.lastrowid
+                return render_template(
+                    "continue_from_create_dataset.html",
+                    filename=form["filename"], filetype=form["filetype"],
+                    species=form["species"], genechipid=form["genechipid"],
+                    studyid=form["studyid"], datasetid=new_datasetid,
+                    totallines=form["totallines"])
+    except AssertionError as aserr:
+        flash(f"Missing data {aserr.args[0]}", "alert-error")
+        return redirect(url_for("dbinsert.select_dataset"), code=307)
+
+def study_by_id(studyid:int) -> Union[dict, None]:
+    "Get a study by its Id"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                "SELECT * FROM ProbeFreeze WHERE Id=%s",
+                (studyid,))
+            return cursor.fetchone()
+
+def dataset_by_id(datasetid:int) -> Union[dict, None]:
+    "Retrieve a dataset by its id"
+    with database_connection(app.config["SQL_URI"]) as conn:
+        with conn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                ("SELECT AvgMethod.Name AS AvgMethodName, ProbeSetFreeze.* "
+                 "FROM ProbeSetFreeze INNER JOIN AvgMethod "
+                 "ON ProbeSetFreeze.AvgId=AvgMethod.AvgMethodId "
+                 "WHERE ProbeSetFreeze.Id=%s"),
+                (datasetid,))
+            return cursor.fetchone()
+
+def selected_keys(original: dict, keys: tuple) -> dict:
+    "Return a new dict from the `original` dict with only `keys` present."
+    return {key: value for key,value in original.items() if key in keys}
+
+@dbinsertbp.route("/final-confirmation", methods=["POST"])
+@require_login
+def final_confirmation():
+    "Preview the data before triggering entry into the database"
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyid"), "study"
+        assert form.get("datasetid"), "dataset"
+
+        speciesid = form["species"]
+        genechipid = form["genechipid"]
+        studyid = form["studyid"]
+        datasetid=form["datasetid"]
+        return render_template(
+            "final_confirmation.html", filename=form["filename"],
+            filetype=form["filetype"], totallines=form["totallines"],
+            species=speciesid, genechipid=genechipid, studyid=studyid,
+            datasetid=datasetid, the_species=selected_keys(
+                with_db_connection(lambda conn: species_by_id(conn, speciesid)),
+                ("SpeciesName", "Name", "MenuName")),
+            platform=selected_keys(
+                platform_by_id(genechipid),
+                ("GeneChipName", "Name", "GeoPlatform", "Title", "GO_tree_value")),
+            study=selected_keys(
+                study_by_id(studyid), ("Name", "FullName", "ShortName")),
+            dataset=selected_keys(
+                dataset_by_id(datasetid),
+                ("AvgMethodName", "Name", "Name2", "FullName", "ShortName",
+                 "DataScale")))
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")
+
+@dbinsertbp.route("/insert-data", methods=["POST"])
+@require_login
+def insert_data():
+    "Trigger data insertion"
+    form = request.form
+    try:
+        assert form.get("filename"), "filename"
+        assert form.get("filetype"), "filetype"
+        assert form.get("species"), "species"
+        assert form.get("genechipid"), "platform"
+        assert form.get("studyid"), "study"
+        assert form.get("datasetid"), "dataset"
+
+        filename = form["filename"]
+        filepath = f"{app.config['UPLOAD_FOLDER']}/{filename}"
+        redisurl = app.config["REDIS_URL"]
+        if os.path.exists(filepath):
+            with Redis.from_url(redisurl, decode_responses=True) as rconn:
+                job = jobs.launch_job(
+                    jobs.data_insertion_job(
+                        rconn, filepath, form["filetype"], form["totallines"],
+                        form["species"], form["genechipid"], form["datasetid"],
+                        app.config["SQL_URI"], redisurl,
+                        app.config["JOBS_TTL_SECONDS"]),
+                    redisurl, f"{app.config['UPLOAD_FOLDER']}/job_errors")
+
+            return redirect(url_for("dbinsert.insert_status", job_id=job["jobid"]))
+        return render_error(f"File '{filename}' no longer exists.")
+    except AssertionError as aserr:
+        return render_error(f"Missing data: {aserr.args[0]}")
+
+@dbinsertbp.route("/status/<job_id>", methods=["GET"])
+def insert_status(job_id: str):
+    "Retrieve status of data insertion."
+    with Redis.from_url(app.config["REDIS_URL"], decode_responses=True) as rconn:
+        job = jobs.job(rconn, jobs.jobsnamespace(), job_id)
+
+    if job:
+        job_status = job["status"]
+        if job_status == "success":
+            return render_template("insert_success.html", job=job)
+        if job["status"] == "error":
+            return render_template("insert_error.html", job=job)
+        return render_template("insert_progress.html", job=job)
+    return render_template("no_such_job.html", job_id=job_id), 400