def loaded_annotations(id):
"""
Show LOJ of variants and annotations tables.
Display all annotations for every registered user.
Display only annotations for the selected genome.
Provide editing link only for logged user.
"""
db = get_db()
technologies_tmp = db.execute(
"SELECT technology FROM files WHERE genome_id = ?",
(id, ),
).fetchall()
technologies = [i[0].lower() for i in technologies_tmp]
if 'illumina' in technologies:
illumina = True
else:
illumina = False
if 'pacbio' in technologies:
pacbio = True
else:
pacbio = False
if 'nanopore' in technologies:
nanopore = True
else:
nanopore = False
if 'tenx' in technologies:
tenx = True
else:
tenx = False
annotations = db.execute(
## LOJ to get data from 4 tables via respective ids:
"SELECT"
" a.id, a.variant_id,"
" a.bionano_presence, a.bionano_depth, a.bionano_spanning, a.bionano_partial,"
" a.illumina_presence, a.illumina_depth, a.illumina_paired_end, a.illumina_soft_clipped, a.illumina_split_read,"
" a.nanopore_presence, a.nanopore_depth, a.nanopore_soft_clipped, a.nanopore_split_read,"
" a.pacbio_presence, a.pacbio_depth, a.pacbio_soft_clipped, a.pacbio_split_read,"
" a.tenx_presence, a.tenx_depth, a.tenx_soft_clipped, a.tenx_split_read, a.tenx_linked_read,"
" a.user_id, a.comments,"
" g.id, g.genome,"
" u.username,"
" v.chromosome, v.sv_start, v.sv_end, v.sv_type, v.genome_id,"
" GROUP_CONCAT(f.technology), f.genome_id"
" FROM annotations a"
" LEFT OUTER JOIN variants AS v ON v.id = a.variant_id"
" LEFT OUTER JOIN user AS u ON u.id = a.user_id"
" LEFT OUTER JOIN genomes AS g ON g.id = v.genome_id"
" LEFT OUTER JOIN files AS f ON f.genome_id = g.id"
# " WHERE g.id = ?"
" WHERE f.genome_id = ?"
" GROUP BY v.chromosome, v.sv_start, v.sv_end"
" ORDER BY v.chromosome, v.sv_start, v.sv_end asc",
(id, ),
).fetchall()
return render_template("annotation/loaded_annotations.html",
annotations=annotations,
illumina=illumina,
pacbio=pacbio,
nanopore=nanopore,
tenx=tenx)
def get_annotation(id, check_author=True):
"""Get an annotation and its author by id.
Checks that the id exists and optionally that the current user is
the author.
:param id: id of annotation to get
:param check_author: require the current user to be the author
:return: the annotation with author information
:raise 404: if a annotation with the given id doesn't exist
:raise 403: if the current user isn't the author
"""
annotation = (get_db().execute(
"SELECT id, comments, created, user_id, variant_id,"
" bionano_presence, bionano_depth, bionano_spanning, bionano_partial, "
" illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read,"
" nanopore_depth, nanopore_soft_clipped, nanopore_split_read,"
" pacbio_depth, pacbio_soft_clipped, pacbio_split_read,"
" tenx_depth, tenx_soft_clipped, tenx_split_read, tenx_linked_read"
" FROM annotations"
" WHERE id = ?",
(id, ),
).fetchone())
if annotation is None:
abort(404, "Annotation id {0} doesn't exist.".format(id))
# if check_author and annotation["user_id"] != g.user["id"]:
# abort(403)
return annotation
def load_logged_in_user():
"""If a user id is stored in the session, load the user object from
the database into ``g.user``."""
user_id = session.get("user_id")
if user_id is None:
g.user = None
else:
g.user = (get_db().execute("SELECT * FROM user WHERE id = ?",
(user_id, )).fetchone())
def create_annotation(id):
"""Create a new annotation, added to loaded annotations for all users.
id is for current genome.
"""
if request.method == "POST":
chromosome = request.form["chromosome"]
sv_start = request.form["sv_start"]
sv_end = request.form["sv_end"]
sv_type = request.form["sv_type"]
genome_id = id
error = None
if not chromosome or not sv_start or not sv_end or not sv_type:
error = "All fields are required."
if error is not None:
flash(error)
else:
db = get_db()
# INSERTs only if unique constraints met; chromosome, sv_start, sv_end, sv_type, genome_id:
db.execute(
"INSERT OR IGNORE INTO variants"
" (chromosome, sv_start, sv_end, sv_type, genome_id)"
" VALUES (?, ?, ?, ?, ?);",
(chromosome, sv_start, sv_end, sv_type, genome_id))
variant_id = db.execute("SELECT last_insert_rowid()").fetchone()[0]
# INSERT variant_id into annotations table only for all users but for only this variant_id:
registered_user_ids = db.execute(
"SELECT id FROM user ORDER BY id ASC").fetchall()
for user_id in registered_user_ids:
# db.execute("INSERT OR IGNORE INTO annotations (variant_id, user_id) VALUES (?,?)", (variant_id, user_id[0]))
db.execute(
"INSERT OR IGNORE INTO annotations"
" (variant_id, user_id,"
" bionano_presence, bionano_depth, bionano_spanning, bionano_partial,"
" illumina_presence, illumina_depth, illumina_paired_end, illumina_soft_clipped, illumina_split_read,"
" nanopore_presence, nanopore_depth, nanopore_soft_clipped, nanopore_split_read,"
" pacbio_presence, pacbio_depth, pacbio_soft_clipped, pacbio_split_read,"
" tenx_presence, tenx_depth, tenx_soft_clipped, tenx_split_read, tenx_linked_read)"
" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
(variant_id, user_id[0], "Unsure", "", "", "", "Unsure",
"", "", "", "", "Unsure", "", "", "", "Unsure", "", "",
"", "Unsure", "", "", "", ""))
db.commit()
db.close()
return redirect(
url_for('annotation.loaded_annotations', id=genome_id))
# Obtain the current genome_id for "go back to annotations" (/genome_id/loaded_annotations) in template:
genome_id = id
return render_template("annotation/create_annotation.html",
genome_id=genome_id)
def get_genome(id):
"""Get a genome by its id.
:param id: id of genome to get
:return: the genome
:raise 404: if a genome with the given id doesn't exist
"""
genome = (get_db().execute(
"SELECT * FROM genomes WHERE id = ?",
(id, ),
).fetchone())
if genome is None:
abort(404, "Genome id {0} doesn't exist.".format(id))
return genome
def delete_genomes(id):
"""Delete a genome.
Ensures that the genome exists.
"""
get_genome(id)
db = get_db()
db.execute("DELETE FROM genomes WHERE id = ?", (id, ))
variant_id = [
i for i in db.execute("SELECT id FROM variants WHERE genome_id = ?", (
id, )).fetchall()
]
db.execute("DELETE FROM variants WHERE genome_id = ?", (id, ))
for i in variant_id:
db.execute("DELETE FROM annotations WHERE variant_id = ?", (i[0], ))
db.commit()
return redirect(url_for("annotation.loaded_genomes"))
def delete(id):
"""Delete an annotation.
Ensures that the annotation exists and that the logged in user is the
author of the annotation.
"""
get_annotation(id)
db = get_db()
# Obtain genome_id to redirect to loaded_annotations:
genome_id = db.execute(
## LOJ to get data from multiple tables:
"SELECT"
" v.genome_id"
" FROM annotations a"
" LEFT OUTER JOIN variants AS v ON v.id = a.variant_id").fetchone()[0]
# Delete annotation by id:
db.execute("DELETE FROM annotations WHERE id = ?", (id, ))
db.commit()
# return redirect(url_for("annotation.index"))
return redirect(url_for('annotation.loaded_annotations', id=genome_id))
def login():
"""Log in a registered user by adding the user id to the session."""
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
db = get_db()
error = None
user = db.execute("SELECT * FROM user WHERE username = ?",
(username, )).fetchone()
if user is None:
error = "Incorrect username."
elif not check_password_hash(user["password"], password):
error = "Incorrect password."
if error is None:
# store the user id in a new session and return to the index
session.clear()
session["user_id"] = user["id"]
return redirect(url_for("index"))
flash(error)
return render_template("auth/login.html")
def register():
"""Register a new user.
Validates that the username is not already taken. Hashes the
password for security.
"""
if request.method == "POST":
username = request.form["username"]
password = request.form["password"]
db = get_db()
error = None
if not username:
error = "Username is required."
elif not password:
error = "Password is required."
elif (db.execute("SELECT id FROM user WHERE username = ?",
(username, )).fetchone() is not None):
error = "User {0} is already registered.".format(username)
if error is None:
# the name is available, store it in the database and go to
# the login page
# also create user-specific annotations table
db.execute(
"INSERT INTO user (username, password) VALUES (?, ?)",
(username, generate_password_hash(password)),
)
# This line until commit handles register -> load -> register and register -> load -> register -> register ...:
# Count number rows in annotations:
count = db.execute(
"SELECT count(*) FROM annotations").fetchone()[0]
# A user has registered and loaded before second user or beyond has registered:
if count > 0:
# Get first and only one user_id instance (int, e.g., 1):
set_user_id = db.execute(
"SELECT user_id FROM annotations").fetchone()[0]
# Copy annotations contents but only for the one and only user_id found above:
db.execute(
"CREATE TABLE copied AS SELECT * FROM annotations WHERE user_id = ?",
(set_user_id, ),
)
# Obtain user_id of current logged user:
user_id = db.execute("SELECT id FROM user WHERE username = ?",
(username, )).fetchone()[0]
# Update copied table to have current logged user_id and not one from annotations:
db.execute(
"UPDATE copied SET user_id = ?",
(user_id, ),
)
# copy copied contents to annotations sans id to autoincrement in annotations:
db.execute(
"INSERT INTO annotations"
" (user_id,variant_id,created,"
" bionano_depth,bionano_spanning,bionano_partial,"
" illumina_depth,illumina_paired_end,illumina_soft_clipped,illumina_split_read,"
" nanopore_depth,nanopore_soft_clipped,nanopore_split_read,"
" pacbio_depth,pacbio_soft_clipped,pacbio_split_read,"
" tenx_depth,tenx_soft_clipped,tenx_split_read,tenx_linked_read,"
" comments)"
" SELECT"
" user_id,variant_id,created,"
" bionano_depth,bionano_spanning,bionano_partial,"
" illumina_depth,illumina_paired_end,illumina_soft_clipped,illumina_split_read,"
" nanopore_depth,nanopore_soft_clipped,nanopore_split_read,"
" pacbio_depth,pacbio_soft_clipped,pacbio_split_read,"
" tenx_depth,tenx_soft_clipped,tenx_split_read,tenx_linked_read,"
" comments"
" FROM copied")
# Remove copied to ensure no error on next register:
db.execute("DROP TABLE copied")
db.commit()
return redirect(url_for("auth.login"))
flash(error)
return render_template("auth/register.html")
def example():
"""
Example dataset loaded from /static/data/example
Post submission disabled for now
"""
form_detect = DetectForm()
db = get_db()
if request.method == "POST":
illumina_presence = request.form["i_choices"]
if "idepth_choices" in request.form:
illumina_depth = request.form["idepth_choices"]
else:
illumina_depth = ""
if "ipaired_choices" in request.form:
illumina_paired_end = request.form["ipaired_choices"]
else:
illumina_paired_end = ""
if "isoft_choices" in request.form:
illumina_soft_clipped = request.form["isoft_choices"]
else:
illumina_soft_clipped = ""
if "isplit_choices" in request.form:
illumina_split_read = request.form["isplit_choices"]
else:
illumina_split_read = ""
comments = request.form["comments"]
error = None
if error is not None:
flash(error)
else:
db = get_db()
db.execute(
"UPDATE example"
" SET illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?,"
" comments = ?",
(illumina_presence, illumina_depth, illumina_paired_end,
illumina_soft_clipped, illumina_split_read, comments))
db.commit()
return redirect(url_for("annotation.example"))
# Prepopulating annotation form:
form_detect.i_choices.choices = [('Yes', 'Yes'), ('No', 'No'),
('Unsure', 'Unsure')]
i_detects = db.execute(
"SELECT illumina_presence FROM example").fetchone()[0]
form_detect.i_choices.default = str(i_detects)
form_detect.idepth_choices.choices = BooleanField(default='unchecked')
idepth_detects = db.execute(
"SELECT illumina_depth FROM example").fetchone()[0]
form_detect.idepth_choices.default = str(idepth_detects)
form_detect.ipaired_choices.choices = BooleanField(default='unchecked')
ipaired_detects = db.execute(
"SELECT illumina_paired_end FROM example").fetchone()[0]
form_detect.ipaired_choices.default = str(ipaired_detects)
form_detect.isoft_choices.choices = BooleanField(default='unchecked')
isoft_detects = db.execute(
"SELECT illumina_soft_clipped FROM example").fetchone()[0]
form_detect.isoft_choices.default = str(isoft_detects)
form_detect.isplit_choices.choices = BooleanField(default='unchecked')
isplit_detects = db.execute(
"SELECT illumina_split_read FROM example").fetchone()[0]
form_detect.isplit_choices.default = str(isplit_detects)
form_detect.process() # process choices & default
example = db.execute("SELECT * FROM example").fetchall()
# Processing example setup.csv:
if check_setup_csv('setup.csv') == True:
db = get_db()
with open('setup.csv', 'r') as f:
try:
reader = csv.reader(f)
full_setup_csv = [col for col in reader]
except IOError:
flash("A correctly configured setup.csv file is required.")
example_data = [i for i in full_setup_csv]
else:
flash(
"A correctly configured setup.csv file is required. Did you remove cnv_annotator/static/data/example/setup.csv?"
)
return render_template("annotation/example.html",
form_detect=form_detect,
example=example,
example_data=example_data)
def loaded_genomes():
"""
Loads genome files from setup.csv
Show all genomes currently loaded for logged user
"""
# WILL NEED TO LOWERCASE HG38
if check_setup_csv('setup.csv') == True:
db = get_db()
with open('setup.csv', 'r') as f:
try:
reader = csv.reader(f)
full_setup_csv = [col for col in reader]
except IOError:
flash("A correctly configured setup.csv file is required.")
# Obtain genome names and place into genomes table:
non_header = [
line for line in full_setup_csv if not line[0].startswith('#')
]
genomes = list(set([line[0] for line in non_header]))
for i in genomes:
db.execute(
"INSERT OR IGNORE INTO genomes"
" (genome)"
" VALUES (?)",
(i, ),
)
db.commit()
# Obtain files associated with each genome and place into files table:
# genome_id = db.execute("SELECT id FROM genomes").fetchall()
genome_and_id = db.execute("SELECT id,genome FROM genomes").fetchall()
genome_and_id_dict = {item[0]: item[1] for item in genome_and_id}
to_db = []
for i in non_header:
to_db.append([[key, i[1], i[2], i[3], i[4], i[5], i[6]]
for (key, value) in genome_and_id_dict.items()
if value == i[0]][0])
for i in to_db:
db.execute(
"INSERT OR IGNORE INTO files"
" (genome_id,bam_cram,bai_crai,reference,reference_build,bed,technology)"
" VALUES (?,?,?,?,?,?,?)",
(i[0], i[1], i[2], i[3], i[4], i[5], i[6].lower()),
)
db.commit()
# Insert BED into variants table
# Obtain genome, genome_id, and bed:
# genome_id_and_bed = db.execute("SELECT genome,id,bed FROM genomes").fetchall()
genome_id_and_bed = db.execute(
"SELECT"
" g.genome, g.id,"
" f.bed"
" FROM files f"
" LEFT OUTER JOIN genomes AS g ON f.genome_id = g.id").fetchall()
# Open BED and obtain 4 cols along with genome_name and genome_id for this BED:
for genome, genome_id, bed in genome_id_and_bed:
if os.path.exists('cnv_annotator/static/data/public/' + bed):
# print(genome, genome_id, bed)
try:
with open('cnv_annotator/static/data/public/' + bed,
'r') as fin:
dr = csv.reader(fin, delimiter='\t')
to_db = [(i[0], i[1], i[2], i[3], genome_id)
for i in dr]
except IOError:
flash(".bed file does not exist")
# INSERTs only if unique constraints met; genome_id, chromosome, sv_start, sv_end, sv_type:
db.executemany(
"INSERT OR IGNORE INTO variants (chromosome, sv_start, sv_end, sv_type, genome_id) VALUES (?, ?, ?, ?, ?);",
to_db)
db.execute(
"INSERT OR IGNORE INTO annotations (variant_id) SELECT id FROM variants"
)
db.execute(
"UPDATE OR IGNORE annotations SET user_id = ?",
(g.user['id'], ),
)
db.execute("DELETE FROM annotations WHERE user_id IS NULL")
db.execute(
"UPDATE OR IGNORE annotations"
" SET bionano_presence = ?, bionano_depth = ?, bionano_spanning = ?, bionano_partial = ?,"
" illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?,"
" nanopore_presence = ?, nanopore_depth = ?, nanopore_soft_clipped = ?, nanopore_split_read = ?,"
" pacbio_presence = ?, pacbio_depth = ?, pacbio_soft_clipped = ?, pacbio_split_read = ?,"
" tenx_presence = ?, tenx_depth = ?, tenx_soft_clipped = ?, tenx_split_read = ?, tenx_linked_read = ?",
("Unsure", "", "", "", "Unsure", "", "", "", "", "Unsure",
"", "", "", "Unsure", "", "", "", "Unsure", "", "", "",
""))
db.commit()
## Handles register -> register -> ... -> load:
# Get all user_ids which not yet have annotations assigned to them:
tmp = db.execute(
"SELECT id FROM user WHERE NOT EXISTS (SELECT user_id FROM annotations WHERE annotations.user_id = user.id)"
).fetchall()
# Places these ids into a list:
if len(tmp) > 0:
user_id_list = [i[0] for i in tmp]
# Iterate over list of registered ids not having annotations and assign annotations to them:
for i in user_id_list:
db.execute(
"CREATE TEMPORARY TABLE tmp AS SELECT * FROM annotations")
db.execute("UPDATE tmp SET id = NULL")
db.execute(
"UPDATE tmp SET user_id = ?",
(i, ),
)
db.execute(
"INSERT OR IGNORE INTO annotations SELECT * FROM tmp")
db.execute("DROP TABLE tmp")
db.commit()
genomes = db.execute("SELECT * from genomes").fetchall()
else:
flash("A correctly configured setup.csv file is required.")
return render_template("annotation/loaded_genomes.html", genomes=genomes)
def update(id):
"""Update an annotation if the current user is the author."""
annotation = get_annotation(id)
form_detect = DetectForm()
db = get_db()
if request.method == "POST":
# bionano_presence = request.form["b_choices"]
# if "bdepth_choices" in request.form:
# bionano_depth = request.form["bdepth_choices"]
# else:
# bionano_depth = ""
# if "bmols_choices" in request.form:
# bionano_molecules_spanning = request.form["bmols_choices"]
# else:
# bionano_molecules_spanning = ""
# if "bpartial_choices" in request.form:
# bionano_partial_molecules = request.form["bpartial_choices"]
# else:
# bionano_partial_molecules = ""
if "i_choices" in request.form:
illumina_presence = request.form["i_choices"]
else:
illumina_presence = ""
if "idepth_choices" in request.form:
illumina_depth = request.form["idepth_choices"]
else:
illumina_depth = ""
if "ipaired_choices" in request.form:
illumina_paired_end = request.form["ipaired_choices"]
else:
illumina_paired_end = ""
if "isoft_choices" in request.form:
illumina_soft_clipped = request.form["isoft_choices"]
else:
illumina_soft_clipped = ""
if "isplit_choices" in request.form:
illumina_split_read = request.form["isplit_choices"]
else:
illumina_split_read = ""
if "n_choices" in request.form:
nanopore_presence = request.form["n_choices"]
else:
nanopore_presence = ""
if "ndepth_choices" in request.form:
nanopore_depth = request.form["ndepth_choices"]
else:
nanopore_depth = ""
if "nsoft_choices" in request.form:
nanopore_soft_clipped = request.form["nsoft_choices"]
else:
nanopore_soft_clipped = ""
if "nsplit_choices" in request.form:
nanopore_split_read = request.form["nsplit_choices"]
else:
nanopore_split_read = ""
if "p_choices" in request.form:
pacbio_presence = request.form["p_choices"]
else:
pacbio_presence = ""
if "pdepth_choices" in request.form:
pacbio_depth = request.form["pdepth_choices"]
else:
pacbio_depth = ""
if "psoft_choices" in request.form:
pacbio_soft_clipped = request.form["psoft_choices"]
else:
pacbio_soft_clipped = ""
if "psplit_choices" in request.form:
pacbio_split_read = request.form["psplit_choices"]
else:
pacbio_split_read = ""
if "t_choices" in request.form:
tenx_presence = request.form["t_choices"]
else:
tenx_presence = ""
if "tdepth_choices" in request.form:
tenx_depth = request.form["tdepth_choices"]
else:
tenx_depth = ""
if "tsoft_choices" in request.form:
tenx_soft_clipped = request.form["tsoft_choices"]
else:
tenx_soft_clipped = ""
if "tsplit_choices" in request.form:
tenx_split_read = request.form["tsplit_choices"]
else:
tenx_split_read = ""
if "tlinked_choices" in request.form:
tenx_linked_read = request.form["tlinked_choices"]
else:
tenx_linked_read = ""
comments = request.form["comments"]
error = None
# Nothing is required as of yet:
# if not bionano_depth:
# error = "Bionano Depth is required."
if error is not None:
flash(error)
else:
db = get_db()
variant_id = db.execute(
"SELECT variant_id FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
# User already annotated:
db.execute(
"UPDATE annotations"
# " SET bionano_presence = ?, bionano_depth = ?, bionano_spanning = ?, bionano_partial = ?,"
" SET illumina_presence = ?, illumina_depth = ?, illumina_paired_end = ?, illumina_soft_clipped = ?, illumina_split_read = ?,"
" nanopore_presence = ?, nanopore_depth = ?, nanopore_soft_clipped = ?, nanopore_split_read = ?,"
" pacbio_presence = ?, pacbio_depth = ?, pacbio_soft_clipped = ?, pacbio_split_read = ?,"
" tenx_presence = ?, tenx_depth = ?, tenx_soft_clipped = ?, tenx_split_read = ?, tenx_linked_read = ?,"
" comments = ?"
" WHERE (user_id = ? and variant_id = ? and id = ?)",
# (bionano_presence, bionano_depth, bionano_molecules_spanning, bionano_partial_molecules,
(illumina_presence, illumina_depth, illumina_paired_end,
illumina_soft_clipped, illumina_split_read, nanopore_presence,
nanopore_depth, nanopore_soft_clipped, nanopore_split_read,
pacbio_presence, pacbio_depth, pacbio_soft_clipped,
pacbio_split_read, tenx_presence, tenx_depth,
tenx_soft_clipped, tenx_split_read, tenx_linked_read,
comments, g.user["id"], variant_id, id))
db.commit()
return redirect(url_for("annotation.update", id=annotation['id']))
# Obtain total number of annotations to use for "Next Annotation":
total_records = [
i[0]
for i in db.execute('SELECT COALESCE(MAX(id), 0) FROM annotations')
][0]
# Obtain genome id via LOJ:
genome_id = db.execute(
"SELECT genomes.id"
" FROM annotations"
" LEFT JOIN variants ON"
" variants.id = ?"
" LEFT JOIN genomes ON"
" genomes.id = variants.genome_id",
(annotation['variant_id'], ),
).fetchone()[0]
# Obtain Illumina BAM/CRAM and indices:
technologies_check_tmp = db.execute(
"SELECT technology FROM files WHERE genome_id = ?",
(genome_id, ),
).fetchall()
technologies_check = [i[0] for i in technologies_check_tmp]
if 'illumina' in technologies_check:
illumina_str = 'illumina'
genome_bam_cram = db.execute(
"SELECT bam_cram"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
illumina_str,
),
).fetchone()[0]
genome_bai_crai = db.execute(
"SELECT bai_crai"
" FROM files"
" WHERE genome_id = ?",
(genome_id, ),
).fetchone()[0]
else:
genome_bam_cram = 'none'
genome_bai_crai = 'none'
# PacBio:
if 'pacbio' in technologies_check:
pacbio_genome_bam_cram = db.execute(
"SELECT bam_cram"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'pacbio',
),
).fetchone()[0]
pacbio_genome_bai_crai = db.execute(
"SELECT bai_crai"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'pacbio',
),
).fetchone()[0]
pacbio_technology = db.execute(
"SELECT technology"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'pacbio',
),
).fetchone()[0]
else:
pacbio_genome_bam_cram = 'none'
pacbio_genome_bai_crai = 'none'
pacbio_technology = 'none'
# Nanopore:
if 'nanopore' in technologies_check:
nanopore_genome_bam_cram = db.execute(
"SELECT bam_cram"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'nanopore',
),
).fetchone()[0]
nanopore_genome_bai_crai = db.execute(
"SELECT bai_crai"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'nanopore',
),
).fetchone()[0]
nanopore_technology = db.execute(
"SELECT technology"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'nanopore',
),
).fetchone()[0]
else:
nanopore_genome_bam_cram = 'none'
nanopore_genome_bai_crai = 'none'
nanopore_technology = 'none'
# TenX:
if 'tenx' in technologies_check:
tenx_genome_bam_cram = db.execute(
"SELECT bam_cram"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'tenx',
),
).fetchone()[0]
tenx_genome_bai_crai = db.execute(
"SELECT bai_crai"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'tenx',
),
).fetchone()[0]
tenx_technology = db.execute(
"SELECT technology"
" FROM files"
" WHERE genome_id = ? AND technology = ?",
(
genome_id,
'tenx',
),
).fetchone()[0]
else:
tenx_genome_bam_cram = 'none'
tenx_genome_bai_crai = 'none'
tenx_technology = 'none'
genome_name = db.execute(
"SELECT genome"
" FROM genomes"
" WHERE id = ?",
(genome_id, ),
).fetchone()[0]
reference = db.execute(
"SELECT reference"
" FROM files"
" WHERE genome_id = ?",
(genome_id, ),
).fetchone()[0]
reference_build = db.execute(
"SELECT reference_build"
" FROM files"
" WHERE genome_id = ?",
(genome_id, ),
).fetchone()[0]
bed = db.execute(
"SELECT bed"
" FROM files"
" WHERE genome_id = ?",
(genome_id, ),
).fetchone()[0]
technology = db.execute(
"SELECT technology"
" FROM files"
" WHERE genome_id = ?",
(genome_id, ),
).fetchone()[0]
# Obtain other parameters for IGV.js localization:
igv_parameters = db.execute(
"SELECT variants.chromosome, variants.sv_start, variants.sv_end"
" FROM annotations"
" LEFT JOIN variants ON"
" variants.id = ?"
" LEFT JOIN genomes ON"
" genomes.id = variants.genome_id",
(annotation['variant_id'], ),
).fetchone()
# Prepopulating annotation form:
# form_detect.b_choices.choices = [('Yes', 'Yes'), ('No', 'No'), ('Unsure', 'Unsure')]
# b_detects = db.execute("SELECT bionano_presence FROM annotations WHERE id = ?", (id,),).fetchone()[0]
# form_detect.b_choices.default = str(b_detects)
# form_detect.bdepth_choices.choices = BooleanField(default='unchecked')
# bdepth_detects = db.execute("SELECT bionano_depth FROM annotations WHERE id = ?", (id,),).fetchone()[0]
# form_detect.bdepth_choices.default = str(bdepth_detects)
# form_detect.bmols_choices.choices = BooleanField(default='unchecked')
# bmols_detects = db.execute("SELECT bionano_spanning FROM annotations WHERE id = ?", (id,),).fetchone()[0]
# form_detect.bmols_choices.default = str(bmols_detects)
# form_detect.bpartial_choices.choices = BooleanField(default='unchecked')
# bpartial_detects = db.execute("SELECT bionano_partial FROM annotations WHERE id = ?", (id,),).fetchone()[0]
# form_detect.bpartial_choices.default = str(bpartial_detects)
form_detect.i_choices.choices = [('Yes', 'Yes'), ('No', 'No'),
('Unsure', 'Unsure')]
i_detects = db.execute(
"SELECT illumina_presence FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.i_choices.default = str(i_detects)
form_detect.idepth_choices.choices = BooleanField(default='unchecked')
idepth_detects = db.execute(
"SELECT illumina_depth FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.idepth_choices.default = str(idepth_detects)
form_detect.ipaired_choices.choices = BooleanField(default='unchecked')
ipaired_detects = db.execute(
"SELECT illumina_paired_end FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.ipaired_choices.default = str(ipaired_detects)
form_detect.isoft_choices.choices = BooleanField(default='unchecked')
isoft_detects = db.execute(
"SELECT illumina_soft_clipped FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.isoft_choices.default = str(isoft_detects)
form_detect.isplit_choices.choices = BooleanField(default='unchecked')
isplit_detects = db.execute(
"SELECT illumina_split_read FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.isplit_choices.default = str(isplit_detects)
form_detect.n_choices.choices = [('Yes', 'Yes'), ('No', 'No'),
('Unsure', 'Unsure')]
n_detects = db.execute(
"SELECT nanopore_presence FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.n_choices.default = str(n_detects)
form_detect.ndepth_choices.choices = BooleanField(default='unchecked')
ndepth_detects = db.execute(
"SELECT nanopore_depth FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.ndepth_choices.default = str(ndepth_detects)
form_detect.nsoft_choices.choices = BooleanField(default='unchecked')
nsoft_detects = db.execute(
"SELECT nanopore_soft_clipped FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.nsoft_choices.default = str(nsoft_detects)
form_detect.nsplit_choices.choices = BooleanField(default='unchecked')
nsplit_detects = db.execute(
"SELECT nanopore_split_read FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.nsplit_choices.default = str(nsplit_detects)
form_detect.p_choices.choices = [('Yes', 'Yes'), ('No', 'No'),
('Unsure', 'Unsure')]
p_detects = db.execute(
"SELECT pacbio_presence FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.p_choices.default = str(p_detects)
form_detect.pdepth_choices.choices = BooleanField(default='unchecked')
pdepth_detects = db.execute(
"SELECT pacbio_depth FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.pdepth_choices.default = str(pdepth_detects)
form_detect.psoft_choices.choices = BooleanField(default='unchecked')
psoft_detects = db.execute(
"SELECT pacbio_soft_clipped FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.psoft_choices.default = str(psoft_detects)
form_detect.psplit_choices.choices = BooleanField(default='unchecked')
psplit_detects = db.execute(
"SELECT pacbio_split_read FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.psplit_choices.default = str(psplit_detects)
form_detect.t_choices.choices = [('Yes', 'Yes'), ('No', 'No'),
('Unsure', 'Unsure')]
t_detects = db.execute(
"SELECT tenx_presence FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.t_choices.default = str(t_detects)
form_detect.tdepth_choices.choices = BooleanField(default='unchecked')
tdepth_detects = db.execute(
"SELECT tenx_depth FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.tdepth_choices.default = str(tdepth_detects)
form_detect.tsoft_choices.choices = BooleanField(default='unchecked')
tsoft_detects = db.execute(
"SELECT tenx_soft_clipped FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.tsoft_choices.default = str(tsoft_detects)
form_detect.tsplit_choices.choices = BooleanField(default='unchecked')
tsplit_detects = db.execute(
"SELECT tenx_split_read FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.tsplit_choices.default = str(tsplit_detects)
form_detect.tlinked_choices.choices = BooleanField(default='unchecked')
tlinked_detects = db.execute(
"SELECT tenx_linked_read FROM annotations WHERE id = ?",
(id, ),
).fetchone()[0]
form_detect.tlinked_choices.default = str(tlinked_detects)
form_detect.process() # process choices & default
# Obtain techs available for this genome:
technologies_tmp = db.execute(
"SELECT technology FROM files WHERE genome_id = ?",
(genome_id, ),
).fetchall()
technologies = [i[0].lower() for i in technologies_tmp]
if 'illumina' in technologies:
illumina = True
else:
illumina = False
if 'pacbio' in technologies:
pacbio = True
else:
pacbio = False
if 'nanopore' in technologies:
nanopore = True
else:
nanopore = False
if 'tenx' in technologies:
tenx = True
else:
tenx = False
return render_template("annotation/update.html",
annotation=annotation,
total_records=total_records,
genome_id=genome_id,
genome_bam_cram=genome_bam_cram,
genome_bai_crai=genome_bai_crai,
genome_name=genome_name,
reference=reference,
reference_build=reference_build,
bed=bed,
technology=technology,
form_detect=form_detect,
igv_parameters=igv_parameters,
illumina=illumina,
pacbio=pacbio,
nanopore=nanopore,
tenx=tenx,
pacbio_genome_bam_cram=pacbio_genome_bam_cram,
pacbio_genome_bai_crai=pacbio_genome_bai_crai,
nanopore_genome_bam_cram=pacbio_genome_bam_cram,
nanopore_genome_bai_crai=pacbio_genome_bai_crai,
tenx_genome_bam_cram=pacbio_genome_bam_cram,
tenx_genome_bai_crai=pacbio_genome_bai_crai,
pacbio_technology=pacbio_technology,
nanopore_technology=nanopore_technology,
tenx_technology=tenx_technology)