Review main Merged

84050[TASK] Enrich ReferenceIndex with more relation data

Christian Kuhn CR +2 V +2 Impossible +15211 −15065 updated 04/28/24, 10:24

Commit message

ReferenceIndex (table sys_refindex) has two main jobs:
* It registers "soft" relations, for instance when a
  bodytext sets a link in its text string. Similar things
  are done for relations in anonymous flex form strings.
* It is a registry of DB relations attached to TCA based
  table records.

This patch is about the DB relation part of refindex.

When a TCA record needs to know all its database relations
attached to it, it can either ask the (clumsy)
RelationHandler for relations of each field, which then
queries the foreign table for relations, OR it could ask
the refindex for all relations with a single query. It
is possible to do this for multiple records in one query.

Refindex has significant test coverage within DataHandler:
With over a thousand functional tests validating the
refindex, we can be confident in the accuracy of the
functionality. This coverage has been in place and
continuously improved since TYPO3 v11.

This de-normalized nature of having a single table that
tracks all TCA DB relations of all TCA tables is a powerful
structure that allows us to query relations in a performant
way. This is currently only leveraged in a couple of places
in the backend, though.

Integrating refindex within the frontend is promising to
alleviate the query load on not-yet-cached pages, though:
There are various places that tend to hammer the database
with hundreds or thousand of queries to look up relations,
RootlineUtility in particular is very demanding.

Refindex however does not carry relation details important
in the frontend: It does not know about hidden, starttime
and endtime restrictions applied to single relations.

The patch changes this by adding a series of columns to
sys_refindex, and adapts class ReferenceIndex to take care
of them.

Adding fields to sys_refindex has one drawback:
Primary key 'hash' is a hash of all columns. Adding
columns invalidates all existing hashes, refindex needs
to be rebuild. This is why the patch is so huge: All tests
checking refindex integrity need new rows. It also means
instances need to update refindex. The patch dedicates
an Important.rst to this.

With forcing a full re-index for everyone, the patch takes
the opportunity to use xxh128 over md5 for the hash, which
is a significantly quicker hash algorithm with good
collision protection, and with the same length of 128
bit (128 bit = 16 bytes = 16 hex numbers = 32 chars).

The patch also adds more fine grained table column and
index definitions: Two changed indexes are designed to help
queries having the typical query signature, and queries can
be crafted to use large parts of the combined indexes.

Another detail is that 'endtime' defaults to a high value:
When a casual endtime-aware TCA record has no endtime set,
it's column value is 0. This requires an OR in the query.
refindex de-normalizes this to a high value instead, to
always query rows with a "greater than now" where clause.

The patch reveals a bug within MM when resorting rows. A
single functional test is disabled for now. The situation
will be fixed with another patch.

sys_refindex contains columns for the "left" / "local" side,
and columns for the "right" / "foreign" side. Foreign side
columns are prefixed with "ref_". A quick rundown of
sys_refindex table columns:

hash:
primaray key, xxh128 hash of the other column values

tablename & ref_table:
names of the involved tables. tablename is "_STRING" when
dealing with a softref row.

recuid & ref_uid:
uids of the involved table rows.

field & (new)ref_field:
names of the involved table fields.

sorting & ref_sorting:
keep relations in a deterministic order.

(new)hidden & (new)ref_hidden:
int '1' if the table is TCA hidden aware and the row is hidden.

(new)starttime & (new)ref_starttime:
starttime timestamp of the row if the table is TCA starttime
aware and a value is set, else '0'.

(new)endtime & (new)ref_endtime:
endtime timestamp of the row if the table is TCA endtime aware
and a value is set, else 2^31-1 (year 2038) since postgres 32-bit
int is always signed. Could be extended later.

workspace:
calculated workspace-uid this refindex row is relevant in. With
MM relations, there can be rows with workspace-uid 0 on one side
and workspace-uid 1 on the other side, with the sys_refindex
row being valid for workspace-uid 1.

(new)t3ver_state & (new)ref_t3ver_state:
workspace versionState value of the table row, default int 0.
Used to filter especially deleted placeholder rows (int 2).

flexpointer, softref_key, softref_id, ref_string:
softref and flex form path details fields.

Review

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

Issues 4

Files 1114

Show 1094 more files

Review log 1929

Show 24 earlier messages
  1. Christian Kuhn uploaded patch set 1 04/27/24, 14:47

  2. Christian Kuhn uploaded patch set 2 04/27/24, 14:49

  3. core-ci voted on patch set 1 V -1 04/27/24, 14:54 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56857

  4. core-ci voted on patch set 2 V -1 04/27/24, 14:56 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56858

  1. Christian Kuhn uploaded patch set 3 V -1 04/27/24, 15:42

  2. core-ci voted on patch set 3 V +1 04/27/24, 15:47 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56860

  3. Christian Kuhn updated the commit message in patch set 4 V +1 04/27/24, 17:16

  4. core-ci voted on patch set 4 V +1 04/27/24, 17:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56862

  5. Christian Kuhn uploaded patch set 5 V +1 04/27/24, 17:46

  6. core-ci voted on patch set 5 V +1 04/27/24, 17:51 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56863

  7. Christian Kuhn uploaded patch set 6 V +1 04/28/24, 08:22

  8. Christian Kuhn uploaded patch set 7 04/28/24, 08:25

  9. core-ci voted on patch set 6 V +1 04/28/24, 08:27 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56901

  10. Christian Kuhn updated the commit message in patch set 8 04/28/24, 08:30

  11. core-ci voted on patch set 7 V +1 04/28/24, 08:30 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56902

  12. Christian Kuhn updated the commit message in patch set 9 04/28/24, 08:33

  13. core-ci voted on patch set 8 V +1 04/28/24, 08:35 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56903

  14. Christian Kuhn updated the commit message in patch set 10 04/28/24, 08:37

  15. core-ci voted on patch set 10 V +1 04/28/24, 08:43 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56905

  16. Oliver Klee voted on patch set 10 V +1 04/28/24, 08:50

  17. Oliver Klee commented on patch set 10 04/28/24, 08:51

    ReferenceIndex.php:44

    We can also add `@var int` (or `@var positive-int`) to make this technical requirement PHPStan-accessible.
  18. Anja Leichsenring voted on patch set 10 CR +1 V +1 04/28/24, 09:14

  19. Christian Kuhn uploaded patch set 11 CR +1 V +1 04/28/24, 09:19

  20. Christian Kuhn commented on patch set 10 04/28/24, 09:20

    ReferenceIndex.php:44 Reply

    Done
  21. Anja Leichsenring voted on patch set 11 CR +1 V +1 04/28/24, 09:20

  22. core-ci voted on patch set 11 V +1 04/28/24, 09:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/56906

  23. Stefan Bürk voted on patch set 11 CR +1 V +1 04/28/24, 10:15

  24. Christian Kuhn commented on patch set 11 CR +2 V +2 04/28/24, 10:23

    Risk assessment: This "only" changes refindex, and only in main. The patch has been in the works for quite a while now and got feedback from various people in addition to the votes already. I think it's fine to merge, and we need this merged to finish the patch using the new refindex fields in RootlineUtility.
  25. Christian Kuhn merged patch set 12 04/28/24, 10:24