path: root/qc_app/dbinsert.py

"Handle inserting data into the database"
import os
import json
from typing import Union
from functools import reduce
from datetime import datetime

import requests
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 . import jobs
from .db_utils import database_connection

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 species() -> tuple:
    "Retrieve the species from the database."
    with database_connection() as conn:
        with conn.cursor(cursorclass=DictCursor) as cursor:
            cursor.execute(
                "SELECT SpeciesId, SpeciesName, LOWER(Name) AS Name, MenuName "
                "FROM Species")
            return tuple(cursor.fetchall())

    return tuple()

def species_by_id(speciesid) -> Union[dict, None]:
    "Retrieve the species from the database by id."
    with database_connection() as conn:
        with conn.cursor(cursorclass=DictCursor) as cursor:
            cursor.execute(
                (
                    "SELECT "
                    "SpeciesId, SpeciesName, LOWER(Name) AS Name, MenuName "
                    "FROM Species WHERE SpeciesId=%s"),
                (speciesid,))
            return cursor.fetchone()

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() 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() 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() 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 groups_by_species(speciesid:int) -> tuple:
    "Retrieve group (InbredSet) information from the database."
    with database_connection() as conn:
        with conn.cursor(cursorclass=DictCursor) as cursor:
            query = "SELECT * FROM InbredSet WHERE SpeciesId=%s"
            cursor.execute(query, (speciesid,))
            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() 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"])
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:
        job = jobs.job(rconn, 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"], default_species=default_species,
                    species=species(), 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"])
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, groups_by_species(speciesid), {})
        return render_template(
            "select_study.html", filename=form["filename"],
            filetype=form["filetype"], 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"])
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() as conn:
            with conn.cursor(cursorclass=DictCursor) as cursor:
                cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeFreeze")
                new_studyid = cursor.fetchone()["last_id"] + 1
                values = (
                    new_studyid, new_studyid, 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() "
                    "VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)")
                cursor.execute(query, values)
                flash("Study created successfully", "alert-success")
                return render_template(
                    "continue_from_create_study.html",
                    filename=form["filename"], filetype=form["filetype"],
                    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() 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() 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() 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"])
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", filename=form["filename"],
            filetype=form["filetype"], species=form["species"],
            genechipid=form["genechipid"], 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"])
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() as conn:
            with conn.cursor(cursorclass=DictCursor) as cursor:
                cursor.execute("SELECT MAX(Id) AS last_id FROM ProbeSetFreeze")
                new_datasetid = cursor.fetchone()["last_id"] + 1
                values = (
                    new_datasetid, new_datasetid, form["avgid"],
                    form.get("datasetname",""), form["datasetname2"],
                    form["datasetfullname"], form["datasetshortname"],
                    datetime.now().date().strftime("%Y-%m-%d"),
                    form["datasetpublic"], form["datasetconfidentiality"],
                    form["datasetdatascale"])
                query = (
                    "INSERT INTO ProbeSetFreeze VALUES"
                    "(%s, %s, %s, %s, %s, %s, %s, %s, NULL, %s, %s, NULL, %s)")
                cursor.execute(query, values)
                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)
    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() as conn:
        with conn.cursor(cursorclass=DictCursor) as cursor:
            cursor.execute(
                "SELECT * FROM ProbeFreeze WHERE ProbeFreezeId=%s",
                (studyid,))
            return cursor.fetchone()

def dataset_by_id(datasetid:int) -> Union[dict, None]:
    "Retrieve a dataset by its id"
    with database_connection() 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"])
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"], species=speciesid, genechipid=genechipid,
            studyid=studyid, datasetid=datasetid,
            the_species=selected_keys(
                species_by_id(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]}")