pdo-document/src/Query.php

<?php declare(strict_types=1);

namespace BitBadger\PDODocument;

use Random\RandomException;

/**
 * Query construction functions
 */
class Query
{
    /**
     * Create a SELECT clause to retrieve the document data from the given table
     *
     * @param string $tableName The name of the table from which document data should be retrieved
     * @return string The SELECT clause to retrieve a document from the given table
     */
    public static function selectFromTable(string $tableName): string
    {
        return "SELECT data FROM $tableName";
    }

    /**
     * Create a WHERE clause fragment to implement a comparison on fields in a JSON document
     *
     * @param Field[] $fields The field comparison to generate
     * @param FieldMatch|null $match How to join multiple conditions (optional; defaults to All)
     * @return string The WHERE clause fragment matching the given fields and parameter
     * @throws DocumentException If the database mode has not been set
     */
    public static function whereByFields(array $fields, ?FieldMatch $match = null): string
    {
        return implode(' ' . ($match ?? FieldMatch::All)->toString() . ' ',
            array_map(fn($it) => $it->toWhere(), $fields));
    }

    /**
     * Create a WHERE clause fragment to implement an ID-based query
     *
     * @param string $paramName The parameter name where the value of the ID will be provided (optional; default @id)
     * @param mixed $docId The ID of the document to be retrieved; used to determine type for potential JSON field
     *     casts (optional; string ID assumed if no value is provided)
     * @return string The WHERE clause fragment to match by ID
     * @throws DocumentException If the database mode has not been set
     */
    public static function whereById(string $paramName = ':id', mixed $docId = null): string
    {
        return self::whereByFields([Field::EQ(Configuration::$idField, $docId ?? '', $paramName)]);
    }

    /**
     * Create a WHERE clause fragment to implement a JSON containment query (PostgreSQL only)
     *
     * @param string $paramName The name of the parameter (optional; defaults to `:criteria`)
     * @return string The WHERE clause fragment for a JSON containment query
     * @throws DocumentException If the database mode is not PostgreSQL
     */
    public static function whereDataContains(string $paramName = ':criteria'): string
    {
        if (Configuration::$mode <> Mode::PgSQL) {
            throw new DocumentException('JSON containment is only supported on PostgreSQL');
        }
        return "data @> $paramName";
    }

    /**
     * Create a WHERE clause fragment to implement a JSON Path match query (PostgreSQL only)
     *
     * @param string $paramName The name of the parameter (optional; defaults to `:path`)
     * @return string The WHERE clause fragment for a JSON Path match query
     * @throws DocumentException If the database mode is not PostgreSQL
     */
    public static function whereJsonPathMatches(string $paramName = ':path'): string
    {
        if (Configuration::$mode <> Mode::PgSQL) {
            throw new DocumentException('JSON Path matching is only supported on PostgreSQL');
        }
        return "jsonb_path_exists(data, $paramName::jsonpath)";
    }

    /**
     * Create an `INSERT` statement for a document
     *
     * @param string $tableName The name of the table into which the document will be inserted
     * @param AutoId|null $autoId The version of automatic ID query to generate (optional, defaults to None)
     * @return string The `INSERT` statement to insert a document
     * @throws DocumentException If the database mode is not set
     */
    public static function insert(string $tableName, ?AutoId $autoId = null): string
    {
        try {
            $id = Configuration::$idField;
            $values = match (Configuration::$mode) {
                Mode::SQLite => match ($autoId ?? AutoId::None) {
                    AutoId::None         => ':data',
                    AutoId::Number       => "json_set(:data, '$.$id', "
                                                . "(SELECT coalesce(max(data->>'$id'), 0) + 1 FROM $tableName))",
                    AutoId::UUID         => "json_set(:data, '$.$id', '" . AutoId::generateUUID() . "')",
                    AutoId::RandomString => "json_set(:data, '$.$id', '" . AutoId::generateRandom() ."')"
                },
                Mode::PgSQL => match ($autoId ?? AutoId::None) {
                    AutoId::None         => ':data',
                    AutoId::Number       => ":data::jsonb || ('{\"$id\":' || "
                                                . "(SELECT COALESCE(MAX((data->>'$id')::numeric), 0) + 1 "
                                                    . "FROM $tableName) || '}')::jsonb",
                    AutoId::UUID         => ":data::jsonb || '{\"$id\":\"" . AutoId::generateUUID() . "\"}'",
                    AutoId::RandomString => ":data::jsonb || '{\"$id\":\"" . AutoId::generateRandom() . "\"}'",
                },
                default =>
                    throw new DocumentException('Database mode not set; cannot generate auto-ID INSERT statement'),
            };
            return "INSERT INTO $tableName VALUES ($values)";
        } catch (RandomException $ex) {
            throw new DocumentException('Unable to generate ID: ' . $ex->getMessage(), previous: $ex);
        }
    }

    /**
     * Query to save a document, inserting it if it does not exist and updating it if it does (AKA "upsert")
     *
     * @param string $tableName The name of the table into which a document should be saved
     * @return string The INSERT...ON CONFLICT query for the document
     */
    public static function save(string $tableName): string
    {
        $id = Configuration::$idField;
        return "INSERT INTO $tableName VALUES (:data) ON CONFLICT ((data->>'$id')) DO UPDATE SET data = EXCLUDED.data";
    }

    /**
     * Query to update a document
     *
     * @param string $tableName The name of the table in which the document should be updated
     * @return string The UPDATE query for the document
     * @throws DocumentException If the database mode has not been set
     */
    public static function update(string $tableName): string
    {
        return "UPDATE $tableName SET data = :data WHERE " . self::whereById();
    }
}