[ensembl-dev] Core-Funcgen schema/keys?

[William Michels]

Hi Nathan, thanks for your reply!

On Fri, Feb 20, 2015 at 1:32 AM, njohnson <njohnson at ebi.ac.uk> wrote:

> HI WIlliam
> See below for comments.
> Nathan Johnson
> Schema documentation can be found here:
>         http://www.ensembl.org/info/docs/api/funcgen/index.html



Yes I found this png for Funcgen (which is missing Core-like tables), and I
found five pngs for Core (
http://uswest.ensembl.org/info/docs/api/core/core_schema.html).

However, I couldn't find a png that linked the two databases (Core and
Funcgen) together.


Correct,  the funcgen xrefs which are associated with the
> rattus_norvegicus_core_Transcript external_db record with db_release 78_5.
> The xref.dbprimary_acc in this case will be the transcript stable ID.
> >
> > Thus to connect the two top-level databases this leads to a rather
> odd-looking WHERE clause:
> >
> > "WHERE rattus_norvegicus_funcgen_78_5.xref.dbprimary_acc =
> rattus_norvegicus_core_78_5.transcript.stable_id"
> >
> >
> You’re pretty much spot on there, that is the standard way to connect
> between two DBs on the same server, although you’re missing the join to
> external_db as described above.
> Nath
>


What I think you're saying is in addition to using the join or *WHERE*
clause above, I should also be sure to add a *WHERE* clause restricting the
query to Ensembl version_78.5 as thus:  "WHERE
rattus_norvegicus_funcgen_78_5.xref.external_db_id = 2234"

However I don't feel I understand your instruction to do a* JOIN* to
"external_db". The Core schema doesn't indicate that this takes me
anywhere, except to unmapped objects (
http://uswest.ensembl.org/info/docs/api/core/xrefs_core.pdf). Also,
rattus_norvegicus_core_78_5.transcript has no column with the word or
phrase "external" or "external_db".

Do you mean I should take the "external_db_id" column from the respective
"external_db" tables and *JOIN *rattus_norvegicus_funcgen_78_5.external_db
to rattus_norvegicus_core_78_5.external_db ?  I looked at these identifiers
individually and this particular  *JOIN* doesn't make sense:  the
identifiers do not match up.

rattus_norvegicus_funcgen_78_5.external_db.db_name =
"rattus_norvegicus_core_Transcript" AND
 rattus_norvegicus_funcgen_78_5.external_db.db_release = "78_5" results in
a single row "external_db_id" of *2234*
(rattus_norvegicus_funcgen_78_5.external_db.external_db_id).

OTOH rattus_norvegicus_core_78_5.external_db.db_name = "Ens_Rn_transcript"
gives a single row "external_db_id" of *50685*
(rattus_norvegicus_core_78_5.external_db.external_db_id).

I guess the question I'm trying to answer is--if I can't do a reasonable
*JOIN* from Funcgen to Core, how to get Ontology information out of
Funcgen? Currently the rattus_norvegicus_funcgen_78_5.ontology_xref table
is unpopulated, while the same table in Core has 288,664 rows.

Either way, without a schema delineating a key relationship between Core
and Funcgen I don't feel that going forward these queries can be relied
upon in the future.

Thanks, Bill.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20150220/542dd86e/attachment.html>