Don't get me wrong... I'm fully in support of continuing to generate
the AUTO_PK_SUPPORT table. :)
I'm just curious why we insert rows in there for entities that have a
"db generated" pk generation strategy, for databases that support db-
generated keys.
That is, given the following (simplified) mapping:
User -> db generated key
UsageRecord -> cayenne generated (for performance, say, so cayenne can
batch the keys)
For schema generation on mysql, I would expect:
create table AUTO_PK_SUPPORT ...;
insert into AUTO_PK_SUPPORT (...) values('UsageRecord',200);
But what you get is:
create table AUTO_PK_SUPPORT ...;
insert into AUTO_PK_SUPPORT (...) values('UsageRecord',200);
insert into AUTO_PK_SUPPORT (...) values('User',200); <----- this is
the point of curiousness, and, I think, the point of confusion for Joe.
The question becomes: on a database that supports db-generated keys
(mysql), for a table using db-generated keys (User), why is there a
corresponding row in the AUTO_PK_SUPPORT table?
Again... it doesn't really hurt anything, but I agree with Joe that
it's a little confusing. :)
Robert
On Apr 21, 2010, at 4/217:45 AM , Michael Gentry wrote:
Against a MySQL DB, I can use MySQL-generated keys and the Cayenne
AUTO_PK_SUPPORT table at the same time (different entities, of
course). Like I said earlier, perhaps if NO entities use the
AUTO_PK_SUPPORT keys, we might could hide the checkbox for the
AUTO_PK_SUPPORT schema generation, but I'm not 100% convinced of that
argument yet.
Thanks,
mrg
On Wed, Apr 21, 2010 at 12:27 AM, Robert Zeigler
<[email protected]> wrote:
Sure... I can understand that.
But if you're generating against a db that supports db-generated
keys, and
you have an entity that requests db-generated keys, then it seems
like
there's no reason to include the row for that entity in the
auto_pk_support
table, other than the fact that having it there doesn't do any harm
(except
confuse new users :), but keeps the codebase for the schema
generation a
little cleaner. It smells like an oversight, but I'm not sure that
it is,
so I was curious. :)
Robert
On Apr 20, 2010, at 4/204:09 PM , Michael Gentry wrote:
"I'm actually curious to know the rationale behind that one,
myself."
In the beginning ... OK, I'm speculating here because I wasn't
around
in the beginning ... I believe Cayenne ONLY supported the
AUTO_PK_SUPPORT, although maybe Oracle sequences were there early
on.
Over time, DB-generated key support was added (sometime in 2.x, I
think) and also PostgreSQL sequences. It is also the only method
that
is going to work on all DBs.
mrg
On Tue, Apr 20, 2010 at 5:01 PM, Robert Zeigler
<[email protected]> wrote:
I can understand Joe's confusion: not only does cayenne generate
the
AUTO_PK_SUPPORT table, but it also inserts a row for each table,
regardless
of whether that table is using cayenne vs. db-generated ids.
I'm actually curious to know the rationale behind that one,
myself. :) I
mean, I know cayenne will fall back to using cayenne-generated
ids if the
db
connected to (or corresponding driver) doesn't support auto-pk
generation.
But you should be able to detect that at schema generation time,
and you
have the mapping in hand to determine which entities will use
db-generated
pks... so... at the very least, shouldn't you only insert a row
for those
entities which need it?
The flip side, though, is that having an unused row in the db
will have
virtually no performance impact, and it keeps the generation code
much
simpler. *shrug* Just thinking out loud, I guess... I'd love to
hear the
original rationale for including all entities in the table,
regardless of
what their pk-generation strategy is. :)
Robert
On Apr 20, 2010, at 4/203:43 PM , Michael Gentry wrote:
Hi Joe,
On Tue, Apr 20, 2010 at 1:12 PM, Joe Baldwin <[email protected]
>
wrote:
Michael,
However, I would suggest not doing both strategies on the same
table
as
it will probably bite you in the long run somehow.
That is not my intention (but I agree with you). I am
verifying all of
my entities right now. However, the Cayenne Modeler
configuration was
not
clear. You said that if I set the "PK Generation Strategy" to
"Database
Generated" but then I unintentionally had the "Create Primary Key
Support"
checked in the "Generate DB Schema" Options dialog, then it would
create the
AUTO_PK_SUPPORT table.
It is not clear to me why you have this in two separate config
parameters. Base on your explanation (in the previous email),
that if
you
select the "PK Generation Strategy" type for the individual
entities,
then
the "Create Primary Key Support" option should be automatically
configured
at that time.
Said a different way: why would the Cayenne Modeler create
Cayenne-Managed Primary Key Support for tables with the "PK
Generation
Strategy" to "Database Generated"?
I think you are confusing Cayenne Modeler's schema generation
feature
with Cayenne's runtime primary key support feature. More below.
I may be wrong, but base on what you had described, it seems like
Cayenne
Modeler is creating a conflicting configuration in this scenario.
There is no conflict. Perhaps if you don't use "Database
Generated"
on any DbEntities then it would be safe in Cayenne Modeler to
not have
the checkbox when generating the SQL to create the AUTO_PK_SUPPORT
table, but I'm not even sure I agree with that idea. Keep in mind
that each table can have different PK generation options (even
though
it would potentially be confusing). Cayenne doesn't stop you from
using the AUTO_PK_SUPPORT on some entities even when you are using
MySQL's auto-generated PK on other entities (for example, you
may need
higher performance on some tables for bulk inserts). Cayenne
will use
whichever strategy you specify for the entity, but allows you to
create the AUTO_PK_SUPPORT if it is needed (your call) when you
generate the SQL.
If you have Cayenne generating the keys, it'll push them to
MySQL.
I agree, but I have clearly set "PK Generation Strategy" to
"Database
Generated". So my question remains: given that the CM allows
conflicting
parameters, which one takes precedence here? I have set "PK
Generation
Strategy" to "Database Generated", are you saying that Cayenne
then
ignores
this configuration?
However, I would suggest not doing both strategies on the same
table
as
it will probably bite you in the long run somehow.
I agree. That is *definitely* not my intention, (But as I
described
above, it appears that this is very easy to do with
CayenneModeler.)
Michael, what you have described concerning CM is not
intuitive. I
could
easily see a designer configuring one table with Cayenne-Managed
auto-generation, and another with database-auto-generation
(because
Cayenne
Modeler allows it). If what you are saying is true, then
selecting the
"Create Primary Key Support" checkbox, will override they
"database-auto-generation" parameter.
That's actually not at all what I am saying. Selecting the
"Create
Primary Key Support" checkbox in Cayenne Modeler's schema
generation
tool just creates it in the schema. It doesn't override what
you set
for each individual entity. Whomever is doing the design needs to
decide how the PKs are generated on each table. In a lot of
applications it is the same method for all entities, but it
doesn't
have to be that way.
If this is as bad as you suggest, then Cayenne Modeler should
either
prevent this from happening or display an explicit warning.
I don't think it is bad at all and never suggested it was. It
is a
very important and useful feature.
mrg
