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 <[email protected]> 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" <[email protected]> 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" <[email protected]> >>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 >>> [email protected] >>> https://lists.biomart.org/mailman/listinfo/users >> >> >>_______________________________________________ >>Users mailing list >>[email protected] >>https://lists.biomart.org/mailman/listinfo/users > > _______________________________________________ Users mailing list [email protected] https://lists.biomart.org/mailman/listinfo/users
