InsertQueryTest.php

<?php

declare(strict_types=1);

namespace Cycle\Database\Tests\Functional\Driver\SQLite\Query;

// phpcs:ignore
use Cycle\Database\Driver\SQLite\Query\SQLiteInsertQuery;
use Cycle\Database\Exception\BuilderException;
use Cycle\Database\Injection\Fragment;
use Cycle\Database\Tests\Functional\Driver\Common\Query\InsertQueryTest as CommonClass;

/**
 * @group driver
 * @group driver-sqlite
 */
class InsertQueryTest extends CommonClass
{
    public const DRIVER = 'sqlite';

    public function testQueryInstance(): void
    {
        parent::testQueryInstance();
        $this->assertInstanceOf(SQLiteInsertQuery::class, $this->database->insert());
    }

    public function testCustomReturning(): void
    {
        $insert = $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->returning('name');

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) VALUES (?, ?) RETURNING {name}',
            $insert,
        );
    }

    public function testCustomMultipleReturning(): void
    {
        $insert = $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->returning('name', 'created_at');

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) VALUES (?, ?) RETURNING {name}, {created_at}',
            $insert,
        );
    }

    public function testCustomReturningWithFragment(): void
    {
        $insert = $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->returning(new Fragment('"name" as "full_name"'));

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) VALUES (?, ?) RETURNING {name} as {full_name}',
            $insert,
        );
    }

    public function testReturningWithoutColumnsShouldThrowAnException(): void
    {
        $this->expectException(BuilderException::class);
        $this->expectExceptionMessage('RETURNING clause should contain at least 1 column.');

        $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->returning();
    }

    public function testReturningSingleValueFromDatabase(): void
    {
        $schema = $this->schema('returning_value');
        $schema->primary('id');
        $schema->string('name');
        $schema->save();

        $returning = $this->database
            ->insert('returning_value')
            ->values(['name' => 'foo'])
            ->returning('id')
            ->run();

        $this->assertSame(1, $returning);
    }

    public function testReturningValuesFromDatabase(): void
    {
        $schema = $this->schema('returning_values');
        $schema->primary('id');
        $schema->string('name');
        $schema->save();

        $returning = $this->database
            ->insert('returning_values')
            ->values(['name' => 'foo'])
            ->returning('id', 'name')
            ->run();

        $this->assertSame(1, $returning['id']);
        $this->assertSame('foo', $returning['name']);

        $returning = $this->database
            ->insert('returning_values')
            ->values(['name' => 'bar'])
            ->returning('id', new Fragment('"name" as "title"'))
            ->run();

        $this->assertSame(2, $returning['id']);
        $this->assertSame('bar', $returning['title']);
    }

    /**
     * A part of a composite key generated by the database can only be read back via RETURNING:
     * the `lastInsertId` fallback is limited to single-column keys.
     */
    public function testReturningGeneratedPartOfCompositeKey(): void
    {
        $this->database->execute(
            <<<'SQL'
                CREATE TABLE composite_key (
                    parent_id varchar(36) NOT NULL,
                    id        integer     NOT NULL PRIMARY KEY AUTOINCREMENT,
                    UNIQUE (parent_id, id)
                )
                SQL,
        );

        $id = $this->database
            ->insert('composite_key')
            ->values(['parent_id' => 'parent'])
            ->returning('id')
            ->run();

        $this->assertSame(1, $id);
    }

    public function testSimpleInsertMultipleRows(): void
    {
        $insert = $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->values('John', 200);

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) SELECT ? AS {name}, ? AS {balance}
UNION ALL SELECT ?, ?',
            $insert,
        );
    }

    public function testInsertMultipleRowsAsArray(): void
    {
        $insert = $this->database->insert()->into('table')->values([
            ['name' => 'Anton', 'balance' => 100],
            ['name' => 'John', 'balance' => 200],
        ]);

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) SELECT ? AS {name}, ? AS {balance}
UNION ALL SELECT ?, ?',
            $insert,
        );
    }

    public function testSimpleInsertMultipleRows2(): void
    {
        $insert = $this->database->insert()->into('table')
            ->columns('name', 'balance')
            ->values('Anton', 100)
            ->values('John', 200)
            ->values('Pitt', 200);

        $this->assertSameQuery(
            'INSERT INTO {table} ({name}, {balance}) SELECT ? AS {name}, ? AS {balance}'
            . ' UNION ALL SELECT ?, ?'
            . ' UNION ALL SELECT ?, ?',
            $insert,
        );
    }

    public function testInsertMicroseconds(): void
    {
        $schema = $this->schema(
            table: 'with_microseconds',
            driverConfig: ['options' => ['withDatetimeMicroseconds' => true]],
        );
        $schema->primary('id');
        $schema->datetime('datetime', 6);
        $schema->save();

        $expected = new \DateTimeImmutable();

        $id = $this->db(
            driverConfig: ['options' => ['withDatetimeMicroseconds' => true]],
        )->insert('with_microseconds')->values([
            'datetime' => $expected,
        ])->run();

        $result = $this->db(
            driverConfig: ['options' => ['withDatetimeMicroseconds' => true]],
        )->select('datetime')
            ->from('with_microseconds')
            ->where('id', $id)
            ->run()
            ->fetch();

        $this->assertSame(
            $expected->setTimezone($this->database->getDriver()->getTimezone())->format('Y-m-d H:i:s.u'),
            $result['datetime'],
        );
    }

    public function testInsertDatetimeWithoutMicroseconds(): void
    {
        $schema = $this->schema('without_microseconds');
        $schema->primary('id');
        $schema->datetime('datetime');
        $schema->save();

        $expected = new \DateTimeImmutable();

        $id = $this->database->insert('without_microseconds')->values([
            'datetime' => $expected,
        ])->run();

        $result = $this->database->select('datetime')
            ->from('without_microseconds')
            ->where('id', $id)
            ->run()
            ->fetch();

        $this->assertSame(
            $expected->setTimezone($this->database->getDriver()->getTimezone())->format('Y-m-d H:i:s'),
            $result['datetime'],
        );
    }
}