Flattened Relationships generating empty inserts into cross table

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Flattened Relationships generating empty inserts into cross table

Musall, Maik
Hi,

I have an m:n relationship that works fine on queries but generates an empty insert when adding entries.

Table 1: PDCUserRole
Table 2: PDCUserRight
x-Table: PDCUserRolePDCUserRight

Here are the relevant parts of my map.xml:

        <db-entity name="PDCUserRolePDCUserRight">
                <db-attribute name="userRightRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
                <db-attribute name="userRoleRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
        </db-entity>

        <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRight" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
                <db-attribute-pair source="userRightKey" target="userRightRef"/>
        </db-relationship>
        <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRole" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
                <db-attribute-pair source="userRoleKey" target="userRoleRef"/>
        </db-relationship>
        <db-relationship name="userRight" source="PDCUserRolePDCUserRight" target="PDCUserRight" toMany="false">
                <db-attribute-pair source="userRightRef" target="userRightKey"/>
        </db-relationship>
        <db-relationship name="userRole" source="PDCUserRolePDCUserRight" target="PDCUserRole" toMany="false">
                <db-attribute-pair source="userRoleRef" target="userRoleKey"/>
        </db-relationship>
       
        <obj-relationship name="userRoleArray" source="PDCUserRight" target="PDCUserRole" db-relationship-path="pcdUserRolePCDUserRightArray.userRole"/>
        <obj-relationship name="userRightArray" source="PDCUserRole" target="PDCUserRight" db-relationship-path="pcdUserRolePCDUserRightArray.userRight"/>

I then have an object "role" and an object "right" and want to create the relation. I'm essentially doing (through a wrapper method):

        role.addToManyTarget( USER_RIGHT_ARRAY_KEY, right, true );

which generates the following SQL:

SELECT t0.longDescription, t0.shortDescription, t0.sortKey, t0.string, t0.title, t0.value, t0.userRightKey FROM PDCUserRight t0 JOIN PDCUserRolePDCUserRight t1 ON (t0.userRightKey = t1.userRightRef) WHERE t1.userRoleRef = ? [bind: 1->userRoleRef:11]
INSERT INTO PDCUserRolePDCUserRight () VALUES ()

which of course produces: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"

I read these pages, is there anything more on this?
https://cayenne.apache.org/docs/3.0/flattened-relationships.html <https://cayenne.apache.org/docs/3.0/flattened-relationships.html>
https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html <https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html>

What could be wrong? I have the same type of problem on all my m:n relationships. I created this model by importing the old EOModel, but I also tried reengineering from database and comparing the two. No meaningful differences aside from character case and similar details.

Maik

Reply | Threaded
Open this post in threaded view
|

Re: Flattened Relationships generating empty inserts into cross table

Andrus Adamchik
Hi Maik,

> <db-attribute name="userRightRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
> <db-attribute name="userRoleRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>

I suspect 'isGenerated="true"'. Join table should not have auto-increment columns. (And we may need to do a better job detecting this condition in Cayenne).

Andrus


> On Feb 23, 2017, at 12:12 AM, Musall, Maik <[hidden email]> wrote:
>
> Hi,
>
> I have an m:n relationship that works fine on queries but generates an empty insert when adding entries.
>
> Table 1: PDCUserRole
> Table 2: PDCUserRight
> x-Table: PDCUserRolePDCUserRight
>
> Here are the relevant parts of my map.xml:
>
> <db-entity name="PDCUserRolePDCUserRight">
> <db-attribute name="userRightRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
> <db-attribute name="userRoleRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
> </db-entity>
>
> <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRight" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
> <db-attribute-pair source="userRightKey" target="userRightRef"/>
> </db-relationship>
> <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRole" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
> <db-attribute-pair source="userRoleKey" target="userRoleRef"/>
> </db-relationship>
> <db-relationship name="userRight" source="PDCUserRolePDCUserRight" target="PDCUserRight" toMany="false">
> <db-attribute-pair source="userRightRef" target="userRightKey"/>
> </db-relationship>
> <db-relationship name="userRole" source="PDCUserRolePDCUserRight" target="PDCUserRole" toMany="false">
> <db-attribute-pair source="userRoleRef" target="userRoleKey"/>
> </db-relationship>
>
> <obj-relationship name="userRoleArray" source="PDCUserRight" target="PDCUserRole" db-relationship-path="pcdUserRolePCDUserRightArray.userRole"/>
> <obj-relationship name="userRightArray" source="PDCUserRole" target="PDCUserRight" db-relationship-path="pcdUserRolePCDUserRightArray.userRight"/>
>
> I then have an object "role" and an object "right" and want to create the relation. I'm essentially doing (through a wrapper method):
>
> role.addToManyTarget( USER_RIGHT_ARRAY_KEY, right, true );
>
> which generates the following SQL:
>
> SELECT t0.longDescription, t0.shortDescription, t0.sortKey, t0.string, t0.title, t0.value, t0.userRightKey FROM PDCUserRight t0 JOIN PDCUserRolePDCUserRight t1 ON (t0.userRightKey = t1.userRightRef) WHERE t1.userRoleRef = ? [bind: 1->userRoleRef:11]
> INSERT INTO PDCUserRolePDCUserRight () VALUES ()
>
> which of course produces: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
>
> I read these pages, is there anything more on this?
> https://cayenne.apache.org/docs/3.0/flattened-relationships.html <https://cayenne.apache.org/docs/3.0/flattened-relationships.html>
> https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html <https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html>
>
> What could be wrong? I have the same type of problem on all my m:n relationships. I created this model by importing the old EOModel, but I also tried reengineering from database and comparing the two. No meaningful differences aside from character case and similar details.
>
> Maik
>

Reply | Threaded
Open this post in threaded view
|

Re: Flattened Relationships generating empty inserts into cross table

Musall, Maik
Hi Andrus,

bingo, that was it. Thanks!

Maik

> Am 02.03.2017 um 09:43 schrieb Andrus Adamchik <[hidden email]>:
>
> Hi Maik,
>
>> <db-attribute name="userRightRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
>> <db-attribute name="userRoleRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
>
> I suspect 'isGenerated="true"'. Join table should not have auto-increment columns. (And we may need to do a better job detecting this condition in Cayenne).
>
> Andrus
>
>
>> On Feb 23, 2017, at 12:12 AM, Musall, Maik <[hidden email]> wrote:
>>
>> Hi,
>>
>> I have an m:n relationship that works fine on queries but generates an empty insert when adding entries.
>>
>> Table 1: PDCUserRole
>> Table 2: PDCUserRight
>> x-Table: PDCUserRolePDCUserRight
>>
>> Here are the relevant parts of my map.xml:
>>
>> <db-entity name="PDCUserRolePDCUserRight">
>> <db-attribute name="userRightRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
>> <db-attribute name="userRoleRef" type="INTEGER" isPrimaryKey="true" isGenerated="true" isMandatory="true"/>
>> </db-entity>
>>
>> <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRight" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
>> <db-attribute-pair source="userRightKey" target="userRightRef"/>
>> </db-relationship>
>> <db-relationship name="pcdUserRolePCDUserRightArray" source="PDCUserRole" target="PDCUserRolePDCUserRight" toDependentPK="true" toMany="true">
>> <db-attribute-pair source="userRoleKey" target="userRoleRef"/>
>> </db-relationship>
>> <db-relationship name="userRight" source="PDCUserRolePDCUserRight" target="PDCUserRight" toMany="false">
>> <db-attribute-pair source="userRightRef" target="userRightKey"/>
>> </db-relationship>
>> <db-relationship name="userRole" source="PDCUserRolePDCUserRight" target="PDCUserRole" toMany="false">
>> <db-attribute-pair source="userRoleRef" target="userRoleKey"/>
>> </db-relationship>
>>
>> <obj-relationship name="userRoleArray" source="PDCUserRight" target="PDCUserRole" db-relationship-path="pcdUserRolePCDUserRightArray.userRole"/>
>> <obj-relationship name="userRightArray" source="PDCUserRole" target="PDCUserRight" db-relationship-path="pcdUserRolePCDUserRightArray.userRight"/>
>>
>> I then have an object "role" and an object "right" and want to create the relation. I'm essentially doing (through a wrapper method):
>>
>> role.addToManyTarget( USER_RIGHT_ARRAY_KEY, right, true );
>>
>> which generates the following SQL:
>>
>> SELECT t0.longDescription, t0.shortDescription, t0.sortKey, t0.string, t0.title, t0.value, t0.userRightKey FROM PDCUserRight t0 JOIN PDCUserRolePDCUserRight t1 ON (t0.userRightKey = t1.userRightRef) WHERE t1.userRoleRef = ? [bind: 1->userRoleRef:11]
>> INSERT INTO PDCUserRolePDCUserRight () VALUES ()
>>
>> which of course produces: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
>>
>> I read these pages, is there anything more on this?
>> https://cayenne.apache.org/docs/3.0/flattened-relationships.html <https://cayenne.apache.org/docs/3.0/flattened-relationships.html>
>> https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html <https://cayenne.apache.org/docs/3.0/cayennemodeler-flattened-relationships.html>
>>
>> What could be wrong? I have the same type of problem on all my m:n relationships. I created this model by importing the old EOModel, but I also tried reengineering from database and comparing the two. No meaningful differences aside from character case and similar details.
>>
>> Maik
>>
>