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:[EMAIL PROTECTED] Sent: Wednesday, February 20, 2008 4:24 PM To: [email protected] 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 <[EMAIL PROTECTED]> 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:[EMAIL PROTECTED] > > > Sent: Wednesday, February 20, 2008 2:59 PM > To: [email protected] > 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:[EMAIL PROTECTED] > Sent: > Wednesday, February 20, 2008 1:57 PM > To: [email protected] > > 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:[EMAIL PROTECTED] >> Sent: > Wednesday, February 20, 2008 2:01 AM >> To: [email protected] > >> 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 > >> > > > > > >
