Database.php

<?php

declare(strict_types=1);


/**
 * Gazelle\Database
 *
 * The blunt singleton, for your procedural code.
 *
 * @see https://phpdelusions.net/pdo/pdo_wrapper
 * @see https://github.com/DoctorMcKay/php-mypdoms
 *
 * todo: consider updating this to use RecursiveCollection instances
 */

namespace Gazelle;

class Database extends \PDO
{
    # instance
    private static ?self $instance = null;

    # database meta
    public \PDO $source;
    public array $replicas = [];
    public \PDO $last;

    # cache settings
    private string $cachePrefix = "database:";
    private string $cacheDuration = "1 minute";

    # bitmask of json_encode flags
    private int $jsonFlags = JSON_INVALID_UTF8_SUBSTITUTE;

    # commands that should only hit the source
    private array $sourceCommands = [
        "alter",
        "create",
        "delete",
        "drop",
        "insert",
        "load",
        "rename",
        "replace",
        "truncate",
        "update",
    ];


    /**
     * __functions
     */
    public function __construct()
    {
        return;
    }

    public function __clone()
    {
        return trigger_error(
            "clone not allowed",
            E_USER_ERROR
        );
    }

    public function __wakeup()
    {
        return trigger_error(
            "wakeup not allowed",
            E_USER_ERROR
        );
    }


    /** */


    /**
     * go
     *
     * @param array $options the options to use
     * @return self
     */
    public static function go(array $options = []): self
    {
        if (!self::$instance) {
            self::$instance = new self();
            self::$instance->factory($options);
        }

        return self::$instance;
    }


    /**
     * factory
     *
     * @param array $options the options to use
     * @return void
     */
    private function factory(array $options = []): void
    {
        $app = App::go();

        # don't cache on dev
        if ($app->env->dev) {
            $this->cacheDuration = "0 seconds";
        }

        # database variables
        $source = $app->env->private("databaseSource");
        $replicas = $app->env->private("databaseReplicas");

        # default options
        $defaultOptions = [
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
            \PDO::ATTR_EMULATE_PREPARES => false,
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        ];

        # merge with any options passed
        $options = array_replace($defaultOptions, $options);

        # construct the source dsn
        $dsn = "mysql:host={$source->host};dbname={$source->database};port={$source->port};charset={$source->charset}";
        if ($source->socket) {
            $dsn = str_replace("port={$source->port}", "unix_socket={$source->socket}", $dsn);
        }

        try {
            # try to instantiate the source
            $this->source = new \PDO($dsn, $source->username, $source->passphrase, $options);
        } catch (\Throwable $e) {
            throw new Exception($e->getMessage());
        }

        # set the last host to the source
        $this->last = $this->source;

        # bail out if replicas aren't defined
        if (!$app->env->enableDatabaseReplication || empty($replicas)) {
            return;
        }

        # set up the replicas
        foreach ($replicas as $key => $replica) {
            # construct the replica dsn
            $dsn = "mysql:host={$replica->host};dbname={$replica->database};port={$replica->port};charset={$replica->charset}";
            if ($replica->socket) {
                $dsn = str_replace("port={$replica->port}", "unix_socket={$replica->socket}", $dsn);
            }

            try {
                # try to instantiate the replica
                $this->replicas[$key] = new \PDO($dsn, $replica->username, $replica->passphrase, $options);
            } catch (\Throwable $e) {
                throw new Exception($e->getMessage());
            }
        }
    }


    /**
     * determineHost
     *
     * Determine the host to use for a query.
     *
     * @param string $query the query to be executed
     * @param ?string $hostname the host to use
     * @return \PDO the host to use
     */
    private function determineHost(string $query = "", ?string $hostname = null): \PDO
    {
        # force certain queries to use the source
        if (preg_match("/^(" . implode("|", $this->sourceCommands) . ")/i", $query)) {
            $this->last = $this->source;
            return $this->last;
        }

        # check for a specific host
        if ($hostname === "source") {
            $this->last = $this->source;
            return $this->last;
        }

        if ($hostname === "last") {
            $this->last = $this->last;
            return $this->last;
        }

        if (in_array($hostname, array_keys($this->replicas))) {
            $this->last = $this->replicas[$hostname];
            return $this->last;
        }

        # pick a random replica
        if (!empty($this->replicas)) {
            $this->last = $this->replicas[array_rand($this->replicas)];
            return $this->last;
        }

        # default to the source
        $this->last = $this->source;
        return $this->last;
    }


    /**
     * now
     *
     * Return an SQL datetime string.
     * Defaults to the current time.
     *
     * @param ?int $timestamp
     * @return string
     */
    public function now(?int $timestamp = null): string
    {
        return date(
            "Y-m-d H:i:s",
            $timestamp ?? time()
        );
    }


    /** identifiers */


    /**
     * binaryUuid
     *
     * Generate a unique id suitable for a database key.
     *
     * @return string uuid v7 binary
     *
     * @see https://uuid.ramsey.dev/en/stable/rfc4122/version7.html
     * @see https://uuid.ramsey.dev/en/stable/database.html
     */
    public function binaryUuid(): string
    {
        return \Ramsey\Uuid\Uuid::uuid7()->getBytes();
    }


    /**
     * stringUuid
     *
     * Get the string representation of a binary uuid.
     *
     * @return string uuid v7 string
    */
    public function stringUuid(): string
    {
        return \Ramsey\Uuid\Uuid::uuid7()->getBytes()->toString();
    }


    /**
     * shortUuid
     *
     * Generate a short uuid.
     *
     * @return string e.g., "100455158982377479"
     *
     * @see https://mariadb.com/kb/en/uuid_short/
     */
    public function shortUuid(): string
    {
        # prevent query caching
        $query = "select uuid_short()";
        $options = [ "preventCache" => bin2hex(random_bytes(16)) ];

        return strval($this->single($query, [], [$options]));
    }


    /**
     * slug
     *
     * @param ?string $string
     * @return string
     *
     * @see https://laravel.com/api/master/Illuminate/Support/Str.html#method_slug
     */
    public function slug(?string $string): string
    {
        return \Illuminate\Support\Str::slug($string);
    }


    /**
     * determineId
     *
     * Determine the identifier to use for a query.
     * Used for finding stuff by id, uuid, or slug.
     *
     * @param int|string $id
     * @param bool $insteadExtractId returns the unique part of the id instead of the column name
     * @return string
     */
    public function determineId(int|string $id, bool $insteadExtractId = false): string
    {
        $app = App::go();

        # cast to string
        $id = urldecode(strval($id));

        # normal numeric id
        $good = preg_match("/{$app->env->regexShortUuid}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "id" : $id);
        }

        # doi
        $good = preg_match("/{$app->env->regexDoi}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "doi" : $matches[0]);
        }

        # info hash
        $good = preg_match("/{$app->env->regexInfoHash}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "info_hash" : $matches[0]);
        }

        # issn
        $good = preg_match("/{$app->env->regexIssn}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "issn" : $matches[0]);
        }

        # openAlex
        $good = preg_match("/{$app->env->regexOpenAlex}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "openAlexId" : $matches[0]);
        }

        # orcid
        $good = preg_match("/{$app->env->regexOrcid}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "orcid" : $matches[0]);
        }

        # rorId: collides with id
        $good = preg_match("/{$app->env->regexRor}/i", $id, $matches);
        if ($good && strlen($id) !== 18) {
            return (!$insteadExtractId ? "rorId" : $matches[0]);
        }

        # default id
        return (!$insteadExtractId ? "id" : $id);

        /*
        # normal numeric id
        $good = preg_match("/{$app->env->regexShortUuid}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "id" : $id);
        }

        # https://ihateregex.io/expr/uuid/
        if (is_string($id) && strlen($id) === 36 && preg_match("/{$app->env->regexUuid}/iD", $id)) {
            return "uuid";
        }

        # is it binary?
        if (Text::isBinary($id) && strlen($id) === 16) {
            return "uuid";
        }

        # semantic scholar author
        $good = preg_match("/{$app->env->regexSemanticScholarAuthor}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "semanticScholarId" : $matches[0]);
        }

        # semantic scholar paper
        $good = preg_match("/{$app->env->regexSemanticScholarPaper}/i", $id, $matches);
        if ($good) {
            return (!$insteadExtractId ? "semanticScholarId" : $matches[0]);
        }
        */
    }


    /**
     * extractId
     *
     * Extract the unique part of an id.
     *
     * @param int|string $id
     * @return string
     */
    public function extractId(int|string $id): string
    {
        return $this->determineId($id, true);
    }


    /**
     * fullId
     *
     * Get the full id from a unique part.
     *
     * @param int|string $id
     * @return string
     */
    public function fullId(int|string $id): string
    {
        $column = $this->determineId($id);
        $uniqueId = $this->extractId($id);

        return match ($column) {
            "doi" => "https://doi.org/{$uniqueId}",
            "openAlexId" => "https://openalex.org/{$uniqueId}",
            "orcid" => "https://orcid.org/{$uniqueId}",
            "rorId" => "https://ror.org/{$uniqueId}",
            default => strval($id),
        };
    }


    /**
     * translateBinary
     *
     * Translates a binary field to a string representation.
     *
     * @param array $row single database row
     * @return array translated row
     */
    private function translateBinary(array $row): array
    {
        /*
        # uuid v7
        $row["uuid"] ??= null;
        if ($row["uuid"]) {
            $row["uuid"] = $this->stringUuid($row["uuid"]);
        } else {
            unset($row["uuid"]);
        }
        */

        # webauthn
        $row["aaguid"] ??= null;
        if ($row["aaguid"]) {
            $row["aaguid"] = $this->stringUuid($row["aaguid"]);
        } else {
            unset($row["aaguid"]);
        }

        /*
        # peer_id
        $row["peer_id"] ??= null;
        if ($row["peer_id"]) {
            $row["peer_id"] = bin2hex($row["peer_id"]);
        } else {
            unset($row["peer_id"]);
        }
        */

        # infoHash
        $row["infoHash"] ??= null;
        if ($row["infoHash"]) {
            $row["infoHash"] = bin2hex($row["infoHash"]);
        } else {
            unset($row["infoHash"]);
        }

        # legacy PascalCase
        $row["InfoHash"] ??= null;
        if ($row["InfoHash"]) {
            $row["InfoHash"] = bin2hex($row["InfoHash"]);
        } else {
            unset($row["InfoHash"]);
        }

        # info_hash
        $row["info_hash"] ??= null;
        if ($row["info_hash"]) {
            $row["info_hash"] = bin2hex($row["info_hash"]);
        } else {
            unset($row["info_hash"]);
        }

        return $row;
    }


    /** query operations */


    /**
     * do
     *
     * For update, insert, etc.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return \PDOStatement
     */
    public function do(string $query, array $parameters = [], array $options = []): \PDOStatement
    {
        $app = App::go();

        # determine the host
        $hostname = $options["hostname"] ?? null;
        $host = $this->determineHost($query, $hostname);

        # prepare
        $statement = $host->prepare($query);

        # debug: before the first return
        if ($app->env->dev) {
            $app->debug["database"]->log(
                $this->meta()
            );
        }

        # no params
        if (empty($parameters)) {
            return $host->query($query);
        }

        /*
        # https://ihateregex.io/expr/uuid/
        foreach ($parameters as $key => $value) {
            if (is_string($value) && strlen($value) === 36 && preg_match("/{$app->env->regexUuid}/iD", $value)) {
                $parameters[$key] = $this->binaryUuid($value);
            }
        }
        */

        # execute
        $statement->execute($parameters);

        # errors
        $errors = $host->errorInfo();
        if ($errors[0] !== "00000") {
            throw new Exception("{$errors[0]}: {$errors[2]}");
        }

        # good
        return $statement;
    }


    /**
     * single
     *
     * Gets a single value.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return mixed
     */
    public function single(string $query, array $parameters = [], array $options = []): mixed
    {
        $app = App::go();

        $cacheKey = $this->cachePrefix . hash($app->env->cacheAlgorithm, json_encode([$query, $parameters, $options], $this->jsonFlags));
        $cacheHit = $app->cache->get($cacheKey);

        if ($cacheHit) {
            return $cacheHit;
        }

        $statement = $this->do($query, $parameters, $options);
        $ref = $statement->fetchAll();

        if (!$ref) {
            return null;
        }

        foreach ($ref as $row) {
            # translate binary
            $row = $this->translateBinary($row);

            foreach ($row as $key => $value) {
                $app->cache->set($cacheKey, $value, $this->cacheDuration);
                return $value;
            }
        }
    }


    /**
     * row
     *
     * Gets a single row.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return array
     */
    public function row(string $query, array $parameters = [], array $options = []): array
    {
        $app = App::go();

        $cacheKey = $this->cachePrefix . hash($app->env->cacheAlgorithm, json_encode([$query, $parameters, $options], $this->jsonFlags));
        $cacheHit = $app->cache->get($cacheKey);

        if ($cacheHit) {
            return $cacheHit;
        }

        $statement = $this->do($query, $parameters, $options);
        $ref = $statement->fetchAll();

        if (!$ref) {
            return [];
        }

        foreach ($ref as $row) {
            # translate binary
            $row = $this->translateBinary($row);

            $app->cache->set($cacheKey, $row, $this->cacheDuration);
            return $row;
        }
    }


    /**
     * column
     *
     * Gets a single column.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return array
     */
    public function column(string $query, array $parameters = [], array $options = []): array
    {
        $app = App::go();

        $cacheKey = $this->cachePrefix . hash($app->env->cacheAlgorithm, json_encode([$query, $parameters, $options], $this->jsonFlags));
        $cacheHit = $app->cache->get($cacheKey);

        if ($cacheHit) {
            return $cacheHit;
        }

        # get all rows to process
        $ref = $this->multi($query, $parameters, $options);

        if (!$ref) {
            return [];
        }

        foreach ($ref as $key => $row) {
            if (count($row) !== 1) {
                throw new Exception("column query returned more than one column");
            }

            # translate binary
            $ref[$key] = $this->translateBinary($row);
        }

        # flatten to a single array
        $column = \Illuminate\Support\Arr::flatten($ref);

        $app->cache->set($cacheKey, $column, $this->cacheDuration);
        return $column;
    }


    /**
     * multi
     *
     * Gets all results.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return array
     */
    public function multi(string $query, array $parameters = [], array $options = []): array
    {
        $app = App::go();

        $cacheKey = $this->cachePrefix . hash($app->env->cacheAlgorithm, json_encode([$query, $parameters, $options], $this->jsonFlags));
        $cacheHit = $app->cache->get($cacheKey);

        if ($cacheHit) {
            return $cacheHit;
        }

        $statement = $this->do($query, $parameters, $options);
        $ref = $statement->fetchAll();

        if (!$ref) {
            return [];
        }

        foreach ($ref as $key => $row) {
            # translate binary
            $ref[$key] = $this->translateBinary($row);
        }

        $app->cache->set($cacheKey, $ref, $this->cacheDuration);
        return $ref;
    }


    /** pseudo orm */


    /**
     * upsert
     *
     * Mass assigns a data array to a table.
     * Similar to Eloquent's updateOrCreate.
     *
     * @param string $table
     * @param array $data
     * @return array
     */
    public function upsert(string $table, array $data = []): array
    {
        # extract the columns and values
        $columns = array_keys($data);
        $values = array_values($data);

        # comma-separated list of columns and named placeholders
        $insertColumns = implode(", ", $columns);
        $insertPlaceholders = ":" . implode(", :", $columns);

        # update column expressions with named placeholders
        $updateColumns = array_map(function ($column) {
            return "{$column} = :{$column}_update";
        }, $columns);

        # named placeholders for the update column values
        $updatePlaceholders = array_map(function ($column) {
            return ":{$column}_update";
        }, $columns);

        /*
        # construct the sql query string for the upsert operation
        $query = "
            replace into {$table} ({$insertColumns})
            values ({$insertPlaceholders})
        ";
        */

        # construct the sql query string for the upsert operation
        $query = "
            insert into {$table} ({$insertColumns})
            values ({$insertPlaceholders})
            on duplicate key update " . implode(", ", $updateColumns);

        # merge the original data array with the update placeholders and their corresponding values
        $data = array_merge($data, array_combine($updatePlaceholders, $values));

        # execute the query with the data
        $statement = $this->do($query, $data);

        # return the newly created or updated row
        $lastInsertId = $this->lastInsertId(); # 0 if not inserted
        if (!empty($lastInsertId)) {
            $query = "select * from {$table} where id = ?";
            return $this->row($query, [$lastInsertId], ["hostname" => "source"]);
        }

        # it was updated, resolve a key from the data
        foreach ($data as $key => $value) {
            if (in_array(strtolower(strval($key)), ["id"])) {
                $column = $this->determineId($value);
                $query = "select * from {$table} where {$column} = ?";
                return $this->row($query, [$value], ["hostname" => "source"]);
            }
        }

        # this should never happen
        throw new Exception("unable to upsert into {$table}");
    }


    /**
     * findOne
     *
     * Finds a single row by a set of contraints.
     *
     * @param string $table
     * @param array $conditions ["column" => "value"]
     * @return array
     */
    public function findOne(string $table, array $conditions = []): array
    {
        # important! trailing whitespace
        $query = "select * from {$table} where ";

        $parameters = [];
        foreach ($conditions as $key => $value) {
            $parameters[] = "{$key} = :{$key}";
        }

        $query .= implode(" and ", $parameters);
        return $this->row($query, $conditions);
    }


    /**
     * findAll
     *
     * Finds all rows by a set of contraints.
     *
     * @param string $table
     * @param array $conditions ["column" => "value"]
     * @return array
     */
    public function findAll(string $table, array $conditions = []): array
    {
        # important! trailing whitespace
        $query = "select * from {$table} where ";

        $parameters = [];
        foreach ($conditions as $key => $value) {
            $parameters[] = "{$key} = :{$key}";
        }

        $query .= implode(" and ", $parameters);
        return $this->multi($query, $conditions);
    }


    /** statement metadata */


    /**
     * lastInsertId
     *
     * Gets the last inserted id.
     * Defaults to the source.
     *
     * @param ?string $name
     * @return string|false
     */
    public function lastInsertId(?string $name = null): string|false
    {
        return $this->source->lastInsertId($name);
    }


    /**
     * rowCount
     *
     * Gets the number of rows.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return int
     */
    public function rowCount(string $query, array $parameters = [], array $options = []): int
    {
        $statement = $this->do($query, $parameters, $options);
        $rowCount = $statement->rowCount();

        return $rowCount;
    }


    /**
     * columnCount
     *
     * Gets the number of columns.
     *
     * @param string $query
     * @param array $parameters
     * @param array $options
     * @return int
     */
    public function columnCount(string $query, array $parameters = [], array $options = []): int
    {
        $statement = $this->do($query, $parameters, $options);
        $columnCount = $statement->columnCount();

        return $columnCount;
    }


    /**
     * meta
     *
     * Gets the query metadata.
     *
     * @param ?\PDOStatement $statement
     * @param ?string $hostname
     * @return array
     */
    public function meta(?\PDOStatement $statement = null, ?string $hostname = null): array
    {
        $host = $this->determineHost("", $hostname);
        $metadata = [];

        /** */

        # https://www.php.net/manual/en/pdo.getattribute.php
        $metadata["attributes"] = [
            "autocommit" => $host->getAttribute(\PDO::ATTR_AUTOCOMMIT),
            "case" => $host->getAttribute(\PDO::ATTR_CASE),
            "clientVersion" => $host->getAttribute(\PDO::ATTR_CLIENT_VERSION),
            "connectionStatus" => $host->getAttribute(\PDO::ATTR_CONNECTION_STATUS),
            "driverName" => $host->getAttribute(\PDO::ATTR_DRIVER_NAME),
            "errmode" => $host->getAttribute(\PDO::ATTR_ERRMODE),
            "oracleNulls" => $host->getAttribute(\PDO::ATTR_ORACLE_NULLS),
            "persistent" => $host->getAttribute(\PDO::ATTR_PERSISTENT),
            "serverInfo" => $host->getAttribute(\PDO::ATTR_SERVER_INFO),
            "serverVersion" => $host->getAttribute(\PDO::ATTR_SERVER_VERSION),
        ];

        /** */

        $metadata["pdo"] = [
            # https://www.php.net/manual/en/pdo.errorcode.php
            "errorCode" => $host->errorCode(),

            # https://www.php.net/manual/en/pdo.errorinfo.php
            "errorInfo" => $host->errorInfo(),

            # https://www.php.net/manual/en/pdo.getavailabledrivers.php
            "availableDrivers" => $host->getAvailableDrivers(),

            # https://www.php.net/manual/en/pdo.intransaction.php
            "inTransaction" => $host->inTransaction(),

            # https://www.php.net/manual/en/pdo.lastinsertid.php
            "lastInsertId" => $host->lastInsertId(),
        ];

        /** */

        # return early if no statement
        if (!$statement) {
            return $metadata;
        }

        /** */

        $metadata["statement"] = [
            # https://www.php.net/manual/en/pdostatement.columncount.php
            "columnCount" => $statement->columnCount(),

            # https://www.php.net/manual/en/pdostatement.debugdumpparams.php
            "debugDumpParams" => $statement->debugDumpParams(),

            # https://www.php.net/manual/en/pdostatement.errorcode.php
            "errorCode" => $statement->errorCode(),

            # https://www.php.net/manual/en/pdostatement.errorinfo.php
            "errorInfo" => $statement->errorInfo(),

            # https://www.php.net/manual/en/pdostatement.getcolumnmeta.php
            #"columnMeta" => $statement->getColumnMeta($todo),

            # https://www.php.net/manual/en/pdostatement.rowcount.php
            "rowCount" => $statement->rowCount(),
        ];

        /** */

        return $metadata;
    }


    /** transaction wrappers */


    /**
     * beginTransaction
     *
     * Defaults to the source.
     *
     * @return bool
     *
     * @see https://www.php.net/manual/en/pdo.begintransaction.php
     */
    public function beginTransaction(): bool
    {
        return $this->source->beginTransaction();
    }


    /**
     * commit
     *
     * Defaults to the source.
     *
     * @return bool
     *
     * @see https://www.php.net/manual/en/pdo.commit.php
     */
    public function commit(): bool
    {
        return $this->source->commit();
    }


    /**
     * rollBack
     *
     * Defaults to the source.
     *
     * @return bool
     *
     * @see https://www.php.net/manual/en/pdo.rollback.php
     */
    public function rollBack(): bool
    {
        return $this->source->rollBack();
    }
} # class