Merge conflict Gerrit cannot merge this change. It needs a rebase onto main.

Review main Ready for review

93806[FEATURE] Auto-generate INT columns for type=group relationships

Benjamin Kott CR 0 V 0 Expert +1093 −38 updated 11/10/26, 11:46

Commit message

DefaultTcaSchema previously only auto-generated an INT column for
type=group fields with a manyToMany relationship. Fields storing a
single uid (manyToOne, oneToOne) still had to be declared manually in
ext_tables.sql, or ended up as TEXT columns.

Auto-generation is extended to manyToOne and oneToOne fields that
always store a plain integer: exactly one non-wildcard allowed table,
maxitems of at most 1 and no prepend_tname. oneToMany fields, fields
without an explicit relationship and multi-table fields are skipped, as
they store a comma-separated list or "tablename_uid" values. A
columnsOverrides on any record type must meet the same conditions, as
all record types share one physical column.

Existing installations get such TEXT columns changed to INT by the
database analyzer, felogin_redirectPid of fe_users and fe_groups
included. Two things made that change fail:

* Empty strings, and NULL values in a column that becomes NOT NULL,
  cannot be converted. A generic upgrade wizard sets them to the
  default value of the integer column, for every text column the
  schema migration is about to change to an integer column.
* PostgreSQL refuses to change a text column to an integer column
  without a USING clause, which Doctrine DBAL does not emit, and
  cannot cast a string default of the column. The TYPO3 PostgreSQL
  platforms now add the cast and replace the default.

The now-redundant SQL definitions are removed from core,
indexed_search, reactions and styleguide.

EXT:styleguide gains the field group_db_14 with a oneToOne
relationship, a configuration it did not show yet.

The ManyToMany DataScenarios gain a group field with MM relations to
more than one table and without MM_oppositeUsage, the configuration
of styleguide's group_db_13 nothing tested so far.

Test this change

Run a command in your clone of the TYPO3 Core. Then try what the change does and vote on Verified in Gerrit. How to review a patch

Check out patch set 15
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/06/93806/15 && git checkout FETCH_HEAD
Cherry-pick onto main
git fetch https://review.typo3.org/Packages/TYPO3.CMS refs/changes/06/93806/15 && git cherry-pick FETCH_HEAD

Review

  • Code review CR 0 Needs a vote
  • Verified V 0 Needs a vote
    • core-ci +1 CI

Issues 1

Files 31

Show 11 more files

Review log 3247

Show 1522 earlier messages
  1. Benjamin Kott uploaded patch set 1 04/22/26, 20:32

  2. core-ci voted on patch set 1 V -1 04/22/26, 20:46 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/100928

  3. Benjamin Kott uploaded patch set 2 V -1 04/23/26, 08:51

  4. core-ci voted on patch set 2 V -1 04/23/26, 09:10 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/101008

  5. Nikita Hovratov commented on patch set 2 04/24/26, 16:17

    I like further reducation of manual SQL in this area! What I'm thinking is: How do you store oneToMany as an integer? Type group stores them as a comma list as far as I know.
  6. Nikita Hovratov commented on patch set 2 04/24/26, 16:19

    Reply

    Also, we need to account for this pesky TCA option: https://docs.typo3.org/permalink/t3tca:confval-group-prepend-tname
  7. Benjamin Kott commented on patch set 2 CR -1 04/27/26, 06:12

    Reply

    good catch, will check later
  8. Benjamin Kott updated the commit message in patch set 3 CR -1 V -1 04/27/26, 07:46

  9. Benjamin Kott commented on patch set 3 04/27/26, 07:47

    i gave it a quick look with fresh eyes, we still have more to cover here in addition to the things nikita already pointed out. we have max item settings and also overwrites to consider on sub types. i will pause it for now here.
  10. Benjamin Kott marked it work in progress 04/27/26, 07:48

  11. core-ci voted on patch set 3 V -1 04/27/26, 07:59 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/101150

  12. Benni Mack uploaded patch set 4 V -1 09/14/26, 10:52

  13. core-ci voted on patch set 4 V -1 09/14/26, 11:07 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/108519

  14. Benni Mack uploaded patch set 5 V -1 09/14/26, 12:11

  15. Benni Mack commented on patch set 5 09/14/26, 12:13

    This now: - takes "prepend_tname" into account now - does not consider ONE_TO_MANY now - maxitems also considered - handles possible columnsOverrides options in this case as well (even though I don't know if we want this)
  16. core-ci voted on patch set 5 V -1 09/14/26, 12:23 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/108528

  17. Benni Mack uploaded patch set 6 V -1 09/25/26, 17:18

  18. core-ci voted on patch set 6 V -1 09/25/26, 17:28 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109281

  19. Benni Mack uploaded patch set 7 V -1 09/25/26, 18:01

  20. core-ci voted on patch set 7 V +1 09/25/26, 18:13 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109289

  21. Benni Mack uploaded patch set 8 V +1 09/30/26, 21:47

  22. Benni Mack commented on patch set 8 09/30/26, 21:47

    Reply

    covered now
  1. core-ci voted on patch set 8 V -1 09/30/26, 22:03 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/109724

  2. Benjamin Kott uploaded patch set 9 V -1 10/05/26, 11:05

  3. core-ci voted on patch set 9 V +1 10/05/26, 11:15 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/110105

  4. Benjamin Kott uploaded patch set 10 V +1 10/05/26, 11:21

  5. core-ci voted on patch set 10 V +1 10/05/26, 11:29 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/110107

  6. Benjamin Kott uploaded patch set 11 V +1 10/05/26, 11:46

  7. core-ci voted on patch set 11 V +1 10/05/26, 12:02 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/110111

  8. Benjamin Kott uploaded patch set 12 V +1 10/05/26, 14:11

  9. core-ci voted on patch set 12 V +1 10/05/26, 14:19 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/110121

  10. Benjamin Kott commented on patch set 12 10/06/26, 22:00

    This change is ready for review.
  11. Benni Mack voted on patch set 12 CR +1 V +1 10/07/26, 21:24

  12. Andreas Kienast commented on patch set 12 CR -1 10/10/26, 19:14

    TextToIntegerColumnMigration.php:139

    The wizard only catches '' and NULL, but a former TEXT column of a manyToOne group field may also hold values that are no plain integer, e.g. 12,13 or pages_12. That happens when the TCA had maxitems > 1 or more than one allowed table at some point before the relationship was set. I checked this locally with group_db_10 as a TEXT column holding 12,13 and pages_12. updateNecessary() returns false in every case, so the wizard reports nothing to do. Then: - PostgreSQL: the schema update fails with invalid input syntax for integer: "12,13" - MySQL 8 / MariaDB in strict mode: it fails with Data truncated for column 'group_db_10' / Truncated incorrect INTEGER value: '12,13' - MySQL 8 / MariaDB with an empty sql_mode: it succeeds without any error, and the values become 12 and 0. So pages_12 loses its reference completely and silently. The changelog says "Other non-numeric values have to be corrected manually", but nothing tells the integrator which rows are affected. They only find out when the analyzer throws, or not at all in non-strict mode. I suggest to address this in this change: 1. The wizard should detect rows with non-numeric values and list them in getDescription(), e.g. tx_foo.bar: 3 rows with non-numeric values, fix them manually, without changing them. 2. The changelog should state that MySQL/MariaDB without strict mode convert such values silently, e.g. pages_12 becomes 0.
  13. Benni Mack commented on patch set 12 10/11/26, 06:50

    TextToIntegerColumnMigration.php:139 Reply

    you mean prepend_tname is not handled? Because I had a prepatch dealing with this situation already, which I need to dive in again then.
  14. Benni Mack commented on patch set 12 CR 0 V 0 10/11/26, 08:02

    TextToIntegerColumnMigration.php:139 Reply

    ha, OK. Now I see, we don't know what "could have been" in a DB table field before. I didn't experience this in my test setup. So, I think the question is what to do in such cases? Keep them as TEXT to avoid data loss?
  15. Andreas Kienast commented on patch set 12 10/12/26, 05:35

    TextToIntegerColumnMigration.php:139 Reply

    IMO we should deny an automatic migration until all cases have been solved. That's where either the migration wizards comes in again to list these cases, and/or a separate report in EXT:reports (which is a non-required dependency, tho).
  16. Markus Klein commented on patch set 12 11/02/26, 22:19

    I didn't see this change unfortunately and created https://review.typo3.org/c/Packages/TYPO3.CMS/+/97299 today. For me this is a BUGFIX and not a feature. That was wrong from the very beginning. I'll compare the two patches, because mine is much smaller, but breaking.
  17. Markus Klein commented on patch set 12 11/02/26, 22:24

    TextToIntegerColumnMigration.php:87

    That does not make sense. A migration must happen BEFORE the type is migrated. So a DB migration MUST fail until the wizard has been run. See https://review.typo3.org/c/Packages/TYPO3.CMS/+/97299/4/typo3/sysext/core/Classes/Upgrades/AbstractGroupFieldMigration.php Which adds an extensible wizard for extensions.
  18. Markus Klein commented on patch set 12 11/02/26, 22:27

    TextToIntegerColumnMigration.php:139 Reply Unresolved

    IMO some "old value", based on some old TCA can be ignored. This is not even real data-loss, since a former multi-value now run through a single-value TCA config, will never reveal the additional old multi-values. So, casting the string to the first integer is totally fine for me.
  19. Markus Klein uploaded patch set 13 CR -1 V +1 11/10/26, 08:11

  20. core-ci voted on patch set 13 V -1 11/10/26, 08:21 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/113857

  21. Markus Klein uploaded patch set 14 V -1 11/10/26, 08:39

  22. core-ci voted on patch set 14 V +1 11/10/26, 08:49 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/113860

  23. Markus Klein uploaded patch set 15 V +1 11/10/26, 11:31

  24. Markus Klein commented on patch set 15 11/10/26, 11:33

    I merged my changes fomr the other review now here. Not much to do, luckily.

    TextToIntegerColumnMigration.php:87 Reply Unresolved

    I dropped my review now. Still it needs testing, whether this prerequisite is actually correct here.
  25. core-ci voted on patch set 15 V +1 11/10/26, 11:46 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/113890