You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@grails.apache.org by "borinquenkid (via GitHub)" <gi...@apache.org> on 2026/10/06 14:02:35 UTC

[PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

borinquenkid opened a new pull request, #16539:
URL: https://github.com/apache/grails-core/pull/16539

   ## Problem
   For a foreign key into an entity whose composite identifier contains a to-one to **another composite entity** (a nested composite chain), the generated foreign key columns are named in the nested entity's *mapped* order, while Hibernate sorts the nested component's properties by name and gives the referenced key's columns that order. The types follow positionally, so names and types are swapped relative to each other. Example (`Leaf` -> `Middle` -> `GrandParent`, where `GrandParent` has `composite('name', 'luckyNumber')`, a String and an Integer):
   
   | column of `leaf` | type before | type after |
   |---|---|---|
   | `cfk_middle_grand_parent_name` | `INTEGER` | `VARCHAR` |
   | `cfk_middle_grand_parent_lucky_number` | `VARCHAR` | `INTEGER` |
   
   It does **not** occur with a single level (a child pointing at a composite parent), where `sortOrIndexForeignKeyColumns` already aligns the columns.
   
   ## Impact
   Cosmetic. Positionally the columns still line up with the primary key, so save and reload work (verified through the GORM API before and after). The wrong names and types are visible to schema diff tools, `hibernate.hbm2ddl.auto=validate` and native SQL.
   
   ## Fix
   `CompositeIdentifierToManyToOneBinder.tryExpandNestedComposite` now sorts the nested parts by property name when expanding, matching Hibernate's order.
   
   ## Tests
   New `CompositeForeignKeyColumnTypesSpec` (4 features): the one-level DDL, the three-level DDL (fails before the fix), and runtime save/reload for both. `CompositeIdentifierToManyToOneBinderSpec`'s nested mock now declares its parts in reverse name order so it pins the sorting. `cfg.*`, `grails.gorm.tests.*` and `*Composite*`: 1930 tests, 0 failures, 3 skipped.
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4225225207


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeIdentifierToManyToOneBinder.java:
##########
@@ -126,7 +127,10 @@ private Optional<Stream<ColumnConfig>> tryExpandNestedComposite(
         if (nestedComposite == null) {
             return Optional.empty();
         }
+        // Hibernate sorts the properties of a composite identifier by name, and the columns this key
+        // references follow that order, so the foreign key columns are named in the same order
         return Optional.of(Arrays.stream(nestedComposite)
+                .sorted(Comparator.comparing(HibernatePersistentProperty::getName))

Review Comment:
   You're right, the property permutation only fit one-column parts. `sortOrIndexForeignKeyColumns` and `getReferencedIdentifierColumns` now move each part with its whole column span. Your PrbGrand/PrbMiddle/PrbLeaf case, plus a three-column nested part between two plain parts, are in `CompositeForeignKeyColumnTypesSpec` with the key-order pairs and a save and reload. I also removed the unused `Transactional` import and updated the PR description.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "testlens-app[bot] (via GitHub)" <gi...@apache.org>.
testlens-app[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6072430999

   ## ✅ All tests passed ✅
   
   🏷️ Commit: 2d921e99dd1b6ebea54453ac8de0aded5533f5b0
   ▶️ Tests:  96471 executed
   ⚪️ Checks: 89/89 completed
   
   ---
   _Learn more about TestLens at [testlens.app/docs](https://testlens.app/docs/features/pr-comment/)._
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4231960367


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyRegistrationOrderSpec.groovy:
##########
@@ -0,0 +1,128 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.Specification
+import spock.lang.Unroll
+
+/**
+ * An application registers its entities in class-path scan order, so an entity is often bound before
+ * the entity its composite foreign key references. The key must still be aligned with the referenced
+ * identifier once that identifier is bound, exactly as when the referenced entity is registered first.
+ * The entities are the ones of {@link CompositeForeignKeyColumnTypesSpec}, which registers every
+ * referenced entity first.
+ */
+class CompositeForeignKeyRegistrationOrderSpec extends Specification {
+
+    private static final List<List<String>> LEAF_KEY = [
+            ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+            ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+            ['prb_middle_name', 'name']]
+
+    private static final List<List<String>> HUB_KEY = [
+            ['prb_hub_ace', 'ace'],
+            ['prb_hub_grand_alpha', 'prb_grand_alpha'],
+            ['prb_hub_grand_zeta', 'prb_grand_zeta'],
+            ['prb_hub_zed', 'zed']]
+
+    private static Map<String, Object> config(List<Class> classes) {
+        [
+                'dataSource.url'        : "jdbc:h2:mem:compositeFkOrder${classes*.simpleName.join('')};LOCK_TIMEOUT=10000".toString(),
+                'dataSource.dbCreate'   : 'create-drop',
+                'dataSource.dialect'    : H2Dialect.name,
+                'hibernate.hbm2ddl.auto': 'create',
+        ]
+    }
+
+    @Unroll
+    void "the keys of a three level composite chain registered as #order are aligned with the keys they reference"() {
+        given:
+        HibernateDatastore datastore = new HibernateDatastore(config(classes), classes as Class[])
+
+        when:
+        Map<String, List<List<String>>> middleKeys = ForeignKeyPairs.of(datastore, 'PRB_MIDDLE')
+        Map<String, List<List<String>>> leafKeys = ForeignKeyPairs.of(datastore, 'PRB_LEAF')
+
+        then:
+        middleKeys == [prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]]
+        leafKeys == [prb_middle: LEAF_KEY]
+
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'

Review Comment:
   Filed as #16565.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jamesfredley (via GitHub)" <gi...@apache.org>.
jamesfredley commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4215266925


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,179 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import grails.gorm.transactions.Transactional
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }

Review Comment:
   The spec now reads the imported foreign keys in KEY_SEQ order and compares those column pairs, so a lookup by column name can no longer hide a reversed sequence.



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/CompositeIdentifierToManyToOneBinderSpec.groovy:
##########
@@ -83,7 +83,10 @@ class CompositeIdentifierToManyToOneBinderSpec extends Specification {
 
         def nestedPartA = Mock(HibernatePersistentProperty)
         def nestedPartB = Mock(HibernatePersistentProperty)
-        def perArray = [nestedPartA, nestedPartB] as HibernatePersistentProperty[]
+        nestedPartA.name >> "partA"
+        nestedPartB.name >> "partB"
+        // mapped in the reverse of the name order: the columns must still follow the sorted order of the referenced key
+        def perArray = [nestedPartB, nestedPartA] as HibernatePersistentProperty[]

Review Comment:
   The ordering claim now comes from a real mapping whose identifier parts are declared zeta then alpha. The schema check requires alpha before zeta, including the nested chain.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4223901954


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeIdentifierToManyToOneBinder.java:
##########
@@ -126,7 +127,10 @@ private Optional<Stream<ColumnConfig>> tryExpandNestedComposite(
         if (nestedComposite == null) {
             return Optional.empty();
         }
+        // Hibernate sorts the properties of a composite identifier by name, and the columns this key
+        // references follow that order, so the foreign key columns are named in the same order
         return Optional.of(Arrays.stream(nestedComposite)
+                .sorted(Comparator.comparing(HibernatePersistentProperty::getName))

Review Comment:
   Yes, I'll fix it in this PR. Thanks for the reproduction and the sketch.
   
   I'll add your PrbGrand/PrbMiddle/PrbLeaf case as a Hibernate-backed spec in `CompositeForeignKeyColumnTypesSpec`, with the `KEY_SEQ` assertion plus a save and reload. The fix applies the permutation across each part's column span in both `sortOrIndexForeignKeyColumns` and `getReferencedIdentifierColumns`. I'll also remove the unused `Transactional` import and update the description.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "testlens-app[bot] (via GitHub)" <gi...@apache.org>.
testlens-app[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6019898753

   ## ✅ All tests passed ✅
   
   🏷️ Commit: 5d05ec05edd89cdbd623bdcdc1198c705bdafcf2
   ▶️ Tests:  94185 executed
   ⚪️ Checks: 86/86 completed
   
   ---
   _Learn more about TestLens at [testlens.app/docs](https://testlens.app/docs/features/pr-comment/)._
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4230837726


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,393 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf,
+            CfkOrdParent, CfkOrdChild, CfkOrdGrand, CfkOrdMiddle, CfkOrdLeaf,
+            PrbGrand, PrbMiddle, PrbLeaf, PrbSpanLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+
+    /**
+     * The foreign keys of a table, each as its column pairs [foreign key column, referenced column] in
+     * {@code KEY_SEQ} order, which is the order the database matches the columns of a key by.
+     */
+    private Map<String, List<List<String>>> foreignKeyPairs(String table) {
+        Map<String, List<List<String>>> keys = [:]
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.metaData.getImportedKeys(null, null, table).withCloseable { rs ->
+                    List<List> rows = []
+                    while (rs.next()) {
+                        rows << [rs.getString('FK_NAME'), rs.getInt('KEY_SEQ'),
+                                 rs.getString('PKTABLE_NAME').toLowerCase(),
+                                 rs.getString('FKCOLUMN_NAME').toLowerCase(), rs.getString('PKCOLUMN_NAME').toLowerCase()]
+                    }
+                    rows.sort { it[1] }.each { List row ->
+                        keys.get(row[0] + ' -> ' + row[2], []) << [row[3], row[4]]
+                    }
+                }
+            }
+        }
+        keys.collectEntries { String name, List<List<String>> pairs -> [(name.substring(name.indexOf(' -> ') + 4)): pairs] }
+    }
+
+    void "a foreign key to a composite parent pairs its columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_CHILD') == [
+                cfk_parent: [['cfk_parent_lucky_number', 'lucky_number'], ['cfk_parent_name', 'name']]
+        ]
+    }
+
+    void "a foreign key to a composite parent whose parts are declared out of name order pairs the same-typed parts in key order"() {
+        expect: 'the parts are declared as zeta, alpha, and the primary key and the foreign key both follow the name order'
+        foreignKeyPairs('CFK_ORD_CHILD') == [
+                cfk_ord_parent: [['cfk_ord_parent_alpha', 'alpha'], ['cfk_ord_parent_zeta', 'zeta']]
+        ]
+    }
+
+    void "the foreign keys of a three level composite chain pair their columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_MIDDLE') == [
+                cfk_grand_parent: [['cfk_grand_parent_lucky_number', 'lucky_number'], ['cfk_grand_parent_name', 'name']]
+        ]
+        foreignKeyPairs('CFK_LEAF') == [
+                cfk_middle: [
+                        ['cfk_middle_grand_parent_lucky_number', 'cfk_grand_parent_lucky_number'],
+                        ['cfk_middle_grand_parent_name', 'cfk_grand_parent_name'],
+                        ['cfk_middle_name', 'name']]
+        ]
+    }
+
+    void "the foreign keys of a three level chain with out of order, same-typed parts pair them in key order"() {
+        expect: 'the nested composite is declared as zeta, alpha, so only its order tells the columns apart'
+        foreignKeyPairs('CFK_ORD_MIDDLE') == [
+                cfk_ord_grand: [['cfk_ord_grand_alpha', 'alpha'], ['cfk_ord_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('CFK_ORD_LEAF') == [
+                cfk_ord_middle: [
+                        ['cfk_ord_middle_grand_parent_alpha', 'cfk_ord_grand_alpha'],
+                        ['cfk_ord_middle_grand_parent_zeta', 'cfk_ord_grand_zeta'],
+                        ['cfk_ord_middle_name', 'name']]
+        ]
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> middle = columns('CFK_MIDDLE').collectEntries { [(it[0]): it[1]] }
+
+        Map<String, String> leaf = columns('CFK_LEAF').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        grand.name
+        middle.cfk_grand_parent_name == grand.name
+        middle.cfk_grand_parent_lucky_number == grand.lucky_number
+        leaf.cfk_middle_grand_parent_name == grand.name
+        leaf.cfk_middle_grand_parent_lucky_number == grand.lucky_number
+    }
+
+    void "a leaf of a three level composite chain is saved, reloaded and found through the association"() {
+        when:
+        CfkGrandParent.withNewTransaction {
+            CfkGrandParent grand = new CfkGrandParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            CfkMiddle middle = new CfkMiddle(name: 'Bob', grandParent: grand).save(failOnError: true)
+            new CfkLeaf(name: 'Chuck', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkLeaf.withNewSession {
+            CfkLeaf leaf = CfkLeaf.findByName('Chuck')
+            leaf.middle.name == 'Bob' && leaf.middle.grandParent.luckyNumber == 7
+        }
+    }
+
+    void "a child referencing a composite parent is saved, reloaded and found through the association"() {
+        when:
+        CfkParent.withNewTransaction {
+            CfkParent parent = new CfkParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            new CfkChild(label: 'kid', parent: parent).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkChild.withNewSession {
+            CfkChild child = CfkChild.findByLabel('kid')
+            child.parent.name == 'Fred' && child.parent.luckyNumber == 7
+        }
+        CfkChild.withNewSession {
+            CfkChild.where { parent.name == 'Fred' && parent.luckyNumber == 7 }.count() == 1
+        }
+    }
+
+    void "a nested composite part that sorts after a plain part moves with all of its columns"() {
+        expect: 'the middle key is declared as name, grandParent, so grandParent spans the columns that sort before and after name'
+        foreignKeyPairs('PRB_MIDDLE') == [
+                prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('PRB_LEAF') == [
+                prb_middle: [
+                        ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+                        ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+                        ['prb_middle_name', 'name']]
+        ]
+    }
+
+    void "a leaf whose composite key nests a part declared after a plain part is saved and reloaded"() {
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'
+        }
+    }
+
+    void "a multi-column nested part between two plain parts keeps its columns together in key order"() {

Review Comment:
   Done in 289e98ace8: both features now use your PrbHub/PrbHubTag/PrbHubRef layout, asserting the KEY_SEQ pairs on PRB_HUB_REF and on the PRB_HUB_PRB_HUB_TAG join table plus a save and reload. With the plain property permutation the pairs come out crossed and the save fails. The Tests section of the description is updated.
   



##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   Done in 0b0be81a04: when the referenced identifier (or one nested in it) is bound later, the binder now defers the alignment to a second pass instead of marking the columns sorted early, and aligns pending parts on demand. A second cause surfaced on the way: the first pass created Hibernate's positional foreign key before the deferred pass could create the explicit one, so that is skipped for a many-to-one to a composite identifier. `CompositeForeignKeyRegistrationOrderSpec` boots the same classes in five orders, including `PrbGrand, PrbLeaf, PrbMiddle` and a middle before its grand parent; four of them failed before.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4213508013


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,179 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import grails.gorm.transactions.Transactional
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }

Review Comment:
   Done in 36ad658187: the spec now reads the foreign keys through JDBC `getImportedKeys` in `KEY_SEQ` order and asserts each foreign key column against its referenced primary key column, including `cfk_middle_name`, plus new same-typed, out-of-name-order parts. Removing the sort in `tryExpandNestedComposite` makes it fail with the zeta and alpha columns crossed.
   



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/CompositeIdentifierToManyToOneBinderSpec.groovy:
##########
@@ -83,7 +83,10 @@ class CompositeIdentifierToManyToOneBinderSpec extends Specification {
 
         def nestedPartA = Mock(HibernatePersistentProperty)
         def nestedPartB = Mock(HibernatePersistentProperty)
-        def perArray = [nestedPartA, nestedPartB] as HibernatePersistentProperty[]
+        nestedPartA.name >> "partA"
+        nestedPartB.name >> "partB"
+        // mapped in the reverse of the name order: the columns must still follow the sorted order of the referenced key
+        def perArray = [nestedPartB, nestedPartA] as HibernatePersistentProperty[]

Review Comment:
   Done in 36ad658187: the ordering claim now rests on the Hibernate-backed spec (a real mapping with parts declared zeta, alpha, direct and nested). I removed the reversed array from the mock-based `CompositeIdentifierToManyToOneBinderSpec`, which no longer asserts ordering.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jdaugherty (via GitHub)" <gi...@apache.org>.
jdaugherty commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4230707432


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   Are you still planning to push the deferred sort in this PR? The branch is still at `2d921e99dd`.
   
   When you add it, the deferred path needs to apply `toColumnPermutation` itself. If the columns are only left unsorted, Hibernate's `ToOne.sortProperties()` sorts them later with the plain property permutation, which splits a multi-column part.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4226098699


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,393 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf,
+            CfkOrdParent, CfkOrdChild, CfkOrdGrand, CfkOrdMiddle, CfkOrdLeaf,
+            PrbGrand, PrbMiddle, PrbLeaf, PrbSpanLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+
+    /**
+     * The foreign keys of a table, each as its column pairs [foreign key column, referenced column] in
+     * {@code KEY_SEQ} order, which is the order the database matches the columns of a key by.
+     */
+    private Map<String, List<List<String>>> foreignKeyPairs(String table) {
+        Map<String, List<List<String>>> keys = [:]
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.metaData.getImportedKeys(null, null, table).withCloseable { rs ->
+                    List<List> rows = []
+                    while (rs.next()) {
+                        rows << [rs.getString('FK_NAME'), rs.getInt('KEY_SEQ'),
+                                 rs.getString('PKTABLE_NAME').toLowerCase(),
+                                 rs.getString('FKCOLUMN_NAME').toLowerCase(), rs.getString('PKCOLUMN_NAME').toLowerCase()]
+                    }
+                    rows.sort { it[1] }.each { List row ->
+                        keys.get(row[0] + ' -> ' + row[2], []) << [row[3], row[4]]
+                    }
+                }
+            }
+        }
+        keys.collectEntries { String name, List<List<String>> pairs -> [(name.substring(name.indexOf(' -> ') + 4)): pairs] }
+    }
+
+    void "a foreign key to a composite parent pairs its columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_CHILD') == [
+                cfk_parent: [['cfk_parent_lucky_number', 'lucky_number'], ['cfk_parent_name', 'name']]
+        ]
+    }
+
+    void "a foreign key to a composite parent whose parts are declared out of name order pairs the same-typed parts in key order"() {
+        expect: 'the parts are declared as zeta, alpha, and the primary key and the foreign key both follow the name order'
+        foreignKeyPairs('CFK_ORD_CHILD') == [
+                cfk_ord_parent: [['cfk_ord_parent_alpha', 'alpha'], ['cfk_ord_parent_zeta', 'zeta']]
+        ]
+    }
+
+    void "the foreign keys of a three level composite chain pair their columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_MIDDLE') == [
+                cfk_grand_parent: [['cfk_grand_parent_lucky_number', 'lucky_number'], ['cfk_grand_parent_name', 'name']]
+        ]
+        foreignKeyPairs('CFK_LEAF') == [
+                cfk_middle: [
+                        ['cfk_middle_grand_parent_lucky_number', 'cfk_grand_parent_lucky_number'],
+                        ['cfk_middle_grand_parent_name', 'cfk_grand_parent_name'],
+                        ['cfk_middle_name', 'name']]
+        ]
+    }
+
+    void "the foreign keys of a three level chain with out of order, same-typed parts pair them in key order"() {
+        expect: 'the nested composite is declared as zeta, alpha, so only its order tells the columns apart'
+        foreignKeyPairs('CFK_ORD_MIDDLE') == [
+                cfk_ord_grand: [['cfk_ord_grand_alpha', 'alpha'], ['cfk_ord_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('CFK_ORD_LEAF') == [
+                cfk_ord_middle: [
+                        ['cfk_ord_middle_grand_parent_alpha', 'cfk_ord_grand_alpha'],
+                        ['cfk_ord_middle_grand_parent_zeta', 'cfk_ord_grand_zeta'],
+                        ['cfk_ord_middle_name', 'name']]
+        ]
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> middle = columns('CFK_MIDDLE').collectEntries { [(it[0]): it[1]] }
+
+        Map<String, String> leaf = columns('CFK_LEAF').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        grand.name
+        middle.cfk_grand_parent_name == grand.name
+        middle.cfk_grand_parent_lucky_number == grand.lucky_number
+        leaf.cfk_middle_grand_parent_name == grand.name
+        leaf.cfk_middle_grand_parent_lucky_number == grand.lucky_number
+    }
+
+    void "a leaf of a three level composite chain is saved, reloaded and found through the association"() {
+        when:
+        CfkGrandParent.withNewTransaction {
+            CfkGrandParent grand = new CfkGrandParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            CfkMiddle middle = new CfkMiddle(name: 'Bob', grandParent: grand).save(failOnError: true)
+            new CfkLeaf(name: 'Chuck', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkLeaf.withNewSession {
+            CfkLeaf leaf = CfkLeaf.findByName('Chuck')
+            leaf.middle.name == 'Bob' && leaf.middle.grandParent.luckyNumber == 7
+        }
+    }
+
+    void "a child referencing a composite parent is saved, reloaded and found through the association"() {
+        when:
+        CfkParent.withNewTransaction {
+            CfkParent parent = new CfkParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            new CfkChild(label: 'kid', parent: parent).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkChild.withNewSession {
+            CfkChild child = CfkChild.findByLabel('kid')
+            child.parent.name == 'Fred' && child.parent.luckyNumber == 7
+        }
+        CfkChild.withNewSession {
+            CfkChild.where { parent.name == 'Fred' && parent.luckyNumber == 7 }.count() == 1
+        }
+    }
+
+    void "a nested composite part that sorts after a plain part moves with all of its columns"() {
+        expect: 'the middle key is declared as name, grandParent, so grandParent spans the columns that sort before and after name'
+        foreignKeyPairs('PRB_MIDDLE') == [
+                prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('PRB_LEAF') == [
+                prb_middle: [
+                        ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+                        ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+                        ['prb_middle_name', 'name']]
+        ]
+    }
+
+    void "a leaf whose composite key nests a part declared after a plain part is saved and reloaded"() {
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'
+        }
+    }
+
+    void "a multi-column nested part between two plain parts keeps its columns together in key order"() {

Review Comment:
   You're right: `PrbSpanLeaf`'s own key is laid out by Hibernate and nothing references it, so the permutation never runs there. I'll switch both features to your `PrbHub`/`PrbHubTag`/`PrbHubRef` layout (key pairs on `PRB_HUB_REF` and on the join table, plus a save and reload) and update the Tests section of the description.
   



##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   Agreed, that is a real bug and it belongs with this fix. I'll handle it in this PR: defer the sort until the referenced identifier is bound (not marking the columns sorted while the referenced entity is unbound), with a spec that registers the classes in alphabetical order (`PrbGrand, PrbLeaf, PrbMiddle`) and asserts the key pairs plus a save and reload, and the same for a middle registered before its grand parent.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "testlens-app[bot] (via GitHub)" <gi...@apache.org>.
testlens-app[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6086653058

   ## ✅ All tests passed ✅
   
   🏷️ Commit: d9018f61663337b0d0ef3b5eae39e037fdb5b7cb
   ▶️ Tests:  96623 executed
   ⚪️ Checks: 89/89 completed
   
   ---
   _Learn more about TestLens at [testlens.app/docs](https://testlens.app/docs/features/pr-comment/)._
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jamesfredley (via GitHub)" <gi...@apache.org>.
jamesfredley commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4210684577


##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,179 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import grails.gorm.transactions.Transactional
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }

Review Comment:
   `columns()` reads `ordinal_position`, then `collectEntries` throws that order away. The assertions check each column's name and type. That catches an integer paired with a string. It does not catch two same-type key parts written in the wrong order, which is the bug this change fixes.
   
   Please assert the foreign key's column pairs in `KEY_SEQ` order, including `cfk_middle_name`. Table-column ordinal order is not the key order.



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/CompositeIdentifierToManyToOneBinderSpec.groovy:
##########
@@ -83,7 +83,10 @@ class CompositeIdentifierToManyToOneBinderSpec extends Specification {
 
         def nestedPartA = Mock(HibernatePersistentProperty)
         def nestedPartB = Mock(HibernatePersistentProperty)
-        def perArray = [nestedPartA, nestedPartB] as HibernatePersistentProperty[]
+        nestedPartA.name >> "partA"
+        nestedPartB.name >> "partB"
+        // mapped in the reverse of the name order: the columns must still follow the sorted order of the referenced key
+        def perArray = [nestedPartB, nestedPartA] as HibernatePersistentProperty[]

Review Comment:
   Reversing this array is the ordering case, but the spec still calls `bindCompositeIdentifierToManyToOne()` directly and stubs `sortOrIndexForeignKeyColumns` and `getReferencedIdentifierColumns`. Those stubs are the behavior under test, so the column-name checks below do not show that a real mapping sorts the referenced key.
   
   Please cover the reversed declaration order with a Hibernate-backed spec and the ordered foreign-key metadata assertion, instead of this mock.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jdaugherty (via GitHub)" <gi...@apache.org>.
jdaugherty commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4230979723


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   0b0be81a04 fixes this for the to-one and join-table keys, but one order of the same three classes still fails:
   
   ```groovy
   new HibernateDatastore(config, PrbMiddle, PrbLeaf, PrbGrand)
   ```
   
   ```
   Referential integrity constraint violation: "FKDEN36EQGOPJY1BD3T41V9BTYS: PUBLIC.PRB_LEAF FOREIGN KEY(PRB_MIDDLE_GRAND_PARENT_ALPHA, PRB_MIDDLE_GRAND_PARENT_ZETA, PRB_MIDDLE_NAME) REFERENCES PUBLIC.PRB_MIDDLE(NAME, PRB_GRAND_ALPHA, PRB_GRAND_ZETA) ('a', 'z', 'm')"
   ```
   
   The foreign key that reaches the database here isn't the binder's. The inverse `PrbMiddle.leaves` key gets its own positional foreign key from `collection.createAllKeys()` (through `GrailsSecondPass.createCollectionKeys`), under the same name as the binder's explicit key for `PrbLeaf.middle`. Both keys are in the metadata in every order, and only the first one registered is created. The second fails with `Constraint "FKDEN36EQGOPJY1BD3T41V9BTYS" already exists`, which `CompositeForeignKeyRegistrationOrderSpec` logs on every run. In this order the collection's second pass is queued before the `CompositeForeignKeySecondPass` for `PrbLeaf.middle`, so the positional key is the one created. A bidirectional `hasMany` into the `PrbHub` layout fails the same way when registered as hub, child, grand.
   
   As a local check, I disabled the collection key's foreign key in `BidirectionalOneToManyLinker.link`, leaving the to-one side to create it. All six orders then save, and the duplicate-name errors go away. I only ran the composite specs with that change.
   
   Could you fix this order in this PR? `[PrbGrand, PrbMiddle, PrbLeaf].permutations()` in the `where:` block would cover all six orders.



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyRegistrationOrderSpec.groovy:
##########
@@ -0,0 +1,128 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.Specification
+import spock.lang.Unroll
+
+/**
+ * An application registers its entities in class-path scan order, so an entity is often bound before
+ * the entity its composite foreign key references. The key must still be aligned with the referenced
+ * identifier once that identifier is bound, exactly as when the referenced entity is registered first.
+ * The entities are the ones of {@link CompositeForeignKeyColumnTypesSpec}, which registers every
+ * referenced entity first.
+ */
+class CompositeForeignKeyRegistrationOrderSpec extends Specification {
+
+    private static final List<List<String>> LEAF_KEY = [
+            ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+            ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+            ['prb_middle_name', 'name']]
+
+    private static final List<List<String>> HUB_KEY = [
+            ['prb_hub_ace', 'ace'],
+            ['prb_hub_grand_alpha', 'prb_grand_alpha'],
+            ['prb_hub_grand_zeta', 'prb_grand_zeta'],
+            ['prb_hub_zed', 'zed']]
+
+    private static Map<String, Object> config(List<Class> classes) {
+        [
+                'dataSource.url'        : "jdbc:h2:mem:compositeFkOrder${classes*.simpleName.join('')};LOCK_TIMEOUT=10000".toString(),
+                'dataSource.dbCreate'   : 'create-drop',
+                'dataSource.dialect'    : H2Dialect.name,
+                'hibernate.hbm2ddl.auto': 'create',
+        ]
+    }
+
+    @Unroll
+    void "the keys of a three level composite chain registered as #order are aligned with the keys they reference"() {
+        given:
+        HibernateDatastore datastore = new HibernateDatastore(config(classes), classes as Class[])
+
+        when:
+        Map<String, List<List<String>>> middleKeys = ForeignKeyPairs.of(datastore, 'PRB_MIDDLE')
+        Map<String, List<List<String>>> leafKeys = ForeignKeyPairs.of(datastore, 'PRB_LEAF')
+
+        then:
+        middleKeys == [prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]]
+        leafKeys == [prb_middle: LEAF_KEY]
+
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'

Review Comment:
   This reload only follows `leaf.middle`. `grand.middles` and `middle.leaves` load empty here, in every registration order. This predates the PR and doesn't need nesting. On 8.0.x, a parent with `composite('zeta', 'alpha')` and `hasMany = [kids: Kid]`, where `Kid` `belongsTo` the parent, loads no kids. Declared as `composite('alpha', 'zeta')`, it loads them.
   
   `BidirectionalOneToManyLinker.link` copies the to-one's columns, which are already in key order. Then `key.sortProperties()` applies the owner identifier's permutation a second time. With the referenced entities registered first, `PrbMiddle.leaves` gets `prb_middle_grand_parent_zeta, prb_middle_grand_parent_alpha, prb_middle_name` while `PrbLeaf.middle` is `prb_middle_grand_parent_alpha, prb_middle_grand_parent_zeta, prb_middle_name`. So the collection is loaded with the identifier values bound to the wrong columns. I tried marking the copied key sorted instead of re-sorting it. The collections then load in every order except the two where the collection's second pass runs before the to-one's deferred alignment. There, the to-one would need aligning before it is copied.
   
   This is the same method as the foreign key in the other thread. Would you take it in this PR, or should we open a follow-up issue?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "testlens-app[bot] (via GitHub)" <gi...@apache.org>.
testlens-app[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6050118132

   ## 🚨 All tests passed but jobs failed 🚨
   
   ### Failed Jobs without Test Failures
   
   ❌ [End to End / End to End Tests \(end-to-end build only\)](https://github.com/apache/grails-core/actions/runs/37709682137/job/113092399971?pr=16539)
   
   🏷️ Commit: 36ad6581871f2afcdc03792c6af8935433e16ca2
   ▶️ Tests:  38699 executed
   🟡 Checks: 33/83 completed
   
   ### Rerun Controls
   
   Click the checkbox to trigger a rerun:
   
   🔲 **Rerun jobs**
   
   ---
   _Learn more about TestLens at [testlens.app/docs](https://testlens.app/docs/features/pr-comment/)._
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "codecov[bot] (via GitHub)" <gi...@apache.org>.
codecov[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6018833627

   ## [Codecov](https://app.codecov.io/gh/apache/grails-core/pull/16539?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache) Report
   :white_check_mark: All modified and coverable lines are covered by tests.
   :white_check_mark: Project coverage is 59.5429%. Comparing base ([`aa54db7`](https://app.codecov.io/gh/apache/grails-core/commit/aa54db705697582f726da4b48c02408a7ce3607b?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)) to head ([`5d05ec0`](https://app.codecov.io/gh/apache/grails-core/commit/5d05ec05edd89cdbd623bdcdc1198c705bdafcf2?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)).
   
   <details><summary>Additional details and impacted files</summary>
   
   
   
   [![Impacted file tree graph](https://app.codecov.io/gh/apache/grails-core/pull/16539/graphs/tree.svg?width=650&height=150&src=pr&token=qJrryhpk7u&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)](https://app.codecov.io/gh/apache/grails-core/pull/16539?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)
   
   ```diff
   @@                Coverage Diff                 @@
   ##                8.0.x     #16539        +/-   ##
   ==================================================
   + Coverage     59.5410%   59.5429%   +0.0018%     
   - Complexity      25020      25022         +2     
   ==================================================
     Files            2175       2175                
     Lines          108636     108636                
     Branches        19823      19823                
   ==================================================
   + Hits            64683      64685         +2     
   + Misses          34999      34998         -1     
   + Partials         8954       8953         -1     
   ```
   [see 4 files with indirect coverage changes](https://app.codecov.io/gh/apache/grails-core/pull/16539/indirect-changes?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=apache)
   </details>
   <details><summary> :rocket: New features to boost your workflow: </summary>
   
   - :snowflake: [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
   - :package: [JS Bundle Analysis](https://docs.codecov.com/docs/javascript-bundle-analysis): Save yourself from yourself by tracking and limiting bundle sizes in JS merges.
   </details>


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid merged PR #16539:
URL: https://github.com/apache/grails-core/pull/16539


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "testlens-app[bot] (via GitHub)" <gi...@apache.org>.
testlens-app[bot] commented on PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#issuecomment-6069729979

   ## ✅ All tests passed ✅
   
   🏷️ Commit: 567fb8be8f6181b688088531fee0b8464b36bf96
   ▶️ Tests:  96487 executed
   ⚪️ Checks: 89/89 completed
   
   ---
   _Learn more about TestLens at [testlens.app/docs](https://testlens.app/docs/features/pr-comment/)._
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "borinquenkid (via GitHub)" <gi...@apache.org>.
borinquenkid commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4231949089


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   Done in 8f46242382: `BidirectionalOneToManyLinker.link` disables the collection key's own foreign key, so the to-one side's key is the only one. For a simple identifier the two were already the same `ForeignKey`, so that DDL is unchanged, and a feature pins it. `CompositeForeignKeyRegistrationOrderSpec` now runs all 6 chain orders, all 24 hub orders and 6 orders of a bidirectional `PrbHubChild`, and asserts no duplicate foreign key name in the metadata. The full suite is green.
   



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyRegistrationOrderSpec.groovy:
##########
@@ -0,0 +1,128 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.Specification
+import spock.lang.Unroll
+
+/**
+ * An application registers its entities in class-path scan order, so an entity is often bound before
+ * the entity its composite foreign key references. The key must still be aligned with the referenced
+ * identifier once that identifier is bound, exactly as when the referenced entity is registered first.
+ * The entities are the ones of {@link CompositeForeignKeyColumnTypesSpec}, which registers every
+ * referenced entity first.
+ */
+class CompositeForeignKeyRegistrationOrderSpec extends Specification {
+
+    private static final List<List<String>> LEAF_KEY = [
+            ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+            ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+            ['prb_middle_name', 'name']]
+
+    private static final List<List<String>> HUB_KEY = [
+            ['prb_hub_ace', 'ace'],
+            ['prb_hub_grand_alpha', 'prb_grand_alpha'],
+            ['prb_hub_grand_zeta', 'prb_grand_zeta'],
+            ['prb_hub_zed', 'zed']]
+
+    private static Map<String, Object> config(List<Class> classes) {
+        [
+                'dataSource.url'        : "jdbc:h2:mem:compositeFkOrder${classes*.simpleName.join('')};LOCK_TIMEOUT=10000".toString(),
+                'dataSource.dbCreate'   : 'create-drop',
+                'dataSource.dialect'    : H2Dialect.name,
+                'hibernate.hbm2ddl.auto': 'create',
+        ]
+    }
+
+    @Unroll
+    void "the keys of a three level composite chain registered as #order are aligned with the keys they reference"() {
+        given:
+        HibernateDatastore datastore = new HibernateDatastore(config(classes), classes as Class[])
+
+        when:
+        Map<String, List<List<String>>> middleKeys = ForeignKeyPairs.of(datastore, 'PRB_MIDDLE')
+        Map<String, List<List<String>>> leafKeys = ForeignKeyPairs.of(datastore, 'PRB_LEAF')
+
+        then:
+        middleKeys == [prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]]
+        leafKeys == [prb_middle: LEAF_KEY]
+
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'

Review Comment:
   Agreed, that one is separate. It is pinned as a `@PendingFeature` in `InverseHasManyCompositeIdentifierOrderSpec` with your `composite('zeta', 'alpha')` repro (the name-order variant passes), and I'm opening a follow-up issue with your cause and what you tried.
   



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jdaugherty (via GitHub)" <gi...@apache.org>.
jdaugherty commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4225917979


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/hibernate/GrailsHibernatePersistentEntity.java:
##########
@@ -384,9 +385,10 @@ default boolean isLazy(HibernatePersistentProperty property) {
     default void sortOrIndexForeignKeyColumns(SimpleValue value) {
         PersistentClass pc = getPersistentClass();
         KeyValue identifier = pc != null ? pc.getIdentifier() : null;
-        int[] originalOrder = identifier instanceof Component c ? c.sortProperties() : null;
+        Component component = identifier instanceof Component c ? c : null;
+        int[] originalOrder = component != null ? component.sortProperties() : null;
         if (originalOrder != null) {
-            value.sortColumns(originalOrder);
+            value.sortColumns(toColumnPermutation(originalOrder, partColumnSpans(component, originalOrder)));
         } else {

Review Comment:
   This branch runs when the referenced entity hasn't been bound yet. `getPersistentClass()` is still null, so the columns keep their declared order and only get sequential type indexes. `markValueSorted` then sets `sorted`, so Hibernate's `ToOne.sortProperties()` skips them later too. Whether a key gets sorted depends on whether the entity it references was registered first.
   
   The spec always registers the referenced entity first. Here are the same classes in alphabetical order:
   
   ```groovy
   new HibernateDatastore(config, PrbGrand, PrbLeaf, PrbMiddle)
   ```
   
   The leaf's key columns stay `prb_middle_name, prb_middle_grand_parent_alpha, prb_middle_grand_parent_zeta` while the values are bound in key order, and saving a leaf fails:
   
   ```
   Referential integrity constraint violation: "FKDEN36EQGOPJY1BD3T41V9BTYS: PUBLIC.PRB_LEAF FOREIGN KEY(PRB_MIDDLE_NAME, PRB_MIDDLE_GRAND_PARENT_ALPHA, PRB_MIDDLE_GRAND_PARENT_ZETA) REFERENCES PUBLIC.PRB_MIDDLE(NAME, PRB_GRAND_ALPHA, PRB_GRAND_ZETA) ('a', 'z', 'm')"
   ```
   
   The middle's own key has the same problem when the middle is registered before its grand parent. Applications don't choose this order; their domain classes come from the class-path scan. Leaving `sorted` unset would not be enough on its own, because Hibernate's sort applies the property permutation and runs into the column-span problem this commit fixes.
   
   This predates the PR: 8.0.x fails the same way, with the columns crossed differently. Are you willing to handle it here, for example by deferring the sort until the referenced identifier is bound? Or would you rather we open a follow-up issue for it?



##########
grails-data-hibernate7/core/src/test/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeForeignKeyColumnTypesSpec.groovy:
##########
@@ -0,0 +1,393 @@
+/*
+ *  Licensed to the Apache Software Foundation (ASF) under one
+ *  or more contributor license agreements.  See the NOTICE file
+ *  distributed with this work for additional information
+ *  regarding copyright ownership.  The ASF licenses this file
+ *  to you under the Apache License, Version 2.0 (the
+ *  "License"); you may not use this file except in compliance
+ *  with the License.  You may obtain a copy of the License at
+ *
+ *    https://www.apache.org/licenses/LICENSE-2.0
+ *
+ *  Unless required by applicable law or agreed to in writing,
+ *  software distributed under the License is distributed on an
+ *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ *  KIND, either express or implied.  See the License for the
+ *  specific language governing permissions and limitations
+ *  under the License.
+ */
+
+
+package org.grails.orm.hibernate.cfg.domainbinding.binder
+
+import java.sql.Connection
+
+import grails.gorm.annotation.Entity
+import grails.gorm.hibernate.mapping.MappingBuilder
+import org.grails.orm.hibernate.HibernateDatastore
+import org.hibernate.dialect.H2Dialect
+import spock.lang.AutoCleanup
+import spock.lang.Shared
+import spock.lang.Specification
+
+/**
+ * A foreign key to an entity whose composite identifier has parts of different types must name
+ * and type each column after the referenced primary key column it points at.
+ */
+class CompositeForeignKeyColumnTypesSpec extends Specification {
+
+    @Shared
+    @AutoCleanup
+    HibernateDatastore datastore = new HibernateDatastore(
+            [
+                    'dataSource.url'      : 'jdbc:h2:mem:compositeFkTypesDB;LOCK_TIMEOUT=10000',
+                    'dataSource.dbCreate' : 'create-drop',
+                    'dataSource.dialect'  : H2Dialect.name,
+                    'hibernate.hbm2ddl.auto': 'create',
+            ],
+            CfkParent, CfkChild, CfkGrandParent, CfkMiddle, CfkLeaf,
+            CfkOrdParent, CfkOrdChild, CfkOrdGrand, CfkOrdMiddle, CfkOrdLeaf,
+            PrbGrand, PrbMiddle, PrbLeaf, PrbSpanLeaf)
+
+    private List<List> columns(String table) {
+        List<List> rows = []
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.createStatement().withCloseable { st ->
+                    st.executeQuery(
+                            "select column_name, data_type from information_schema.columns where table_name = '${table}' order by ordinal_position".toString())
+                            .withCloseable { rs ->
+                                while (rs.next()) {
+                                    rows << [rs.getString(1).toLowerCase(), rs.getString(2).toUpperCase()]
+                                }
+                            }
+                }
+            }
+        }
+        rows
+    }
+
+
+    /**
+     * The foreign keys of a table, each as its column pairs [foreign key column, referenced column] in
+     * {@code KEY_SEQ} order, which is the order the database matches the columns of a key by.
+     */
+    private Map<String, List<List<String>>> foreignKeyPairs(String table) {
+        Map<String, List<List<String>>> keys = [:]
+        datastore.sessionFactory.openSession().withCloseable { session ->
+            session.doWork { Connection c ->
+                c.metaData.getImportedKeys(null, null, table).withCloseable { rs ->
+                    List<List> rows = []
+                    while (rs.next()) {
+                        rows << [rs.getString('FK_NAME'), rs.getInt('KEY_SEQ'),
+                                 rs.getString('PKTABLE_NAME').toLowerCase(),
+                                 rs.getString('FKCOLUMN_NAME').toLowerCase(), rs.getString('PKCOLUMN_NAME').toLowerCase()]
+                    }
+                    rows.sort { it[1] }.each { List row ->
+                        keys.get(row[0] + ' -> ' + row[2], []) << [row[3], row[4]]
+                    }
+                }
+            }
+        }
+        keys.collectEntries { String name, List<List<String>> pairs -> [(name.substring(name.indexOf(' -> ') + 4)): pairs] }
+    }
+
+    void "a foreign key to a composite parent pairs its columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_CHILD') == [
+                cfk_parent: [['cfk_parent_lucky_number', 'lucky_number'], ['cfk_parent_name', 'name']]
+        ]
+    }
+
+    void "a foreign key to a composite parent whose parts are declared out of name order pairs the same-typed parts in key order"() {
+        expect: 'the parts are declared as zeta, alpha, and the primary key and the foreign key both follow the name order'
+        foreignKeyPairs('CFK_ORD_CHILD') == [
+                cfk_ord_parent: [['cfk_ord_parent_alpha', 'alpha'], ['cfk_ord_parent_zeta', 'zeta']]
+        ]
+    }
+
+    void "the foreign keys of a three level composite chain pair their columns with the referenced key columns in key order"() {
+        expect:
+        foreignKeyPairs('CFK_MIDDLE') == [
+                cfk_grand_parent: [['cfk_grand_parent_lucky_number', 'lucky_number'], ['cfk_grand_parent_name', 'name']]
+        ]
+        foreignKeyPairs('CFK_LEAF') == [
+                cfk_middle: [
+                        ['cfk_middle_grand_parent_lucky_number', 'cfk_grand_parent_lucky_number'],
+                        ['cfk_middle_grand_parent_name', 'cfk_grand_parent_name'],
+                        ['cfk_middle_name', 'name']]
+        ]
+    }
+
+    void "the foreign keys of a three level chain with out of order, same-typed parts pair them in key order"() {
+        expect: 'the nested composite is declared as zeta, alpha, so only its order tells the columns apart'
+        foreignKeyPairs('CFK_ORD_MIDDLE') == [
+                cfk_ord_grand: [['cfk_ord_grand_alpha', 'alpha'], ['cfk_ord_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('CFK_ORD_LEAF') == [
+                cfk_ord_middle: [
+                        ['cfk_ord_middle_grand_parent_alpha', 'cfk_ord_grand_alpha'],
+                        ['cfk_ord_middle_grand_parent_zeta', 'cfk_ord_grand_zeta'],
+                        ['cfk_ord_middle_name', 'name']]
+        ]
+    }
+
+    void "foreign key columns carry the type of the primary key column they are named after"() {
+        when:
+        Map<String, String> parent = columns('CFK_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> child = columns('CFK_CHILD').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        parent.name == 'CHARACTER VARYING'
+        parent.lucky_number == 'INTEGER'
+        child.cfk_parent_name == parent.name
+        child.cfk_parent_lucky_number == parent.lucky_number
+    }
+
+    void "foreign key columns of a three level composite chain are named and typed after the referenced key"() {
+        when:
+        Map<String, String> grand = columns('CFK_GRAND_PARENT').collectEntries { [(it[0]): it[1]] }
+        Map<String, String> middle = columns('CFK_MIDDLE').collectEntries { [(it[0]): it[1]] }
+
+        Map<String, String> leaf = columns('CFK_LEAF').collectEntries { [(it[0]): it[1]] }
+
+        then:
+        grand.name
+        middle.cfk_grand_parent_name == grand.name
+        middle.cfk_grand_parent_lucky_number == grand.lucky_number
+        leaf.cfk_middle_grand_parent_name == grand.name
+        leaf.cfk_middle_grand_parent_lucky_number == grand.lucky_number
+    }
+
+    void "a leaf of a three level composite chain is saved, reloaded and found through the association"() {
+        when:
+        CfkGrandParent.withNewTransaction {
+            CfkGrandParent grand = new CfkGrandParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            CfkMiddle middle = new CfkMiddle(name: 'Bob', grandParent: grand).save(failOnError: true)
+            new CfkLeaf(name: 'Chuck', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkLeaf.withNewSession {
+            CfkLeaf leaf = CfkLeaf.findByName('Chuck')
+            leaf.middle.name == 'Bob' && leaf.middle.grandParent.luckyNumber == 7
+        }
+    }
+
+    void "a child referencing a composite parent is saved, reloaded and found through the association"() {
+        when:
+        CfkParent.withNewTransaction {
+            CfkParent parent = new CfkParent(name: 'Fred', luckyNumber: 7).save(failOnError: true)
+            new CfkChild(label: 'kid', parent: parent).save(failOnError: true, flush: true)
+        }
+
+        then:
+        CfkChild.withNewSession {
+            CfkChild child = CfkChild.findByLabel('kid')
+            child.parent.name == 'Fred' && child.parent.luckyNumber == 7
+        }
+        CfkChild.withNewSession {
+            CfkChild.where { parent.name == 'Fred' && parent.luckyNumber == 7 }.count() == 1
+        }
+    }
+
+    void "a nested composite part that sorts after a plain part moves with all of its columns"() {
+        expect: 'the middle key is declared as name, grandParent, so grandParent spans the columns that sort before and after name'
+        foreignKeyPairs('PRB_MIDDLE') == [
+                prb_grand: [['prb_grand_alpha', 'alpha'], ['prb_grand_zeta', 'zeta']]
+        ]
+        foreignKeyPairs('PRB_LEAF') == [
+                prb_middle: [
+                        ['prb_middle_grand_parent_alpha', 'prb_grand_alpha'],
+                        ['prb_middle_grand_parent_zeta', 'prb_grand_zeta'],
+                        ['prb_middle_name', 'name']]
+        ]
+    }
+
+    void "a leaf whose composite key nests a part declared after a plain part is saved and reloaded"() {
+        when:
+        PrbGrand.withNewTransaction {
+            PrbGrand grand = new PrbGrand(zeta: 'z', alpha: 'a').save(failOnError: true)
+            PrbMiddle middle = new PrbMiddle(name: 'm', grandParent: grand).save(failOnError: true)
+            new PrbLeaf(name: 'l', middle: middle).save(failOnError: true, flush: true)
+        }
+
+        then:
+        PrbLeaf.withNewSession {
+            PrbLeaf leaf = PrbLeaf.findByName('l')
+            leaf.middle.name == 'm' && leaf.middle.grandParent.alpha == 'a' && leaf.middle.grandParent.zeta == 'z'
+        }
+    }
+
+    void "a multi-column nested part between two plain parts keeps its columns together in key order"() {

Review Comment:
   `PrbSpanLeaf`'s foreign key points at `PrbMiddle`, so these two features check the same key as the `PrbLeaf` ones. `composite('zed', 'middle', 'ace')` only lays out `PrbSpanLeaf`'s own primary key. Hibernate orders that key itself, and nothing references it, so the permutation for a multi-column part between two plain parts never runs here.
   
   An entity that references a key with that layout does exercise it:
   
   ```groovy
   @Entity
   class PrbHub implements Serializable {
       String zed
       String ace
       PrbGrand grand
       static hasMany = [tags: PrbHubTag]
   
       static mapping = MappingBuilder.define {
           composite('zed', 'grand', 'ace')
       }
   }
   
   @Entity
   class PrbHubTag {
       String label
   }
   
   @Entity
   class PrbHubRef {
       String name
       PrbHub hub
   }
   ```
   
   With this commit, the `PRB_HUB_REF` key pairs are `[prb_hub_ace, ace], [prb_hub_grand_alpha, prb_grand_alpha], [prb_hub_grand_zeta, prb_grand_zeta], [prb_hub_zed, zed]`. The `PRB_HUB_PRB_HUB_TAG` join table gets the same pairs, and a hub with a tag and a ref saves and reloads. On 8.0.x the pairs come out as `[prb_hub_grand_alpha, prb_grand_zeta], [prb_hub_grand_zeta, prb_grand_alpha], [prb_hub_zed, zed], [prb_hub_ace, ace]`, and the save fails with a referential integrity violation.
   
   Could these two features use this layout instead? The join table also covers the `CollectionWithJoinTableBinder` path. The Tests section of the description would need to change to match.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Hibernate 7: name nested composite foreign key columns in the referenced key's order [grails-core]

Posted by "jdaugherty (via GitHub)" <gi...@apache.org>.
jdaugherty commented on code in PR #16539:
URL: https://github.com/apache/grails-core/pull/16539#discussion_r4218417671


##########
grails-data-hibernate7/core/src/main/groovy/org/grails/orm/hibernate/cfg/domainbinding/binder/CompositeIdentifierToManyToOneBinder.java:
##########
@@ -126,7 +127,10 @@ private Optional<Stream<ColumnConfig>> tryExpandNestedComposite(
         if (nestedComposite == null) {
             return Optional.empty();
         }
+        // Hibernate sorts the properties of a composite identifier by name, and the columns this key
+        // references follow that order, so the foreign key columns are named in the same order
         return Optional.of(Arrays.stream(nestedComposite)
+                .sorted(Comparator.comparing(HibernatePersistentProperty::getName))

Review Comment:
   This orders the columns inside the nested part, but the parts themselves are still misordered when the nested to-one is declared after a part that sorts before it. `sortOrIndexForeignKeyColumns` passes the property permutation from `Component.sortProperties()` to `SimpleValue.sortColumns`, which applies it to the column list. That only lines up when every part has one column. `getReferencedIdentifierColumns` does the same through `sortedByPermutation`.
   
   To reproduce, declare the middle entity's nested part second:
   
   ```groovy
   @Entity
   class PrbGrand implements Serializable {
       String zeta
       String alpha
       static hasMany = [middles: PrbMiddle]
       static mapping = MappingBuilder.define { composite('zeta', 'alpha') }
   }
   
   @Entity
   class PrbMiddle implements Serializable {
       String name
       static belongsTo = [grandParent: PrbGrand]
       static hasMany = [leaves: PrbLeaf]
       static mapping = MappingBuilder.define { composite('name', 'grandParent') }
   }
   
   @Entity
   class PrbLeaf implements Serializable {
       String name
       static belongsTo = [middle: PrbMiddle]
       static mapping = MappingBuilder.define { composite('middle', 'name') }
   }
   ```
   
   The leaf's foreign key, in `KEY_SEQ` order, is `prb_middle_grand_parent_alpha, prb_middle_name, prb_middle_grand_parent_zeta`. Saving a leaf fails:
   
   ```
   Referential integrity constraint violation: "FKDEN36EQGOPJY1BD3T41V9BTYS: PUBLIC.PRB_LEAF FOREIGN KEY(PRB_MIDDLE_GRAND_PARENT_ALPHA, PRB_MIDDLE_NAME, PRB_MIDDLE_GRAND_PARENT_ZETA) REFERENCES PUBLIC.PRB_MIDDLE(PRB_GRAND_ALPHA, NAME, PRB_GRAND_ZETA) ('Z', 'A', 'M')"
   ```
   
   `aa54db7056` fails the same way, so this is not a regression. It is the same nested composite chain this PR fixes, though, and the new spec misses it because both middle entities declare `composite('grandParent', 'name')`, which is already in name order.
   
   I tried turning the property permutation into a column permutation, moving each part to its sorted position across its whole column span, in both `sortOrIndexForeignKeyColumns` and `getReferencedIdentifierColumns`. With that change the foreign key becomes `alpha, zeta, name`, the leaf saves and reloads, and the `*Composite*` specs still pass.
   
   Would you be willing to fix this in this PR? If so, a middle entity that declares `composite('name', 'grandParent')` in `CompositeForeignKeyColumnTypesSpec` would cover it, with the `KEY_SEQ` assertion and a save and reload.



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@grails.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org