SQLCache::decrement() - Code Metrics - Inspection of "initial redis cache impl" - Vectorface/cache - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — redis ( 69cfbf...5e7e33 )

by Francis

created 2020-12-21 23:37 UTC

SQLCache::decrement() B

↳ Parent: SQLCache

Complexity

Conditions	7
Paths	14

Size

Total Lines	30
Code Lines	19

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
eloc	19
c	1
b	0
f	0
dl	0
loc	30
rs	8.8333
cc	7
nc	14
nop	3

<?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;
    }
}


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

Vectorface / cache

Push — redis ( 69cfbf...5e7e33 )

SQLCache::decrement() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like