cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Kienenberger" <mkien...@gmail.com>
Subject to-dep checkbox [Was: one-to-many problem]
Date Tue, 13 May 2008 23:27:20 GMT
Yeah, we probably should have moved this discussion to another thread
as soon as it became obvious it wasn't the direct cause of your
problem.

Here's an example of why to-dep-pk makes a difference:

Tables:

USER w/ pk of USER_ID
ACCOUNT w/pk of ACCOUNT_ID
USER_ACCOUNT join table with pk of USER_ID & ACCOUNT_ID.

User user = DatabaseContext.getContext().newObject(User.class);
Account account = DatabaseContext.getContext().newObject(Account.class);
UserAccount userAccount =
DatabaseContext.getContext().newObject(UserAccount.class);

userAccount.setAccount(account);
userAccount.setUser(user);

DatabaseContext.getContext().commitChanges();

This assumes that there's no flattened relationship, but the situation
is the same if that's the case -- only the java code to set the
relationships change.

User is going to get a pk generated by the database.
Account is going to get a pk generated by the database.
What is UserAccount going to get as a pk?

Without to-dep-pk, the options are as follows:
1) generated by the database -- won't work here.
2) manually set by the application -- again, doesn't work here since
we don't know what the pk values will be yet.

By specifying that the pk(s) of UserAccount depend on User and
Account, the Cayenne engine can "know" that these values are copied
from the master tables.

You might ask how this is any different from a fk that points to
USER_ID.  The difference is that a fk column is never generated, so
it's obvious that the pk side is generated by the database, and the fk
side is copied.

However, when both sides are pks, then it's no longer obvious which
side needs to be generated, and which side is copied.

And while joins are somewhat easier to guess at (in theory), a true
dependent table relationship isn't.

For example, USER with USER_ID as the pk, and USER_EXTRA_STUFF with
USER_ID as the pk.   USER_EXTRA_STUFF being a dependent table of
USER_ID where records are optional, but where USER.USER_ID always
equals USER_EXTRA_STUFF.USER_ID.

On 5/13/08, Scott Anderson <sanderson@airvana.com> wrote:
> That makes sense, but I still don't see how it's more than an
>  annotation. If a relationship exists, an instance of that relationship
>  is, by definition, where the values on each side of the relationship are
>  equal.
>
>  Can you provide an example of when the field makes a difference? Why
>  does Cayenne care which table is the master/slave? I can't see how
>  Cayenne's behavior would change in either a one-to-one or a many-to-many
>  relationship.
>
>
>  -----Original Message-----
>  From: Mike Kienenberger [mailto:mkienenb@gmail.com]
>
> Sent: Tuesday, May 13, 2008 1:03 PM
>  To: user@cayenne.apache.org
>  Subject: Re: one-to-many problem
>
>  It tells the Cayenne Runtime where to find/generate the value for the
>  PK.
>
>  Normally, they're generated by some "strategy" specified in the model,
>  or sometimes they are explicitly assigned by the application.
>
>  However, a dependent primary key (like a join table) has its primary
>  key value set with the value of the master primary key.
>
>  Ie,
>
>  a User/Account join table composed of USER_ID and ACCOUNT_ID, both
>  primary keys, needs to get the value for each from the primary key of
>  the USER table and the ACCOUNT table.   We don't want to generate one.
>   We don't want to assign one.   We want it to pick up the existing
>  value and reuse it.
>
>  Hope this helps explain dependent primary keys better.
>
>  On 5/12/08, Scott Anderson <sanderson@airvana.com> wrote:
>  > Seems like a pointless annotation; vaguely equivalent to the reverse
>  of the relationship being ON DELETE CASCADE.
>  >
>  >
>  >
>  >  -----Original Message-----
>  >  From: Mike Kienenberger [mailto:mkienenb@gmail.com]
>  >
>  > Sent: Mon 5/12/2008 7:16 PM
>  >  To: user@cayenne.apache.org
>  >  Subject: Re: one-to-many problem
>  >
>  >  Yeah, in retrospect it won't work because command.name isn't a pk.
>  >
>  >  http://cayenne.apache.org/doc12/to-dep-pk-checkbox.html should
>  explain
>  >  it fairly well.
>  >
>  >  On 5/12/08, Scott Anderson <sanderson@airvana.com> wrote:
>  >  > Can't check the box. Can you explain what that does? I read the
>  >  >  documentation for it about 100 times and I still can't make heads
>  or
>  >  >  tails of it.
>  >  >
>  >  >
>  >  >  -----Original Message-----
>  >  >  From: Mike Kienenberger [mailto:mkienenb@gmail.com]
>  >  >  Sent: Monday, May 12, 2008 6:57 PM
>  >  >  To: user@cayenne.apache.org
>  >  >  Subject: Re: one-to-many problem
>  >  >
>  >  >  One thing you could try is to make command.name ->
>  command_alias.name
>  >  >  to-dependent-key=true if it's not already set that way.  Don't
>  know if
>  >  >  it'll help, though.
>  >  >
>  >  >
>  >  >  On 5/12/08, Scott Anderson <sanderson@airvana.com> wrote:
>  >  >  > I've got a table `command which has:
>  >  >  >         `id` int PK
>  >  >  >         `name` varchar(32) UNIQUE
>  >  >  >
>  >  >  >  and a table `command_alias` which has
>  >  >  >         `alias` varchar(32) PK
>  >  >  >         `name` varchar(32) FK REF `command`.`name`
>  >  >  >
>  >  >  >  Take special note how `command_alias`.`name` is a FK to
>  >  >  `command`.`name`
>  >  >  >  (unique field) and not `command`.`id` (the PK)
>  >  >  >
>  >  >  >  The following code chokes:
>  >  >  >         public static CommandAlias create(Command command,
>  String
>  >  >  alias)
>  >  >  >  {
>  >  >  >                 CommandAlias ca =
>  >  >  >  DatabaseContext.getContext().newObject(CommandAlias.class);
>  >  >  >                 ca.setAlias(alias);
>  >  >  >                 ca.setToCommand(command);
>  >  >  >                 command.addToAliases(ca);
>  >  >  >                 try {
>  >  >  >                         ca.updateRow();
>  >  >  >                         return ca;
>  >  >  >                 } catch(Exception e) {
>  >  >  >                         Out.exception(e);
>  >  >  >                         return null;
>  >  >  >                 }
>  >  >  >         }
>  >  >  >
>  >  >  >  With the error:
>  >  >  >
>  >  >  >  May 12, 2008 6:15:55 PM org.apache.cayenne.access.QueryLogger
>  >  >  logQuery
>  >  >  >  INFO: INSERT INTO command_alias (alias, name) VALUES (?, ?)
>  >  >  >  INFO: [batch bind: 1->alias:'aa', 2->name:NULL]
>  >  >  >  May 12, 2008 6:15:55 PM org.apache.cayenne.access.QueryLogger
>  >  >  >  logQueryError
>  >  >  >  INFO: *** error.
>  >  >  >  java.sql.SQLIntegrityConstraintViolationException: Column
>  'NAME'
>  >  >  cannot
>  >  >  >  accept a NULL value.
>  >  >  >
>  >  >  >
>  >  >  >  I am 100% sure that I am not sending a null Command object. I
>  believe
>  >  >  >  this stems from the fact that the relationship is not a FK-PK
>  >  >  >  relationship, but a FK-UNIQUE relationship, as evidenced by the
>  fact
>  >  >  >  that if I make the `command_alias`.`name` field visible in the
>  code,
>  >  >  and
>  >  >  >  set do ca.setName(command.getName()) then this error does not
>  occur.
>  >  >  >
>  >  >  >  I am using a 3.0 snapshot from March.
>  >  >  >
>  >  >  >
>  >  >  >  And here's the relevant sections of my mapping file (I removed
>  some
>  >  >  >  unrelated fields from command):
>  >  >  >
>  >  >  >         <db-entity name="command">
>  >  >  >                 <db-attribute name="id" type="INTEGER"
>  >  >  >  isPrimaryKey="true" isGenerated="true" isMandatory="true"
>  >  >  length="11"/>
>  >  >  >                 <db-attribute name="name" type="VARCHAR"
>  >  >  >  isMandatory="true" length="32"/>
>  >  >  >         </db-entity>
>  >  >  >         <db-entity name="command_alias">
>  >  >  >                 <db-attribute name="alias" type="VARCHAR"
>  >  >  >  isPrimaryKey="true" isMandatory="true" length="32"/>
>  >  >  >                 <db-attribute name="name" type="VARCHAR"
>  length="32"/>
>  >  >  >         </db-entity>
>  >  >  >         <obj-entity name="Command"
>  className="net.bnubot.db.Command"
>  >  >  >  dbEntityName="command"
>  >  >  superClassName="net.bnubot.db.CustomDataObject">
>  >  >  >                 <obj-attribute name="name"
>  type="java.lang.String"
>  >  >  >  db-attribute-path="name"/>
>  >  >  >         </obj-entity>
>  >  >  >         <obj-entity name="CommandAlias"
>  >  >  >  className="net.bnubot.db.CommandAlias"
>  dbEntityName="command_alias"
>  >  >  >  superClassName="net.bnubot.db.CustomDataObject">
>  >  >  >                 <obj-attribute name="alias"
>  type="java.lang.String"
>  >  >  >  db-attribute-path="alias"/>
>  >  >  >         </obj-entity>
>  >  >  >         <db-relationship name="commandAliasArray"
>  source="command"
>  >  >  >  target="command_alias" toMany="true">
>  >  >  >                 <db-attribute-pair source="name" target="name"/>
>  >  >  >         </db-relationship>
>  >  >  >         <db-relationship name="toCommand" source="command_alias"
>  >  >  >  target="command" toMany="false">
>  >  >  >                 <db-attribute-pair source="name" target="name"/>
>  >  >  >         </db-relationship>
>  >  >  >         <obj-relationship name="aliases" source="Command"
>  >  >  >  target="CommandAlias" deleteRule="Deny"
>  >  >  >  db-relationship-path="commandAliasArray"/>
>  >  >  >         <obj-relationship name="toCommand" source="CommandAlias"
>  >  >  >  target="Command" db-relationship-path="toCommand"/>
>  >  >  >
>  >  >
>  >
>  >
>

Mime
View raw message