cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Kienenberger" <mkien...@gmail.com>
Subject Re: Specifying PK when creating a new object in DB generated PK mode
Date Wed, 20 Feb 2008 21:23:50 GMT
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 <sanderson@airvana.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:andrus@objectstyle.org]
>
>
> Sent: Wednesday, February 20, 2008 2:59 PM
>  To: user@cayenne.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:andrus@objectstyle.org]
>  > Sent: Wednesday, February 20, 2008 1:57 PM
>  > To: user@cayenne.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:ari@ish.com.au]
>  >> Sent: Wednesday, February 20, 2008 2:01 AM
>  >> To: user@cayenne.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
>  >>
>  >
>  >
>
>

Mime
View raw message