You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@isis.apache.org by Jeroen van der Wal <je...@stromboli.it> on 2014/01/07 12:17:59 UTC
Re: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
@Column defines the column into which a field is persisted. I can't
see why you want to use the same column for three fields. If you don't
want to store the address_id on OmSite you can use the
@Persistent(mappedBy="<OmSite field on OmAddress>") annotation [1].
HTH
[1] http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.html
On Tue, Jan 7, 2014 at 11:54 AM, <ch...@wipro.com> wrote:
> Consider this following scenario:
>
> public class OmSite implements java.io.Serializable {
>
> //OmSitePK.class is also created as per the specifications
> //for these two fields. No issue with that
> @Persistent(primaryKey = "true", dependent = "false")
> @Column(allowsNull = "false", name = "site_id")
> private String siteId;
>
> @Persistent(primaryKey = "true", dependent = "false")
> @Column(allowsNull = "false", name = "org_id")
> private String orgId;
>
> @Persistent(columns = {@Column(name="address_id", target = "address_id"), @Column(name="org_id", target = "org_id")})
> @Optional
> private OmAddress omAddress;
>
> @Persistent(columns = { @Column(name = "location_id"),
> @Column(name = "org_id")})
> @Optional
> private OmGeoLocation omGeoLocation;
> //Corresponding PK classes also created as per the specifications.
>
> //getters and setters
>
> }
>
>
> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
> As you can see same column org_id is being used/referred at 3 places
>
> With this mapping, when OmSite is constructed with its PK fields(orgId, siteId) and other mandatory fields. But not FK fields(omGeoLocation, omAddress), I have got not-null constraint error for one of the fields in compiste PK that is org_id.
>
> And when I construct OmSite by setting FKS (omGeoLocation, omAddress)also with org_id being same accorss PK and FK's, org_id value is being sent to Database.
>
>
> I suspect that org_id column is set when all the fields are set which use org_id column.
> This is not valid behaviour. Because OmGeoLocation and OmAddress are optional.
>
> Please help on this issue
>
> Thanks
> Ranganath
>
> BR
> Ranganath Varma
>
>
> The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
>
> www.wipro.com
RE: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
Posted by ch...@wipro.com.
Thanks Dan and Oscar. Will try your suggestions.
BR
Ranganath Varma
-----Original Message-----
From: Dan Haywood [mailto:dan@haywood-associates.co.uk]
Sent: Thursday, January 09, 2014 8:41 PM
To: users
Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same column throws not-null constraint for PK
On 8 January 2014 21:32, GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>wrote:
> Hi Varma,
>
> A different approach could be to try the org_id as the multi-tenant
> discriminator. See [1]
>
> [1]
> http://www.datanucleus.org/products/accessplatform_3_1/jdo/multitenanc
> y.html
>
>
That's a good suggestion, Oscar.... I could see it might be appropriate.
Jeroen and I did half-heartedly attempt to use this, but hit some issues.
I can't remember what they were, and we decided to go some other way in the end anyway. My suspicion was that we just hadn't configured JDO correctly, rather than it being an Isis issue though (multi-tenancy ought to be transparent to Isis, methinks).
Dan
>
> You can also disable that behavior for those classes that doesn't
> depend on the organization.
>
> I've not tested it on Isis.
>
>
> HTH,
>
> Oscar
>
>
>
>
> El 07/01/2014, a las 12:44, chittari.varma@wipro.com escribió:
>
> >
> >
> > Hi Jeroen,
> >
> > Please find below the legacy table schema definition:
> >
> > CREATE TABLE om_site
> > (
> > site_id character varying(80) NOT NULL, org_id character
> > varying(18) NOT NULL, site_name character varying(45), site_desc
> > character varying(60), site_type character varying(45) NOT NULL, --
> > Type of the site. Can be
> CAMPUS or BUILDING
> > address_id character varying(10),
> > location_id character varying(80),
> > change_by character varying(60) NOT NULL DEFAULT
> 'System/Admin'::character varying,
> > change_dt timestamp without time zone NOT NULL, CONSTRAINT
> > om_site_pk PRIMARY KEY (site_id, org_id), CONSTRAINT
> > om_site_address_id FOREIGN KEY (address_id, org_id)
> > REFERENCES om_address (address_id, org_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT
> > om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
> > REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION
> > )
> > I have to get this schema in synch with JDO entity mapping.
> >
> > [Jeroen]: @Column defines the column into which a field is
> > persisted. I
> can't see why you want to use the same column for three fields
> > [Varma]: Because org_id column at the database side is being used in
> three constraints (om_site_pk, om_site_address_id,
> om_site_factility_org_fk)
> >
> > [Jeroen]: If you don't want to store the address_id on OmSite you
> > can
> use the @Persistent(mappedBy="<OmSite field on OmAddress>")
> > [Varma]: OmSite and OmAddress are related as ManyToOne and
> bidirectional, so in OmAddress I have Set<OmSite> sites with
> annotation @Persistent (mappedby="omAddress"). And I want to store
> address_id in om_site table only as a foreign key. Same case is with
> the other FK field
> >
> > Note: The DDL is legacy one, so it could not be changed.
> >
> > This is a valid scenario at RDBMS side
> >
> > This kind of mapping works with JPA where in same column can be used
> > as
> for the fields above. I tested its working fine(JPA/Hibernate out of
> ISIS
> environment)
> >
> >
> > And also I tried to resolve this issue by adding a column unique
> rowsequence_id(not PK) in om_address table, and at om_site foreign key
> is changed to refer to that unique rowsequence_id which is not PK. But
> JDO did not allow this as FK should refer to PK field only.
> >
> > This is really annoying as this is also valid at RDBMS side but not
> > at
> JPA/JDO side.
> >
> >
> >
> >
> >
> >
> > BR
> > Ranganath Varma
> >
> >
> > BR
> > Ranganath Varma
> >
> >
> > -----Original Message-----
> > From: Jeroen van der Wal [mailto:jeroen@stromboli.it]
> > Sent: Tuesday, January 07, 2014 4:48 PM
> > To: users
> > Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same
> column throws not-null constraint for PK
> >
> > @Column defines the column into which a field is persisted. I can't
> > see
> why you want to use the same column for three fields. If you don't
> want to store the address_id on OmSite you can use the
> @Persistent(mappedBy="<OmSite field on OmAddress>") annotation [1].
> >
> > HTH
> >
> > [1]
> http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.
> html
> >
> > On Tue, Jan 7, 2014 at 11:54 AM, <ch...@wipro.com> wrote:
> >> Consider this following scenario:
> >>
> >> public class OmSite implements java.io.Serializable {
> >>
> >> //OmSitePK.class is also created as per the specifications //for
> >> these two fields. No issue with that @Persistent(primaryKey =
> >> "true", dependent = "false") @Column(allowsNull = "false", name =
> >> "site_id") private String siteId;
> >>
> >> @Persistent(primaryKey = "true", dependent = "false")
> >> @Column(allowsNull = "false", name = "org_id") private String
> >> orgId;
> >>
> >> @Persistent(columns = {@Column(name="address_id", target =
> >> "address_id"), @Column(name="org_id", target = "org_id")})
> >> @Optional private OmAddress omAddress;
> >>
> >> @Persistent(columns = { @Column(name = "location_id"), @Column(name
> >> =
> >> "org_id")}) @Optional private OmGeoLocation omGeoLocation;
> >> //Corresponding PK classes also created as per the specifications.
> >>
> >> //getters and setters
> >>
> >> }
> >>
> >>
> >> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
> >> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
> >> As you can see same column org_id is being used/referred at 3
> >> places
> >>
> >> With this mapping, when OmSite is constructed with its PK
> >> fields(orgId,
> siteId) and other mandatory fields. But not FK fields(omGeoLocation,
> omAddress), I have got not-null constraint error for one of the fields
> in compiste PK that is org_id.
> >>
> >> And when I construct OmSite by setting FKS (omGeoLocation,
> omAddress)also with org_id being same accorss PK and FK's, org_id
> value is being sent to Database.
> >>
> >>
> >> I suspect that org_id column is set when all the fields are set
> >> which
> use org_id column.
> >> This is not valid behaviour. Because OmGeoLocation and OmAddress
> >> are
> optional.
> >>
> >> Please help on this issue
> >>
> >> Thanks
> >> Ranganath
> >>
> >> BR
> >> Ranganath Varma
> >>
> >>
> >> The information contained in this electronic message and any
> attachments to this message are intended for the exclusive use of the
> addressee(s) and may contain proprietary, confidential or privileged
> information. If you are not the intended recipient, you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and destroy all copies of this message and any attachments.
> >>
> >> WARNING: Computer viruses can be transmitted via email. The
> >> recipient
> should check this email and any attachments for the presence of viruses.
> The company accepts no liability for any damage caused by any virus
> transmitted by this email.
> >>
> >> www.wipro.com
> >
> > The information contained in this electronic message and any
> > attachments
> to this message are intended for the exclusive use of the addressee(s)
> and may contain proprietary, confidential or privileged information.
> If you are not the intended recipient, you should not disseminate,
> distribute or copy this e-mail. Please notify the sender immediately
> and destroy all copies of this message and any attachments.
> >
> > WARNING: Computer viruses can be transmitted via email. The
> > recipient
> should check this email and any attachments for the presence of viruses.
> The company accepts no liability for any damage caused by any virus
> transmitted by this email.
> >
> > www.wipro.com
>
>
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com
Re: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
Posted by Dan Haywood <da...@haywood-associates.co.uk>.
On 8 January 2014 21:32, GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>wrote:
> Hi Varma,
>
> A different approach could be to try the org_id as the multi-tenant
> discriminator. See [1]
>
> [1]
> http://www.datanucleus.org/products/accessplatform_3_1/jdo/multitenancy.html
>
>
That's a good suggestion, Oscar.... I could see it might be appropriate.
Jeroen and I did half-heartedly attempt to use this, but hit some issues.
I can't remember what they were, and we decided to go some other way in
the end anyway. My suspicion was that we just hadn't configured JDO
correctly, rather than it being an Isis issue though (multi-tenancy ought
to be transparent to Isis, methinks).
Dan
>
> You can also disable that behavior for those classes that doesn't depend
> on the organization.
>
> I've not tested it on Isis.
>
>
> HTH,
>
> Oscar
>
>
>
>
> El 07/01/2014, a las 12:44, chittari.varma@wipro.com escribió:
>
> >
> >
> > Hi Jeroen,
> >
> > Please find below the legacy table schema definition:
> >
> > CREATE TABLE om_site
> > (
> > site_id character varying(80) NOT NULL,
> > org_id character varying(18) NOT NULL,
> > site_name character varying(45),
> > site_desc character varying(60),
> > site_type character varying(45) NOT NULL, -- Type of the site. Can be
> CAMPUS or BUILDING
> > address_id character varying(10),
> > location_id character varying(80),
> > change_by character varying(60) NOT NULL DEFAULT
> 'System/Admin'::character varying,
> > change_dt timestamp without time zone NOT NULL,
> > CONSTRAINT om_site_pk PRIMARY KEY (site_id, org_id),
> > CONSTRAINT om_site_address_id FOREIGN KEY (address_id, org_id)
> > REFERENCES om_address (address_id, org_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION,
> > CONSTRAINT om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
> > REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
> > ON UPDATE NO ACTION ON DELETE NO ACTION
> > )
> > I have to get this schema in synch with JDO entity mapping.
> >
> > [Jeroen]: @Column defines the column into which a field is persisted. I
> can't see why you want to use the same column for three fields
> > [Varma]: Because org_id column at the database side is being used in
> three constraints (om_site_pk, om_site_address_id, om_site_factility_org_fk)
> >
> > [Jeroen]: If you don't want to store the address_id on OmSite you can
> use the @Persistent(mappedBy="<OmSite field on OmAddress>")
> > [Varma]: OmSite and OmAddress are related as ManyToOne and
> bidirectional, so in OmAddress I have Set<OmSite> sites with annotation
> @Persistent (mappedby="omAddress"). And I want to store address_id in
> om_site table only as a foreign key. Same case is with the other FK field
> >
> > Note: The DDL is legacy one, so it could not be changed.
> >
> > This is a valid scenario at RDBMS side
> >
> > This kind of mapping works with JPA where in same column can be used as
> for the fields above. I tested its working fine(JPA/Hibernate out of ISIS
> environment)
> >
> >
> > And also I tried to resolve this issue by adding a column unique
> rowsequence_id(not PK) in om_address table, and at om_site foreign key is
> changed to refer to that unique rowsequence_id which is not PK. But JDO did
> not allow this as FK should refer to PK field only.
> >
> > This is really annoying as this is also valid at RDBMS side but not at
> JPA/JDO side.
> >
> >
> >
> >
> >
> >
> > BR
> > Ranganath Varma
> >
> >
> > BR
> > Ranganath Varma
> >
> >
> > -----Original Message-----
> > From: Jeroen van der Wal [mailto:jeroen@stromboli.it]
> > Sent: Tuesday, January 07, 2014 4:48 PM
> > To: users
> > Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same
> column throws not-null constraint for PK
> >
> > @Column defines the column into which a field is persisted. I can't see
> why you want to use the same column for three fields. If you don't want to
> store the address_id on OmSite you can use the
> @Persistent(mappedBy="<OmSite field on OmAddress>") annotation [1].
> >
> > HTH
> >
> > [1]
> http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.html
> >
> > On Tue, Jan 7, 2014 at 11:54 AM, <ch...@wipro.com> wrote:
> >> Consider this following scenario:
> >>
> >> public class OmSite implements java.io.Serializable {
> >>
> >> //OmSitePK.class is also created as per the specifications //for these
> >> two fields. No issue with that @Persistent(primaryKey = "true",
> >> dependent = "false") @Column(allowsNull = "false", name = "site_id")
> >> private String siteId;
> >>
> >> @Persistent(primaryKey = "true", dependent = "false")
> >> @Column(allowsNull = "false", name = "org_id") private String orgId;
> >>
> >> @Persistent(columns = {@Column(name="address_id", target =
> >> "address_id"), @Column(name="org_id", target = "org_id")}) @Optional
> >> private OmAddress omAddress;
> >>
> >> @Persistent(columns = { @Column(name = "location_id"), @Column(name =
> >> "org_id")}) @Optional private OmGeoLocation omGeoLocation;
> >> //Corresponding PK classes also created as per the specifications.
> >>
> >> //getters and setters
> >>
> >> }
> >>
> >>
> >> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
> >> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
> >> As you can see same column org_id is being used/referred at 3 places
> >>
> >> With this mapping, when OmSite is constructed with its PK fields(orgId,
> siteId) and other mandatory fields. But not FK fields(omGeoLocation,
> omAddress), I have got not-null constraint error for one of the fields in
> compiste PK that is org_id.
> >>
> >> And when I construct OmSite by setting FKS (omGeoLocation,
> omAddress)also with org_id being same accorss PK and FK's, org_id value is
> being sent to Database.
> >>
> >>
> >> I suspect that org_id column is set when all the fields are set which
> use org_id column.
> >> This is not valid behaviour. Because OmGeoLocation and OmAddress are
> optional.
> >>
> >> Please help on this issue
> >>
> >> Thanks
> >> Ranganath
> >>
> >> BR
> >> Ranganath Varma
> >>
> >>
> >> The information contained in this electronic message and any
> attachments to this message are intended for the exclusive use of the
> addressee(s) and may contain proprietary, confidential or privileged
> information. If you are not the intended recipient, you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately and destroy all copies of this message and any attachments.
> >>
> >> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses.
> The company accepts no liability for any damage caused by any virus
> transmitted by this email.
> >>
> >> www.wipro.com
> >
> > The information contained in this electronic message and any attachments
> to this message are intended for the exclusive use of the addressee(s) and
> may contain proprietary, confidential or privileged information. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately and destroy all copies of
> this message and any attachments.
> >
> > WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses.
> The company accepts no liability for any damage caused by any virus
> transmitted by this email.
> >
> > www.wipro.com
>
>
Re: ISIS and JDO: Multiple fields(FK's and PK) using same column throws not-null constraint for PK
Posted by GESCONSULTOR - Óscar Bou <o....@gesconsultor.com>.
Hi Varma,
A different approach could be to try the org_id as the multi-tenant discriminator. See [1]
[1] http://www.datanucleus.org/products/accessplatform_3_1/jdo/multitenancy.html
You can also disable that behavior for those classes that doesn't depend on the organization.
I've not tested it on Isis.
HTH,
Oscar
El 07/01/2014, a las 12:44, chittari.varma@wipro.com escribió:
>
>
> Hi Jeroen,
>
> Please find below the legacy table schema definition:
>
> CREATE TABLE om_site
> (
> site_id character varying(80) NOT NULL,
> org_id character varying(18) NOT NULL,
> site_name character varying(45),
> site_desc character varying(60),
> site_type character varying(45) NOT NULL, -- Type of the site. Can be CAMPUS or BUILDING
> address_id character varying(10),
> location_id character varying(80),
> change_by character varying(60) NOT NULL DEFAULT 'System/Admin'::character varying,
> change_dt timestamp without time zone NOT NULL,
> CONSTRAINT om_site_pk PRIMARY KEY (site_id, org_id),
> CONSTRAINT om_site_address_id FOREIGN KEY (address_id, org_id)
> REFERENCES om_address (address_id, org_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
> REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> I have to get this schema in synch with JDO entity mapping.
>
> [Jeroen]: @Column defines the column into which a field is persisted. I can't see why you want to use the same column for three fields
> [Varma]: Because org_id column at the database side is being used in three constraints (om_site_pk, om_site_address_id, om_site_factility_org_fk)
>
> [Jeroen]: If you don't want to store the address_id on OmSite you can use the @Persistent(mappedBy="<OmSite field on OmAddress>")
> [Varma]: OmSite and OmAddress are related as ManyToOne and bidirectional, so in OmAddress I have Set<OmSite> sites with annotation @Persistent (mappedby="omAddress"). And I want to store address_id in om_site table only as a foreign key. Same case is with the other FK field
>
> Note: The DDL is legacy one, so it could not be changed.
>
> This is a valid scenario at RDBMS side
>
> This kind of mapping works with JPA where in same column can be used as for the fields above. I tested its working fine(JPA/Hibernate out of ISIS environment)
>
>
> And also I tried to resolve this issue by adding a column unique rowsequence_id(not PK) in om_address table, and at om_site foreign key is changed to refer to that unique rowsequence_id which is not PK. But JDO did not allow this as FK should refer to PK field only.
>
> This is really annoying as this is also valid at RDBMS side but not at JPA/JDO side.
>
>
>
>
>
>
> BR
> Ranganath Varma
>
>
> BR
> Ranganath Varma
>
>
> -----Original Message-----
> From: Jeroen van der Wal [mailto:jeroen@stromboli.it]
> Sent: Tuesday, January 07, 2014 4:48 PM
> To: users
> Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same column throws not-null constraint for PK
>
> @Column defines the column into which a field is persisted. I can't see why you want to use the same column for three fields. If you don't want to store the address_id on OmSite you can use the @Persistent(mappedBy="<OmSite field on OmAddress>") annotation [1].
>
> HTH
>
> [1] http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.html
>
> On Tue, Jan 7, 2014 at 11:54 AM, <ch...@wipro.com> wrote:
>> Consider this following scenario:
>>
>> public class OmSite implements java.io.Serializable {
>>
>> //OmSitePK.class is also created as per the specifications //for these
>> two fields. No issue with that @Persistent(primaryKey = "true",
>> dependent = "false") @Column(allowsNull = "false", name = "site_id")
>> private String siteId;
>>
>> @Persistent(primaryKey = "true", dependent = "false")
>> @Column(allowsNull = "false", name = "org_id") private String orgId;
>>
>> @Persistent(columns = {@Column(name="address_id", target =
>> "address_id"), @Column(name="org_id", target = "org_id")}) @Optional
>> private OmAddress omAddress;
>>
>> @Persistent(columns = { @Column(name = "location_id"), @Column(name =
>> "org_id")}) @Optional private OmGeoLocation omGeoLocation;
>> //Corresponding PK classes also created as per the specifications.
>>
>> //getters and setters
>>
>> }
>>
>>
>> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
>> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
>> As you can see same column org_id is being used/referred at 3 places
>>
>> With this mapping, when OmSite is constructed with its PK fields(orgId, siteId) and other mandatory fields. But not FK fields(omGeoLocation, omAddress), I have got not-null constraint error for one of the fields in compiste PK that is org_id.
>>
>> And when I construct OmSite by setting FKS (omGeoLocation, omAddress)also with org_id being same accorss PK and FK's, org_id value is being sent to Database.
>>
>>
>> I suspect that org_id column is set when all the fields are set which use org_id column.
>> This is not valid behaviour. Because OmGeoLocation and OmAddress are optional.
>>
>> Please help on this issue
>>
>> Thanks
>> Ranganath
>>
>> BR
>> Ranganath Varma
>>
>>
>> The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
>>
>> WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
>>
>> www.wipro.com
>
> The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
>
> www.wipro.com
Re: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
Posted by Dan Haywood <da...@haywood-associates.co.uk>.
On 7 January 2014 11:44, <ch...@wipro.com> wrote:
>
> Please find below the legacy table schema definition:
>
> CREATE TABLE om_site
> (
> site_id character varying(80) NOT NULL,
> org_id character varying(18) NOT NULL,
> site_name character varying(45),
> site_desc character varying(60),
> site_type character varying(45) NOT NULL, -- Type of the site. Can be
> CAMPUS or BUILDING
> address_id character varying(10),
> location_id character varying(80),
> change_by character varying(60) NOT NULL DEFAULT
> 'System/Admin'::character varying,
> change_dt timestamp without time zone NOT NULL,
> CONSTRAINT om_site_pk PRIMARY KEY (site_id, org_id),
> CONSTRAINT om_site_address_id FOREIGN KEY (address_id, org_id)
> REFERENCES om_address (address_id, org_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
> REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> I have to get this schema in synch with JDO entity mapping.
>
>
> Note: The DDL is legacy one, so it could not be changed.
>
>
Just a side note on this... one option may be to create views over the top
of your legacy schema, and then map Isis/JDO to that instead. For example,
if the issue turns out that JDO really won't let you map org_id three times
(as a component of the PK and also as a component of the two FKs), then a
view on top of this could "export" org_id for the other maps
eg:
create view isis_om_site as
select *, org_id as addr_org_id, org_id as loc_org_id
from om_site
then map the Address FK to addr_org_id "column", and Location FK similarly
to loc_org_id
You can then use INSTEAD OF triggers to go the other way (assuming you are
using a half-decent database here).
In fact, I suggest that this is good practice for all tables whenever
integrating into any legacy database. (It might even be good practice for
every app, legacy schema or not. Having a view layer provides a useful
abstraction layer for systems where there is more than one client of the
database.
> This is a valid scenario at RDBMS side
>
> This kind of mapping works with JPA where in same column can be used as
> for the fields above. I tested its working fine(JPA/Hibernate out of ISIS
> environment)
>
>
>
Have you also tested it with JDO (outside of Isis environment)? It'd be
good to know if its JDO that can't handle this, or if it is Isis.
Dan
RE: ISIS and JDO: Multiple fields(FK's and PK) using same column
throws not-null constraint for PK
Posted by ch...@wipro.com.
Hi Jeroen,
Please find below the legacy table schema definition:
CREATE TABLE om_site
(
site_id character varying(80) NOT NULL,
org_id character varying(18) NOT NULL,
site_name character varying(45),
site_desc character varying(60),
site_type character varying(45) NOT NULL, -- Type of the site. Can be CAMPUS or BUILDING
address_id character varying(10),
location_id character varying(80),
change_by character varying(60) NOT NULL DEFAULT 'System/Admin'::character varying,
change_dt timestamp without time zone NOT NULL,
CONSTRAINT om_site_pk PRIMARY KEY (site_id, org_id),
CONSTRAINT om_site_address_id FOREIGN KEY (address_id, org_id)
REFERENCES om_address (address_id, org_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT om_site_factility_org_fk FOREIGN KEY (location_id, org_id)
REFERENCES om_geo_location (location_id, org_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
I have to get this schema in synch with JDO entity mapping.
[Jeroen]: @Column defines the column into which a field is persisted. I can't see why you want to use the same column for three fields
[Varma]: Because org_id column at the database side is being used in three constraints (om_site_pk, om_site_address_id, om_site_factility_org_fk)
[Jeroen]: If you don't want to store the address_id on OmSite you can use the @Persistent(mappedBy="<OmSite field on OmAddress>")
[Varma]: OmSite and OmAddress are related as ManyToOne and bidirectional, so in OmAddress I have Set<OmSite> sites with annotation @Persistent (mappedby="omAddress"). And I want to store address_id in om_site table only as a foreign key. Same case is with the other FK field
Note: The DDL is legacy one, so it could not be changed.
This is a valid scenario at RDBMS side
This kind of mapping works with JPA where in same column can be used as for the fields above. I tested its working fine(JPA/Hibernate out of ISIS environment)
And also I tried to resolve this issue by adding a column unique rowsequence_id(not PK) in om_address table, and at om_site foreign key is changed to refer to that unique rowsequence_id which is not PK. But JDO did not allow this as FK should refer to PK field only.
This is really annoying as this is also valid at RDBMS side but not at JPA/JDO side.
BR
Ranganath Varma
BR
Ranganath Varma
-----Original Message-----
From: Jeroen van der Wal [mailto:jeroen@stromboli.it]
Sent: Tuesday, January 07, 2014 4:48 PM
To: users
Subject: Re: ISIS and JDO: Multiple fields(FK's and PK) using same column throws not-null constraint for PK
@Column defines the column into which a field is persisted. I can't see why you want to use the same column for three fields. If you don't want to store the address_id on OmSite you can use the @Persistent(mappedBy="<OmSite field on OmAddress>") annotation [1].
HTH
[1] http://www.datanucleus.org/products/accessplatform/jdo/orm/one_to_one.html
On Tue, Jan 7, 2014 at 11:54 AM, <ch...@wipro.com> wrote:
> Consider this following scenario:
>
> public class OmSite implements java.io.Serializable {
>
> //OmSitePK.class is also created as per the specifications //for these
> two fields. No issue with that @Persistent(primaryKey = "true",
> dependent = "false") @Column(allowsNull = "false", name = "site_id")
> private String siteId;
>
> @Persistent(primaryKey = "true", dependent = "false")
> @Column(allowsNull = "false", name = "org_id") private String orgId;
>
> @Persistent(columns = {@Column(name="address_id", target =
> "address_id"), @Column(name="org_id", target = "org_id")}) @Optional
> private OmAddress omAddress;
>
> @Persistent(columns = { @Column(name = "location_id"), @Column(name =
> "org_id")}) @Optional private OmGeoLocation omGeoLocation;
> //Corresponding PK classes also created as per the specifications.
>
> //getters and setters
>
> }
>
>
> Here I have 1 composite PK(site_id, org_id), 2 composite FK's
> OmGeoLocation(location_id,org_id), OmAddress(address_id, org_id).
> As you can see same column org_id is being used/referred at 3 places
>
> With this mapping, when OmSite is constructed with its PK fields(orgId, siteId) and other mandatory fields. But not FK fields(omGeoLocation, omAddress), I have got not-null constraint error for one of the fields in compiste PK that is org_id.
>
> And when I construct OmSite by setting FKS (omGeoLocation, omAddress)also with org_id being same accorss PK and FK's, org_id value is being sent to Database.
>
>
> I suspect that org_id column is set when all the fields are set which use org_id column.
> This is not valid behaviour. Because OmGeoLocation and OmAddress are optional.
>
> Please help on this issue
>
> Thanks
> Ranganath
>
> BR
> Ranganath Varma
>
>
> The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
>
> www.wipro.com
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.
WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.
www.wipro.com