Review main Merged

90786[TASK] Streamline `SchemaInformation` cache handling

Stefan Bürk CR +2 V +2 Hard +281 −71 updated 10/07/25, 13:20

Commit message

During introducing of the native json field support the custom
(and internal) `SchemaInformation` class has been implemented [1],
which was required to provide a cache layer to reduce cost related
database schema lookups in a handfull of places.

The cache layer has been changed from core cache to a dedicated
`database_schema` cache to use the VariableFrontend per default,
using `serialize/deserialize` under the hood later on [2].

Once we tried to replace even more costly database schema calls in
TYPO3 [3] an issue with the caching of Doctrine DBAL schema objects
was revealed and the respective change got reverted [4].

Further investigation revealed, that we actually only need a very
specific subset of schema information in several places of our
beloved CMS, which nails down to:

* list table names of a database connection
* list table column names for a database of a connection
* list table columns to get information required to make
  decision how to process things in a handfull of places.

`SchemaInformation` was never ment to provide a complete
cached replacement for the database schema methods provided
by `AbstractSchemaManager` from Doctrine DBAL, the goal
was to only have necessary data cached to reduce database
queries to slow schema information tables.

This change takes all of the points into account:

* Add `ColumnInfo` to hold subset column schema information
  for one table column prepared to be unserializable and
  serializable.

* Add `TableInfo` providing a small container to hold the
  `ColumnInfo` information objects of a table prepared to
  be serializeable/unserializable.

* `SchemaInformation` implementation is streamlined, adding
  private build:

    * `buildTableNames()`: Build table names list for the
      connection and cache it, using a cheaper method to
      retrieve the table names in the connection.
    * `buildTableInformation(): Build up simplified table
      and table column information based on new DTO's and
      cache them for specified table.

* Added `SchemaInformation->getTableInfo(string $tableName)`
  to get minimuzed `TableInfo` including `ColumnInfo's`.

* Adding `listTableColumnNames(string $tableName)` as simple
  method to get only table column names using the `TableInfo`
  cache for specified table.

* Adding `listTableColumnInfos(string $table)` to retrieve
  an associative array of table column infos, using the
  `TableInfo()` cache for specified table.

* `SchemaInformation->listTableNames()` is now also cached,
  using new internal `buildTableNames()` method and avoid
  retrieving full schema information during the aggregation
  of the list.

* `SchemaInformation->getTableInfo(string $tableName)` is
  added to return the `TableInfo` for specified table using
  the `TableInfo` cache like other methods and sharing cache
  information.

* `introspectSchema()` and `introspectTable()` are removed
  from `SchemaInformation` in favour of the new DTO's and
  related method.

* A handfull usages in TYPO3 core code and tests are modifed
  to use the new methods and DTO's and replacing usage of
  removed methods.

Note that `SchemaInformation` is an internal structure and
making the aforementioned changes are not breaking based
on the TYPO3 breaking policy as long as internal usage is
adopted correctly, which is done and allows backporting
the change as base for mentioned reverted performance
change.

[1] https://review.typo3.org/c/Packages/TYPO3.CMS/+/76553
[2] https://review.typo3.org/c/Packages/TYPO3.CMS/+/79148
[3] https://review.typo3.org/c/Packages/TYPO3.CMS/+/90562
[4] https://review.typo3.org/c/Packages/TYPO3.CMS/+/90588

Review

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

Issues 5

Files 9

Review log 2434

Show 69 earlier messages
  1. Stefan Bürk uploaded patch set 1 09/24/25, 14:17

  2. core-ci voted on patch set 1 V +1 09/24/25, 14:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85450

  3. Stefan Bürk uploaded patch set 2 V +1 09/24/25, 15:21

  4. core-ci voted on patch set 2 V +1 09/24/25, 15:40 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85455

  5. Benjamin Franzke commented on patch set 2 09/25/25, 12:17

    I think this simplification is a good way to solve the issue and implementation looks good so far. Feel free to finish the patch. :)
    I think we should ideally implement one (un)serialization method serialize or __set_state. I personally prefer __set_state because it created PHP that can just be required and opcached, but you can keep serialize as well. If you stick with one, let's remove the other, do avoid dead code.

    TableInfo.php:47

    I'd prefer to trust the cache and avoid sanitization in the constructor. … use `PackageDependentCacheIdentifier` in the cache lookup, which helps ensure that caches are refreshed on every system change (update/extension install).

    SchemaInformation.php:47

    Let's use `PackageDependentCacheIdentifier` to create a version that is unique per typo3 version/set-of-installed-extensions, which helps ensure that caches are refreshed on every system change and avoids custom version numbers that we need to update manually in here. (The serialization of the connection params can actually be passed as additional hashing information to `PackageDependentCacheIdentifier`)
  6. Stefan Bürk uploaded patch set 3 V +1 09/26/25, 11:20

  7. Stefan Bürk commented on patch set 3 09/26/25, 11:24

    Reply

    Thanks. At least it feels a little bit better that way.

    Reply

    Sticked with `__unserialize/__serialize` for now, because that is used in the current default configuration for the `database_schema` cache. I would prefere the `__set_state()` also, but that makes only sense using a code cache like `core` cache, which it was originally. Helmut changed that, and changing that back is out of the scope for this change now. Removed the currently not used `__set_state()` to remove dead code. Set to resolved, hope that is okay.

    TableInfo.php:47 Reply

    Done

    SchemaInformation.php:47 Reply

    Done
  8. Stefan Bürk marked it work in progress 09/26/25, 11:26

  9. core-ci voted on patch set 3 V -1 09/26/25, 11:33 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85594

  1. Stefan Bürk uploaded patch set 4 V -1 09/26/25, 11:45

  2. core-ci voted on patch set 4 V -1 09/26/25, 11:59 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85597

  3. Stefan Bürk uploaded patch set 5 V -1 09/26/25, 12:20

  4. core-ci voted on patch set 5 V -1 09/26/25, 12:30 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85601

  5. Stefan Bürk uploaded patch set 6 V -1 09/26/25, 12:32

  6. core-ci voted on patch set 6 V +1 09/26/25, 12:38 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85602

  7. Stefan Bürk marked it ready for review 09/26/25, 12:39

  8. Benjamin Franzke uploaded patch set 7 V +1 10/01/25, 06:50

  9. Benjamin Franzke commented on patch set 7 10/01/25, 06:55

    ColumnInfo.php:49

    Hm, `Type::lookupName` implicitly bootstraps the doctrine TypeRegistry, which creates all types and so on. I think i'd only store the `typeName` and only create the type object if actually needed. What do you think?

    SchemaInformation.php:126

    Why is this `trim` needed? `$tableName` is an argument to this function to `listTableColumnInfos()`, why do we expect users of this API to provided quoted table names?
  10. core-ci voted on patch set 7 V +1 10/01/25, 06:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85780

  11. Stefan Bürk uploaded patch set 8 V +1 10/01/25, 07:51

  12. Stefan Bürk commented on patch set 7 10/01/25, 07:51

    ColumnInfo.php:49 Reply

    I get your point in the one hand, but I'd keep it that way it is now. What you are mention means, we would need a "getter" for it (`getType()`) getting the type based on `$typeName` ... mixing the public properties with one getter, which feels weired. When we do that, we should make the properties private and provide getters for all of them making the implementation bigger. On the other hand, I'd say that this does not really hurt. Only the typeName is saved (serialized/unserialized) and the Type instace created in the unserialize method to pass it ... and also passed when creating an instance with `ColumnInfo->convertFromDoctrineColumn()` called from `SchemaInformation`. In all the scenarios, when something calls the method to retrieve a ColumnInfo object the doctrine type registry is already bootstrapped and loaded which is part of creating and connecting a database connection, which is required to reach that code. I would rather keep the slimmer public property api for the object and avoid mixing one getter and public property access. How should we continue ? Moving completly to getter and not storing the type as property ?

    SchemaInformation.php:126 Reply

    Good point. You are right, we can simply assign `$tableName` here and no need to trim quotes here, which is a left over from earlier tries where this was feed from the doctrine Table object where it could have been quoted. I rechecked this, we don't need the quote trimming at all. That means that it is also not needed to trim the colum name in `ColumnInfo->convertFromDoctrineColumn()` and I removed the platform helper, property and passing the argument now. Removing the quotes was muscle memory from working in the database analyzer stack in the past, which required doing this for the parsed virtual schema based on `ext_tables.sql`. Literally, it would not have done any harm here but as it is not required and needed we can leave that out.
  13. core-ci voted on patch set 8 V +1 10/01/25, 07:57 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/85783

  14. Benjamin Franzke commented on patch set 8 10/06/25, 08:21

    ColumnInfo.php:49 Reply

    If you dislike the getter, we can resort to use `Type::lookupName($columnInfo->typeName)` is the users of this API. Would that be bearable for you?

    SchemaInformation.php:126 Reply

    Alright thanks for the background, then let's leave it out.
  15. Stefan Bürk uploaded patch set 9 V +1 10/06/25, 14:11

  16. core-ci voted on patch set 9 V +1 10/06/25, 14:22 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/86071

  17. Benjamin Franzke commented on patch set 9 CR +1 V +1 10/06/25, 14:33

    ColumnInfo.php:49 Reply

    done, thx!

    SchemaInformation.php:126 Reply

    Done
  18. Christian Kuhn commented on patch set 9 10/06/25, 16:21

    ColumnInfo.php:77

    the two methods __serialize() and __unserialize() could be removed now since there is no $type property anymore? did i miss something?
  19. Stefan Bürk uploaded patch set 10 CR +1 V +1 10/07/25, 08:13

  20. Stefan Bürk commented on patch set 10 10/07/25, 08:18

    ColumnInfo.php:77 Reply

    Good point. Yes, not having the `Type $type` property anymore makes the serialize/unserialize methods obsolete now. At first, I thought to keep them to make the mapped data clear and discarded that. The DTO is only internal and not many people working in this area and I think it's better to keep the code clean when it does not have any benefit. Removed the methods here and in `TableInfo`, adding a comment in the static transofrmation method of `ColumnInfo`.
  21. core-ci voted on patch set 10 V +1 10/07/25, 08:23 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/86132

  22. Christian Kuhn voted on patch set 10 CR +1 V +1 10/07/25, 11:55

  23. Benjamin Franzke voted on patch set 10 CR +1 V +1 10/07/25, 12:37

  24. Stefan Bürk voted on patch set 10 CR +2 V +2 10/07/25, 13:02

  25. Stefan Bürk merged patch set 11 10/07/25, 13:20