Re: Specifying PK when creating a new object in DB generated PK mode

From: Mike Kienenberger (mkienen..mail.com)
Date: Wed Feb 20 2008 - 16:23:50 EST

  • Next message: Scott Anderson: "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 - 16:24:30 EST