[BioMart Users] Problem with creating virtual mart

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

[BioMart Users] Problem with creating virtual mart

Jane Charlesworth
Hi,

I'm relatively new to biomart, so pardon me if this is a totally stupid question.

I'm trying to create a virtual mart from a mysql database (tables are isam), but when I open the schema_editor it displays the tables and columns correctly, but doesn't seem to link all the tables to each other the way I expect.

Eg I have a table called "experiment" and one called "massSpec", which ought to be linked by the column "file_name", but no link is shown. I've tried some joins which work with the mysql database, which suggests to me that the tables are linked okay in mysql.

I hope this is something dead simple that I've missed, but the documentation seems to suggest that one can't alter the links between tables in a source schema - is there a way to do this or do I need to redesign my database?

thanks in advance,

Jane Charlesworth

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

Re: [BioMart Users] Problem with creating virtual mart

Joachim Baran-2
Hi!

On 11-05-02 11:36 AM, "Jane Charlesworth" <[hidden email]> wrote:
Eg I have a table called "experiment" and one called "massSpec", which ought to be linked by the column "file_name", but no link is shown. I've tried some joins which work with the mysql database, which suggests to me that the tables are linked okay in mysql.
  Try adding "_key" to the end of the column that links the tables.

  Alternatively you can also use InnoDB and link the tables by primary-/foreign-keys if you do not wish to use the "_key" suffix.

Joachim

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

Re: [BioMart Users] Problem with creating virtual mart

Yong Liang
In reply to this post by Jane Charlesworth
Hi Jane,

Since MyISAM doesn't support foreign key, biomart has it's own keyguessing
algorithm for MyISAM to figure the foreign key and link the tables
automatically. To use keyguessing, the primary key should be defined as
either of:
1. the same as table name;
2. 'tablename'_id
3. id

For example, two tables "experiment" and "massSpec". The primary key for
"experiment" can be "experiment"/"experiment_id"/"id". If table "massSpec"
has a column name "experiment"/"experiment_id"/"id", the two tables will be
linked automatically.

Let me know if you have any problems.

Yong


On 11-05-02 11:36 AM, "Jane Charlesworth" <[hidden email]>
wrote:

> Hi,
>
> I'm relatively new to biomart, so pardon me if this is a totally stupid
> question.
>
> I'm trying to create a virtual mart from a mysql database (tables are isam),
> but when I open the schema_editor it displays the tables and columns
> correctly, but doesn't seem to link all the tables to each other the way I
> expect.
>
> Eg I have a table called "experiment" and one called "massSpec", which ought
> to be linked by the column "file_name", but no link is shown. I've tried some
> joins which work with the mysql database, which suggests to me that the tables
> are linked okay in mysql.
>
> I hope this is something dead simple that I've missed, but the documentation
> seems to suggest that one can't alter the links between tables in a source
> schema - is there a way to do this or do I need to redesign my database?
>
> thanks in advance,
>
> Jane Charlesworth


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

Re: [BioMart Users] Problem with creating virtual mart

Joachim Baran-2
Hi, again!

On 11-05-02 12:12 PM, "Yong Liang" <[hidden email]> wrote:
>[...] biomart has it's own keyguessing algorithm for MyISAM to figure
>the foreign key and link the tables automatically. [...]
>1. the same as table name;
>2. 'tablename'_id
>3. Id

  Please listen to what Yong said for MyISAM. Apparently, I use InnoDB and
PKs/FKs.. :)

Joachim

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