PostgreSQLUpsertBuilder

PostgreSQLUpsertBuilder.php

<?php

declare(strict_types=1);

namespace App\Doctrine\DBAL\Builder;

use Countable;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Statement;
use Webmozart\Assert\Assert;

final class PostgreSQLUpsertBuilder implements Countable
{
    /**
     * @var Connection
     */
    private $connection;

    /**
     * @var string[]
     */
    private $columns;

    /**
     * @var array
     */
    private $types;

    /**
     * @var string
     */
    private $sql;

    /**
     * @var string[]
     */
    private $inserts = [];

    /**
     * @var array[]
     */
    private $values = [];

    public function __construct(
        Connection $connection,
        string $table,
        array $columns,
        array $types,
        array $conflictColumns
    ) {
        Assert::minCount($columns, 1);
        Assert::count($types, count($columns));

        $this->columns = array_values($columns);
        $this->types = array_values($types);
        $this->connection = $connection;

        $this->sql = sprintf(
            'insert into %s (%s) values %%s on conflict (%s) do update set %s',
            $table,
            implode(', ', $columns),
            implode(', ', $conflictColumns),
            implode(', ', array_map(static function (string $column): string {
                return sprintf('%s = excluded.%1$s', $column);
            }, $columns))
        );
    }

    public function addRow(array $values): void
    {
        Assert::count($values, count($this->columns));

        $parameters = [];
        $index = $this->count();

        foreach (array_values($values) as $i => $value) {
            $parameters[] = $parameter = sprintf(':row_%d_%s', $index, $this->columns[$i]);
            $this->values[] = [$parameter, $value, $this->types[$i]];
        }

        $this->inserts[] = sprintf('(%s)', implode(', ', $parameters));
    }

    public function build(): Statement
    {
        Assert::greaterThan($this->count(), 0);

        $statement = new Statement(
            sprintf($this->sql, implode(', ', $this->inserts)),
            $this->connection
        );

        foreach ($this->values as [$name, $value, $type]) {
            $statement->bindValue($name, $value, $type);
        }

        return $statement;
    }

    public function reset(): void
    {
        $this->inserts = [];
        $this->values = [];
    }

    /**
     * {@inheritDoc}
     */
    public function count(): int
    {
        return count($this->inserts);
    }
}