neat-php / database Goto Github PK

Add support for batch inserts on the connection and query builder

/**
 * @param string $table  The table to insert the given records in
 * @param array[] $rows The rows to insert into the table
 */
public function batchInsert(string $table, array $rows): int

Each row should consist of an key-value array where the key should match the column name.
All the rows should contain the same amount of columns.
The order of the columns doesn't matter (the implementation should sort the keys alphabetically ksort).

Add public function getCount(string $expression = '1'): int; method to the QueryBuilder.

The getCount method should ignore the query type and select expressions and use $expression as COUNT($expression).

/** @var \Neat\Database\Connection $connection */
$userQuery = $connection->select('*')->from('user'); // \Neat\Database\QueryBuilder
$userCount = $userQuery->getCount();                 // 5
$users     = $userQuery->query();                    // \Neat\Database\Result

Some DBMS-es allow for atomic upsert (update/insert) queries. When supported, the query builder should be able to write these queries using driver specific query building implementations (dialects?).

References:
https://mariadb.com/kb/en/library/insert-on-duplicate-key-update/
https://wiki.postgresql.org/wiki/UPSERT
https://www.sqlite.org/lang_UPSERT.html

Making the query builder immutable makes code safer and allows the user to write cleaner code.

<?php
/** @var \Neat\Database\Connection $db */
$query = new \Neat\Database\Query($db);

$usersQuery         = $query->from('user', 'u');
$totalUsers         = $usersQuery->select('COUNT(1)')->query()->value();
$users              = $usersQuery->select('u.*')->limit(5)->query()->rows();
$activeUsersQuery   = $usersQuery->where(['active' => true]);
$totalActiveUsers   = $activeUsersQuery->select('COUNT(1)')->query()->value();
$activeRoles        = $activeUsersQuery->select('DISTINCT u.role')->query()->values();
$activeUsers        = $activeUsersQuery->select('u.*')->limit(5)->query()->rows();

Making the query builder immutable might break code so we'll need an upgrade path:
1.x release:

• Introduce QueryBuilderInterface
• Introduce ImmutableQuery implements QueryBuilderInterface
• Introduce MutableQuery extends Query implements QueryBuilderInterface
• Deprecate Query

2.0 release:

• Query will be immutable
• ImmutableQuery will extend Query

It would be nice to be able to get results indexed by a specified result column. Something like:

<?php

$users = $db->query('SELECT email, name FROM contacts')->index('email')->values('name');
// $users = [ '[email protected]' => 'John Doe', ...]

The current object repository has two responsibilities, managing entities and managing rows. It would be nice to have it separated and make the object repository only responsible for managing entities. This requires having a separate table class resposible for managing the rows associated with the entity.

The table class should be something like object/classes/Repository.php@94cda41

It would be nice to be able to hook into an event that fires for each query executed. This would allow for easier debugging, logging and analysis of performance bottlenecks in applications.

The event should fire after the query result is returned and provide the SQL query, number of rows affected and execution time (Δmicrotime(true)) in its parameters.

When performing a query that results in an error, PDO will (when exceptions not enabled) return a boolean instead of throwing a PDOException. This results in an internal error when constructing the query result using this boolean.

Instead we should issue an exception, preferably one implemented by the database package. This exception class should allow for easy access to the query that triggered the exception.