Review main Merged

81604[FEATURE] Auto create DB fields from TCA for type "select"

Anja Leichsenring CR +2 V +2 Hard +580 −322 updated 12/19/23, 15:08

Commit message

Tables with TCA columns set to type="select" do not
need an ext_tables.sql entry anymore. The core now
creates this field automatically.

This is one of many patches in this area: The v13 goal
is to obsolete ext_tables.sql in most cases by
creating default "business" fields of TCA tables
automatically.

The patch adds the main magic in class DefaultTcaSchema
and removes own ext_tables.sql definitions having
type=select.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Garvin Hicking +1
    • Anja Leichsenring +1
    • Christian Kuhn +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Garvin Hicking +1
    • Anja Leichsenring +1
    • Christian Kuhn +1
    • core-ci +1 CI

Issues 2

Files 51

Show 31 more files

Review log 5173

Show 3248 earlier messages
  1. Anja Leichsenring uploaded patch set 1 10/28/23, 11:21

  2. core-ci voted on patch set 1 V -1 10/28/23, 11:31 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48324

  3. Chris Müller commented on patch set 1 10/28/23, 11:43

    Feature-101553-Auto-createDBFieldsFromTCAColumns.rst:70

    Please also add the "number" type which was missed with the last change in this area.
  4. Anja Leichsenring uploaded patch set 2 V -1 10/28/23, 12:06

  5. core-ci voted on patch set 2 V -1 10/28/23, 12:16 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48325

  6. Anja Leichsenring uploaded patch set 3 V -1 10/28/23, 12:26

  7. core-ci voted on patch set 3 V -1 10/28/23, 12:33 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48326

  8. Anja Leichsenring uploaded patch set 4 V -1 10/28/23, 12:49

  9. core-ci voted on patch set 4 V +1 10/28/23, 12:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48327

  10. Chris Müller commented on patch set 4 10/28/23, 13:52

    Feature-101553-Auto-createDBFieldsFromTCAColumns.rst:70 Reply

    Done
  11. Oliver Klee voted on patch set 4 V +1 10/28/23, 14:13

  12. Garvin Hicking commented on patch set 4 10/28/23, 14:13

    (thanks for the [more complicated] work, couldn't review proper, first "table read" looks very good!)

    DefaultTcaSchema.php:1103

    Totally minor: "save" ;)
  13. Anja Leichsenring commented on patch set 5 V +1 10/29/23, 07:43

    Published edit on patch set 4.
  14. Anja Leichsenring commented on patch set 5 10/29/23, 07:43

    DefaultTcaSchema.php:1103 Reply

    Done
  15. core-ci voted on patch set 5 V +1 10/29/23, 07:50 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48335

  16. Oliver Klee voted on patch set 5 V +1 10/29/23, 09:23

  17. Garvin Hicking commented on patch set 5 10/29/23, 13:38

    Many fileds change from varchar(xx) to a longtext - maybe a TCA option could be introduced (and set for the corefields) that would add a length indicator? It could also apply the other way round to not switch i.e. from TEXT to a lower VARCHAR(255) on a field like `be_groups.file_mountpoints`? A couple of tt_content varchar() fields are enlarged from 64 to 255, which takes up valuable place (row_size), so that could soften the impact? Also, a couple of fields are now lacking a "default" that had been defined earlier on a DB-level. Not sure if it is taken care of via DataHandler/FormEngine to insert proper default values? My TCA recommendation, split into two groups (I used "itemlength" for an example, a proper name and whether bytes are put into it, or if it shall be a enumerated list is of course up to discussion): # itemlength=3 (varchar(3) VS varchar(255)) * sys_file_metadata.unit # itemlength=4 (varchar(4) VS varchar(255)) * sys_file_metadata.color_space * tt_content.filelink_sorting_direction # itemlength=10 (varchar(10) VS longtext) * be_users.lang * pages.sitemap_changefreq # itemlength=64 (varchar(64) VS longtext) * pages.backend_layout * pages.backend_layout_next_level * tt_content.space_before_class * tt_content.space_after_class * tt_content.header_layout (see note) * tt_content.filelink_sorting * tt_content.category_field * tt_content.table_class * index_config.type (see note) # itemlength=255 (varchar(255) VS longtext) (would be too huge otherwise; could also be TINYTEXT?) * be_groups.allowed_languages * be_users.allowed_languages * index_config.table2index # itemlength=65535 (text vs. varchar(255)) (would be too small otherwise) * be_groups.file_mountpoints * be_groups.subgroup * be_users.usergroup * be_users.file_mountpoints * fe_groups.subgroup * fe_users.usergroup * sys_workspace.file_mountpoints # itemlength=[BIGINT] (bigint vs. int) * sys_file.size And here's a complete list of changes if it may help, with a few comments/reasoning for the above. (Format: first row new value, second row old value) # be_groups allowed_languages LONGTEXT DEFAULT NULL allowed_languages VARCHAR(255) DEFAULT '' NOT NULL file_mountpoints VARCHAR(255) DEFAULT '' NOT NULL file_mountpoints TEXT DEFAULT NULL * Less mountpoints now? subgroup VARCHAR(255) DEFAULT '' NOT NULL subgroup TEXT DEFAULT NULL * Fewer subgroups now? # be_users usergroup VARCHAR(255) DEFAULT '' NOT NULL usergroup TEXT DEFAULT NULL * Fewer assignable groups now? lang LONGTEXT DEFAULT NULL lang VARCHAR(10) DEFAULT 'default' NOT NULL * Much longer storage for a language now allowed_languages LONGTEXT DEFAULT NULL allowed_languages VARCHAR(255) DEFAULT '' NOT NULL * Too large? file_mountpoints VARCHAR(255) DEFAULT '' NOT NULL file_mountpoints TEXT DEFAULT NULL * Less mountpoints now? # pages backend_layout LONGTEXT DEFAULT NULL backend_layout VARCHAR(64) DEFAULT '' NOT NULL * Very long descriptors now? backend_layout_next_level LONGTEXT DEFAULT NULL backend_layout_next_level VARCHAR(64) DEFAULT '' NOT NULL * Very long descriptors now? sitemap_priority VARCHAR(255) DEFAULT '' NOT NULL sitemap_priority NUMERIC(2, 1) DEFAULT '0.5' NOT NULL * Missing a "0.5" default now? Numeric to char change should be ok due to SQLite. sitemap_changefreq VARCHAR(255) DEFAULT '' NOT NULL sitemap_changefreq VARCHAR(10) DEFAULT '' NOT NULL # sys_file_storage driver VARCHAR(255) DEFAULT '' NOT NULL driver TINYTEXT DEFAULT NULL # sys_file storage INT UNSIGNED DEFAULT 0 NOT NULL storage INT DEFAULT 0 NOT NULL type INT UNSIGNED DEFAULT 0 NOT NULL type VARCHAR(10) DEFAULT '' NOT NULL * Default not set to "0", could this be a problem? size INT DEFAULT 0 NOT NULL size BIGINT UNSIGNED DEFAULT 0 NOT NULL * Would no longer allow file sizes > 2GB; should at least be unsigned, but ideally remain bigint # sys_file_metadata file INT UNSIGNED DEFAULT 0 NOT NULL file INT DEFAULT 0 NOT NULL status INT UNSIGNED DEFAULT 0 NOT NULL status VARCHAR(24) DEFAULT '' ranking INT UNSIGNED DEFAULT 0 NOT NULL ranking INT UNSIGNED DEFAULT 0 unit VARCHAR(255) DEFAULT '' NOT NULL unit VARCHAR(3) DEFAULT '' duration INT DEFAULT 0 NOT NULL duration DOUBLE PRECISION DEFAULT '0' * Has been mentioned already, I think this is conceptually more sane to store. May lead to require a migration wizard though? color_space VARCHAR(255) DEFAULT '' NOT NULL color_space VARCHAR(4) DEFAULT '' # sys_file_collection type VARCHAR(255) DEFAULT '' NOT NULL type VARCHAR(30) DEFAULT 'static' NOT NULL * Missing the "static" default now? # fe_groups tx_extbase_type VARCHAR(255) DEFAULT '' NOT NULL tx_extbase_type VARCHAR(255) DEFAULT '0' NOT NULL * Missing "0" default now, could this be problematic? subgroup VARCHAR(255) DEFAULT '' NOT NULL subgroup TINYTEXT DEFAULT NULL # fe_users tx_extbase_type VARCHAR(255) DEFAULT '' NOT NULL tx_extbase_type VARCHAR(255) DEFAULT '0' NOT NULL * Missing "0" default now, could this be problematic? usergroup VARCHAR(255) DEFAULT '' NOT NULL usergroup TEXT DEFAULT NULL # sys_template static_file_mode INT UNSIGNED DEFAULT 0 NOT NULL static_file_mode SMALLINT UNSIGNED DEFAULT 0 NOT NULL # tt_content bullets_type INT UNSIGNED DEFAULT 0 NOT NULL bullets_type SMALLINT UNSIGNED DEFAULT 0 NOT NULL uploads_type INT UNSIGNED DEFAULT 0 NOT NULL uploads_type SMALLINT UNSIGNED DEFAULT 0 NOT NULL imageorient INT UNSIGNED DEFAULT 0 NOT NULL imageorient SMALLINT UNSIGNED DEFAULT 0 NOT NULL imagecols INT UNSIGNED DEFAULT 0 NOT NULL imagecols SMALLINT UNSIGNED DEFAULT 0 NOT NULL frame_class VARCHAR(255) DEFAULT '' NOT NULL frame_class VARCHAR(60) DEFAULT 'default' NOT NULL * Missing default "default" now? space_before_class VARCHAR(255) DEFAULT '' NOT NULL space_before_class VARCHAR(60) DEFAULT '' NOT NULL space_after_class VARCHAR(255) DEFAULT '' NOT NULL space_after_class VARCHAR(60) DEFAULT '' NOT NULL header_layout INT UNSIGNED DEFAULT 0 NOT NULL header_layout VARCHAR(30) DEFAULT '0' NOT NULL * Not sure if a change to INT could cause problems for extensions? Better leave at varchar? filelink_sorting VARCHAR(255) DEFAULT '' NOT NULL filelink_sorting VARCHAR(64) DEFAULT '' NOT NULL filelink_sorting_direction VARCHAR(255) DEFAULT '' NOT NULL filelink_sorting_direction VARCHAR(4) DEFAULT '' NOT NULL recursive INT UNSIGNED DEFAULT 0 NOT NULL recursive SMALLINT UNSIGNED DEFAULT 0 NOT NULL * Currently seems to only go to 250 (SMALLINT) but INT shouldn't hurt too much here. category_field LONGTEXT DEFAULT NULL category_field VARCHAR(64) DEFAULT '' NOT NULL table_class VARCHAR(255) DEFAULT '' NOT NULL table_class VARCHAR(60) DEFAULT '' NOT NULL table_delimiter INT UNSIGNED DEFAULT 0 NOT NULL table_delimiter SMALLINT UNSIGNED DEFAULT 0 NOT NULL table_enclosure INT UNSIGNED DEFAULT 0 NOT NULL table_enclosure SMALLINT UNSIGNED DEFAULT 0 NOT NULL table_header_position INT UNSIGNED DEFAULT 0 NOT NULL table_header_position SMALLINT UNSIGNED DEFAULT 0 NOT NULL # sys_redirect target_statuscode INT UNSIGNED DEFAULT 0 NOT NULL target_statuscode INT DEFAULT 307 NOT * Missing the "307" default now # index_config type INT UNSIGNED DEFAULT 0 NOT NULL type VARCHAR(30) DEFAULT '' NOT NULL * Unsure if extensions may extend upon this and might use char? table2index LONGTEXT DEFAULT NULL table2index VARCHAR(255) DEFAULT '' NOT NULL timer_frequency INT UNSIGNED DEFAULT 0 NOT NULL timer_frequency INT DEFAULT 0 NOT NULL # tx_extensionmanager_domain_model_extension state INT UNSIGNED DEFAULT 0 NOT NULL state INT DEFAULT 0 NOT NULL category INT UNSIGNED DEFAULT 0 NOT NULL category INT DEFAULT 0 NOT NULL # sys_note category INT UNSIGNED DEFAULT 0 NOT NULL category SMALLINT UNSIGNED DEFAULT 0 NOT NULL position INT UNSIGNED DEFAULT 0 NOT NULL position INT DEFAULT 0 NOT NULL * Could there be extensions making use of it and setting a negative position index (that would not be allowed?) # sys_workspace file_mountpoints VARCHAR(255) DEFAULT '' NOT NULL file_mountpoints TEXT DEFAULT NULL stagechg_notification INT UNSIGNED DEFAULT 0 NOT NULL stagechg_notification SMALLINT DEFAULT 0 NOT NULL
  18. Anja Leichsenring uploaded patch set 6 V +1 11/05/23, 14:49

  19. core-ci voted on patch set 6 V +1 11/05/23, 14:56 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48592

  20. Nikita Hovratov uploaded patch set 7 V +1 11/05/23, 16:57

  21. core-ci voted on patch set 7 V +1 11/05/23, 17:04 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48594

  22. Anja Leichsenring rebased onto patch set 8 V +1 11/26/23, 17:49

  23. core-ci voted on patch set 8 V -1 11/26/23, 17:56 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49951

  24. Anja Leichsenring uploaded patch set 9 V -1 11/28/23, 07:05

  25. core-ci voted on patch set 9 V +1 11/28/23, 07:10 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50043

  26. Anja Leichsenring uploaded patch set 10 V +1 11/29/23, 05:51

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

  28. Nikita Hovratov voted on patch set 10 CR +1 V +1 11/29/23, 08:49

  29. Oli Bartsch commented on patch set 10 11/29/23, 09:01

    TypoScriptFrontendControllerTest.php:229

    Isn't this a change in behaviour? Should we document this?
  30. Garvin Hicking commented on patch set 10 CR +1 V +1 11/29/23, 09:04

    To keep the scope of this patch within sane boundaries, my suggestions can be covered in a follow-up. Let's get this merged to improve upon it, great step forward, thank you!
  31. Oli Bartsch commented on patch set 10 CR -1 11/29/23, 09:10

    ext_tables.sql:5

    Why do we remove such character restrictions making db fields unnecessary big? - Applies also for `unit`, `color_space`. Additionally there might be other differences, see e.g. https://www.scaler.com/topics/varchar-vs-text-mysql/

    ext_tables.sql:19

    Are we sure behaviour does not change here?

    ext_tables.sql:10

    Should we keep this?

    ext_tables.sql:6

    Should we keep this?
  32. Oli Bartsch commented on patch set 10 11/29/23, 14:11

  33. Christian Kuhn commented on patch set 10 12/01/23, 09:37

    Well, some fields now become longtext, especially those with userFuncs. Some others are extended rather heavily from varchar(4) to varchar(255) and similar. I tend to agree to Oliver that may just be a bit too much. I think, we may need to introduce some 'dbFieldLength' (?) TCA setting to optionally set a specific length when a developer *knows* that a (non-int) field can extend a certain length? What do you think?

    DefaultTcaSchema.php:1060

    let's split that loop into two runs: one for $itemsContainsOnlyIntegers, one for $itemsAreAllPositive to always end up with clean state even if the first if() triggers a break.

    DefaultTcaSchema.php:1080

    isn't it possible to have both 'static' items plus relations? i remember something from select tree (even though it's mm). if so, we may want to check for foreign_table instead?

    ext_tables.sql:5

    this now becomes 'DEFAULT '' NOT NULL' ... probably not what we want?
  34. Anja Leichsenring uploaded patch set 11 CR +1 V +1 12/04/23, 09:26

  35. core-ci voted on patch set 11 V +1 12/04/23, 09:33 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50305

  36. Anja Leichsenring commented on patch set 11 CR -1 12/04/23, 09:34

    still on it

    DefaultTcaSchema.php:1060 Reply

    Done

    DefaultTcaSchema.php:1080 Reply

    I don't understand, let's talk please.
  37. Anja Leichsenring uploaded patch set 12 CR -1 V +1 12/04/23, 09:40

  38. core-ci voted on patch set 12 V +1 12/04/23, 09:48 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50307

  39. Anja Leichsenring uploaded patch set 13 V +1 12/04/23, 09:56

  40. Anja Leichsenring commented on patch set 13 12/04/23, 09:57

    TypoScriptFrontendControllerTest.php:229 Reply

    reverted, so no need to document anything
  41. core-ci voted on patch set 13 V +1 12/04/23, 10:01 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50308

  42. Anja Leichsenring commented on patch set 13 12/04/23, 10:24

    review ready now

    ext_tables.sql:5 Reply

    field length is respected, so issue is resolved

    ext_tables.sql:5 Reply

    field length is respected, so issue is resolved

    ext_tables.sql:19 Reply

    its varchar now, so no change in behaviour

    ext_tables.sql:10 Reply

    field length is respected, so issue is resolved

    ext_tables.sql:6 Reply

    field length is respected, so issue is resolved
  43. Anja Leichsenring uploaded patch set 14 V +1 12/04/23, 10:54

  44. core-ci voted on patch set 14 V +1 12/04/23, 10:59 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50317

  45. Anja Leichsenring rebased onto patch set 15 V +1 12/08/23, 07:41

  46. core-ci voted on patch set 15 V +1 12/08/23, 07:46 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50472

  47. Anja Leichsenring rebased onto patch set 16 V +1 12/15/23, 10:10

  48. core-ci voted on patch set 16 V +1 12/15/23, 10:17 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50674

  1. Anja Leichsenring rebased onto patch set 17 V +1 12/19/23, 07:36

  2. core-ci voted on patch set 17 V +1 12/19/23, 07:41 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50785

  3. Christian Kuhn commented on patch set 17 CR -1 12/19/23, 08:14

    on it.
  4. Christian Kuhn uploaded patch set 18 CR -1 V +1 12/19/23, 12:50

  5. Christian Kuhn commented on patch set 18 12/19/23, 12:51

    DefaultTcaSchema.php:1080 Reply

    edgy. and no problem here.
  6. core-ci voted on patch set 18 V -1 12/19/23, 12:57 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50809

  7. Christian Kuhn uploaded patch set 19 V -1 12/19/23, 13:08

  8. core-ci voted on patch set 19 V +1 12/19/23, 13:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50810

  9. Christian Kuhn uploaded patch set 20 V +1 12/19/23, 13:25

  10. core-ci voted on patch set 20 V +1 12/19/23, 13:30 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50811

  11. Christian Kuhn voted on patch set 20 CR +1 V +1 12/19/23, 13:32

  12. Stefan Bürk voted on patch set 20 CR -1 12/19/23, 13:56

  13. Anja Leichsenring commented on patch set 20 CR +1 V +1 12/19/23, 14:50

    thanks for the review and the changes, @lolli@schwarzbu.ch
  14. Nikita Hovratov commented on patch set 20 12/19/23, 14:55

    Feature-101553-Auto-createDBFieldsFromTCAColumns.rst:38

    who wish
  15. Stefan Bürk commented on patch set 21 CR +1 V +1 12/19/23, 15:01

    Published edit on patch set 20.
  16. Stefan Bürk commented on patch set 22 12/19/23, 15:02

    Published edit on patch set 21.
  17. Christian Kuhn voted on patch set 22 CR +1 V +1 12/19/23, 15:02

  18. Christian Kuhn commented on patch set 22 12/19/23, 15:02

    Feature-101553-Auto-createDBFieldsFromTCAColumns.rst:38 Reply

    Done
  19. Stefan Bürk commented on patch set 22 12/19/23, 15:02

    Feature-101553-Auto-createDBFieldsFromTCAColumns.rst:38 Reply

    Done
  20. Garvin Hicking commented on patch set 22 CR +1 V +1 12/19/23, 15:05

    Looking good. Thanks everyone for staying on it. (I believe this reveals we'll need to move (fe|be)_users.file_mountpoints and usersgroup soon to true MM releations; their restriction to a couple dozen mountpoints/usersgroups with smaller varchar fields shouldn't be necessary in the long run)
  21. Anja Leichsenring voted on patch set 22 CR +1 V +1 12/19/23, 15:06

  22. core-ci voted on patch set 21 V +1 12/19/23, 15:07 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50812

  23. core-ci voted on patch set 22 V +1 12/19/23, 15:08 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/50813

  24. Stefan Bürk voted on patch set 22 CR +2 V +2 12/19/23, 15:08

  25. Stefan Bürk merged patch set 23 12/19/23, 15:08