<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas","serif";
color:black;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body bgcolor=white lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Plus, a SQL-expert is pretty hard to find in bioinformatics. But I do agree with Rob that the fastest systems use SQL stored procedures. They can be made to be blazingly fast, BTDT, but I hail originally from commercial software development.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>Having said that, I use the ensembl api for accessing ensembl data, when in Rome, do as the Romans. But I always wondered what was all the way under the hood, just standard SQL and perl. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'>I guess the bottom line is at the base of ensembl is it sql executes or sql selects, updates and inserts?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext'>From:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif";color:windowtext'> dev-bounces@ensembl.org [mailto:dev-bounces@ensembl.org] <b>On Behalf Of </b>Rob Sargent<br><b>Sent:</b> Tuesday, December 16, 2014 12:08 PM<br><b>To:</b> dev@ensembl.org<br><b>Subject:</b> Re: [ensembl-dev] SQL query to retrieve gene sequence...<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:10.0pt;font-family:"Courier New"'>I don't know which server you are using but I had these do amazing things, and amazingly fast. Try it, you might like it. :)<br><br>rjs</span><o:p></o:p></p><div><p class=MsoNormal>On 12/16/2014 09:58 AM, Andrew Yates wrote:<o:p></o:p></p></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><p class=MsoNormal>Hi Rob, <o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I won’t ever say it cannot be done just in the database. More I don’t think it’ll be as performant or as easy as the alternatives myself & Kieron suggested :).<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Andy<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>------------<br>Andrew Yates - Ensembl Support Coordinator<br>European Molecular Biology Laboratory<br>European Bioinformatics Institute<br>Wellcome Trust Genome Campus<br>Hinxton, Cambridge<br>CB10 1SD, United Kingdom<br>Tel: +44-(0)1223-492538<br>Fax: +44-(0)1223-494468<br>Skype: andrewyatz<br><a href="http://www.ensembl.org/">http://www.ensembl.org/</a> <o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal>On 16 Dec 2014, at 16:40, Rob Sargent <<a href="mailto:rob.sargent@utah.edu">rob.sargent@utah.edu</a>> wrote:<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal style='margin-bottom:12.0pt'><span style='font-size:10.0pt;font-family:"Courier New"'>A function/stored-procedure using a recursive CTE might be the way for Steve to go.</span><o:p></o:p></p><div><p class=MsoNormal>On 12/16/2014 09:28 AM, Andrew Yates wrote:<o:p></o:p></p></div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><p class=MsoNormal>Hey Steve, <o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>The problem with using the database is that sequence is not stored against the top-level sequences annotation is held against. Instead sequence is held against the contig sequence regions which requires descending through the assembly table an unspecified number of times (once for each mapping e.g. chromosome -> supercontig -> contig). <o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>I would seriously *not* recommend doing this. Not only do you have to deal with descending down the assembly but also having to think about concatenating the sequence & paying attention to the orientation of assembly. Instead you could use the Perl API (probably not an option considering you’re a Python guy), BioMart (you can access unspliced gene sequence quite easily), the REST API or download the full genome sequence from FTP and doing subslices. The faindex index tool from htslib/samtools is pretty good at extracting arbitrary sequence from very large FASTA files.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Andy<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>------------<br>Andrew Yates - Ensembl Support Coordinator<br>European Molecular Biology Laboratory<br>European Bioinformatics Institute<br>Wellcome Trust Genome Campus<br>Hinxton, Cambridge<br>CB10 1SD, United Kingdom<br>Tel: +44-(0)1223-492538<br>Fax: +44-(0)1223-494468<br>Skype: andrewyatz<br><a href="http://www.ensembl.org/">http://www.ensembl.org/</a> <o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><blockquote style='margin-top:5.0pt;margin-bottom:5.0pt'><div><p class=MsoNormal>On 16 Dec 2014, at 16:15, Steve Moss <<a href="mailto:gawbul@gmail.com">gawbul@gmail.com</a>> wrote:<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal><span style='font-size:10.0pt'>Dear EnsEMBL Dev,</span> <o:p></o:p></p><div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>I'm trying to write a raw SQL query to retrieve the sequence for the human BRCA2 gene to compare different methods of accessing EnsEMBL data. I'm currently doing the following, but getting an empty set.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><div><p class=MsoNormal><span style='font-size:10.0pt'>SELECT SUBSTRING(sequence, g.seq_region_start, g.seq_region_end)<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>FROM dna d<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>JOIN gene g<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>ON d.seq_region_id = g.seq_region_id<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>WHERE g.stable_id="ENSG00000139618"<o:p></o:p></span></p></div></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>What am I missing? I think I'm falling short on working out the coord. system mapping stuff. Any pointers to help in fixing please?<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'>Cheers,<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-size:10.0pt'><br>Steve<o:p></o:p></span></p></div></div><div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal>-- <o:p></o:p></p><div><div><table class=MsoNormalTable border=0 cellspacing=0 cellpadding=0 style='outline:0px;font-style:inherit;border-spacing:0px'><tr style='height:22.5pt;outline:0px;font-style:inherit'><td valign=bottom style='padding:0in 0in 0in 0in;height:22.5pt;outline:0px;font-style:inherit'><p class=MsoNormal><span style='font-size:1.0pt'> <o:p></o:p></span></p></td></tr><tr style='outline:0px;font-style:inherit'><td valign=top style='padding:0in 0in 0in 0in;outline:0px;font-style:inherit'><div><p class=MsoNormal style='vertical-align:baseline'><b><span style='font-size:13.5pt;color:#333333'>Steve Moss<o:p></o:p></span></b></p></div><div style='margin-top:2.25pt;outline:0px;font-style:inherit'><p class=MsoNormal style='vertical-align:baseline'><span style='font-size:9.0pt'>about.me/gawbul<o:p></o:p></span></p></div></td></tr><tr style='outline:0px;font-style:inherit'><td valign=top style='padding:6.0pt 0in 0in 0in;outline:0px;font-style:inherit'><p class=MsoNormal align=right style='text-align:right;background:#C5D0E0;vertical-align:baseline'><img border=0 width=88 height=4 id="_x0000_i1025" src="http://d13pix9kaak6wt.cloudfront.net/signature/colorbar.png" alt="Steve Moss on about.me"><o:p></o:p></p></td></tr><tr style='height:15.0pt;outline:0px;font-style:inherit'><td valign=bottom style='padding:0in 0in 0in 0in;height:15.0pt;outline:0px;font-style:inherit'><p class=MsoNormal><span style='font-size:1.0pt'> <o:p></o:p></span></p></td></tr></table></div></div></div><p class=MsoNormal>_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></p></div></blockquote></div><p class=MsoNormal><br><br><br><br><o:p></o:p></p><pre>_______________________________________________<o:p></o:p></pre><pre>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><o:p></o:p></pre><pre>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><o:p></o:p></pre><pre>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></pre></blockquote><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal>_______________________________________________<br>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><br>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><br>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></p></div></blockquote></div><p class=MsoNormal><o:p> </o:p></p></div><p class=MsoNormal><br><br><br><o:p></o:p></p><pre>_______________________________________________<o:p></o:p></pre><pre>Dev mailing list <a href="mailto:Dev@ensembl.org">Dev@ensembl.org</a><o:p></o:p></pre><pre>Posting guidelines and subscribe/unsubscribe info: <a href="http://lists.ensembl.org/mailman/listinfo/dev">http://lists.ensembl.org/mailman/listinfo/dev</a><o:p></o:p></pre><pre>Ensembl Blog: <a href="http://www.ensembl.info/">http://www.ensembl.info/</a><o:p></o:p></pre></blockquote><p class=MsoNormal><o:p> </o:p></p></div></body></html>