Nullify for relation(ships)

Discuss all feature requests you have for a new Servoy versions here. Make sure to be clear about what you want, provide an example and indicate how important the feature is for you

Nullify for relation(ships)

Postby Robert Huber » Wed Jan 20, 2010 12:37 pm

Hi

A Datamodelling Tool allows for relation(ships) - if it's fully supportive - 4 actions to model for a relation(ship):
- Cascading Delete
- No Action
- Deny
- Nullify

With Servoy, Cascading delete, No Action, and Deny, can be implemented with the help of the 3 checkboxes available in the Options part of the relation.
But Nullify, which means: Set the foreign key column(s) to null if the record on the 1-side is deleted, doesn't exist in Servoy ( Servoy 4 is my reference).

My request is to add this functionality, i. e. to include, for example, a checkbox named Nullify (see Note 1). This would greatly reduce coding effort, as Nullify has to be done by JavaScipt coding right now. The relation already "knows" which column(s) are affected and could easely nullify the foreign key column(s) if this option is set.

Best regards, Robert

Note 1) I say checkbox as a quick and dirty solution for the current state (display) of these options. The reason for this statement is that I think the current grouping is not very well done, as it's a problem of OR's and AND's, how they can be applied. And the current checkboxes don't reflect that. Only a few combinations of the 3 checkboxes are useful.
I didn't think in depth about it but assume a better arrangement would be of checkboxes and radio buttons to reflect what is when useful.
Robert Huber
7r gmbh, Switzerland
SAN Developer
www.seven-r.ch
User avatar
Robert Huber
 
Posts: 1239
Joined: Tue Aug 23, 2005 6:52 pm
Location: Schaffhausen, Switzerland

Re: Nullify for relation(ships)

Postby martinh » Wed Jan 20, 2010 1:55 pm

Good request Robert,

I totally agree with you

Martin
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Nullify for relation(ships)

Postby Robert Huber » Wed Jan 20, 2010 3:28 pm

Thanks, Martin :-)
Robert Huber
7r gmbh, Switzerland
SAN Developer
www.seven-r.ch
User avatar
Robert Huber
 
Posts: 1239
Joined: Tue Aug 23, 2005 6:52 pm
Location: Schaffhausen, Switzerland

Re: Nullify for relation(ships)

Postby jcompagner » Thu Jan 21, 2010 1:54 pm

make a feature request for this.

Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.
Johan Compagner
Servoy
User avatar
jcompagner
 
Posts: 8829
Joined: Tue May 27, 2003 7:26 pm
Location: The Internet

Re: Nullify for relation(ships)

Postby martinh » Thu Jan 21, 2010 2:29 pm

jcompagner wrote:make a feature request for this.

Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.


Johan,

In my opinion this nullable feature should only work on:

On delete product, put NULL value in the child records (like orderlines)

Now we have the following options:
    Allow parent delete, when having related records
    Delete related records

I would add an extra option:

    Nullify related records

Of course Delete related records and Nullify related records can not be selected at the same time

The case you describe

Code: Select all
record.orderlines_to_product.deleteRecord(1)


shoud update also the other 10K of orderlines. I just hope that there is nobody that uses a construction like above :?

Martin
Martin
------------------------------------------------
Servoy Developer
Version 5.2.10/5.2.13
Java version 1.6 update 31
Database SQL Server 2008 R2
martinh
 
Posts: 857
Joined: Wed May 09, 2007 5:34 pm
Location: Belgium

Re: Nullify for relation(ships)

Postby Robert Huber » Sat Jan 23, 2010 1:55 pm

Hi Johann

jcompagner wrote:make a feature request for this.


I have done a feature request.

jcompagner wrote:Problem i see with this is do people also expect that the whole data really stays in sync?
i mean i have this:

orderlines has a productid -> product.productid

now i have many orderlines pointing to the same productid

then you do through one of those

orderlines record.orderlines_to_product.deleteRecord(1)

then if we have nullable we could null the productid in that orderlines record
But what about the rest? the 10K orderlines records that also have a reference to that productid?

if so of we have that and we do just in a none related foundset based on products deleteRecord(1) what should happen then?
so i think nullable can quickly result in inconsistent data in the database, the database itself should also handle these events.


Nullify means set the foreign key column(s) to null if the 1-side record is deleted (the master record of a master - details situation). So you describe above a rare situation, where the master record is being deleted from a detail record. But nevertheless, the effect is, you just delete the master record. And the nullify rule says, if you delete the master record, nullify the details records foreign key.
The answer for your above described situation is: Nullify ALL details records foreign key for that relation! (in your example nullify all the foreign keys (of the p.product_to_orderlines.o relation) of the 10k orderlines.

The question is not how the master record (productid) is deleted, but when it is deleted, nullify the foreign key.

Best regards, Robert

PS1: If you don't delete the foreign key (currently manually with code developed by the developer) but delete the master record, there are 10k (in your example) of WRONG foreign key data in these records, which of course death data! It even can led to wrong results - for example if some code checks for the existance of the relation by checking if data exists in the foreign key.

PS2: Your example of orderlines record.orderlines_to_product.deleteRecord(1) is very rare, as the detail record normaly does NOT have the right to delete the master record. An ordinary exception to this may be the case when the detail record is the last record to exist, then, if the master - detail relation(ship) is defined mandatory, the master record has to be deleted as well.
Robert Huber
7r gmbh, Switzerland
SAN Developer
www.seven-r.ch
User avatar
Robert Huber
 
Posts: 1239
Joined: Tue Aug 23, 2005 6:52 pm
Location: Schaffhausen, Switzerland


Return to Discuss Feature Requests

Who is online

Users browsing this forum: No registered users and 8 guests