SQLCache::decrement() - Code Metrics - Inspection of "Don't set eternal TTL when incrementing in SQLCach..." - Vectorface/cache - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — master ( 84d05b...6bfc92 )

by Francis

created 2024-05-10 14:46 UTC

SQLCache::decrement() A

↳ Parent: SQLCache

Complexity

Conditions	1
Paths	1

Size

Total Lines	3
Code Lines	1

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	1
CRAP Score	1

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	1
c	1
b	0
f	0
dl	0
loc	3
ccs	1
cts	1
cp	1
rs	10
cc	1
nc	1
nop	3
crap	1

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

namespace Vectorface\Cache;

use DateInterval;
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 atomically updating a stored count if an entry already exists.
     */
    const UPDATE_INCREMENT_SQL = 'UPDATE cache SET value = ? 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)';

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

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

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

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

    /**
     * @inheritDoc
     */
    public function getMultiple(iterable $keys, mixed $default = null) : iterable
    {
        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) {
            $result = [];
        }

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


    /**
     * @inheritDoc
     */
    public function set(string $key, mixed $value, DateInterval|int|null $ttl = null) : bool
    {
        $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) {
            // 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) {
            return false;
        }
    }

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

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

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

    /**
     * @inheritDoc
     */
    public function deleteMultiple(iterable $keys) : bool
    {
        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) {
            return false;
        }
        return true;
    }

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

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

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

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

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

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

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

            $current = $this->get($key);
            $next = ($current ?? 0) + $step;
            if ($current !== null) {
                $stmt = $this->getStatement(__METHOD__, self::UPDATE_INCREMENT_SQL);
                $result = $stmt->execute([serialize($next), $key]) && $stmt->rowCount() === 1;
            } else {
                $result = $this->set($key, $next, $ttl);
            }
            if (!$result) {
                $this->conn->rollBack();
                return false;
            }

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

    /**
     * @inheritDoc
     * @throws Exception\CacheException
     */
    public function decrement(string $key, int $step = 1, DateInterval|int|null $ttl = null) : int|false
    {
        return $this->increment($key, -$step, $ttl);
    }

    /**
     * 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(string $method, string $sql) : PDOStatement
    {
        return $this->statements[$method] ??= $this->conn->prepare($sql);
    }

    /**
     * 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(string $key) : string
    {
        return (strlen($key) > self::MAX_KEY_LEN) ? hash('sha256', $key) : $key;
    }
}


1		<?php
2		/** @noinspection SqlResolve */
3		/** @noinspection SqlWithoutWhere */
4		/** @noinspection SqlNoDataSourceInspection */
5		/** @noinspection PhpComposerExtensionStubsInspection */
6
7		namespace Vectorface\Cache;
8
9		use DateInterval;
10		use PDO;
11		use PDOStatement;
12		use PDOException;
13		use Vectorface\Cache\Common\PSR16Util;
14
15		/**
16		* This cache is slow, according to basic benchmarks:
17		*
18		* Parameters:
19		* MySQL 5.0, running locally
20		* 9-byte key
21		* 151-byte value
22		* 10000-iteration test
23		*
24		* Result:
25		* 16.7824881077 seconds
26		*
27		* Conclusion:
28		* Capable of approximately 595.85 requests/second
29		*/
30
31		/**
32		* A cache implementation that uses SQL for storage.
33		*
34		* An example table might look like:
35		* CREATE TABLE cache (
36		* entry VARCHAR(64) PRIMARY KEY NOT NULL,
37		* value LONGBLOB,
38		* expires BIGINT UNSIGNED DEFAULT NULL,
39		* KEY expires (expires)
40		* ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
41		*/
42		class SQLCache implements Cache, AtomicCounter
43		{
44		use PSR16Util;
45
46		/**
47		* Hash keys beyond this size
48		*/
49		const MAX_KEY_LEN = 64;
50
51		/**
52		* Statement for flushing all entries from the cache.
53		*/
54		const FLUSH_SQL = 'DELETE FROM cache';
55
56		/**
57		* Statement for deleting expired entries from the cache.
58		*/
59		const CLEAN_SQL = 'DELETE FROM cache WHERE expires <= UNIX_TIMESTAMP()';
60
61		/**
62		* Statement for inserting or updating entries in the cache.
63		*/
64		const SET_SQL = 'INSERT INTO cache (entry, value, expires) VALUES(?, ?, ?)';
65
66		/**
67		* Statement for updating if an entry already exists.
68		*/
69		const UPDATE_SQL = 'UPDATE cache SET value = ?, expires = ? WHERE entry = ?';
70
71		/**
72		* Statement for atomically updating a stored count if an entry already exists.
73		*/
74		const UPDATE_INCREMENT_SQL = 'UPDATE cache SET value = ? WHERE entry = ?';
75
76		/**
77		* Statement for checking if an entry exists
78		*/
79		const HAS_SQL = 'SELECT COUNT(*) AS num FROM cache WHERE entry = ? AND expires >= UNIX_TIMESTAMP()';
80
81		/**
82		* Statement for retrieving an entry from the cache
83		*/
84		const GET_SQL = 'SELECT value FROM cache WHERE entry = ? AND expires >= UNIX_TIMESTAMP()';
85
86		/**
87		* Statement for retrieving entries from the cache (no statement caching)
88		*/
89		const MGET_SQL = 'SELECT entry, value FROM cache WHERE entry IN(%s) AND expires >= UNIX_TIMESTAMP()';
90
91		/**
92		* Statement for deleting an entry from the cache
93		*/
94		const DELETE_SQL = 'DELETE FROM cache WHERE entry = ?';
95
96		/**
97		* Statement for deleting entries from the cache (no statement caching)
98		*/
99		const MDELETE_SQL = 'DELETE FROM cache WHERE entry IN(%s)';
100
101		/**
102		* An associative array of PDO statements used in get/set.
103		*
104		* @var PDOStatement[]
105		*/
106		private array $statements = [];
107
108		/**
109		* Create an instance of the SQL cache.
110		*
111		* @param PDO $conn The database connection to use for cache operations.
112		*/
113		public function __construct(
114	21	private PDO $conn,
115		) {}
116	21
117	21	/**
118		* @inheritDoc
119		*/
120		public function get(string $key, mixed $default = null) : mixed
121		{
122	17	$key = $this->key($key);
123		$key = $this->hashKey($key);
124	17
125	16	try {
126		$stmt = $this->getStatement(__METHOD__, self::GET_SQL);
127		$stmt->execute([$key]);
128	16	} catch (PDOException) {
129	16	return $default;
130	1	}
131	1	$result = $stmt->fetchColumn();
132		if (empty($result)) {
133	16	return $default;
134	16	}
135		return unserialize($result);
136		}
137
138		/**
139		* @inheritDoc
140	4	*/
141		public function getMultiple(iterable $keys, mixed $default = null) : iterable
142	4	{
143	1	if (empty($keys)) {
144		return [];
145		}
146	4
147	3	$keys = $this->keys($keys);
148		$sqlKeys = array_map([$this, 'hashKey'], $keys);
149
150	3	try {
151	3	$stmt = $this->conn->prepare(sprintf(
152	3	self::MGET_SQL,
153		implode(',', array_fill(0, count($keys), '?'))
154	2	));
155	2	$stmt->execute($sqlKeys);
156	1	$result = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
157	1	} catch (PDOException) {
158		$result = [];
159		}
160	3
161	3	$return = array_map('unserialize', $result);
162	3	foreach ($keys as $key) {
163	3	$return[$key] ??= $default;
164		}
165		return $return;
166	3	}
167
168
169		/**
170		* @inheritDoc
171		*/
172		public function set(string $key, mixed $value, DateInterval\|int\|null $ttl = null) : bool
173	20	{
174		$key = $this->key($key);
175	20	$key = $this->hashKey($key);
176	20	$ttl = $this->ttl($ttl);
177	20	$ttl = $ttl ? ($ttl + time()) : PHP_INT_MAX;
178	19	$value = serialize($value);
179	19
180		try {
181		$stmt = $this->getStatement(__METHOD__ . ".insert", self::SET_SQL);
182	19	return $stmt->execute([$key, $value, $ttl]);
183	19	} catch (PDOException) {
184	5	// Insert can fail if the entry exists; It's normal.
185		}
186
187		try {
188		$stmt = $this->getStatement(__METHOD__ . ".update", self::UPDATE_SQL);
189	5	$success = $stmt->execute([$value, $ttl, $key]);
190	5	return $success && $stmt->rowCount() === 1;
191	5	} catch (PDOException) {
192	1	return false;
193	1	}
194		}
195
196		/**
197		* @inheritDoc
198		*/
199		public function setMultiple(iterable $values, DateInterval\|int\|null $ttl = null) : bool
200	3	{
201		$success = true;
202	3	foreach ($this->values($values) as $key => $value) {
203	3	$success = $this->set($key, $value, $ttl) && $success;
204	2	}
205		return $success;
206	2	}
207
208		/**
209		* @inheritDoc
210		*/
211		public function delete(string $key) : bool
212	5	{
213		$key = $this->hashKey($this->key($key));
214	5
215		try {
216		$stmt = $this->getStatement(__METHOD__, self::DELETE_SQL);
217	5	return $stmt->execute([$key]);
218	5	} catch (PDOException) {
219	1	return false;
220	1	}
221		}
222
223		/**
224		* @inheritDoc
225		*/
226		public function deleteMultiple(iterable $keys) : bool
227	3	{
228		if (empty($keys)) {
229	3	return true;
230	1	}
231
232		$keysArray = array_map([$this, 'hashKey'], is_array($keys) ? $keys : iterator_to_array($keys));
233	3
234		try {
235		$stmt = $this->conn->prepare(sprintf(
236	3	self::MDELETE_SQL,
237	3	implode(',', array_fill(0, count($keysArray), '?'))
238	3	));
239		$stmt->execute($keysArray);
240	2	} catch (PDOException) {
241	1	return false;
242	1	}
243		return true;
244	2	}
245
246		/**
247		* @inheritdoc
248		*/
249		public function clean() : bool
250	2	{
251		try {
252		$this->conn->exec(self::CLEAN_SQL);
253	2	} catch (PDOException) {
254	1	return false;
255	1	}
256		return true;
257	2	}
258
259		/**
260		* @inheritdoc
261		*/
262		public function flush() : bool
263	7	{
264		try {
265		$this->conn->exec(self::FLUSH_SQL);
266	7	} catch (PDOException) {
267	1	return false;
268	1	}
269		return true;
270	7	}
271
272		/**
273		* @inheritDoc
274		*/
275		public function clear() : bool
276	3	{
277		return $this->flush();
278	3	}
279
280		/**
281		* @inheritDoc
282		*/
283		public function has(string $key) : bool
284	2	{
285		$key = $this->hashKey($key);
286	2
287		try {
288		$stmt = $this->getStatement(__METHOD__, self::HAS_SQL);
289	2	$stmt->execute([$key]);
290	1	} catch (PDOException) {
291	1	return false;
292	1	}
293		return (bool)$stmt->fetchColumn();
294	1	}
295
296		/**
297		* @inheritDoc
298		* @throws Exception\CacheException
299		*/
300		public function increment(string $key, int $step = 1, DateInterval\|int\|null $ttl = null) : int\|false
301	1	{
302		$step = $this->step($step);
303	1
304		try {
305		$result = $this->conn->beginTransaction();
306	1	if (!$result) {
307	1	return false;
308		}
309
310		$current = $this->get($key);
311	1	$next = ($current ?? 0) + $step;
312	1	if ($current !== null) {
313	1	$stmt = $this->getStatement(__METHOD__, self::UPDATE_INCREMENT_SQL);
314	1	$result = $stmt->execute([serialize($next), $key]) && $stmt->rowCount() === 1;
315		} else {
316		$result = $this->set($key, $next, $ttl);
317		}
318		if (!$result) {
319	1	$this->conn->rollBack();
320	1	return false;
321	1	}
322
323		$result = $this->conn->commit();
324		if (!$result) {
325		return false;
326		}
327		return $next;
328		} catch (PDOException) {
329		if ($this->conn->inTransaction()) {
330	1	$this->conn->rollBack();
331		}
332		return false;
333		}
334		}
335
336		/**
337	1	* @inheritDoc
338		* @throws Exception\CacheException
339	1	*/
340		public function decrement(string $key, int $step = 1, DateInterval\|int\|null $ttl = null) : int\|false
341		{
342	1	return $this->increment($key, -$step, $ttl);
343	1	}
344
345		/**
346		* Get a prepared statement for the given method's SQL.
347	1	*
348	1	* The result is stored internally to limit repeated preparing of SQL.
349	1	*
350	1	* @param string $method The method name to for which this statement applies.
351		* @param string $sql The SQL statement associated with the given method.
352		* @return PDOStatement Returns the prepared statement for the given method.
353		*/
354		private function getStatement(string $method, string $sql) : PDOStatement
355	1	{
356	1	return $this->statements[$method] ??= $this->conn->prepare($sql);
357	1	}
358
359		/**
360		* Get a unique hash key when the key is too long
361		*
362		* @param string $key
363		* @return string The key, or the hash of the key parameter if it goes beyond maximum length
364		* @private Public for testing
365		*/
366	1	public static function hashKey(string $key) : string
367		{
368		return (strlen($key) > self::MAX_KEY_LEN) ? hash('sha256', $key) : $key;
369		}
370		}
371

Vectorface / cache

Push — master ( 84d05b...6bfc92 )

SQLCache::decrement() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like