pdo-document/tests/Unit/QueryTest.php

<?php
/**
 * @author Daniel J. Summers <daniel@bitbadger.solutions>
 * @license MIT
 */

declare(strict_types=1);

use BitBadger\PDODocument\{AutoId, Configuration, DocumentException, Field, FieldMatch, Mode, Query};

pest()->group('unit');

beforeEach(function () { Configuration::overrideMode(Mode::SQLite); });
afterEach(function () { Configuration::overrideMode(null); });

describe('::selectFromTable()', function () {
    test('correctly forms a query', function () {
        expect(Query::selectFromTable('testing'))->toBe('SELECT data FROM testing');
    });
});

describe('::whereByFields()', function () {
    test('generates a single field correctly', function () {
        expect(Query::whereByFields([Field::lessOrEqual('test_field', '', ':it')]))->toBe("data->>'test_field' <= :it");
    });
    test('generates all fields correctly', function () {
        expect(Query::whereByFields(

                [Field::lessOrEqual('test_field', '', ':it'), Field::equal('other_field', '', ':other')]))
            ->toBe("data->>'test_field' <= :it AND data->>'other_field' = :other",);
    });
    test('generates any field correctly', function () {
        expect(Query::whereByFields(
                [Field::lessOrEqual('test_field', '', ':it'), Field::equal('other_field', '', ':other')],
                FieldMatch::Any))
            ->toBe("data->>'test_field' <= :it OR data->>'other_field' = :other");
    });
});

describe('::whereById()', function () {
    test('uses default parameter name', function () {
        expect(Query::whereById())->toBe("data->>'id' = :id");
    });
    test('uses provided parameter name', function () {
        expect(Query::whereById(':di'))->toBe("data->>'id' = :di");
    });
});

describe('::whereDataContains()', function () {
    test('uses default parameter [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::whereDataContains())->toBe('data @> :criteria');
    });
    test('uses provided parameter [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::whereDataContains(':it'))->toBe('data @> :it');
    });
    test('throws [SQLite]', function () {
        expect(fn () => Query::whereDataContains())->toThrow(DocumentException::class);
    });
});

describe('::whereJsonPathMatches()', function () {
    test('uses default parameter [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::whereJsonPathMatches())->toBe('jsonb_path_exists(data, :path::jsonpath)');
    });
    test('uses provided parameter [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::whereJsonPathMatches(':road'))->toBe('jsonb_path_exists(data, :road::jsonpath)');
    });
    test('throws [SQLite]', function () {
        expect(fn () => Query::whereJsonPathMatches())->toThrow(DocumentException::class);
    });
});

describe('::insert()', function () {
    test('generates with no auto-ID [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::insert('test_tbl'))->toBe('INSERT INTO test_tbl VALUES (:data)');
    });
    test('generates with no auto-ID [SQLite]', function () {
        expect(Query::insert('test_tbl'))->toBe('INSERT INTO test_tbl VALUES (:data)');
    });
    test('generates with auto numeric ID [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::insert('test_tbl', AutoId::Number))
            ->toBe("INSERT INTO test_tbl VALUES (:data::jsonb || ('{\"id\":' "
                . "|| (SELECT COALESCE(MAX((data->>'id')::numeric), 0) + 1 FROM test_tbl) || '}')::jsonb)");
    });
    test('generates with auto numeric ID [SQLite]', function () {
        expect(Query::insert('test_tbl', AutoId::Number))
            ->toBe("INSERT INTO test_tbl VALUES (json_set(:data, '$.id', "
                . "(SELECT coalesce(max(data->>'id'), 0) + 1 FROM test_tbl)))");
    });
    test('generates with auto UUID [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::insert('test_tbl', AutoId::UUID))
            ->toStartWith("INSERT INTO test_tbl VALUES (:data::jsonb || '{\"id\":\"")
            ->toEndWith("\"}')");
    });
    test('generates with auto UUID [SQLite]', function () {
        expect(Query::insert('test_tbl', AutoId::UUID))
            ->toStartWith("INSERT INTO test_tbl VALUES (json_set(:data, '$.id', '")
            ->toEndWith("'))");
    });
    test('generates with auto random string [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        Configuration::$idStringLength = 8;
        try {
            $query = Query::insert('test_tbl', AutoId::RandomString);
            expect($query)
                ->toStartWith("INSERT INTO test_tbl VALUES (:data::jsonb || '{\"id\":\"")
                ->toEndWith("\"}')")
                ->and(str_replace(["INSERT INTO test_tbl VALUES (:data::jsonb || '{\"id\":\"", "\"}')"], '', $query))
                ->toHaveLength(8);
        } finally {
            Configuration::$idStringLength = 16;
        }
    });
    test('generates with auto random string [SQLite]', function () {
        $query = Query::insert('test_tbl', AutoId::RandomString);
        expect($query)
            ->toStartWith("INSERT INTO test_tbl VALUES (json_set(:data, '$.id', '")
            ->toEndWith("'))")
            ->and(str_replace(["INSERT INTO test_tbl VALUES (json_set(:data, '$.id', '", "'))"], '', $query))
            ->toHaveLength(16);
    });
    test('throws when mode not set', function () {
        Configuration::overrideMode(null);
        expect(fn () => Query::insert('kaboom'))->toThrow(DocumentException::class);
    });
});

describe('::save()', function () {
    test('generates the correct query', function () {
        expect(Query::save('test_tbl'))
            ->toBe("INSERT INTO test_tbl VALUES (:data) ON CONFLICT ((data->>'id')) DO UPDATE SET data = EXCLUDED.data");
    });
});

describe('::update()', function () {
    test('generates the correct query', function () {
        expect(Query::update('testing'))->toBe("UPDATE testing SET data = :data WHERE data->>'id' = :id");
    });
});

describe('::orderBy()', function () {
    test('returns blank for no criteria [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::orderBy([]))->toBeEmpty();
    });
    test('returns blank for no criteria [SQLite]', function () {
        expect(Query::orderBy([]))->toBeEmpty();
    });
    test('generates one field with no direction [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::orderBy([Field::named('TestField')]))->toBe(" ORDER BY data->>'TestField'");
    });
    test('generates one field with no direction [SQLite]', function () {
        expect(Query::orderBy([Field::named('TestField')]))->toBe(" ORDER BY data->>'TestField'");
    });
    test('generates with one qualified field [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        $field = Field::named('TestField');
        $field->qualifier = 'qual';
        expect(Query::orderBy([$field]))->toBe(" ORDER BY qual.data->>'TestField'");
    });
    test('generates with one qualified field [SQLite]', function () {
        $field = Field::named('TestField');
        $field->qualifier = 'qual';
        expect(Query::orderBy([$field]))->toBe(" ORDER BY qual.data->>'TestField'");
    });
    test('generates with multiple fields and direction [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::orderBy(
                [Field::named('Nested.Test.Field DESC'), Field::named('AnotherField'), Field::named('It DESC')]))
            ->toBe(" ORDER BY data#>>'{Nested,Test,Field}' DESC, data->>'AnotherField', data->>'It' DESC");
    });
    test('generates with multiple fields and direction [SQLite]', function () {
        expect(Query::orderBy(
                [Field::named('Nested.Test.Field DESC'), Field::named('AnotherField'), Field::named('It DESC')]))
            ->toBe(" ORDER BY data->'Nested'->'Test'->>'Field' DESC, data->>'AnotherField', data->>'It' DESC");
    });
    test('generates with numeric field [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::orderBy([Field::named('n:Test')]))->toBe(" ORDER BY (data->>'Test')::numeric");
    });
    test('generates with numeric field [SQLite]', function () {
        expect(Query::orderBy([Field::named('n:Test')]))->toBe(" ORDER BY data->>'Test'");
    });
    test('generates case-insensitive ordering [PostgreSQL]', function () {
        Configuration::overrideMode(Mode::PgSQL);
        expect(Query::orderBy([Field::named('i:Test.Field DESC NULLS FIRST')]))
            ->toBe(" ORDER BY LOWER(data#>>'{Test,Field}') DESC NULLS FIRST");
    });
    test('generates case-insensitive ordering [SQLite]', function () {
        expect(Query::orderBy([Field::named('i:Test.Field ASC NULLS LAST')]))
            ->toBe(" ORDER BY data->'Test'->>'Field' COLLATE NOCASE ASC NULLS LAST");
    });
});