Vectorface\Cache\SQLCache

Complexity

Total Complexity

49

Size/Duplication

Total Lines	355
Duplicated Lines	0 %

Importance

Changes	3
Bugs	0	Features	0

15 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	3	1
A	clear()	0	3	1
A	setMultiple()	0	7	3
A	getStatement()	0	6	2
A	getMultiple()	0	27	5
A	deleteMultiple()	0	18	4
A	clean()	0	8	2
A	get()	0	13	3
A	has()	0	11	3
A	flush()	0	8	2
B	decrement()	0	30	7
A	hashKey()	0	3	2
A	delete()	0	9	2
B	increment()	0	30	7
A	set()	0	21	5

<?php
/** @noinspection SqlResolve */
/** @noinspection SqlWithoutWhere */
/** @noinspection SqlNoDataSourceInspection */
/** @noinspection PhpComposerExtensionStubsInspection */

namespace Vectorface\Cache;

use PDO;
use PDOStatement;
use PDOException;
use Vectorface\Cache\Common\PSR16Util;

/**
 * This cache is slow, according to basic benchmarks:
 *
 * Parameters:
 *   MySQL 5.0, running locally
 *   9-byte key
 *   151-byte value
 *   10000-iteration test
 *
 * Result:
 *   16.7824881077 seconds
 *
 * Conclusion:
 *   Capable of approximately 595.85 requests/second
 */

/**
 * A cache implementation that uses SQL for storage.
 *
 * An example table might look like:
 * CREATE TABLE cache (
 *     entry VARCHAR(64) PRIMARY KEY NOT NULL,
 *     value LONGBLOB,
 *     expires BIGINT UNSIGNED DEFAULT NULL,
 *     KEY expires (expires)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 */
class SQLCache implements Cache, AtomicCounter
{
    use PSR16Util;

    /**
     * Hash keys beyond this size
     */
    const MAX_KEY_LEN = 64;

    /**
     * Statement for flushing all entries from the cache.
     */
    const FLUSH_SQL = 'DELETE FROM cache';

    /**
     * Statement for deleting expired entries from the cache.
     */
    const CLEAN_SQL = 'DELETE FROM cache WHERE expires<=UNIX_TIMESTAMP()';

    /**
     * Statement for inserting or updating entries in the cache.
     */
    const SET_SQL = 'INSERT INTO cache (entry,value,expires) VALUES(?,?,?)';

    /**
     * Statement for updating if an entry already exists.
     */
    const UPDATE_SQL = 'UPDATE cache SET value=?, expires=? WHERE entry=?';

    /**
     * Statement for checking if an entry exists
     */
    const HAS_SQL = 'SELECT COUNT(*) AS num FROM cache WHERE entry=? AND expires>=UNIX_TIMESTAMP()';

    /**
     * Statement for retrieving an entry from the cache
     */
    const GET_SQL = 'SELECT value FROM cache WHERE entry=? AND expires>=UNIX_TIMESTAMP()';

    /**
     * Statement for retrieving entries from the cache (no statement caching)
     */
    const MGET_SQL = 'SELECT entry,value FROM cache WHERE entry IN(%s) AND expires>=UNIX_TIMESTAMP()';

    /**
     * Statement for deleting an entry from the cache
     */
    const DELETE_SQL = 'DELETE FROM cache WHERE entry=?';

    /**
     * Statement for deleting entries from the cache (no statement caching)
     */
    const MDELETE_SQL = 'DELETE FROM cache WHERE entry IN(%s)';

    /**
     * The database connection to be used for cache operations.
     *
     * @var PDO
     */
    private $conn;

    /**
     * An associative array of PDO statements used in get/set.
     *
     * @var PDOStatement
     */
    private $statements = [];

    /**
     * Create an instance of the SQL cache.
     *
     * @param PDO $conn The database connection to use for cache operations.
     */
    public function __construct(PDO $conn)
    {
        $this->conn = $conn;
    }

    /**
     * @inheritDoc
     */
    public function get($key, $default = null)
    {
        $key = $this->key($key);
        $key = $this->hashKey($key);

        try {
            $stmt = $this->getStatement(__METHOD__, self::GET_SQL);
            $stmt->execute([$key]);
        } catch (PDOException $e) {
            return $default;
        }
        $result = $stmt->fetchColumn();
        return empty($result) ? $default : unserialize($result);
    }

    /**
     * @inheritDoc
     */
    public function getMultiple($keys, $default = null)
    {
        if (empty($keys)) {
            return [];
        }

        $keys = $this->keys($keys);
        $sqlKeys = array_map([$this, 'hashKey'], $keys);

        try {
            $stmt = $this->conn->prepare(sprintf(
                self::MGET_SQL,
                implode(',', array_fill(0, count($keys), '?'))
            ));
            $stmt->execute($sqlKeys);
            $result = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
        } catch (PDOException $e) {
            $result = [];
        }

        $return = array_map('unserialize', $result);
        foreach ($keys as $key) {
            if (!isset($return[$key])) {
                $return[$key] = $default;
            }
        }
        return $return;
    }


    /**
     * @inheritDoc
     */
    public function set($key, $value, $ttl = null)
    {
        $key = $this->key($key);
        $key = $this->hashKey($key);
        $ttl = $this->ttl($ttl);
        $ttl = $ttl ? ($ttl + time()) : PHP_INT_MAX;
        $value = serialize($value);

        try {
            $stmt = $this->getStatement(__METHOD__ . ".insert", self::SET_SQL);
            return $stmt->execute([$key, $value, $ttl]);
        } catch (PDOException $e) {
            // Insert can fail if the entry exists; It's normal.
        }

        try {
            $stmt = $this->getStatement(__METHOD__ . ".update", self::UPDATE_SQL);
            $success = $stmt->execute([$value, $ttl, $key]);
            return $success && $stmt->rowCount() === 1;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * @inheritDoc
     */
    public function setMultiple($values, $ttl = null)
    {
        $success = true;
        foreach ($this->values($values) as $key => $value) {
            $success = $this->set($key, $value, $ttl) && $success;
        }
        return $success;
    }

    /**
     * @inheritDoc
     */
    public function delete($key)
    {
        $key = $this->hashKey($this->key($key));

        try {
            $stmt = $this->getStatement(__METHOD__, self::DELETE_SQL);
            return $stmt->execute([$key]);
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * @inheritDoc
     */
    public function deleteMultiple($keys)
    {
        if (empty($keys)) {
            return true;
        }

        $keysArray = array_map([$this, 'hashKey'], is_array($keys) ? $keys : iterator_to_array($keys));

        try {
            $stmt = $this->conn->prepare(sprintf(
                self::MDELETE_SQL,
                implode(',', array_fill(0, count($keysArray), '?'))
            ));
            $stmt->execute($keysArray);
        } catch (PDOException $e) {
            return false;
        }
        return true;
    }

    /**
     * @inheritdoc
     */
    public function clean()
    {
        try {
            $this->conn->exec(self::CLEAN_SQL);
        } catch (PDOException $e) {
            return false;
        }
        return true;
    }

    /**
     * @inheritdoc
     */
    public function flush()
    {
        try {
            $this->conn->exec(self::FLUSH_SQL);
        } catch (PDOException $e) {
            return false;
        }
        return true;
    }

    /**
     * @inheritDoc
     */
    public function clear()
    {
        return $this->flush();
    }

    /**
     * @inheritDoc
     */
    public function has($key)
    {
        $key = $this->hashKey($key);

        try {
            $stmt = $this->getStatement(__METHOD__, self::HAS_SQL);
            $stmt->execute([$key]);
        } catch (PDOException $e) {
            return false;
        }
        return $stmt->fetchColumn() ? true : false;
    }

    /**
     * @inheritDoc
     * @throws Exception\CacheException
     */
    public function increment($key, $step = 1, $ttl = null)
    {
        $step = $this->step($step);

        try {
            $result = $this->conn->beginTransaction();
            if (!$result) {
                return false;
            }

            $current = $this->get($key);
            $next = ($current ?? 0) + $step;
            $result = $this->set($key, $next, ($current === null ? $ttl : null));
            if (!$result) {
                $this->conn->rollBack();
                return false;
            }

            $result = $this->conn->commit();
            if (!$result) {
                return false;
            }
        } catch (PDOException $e) {
            if ($this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            return false;
        }

        return $next;
    }

    /**
     * @inheritDoc
     * @throws Exception\CacheException
     */
    public function decrement($key, $step = 1, $ttl = null)
    {
        $step = $this->step($step);

        try {
            $result = $this->conn->beginTransaction();
            if (!$result) {
                return false;
            }

            $current = $this->get($key);
            $next = ($current ?? 0) - $step;
            $result = $this->set($key, $next, ($current === null ? $ttl : null));
            if (!$result) {
                $this->conn->rollBack();
                return false;
            }

            $result = $this->conn->commit();
            if (!$result) {
                return false;
            }
        } catch (PDOException $e) {
            if ($this->conn->inTransaction()) {
                $this->conn->rollBack();
            }
            return false;
        }

        return $next;
    }

    /**
     * Get a prepared statement for the given method's SQL.
     *
     * The result is stored internally to limit repeated preparing of SQL.
     *
     * @param string $method The method name to for which this statement applies.
     * @param string $sql The SQL statement associated with the given method.
     * @return PDOStatement Returns the prepared statement for the given method.
     */
    private function getStatement($method, $sql)
    {
        if (empty($this->statements[$method])) {
            $this->statements[$method] = $this->conn->prepare($sql);
        }
        return $this->statements[$method];
    }

    /**
     * Get a unique hash key when the key is too long
     *
     * @param string $key
     * @return string The key, or the hash of the key parameter if it goes beyond maximum length
     * @private Public for testing
     */
    public static function hashKey($key)
    {
        return (strlen($key) > self::MAX_KEY_LEN) ? hash('sha256', $key) : $key;
    }
}