index/tests/SqliteMariaDbPolyfillTest.php

<?php
// SqliteMariaDbPolyfillTest.php
// Created: 2024-10-21
// Updated: 2024-10-21

declare(strict_types=1);

use PHPUnit\Framework\TestCase;
use PHPUnit\Framework\Attributes\{CoversClass,UsesClass};
use Index\Db\Sqlite\{SqliteConnection,SqliteConnectionInfo,SqliteMariaDbPolyfill};

#[CoversClass(SqliteMariaDbPolyfill::class)]
#[UsesClass(SqliteConnection::class)]
#[UsesClass(SqliteConnectionInfo::class)]
final class SqliteMariaDbPolyfillTest extends TestCase {
    public function testSqliteMariaDbPolyfill(): void {
        $dbPath = tempnam(sys_get_temp_dir(), 'ndx');
        $db = new SqliteConnection(SqliteConnectionInfo::createMemory());
        SqliteMariaDbPolyfill::apply($db);

        $result = $db->query('SELECT IF(1, "true", "false"), IF(0, "true", "false"), IF(NULL, "true", "false"), IF("beans", "true", "false")');
        $this->assertTrue($result->next());
        $this->assertEquals('true', $result->getString(0));
        $this->assertEquals('false', $result->getString(1));
        $this->assertEquals('false', $result->getString(2));
        $this->assertEquals('true', $result->getString(3));

        $result = $db->query('SELECT NOW(), NOW(1), NOW(2), NOW(3), NOW(4), NOW(5), NOW(6), NOW(7)');
        $this->assertTrue($result->next());
        $this->assertEquals(date('Y-m-d H:i:s'), $result->getString(0)); // lol
        $this->assertEquals(21, strlen($result->getString(1)));
        $this->assertEquals(22, strlen($result->getString(2)));
        $this->assertEquals(23, strlen($result->getString(3)));
        $this->assertEquals(24, strlen($result->getString(4)));
        $this->assertEquals(25, strlen($result->getString(5)));
        $this->assertEquals(26, strlen($result->getString(6)));
        $this->assertEquals(26, strlen($result->getString(7)));

        // the implementation isn't identical to MariaDB but it does return the same values for a given seed
        $result = $db->query('SELECT RAND(1337), RAND(1337), RAND(6770), RAND(25252)');
        $this->assertTrue($result->next());
        $this->assertEquals('0.5605297529283', $result->getString(0));
        $this->assertEquals('0.5605297529283', $result->getString(1));
        $this->assertEquals('0.19382955825286', $result->getString(2));
        $this->assertEquals('0.76407597350729', $result->getString(3));

        $result = $db->query('SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP("2013-01-27 22:12:34"), UNIX_TIMESTAMP("2013-01-27 22:12:34.567891")');
        $this->assertTrue($result->next());
        $this->assertEquals(time(), $result->getInteger(0));
        $this->assertEquals(1359324754, $result->getInteger(1));
        $this->assertEquals(1359324754.567891, $result->getFloat(2));

        $result = $db->query('SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP("2013-01-27 22:12:34"), UNIX_TIMESTAMP("2013-01-27 22:12:34.567891")');
        $this->assertTrue($result->next());
        $this->assertEquals(time(), $result->getInteger(0));
        $this->assertEquals(1359324754, $result->getInteger(1));
        $this->assertEquals(1359324754.567891, $result->getFloat(2));

        $result = $db->query('SELECT HEX(INET6_ATON("::1")), HEX(INET6_ATON("127.0.0.1")), INET6_ATON("Beans"), INET6_ATON(NULL), HEX(INET6_ATON("86.81.56.51")), HEX(INET6_ATON("2a02:a445:c012:1:690b:2fa4:4009:e06"))');
        $this->assertTrue($result->next());
        $this->assertEquals('00000000000000000000000000000001', $result->getString(0));
        $this->assertEquals('7F000001', $result->getString(1));
        $this->assertTrue($result->isNull(2));
        $this->assertTrue($result->isNull(3));
        $this->assertEquals('56513833', $result->getString(4));
        $this->assertEquals('2A02A445C0120001690B2FA440090E06', $result->getString(5));

        $result = $db->query('SELECT INET6_NTOA(UNHEX("00000000000000000000000000000001")), INET6_NTOA(UNHEX("7f000001")), INET6_NTOA("Bean"), INET6_NTOA("Beans"), INET6_NTOA(NULL), INET6_NTOA(UNHEX("56513833")), INET6_NTOA(UNHEX("2A02A445C0120001690B2FA440090E06"))');
        $this->assertTrue($result->next());
        $this->assertEquals('::1', $result->getString(0));
        $this->assertEquals('127.0.0.1', $result->getString(1));
        $this->assertEquals('66.101.97.110', $result->getString(2));
        $this->assertTrue($result->isNull(3));
        $this->assertTrue($result->isNull(4));
        $this->assertEquals('86.81.56.51', $result->getString(5));
        $this->assertEquals('2a02:a445:c012:1:690b:2fa4:4009:e06', $result->getString(6));

        $result = $db->query('SELECT FROM_UNIXTIME(NULL), FROM_UNIXTIME(1359324754), FROM_UNIXTIME(1359324754.567891), FROM_UNIXTIME(1359324754.567891, "%a %b %c %D %d %e %f %H %h %I %i %j %k %l %M %m %p %r %S %s %T %U %u %V %v %W %w %X %x %Y %y %# %. %@ %%")');
        $this->assertTrue($result->next());
        $this->assertTrue($result->isNull(0));
        $this->assertEquals('2013-01-27 22:12:34', $result->getString(1));
        $this->assertEquals('2013-01-27 22:12:34.567891', $result->getString(2));
        $this->assertEquals('Sun Jan 1 27th 27 27 567891 22 10 22 12 26 22 10 January 01 PM 10:12:34 PM 34 34 22:12:34 04 04 04 04 Sunday 0 2013 2013 2013 13 # . @ %', $result->getString(3));
    }
}