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
