Review main Merged Breaking

80668[!!!][FEATURE] Finetune SQL autocreation of TCA fields type "datetime"

Garvin Hicking CR +2 V +2 Hard +258 −15 updated 11/20/23, 14:45

Commit message

This patch tries to address the issue of several core DB fields
being set to 'integer signed' (1901 to 2038), specifically:

* `be_users.lastlogin`
* `fe_users.lastlogin`
* `pages.lastUpdated`
* `pages.newUntil`
* `sys_redirect.lasthiton`
* `index_config.timer_next_indexing`
* `tx_extensionmanager_domain_model_extension.last_updated`
* `sys_workspace.publish_time`

These fields currently have a cut-off for 2038. Those are now
changed to 'bigint unsigned' and can survive until 2106.

Additionally, these fields:

* `sys_file_metadata.content_creation_date`
* `tt_content.date`

did not allow any dates before 1901, however certain systems might
want to use FAL metadata of i.e. 1799 (historical paintings),
or content elements of historic sites.

Review

  • Code review CR +2 Done
    • Stefan Bürk +2
    • Oliver Klee +1
    • Christian Kuhn +1
  • Verified V +2 Done
    • Stefan Bürk +2
    • Oliver Klee +1
    • Christian Kuhn +1
    • core-ci +1 CI

Issues 2

Files 17

Review log 5067

Show 3142 earlier messages
  1. Garvin Hicking uploaded patch set 1 08/24/23, 16:05

  2. core-ci voted on patch set 1 V +1 08/24/23, 16:12 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/45790

  3. Garvin Hicking updated the commit message in patch set 2 V +1 08/25/23, 18:26

  4. core-ci voted on patch set 2 V +1 08/25/23, 18:33 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/45839

  5. Garvin Hicking rebased onto patch set 3 V +1 08/29/23, 09:21

  6. core-ci voted on patch set 3 V +1 08/29/23, 09:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/45960

  7. Oliver Klee commented on patch set 3 08/29/23, 10:56

    A note on commit messages: They don't get rendered as ReST, but as Markdown (on GitHub). So for using markup here, Markdown should be used.
  8. Garvin Hicking uploaded patch set 4 V +1 08/30/23, 07:41

  9. Garvin Hicking commented on patch set 4 08/30/23, 07:42

    Reply

    Thanks, I will keep this in mind. I just copied it from the RST changelog entry, I modified my commit now.
  10. core-ci voted on patch set 4 V +1 08/30/23, 07:47 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46016

  11. Friedemann Altrock commented on patch set 4 09/07/23, 09:02

    What do you think about using [behaviour][timeRange] = [min_year, max_year] to automatically pick the right column type for a desired year span?
  12. Garvin Hicking commented on patch set 4 09/07/23, 09:09

    Reply

    I did try to think how to make this configurable, but I agree the labelling sounds clumsy and not intuitive. The downside of a max/min year IMO is: What would be configured for the core then? If it leads to just utilizing the maximum everywhere, it's kind of a moot point having it al all. Also, it might seem to suggest that these would then be hard limits, when in reality its just: int signed, int unsigned, bigint signed, bigint unsigned. (For the larger throw DateTime might fix all of this, but that scope is just too big at least for me)
  13. Friedemann Altrock commented on patch set 4 09/07/23, 09:22

    Reply

    For the core columns I would think that we pick the min/max exactly so that the behaviour stays the same or is fixed for Y2K38, just as you did with the life/large time range. * Anything needing min_year < 1970 must be signed. * Anything needing min_year < 1901 must be signed bigint. * Anything needing max_year >= 2038 must be either unsigned or bigint. * Anything needing max_year > 2106 must be bigint.
  14. Garvin Hicking commented on patch set 4 09/07/23, 09:30

    Reply

    Thanks for that nice overview at the end :-) An issue I see: if anyone changes max/min year in the TCA, you'll need to DB compare and perform changes. restricting might also break existing data. That does apply to the current approach to, but IMO having just two toggles would require fewer changes. And in which situations would one enter specific years like "min: 1851" and max "2049"? I'm thinking this might ask people to enter abritrary values anyways...?! (I appreciate your time and help, I'm only arguing to find a good middle ground :))
  15. Friedemann Altrock commented on patch set 4 09/07/23, 10:15

    Reply

    I did an extension for a catholic church once, where they wanted death-days in a calendar, for some relevant people. The earliest they needed was in the 1860s, so it would have been useful to record that somehow. We ended up doing proper datetime columns, but those didn't work with the date picker (don't know about now). I just found that we already have a range property on the datetime type: https://docs.typo3.org/m/typo3/reference-tca/main/en-us/ColumnsConfig/Type/Datetime/Properties/Range.html
  16. Garvin Hicking commented on patch set 4 09/07/23, 10:41

    Reply

    Hhhhhm. Now that you mention it, I forgot too. Good note! So I could rework an alternate approach with those attributes, and setting them in the core fields. Will need to check if that all works with boundaries above 2038/2104 and 197@/1901. Will wait a day or so for some other feedback/input befofe I dive into.
  17. Garvin Hicking uploaded patch set 5 V +1 09/15/23, 12:12

  18. Garvin Hicking commented on patch set 5 09/15/23, 12:15

    Reply

    I pushed a rework now which IMO makes it a LOT better readable. Would be happy for feedback. :-)
    Reworked patch - no longer introduces new fieldConfig settings, but re-uses the range.upper/lower attributes. Tests also adjusted, cleaned up the docs and commit message.
  19. core-ci voted on patch set 5 V -1 09/15/23, 12:21 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46608

  20. Friedemann Altrock commented on patch set 5 CR +1 09/15/23, 12:25

    Reply

    Nicely done.

    DefaultTcaSchema.php:545

    Small typo: metter -> matter.

    Breaking-99937-DateTimeTCAFieldsToBigint.rst:37

    Small typo: use -> uses.
  21. Garvin Hicking uploaded patch set 6 CR +1 V -1 09/15/23, 12:49

  22. Garvin Hicking commented on patch set 6 09/15/23, 12:55

    Changed typos, removed wrong default from 'int unsigned' to 'int signed', and crossing my fingers this fixes the pipeline build error

    DefaultTcaSchema.php:545 Reply

    Ack

    Breaking-99937-DateTimeTCAFieldsToBigint.rst:37 Reply

    Ack
  23. core-ci voted on patch set 6 V -1 09/15/23, 12:59 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46609

  24. Garvin Hicking uploaded patch set 7 V -1 09/15/23, 13:14

  25. core-ci voted on patch set 7 V +1 09/15/23, 13:21 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46610

  26. Friedemann Altrock voted on patch set 7 V +1 CR +1 09/15/23, 13:58

  27. Garvin Hicking uploaded patch set 8 CR +1 V +1 09/18/23, 11:33

  28. Garvin Hicking commented on patch set 8 09/18/23, 11:35

    Had to adapt getting field types to resolve merge conflict due to https://review.typo3.org/81039
  29. core-ci voted on patch set 8 V +1 09/18/23, 11:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/46659

  30. Friedemann Altrock voted on patch set 8 V +1 CR +1 09/18/23, 12:28

  31. Oliver Klee voted on patch set 8 V +1 10/16/23, 13:34

  32. Chris Müller commented on patch set 8 CR -1 10/16/23, 16:30

    on the docs
  33. Chris Müller uploaded patch set 9 CR +1 V +1 10/16/23, 16:43

  34. Chris Müller commented on patch set 9 10/16/23, 16:44

    Reply

    Done
  35. Chris Müller uploaded patch set 10 10/16/23, 16:45

  36. Friedemann Altrock voted on patch set 10 V +1 CR +1 10/16/23, 16:52

  37. core-ci voted on patch set 9 V +1 10/16/23, 16:53 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47834

  38. core-ci voted on patch set 10 V +1 10/16/23, 16:55 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47835

  39. Oliver Klee commented on patch set 10 10/16/23, 17:09

    I would have expected for the corresponding columns to be removed from the `ext_tables.sql` files. Am I missing something here?
  40. Garvin Hicking commented on patch set 10 10/16/23, 17:19

    Reply

    Can't research atm (on mobile) but ai believe the removal of starttime/endtime definitions has already been addressed, thus not part of the patch
  41. Anja Leichsenring updated the commit message in patch set 11 CR +1 V +1 10/17/23, 04:02

  42. core-ci voted on patch set 11 V +1 10/17/23, 04:09 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/47843

  1. Garvin Hicking commented on patch set 11 10/17/23, 06:58

    Reply

    @Oliver: I looked it up now, these fields already vanished in this commit: https://review.typo3.org/c/Packages/TYPO3.CMS/+/74281 Scope of this patch is to adjust the fields depending on their time range, so the groundwork is all there.
  2. Anja Leichsenring updated the commit message in patch set 12 V +1 10/22/23, 11:33

  3. core-ci voted on patch set 12 V +1 10/22/23, 11:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48035

  4. Oliver Klee commented on patch set 12 V +1 CR +1 10/22/23, 11:41

    Haha :-)
  5. Oliver Klee commented on patch set 12 10/22/23, 12:36

    Note: This change now has merge conflicts that need resolving.
  6. Garvin Hicking rebased onto patch set 13 CR +1 V +1 10/22/23, 14:14

    The following files contain Git conflicts: * typo3/sysext/core/Documentation/Changelog/13.0/Feature-101553-Auto-createDBFieldsFromTCAColumns.rst
  7. core-ci voted on patch set 13 V +1 10/22/23, 14:21 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/48043

  8. Chris Müller uploaded patch set 14 V +1 10/22/23, 14:52

  9. Chris Müller commented on patch set 14 10/22/23, 14:53

    Reply

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

  11. Garvin Hicking marked it ready for review 10/22/23, 15:05

  12. Garvin Hicking commented on patch set 14 10/22/23, 15:06

    Reply

    thanks. tried to do it on mobile&gerrit, but turns out that doesn't work well 😅
  13. Oliver Klee voted on patch set 14 V +1 CR +1 10/22/23, 15:35

  14. Garvin Hicking uploaded patch set 15 CR +1 V +1 11/20/23, 12:47

    Refactored for less complexity
  15. Garvin Hicking commented on patch set 15 11/20/23, 12:48

    Refactored recent patchset; a discussion lead to the feeling that code complexity is too high with ranger/upper/lower evaluation. Thus we only use BIGINT (SIGNED) now. Fields that previously specified INTEGER UNSIGNED can be converted to BIGINT SIGNED. Those fields gain the ability to also specify negative values, because BIGINT range is high enough to only enhance old data, but no truncation will occur. INTEGER SIGNED (1970 to 2106) >= BIGINT UNSIGNED (way before christ until way after earth) INTEGER UNSIGNED (1901 to 2038) => BIGINT UNSIGNED The only downside of this is more resource allocation. The upper/lower ranges of the core's TCA fields (if existing) have been adjusted to reference 2106 instead of 2038 (relevant only for the JS-based DateTime picker).
  16. core-ci voted on patch set 15 V -1 11/20/23, 12:51 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49131

  17. Garvin Hicking uploaded patch set 16 V -1 11/20/23, 13:51

    Fix styleguide tests that only checked on IntegerType
  18. core-ci voted on patch set 16 V +1 11/20/23, 13:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49133

  19. Oliver Klee voted on patch set 16 V +1 CR +1 11/20/23, 14:30

  20. Garvin Hicking uploaded patch set 17 CR +1 V +1 11/20/23, 14:31

    Remove obsolete sys workspace range
  21. core-ci voted on patch set 17 V +1 11/20/23, 14:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/49134

  22. Christian Kuhn voted on patch set 17 V +1 CR +1 11/20/23, 14:37

  23. Oliver Klee voted on patch set 17 V +1 CR +1 11/20/23, 14:41

  24. Stefan Bürk voted on patch set 17 V +2 CR +2 11/20/23, 14:45

  25. Stefan Bürk merged patch set 18 11/20/23, 14:45