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 [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
