You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by John Armstrong <si...@siberian.org> on 2010/03/28 08:43:08 UTC
AUTO_PK_SUPPORT, mysql and join tables
Hi all,
I am using mysql with all database generated pks and auto_increment.
This works fine for all tables except join tables. When accessing a join
table cayenne tries to access the AUTO_PK_TABLE rather then relying on the
auto_increment.
My join tables are configured identically :
<db-entity name="USERPROJECTS" catalog="pgi">
<db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
<db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
isGenerated="true" isMandatory="true" length="10"/>
<db-attribute name="PGUSER" type="INTEGER" length="10"/>
<db-attribute name="PROJECT" type="INTEGER" length="10"/>
</db-entity>
<obj-entity name="Userprojects" className="com.pgi.common.Userprojects"
dbEntityName="USERPROJECTS">
<obj-attribute name="creationdate" type="java.util.Date"
db-attribute-path="CREATIONDATE"/>
<obj-attribute name="id" type="java.lang.Integer" db-attribute-path="ID"/>
</obj-entity>
Any ideas?
John-
Re: AUTO_PK_SUPPORT, mysql and join tables
Posted by Andrus Adamchik <an...@objectstyle.org>.
Cool. Also I just committed the fix per https://issues.apache.org/jira/browse/CAY-1409
.
On Mar 28, 2010, at 10:06 PM, John Armstrong wrote:
> Thanks Andrus!
>
> I have worked around it for now by populating the AUTO_PK_TABLE as
> required
> so Cayenne can lock and unlock it (in case anyone else is having this
> problem).
>
> John-
>
> On Sun, Mar 28, 2010 at 12:00 PM, Andrus Adamchik <andrus@objectstyle.org
> >wrote:
>
>> I just wrote a unit test, and it looks like indeed in the case of a
>> flattened relationship with "Database-generated" PK, AUTO_PK_TABLE is
>> accessed, although after that Cayenne does the right thing and
>> builds a
>> query based on an auto-incremented PK, ignoring what's returned from
>> AUTO_PK_TABLE. So this is still a bug, albeit a minor one, not
>> affecting the
>> app correctness. I will open a bug report.
>>
>> Andrus
>>
>>
>>
>> On Mar 28, 2010, at 6:56 PM, John Armstrong wrote:
>>
>> Hi Andrus,
>>> I downgraded to 3.0M6 from RC2 a few weeks ago to troubleshoot some
>>> problems. I tested this in both M6 and RC2 with the same results.
>>>
>>> I have a few other join tables that are similar and exhibit the same
>>> behavior.
>>> Tx!
>>> J
>>>
>>> Tthe flattened relationship is defined as :
>>>
>>> <db-relationship name="userprojectsArray" source="PROJECT"
>>> target="USERPROJECTS" toMany="true">
>>> <db-attribute-pair source="ID" target="PROJECT"/>
>>> </db-relationship>
>>>
>>> <db-relationship name="userprojectsArray" source="USER"
>>> target="USERPROJECTS" toMany="true">
>>> <db-attribute-pair source="ID" target="PGUSER"/>
>>> </db-relationship>
>>>
>>> <db-relationship name="toProject" source="USERPROJECTS"
>>> target="PROJECT"
>>> toMany="false">
>>> <db-attribute-pair source="PROJECT" target="ID"/>
>>> </db-relationship>
>>> <db-relationship name="toUser" source="USERPROJECTS" target="USER"
>>> toMany="false">
>>> <db-attribute-pair source="PGUSER" target="ID"/>
>>>
>>>
>>> On Sun, Mar 28, 2010 at 7:36 AM, Andrus Adamchik <andrus@objectstyle.org
>>>> wrote:
>>>
>>> Odd. Join table with its own (non-propagated) PK should work the
>>> same as
>>>> any other table. Which vesrion of Cayenne is this and are you using
>>>> flattened relationships across the join table?
>>>>
>>>> Andrus
>>>>
>>>>
>>>> On Mar 28, 2010, at 9:43 AM, John Armstrong wrote:
>>>>
>>>> Hi all,
>>>>
>>>>> I am using mysql with all database generated pks and
>>>>> auto_increment.
>>>>>
>>>>> This works fine for all tables except join tables. When
>>>>> accessing a join
>>>>> table cayenne tries to access the AUTO_PK_TABLE rather then
>>>>> relying on
>>>>> the
>>>>> auto_increment.
>>>>>
>>>>> My join tables are configured identically :
>>>>>
>>>>> <db-entity name="USERPROJECTS" catalog="pgi">
>>>>> <db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
>>>>> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
>>>>> isGenerated="true" isMandatory="true" length="10"/>
>>>>> <db-attribute name="PGUSER" type="INTEGER" length="10"/>
>>>>> <db-attribute name="PROJECT" type="INTEGER" length="10"/>
>>>>> </db-entity>
>>>>> <obj-entity name="Userprojects"
>>>>> className="com.pgi.common.Userprojects"
>>>>> dbEntityName="USERPROJECTS">
>>>>> <obj-attribute name="creationdate" type="java.util.Date"
>>>>> db-attribute-path="CREATIONDATE"/>
>>>>> <obj-attribute name="id" type="java.lang.Integer"
>>>>> db-attribute-path="ID"/>
>>>>> </obj-entity>
>>>>>
>>>>> Any ideas?
>>>>>
>>>>> John-
>>>>>
>>>>>
>>>>
>>>>
>>
Re: AUTO_PK_SUPPORT, mysql and join tables
Posted by John Armstrong <si...@gmail.com>.
Thanks Andrus!
I have worked around it for now by populating the AUTO_PK_TABLE as required
so Cayenne can lock and unlock it (in case anyone else is having this
problem).
John-
On Sun, Mar 28, 2010 at 12:00 PM, Andrus Adamchik <an...@objectstyle.org>wrote:
> I just wrote a unit test, and it looks like indeed in the case of a
> flattened relationship with "Database-generated" PK, AUTO_PK_TABLE is
> accessed, although after that Cayenne does the right thing and builds a
> query based on an auto-incremented PK, ignoring what's returned from
> AUTO_PK_TABLE. So this is still a bug, albeit a minor one, not affecting the
> app correctness. I will open a bug report.
>
> Andrus
>
>
>
> On Mar 28, 2010, at 6:56 PM, John Armstrong wrote:
>
> Hi Andrus,
>> I downgraded to 3.0M6 from RC2 a few weeks ago to troubleshoot some
>> problems. I tested this in both M6 and RC2 with the same results.
>>
>> I have a few other join tables that are similar and exhibit the same
>> behavior.
>> Tx!
>> J
>>
>> Tthe flattened relationship is defined as :
>>
>> <db-relationship name="userprojectsArray" source="PROJECT"
>> target="USERPROJECTS" toMany="true">
>> <db-attribute-pair source="ID" target="PROJECT"/>
>> </db-relationship>
>>
>> <db-relationship name="userprojectsArray" source="USER"
>> target="USERPROJECTS" toMany="true">
>> <db-attribute-pair source="ID" target="PGUSER"/>
>> </db-relationship>
>>
>> <db-relationship name="toProject" source="USERPROJECTS" target="PROJECT"
>> toMany="false">
>> <db-attribute-pair source="PROJECT" target="ID"/>
>> </db-relationship>
>> <db-relationship name="toUser" source="USERPROJECTS" target="USER"
>> toMany="false">
>> <db-attribute-pair source="PGUSER" target="ID"/>
>>
>>
>> On Sun, Mar 28, 2010 at 7:36 AM, Andrus Adamchik <andrus@objectstyle.org
>> >wrote:
>>
>> Odd. Join table with its own (non-propagated) PK should work the same as
>>> any other table. Which vesrion of Cayenne is this and are you using
>>> flattened relationships across the join table?
>>>
>>> Andrus
>>>
>>>
>>> On Mar 28, 2010, at 9:43 AM, John Armstrong wrote:
>>>
>>> Hi all,
>>>
>>>> I am using mysql with all database generated pks and auto_increment.
>>>>
>>>> This works fine for all tables except join tables. When accessing a join
>>>> table cayenne tries to access the AUTO_PK_TABLE rather then relying on
>>>> the
>>>> auto_increment.
>>>>
>>>> My join tables are configured identically :
>>>>
>>>> <db-entity name="USERPROJECTS" catalog="pgi">
>>>> <db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
>>>> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
>>>> isGenerated="true" isMandatory="true" length="10"/>
>>>> <db-attribute name="PGUSER" type="INTEGER" length="10"/>
>>>> <db-attribute name="PROJECT" type="INTEGER" length="10"/>
>>>> </db-entity>
>>>> <obj-entity name="Userprojects" className="com.pgi.common.Userprojects"
>>>> dbEntityName="USERPROJECTS">
>>>> <obj-attribute name="creationdate" type="java.util.Date"
>>>> db-attribute-path="CREATIONDATE"/>
>>>> <obj-attribute name="id" type="java.lang.Integer"
>>>> db-attribute-path="ID"/>
>>>> </obj-entity>
>>>>
>>>> Any ideas?
>>>>
>>>> John-
>>>>
>>>>
>>>
>>>
>
Re: AUTO_PK_SUPPORT, mysql and join tables
Posted by Andrus Adamchik <an...@objectstyle.org>.
I just wrote a unit test, and it looks like indeed in the case of a
flattened relationship with "Database-generated" PK, AUTO_PK_TABLE is
accessed, although after that Cayenne does the right thing and builds
a query based on an auto-incremented PK, ignoring what's returned from
AUTO_PK_TABLE. So this is still a bug, albeit a minor one, not
affecting the app correctness. I will open a bug report.
Andrus
On Mar 28, 2010, at 6:56 PM, John Armstrong wrote:
> Hi Andrus,
> I downgraded to 3.0M6 from RC2 a few weeks ago to troubleshoot some
> problems. I tested this in both M6 and RC2 with the same results.
>
> I have a few other join tables that are similar and exhibit the same
> behavior.
> Tx!
> J
>
> Tthe flattened relationship is defined as :
>
> <db-relationship name="userprojectsArray" source="PROJECT"
> target="USERPROJECTS" toMany="true">
> <db-attribute-pair source="ID" target="PROJECT"/>
> </db-relationship>
>
> <db-relationship name="userprojectsArray" source="USER"
> target="USERPROJECTS" toMany="true">
> <db-attribute-pair source="ID" target="PGUSER"/>
> </db-relationship>
>
> <db-relationship name="toProject" source="USERPROJECTS"
> target="PROJECT"
> toMany="false">
> <db-attribute-pair source="PROJECT" target="ID"/>
> </db-relationship>
> <db-relationship name="toUser" source="USERPROJECTS" target="USER"
> toMany="false">
> <db-attribute-pair source="PGUSER" target="ID"/>
>
>
> On Sun, Mar 28, 2010 at 7:36 AM, Andrus Adamchik <andrus@objectstyle.org
> >wrote:
>
>> Odd. Join table with its own (non-propagated) PK should work the
>> same as
>> any other table. Which vesrion of Cayenne is this and are you using
>> flattened relationships across the join table?
>>
>> Andrus
>>
>>
>> On Mar 28, 2010, at 9:43 AM, John Armstrong wrote:
>>
>> Hi all,
>>> I am using mysql with all database generated pks and auto_increment.
>>>
>>> This works fine for all tables except join tables. When accessing
>>> a join
>>> table cayenne tries to access the AUTO_PK_TABLE rather then
>>> relying on the
>>> auto_increment.
>>>
>>> My join tables are configured identically :
>>>
>>> <db-entity name="USERPROJECTS" catalog="pgi">
>>> <db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
>>> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
>>> isGenerated="true" isMandatory="true" length="10"/>
>>> <db-attribute name="PGUSER" type="INTEGER" length="10"/>
>>> <db-attribute name="PROJECT" type="INTEGER" length="10"/>
>>> </db-entity>
>>> <obj-entity name="Userprojects"
>>> className="com.pgi.common.Userprojects"
>>> dbEntityName="USERPROJECTS">
>>> <obj-attribute name="creationdate" type="java.util.Date"
>>> db-attribute-path="CREATIONDATE"/>
>>> <obj-attribute name="id" type="java.lang.Integer" db-attribute-
>>> path="ID"/>
>>> </obj-entity>
>>>
>>> Any ideas?
>>>
>>> John-
>>>
>>
>>
Re: AUTO_PK_SUPPORT, mysql and join tables
Posted by John Armstrong <si...@gmail.com>.
Hi Andrus,
I downgraded to 3.0M6 from RC2 a few weeks ago to troubleshoot some
problems. I tested this in both M6 and RC2 with the same results.
I have a few other join tables that are similar and exhibit the same
behavior.
Tx!
J
Tthe flattened relationship is defined as :
<db-relationship name="userprojectsArray" source="PROJECT"
target="USERPROJECTS" toMany="true">
<db-attribute-pair source="ID" target="PROJECT"/>
</db-relationship>
<db-relationship name="userprojectsArray" source="USER"
target="USERPROJECTS" toMany="true">
<db-attribute-pair source="ID" target="PGUSER"/>
</db-relationship>
<db-relationship name="toProject" source="USERPROJECTS" target="PROJECT"
toMany="false">
<db-attribute-pair source="PROJECT" target="ID"/>
</db-relationship>
<db-relationship name="toUser" source="USERPROJECTS" target="USER"
toMany="false">
<db-attribute-pair source="PGUSER" target="ID"/>
On Sun, Mar 28, 2010 at 7:36 AM, Andrus Adamchik <an...@objectstyle.org>wrote:
> Odd. Join table with its own (non-propagated) PK should work the same as
> any other table. Which vesrion of Cayenne is this and are you using
> flattened relationships across the join table?
>
> Andrus
>
>
> On Mar 28, 2010, at 9:43 AM, John Armstrong wrote:
>
> Hi all,
>> I am using mysql with all database generated pks and auto_increment.
>>
>> This works fine for all tables except join tables. When accessing a join
>> table cayenne tries to access the AUTO_PK_TABLE rather then relying on the
>> auto_increment.
>>
>> My join tables are configured identically :
>>
>> <db-entity name="USERPROJECTS" catalog="pgi">
>> <db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
>> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
>> isGenerated="true" isMandatory="true" length="10"/>
>> <db-attribute name="PGUSER" type="INTEGER" length="10"/>
>> <db-attribute name="PROJECT" type="INTEGER" length="10"/>
>> </db-entity>
>> <obj-entity name="Userprojects" className="com.pgi.common.Userprojects"
>> dbEntityName="USERPROJECTS">
>> <obj-attribute name="creationdate" type="java.util.Date"
>> db-attribute-path="CREATIONDATE"/>
>> <obj-attribute name="id" type="java.lang.Integer" db-attribute-path="ID"/>
>> </obj-entity>
>>
>> Any ideas?
>>
>> John-
>>
>
>
Re: AUTO_PK_SUPPORT, mysql and join tables
Posted by Andrus Adamchik <an...@objectstyle.org>.
Odd. Join table with its own (non-propagated) PK should work the same
as any other table. Which vesrion of Cayenne is this and are you using
flattened relationships across the join table?
Andrus
On Mar 28, 2010, at 9:43 AM, John Armstrong wrote:
> Hi all,
> I am using mysql with all database generated pks and auto_increment.
>
> This works fine for all tables except join tables. When accessing a
> join
> table cayenne tries to access the AUTO_PK_TABLE rather then relying
> on the
> auto_increment.
>
> My join tables are configured identically :
>
> <db-entity name="USERPROJECTS" catalog="pgi">
> <db-attribute name="CREATIONDATE" type="TIMESTAMP" length="19"/>
> <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
> isGenerated="true" isMandatory="true" length="10"/>
> <db-attribute name="PGUSER" type="INTEGER" length="10"/>
> <db-attribute name="PROJECT" type="INTEGER" length="10"/>
> </db-entity>
> <obj-entity name="Userprojects"
> className="com.pgi.common.Userprojects"
> dbEntityName="USERPROJECTS">
> <obj-attribute name="creationdate" type="java.util.Date"
> db-attribute-path="CREATIONDATE"/>
> <obj-attribute name="id" type="java.lang.Integer" db-attribute-
> path="ID"/>
> </obj-entity>
>
> Any ideas?
>
> John-