[BioMart Users] Relations In Biomart v0.8, rc5

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

[BioMart Users] Relations In Biomart v0.8, rc5

Sri Krishna Suresh Kumar
Hello,

I am trying to set up a new biomart based system for our database. I
have few queries that I have in this process and request your help.

When I successfully add a relational database, and view the schema
editor, the relations defined in the database does not get reflected,
and similarly when I deploy, the relations are not shown.

So In order to understand this, I was working on a test database with 3 tables.

In this following set of table creation, the relations are incorrect in Biomart.
CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order
(
 no INT NOT NULL AUTO_INCREMENT,
 product_id1 INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 INDEX (product_id1),
 FOREIGN KEY (product_id1) REFERENCES product(id),
 INDEX (customer_id),
 FOREIGN KEY (customer_id) REFERENCES customer(id)
) ENGINE=INNODB;

While When I use these following statements, I get the desired relation
CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order
(
 no INT NOT NULL AUTO_INCREMENT,
 product_id INT NOT NULL,
 customer_id INT NOT NULL,
 PRIMARY KEY(no),
 INDEX (product_id),
 FOREIGN KEY (product_id) REFERENCES product(id),
 INDEX (customer_id),
 FOREIGN KEY (customer_id) REFERENCES customer(id)
) ENGINE=INNODB;

The difference between the two is just the column name in the
product_order table. Here I notice that the reference is created
between a column with name ID (in parent), and table_ID ( in child)
and not otherwise. Could you confirm me if my observation is correct?

Also, When I deploy or try to materialize, there are few values
suffixed to the column name. I was wondering how and where to change
this behaviour.

Thanks and Regards,
Sri Krishna

PS. If available, could you give me pointers to some tutorials on
setting up biomart for a new user.
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Relations In Biomart v0.8, rc5

Yong Liang
Hi Sri,

By default Biomart use myisam for MySQL, if you use innodb, you need to
uncheck the myisam checkbox when you add a new source, that will keep the
relations you defined in the database.

When you do the materialize, there are some suffix in the column name to
avoid the name conflict.

Yong


On 11-05-23 3:39 PM, "Sri Krishna Suresh Kumar" <[hidden email]>
wrote:

> Hello,
>
> I am trying to set up a new biomart based system for our database. I
> have few queries that I have in this process and request your help.
>
> When I successfully add a relational database, and view the schema
> editor, the relations defined in the database does not get reflected,
> and similarly when I deploy, the relations are not shown.
>
> So In order to understand this, I was working on a test database with 3
> tables.
>
> In this following set of table creation, the relations are incorrect in
> Biomart.
> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
> CREATE TABLE product_order
> (
>  no INT NOT NULL AUTO_INCREMENT,
>  product_id1 INT NOT NULL,
>  customer_id INT NOT NULL,
>  PRIMARY KEY(no),
>  INDEX (product_id1),
>  FOREIGN KEY (product_id1) REFERENCES product(id),
>  INDEX (customer_id),
>  FOREIGN KEY (customer_id) REFERENCES customer(id)
> ) ENGINE=INNODB;
>
> While When I use these following statements, I get the desired relation
> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
> CREATE TABLE product_order
> (
>  no INT NOT NULL AUTO_INCREMENT,
>  product_id INT NOT NULL,
>  customer_id INT NOT NULL,
>  PRIMARY KEY(no),
>  INDEX (product_id),
>  FOREIGN KEY (product_id) REFERENCES product(id),
>  INDEX (customer_id),
>  FOREIGN KEY (customer_id) REFERENCES customer(id)
> ) ENGINE=INNODB;
>
> The difference between the two is just the column name in the
> product_order table. Here I notice that the reference is created
> between a column with name ID (in parent), and table_ID ( in child)
> and not otherwise. Could you confirm me if my observation is correct?
>
> Also, When I deploy or try to materialize, there are few values
> suffixed to the column name. I was wondering how and where to change
> this behaviour.
>
> Thanks and Regards,
> Sri Krishna
>
> PS. If available, could you give me pointers to some tutorials on
> setting up biomart for a new user.
> _______________________________________________
> Users mailing list
> [hidden email]
> https://lists.biomart.org/mailman/listinfo/users


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

Re: [BioMart Users] Relations In Biomart v0.8, rc5

Junjun Zhang
Hi Sri,

Just wanted to add some comments to Yong's reply.

BioMart works with MySQL schema differently depending on its storage
engine. For INNODB, information about primary key/foreign key is retrieved
through JDBC API. For MyISAM, since there is no foreign key information
available from JDBC, we implemented a simple key guessing algorithm (what
you described is correct, and it's part of the key guessing process).

At the moment, there is no easy way to check the storage engine type for a
MySQL db, user needs to indicate the engine type at the time he/she adds a
new mysql db. By default, BioMart assumes it's MyISAM. We are looking into
possible ways to automatically handle that without user's input.

Hope this helps,
Junjun



On 11-05-24 10:30 AM, "Yong Liang" <[hidden email]> wrote:

>Hi Sri,
>
>By default Biomart use myisam for MySQL, if you use innodb, you need to
>uncheck the myisam checkbox when you add a new source, that will keep the
>relations you defined in the database.
>
>When you do the materialize, there are some suffix in the column name to
>avoid the name conflict.
>
>Yong
>
>
>On 11-05-23 3:39 PM, "Sri Krishna Suresh Kumar" <[hidden email]>
>wrote:
>
>> Hello,
>>
>> I am trying to set up a new biomart based system for our database. I
>> have few queries that I have in this process and request your help.
>>
>> When I successfully add a relational database, and view the schema
>> editor, the relations defined in the database does not get reflected,
>> and similarly when I deploy, the relations are not shown.
>>
>> So In order to understand this, I was working on a test database with 3
>> tables.
>>
>> In this following set of table creation, the relations are incorrect in
>> Biomart.
>> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
>> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
>> CREATE TABLE product_order
>> (
>>  no INT NOT NULL AUTO_INCREMENT,
>>  product_id1 INT NOT NULL,
>>  customer_id INT NOT NULL,
>>  PRIMARY KEY(no),
>>  INDEX (product_id1),
>>  FOREIGN KEY (product_id1) REFERENCES product(id),
>>  INDEX (customer_id),
>>  FOREIGN KEY (customer_id) REFERENCES customer(id)
>> ) ENGINE=INNODB;
>>
>> While When I use these following statements, I get the desired relation
>> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
>> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
>> CREATE TABLE product_order
>> (
>>  no INT NOT NULL AUTO_INCREMENT,
>>  product_id INT NOT NULL,
>>  customer_id INT NOT NULL,
>>  PRIMARY KEY(no),
>>  INDEX (product_id),
>>  FOREIGN KEY (product_id) REFERENCES product(id),
>>  INDEX (customer_id),
>>  FOREIGN KEY (customer_id) REFERENCES customer(id)
>> ) ENGINE=INNODB;
>>
>> The difference between the two is just the column name in the
>> product_order table. Here I notice that the reference is created
>> between a column with name ID (in parent), and table_ID ( in child)
>> and not otherwise. Could you confirm me if my observation is correct?
>>
>> Also, When I deploy or try to materialize, there are few values
>> suffixed to the column name. I was wondering how and where to change
>> this behaviour.
>>
>> Thanks and Regards,
>> Sri Krishna
>>
>> PS. If available, could you give me pointers to some tutorials on
>> setting up biomart for a new user.
>> _______________________________________________
>> Users mailing list
>> [hidden email]
>> https://lists.biomart.org/mailman/listinfo/users
>
>
>_______________________________________________
>Users mailing list
>[hidden email]
>https://lists.biomart.org/mailman/listinfo/users

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

Re: [BioMart Users] Relations In Biomart v0.8, rc5

Sri Krishna Suresh Kumar
Thanks Junjun and Yong. That did the trick. The relations are
reflecting properly.

Thanks and Regards,
Sri Krishna

On Tue, May 24, 2011 at 8:55 PM, Junjun Zhang <[hidden email]> wrote:

> Hi Sri,
>
> Just wanted to add some comments to Yong's reply.
>
> BioMart works with MySQL schema differently depending on its storage
> engine. For INNODB, information about primary key/foreign key is retrieved
> through JDBC API. For MyISAM, since there is no foreign key information
> available from JDBC, we implemented a simple key guessing algorithm (what
> you described is correct, and it's part of the key guessing process).
>
> At the moment, there is no easy way to check the storage engine type for a
> MySQL db, user needs to indicate the engine type at the time he/she adds a
> new mysql db. By default, BioMart assumes it's MyISAM. We are looking into
> possible ways to automatically handle that without user's input.
>
> Hope this helps,
> Junjun
>
>
>
> On 11-05-24 10:30 AM, "Yong Liang" <[hidden email]> wrote:
>
>>Hi Sri,
>>
>>By default Biomart use myisam for MySQL, if you use innodb, you need to
>>uncheck the myisam checkbox when you add a new source, that will keep the
>>relations you defined in the database.
>>
>>When you do the materialize, there are some suffix in the column name to
>>avoid the name conflict.
>>
>>Yong
>>
>>
>>On 11-05-23 3:39 PM, "Sri Krishna Suresh Kumar" <[hidden email]>
>>wrote:
>>
>>> Hello,
>>>
>>> I am trying to set up a new biomart based system for our database. I
>>> have few queries that I have in this process and request your help.
>>>
>>> When I successfully add a relational database, and view the schema
>>> editor, the relations defined in the database does not get reflected,
>>> and similarly when I deploy, the relations are not shown.
>>>
>>> So In order to understand this, I was working on a test database with 3
>>> tables.
>>>
>>> In this following set of table creation, the relations are incorrect in
>>> Biomart.
>>> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
>>> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
>>> CREATE TABLE product_order
>>> (
>>>  no INT NOT NULL AUTO_INCREMENT,
>>>  product_id1 INT NOT NULL,
>>>  customer_id INT NOT NULL,
>>>  PRIMARY KEY(no),
>>>  INDEX (product_id1),
>>>  FOREIGN KEY (product_id1) REFERENCES product(id),
>>>  INDEX (customer_id),
>>>  FOREIGN KEY (customer_id) REFERENCES customer(id)
>>> ) ENGINE=INNODB;
>>>
>>> While When I use these following statements, I get the desired relation
>>> CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;
>>> CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB;
>>> CREATE TABLE product_order
>>> (
>>>  no INT NOT NULL AUTO_INCREMENT,
>>>  product_id INT NOT NULL,
>>>  customer_id INT NOT NULL,
>>>  PRIMARY KEY(no),
>>>  INDEX (product_id),
>>>  FOREIGN KEY (product_id) REFERENCES product(id),
>>>  INDEX (customer_id),
>>>  FOREIGN KEY (customer_id) REFERENCES customer(id)
>>> ) ENGINE=INNODB;
>>>
>>> The difference between the two is just the column name in the
>>> product_order table. Here I notice that the reference is created
>>> between a column with name ID (in parent), and table_ID ( in child)
>>> and not otherwise. Could you confirm me if my observation is correct?
>>>
>>> Also, When I deploy or try to materialize, there are few values
>>> suffixed to the column name. I was wondering how and where to change
>>> this behaviour.
>>>
>>> Thanks and Regards,
>>> Sri Krishna
>>>
>>> PS. If available, could you give me pointers to some tutorials on
>>> setting up biomart for a new user.
>>> _______________________________________________
>>> Users mailing list
>>> [hidden email]
>>> https://lists.biomart.org/mailman/listinfo/users
>>
>>
>>_______________________________________________
>>Users mailing list
>>[hidden email]
>>https://lists.biomart.org/mailman/listinfo/users
>
>
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users