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 <jfbaldwi..arthlink.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 <jfbaldwi..arthlink.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 <jfbaldwi..arthlink.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
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
This archive was generated by hypermail 2.0.0 : Tue Apr 20 2010 - 13:14:10 EDT