Servoy creating relationship out of foreign key constraint

Classic Servoy
1

Hi All

I think I found a fundamental problem with Servoy being able (by pressing the Create button in the Relations window) to create a relationship (relation) out of a foreign key constraint. As I create all my Data Definition Language (DDL) statements automatically out of the EOModeler, it’s nice to get this relationships by pressing a button, but the relationship is created in the wrong direction!

As was also mentioned by Neale on this discussion, see: http://forum.servoy.com/viewtopic.php?t=1513

Neale:
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.

Servoy should create the relationship (defined by the foreign key constraint) from the m-side to the 1-side. That means the way it’s now created by Servoy leds to a lot of confusion (the relationship name is the one from the m-side but the relationship is from the 1-side to the m-side created).

By the way, what means the lock symbol in automatically created relationships compared to the normal symbol and what does it lock or prevent?

I would like to hear what others think (encountered) and what are the plans for a future release?

Best regards, Robert

Servoy Developer
Version R2 2.2.3-build 335
Java version 1.5.0_05-48 (Mac OS X 10.4.3)

PS: I also checked back with the generated SQL statements from Oracle Case Designer, which gives the same result (same SQL DDL statements, which was to be expected) as the EOModeler.

PS: A little annoyance: When selecting a database in the Relations window (popup) and pressing the Create button, the popup jumps to the top most db, but should stay on the selected db.

2

Hello Servoy developers

No one responded to my question, so I am not sure if nobody is using Servoy with a generated SQL database code? I would love to know what are others experience with Servoy creating automatically relationships from the data model?

Best regards, Robert

3

Hi Robert,

My thoughts on the matter(s):

By the way, what means the lock symbol in automatically created relationships compared to the normal symbol and what does it lock or prevent?

This is pretty straightforward. The lock symbol simply means that the relationship is created by the backend db. The other symbol means that it is created by Servoy and thus ‘editable’ within Servoy whereas the ‘lock’ symbol means that you have to use the db tools to edit the relationship.

Servoy should create the relationship (defined by the foreign key constraint) from the m-side to the 1-side. That means the way it’s now created by Servoy leds to a lot of confusion (the relationship name is the one from the m-side but the relationship is from the 1-side to the m-side created).

In this case, when you ‘press the button’, Servoy isn’t ‘creating’ anything at all. It is simply reading what the backend relationship is (or more precisely what comes through the JDBC connection of that relationship). Personally I think it would be a bit dangerous for Servoy to ‘auto-create’ child-parent relationships just because the parent-child relationship is there in the backend db. For one thing most of the time I have absolutely no need for that relationship and that just makes the relationship tree much more cluttered. Secondly if I do want that relationship it is quite possible I don’t want it with the same keys as the parent-child. Personally, I would rather that that relationship be ‘created’ manually within Servoy on an ‘as needed’ basis. Is it really that much work to create that relationship when you need it? I’m sure everyone’s situation is different and you must have a ton of relationships in the reverse direction where it would be nice to not have to recreate them. To me, though, at most it should be an option, certainly not the default behavior. Now if the same relationship can be used in both directions (and thus keeps just one relationship in the Servoy relationship tree) that would be fine with me. I don’t know if the developers ever decided that was feasible and/or whether there was any overhead or other problems attached to doing that.

John

PS: A little annoyance: When selecting a database in the Relations window (popup) and pressing the Create button, the popup jumps to the top most db, but should stay on the selected db.

Yes that has been there for a long time and I just forget about it. But I’m glad you pointed it out because it is disconcerting as well as a nuisance! I don’t think it needs to go to the top of list but it would be nice to be ‘fixed’ sometime!

4

Hi John and all others

john.allen:
Hi Robert,

My thoughts on the matter(s):

By the way, what means the lock symbol in automatically created relationships compared to the normal symbol and what does it lock or prevent?

john.allen:
This is pretty straightforward. The lock symbol simply means that the relationship is created by the backend db. The other symbol means that it is created by Servoy and thus ‘editable’ within Servoy whereas the ‘lock’ symbol means that you have to use the db tools to edit the relationship.

Thanks for that explanations, sometimes it is just good to be sure how someone else is seeing it, i. e. looking at a given fact.

Servoy should create the relationship (defined by the foreign key constraint) from the m-side to the 1-side. That means the way it’s now created by Servoy leds to a lot of confusion (the relationship name is the one from the m-side but the relationship is from the 1-side to the m-side created).

john.allen:
In this case, when you ‘press the button’, Servoy isn’t ‘creating’ anything at all. It is simply reading what the backend relationship is (or more precisely what comes through the JDBC connection of that relationship). Personally I think it would be a bit dangerous for Servoy to ‘auto-create’ child-parent relationships just because the parent-child relationship is there in the backend db. For one thing most of the time I have absolutely no need for that relationship and that just makes the relationship tree much more cluttered. Secondly if I do want that relationship it is quite possible I don’t want it with the same keys as the parent-child. Personally, I would rather that that relationship be ‘created’ manually within Servoy on an ‘as needed’ basis. Is it really that much work to create that relationship when you need it? I’m sure everyone’s situation is different and you must have a ton of relationships in the reverse direction where it would be nice to not have to recreate them. To me, though, at most it should be an option, certainly not the default behavior. Now if the same relationship can be used in both directions (and thus keeps just one relationship in the Servoy relationship tree) that would be fine with me. I don’t know if the developers ever decided that was feasible and/or whether there was any overhead or other problems attached to doing that.

John

Yes and no, Servoy is creating a relationship to use within Servoy out of a SQL foreign key constraint command and no it is not changing the db of course, that’s how I see it.
The problem however, I have, that I think the automaticlly created relationship is done in the wrong direction! Servoy autocreates (or reads) from 1 to the m-side, whereas I think it should read from the m to the 1-side.
It is, in my opininion, a lot of work to create all relationships I already have defined with proper relationship names (bidirectional) again in Servoy! And, most important, if I change anything in my data modeler tool, I have to do that change manually also in Servoy. Such a property usually leads to inconsistency. And, in an application, you need at least all your model relationships and may be more, otherwise one wouldn’t have it in the data model.

To sum it up, what I try to say is:

  • In a professional develped app, one has a data model with all the relationships defined (in both directions) with proper relationship names. Servoy can read that information, so why should I build it again manually within Servoy?

  • I think Servoy reading the foreign key constraints creates a relationship in the wrong direction. But I would like to hear the opinion from other developers, working with other db modelling tools.

  • I think Servoy should be able to read the foreign key constraints (as it does already) and create the bidirectional relationship out of it. If it does that, it also must be editable, because Servoy can’t know the relationship name in the other direction, it could only create a generic relationship.

  • It would be nice if Servoy could, when autocreating the relationship as it does now, only use the relationship name portion of the foreign key constraint (may be optional).

My problem at the moment is that, unless someone from Servoy tells me if I am right or wrong and how they do that in version 3 (and later), I am sort of stuck, because I can not start using the autocreated relationships (see my opinion above) and I don’t really like to do all that manually again if I can have it automatically. After all, Servoy is not FileMaker, at least that’s what I’ve been told .-) I am also unsure because nobody from Servoy says anything to that, at least to me, quite important topic.

Best regards and thanks for your opinion, Robert

PS: A little annoyance: When selecting a database in the Relations window (popup) and pressing the Create button, the popup jumps to the top most db, but should stay on the selected db.

john.allen:
Yes that has been there for a long time and I just forget about it. But I’m glad you pointed it out because it is disconcerting as well as a nuisance! I don’t think it needs to go to the top of list but it would be nice to be ‘fixed’ sometime!