My model is configured properly with ToDep; I believe the problem is
that Cayenne assumes the wrong PK from the REQUESTS table, which I
suspect is just a different symptom of the same DB-generated PK bug I
opened CAY-987 to address.
Creating a relationship between REQUESTS and REQUESTS_OLD is an
interesting idea, but I do not think it is an appropriate solution.
Among the reasons, the most obvious is that REQUESTS is the master
table, but it is being populated with data from REQUESTS_OLD. Creating a
working relationship would require declaring REQUESTS_OLD as the master
table, which goes entirely against the spirit of the system's redesign.
I may try it anyways, as I am curious about whether the relationship
between REQUESTS and REQUESTS_OLD would break for the same reason the
REQUESTS/REQUESTS_LAB relationship is broken.
-----Original Message-----
From: Mike Kienenberger [mailto:mkienen..mail.com]
Sent: Wednesday, February 20, 2008 4:24 PM
To: use..ayenne.apache.org
Subject: Re: Specifying PK when creating a new object in DB generated PK
mode
I wasn't following closely enough to answer your previous questions, but
I can help you with the RequestsLab issue.
Set up a db relationship from REQUESTS to REQUESTS_LAB using the primary
key of both. In REQUESTS.to_request_lab, mark the
relationship as "ToDep" (which means to dependent primary key). The
primary key value generated (or set) for REQUESTS will be automatically
set for REQUESTS_LAB.
And if I understand right, you could probably use the same kind of
relationship to make the pk of REQUESTS be dependent on the REQUESTS_OLD
pk, avoiding all of this code.
On Wed, Feb 20, 2008 at 3:27 PM, Scott Anderson <sanderso..irvana.com>
wrote:
> Here's my exact scenario:
>
> requests are 'tickets' entered by users on a webpage; they can be to
> one of three groups. If they are to the lab group, there's a parallel
> table called requests_lab that shares a PK with requests.
>
> Another problem that could potentially be related is that I have to
> set the PK of my RequestsLab object manually, because it is not
> properly derrived from the relationship between the two objects, but
> that is quite minor.
>
> Here's my work-flow for importing a request:
>
> SELECT * FROM requests_old;
> foreach(requests_old) {
> SELECT * FROM requests WHERE idx={requests_old.idx};
> if(row exists)
> continue;
>
> Requests req = context.newObject(Requests.class);
> AutomationContext.setIdx(req, requests_old.getIdx());
>
> context.commit();
> INSERT INTO requests (...) VALUES (...);
>
> if(requests_old.type = LAB) {
> RequestsLab req_lab =
> context.newObject(RequestsLab.class);
>
> // I have to set both the PK and the Cayenne
> relationship for this to go properly
> AutomationContext.setIdx(req_lab,
> AutomationContext.getIdx(req));
> req_lab.setRequest(req);
>
> context.commit();
> INSERT INTO requests_lab (...) VALUES (...);
> }
> }
>
> The output, when requests is in DB-generated PK mode:
>
> - --- will run 2 queries.
> - --- transaction started.
> - INSERT INTO requests (assignedto, close_time, completion_comments,
> completion_time, groupId, priority, reject_reason, req_type, request,
> state, submission_comments, submit_time, submitter) VALUES (?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> - [bind: 1->assignedto:291, 2->close_time:NULL,
> 3->completion_comments:'', 4->completion_time:NULL, 5->groupId:11,
> 6->priority:02, 7->reject_reason:NULL, 8->req_type:71, 9->request:'New
> Equipment Entry', 10->state:'UNASSIGNED',
> 11->submission_comments:'ignore me', 12->submit_time:'2006-01-27
> 14:24:30.0', 13->submitter:1]
> - Generated PK: requests.idx = 1021
> - === updated 1 row.
> - INSERT INTO requests_lab (budget, date_needed, device_id, idx,
> rack_no, serial_no) VALUES (?, ?, ?, ?, ?, ?)
> - [bind: 1->budget:NULL, 2->date_needed:NULL, 3->device_id:NULL,
> 4->idx:118, 5->rack_no:NULL, 6->serial_no:NULL]
> - *** error.
> com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException:
> Cannot add or update a child row: a foreign key constraint fails
> (`automation_test/requests_lab`, CONSTRAINT `requests_lab_ibfk_1`
> FOREIGN KEY (`idx`) REFERENCES `requests` (`idx`) ON DELETE CASCADE ON
> UPDATE CASCADE)
>
> If I change the PK generation mode for the requests table to default,
> the importer works as expected.
>
>
> -----Original Message-----
> From: Andrus Adamchik [mailto:andru..bjectstyle.org]
>
>
> Sent: Wednesday, February 20, 2008 2:59 PM
> To: use..ayenne.apache.org
> Subject: Re: Specifying PK when creating a new object in DB generated
> PK mode
>
> Hmm... then I suspect a bug in handling that in DB-generated PK mode.
> We may need to reopen CAY-987, now that the scenario is clarified.
>
> Andrus
>
>
> On Feb 20, 2008, at 9:53 PM, Scott Anderson wrote:
>
> > I am using this function, and the latest changes from SVN:
> >
> > public static final void setIdx(CayenneDataObject obj, int
pk) {
> > obj.getObjectId().getReplacementIdMap().put("idx",
new
> Integer(pk));
> > }
> >
> > Cayenne completely omits the PK field from its INSERT statement.
> >
> > I'm going to set up a new test environment and see if I can
> reproduce > the expected behavior to determine if it's my environment
or Cayenne.
> >
> > Scott
> >
> > -----Original Message-----
> > From: Andrus Adamchik [mailto:andru..bjectstyle.org] > Sent:
> Wednesday, February 20, 2008 1:57 PM > To: use..ayenne.apache.org >
> Subject: Re: Specifying PK when creating a new object in DB generated
> > PK mode > >> Cayenne should not disregard user-made decisions: it
> isn't Cayenne's >> responsibility to enforce PK integrity.
> >
> >
> > It will not disregard the user decisions. If I understood correctly
> > what you are trying to do, my recommendation would be this:
> >
> > MyObject object = context.newObject(MyObject.class);
> > object.getObjectId().getReplacementIdMap().put("PK_COLUMN",
value);
> >
> > When you said "PK is set through the ObjectID map", did you mean
> the > API above or something else? AFAIK the approach above should
work.
> >
> > Andrus
> >
> >
> >
> > On Feb 20, 2008, at 8:31 PM, Scott Anderson wrote:
> >> It looks like there's no model that fits what I'd like to do. IMO,
> >> the > >> user should be able to specify the PK in DB-generated
> mode when >> either > >> the field is exposed, or the PK is set
> through the ObjectID map.
> >> Cayenne
> >> should not disregard user-made decisions: it isn't Cayenne's >>
> responsibility to enforce PK integrity.
> >>
> >> -----Original Message-----
> >> From: Aristedes Maniatis [mailto:ar..sh.com.au] >> Sent:
> Wednesday, February 20, 2008 2:01 AM >> To: use..ayenne.apache.org
> >> Subject: Re: Specifying PK when creating a new object in DB
> generated
>
> >> PK mode
> >>
> >>
> >> On 20/02/2008, at 9:51 AM, Scott Anderson wrote:
> >>
> >>> I am working an a request ticket system, similar to the one I'm
> >>> using > >>> to enter this issue. I need to import requests from
> the old schema >>> to > >>> the new schema, and the PKs must be
> specified while doing an import.
> >>> When using the tool normally, the PKs must be database generated.
> Is
>
> >>> there any way to do this?
> >>
> >> You can specify the key yourself easily:
> >> http://cayenne.apache.org/doc/primary-key-generation.html
> >>
> >> But I can't see how you can guarantee you will not get collisions
> >> between your two schemes. Maybe better to keep the old key in a >>
> separate field for reference purposes.
> >>
> >> Ari Maniatis
> >>
> >
> >
>
>
This archive was generated by hypermail 2.0.0 : Wed Feb 20 2008 - 17:38:01 EST