Introduce Select and Condition classes that abstract from the Connection

Problem/Motivation

SelectInterface and ConditionInterface objects bind the Connection they are built for.

This means you cannot serialize them, as well as that once you create an instance, you cannot reuse it across different database engines.

#3618770: Add support for virtual tables (SQL Views) to the DB Schema Definition API would benefit from a truly db-abstract Select object that could be used in the schema definition of a View - this cannot happen now as schema definition should be db-abstract.

Proposed resolution

• Introduce a connection-independent DatabaseAbstract\Select query builder, which can later be hydrated into Drupal’s connection-specific Query\Select.
• Let Connection::select() and the Query\Select constructor accept either a table name, SelectInterface, or the new abstract Select.
• Add reusable abstract query primitives enums and value objects: Condition, AliasedExpression, Conjunction, JoinType, Operator, Order, and UnionType.
• The abstract `Select` supports WHERE/HAVING conditions, joins, columns, expressions, grouping, ordering, ranges, unions, DISTINCT, comments, tags/metadata, and FOR UPDATE.
• Conditions can contain nested conditions, subqueries, expressions, scalar values, and arrays, including `EXISTS`, `IN`, `BETWEEN`, `LIKE`, and related operators.
• Serialization is supported, but deliberately rejects queries carrying alter metadata or tags.
• Classes in the Drupal\Core\Database\Query\DatabaseAbstract namespace are PHPStan tested on level 10. (It really helps a lot! Once you reach that, any change you make to any signature/variable storage is immediately spotted for anything that depends on that)
• The test suite compares abstract-built hydrated queries against traditional ones to validate like-for-likeness.

Also, make the new API fully fluent: in the current Select API several methods return the alias string of the 'thing' they added to the internal storage, and that forces to use local variables and flatten the construction of the Select build. The new API being fully fluent allows for nesting, at the price of a slight more declarative approach for specifying aliases and condition operators.

Example:

    $query = $this->connection->select('test_task', 't');
    $people_alias = $query->join('test', 'p', '[t].[pid] = [p].[id]');
    $name_field = $query->addField($people_alias, 'name', 'name');
    $query->addField('t', 'task', 'task');
    $priority_field = $query->addField('t', 'priority', 'priority');
    $query->orderBy($priority_field);

is expressed in the abstract API as

    $abstractQuery = DatabaseAbstractSelect::build('test_task', 't')
      ->join('test', 'p', '[t].[pid] = [p].[id]')
      ->column('p', 'name', 'name')
      ->column('t', 'task', 'task')
      ->column('t', 'priority', 'priority')
      ->orderBy('priority');

But also

    $query = Database::getConnection('replica')->select('test_task', 'tt');
    $query->addExpression('[tt].[pid] + 1', 'abc');
    $query->condition('priority', 1, '>');
    $query->condition('priority', 100, '<');
    $subquery = $this->connection->select('test', 'tp');
    $subquery->join('test_one_blob', 'tpb', '[tp].[id] = [tpb].[id]');
    $subquery->join('node', 'n', '[tp].[id] = [n].[nid]');
    $subquery->addTag('node_access');
    $subquery->addMetaData('account', $account);
    $subquery->addField('tp', 'id');
    $subquery->condition('age', 5, '>');
    $subquery->condition('age', 500, '<');
    $query->leftJoin($subquery, 'sq', '[tt].[pid] = [sq].[id]');
    $query->join('test_one_blob', 'tb3', '[tt].[pid] = [tb3].[id]');

is expressed in the abstract API as

    $abstractQuery = DatabaseAbstractSelect::build('test_task', 'tt')
      ->expression('[tt].[pid] + 1', 'abc')
      ->condition('priority', '>', 1)
      ->condition('priority', '<', 100)
      ->join('test_one_blob', 'tb3', '[tt].[pid] = [tb3].[id]')
      ->leftJoin(
        DatabaseAbstractSelect::build('test', 'tp')
          ->join('test_one_blob', 'tpb', '[tp].[id] = [tpb].[id]')
          ->join('node', 'n', '[tp].[id] = [n].[nid]')
          ->addTag('node_access')
          ->addMetaData('account', $account)
          ->column('tp', 'id')
          ->condition('age', '>', 5)
          ->condition('age', '<', 500),
        'sq',
        '[tt].[pid] = [sq].[id]',
      );

which is a net readability and DX improvement IMHO.

Disclaimer: Concept and coding are human generated. AI helped in-progress reviews and summarizing the proposed resolution.

Remaining tasks

User interface changes

Introduced terminology

API changes

Data model changes

Release notes snippet