Review main Abandoned

74643[WIP][POC][TASK] Add support for Common Table Expressions

Stefan Bürk CR 0 V 0 Nightmare +5621 −0 updated 07/03/24, 09:02

Commit message

Common Table Expression provides the ability to write better
performing database queries. Up and down tree traversels can
now mostly be replaced by one recursive query, reducing quite
some query roundtrips for multiple queries to archieve the
same outcome.

This patch implements a first core internal api as addition to
the facade implementations of doctrine/dbal classes. Thus give
us the advantage to reuse the familar QueryBuilder. Using this
way avoids the need to reimplement extensions and adjustments,
like e.g. query restrictions and minimizes the learning curve
for the new API.

Instead of require a full blown 3rd party extension, which
comes with quite some additional dependencies the corresponding
code has been condensed to the needed minumum and aligned to
core facade implementation.

Additional readings:

* Base Library: https://github.com/somnambulist-tech/cte-builder
* Notes: https://notes.typo3.org/B_CotzfgSuemytHdJ_osMA?view

------------------------------------------------------------------

Added addition expression and methods for internal usage only:

* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`
  - added castChar(...)
  - added castInt(..)
  - added concat(...)
  - added space(...)
  - added right(...)
  - added repeat(...)
  - added leftPad(...)
  - added rightPad(...)

  => needs tests !!!!

------------------------------------------------------------------

todo's:

* find a solution for named placeholder not working for
  `mysqli` database driver; positional placeholder not
  working either, because of the nested QueryBuilder/
  Expression construct.
* simple page tree list query designed by Benni from the
  CTE notes document not fully working - not all subpages
  are retrieved
* simple page tree list query needs deterministic ordering
* cleanup simple/dirty collection utility and replace with
  proper core methods; or implement them in proper way

* POSTGRES: analyse and fix path_for_sorting logic for postgres
  to generate proper value instead of a string with only one zero
  and otherwise spaces up to CHAR(4000) cast
----------------------------------------------------------------

possible pre-patch splits in the end:

* ExpressionBuilder methods as task/feature + tests
* ...

----------------------------------------------------------------

Review

  • Code review CR 0
  • Verified V 0

Files 27

Show 7 more files

Review log 4177

Show 2952 earlier messages
  1. Stefan Bürk uploaded patch set 1 05/14/22, 00:05

  2. Stefan Bürk wrote 05/14/22, 00:06

    Set private
  3. Stefan Bürk uploaded patch set 2 05/14/22, 00:20

  4. Stefan Bürk marked it work in progress 05/14/22, 00:21

  5. Stefan Bürk wrote 05/14/22, 00:23

    Unset private
  6. Stefan Bürk wrote 05/14/22, 00:24

    Set private
  7. Stefan Bürk updated the commit message in patch set 3 05/14/22, 00:24

  8. Stefan Bürk wrote 05/14/22, 00:25

    Unset private
  9. Stefan Bürk updated the commit message in patch set 4 05/14/22, 00:28

  10. core-ci voted on patch set 2 V +1 05/14/22, 00:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27198

  11. core-ci voted on patch set 3 V +1 05/14/22, 00:34 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27199

  12. core-ci voted on patch set 4 V +1 05/14/22, 00:37 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27200

  13. Stefan Bürk uploaded patch set 5 05/14/22, 01:43

  14. core-ci voted on patch set 5 V -1 05/14/22, 01:52 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27202

  15. Stefan Bürk uploaded patch set 6 05/14/22, 02:49

  16. core-ci voted on patch set 6 V -1 05/14/22, 02:57 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27204

  17. Stefan Bürk uploaded patch set 7 05/14/22, 10:06

  18. core-ci voted on patch set 7 V +1 05/14/22, 10:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27208

  19. Stefan Bürk uploaded patch set 8 05/14/22, 10:18

  20. core-ci voted on patch set 8 V +1 05/14/22, 10:25 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27209

  21. Stefan Bürk uploaded patch set 9 05/14/22, 12:33

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

  23. Stefan Bürk updated the commit message in patch set 10 05/14/22, 12:53

  24. Stefan Bürk uploaded patch set 11 05/14/22, 13:00

  25. core-ci voted on patch set 10 V +1 05/14/22, 13:00 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27211

  26. core-ci voted on patch set 11 V -1 05/14/22, 13:08 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27212

  27. core-ci voted on patch set 11 V +1 05/14/22, 13:24 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27212

  28. Stefan Bürk uploaded patch set 12 05/15/22, 17:18

  29. core-ci voted on patch set 12 V -1 05/15/22, 17:27 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27231

  30. Stefan Bürk uploaded patch set 13 05/15/22, 17:47

  31. Stefan Bürk updated the commit message in patch set 14 05/15/22, 17:55

  32. core-ci voted on patch set 13 V -1 05/15/22, 17:55 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27233

  33. Stefan Bürk uploaded patch set 15 05/15/22, 17:57

  34. core-ci voted on patch set 14 V -1 05/15/22, 18:05 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27234

  35. core-ci voted on patch set 15 V -1 05/15/22, 18:06 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27235

  36. Stefan Bürk uploaded patch set 16 05/15/22, 18:25

  37. core-ci voted on patch set 16 V +1 05/15/22, 18:32 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27236

  38. Stefan Bürk uploaded patch set 17 05/15/22, 19:16

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

  40. Stefan Bürk uploaded patch set 18 05/15/22, 19:38

  41. core-ci voted on patch set 18 V -1 05/15/22, 19:46 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27238

  42. Stefan Bürk uploaded patch set 19 05/15/22, 20:59

  43. Stefan Bürk uploaded patch set 20 05/15/22, 21:08

  44. core-ci voted on patch set 19 V +1 05/16/22, 01:03 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27240

  45. core-ci voted on patch set 20 V +1 05/16/22, 01:05 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27241

  46. Stefan Bürk rebased onto patch set 21 05/16/22, 22:44

  47. core-ci voted on patch set 21 V +1 05/16/22, 22:51 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27268

  48. Stefan Bürk uploaded patch set 22 05/21/22, 12:03

  49. Stefan Bürk uploaded patch set 23 05/21/22, 12:05

  50. core-ci voted on patch set 22 V -1 05/21/22, 12:10 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27372

  51. core-ci voted on patch set 23 V +1 05/21/22, 12:14 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27373

  52. Stefan Bürk uploaded patch set 24 05/21/22, 13:44

  1. core-ci voted on patch set 24 V +1 05/21/22, 13:51 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27376

  2. Stefan Bürk uploaded patch set 25 05/21/22, 13:53

  3. Stefan Bürk uploaded patch set 26 05/21/22, 13:54

  4. core-ci voted on patch set 26 V +1 05/21/22, 14:02 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27378

  5. core-ci voted on patch set 25 V -1 05/21/22, 14:07 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27377

  6. Stefan Bürk uploaded patch set 27 05/21/22, 14:54

  7. core-ci voted on patch set 27 V +1 05/21/22, 15:01 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27380

  8. Stefan Bürk uploaded patch set 28 05/29/22, 17:07

  9. core-ci voted on patch set 28 V -1 05/29/22, 17:16 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27556

  10. Stefan Bürk uploaded patch set 29 05/29/22, 17:20

  11. core-ci voted on patch set 29 V -1 05/29/22, 17:32 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27558

  12. Stefan Bürk uploaded patch set 30 05/29/22, 18:07

  13. core-ci voted on patch set 30 V -1 05/29/22, 18:15 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27560

  14. Stefan Bürk uploaded patch set 31 05/29/22, 19:33

  15. core-ci voted on patch set 31 V -1 05/29/22, 19:41 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27563

  16. core-ci voted on patch set 31 V +1 05/29/22, 19:45 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27563

  17. Stefan Bürk uploaded patch set 32 05/29/22, 21:07

  18. core-ci voted on patch set 32 V +1 05/29/22, 21:16 Core CI is happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27564

  19. Stefan Bürk uploaded patch set 33 05/31/22, 21:53

  20. core-ci voted on patch set 33 V -1 05/31/22, 22:01 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27609

  21. Stefan Bürk uploaded patch set 34 05/31/22, 22:21

  22. core-ci voted on patch set 34 V -1 05/31/22, 22:29 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27611

  23. Stefan Bürk rebased onto patch set 35 06/08/22, 10:57

  24. core-ci voted on patch set 35 V -1 06/08/22, 11:04 Core CI is not happy: https://git.typo3.org/typo3/CI/cms/-/pipelines/27803

  25. Stefan Bürk abandoned the change 07/03/24, 09:02