cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <mgen...@masslight.net>
Subject Re: Elementary Question
Date Tue, 20 Apr 2010 16:43:38 GMT
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 <jfbaldwin@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 <jfbaldwin@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 <jfbaldwin@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
>>>>>
>>>>>
>>>>>
>>>
>>>
>
>

Mime
View raw message