db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ronald Rudy <ronchal...@gmail.com>
Subject Re: how to cascade on update? Not possible?
Date Tue, 16 Mar 2010 13:26:17 GMT
It's similar to what you're doing, but doing it at the database level..

The reasons you would prefer it at the DB level would be so that if these updates came from
various locations in the code, the update would still be supportable and cascade the values..
There are rarely cases where you know with 100% certainty for the lifecycle of the application
that specific updates will only happen in one location, so it's usually best to enforce things
like this at the DB level.. Even if you think you know 100% that this is the only place it
will happen, you are probably wrong :-).. It's really a question of whether it's okay to do
in the Java code, or to do at the DB level..

I haven't done something like this in a long while, and I'm not certain if Derby triggers
support it, but something like this conceptually:

ON BEFORE UPDATE ON domaintablename OF columnname FOR EACH ROW
	drop/disable constraint(s) to allow the update;


ON AFTER UPDATE ON domaintablename OF columnname FOR EACH ROW
	cascade update to referencing table(s) based on disabled constraints;
	recreate/enable constraint(s) ensuring db integrity;

I admit the above isn't perfect, but I've done it with Oracle IIRC (I haven't done a lot of
RDBMS development in 4-5 years).. But conceptually it would be the same even in Java - to
perform the update I suspect you will need to temporarily disable/remove the constraints then
reapply them.. 

It should be done within a transaction that can be rolled back.. 

You could try to batch all the above updates in Java in a transaction and see if committing
them all together works, but I suspect it will throw from the DB.. 


On Mar 16, 2010, at 8:59:15 AM, Gabriele Kahlout wrote:

> what is the best approach? What you describe seems a DEFFERABLE
> constraint/transaction (checking).
> "|Manually cascade the updates| , that's what I'm doing. Not?
> 
> 2010/3/16, Ronald Rudy <ronchalant@gmail.com>:
>> I haven't worked much with Derby triggers but that seems like a logical
>> place to start.  You may run into situations where you want to (if
>> possible?) disable the constraints temporarily while you manually cascade
>> the updates, and put them back into place once the updates have been
>> completed, but I think that's probably the best approach.
>> 
>> -Ron
>> 
>> 
>> On Mar 16, 2010, at 3:26:29 AM, Gabriele Kahlout wrote:
>> 
>>> Okay, and how do you recommend I work around it?
>>> I should retrieve the effected tuples (the referencing) and store
>>> their values (onto the stack/heap) and then delete the records (since
>>> the referencing fields are primary keys), and then finally updated the
>>> referred to tuples, and then re-insert the data into the referencing
>>> table, with the appropriate modification.
>>> Unfortunately violation checking is done after each statement, and may
>>> not be deffered.
>>> 
>>> Would this be permissible?
>>> 
>>> final Connection con = getConnection(false);
>>> 			final Statement st = con.createStatement();
>>>                       final ResultSet rs =
>>> st.executeQuery(SqlWrapper.select(objColumn, refTable, wColumn,
>>> currentSpelling));
>>> 
>>> 
>>>                       final Statement st1 = con.createStatement();
>>>                       st1.executeUpdate(SqlWrapper.delete(refTable,
>>> wColumn, currentSpelling));
>>> 			st1.executeUpdate(SqlWrapper.update(expTable, expColumn,
>>> newSpelling, currentSpelling));
>>> 
>>> 
>>>                       while (rs.next()){
>>>                           rs.absolute(SqlWrapper.index);
>>>                           rs.updateString(SqlWrapper.index, newSpelling);
>>>                           rs.updateRow();
>>>                       }
>>>                       st1.close();
>>>                       rs.close();
>>>                       st.close();
>>> 
>>> 			con.commit();
>>> 			con.close();
>>> 
>>> 2010/3/16, Sylvain Leroux <sylvain@chicoree.fr>:
>>>> Hi,
>>>> 
>>>> Just passing by:
>>>> http://issues.apache.org/jira/browse/DERBY-735
>>>> 
>>>> 
>>>> Regards,
>>>> - Sylvain
>>>> 
>>>> Gabriele Kahlout a écrit :
>>>>> ya. Also SQLite does.
>>>>> I tried to look where to submit feature requests (not bug reports),
>>>>> but failed. Any links?
>>>>> 
>>>>> 2010/3/16, Rick Genter <rgenter@silverlink.com>:
>>>>>> On 3/15/10 6:46 PM, "Ronald Rudy" <ronchalant@gmail.com> wrote:
>>>>>> 
>>>>>>> I believe you want to look at this:
>>>>>>> http://db.apache.org/derby/manuals/reference/sqlj32.html
>>>>>>> 
>>>>>>> <http://db.apache.org/derby/manuals/reference/sqlj32.html>
>>>>>>> Specifically
>>>>>>> the
>>>>>>> referential ON UPDATE in constraints.  The only "ON UPDATE" actions
>>>>>>> that
>>>>>>> are
>>>>>>> allowed are "NO ACTION" and "RESTRICT"; Derby does not appear
to
>>>>>>> support
>>>>>>> cascading updates.  You can log a feature request, I believe
there
>>>>>>> might
>>>>>>> already be one for it based on this thread:
>>>>>>> http://old.nabble.com/ON-UPDATE-CASCADE-in-derby-td1633870.html
>>>>>>> 
>>>>>>> Incidentally I do believe that MySQL supports on update cascade
with
>>>>>>> its
>>>>>>> INNODB engine, and I do think MS SQL does as well
>>>>>>> (http://msdn.microsoft.com/en-us/library/aa275462(SQL.80).aspx)
>>>>>>> 
>>>>>> Thank you for the pointer. I learn something new every day ;-).
>>>>>> --
>>>>>> Rick Genter
>>>>>> Principal Software Engineer
>>>>>> Silverlink Communications
>>>>>> rgenter@silverlink.com
>>>>>> www.silverlink.com
>>>>>> Office (781) 583-7145
>>>>>> Mobile (408) 398-7006
>>>>>> 
>>>>>> This e-mail, including attachments, may include confidential and/or
>>>>>> proprietary information, and may only be used by the person or entity
>>>>>> to
>>>>>> which it is addressed. If the reader of this e-mail is not the intended
>>>>>> recipient or his or her authorized agent, the reader is hereby notified
>>>>>> that any dissemination, distribution or copying of this e-mail is
>>>>>> prohibited. If you have received this e-mail in error, please notify
>>>>>> the
>>>>>> sender by replying to this message and delete this e-mail immediately
>>>>>> 
>>>>>> 
>>>>> 
>>>>> 
>>>> 
>>>> 
>>>> --
>>>> sylvain@chicoree.fr
>>>> http://www.chicoree.fr
>>>> 
>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> Regards,
>>> K. Gabriele
>>> 
>>> --- unchanged since 25/1/10 ---
>>> P.S. Unless a notification (LON), please reply either with an answer
>>> OR with " ACK" appended to this subject within 48 hours. Otherwise, I
>>> might resend.
>>> In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
>>> this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
>>> ¬IsAnswerTo(x,this)) ⇒ ¬IResend(this).
>>> 
>>> Also note that correspondence may be received only from specified a
>>> priori senders, or if the subject of this email ends with a code, eg.
>>> -LICHT01X, then also from senders whose reply contains it.
>>> ∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
>>> In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).
>> 
>> 
> 
> 
> -- 
> Regards,
> K. Gabriele
> 
> --- unchanged since 25/1/10 ---
> P.S. Unless a notification (LON), please reply either with an answer
> OR with " ACK" appended to this subject within 48 hours. Otherwise, I
> might resend.
> In(LON, this) ∨ In(48h, TimeNow) ∨ ∃x. In(x, MyInbox) ∧ IsAnswerTo(x,
> this) ∨ (In(subject(this), subject(x)) ∧ In(ACK, subject(x)) ∧
> ¬IsAnswerTo(x,this)) ⇒ ¬IResend(this).
> 
> Also note that correspondence may be received only from specified a
> priori senders, or if the subject of this email ends with a code, eg.
> -LICHT01X, then also from senders whose reply contains it.
> ∀x. In(x, MyInbox) ⇒ In(senderAddress(x), MySafeSenderList) ∨ (∃y.
> In(y, subject(this) ) ∧ In(y,x) ∧ isCodeLike(y, -LICHT01X) ).


Mime
View raw message