You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Joe Baldwin <jf...@earthlink.net> on 2010/04/20 02:03:32 UTC
Elementary Question
I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
Please refer to the attached CM configuration panel for an Entity.
1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
Thanks
Joe
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
Robert,
That is how I had originally understood the configuration parameters to work. Thanks.
BTW, Michael sent a reply that I interpreted his meaning to be that the CM configuration could conflict with itself. Your explanation seems to be consistent with how I would expect it to work.
Unless I am off track with Michael's response, I feel I understand yours a bit better.
Thanks,
Joe
On Apr 20, 2010, at 4:24 PM, Robert Zeigler wrote:
> Cayenne generates the appropriat AUTO_PK_SUPPORT table regardless of the pk
> generation strategy, but if your pk generation strategy is "Database
> Generated" (AND if your db + driver support auto-pk generation), it won't
> actually use it; it will allow the database to generate the id and use the
> generated id.
>
> Robert
>
> On Tue, Apr 20, 2010 at 9:15 AM, Joe Baldwin <jf...@earthlink.net>wrote:
>
>> Michael,
>>
>> I need a simple verification (because the webhost tech person is very
>> confused).
>>
>> To explain further: I am using Cayenne Modeler to design the entities, and
>> to create the database tables in MySQL. (i.e. Tools - Generate Database
>> Schema). My understanding, and my tests have proved, if you set [PK
>> Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid
>> (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create
>> a table, then set the oid as the primary key, and set auto_increment to
>> "true" for that field.
>>
>> The following code was generated by the Cayenne Modeler (I deleted
>> everything not having to do with the table or key)
>>
>> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
>> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL,
>> NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
>> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product',
>> 200);
>>
>>
>> The question now is: Has Cayenne Modeler generated SQL that creates a table
>> named "product", with a primary key "oid", whose value is automatically
>> created by MySQL's InnoDB engine, using auto_increment, at the time of the
>> INSERT? Another way of asking this is: In this scenario, does Cayenne
>> generate the key, or does MySQL generate the key?
>>
>> I would like to forward a brief response to the webhost.
>>
>> Thanks,
>> Joe
>>
>>
>>
>>
>> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>>
>>> Hi Joe,
>>>
>>> Your wording on #2 and #3 was a little curious to me. I don't know if
>>> you used Cayenne Modeler to generate your schema or if you are using
>>> an existing schema. Cayenne (at runtime) doesn't really instruct the
>>> database (MySQL) to auto-generate keys. Cayenne will nont provide a
>>> value for "oid" when inserting the record and that essentially
>>> instructs MySQL to auto-generate a key. You set this up when you
>>> create your product table, for example. The database needs to know
>>> that it is responsible for auto-generating the keys -- Cayenne won't
>>> tell it at runtime (other than leaving the key out on insert).
>>>
>>> Maybe I'm a bit off on what you were asking, but it seems like you are
>>> expecting Cayenne to tell MySQL to generate the keys when it actually
>>> works the other way around: MySQL is supposed to know to generate the
>>> key and Cayenne then asks it for the key it generated.
>>>
>>> mrg
>>>
>>> PS. BTW, your settings are correct to use the auto-generated key
>>> feature in MySQL, just be sure the table was created to auto-generate
>>> on your "oid" column.
>>>
>>>
>>> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net>
>> wrote:
>>>> I apologize for this, but I am being forced to verify a configuration
>> that is pretty obvious.
>>>>
>>>> Please refer to the attached CM configuration panel for an Entity.
>>>>
>>>> 1. The Primary Key "PK Generation Strategy" is set to
>> "Database-Generated". Please verify that this means that the PK Generation
>> Strategy is Database Generated.
>>>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please
>> verify that the field being auto-incremented is "oid".
>>>> 3. Please verify that (with the DBMS set to MySQL) that the two previous
>> parameters instruct MySQL to use its auto_increment feature to create a new
>> "oid" value for inserted records.
>>>>
>>>> Thanks
>>>> Joe
>>>>
>>>>
>>>>
>>
>>
Re: Elementary Question
Posted by Robert Zeigler <ro...@roxanemy.com>.
Cayenne generates the appropriat AUTO_PK_SUPPORT table regardless of the pk
generation strategy, but if your pk generation strategy is "Database
Generated" (AND if your db + driver support auto-pk generation), it won't
actually use it; it will allow the database to generate the id and use the
generated id.
Robert
On Tue, Apr 20, 2010 at 9:15 AM, Joe Baldwin <jf...@earthlink.net>wrote:
> Michael,
>
> I need a simple verification (because the webhost tech person is very
> confused).
>
> To explain further: I am using Cayenne Modeler to design the entities, and
> to create the database tables in MySQL. (i.e. Tools - Generate Database
> Schema). My understanding, and my tests have proved, if you set [PK
> Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid
> (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create
> a table, then set the oid as the primary key, and set auto_increment to
> "true" for that field.
>
> The following code was generated by the Cayenne Modeler (I deleted
> everything not having to do with the table or key)
>
> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL,
> NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product',
> 200);
>
>
> The question now is: Has Cayenne Modeler generated SQL that creates a table
> named "product", with a primary key "oid", whose value is automatically
> created by MySQL's InnoDB engine, using auto_increment, at the time of the
> INSERT? Another way of asking this is: In this scenario, does Cayenne
> generate the key, or does MySQL generate the key?
>
> I would like to forward a brief response to the webhost.
>
> Thanks,
> Joe
>
>
>
>
> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>
> > Hi Joe,
> >
> > Your wording on #2 and #3 was a little curious to me. I don't know if
> > you used Cayenne Modeler to generate your schema or if you are using
> > an existing schema. Cayenne (at runtime) doesn't really instruct the
> > database (MySQL) to auto-generate keys. Cayenne will nont provide a
> > value for "oid" when inserting the record and that essentially
> > instructs MySQL to auto-generate a key. You set this up when you
> > create your product table, for example. The database needs to know
> > that it is responsible for auto-generating the keys -- Cayenne won't
> > tell it at runtime (other than leaving the key out on insert).
> >
> > Maybe I'm a bit off on what you were asking, but it seems like you are
> > expecting Cayenne to tell MySQL to generate the keys when it actually
> > works the other way around: MySQL is supposed to know to generate the
> > key and Cayenne then asks it for the key it generated.
> >
> > mrg
> >
> > PS. BTW, your settings are correct to use the auto-generated key
> > feature in MySQL, just be sure the table was created to auto-generate
> > on your "oid" column.
> >
> >
> > On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net>
> wrote:
> >> I apologize for this, but I am being forced to verify a configuration
> that is pretty obvious.
> >>
> >> Please refer to the attached CM configuration panel for an Entity.
> >>
> >> 1. The Primary Key "PK Generation Strategy" is set to
> "Database-Generated". Please verify that this means that the PK Generation
> Strategy is Database Generated.
> >> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please
> verify that the field being auto-incremented is "oid".
> >> 3. Please verify that (with the DBMS set to MySQL) that the two previous
> parameters instruct MySQL to use its auto_increment feature to create a new
> "oid" value for inserted records.
> >>
> >> Thanks
> >> Joe
> >>
> >>
> >>
>
>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
I understand. I just have mixed feelings on it. :-)
I just check or uncheck the checkbox, but I know to do that and can
see it might be a bit confusing to someone less familiar with the
modeler. It could be nice, especially early on, to have it there in
case you decide to change the PK generation type, but that is almost a
cop-out argument.
Like I said, I have mixed feelings. Maybe others will feel more
strongly on it. Maybe I just need more caffeine and less pollen? :-)
mrg
On Wed, Apr 21, 2010 at 8:54 AM, Robert Zeigler
<ro...@roxanemy.com> 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
>> <ro...@roxanemy.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
>>>> <ro...@roxanemy.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 <jf...@earthlink.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
>>>>>
>>>>>
>>>
>>>
>
>
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
Oops!
I previously wrote:
> 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>
I meant to say "NO Database Generated PK support" (which, of course, requires the existence of a AUTO_PK_SUPPORT table.
(I am sure you were scratching your heads over this mistake. :) )
Joe
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
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
>> <ro...@roxanemy.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
>>>> <ro...@roxanemy.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 <jf...@earthlink.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
>>>>>
>>>>>
>>>
>>>
>
Re: Elementary Question
Posted by Robert Zeigler <ro...@roxanemy.com>.
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
> <ro...@roxanemy.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
>>> <ro...@roxanemy.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 <jfbaldwin@earthlink.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
>>>>
>>>>
>>
>>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
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
<ro...@roxanemy.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
>> <ro...@roxanemy.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 <jf...@earthlink.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
>>>
>>>
>
>
Re: Elementary Question
Posted by Robert Zeigler <ro...@roxanemy.com>.
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
> <ro...@roxanemy.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 <jfbaldwin@earthlink.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
>>
>>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
"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
<ro...@roxanemy.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 <jf...@earthlink.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
>
>
Re: Elementary Question
Posted by Robert Zeigler <ro...@roxanemy.com>.
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
> <jf...@earthlink.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
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
Hi Joe,
On Tue, Apr 20, 2010 at 1:12 PM, Joe Baldwin <jf...@earthlink.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
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
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 may be wrong, but base on what you had described, it seems like Cayenne Modeler is creating a conflicting configuration in this scenario.
> MySQL will only try to auto-generate keys when you don't supply one on your own.
I agree. I have tested this.
> 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.
If this is as bad as you suggest, then Cayenne Modeler should either prevent this from happening or display an explicit warning.
Joe
On Apr 20, 2010, at 12:43 PM, Michael Gentry wrote:
> MySQL will only try to auto-generate keys when you don't supply one on
> your own. If you have Cayenne generating the keys, it'll push them to
> MySQL. However, I would suggest not doing both strategies on the same
> table as it will probably bite you in the long run somehow.
>
> mrg
>
>
> On Tue, Apr 20, 2010 at 12:31 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>> Michael,
>>
>> Good catch. I did not intentionally select the checkbox "Create Primary Key Support" (I have deselected this).
>>
>> However, I did verify that all the tables in the database have Auto-Increment for the oid field.
>>
>> So, if I had "PK Generation Strategy" set to "Database-Generated", and each table had "auto-increment" set to true for the oid field (which I just verified by inspecting each of the tables), which would have created the value for the oid field? (I am assuming this would have been generated by MySQL InnoDB.)
>>
>> Thanks,
>> Joe
>>
>>
>>
>>
>>
>> On Apr 20, 2010, at 11:17 AM, Michael Gentry wrote:
>>
>>> Hi Joe,
>>>
>>> The AUTO_PK_SUPPORT table is only used when PK Generation Strategy =
>>> Default. Cayenne Modeler creates the AUTO_PK_SUPPORT if when
>>> generating the SQL you check the "Create Primary Key Support"
>>> checkbox. You don't need AUTO_PK_SUPPORT unless you are using the
>>> Default strategy on some of your tables.
>>>
>>> Your create statement should look more like:
>>>
>>> CREATE TABLE pickers.product (..., oid BIGINT NOT NULL AUTO_INCREMENT,
>>> ..., PRIMARY KEY (oid)) ENGINE=InnoDB;
>>>
>>> Make sure your create statement includes the AUTO_INCREMENT option for
>>> your oid. If you don't have AUTO_INCREMENT as an option, MySQL will
>>> not auto-increment or generate your PKs.
>>>
>>> mrg
>>>
>>>
>>> On Tue, Apr 20, 2010 at 10:15 AM, Joe Baldwin <jf...@earthlink.net> wrote:
>>>> Michael,
>>>>
>>>> I need a simple verification (because the webhost tech person is very confused).
>>>>
>>>> To explain further: I am using Cayenne Modeler to design the entities, and to create the database tables in MySQL. (i.e. Tools - Generate Database Schema). My understanding, and my tests have proved, if you set [PK Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, then set the oid as the primary key, and set auto_increment to "true" for that field.
>>>>
>>>> The following code was generated by the Cayenne Modeler (I deleted everything not having to do with the table or key)
>>>>
>>>> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
>>>> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
>>>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
>>>> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', 200);
>>>>
>>>>
>>>> The question now is: Has Cayenne Modeler generated SQL that creates a table named "product", with a primary key "oid", whose value is automatically created by MySQL's InnoDB engine, using auto_increment, at the time of the INSERT? Another way of asking this is: In this scenario, does Cayenne generate the key, or does MySQL generate the key?
>>>>
>>>> I would like to forward a brief response to the webhost.
>>>>
>>>> Thanks,
>>>> Joe
>>>>
>>>>
>>>>
>>>>
>>>> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>>>>
>>>>> Hi Joe,
>>>>>
>>>>> Your wording on #2 and #3 was a little curious to me. I don't know if
>>>>> you used Cayenne Modeler to generate your schema or if you are using
>>>>> an existing schema. Cayenne (at runtime) doesn't really instruct the
>>>>> database (MySQL) to auto-generate keys. Cayenne will nont provide a
>>>>> value for "oid" when inserting the record and that essentially
>>>>> instructs MySQL to auto-generate a key. You set this up when you
>>>>> create your product table, for example. The database needs to know
>>>>> that it is responsible for auto-generating the keys -- Cayenne won't
>>>>> tell it at runtime (other than leaving the key out on insert).
>>>>>
>>>>> Maybe I'm a bit off on what you were asking, but it seems like you are
>>>>> expecting Cayenne to tell MySQL to generate the keys when it actually
>>>>> works the other way around: MySQL is supposed to know to generate the
>>>>> key and Cayenne then asks it for the key it generated.
>>>>>
>>>>> mrg
>>>>>
>>>>> PS. BTW, your settings are correct to use the auto-generated key
>>>>> feature in MySQL, just be sure the table was created to auto-generate
>>>>> on your "oid" column.
>>>>>
>>>>>
>>>>> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>>>>>> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>>>>>>
>>>>>> Please refer to the attached CM configuration panel for an Entity.
>>>>>>
>>>>>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
>>>>>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
>>>>>> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>>>>>>
>>>>>> Thanks
>>>>>> Joe
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
MySQL will only try to auto-generate keys when you don't supply one on
your own. If you have Cayenne generating the keys, it'll push them to
MySQL. However, I would suggest not doing both strategies on the same
table as it will probably bite you in the long run somehow.
mrg
On Tue, Apr 20, 2010 at 12:31 PM, Joe Baldwin <jf...@earthlink.net> wrote:
> Michael,
>
> Good catch. I did not intentionally select the checkbox "Create Primary Key Support" (I have deselected this).
>
> However, I did verify that all the tables in the database have Auto-Increment for the oid field.
>
> So, if I had "PK Generation Strategy" set to "Database-Generated", and each table had "auto-increment" set to true for the oid field (which I just verified by inspecting each of the tables), which would have created the value for the oid field? (I am assuming this would have been generated by MySQL InnoDB.)
>
> Thanks,
> Joe
>
>
>
>
>
> On Apr 20, 2010, at 11:17 AM, Michael Gentry wrote:
>
>> Hi Joe,
>>
>> The AUTO_PK_SUPPORT table is only used when PK Generation Strategy =
>> Default. Cayenne Modeler creates the AUTO_PK_SUPPORT if when
>> generating the SQL you check the "Create Primary Key Support"
>> checkbox. You don't need AUTO_PK_SUPPORT unless you are using the
>> Default strategy on some of your tables.
>>
>> Your create statement should look more like:
>>
>> CREATE TABLE pickers.product (..., oid BIGINT NOT NULL AUTO_INCREMENT,
>> ..., PRIMARY KEY (oid)) ENGINE=InnoDB;
>>
>> Make sure your create statement includes the AUTO_INCREMENT option for
>> your oid. If you don't have AUTO_INCREMENT as an option, MySQL will
>> not auto-increment or generate your PKs.
>>
>> mrg
>>
>>
>> On Tue, Apr 20, 2010 at 10:15 AM, Joe Baldwin <jf...@earthlink.net> wrote:
>>> Michael,
>>>
>>> I need a simple verification (because the webhost tech person is very confused).
>>>
>>> To explain further: I am using Cayenne Modeler to design the entities, and to create the database tables in MySQL. (i.e. Tools - Generate Database Schema). My understanding, and my tests have proved, if you set [PK Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, then set the oid as the primary key, and set auto_increment to "true" for that field.
>>>
>>> The following code was generated by the Cayenne Modeler (I deleted everything not having to do with the table or key)
>>>
>>> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
>>> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
>>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
>>> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', 200);
>>>
>>>
>>> The question now is: Has Cayenne Modeler generated SQL that creates a table named "product", with a primary key "oid", whose value is automatically created by MySQL's InnoDB engine, using auto_increment, at the time of the INSERT? Another way of asking this is: In this scenario, does Cayenne generate the key, or does MySQL generate the key?
>>>
>>> I would like to forward a brief response to the webhost.
>>>
>>> Thanks,
>>> Joe
>>>
>>>
>>>
>>>
>>> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>>>
>>>> Hi Joe,
>>>>
>>>> Your wording on #2 and #3 was a little curious to me. I don't know if
>>>> you used Cayenne Modeler to generate your schema or if you are using
>>>> an existing schema. Cayenne (at runtime) doesn't really instruct the
>>>> database (MySQL) to auto-generate keys. Cayenne will nont provide a
>>>> value for "oid" when inserting the record and that essentially
>>>> instructs MySQL to auto-generate a key. You set this up when you
>>>> create your product table, for example. The database needs to know
>>>> that it is responsible for auto-generating the keys -- Cayenne won't
>>>> tell it at runtime (other than leaving the key out on insert).
>>>>
>>>> Maybe I'm a bit off on what you were asking, but it seems like you are
>>>> expecting Cayenne to tell MySQL to generate the keys when it actually
>>>> works the other way around: MySQL is supposed to know to generate the
>>>> key and Cayenne then asks it for the key it generated.
>>>>
>>>> mrg
>>>>
>>>> PS. BTW, your settings are correct to use the auto-generated key
>>>> feature in MySQL, just be sure the table was created to auto-generate
>>>> on your "oid" column.
>>>>
>>>>
>>>> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>>>>> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>>>>>
>>>>> Please refer to the attached CM configuration panel for an Entity.
>>>>>
>>>>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
>>>>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
>>>>> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>>>>>
>>>>> Thanks
>>>>> Joe
>>>>>
>>>>>
>>>>>
>>>
>>>
>
>
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
Michael,
Good catch. I did not intentionally select the checkbox "Create Primary Key Support" (I have deselected this).
However, I did verify that all the tables in the database have Auto-Increment for the oid field.
So, if I had "PK Generation Strategy" set to "Database-Generated", and each table had "auto-increment" set to true for the oid field (which I just verified by inspecting each of the tables), which would have created the value for the oid field? (I am assuming this would have been generated by MySQL InnoDB.)
Thanks,
Joe
On Apr 20, 2010, at 11:17 AM, Michael Gentry wrote:
> Hi Joe,
>
> The AUTO_PK_SUPPORT table is only used when PK Generation Strategy =
> Default. Cayenne Modeler creates the AUTO_PK_SUPPORT if when
> generating the SQL you check the "Create Primary Key Support"
> checkbox. You don't need AUTO_PK_SUPPORT unless you are using the
> Default strategy on some of your tables.
>
> Your create statement should look more like:
>
> CREATE TABLE pickers.product (..., oid BIGINT NOT NULL AUTO_INCREMENT,
> ..., PRIMARY KEY (oid)) ENGINE=InnoDB;
>
> Make sure your create statement includes the AUTO_INCREMENT option for
> your oid. If you don't have AUTO_INCREMENT as an option, MySQL will
> not auto-increment or generate your PKs.
>
> mrg
>
>
> On Tue, Apr 20, 2010 at 10:15 AM, Joe Baldwin <jf...@earthlink.net> wrote:
>> Michael,
>>
>> I need a simple verification (because the webhost tech person is very confused).
>>
>> To explain further: I am using Cayenne Modeler to design the entities, and to create the database tables in MySQL. (i.e. Tools - Generate Database Schema). My understanding, and my tests have proved, if you set [PK Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, then set the oid as the primary key, and set auto_increment to "true" for that field.
>>
>> The following code was generated by the Cayenne Modeler (I deleted everything not having to do with the table or key)
>>
>> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
>> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
>> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
>> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', 200);
>>
>>
>> The question now is: Has Cayenne Modeler generated SQL that creates a table named "product", with a primary key "oid", whose value is automatically created by MySQL's InnoDB engine, using auto_increment, at the time of the INSERT? Another way of asking this is: In this scenario, does Cayenne generate the key, or does MySQL generate the key?
>>
>> I would like to forward a brief response to the webhost.
>>
>> Thanks,
>> Joe
>>
>>
>>
>>
>> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>>
>>> Hi Joe,
>>>
>>> Your wording on #2 and #3 was a little curious to me. I don't know if
>>> you used Cayenne Modeler to generate your schema or if you are using
>>> an existing schema. Cayenne (at runtime) doesn't really instruct the
>>> database (MySQL) to auto-generate keys. Cayenne will nont provide a
>>> value for "oid" when inserting the record and that essentially
>>> instructs MySQL to auto-generate a key. You set this up when you
>>> create your product table, for example. The database needs to know
>>> that it is responsible for auto-generating the keys -- Cayenne won't
>>> tell it at runtime (other than leaving the key out on insert).
>>>
>>> Maybe I'm a bit off on what you were asking, but it seems like you are
>>> expecting Cayenne to tell MySQL to generate the keys when it actually
>>> works the other way around: MySQL is supposed to know to generate the
>>> key and Cayenne then asks it for the key it generated.
>>>
>>> mrg
>>>
>>> PS. BTW, your settings are correct to use the auto-generated key
>>> feature in MySQL, just be sure the table was created to auto-generate
>>> on your "oid" column.
>>>
>>>
>>> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>>>> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>>>>
>>>> Please refer to the attached CM configuration panel for an Entity.
>>>>
>>>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
>>>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
>>>> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>>>>
>>>> Thanks
>>>> Joe
>>>>
>>>>
>>>>
>>
>>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
Hi Joe,
The AUTO_PK_SUPPORT table is only used when PK Generation Strategy =
Default. Cayenne Modeler creates the AUTO_PK_SUPPORT if when
generating the SQL you check the "Create Primary Key Support"
checkbox. You don't need AUTO_PK_SUPPORT unless you are using the
Default strategy on some of your tables.
Your create statement should look more like:
CREATE TABLE pickers.product (..., oid BIGINT NOT NULL AUTO_INCREMENT,
..., PRIMARY KEY (oid)) ENGINE=InnoDB;
Make sure your create statement includes the AUTO_INCREMENT option for
your oid. If you don't have AUTO_INCREMENT as an option, MySQL will
not auto-increment or generate your PKs.
mrg
On Tue, Apr 20, 2010 at 10:15 AM, Joe Baldwin <jf...@earthlink.net> wrote:
> Michael,
>
> I need a simple verification (because the webhost tech person is very confused).
>
> To explain further: I am using Cayenne Modeler to design the entities, and to create the database tables in MySQL. (i.e. Tools - Generate Database Schema). My understanding, and my tests have proved, if you set [PK Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, then set the oid as the primary key, and set auto_increment to "true" for that field.
>
> The following code was generated by the Cayenne Modeler (I deleted everything not having to do with the table or key)
>
> CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
> CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
> DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
> INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', 200);
>
>
> The question now is: Has Cayenne Modeler generated SQL that creates a table named "product", with a primary key "oid", whose value is automatically created by MySQL's InnoDB engine, using auto_increment, at the time of the INSERT? Another way of asking this is: In this scenario, does Cayenne generate the key, or does MySQL generate the key?
>
> I would like to forward a brief response to the webhost.
>
> Thanks,
> Joe
>
>
>
>
> On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
>
>> Hi Joe,
>>
>> Your wording on #2 and #3 was a little curious to me. I don't know if
>> you used Cayenne Modeler to generate your schema or if you are using
>> an existing schema. Cayenne (at runtime) doesn't really instruct the
>> database (MySQL) to auto-generate keys. Cayenne will nont provide a
>> value for "oid" when inserting the record and that essentially
>> instructs MySQL to auto-generate a key. You set this up when you
>> create your product table, for example. The database needs to know
>> that it is responsible for auto-generating the keys -- Cayenne won't
>> tell it at runtime (other than leaving the key out on insert).
>>
>> Maybe I'm a bit off on what you were asking, but it seems like you are
>> expecting Cayenne to tell MySQL to generate the keys when it actually
>> works the other way around: MySQL is supposed to know to generate the
>> key and Cayenne then asks it for the key it generated.
>>
>> mrg
>>
>> PS. BTW, your settings are correct to use the auto-generated key
>> feature in MySQL, just be sure the table was created to auto-generate
>> on your "oid" column.
>>
>>
>> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>>> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>>>
>>> Please refer to the attached CM configuration panel for an Entity.
>>>
>>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
>>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
>>> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>>>
>>> Thanks
>>> Joe
>>>
>>>
>>>
>
>
Re: Elementary Question
Posted by Joe Baldwin <jf...@earthlink.net>.
Michael,
I need a simple verification (because the webhost tech person is very confused).
To explain further: I am using Cayenne Modeler to design the entities, and to create the database tables in MySQL. (i.e. Tools - Generate Database Schema). My understanding, and my tests have proved, if you set [PK Generation Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, then set the oid as the primary key, and set auto_increment to "true" for that field.
The following code was generated by the Cayenne Modeler (I deleted everything not having to do with the table or key)
CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB;
CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME));
DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product');
INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', 200);
The question now is: Has Cayenne Modeler generated SQL that creates a table named "product", with a primary key "oid", whose value is automatically created by MySQL's InnoDB engine, using auto_increment, at the time of the INSERT? Another way of asking this is: In this scenario, does Cayenne generate the key, or does MySQL generate the key?
I would like to forward a brief response to the webhost.
Thanks,
Joe
On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote:
> Hi Joe,
>
> Your wording on #2 and #3 was a little curious to me. I don't know if
> you used Cayenne Modeler to generate your schema or if you are using
> an existing schema. Cayenne (at runtime) doesn't really instruct the
> database (MySQL) to auto-generate keys. Cayenne will nont provide a
> value for "oid" when inserting the record and that essentially
> instructs MySQL to auto-generate a key. You set this up when you
> create your product table, for example. The database needs to know
> that it is responsible for auto-generating the keys -- Cayenne won't
> tell it at runtime (other than leaving the key out on insert).
>
> Maybe I'm a bit off on what you were asking, but it seems like you are
> expecting Cayenne to tell MySQL to generate the keys when it actually
> works the other way around: MySQL is supposed to know to generate the
> key and Cayenne then asks it for the key it generated.
>
> mrg
>
> PS. BTW, your settings are correct to use the auto-generated key
> feature in MySQL, just be sure the table was created to auto-generate
> on your "oid" column.
>
>
> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
>> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>>
>> Please refer to the attached CM configuration panel for an Entity.
>>
>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
>> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>>
>> Thanks
>> Joe
>>
>>
>>
Re: Elementary Question
Posted by Michael Gentry <mg...@masslight.net>.
Hi Joe,
Your wording on #2 and #3 was a little curious to me. I don't know if
you used Cayenne Modeler to generate your schema or if you are using
an existing schema. Cayenne (at runtime) doesn't really instruct the
database (MySQL) to auto-generate keys. Cayenne will nont provide a
value for "oid" when inserting the record and that essentially
instructs MySQL to auto-generate a key. You set this up when you
create your product table, for example. The database needs to know
that it is responsible for auto-generating the keys -- Cayenne won't
tell it at runtime (other than leaving the key out on insert).
Maybe I'm a bit off on what you were asking, but it seems like you are
expecting Cayenne to tell MySQL to generate the keys when it actually
works the other way around: MySQL is supposed to know to generate the
key and Cayenne then asks it for the key it generated.
mrg
PS. BTW, your settings are correct to use the auto-generated key
feature in MySQL, just be sure the table was created to auto-generate
on your "oid" column.
On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <jf...@earthlink.net> wrote:
> I apologize for this, but I am being forced to verify a configuration that is pretty obvious.
>
> Please refer to the attached CM configuration panel for an Entity.
>
> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". Please verify that this means that the PK Generation Strategy is Database Generated.
> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please verify that the field being auto-incremented is "oid".
> 3. Please verify that (with the DBMS set to MySQL) that the two previous parameters instruct MySQL to use its auto_increment feature to create a new "oid" value for inserted records.
>
> Thanks
> Joe
>
>
>