MYSQL Database Defined Relations Problem

[JDW]

Hi,

I'm having a problem where a relationship defined in a MySQL database (via add constraint on a foreign key) is not showing as a Database Defined Relation up when using the 'Create' button in Relations. (All tables are innodb.)

There are several from the same database that are showing up, but that one is not. I've tried several MySQL drivers including the latest alpha.

I don't think it's a problem with the schema.. Any ideas?

Thanks.

[Jan Blok]

Issue confirmed , if the driver does not return the relation info sorted it could happen one is not created, will be fixed in Servoy 2.0 rc8

[JDW]

Hi,

Good to hear, I'd like to use Servoy in databases with a large number of foreign key constraints.

Cheers and thanks.

[Neale]

Is this likely to be related to http://forum.servoy.com/viewtopic.php?t=987 ?

Thanks,
Neale.

[Jan Blok]

The latest mysql driver seems not to have that problem anymore

[JDW]

Hi,

Yes, the latest MySQL driver did allow db-defined relationships to be discovered and listed in Relations and I was seeing them.

The issue was that not all of them were being displayed. I just tested, and as Jan indicated, this does appear to have been resolved in RC8 and is working as expected.

Cheers.

[Neale]

Indeed - with Connector/J 3.0.11 (previously I had 3.0.9) database relations are detected

Interesting, only one-many relations (i.e. no many-one) were detected. Is this "expected" behaviour?

But not all one-many were detected - presumably that would go away with a Servoy upgrade.

Thanks,
Neale.

[jcompagner]

Code: Select all

Interesting, only one-many relations (i.e. no many-one) were detected. Is this "expected" behaviour?

please show me the 2 table create statements (with foreignkey constraints) where you have a one-many and a many-one relations specified...

[Neale]

OK, I cobbled together a small test case which appears to demonstrate the question. SQL:

Code: Select all

-- Created by dia2sql.pl v1.2-5ntb1 (Wed Mar 10 10:25:25 EST 2004)

--
-- Table structure for table 'parent'
--

CREATE TABLE parent (
   id INTEGER NOT NULL NOT NULL,
   details CHAR(10),
   PRIMARY KEY(id)
) TYPE=INNODB;


--
-- Table structure for table 'child1'
--

CREATE TABLE child1 (
   id INTEGER NOT NULL NOT NULL,
   parent_id INTEGER NOT NULL,
   details VARCHAR(10),
   PRIMARY KEY(id),
   INDEX parent_id_idx (parent_id),
   FOREIGN KEY (parent_id) REFERENCES parent (id)
) TYPE=INNODB;


--
-- Table structure for table 'child2'
--

CREATE TABLE child2 (
   id INTEGER NOT NULL NOT NULL,
   parent_id INTEGER NOT NULL,
   details VARCHAR(10),
   PRIMARY KEY(id),
   INDEX parent_id_idx (parent_id),
   FOREIGN KEY (parent_id) REFERENCES parent (id)
) TYPE=INNODB;


MySQL consumed the above into a new database without complaint.

In developer, created a DB server connection to this database to this DB and a new solution; selected relations and the database under "Database defined relations" and then create pondered and came forth with (only) "db_parent_to_child1" - which looks just fine, but rather lonely.

This is with Servoy 1.2(final), Java 1.4.2_03 on Linux, MySQL Connector/J 3.0.11.

Given the recent fix, I'm not concerned that the "parent_to_child2" relation didn't appear.

But, shouldn't this have created a "child1_to_parent" relation too? Or have I missed something here?

Hopefully I can re-test with Servoy 2.0rc<latest> soon.

Thanks,
Neale.

[Neale]

With 2.0rc9 both db_parent_to_child1 and db_parent_to_child1 are detected/created

Is there something else we need to do to get the inverse relationships?

Thanks,
Neale.

[jcompagner]

the database uses those foreignkey constraints both ways so it doesn't have 2 relations for the same thing.

So for both ways we have to do that in servoy itself.. Because our relations can also be globals on the left side our relations can't be 2 ways all the time.. So only for database column relations onle (right and left side)

We will look if we can make this better. (for example look at the db column relations only 2 way or make an easy option where you can generate the invert with one click)

[Neale]

Yes please

a) AFAICS, the db-defined relation defines the child-to-parent and thus implies the parent-to-child. I can't imagine why we wouldn't want both available in servoy.

b) In the case of a manually defined option not involving a Global, a selectable option to automagically create the inverse relationship would be very cool.

Thanks,
Neale.