def mane_GRCh37_list(request):
sql = """
SELECT DISTINCT
t1.transcript_id, t1.stable_id as ens_stable_id, t1.stable_id_version as ens_stable_id_version,
relationship_type.shortname as mane_type,
t2.stable_id as refseq_stable_id, t2.stable_id_version as refseq_stable_id_version,
gn1.name as ens_gene_name,
t3.stable_id as grch37_stable_id, t3.stable_id_version as grch37_stable_id_version,
IF(tl3.five_utr_checksum = tl1.five_utr_checksum,'True','False') as five_prime_utr,
'True' as cds,
IF(tl3.three_utr_checksum = tl1.three_utr_checksum,'True','False') as three_prime_utr
FROM
transcript t1
JOIN transcript_release_tag trt1 ON t1.transcript_id=trt1.feature_id
JOIN transcript_release_tag_relationship ON
trt1.transcript_release_id=transcript_release_tag_relationship.transcript_release_object_id
JOIN transcript_release_tag trt2 ON
transcript_release_tag_relationship.transcript_release_subject_id=trt2.transcript_release_id
JOIN transcript t2 ON trt2.feature_id=t2.transcript_id
JOIN relationship_type ON
transcript_release_tag_relationship.relationship_type_id=relationship_type.relationship_type_id
JOIN transcript_gene tg1 ON
t1.transcript_id=tg1.transcript_id
JOIN gene gene1 ON
tg1.gene_id=gene1.gene_id
JOIN gene_names gn1 ON
gene1.name_id=gn1.external_id and gn1.primary_id=1
JOIN transcript t3 ON t3.stable_id = t1.stable_id
AND t3.assembly_id = 1
JOIN translation_transcript tt1 ON tt1.transcript_id = t1.transcript_id
JOIN translation tl1 ON tl1.translation_id = tt1.translation_id
JOIN translation_transcript tt3 ON tt3.transcript_id = t3.transcript_id
JOIN translation tl3 ON tl3.translation_id = tt3.translation_id
WHERE t1.assembly_id = 1001 and tl3.seq_checksum = tl1.seq_checksum ORDER BY gn1.name;
"""
with connections['tark'].cursor() as cursor:
cursor.execute(sql)
results = ReleaseUtils.dictfetchall(cursor)
page = request.GET.get('page', 1)
paginator = Paginator(results,25)
try:
results = paginator.page(page)
except PageNotAnInteger:
results = paginator.page(1)
except EmptyPage:
results = paginator.page(paginator.num_pages)
return render(
request,
'mane_GRCh37_list.html',
context={
'results': results
}
)
def manelist(request):
sql = """
SELECT DISTINCT
t1.transcript_id, t1.stable_id as ens_stable_id, t1.stable_id_version as ens_stable_id_version,
relationship_type.shortname as mane_type,
t2.stable_id as refseq_stable_id, t2.stable_id_version as refseq_stable_id_version,
gn1.name as ens_gene_name
FROM
transcript t1
JOIN transcript_release_tag trt1 ON t1.transcript_id=trt1.feature_id
JOIN transcript_release_tag_relationship ON
trt1.transcript_release_id=transcript_release_tag_relationship.transcript_release_object_id
JOIN transcript_release_tag trt2 ON
transcript_release_tag_relationship.transcript_release_subject_id=trt2.transcript_release_id
JOIN transcript t2 ON trt2.feature_id=t2.transcript_id
JOIN relationship_type ON
transcript_release_tag_relationship.relationship_type_id=relationship_type.relationship_type_id
JOIN transcript_gene tg1 ON
t1.transcript_id=tg1.transcript_id
JOIN gene gene1 ON
tg1.gene_id=gene1.gene_id
JOIN gene_names gn1 ON
gene1.name_id=gn1.external_id
where gn1.primary_id=1 ORDER BY gn1.name;
"""
with connections['tark'].cursor() as cursor:
cursor.execute(sql)
results = ReleaseUtils.dictfetchall(cursor)
return render(
request,
'mane_list.html',
context={
'results': results
}
)
def feature_diff(request,
feature,
from_release,
to_release,
direction="changed",
source="Ensembl"):
"""
Get the list of features that are different between two releases in the context (addition, deletion or
change)
Parameters
----------
feature : str
version : int
direction : str
One of gained|removed|changed
Returns
-------
count : int
"""
# print("feature {} from_release {}, to_release {}, direction {}, source {} ".format(feature, from_release, to_release, direction, source))
sql = """
SELECT
v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version
FROM
(
SELECT
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v0
#DIRECTION# JOIN (
SELECT
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v1 ON (v0.stable_id=v1.stable_id)
WHERE
#OUTER_WHERE#;
"""
sql = sql.replace('#FEATURE#', feature)
if direction == 'removed':
sql = sql.replace('#DIRECTION#', 'LEFT')
sql = sql.replace('#OUTER_WHERE#', 'v1.stable_id IS NULL')
elif direction == 'gained':
sql = sql.replace('#DIRECTION#', 'RIGHT')
sql = sql.replace('#OUTER_WHERE#', 'v0.stable_id IS NULL')
else:
sql = sql.replace('#DIRECTION#', '')
sql = sql.replace('#OUTER_WHERE#',
'v0.stable_id_version!=v1.stable_id_version')
# print(sql)
with connections['tark'].cursor() as cursor:
cursor.execute(sql, [
str(from_release),
source,
str(to_release),
source,
])
results = ReleaseUtils.dictfetchall(cursor)
return render(request,
'feature_diff_list.html',
context={
'feature': feature,
'from_release': from_release,
'to_release': to_release,
'source': source,
'direction': direction,
'results': results
})
def feature_diff(request, feature, from_release, to_release, direction="changed", source="Ensembl"):
"""
Get the list of features that are different between two releases in the context (addition, deletion or
change)
Parameters
----------
feature : str
version : int
direction : str
One of gained|removed|changed
Returns
-------
count : int
"""
# print("feature {} from_release {}, to_release {}, direction {}, source {} ".format(feature, from_release, to_release, direction, source))
# Change the original sql query to include biotypes from gene and transcript tables
sql = """
SELECT
v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version, v1.biotype as new_biotype, case when v1.biotype=v0.biotype then ' ' else v0.biotype end as previous_biotype
FROM
(
SELECT
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
#FEATURE#.biotype,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v0
#DIRECTION# JOIN (
SELECT
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
#FEATURE#.biotype,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v1 ON (v0.stable_id=v1.stable_id)
WHERE
#OUTER_WHERE#;
"""
# In Release stats gene count details page, display gene name along with stable_id, if gene name is present in database
if feature == 'gene':
sql = """
SELECT
v0.gene_symbol as from_gene, v0.stable_id as from_stable_id, v0.stable_id_version as from_stable_id_version, v1.gene_symbol as to_gene, v1.stable_id as to_stable_id, v1.stable_id_version as to_stable_id_version,v1.biotype as new_biotype, case when v1.biotype=v0.biotype then ' ' else v0.biotype end as previous_biotype
FROM
(
SELECT
IF(gn.name IS NULL, '', gn.name) as gene_symbol,
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
#FEATURE#.biotype,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
LEFT JOIN gene_names AS gn ON (#FEATURE#.name_id = gn.external_id) AND (gn.primary_id = 1)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v0
#DIRECTION# JOIN (
SELECT
IF(gn.name IS NULL, '', gn.name) as gene_symbol,
#FEATURE#.stable_id,
#FEATURE#.stable_id_version,
#FEATURE#.biotype,
f_tag.feature_id,
rs.shortname,
rs.description,
rs.assembly_id
FROM
#FEATURE#
JOIN #FEATURE#_release_tag AS f_tag ON (#FEATURE#.#FEATURE#_id=f_tag.feature_id)
JOIN release_set AS rs ON (f_tag.release_id=rs.release_id)
JOIN release_source AS rst ON (rs.source_id=rst.source_id)
LEFT JOIN gene_names AS gn ON (#FEATURE#.name_id = gn.external_id) AND (gn.primary_id = 1)
WHERE
rs.shortname=%s AND
rst.shortname=%s
) AS v1 ON (v0.stable_id=v1.stable_id)
WHERE
#OUTER_WHERE#;
"""
sql = sql.replace('#FEATURE#', feature)
if direction == 'removed':
sql = sql.replace('#DIRECTION#', 'LEFT')
sql = sql.replace('#OUTER_WHERE#', 'v1.stable_id IS NULL')
elif direction == 'gained':
sql = sql.replace('#DIRECTION#', 'RIGHT')
sql = sql.replace('#OUTER_WHERE#', 'v0.stable_id IS NULL')
else:
sql = sql.replace('#DIRECTION#', '')
sql = sql.replace(
'#OUTER_WHERE#',
'v0.stable_id_version!=v1.stable_id_version'
)
# print(sql)
with connections['tark'].cursor() as cursor:
cursor.execute(
sql,
[
str(from_release),
source,
str(to_release),
source,
]
)
results = ReleaseUtils.dictfetchall(cursor)
return render(
request,
'feature_diff_list.html',
context={
'feature': feature,
'from_release': from_release,
'to_release': to_release,
'source': source,
'direction': direction,
'results': results
}
)