From derby-user-return-12450-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Mar 16 13:31:08 2010
Return-Path:
Delivered-To: apmail-db-derby-user-archive@www.apache.org
Received: (qmail 17965 invoked from network); 16 Mar 2010 13:31:08 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
by 140.211.11.9 with SMTP; 16 Mar 2010 13:31:08 -0000
Received: (qmail 80148 invoked by uid 500); 16 Mar 2010 13:31:08 -0000
Delivered-To: apmail-db-derby-user-archive@db.apache.org
Received: (qmail 80133 invoked by uid 500); 16 Mar 2010 13:31:07 -0000
Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
list-help:
list-unsubscribe:
List-Post:
List-Id:
Reply-To: "Derby Discussion"
Delivered-To: mailing list derby-user@db.apache.org
Received: (qmail 80126 invoked by uid 99); 16 Mar 2010 13:31:07 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 16 Mar 2010 13:31:07 +0000
X-ASF-Spam-Status: No, hits=2.2 required=10.0
tests=FREEMAIL_FROM,HTML_MESSAGE,RCVD_IN_DNSWL_NONE,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: domain of ronchalant@gmail.com designates 209.85.211.172 as permitted sender)
Received: from [209.85.211.172] (HELO mail-yw0-f172.google.com) (209.85.211.172)
by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 16 Mar 2010 13:31:00 +0000
Received: by ywh2 with SMTP id 2so1620299ywh.24
for ; Tue, 16 Mar 2010 06:30:38 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:received:received:from:mime-version
:content-type:subject:date:in-reply-to:to:references:message-id
:x-mailer;
bh=kMEk7m0iV/3mWD66mv+uyO4QWviy/uqblTkas6vKDmg=;
b=CY20uZSmSlEIb59Z20ESYNQQAJgxKOGiF51bBYxKzztTYDWdLeNbG8p7Dl+Gq5L3+5
0ttSj6AyysDBgZu6LQwg4X25EPw5wMnVdVEyKEoRHrdJfnEddyxNx7z+wPVIROSyx8sE
R/sXwOFJR0OQqYrlknSHGMODErT02LWSB9BQI=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=from:mime-version:content-type:subject:date:in-reply-to:to
:references:message-id:x-mailer;
b=Rmjf8aBMzJRR6m4NBU2Ac9G+GYhMpKO5ydqVsWFjUUSR45MmxrKrLF5er6fRPiyGSn
cHUIJrGsPuu2nlTWhsZpV0Ew+IKwA9SC2UhoLpkP4xEn44N9gqonIa8nmJ0+n/iGtuns
o7P0lIyzvl6xZDGSLEe04G44p219oHRCaWrmI=
Received: by 10.101.105.26 with SMTP id h26mr348anm.100.1268745980052;
Tue, 16 Mar 2010 06:26:20 -0700 (PDT)
Received: from [10.10.10.226] ([65.210.113.94])
by mx.google.com with ESMTPS id 4sm2053211ywi.51.2010.03.16.06.26.18
(version=TLSv1/SSLv3 cipher=RC4-MD5);
Tue, 16 Mar 2010 06:26:18 -0700 (PDT)
From: Ronald Rudy
Mime-Version: 1.0 (Apple Message framework v1077)
Content-Type: multipart/alternative; boundary=Apple-Mail-9-528485113
Subject: Re: how to cascade on update? Not possible?
Date: Tue, 16 Mar 2010 09:26:17 -0400
In-Reply-To: <1f5d398f1003160559m6891e1bfn53b0dca5631e9263@mail.gmail.com>
To: "Derby Discussion"
References: <1f5d398f1003152207p46d5dc12g5af672b19de5edb3@mail.gmail.com> <4B9F1FAA.9000702@chicoree.fr> <1f5d398f1003160026l33f7dbffu4264e193887b4d06@mail.gmail.com> <7072BFCF-1B88-4A8B-885D-331145851571@gmail.com> <1f5d398f1003160559m6891e1bfn53b0dca5631e9263@mail.gmail.com>
Message-Id: <68FC524D-C4DE-47B9-B620-5E02E321C630@gmail.com>
X-Mailer: Apple Mail (2.1077)
X-Virus-Checked: Checked by ClamAV on apache.org
--Apple-Mail-9-528485113
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=utf-8
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..=20
It should be done within a transaction that can be rolled back..=20
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..=20
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?
>=20
> 2010/3/16, Ronald Rudy :
>> 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.
>>=20
>> -Ron
>>=20
>>=20
>> On Mar 16, 2010, at 3:26:29 AM, Gabriele Kahlout wrote:
>>=20
>>> 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.
>>>=20
>>> Would this be permissible?
>>>=20
>>> final Connection con =3D getConnection(false);
>>> final Statement st =3D con.createStatement();
>>> final ResultSet rs =3D
>>> st.executeQuery(SqlWrapper.select(objColumn, refTable, wColumn,
>>> currentSpelling));
>>>=20
>>>=20
>>> final Statement st1 =3D con.createStatement();
>>> st1.executeUpdate(SqlWrapper.delete(refTable,
>>> wColumn, currentSpelling));
>>> st1.executeUpdate(SqlWrapper.update(expTable, =
expColumn,
>>> newSpelling, currentSpelling));
>>>=20
>>>=20
>>> while (rs.next()){
>>> rs.absolute(SqlWrapper.index);
>>> rs.updateString(SqlWrapper.index, =
newSpelling);
>>> rs.updateRow();
>>> }
>>> st1.close();
>>> rs.close();
>>> st.close();
>>>=20
>>> con.commit();
>>> con.close();
>>>=20
>>> 2010/3/16, Sylvain Leroux :
>>>> Hi,
>>>>=20
>>>> Just passing by:
>>>> http://issues.apache.org/jira/browse/DERBY-735
>>>>=20
>>>>=20
>>>> Regards,
>>>> - Sylvain
>>>>=20
>>>> Gabriele Kahlout a =C3=A9crit :
>>>>> ya. Also SQLite does.
>>>>> I tried to look where to submit feature requests (not bug =
reports),
>>>>> but failed. Any links?
>>>>>=20
>>>>> 2010/3/16, Rick Genter :
>>>>>> On 3/15/10 6:46 PM, "Ronald Rudy" wrote:
>>>>>>=20
>>>>>>> I believe you want to look at this:
>>>>>>> http://db.apache.org/derby/manuals/reference/sqlj32.html
>>>>>>>=20
>>>>>>>
>>>>>>> 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
>>>>>>>=20
>>>>>>> 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)
>>>>>>>=20
>>>>>> 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
>>>>>>=20
>>>>>> 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
>>>>>>=20
>>>>>>=20
>>>>>=20
>>>>>=20
>>>>=20
>>>>=20
>>>> --
>>>> sylvain@chicoree.fr
>>>> http://www.chicoree.fr
>>>>=20
>>>>=20
>>>>=20
>>>=20
>>>=20
>>> --
>>> Regards,
>>> K. Gabriele
>>>=20
>>> --- 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) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, =
MyInbox) =E2=88=A7 IsAnswerTo(x,
>>> this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, =
subject(x)) =E2=88=A7
>>> =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this).
>>>=20
>>> 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.
>>> =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), =
MySafeSenderList) =E2=88=A8 (=E2=88=83y.
>>> In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, =
-LICHT01X) ).
>>=20
>>=20
>=20
>=20
> --=20
> Regards,
> K. Gabriele
>=20
> --- 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) =E2=88=A8 In(48h, TimeNow) =E2=88=A8 =E2=88=83x. In(x, =
MyInbox) =E2=88=A7 IsAnswerTo(x,
> this) =E2=88=A8 (In(subject(this), subject(x)) =E2=88=A7 In(ACK, =
subject(x)) =E2=88=A7
> =C2=ACIsAnswerTo(x,this)) =E2=87=92 =C2=ACIResend(this).
>=20
> 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.
> =E2=88=80x. In(x, MyInbox) =E2=87=92 In(senderAddress(x), =
MySafeSenderList) =E2=88=A8 (=E2=88=83y.
> In(y, subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, =
-LICHT01X) ).
--Apple-Mail-9-528485113
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=utf-8
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:

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 =
DEFFERABLEconstraint/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

Also note that correspondence may be =
received only from specified apriori senders, or if the subject of =
this email ends with a code, eg.-LICHT01X, then also from senders =
whose reply contains it.=E2=88=80x. In(x, MyInbox) =E2=87=92 =
In(senderAddress(x), MySafeSenderList) =E2=88=A8 (=E2=88=83y.In(y, =
subject(this) ) =E2=88=A7 In(y,x) =E2=88=A7 isCodeLike(y, -LICHT01X) =
).