Robert,
I might be able to make a suggestion (from a use-case perspective).
Premise of Confusion
What I previously perceived to be the Cayenne Modeler behavior (based on my interpretation of Michael's comments) was that the [CayenneModeler - Generate Database Schema - Create Primary Key Support] checkbox (set to CHECKED) overrides the the [Entity - PK Generation Strategy - Database Generated] set to "Database-Generated". I now know that this is not correct.
If this had been the case, then my previous comment was that: this is confusing/not-intuitive and it allowed for conflicting configuration. However, your explanation was, that the [Entity - PK Generation Strategy - Database Generated] attribute takes precedence in Cayenne behavior. My more informed comment was that this is what I had expected and that it is not confusing if these are the current rules.
Deployment Env Consideration
I believe that another problem could arise if a user (and even an experienced user) were to implement a mixed PK-Generation configuration, *chronologically following* a previous implementation of only a "Database Generated" configuration. The user would then be required to *remember* to create (or remember to allow CayenneModeler to create) and AUTO_PK_SUPPORT table.
Conclusion:
In the context of an "evolving" design, the current implementation (i.e. create the table and use it if the entity is configured for it) might be the simplest approach (to minimize config errors).
The second comment, is that you might consider creating popups (that one could turn on/off for the Modeler) that could notify you of the consequences of your actions. For example:
Warning: you have at least one Entity that you have configured for "Database Generated" PK support, but you have disabled the AUTO_PK_SUPPORT table generation. This could result in a conflict and possible corrupt Primary key values. We recommend: <enter something here>
This is just a thought.
Joe
On Apr 21, 2010, at 8:54 AM, Robert Zeigler wrote:
> 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
>> <robert.zeigle..oxanemy.com> 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
>>>> <robert.zeigle..oxanemy.com> 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 <jfbaldwi..arthlink.net>
>>>>>> 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
>>>>>
>>>>>
>>>
>>>
>
This archive was generated by hypermail 2.0.0 : Wed Apr 21 2010 - 12:54:04 EDT