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 - 12:33:14 EDT