db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Watzek <mwa.t...@spree.de>
Subject Re: TCK Schema : M-N relations
Date Mon, 05 Sep 2005 13:23:57 GMT
Hi Andy,

I agree with your bug description.

I wonder why JPOX did not throw an FK constraint violation after 
makePersistentAll for "companyM-MRelationships". Actually, the test 
fails comparing test data with inserted data because the 
"reviewedProjects" collection is empty. However, in the JPOX log file 
the FK constraint violation is logged. The following line appears 5 times:

11:11:31,046 (main) ERROR [JPOX.RDBMS.SQL] - Exception thrown while 
executing SQL (INSERT INTO datastoreidentity0.PROJECT_REVIEWER 
(REVIEWER,PROJID) VALUES (?,?) ) : INSERT on table 'PROJECT_REVIEWER' 
caused a violation of foreign key constraint 'PR_PROJ_FK' for key (82). 
  The statement has been rolled back.

I would have expected that the test "companyAllRelationships" had the 
same outcome as "companyM-MRelationships" wrt this issue. Confusingly, 
the test threw an FK constraint violation for datastore identity.

So, what is the explanation for one test throwing an FK constraint 
violation but not the other?

Regards,
Michael

> Schema for M-N unit tests has an odd foreign-key defined currently (certainly 
> for datastore identity) that causes issues. We have the following tables in 
> one of the M-N's
> 
> CREATE TABLE persons (
>     DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
>     PERSONID INTEGER UNIQUE NOT NULL,
>     ...
>     CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
> )
> CREATE TABLE projects (
>     DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
>     PROJID INTEGER UNIQUE NOT NULL,
>     ...
>     CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
> );
> with join table
> CREATE TABLE project_reviewer (
>     PROJID INTEGER NOT NULL,
>     REVIEWER INTEGER NOT NULL
> );
> 
> However there's an FK defined as
> ALTER TABLE project_reviewer
>     ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
>         (PROJID) REFERENCES projects(PROJID);
> 
> so the FK goes from join table project id column *not* to the datastore 
> identity column in the "project" table but instead to a different column. That 
> won't work - the "DATASTORE_IDENTITY" values are set by the implementation, 
> whereas the PROJID is set by the TCK so likely won't concur! The definition 
> of <join> in the spec is that it goes between the *primary key columns* of 
> the primary table and the join table column(s). It should be mapped to the PK 
> of the "projects" table, so should go to "DATASTORE_IDENTITY" not "PROJID". 
> The same applies to the FK "PR_REV_FK" in the schema. 
> 
> The join table "project_member" has no FK's defined on it in the schema 
> currently - so maybe that should have 2 FK's added.
> 
> 
> 
> The schema for the TCK also doesn't currently impose PK's on 
> any of its join tables. While the TCK tests will flush out any implementation 
> behaviour that is non-compliant, this could be aided by addition of PK's. For 
> example on the tables "project_reviewer", "project_member".
> 


-- 
-------------------------------------------------------------------
Michael Watzek                  Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de        Buelowstr. 66
Tel.:  ++49/30/235 520 36       10783 Berlin - Germany
Fax.:  ++49/30/217 520 12       http://www.spree.de/
-------------------------------------------------------------------

Mime
View raw message