RE: Foreign Key constraints on Sybase?

[Michael Vorburger <mv...@odyssey-group.com>]

Continuing on with my monologue on this thread...

The attached patch (does it make it through the list?) removes the getAddForeignKeySQL() with the return new String[0]; from the SybaseDictionary.

I did a brief test, and with this, and <property name="openjpa.jdbc.MappingDefaults" value="ForeignKeyDeleteAction=restrict, JoinForeignKeyDeleteAction=restrict" /> (no cascasde) I get the ALTER TABLE on Sybase, and as explained below, that's perfectly valid.  --  Using http://openjpa.apache.org/unit-tests.html I could check if this patch has no other side effects when running on Sybase if that would help in getting it commited by one of you?  (Unfortunately I currently get "Tests run: 839, Failures: 0, Errors: 5, Skipped: 0" from trunk, so can't really test...)

Can I become commiter if I continue on like this? ;-)

Regards,
Michael


-----Original Message-----
From: Michael Vorburger [mailto:mvorburger@odyssey-group.com] 
Sent: vendredi, 29. février 2008 10:11
To: users@openjpa.apache.org
Subject: RE: Foreign Key constraints on Sybase?

self.reply ;-) with what I learnt from our DB expert about this since writing for future Googlers:

Sybase indeed SUPPORTS foreign key constraints ("ALTER TABLE A_B ADD FOREIGN KEY (A_id) REFERENCES A (id);").  The SybaseDictionary MUST emit these (ignore cascades), IMHO.  I'd argue this is an OpenJPA bug?

Sybase does unfortunately NOT seem to support "...  ON DELETE CASCADE".  A (simple!) TSQL stored proc is typically written for this functionality.  SybaseDictionary COULD probably generate such stored procs for ideal Sybase support.

Comments, anybody?

 

-----Original Message-----
From: Michael Vorburger [mailto:mvorburger@odyssey-group.com]
Sent: mardi, 26. février 2008 19:57
To: users@openjpa.apache.org
Subject: Foreign Key constraints on Sybase?

Hello,
 
I am trying to get Foreign Key constraints generated by the mappingtool on Sybase.
 
After some doc reading and playing around I found mappingtool ...
-foreignKeys true, and tried e.g. 

<property name="openjpa.jdbc.MappingDefaults"
value="ForeignKeyDeleteAction=cascade,
JoinForeignKeyDeleteAction=restrict" /> <!-- Or just restrict? -->

and tried the @ElementForeignKey annotation too, but no mater what, I was constantly awarded with sad messages like this one only:
 
1877  Test-JPA WARN   [main] openjpa.jdbc.Schema - The foreign key
"<foreignkey>" was not added to table "A".

I played around with this some more (too long) until it occured to me to try switching to Derby just for a try - and I got the "ALTER TABLE A_B ADD FOREIGN KEY (A_id) REFERENCES A (id);" kind of DDL!
 
So this wasn't my missconfiguration as I had assumed for hours, but a OpenJPA@Sybase "problem"?? Then I dug into the SybaseDictionary and found this:
public String[] getAddForeignKeySQL(ForeignKey fk) {
    // Sybase has problems with adding foriegn keys via ALTER TABLE command
    return new String[0];
}

why are you doing this?? As far as I can tell, an ALTER TABLE like the one shown above seems to be pass just fine on Sybase... so what's the deal with Foreign Key constraints on Sybase in OpenJPA? There is nothing documented on http://openjpa.apache.org/docs/latest/manual/dbsupport_sybase.html#dbsup
port_sybase_issues about this.
 
Thank you very much,
Michael
 
PS: I did notice Sybase doesn't seem to like "...  ON DELETE CASCADE", I will ask somebody here who speaks Sybase SQL more fluently than I do about this. Surely there is an alternative syntax?
 
 
_____________________________
Michael Vorburger, Odyssey Financial Technologies Direct phone:  +41 21 310 00 86 (OAMS VOIP: 1086) Cell phone: +41 78 805 5541
Mailto: mvorburger@odyssey-group.com
 

____________________________________________________________

* This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
* Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
* Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial Technologies SA unless otherwise specifically stated.
* An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
* If you have received this email in error, please notify the sender immediately
  and delete the original.

____________________________________________________________

* This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
* Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
* Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial Technologies SA unless otherwise specifically stated.
* An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
* If you have received this email in error, please notify the sender immediately
  and delete the original.

____________________________________________________________

• This email and any files transmitted with it are CONFIDENTIAL and intended
  solely for the use of the individual or entity to which they are addressed.
• Any unauthorized copying, disclosure, or distribution of the material within
  this email is strictly forbidden.
• Any views or opinions presented within this e-mail are solely those of the
  author and do not necessarily represent those of Odyssey Financial
Technologies SA unless otherwise specifically stated.
• An electronic message is not binding on its sender. Any message referring to
  a binding engagement must be confirmed in writing and duly signed.
• If you have received this email in error, please notify the sender immediately
  and delete the original.