[BioMart Users] Filter tableConstraint requires dataset prefix?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

[BioMart Users] Filter tableConstraint requires dataset prefix?

Richard Hayes
Hi,

Sorry to send this without a list subscription, but https://lists.biomart.org/mailman/listinfo/users is not responding at the moment.

I discovered a configuration issue in our current live Biomart at http://www.phytozome.net/biomart/martview

Select the Phytozome 7.0 Families dataset, enter a valid transcript ID (18801347 is my test case that should return 7 gene clusters) in Filters -> Clusters -> Limit to Clusters With -> PAC Transcript ID. Click on Results and get an exception error that boils down to:

Table 'phytozome_mart_v7_1.members__dm' doesn't exist

I can query directly in MySQL and get a result:

mysql> select * from phytozome_clusters__members__dm where member_transcript_id = 18801347;
+----------------+-----------------+----------------------+------------+
| cluster_id_key | member_proteome | member_transcript_id | is_founder |
+----------------+-----------------+----------------------+------------+
|       28656658 | Egrandis        |             18801347 |          1 |
|       28733888 | Egrandis        |             18801347 |          1 |
|       28779242 | Egrandis        |             18801347 |          1 |
|       28937929 | Egrandis        |             18801347 |          1 |
|       29033551 | Egrandis        |             18801347 |          1 |
|       29126997 | Egrandis        |             18801347 |          1 |
|       28307459 | Egrandis        |             18801347 |          1 |
+----------------+-----------------+----------------------+------------+
7 rows in set (0.09 sec)

In MartEditor, this filter is set with tableConstraint "members__dm", complete with the correct number of underscores. After turning on Log4Perl debugging, I see that the SQL statement for this join is generated as:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.members__dm, phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (members__dm.member_transcript_id = '18801347') AND main.cluster_id_key=members__dm.cluster_id_key AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

Another example join filter that is working is the Annotation filter for KOG Letter using Filters -> Annotation Filters -> Limit to Clusters with KOG Letters and an example KOG letter of Y generates this correct SQL and displays results:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.phytozome_clusters__kog_letter__dm, phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (phytozome_clusters__kog_letter__dm.kog_letter = 'Y') AND main.cluster_id_key=phytozome_clusters__kog_letter__dm.cluster_id_key AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

tableConstraint in MartEditor for the Filter is "kog_letter__dm"

When I update the top broken example on a development copy of this dataset instance to change the tableConstraint from "members__dm" to the full table name "phytozome_clusters__members__dm" I get successful results from the same example PAC Transcript ID 18801347, with SQL generated as:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (phytozome_clusters__members__dm.member_transcript_id = '18801347') AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

Could there be conflict with some other section of the MartEditor config?

We are still running v0.6, but I can reproduce the same error in a dev installation of v0.7 as well.

Thanks,

--
Richard D. Hayes, Ph.D.
Joint Genome Institute / Lawrence Berkeley National Lab
http://www.phytozome.net

_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Filter tableConstraint requires dataset prefix?

Junjun Zhang
Hi Richard,

Sorry for the delay getting you back.

What you described below might be a bug in MartEditor 0.6. You mentioned you could reproduce the same error in 0.7, I am not sure if test in 0.7 was done from very beginning of creating a naïve config or just picking up the config made by 0.6 then picked by 0.7. If it's the latter, you may consider to retest it.

It is correct for tableConstraint set to something like "members__dm", ie, with the dataset name part (the first part) of the table name being omitted. In MartEditor, what you edit is actually a configuration template, this template will be used to generate the actual configuration for each dataset when you do "Export" the configuration from MartEditor to the database, actual configs are stored in the meta tables. In the actual config, tableConstraint will be set to real dm table name. Please see the screenshot below for how it looks like in the case of ensembl gene mart.



In the case of Ensembl gene mart, admin should just need to maintain one template configure, when "Export", the template will be used to generate actual configurations for all different species, such as, human, mouse etc.

You may wonder, what if I don't have multiple species. MartEditor still works in the same way, what you edit is still a template, template that will be used to generate one actual config.

Hope this helps. Let me know if you have further questions.

Best regards,

Junjun



From: Richard Hayes <[hidden email]>
Date: Fri, 19 Aug 2011 19:08:51 -0400
To: "[hidden email]" <[hidden email]>
Subject: [BioMart Users] Filter tableConstraint requires dataset prefix?

Hi,

Sorry to send this without a list subscription, but https://lists.biomart.org/mailman/listinfo/users is not responding at the moment.

I discovered a configuration issue in our current live Biomart at http://www.phytozome.net/biomart/martview

Select the Phytozome 7.0 Families dataset, enter a valid transcript ID (18801347 is my test case that should return 7 gene clusters) in Filters -> Clusters -> Limit to Clusters With -> PAC Transcript ID. Click on Results and get an exception error that boils down to:

Table 'phytozome_mart_v7_1.members__dm' doesn't exist

I can query directly in MySQL and get a result:

mysql> select * from phytozome_clusters__members__dm where member_transcript_id = 18801347;
+----------------+-----------------+----------------------+------------+
| cluster_id_key | member_proteome | member_transcript_id | is_founder |
+----------------+-----------------+----------------------+------------+
|       28656658 | Egrandis        |             18801347 |          1 |
|       28733888 | Egrandis        |             18801347 |          1 |
|       28779242 | Egrandis        |             18801347 |          1 |
|       28937929 | Egrandis        |             18801347 |          1 |
|       29033551 | Egrandis        |             18801347 |          1 |
|       29126997 | Egrandis        |             18801347 |          1 |
|       28307459 | Egrandis        |             18801347 |          1 |
+----------------+-----------------+----------------------+------------+
7 rows in set (0.09 sec)

In MartEditor, this filter is set with tableConstraint "members__dm", complete with the correct number of underscores. After turning on Log4Perl debugging, I see that the SQL statement for this join is generated as:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.members__dm, phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (members__dm.member_transcript_id = '18801347') AND main.cluster_id_key=members__dm.cluster_id_key AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

Another example join filter that is working is the Annotation filter for KOG Letter using Filters -> Annotation Filters -> Limit to Clusters with KOG Letters and an example KOG letter of Y generates this correct SQL and displays results:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.phytozome_clusters__kog_letter__dm, phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (phytozome_clusters__kog_letter__dm.kog_letter = 'Y') AND main.cluster_id_key=phytozome_clusters__kog_letter__dm.cluster_id_key AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

tableConstraint in MartEditor for the Filter is "kog_letter__dm"

When I update the top broken example on a development copy of this dataset instance to change the tableConstraint from "members__dm" to the full table name "phytozome_clusters__members__dm" I get successful results from the same example PAC Transcript ID 18801347, with SQL generated as:

SELECT phytozome_clusters__members__dm.member_transcript_id, main.cluster_id_key, main.cluster_name, main.cluster_node FROM phytozome_mart_v7_1_1.phytozome_clusters__members__dm, phytozome_mart_v7_1_1.phytozome_clusters__clusters__main main WHERE (phytozome_clusters__members__dm.member_transcript_id = '18801347') AND main.cluster_id_key=phytozome_clusters__members__dm.cluster_id_key LIMIT 200

Could there be conflict with some other section of the MartEditor config?

We are still running v0.6, but I can reproduce the same error in a dev installation of v0.7 as well.

Thanks,

--
Richard D. Hayes, Ph.D.
Joint Genome Institute / Lawrence Berkeley National Lab
http://www.phytozome.net

_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users