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 - 15:27:44 EST