PDO::bindPaginationToStatement() - Code Metrics - alkemann/h2l - Measure and Improve Code Quality continuously with Scrutinizer

PDO::bindPaginationToStatement() A
last analyzed 2022-04-08 19:01 UTC

↳ Parent: PDO

Complexity

Conditions	2
Paths	2

Size

Total Lines	5
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	3
nc	2
nop	2
dl	0
loc	5
rs	10
c	0
b	0
f	0

<?php

namespace alkemann\h2l\data;

use alkemann\h2l\exceptions\ConnectionError;
use alkemann\h2l\interfaces\Source;
use alkemann\h2l\Log;
use PDO as _PDO;

/**
 * Class PDO
 *
 * @package alkemann\h2l\data
 */
class PDO implements Source
{
    /**
     * @var array
     */
    protected array $config = [];

    /**
     * @var null|_PDO
     */
    protected $db = null;

    /** @var string */
    private string $pdo_class = _PDO::class;

    /**
     * @param array $config
     * @param string $pdo_class
     */
    public function __construct(array $config = [], string $pdo_class = _PDO::class)
    {
        $this->pdo_class = $pdo_class;

        if (count($config) === 1 && array_key_exists('url', $config)) {
            $config = parse_url($config['url']);
            if (is_array($config)) {

                $config['db'] = ltrim($config['path'] ?? '', '/');
            } else {
                // @codeCoverageIgnoreStart
                // parse_url always returns an array
                $config = [];
                // @codeCoverageIgnoreEnd
            }
        }

        $defaults = [
            'host' => 'localhost',
            'db' => 'test',
            'user' => null,
            'pass' => null
        ];
        $this->config = $config + $defaults;
    }

    /**
     * @psalm-suppress MoreSpecificReturnType
     * @psalm-suppress PropertyTypeCoercion
     * @psalm-suppress LessSpecificReturnStatement
     * @return _PDO
     */
    private function handler(): object
    {
        if ($this->db) {
            return $this->db;
        }

        $scheme = $this->config['scheme'] ?? 'mysql';
        $host = $this->config['host'];
        $db = $this->config['db'];
        $user = $this->config['user'];
        $pass = $this->config['pass'] ?? '';
        $port = ($this->config['port'] ?? false) ? ";port={$this->config['port']}" : '';
        if (empty($this->config['query'])) {
            $modifiers = '';
        } else {
            $modifiers = ';' . str_replace('&', ';', $this->config['query']);
        }
        $opts = [
            _PDO::ATTR_EMULATE_PREPARES => false,
            _PDO::ATTR_ERRMODE => _PDO::ERRMODE_EXCEPTION,

            'useUnicode' => true,
            'characterEncoding' => 'UTF-8',
        ];
        $dsn = "{$scheme}:host={$host}{$port}{$modifiers};dbname={$db}";
        $class = $this->pdo_class;
        try {
            $this->db = new $class($dsn, $user, $pass, $opts);

            // @TODO use this?
            // $this->db->setAttribute( _PDO::ATTR_EMULATE_PREPARES, false);
        } catch (\PDOException $e) {
            throw new ConnectionError("Unable to connect to $host : $db with user $user");
        }
        return $this->db;
    }

    /**
     * @param mixed $query
     * @param array $params
     * @return array|false false on error
     */
    public function query($query, array $params = [])
    {
        Log::debug("PDO:QUERY [$query]");
        $result = $this->handler()->query($query);
        return $result ? $result->fetchAll(_PDO::FETCH_ASSOC) : false;
    }

    /**
     * @param string $table
     * @param array $conditions
     * @param array $options
     * @return null|array
     * @throws \Error on finding more than 1 match
     */
    public function one(string $table, array $conditions, array $options = []): ?array
    {
        $result = $this->find($table, $conditions, $options);
        if ($result instanceof \Traversable) {
            $result = iterator_to_array($result);
        }
        $hits = sizeof($result);
        if ($hits === 0) {
            return null;
        }
        if ($hits > 1) {
            throw new \Error("One request found more than 1 match!");
        }

        return $result[0];
    }

    /**
     * @param string $table
     * @param array $conditions
     * @param array $options
     * @return iterable
     */
    public function find(string $table, array $conditions, array $options = []): iterable
    {
        $where = $this->where($conditions);
        $limit = $this->limit($options);
        $order = $this->order($options);
        $query = "SELECT * FROM {$table} {$where}{$order}{$limit};";
        $params = $this->boundDebugString($conditions, $options);
        Log::debug("PDO:QUERY [$query][$params]");
        $dbh = $this->handler();
        $stmt = $dbh->prepare($query);
        foreach ($conditions as $key => $value) {
            if (is_array($value)) {
                $index = 0;
                foreach ($value as $v) {
                    $i = 1 + $index++;
                    $stmt->bindValue(":c_{$key}_{$i}", $v);
                }
            } else {
                $stmt->bindValue(":c_{$key}", $value);
            }
        }
        $this->bindPaginationToStatement($options, $stmt);
        $result = $stmt->execute();
        if ($result === false) {
            return new \EmptyIterator();
        }
        // @codeCoverageIgnoreStart
        if ($stmt instanceof \PDOStatement) {
            $stmt->setFetchMode(_PDO::FETCH_ASSOC);
        }
        // @codeCoverageIgnoreEnd
        return $stmt;
    }

    /**
     * @param array $conditions
     * @return string
     */
    private function where(array $conditions): string
    {
        if (empty($conditions)) {
            return "";
        }
        $fun = function($o, $v) use ($conditions) : string {
            if (is_array($conditions[$v])) {
                $qa = [];
                $index = 0;
                foreach ($conditions[$v] as $value) {
                    $i = 1 + $index++;
                    $qa[] = ":c_{$v}_{$i}";
                }
                $qs = join(', ', $qa);
                return "{$o} AND {$v} IN ( $qs )";
            } else {
                return "{$o} AND {$v} = :c_{$v}";
            }
        };
        $where = trim(array_reduce(array_keys($conditions), $fun, ""), ' AND ');
        return "WHERE {$where} ";
    }

    private function limit(array $options): string
    {
        return array_key_exists('limit', $options) ? "LIMIT :o_limit OFFSET :o_offset " : '';
    }

    private function order(array $options): string
    {
        if (array_key_exists('order', $options)) {
            // @TODO Add more protection?
            return "ORDER BY {$options['order']} ";
        }
        return '';
    }

    private function boundDebugString(array $conditions, array $options, array $data = []): string
    {
        $out = [];
        foreach ($conditions as $k => $v) {
            if (is_array($v)) {
                $v = join(',', $v);
            }
            $out[] = "c_{$k}:'{$v}'";
        }
        foreach ($data as $k => $v) {
            $out[] = "d_{$k}:{$v}";
        }
        foreach ($options as $k => $v) {
            $out[] = "o_{$k}:{$v}";
        }
        return join(", ", $out);
    }

    /**
     * @param array $options
     * @param \PDOStatement $stmt
     */
    private function bindPaginationToStatement(array $options, $stmt): void
    {
        if (array_key_exists('limit', $options)) {
            $stmt->bindValue(":o_offset", (int) ($options['offset'] ?? 0), _PDO::PARAM_INT);
            $stmt->bindValue(":o_limit", (int) $options['limit'], _PDO::PARAM_INT);
        }
    }

    /**
     * @param string $table
     * @param array $conditions
     * @param array $data
     * @param array $options
     * @return int
     */
    public function update(string $table, array $conditions, array $data, array $options = []): int
    {
        if (empty($conditions) || empty($data)) {
            return 0;
        }

        $datasql = $this->data($data);
        $where = $this->where($conditions);
        $query = "UPDATE {$table} SET {$datasql} {$where};";

        $params = $this->boundDebugString($conditions, $options, $data);
        Log::debug("PDO:QUERY [$query][$params]");
        $dbh = $this->handler();
        $stmt = $dbh->prepare($query);
        foreach ($data as $key => $value) {
            $stmt->bindValue(":d_{$key}", $value);
        }
        foreach ($conditions as $key => $value) {
            $stmt->bindValue(":c_{$key}", $value);
        }
        $result = $stmt->execute();
        return ($result === true) ? $stmt->rowCount() : 0;
    }

    private function data(array $data): string
    {
        /**
         * @param string $o
         * @param mixed $v
         * @return string
         */
        $f = static fn(string $o, $v): string => "{$o}, {$v} = :d_{$v}";
        return trim((string) array_reduce(array_keys($data), $f, ""), ", ");
    }

    /**
     * @param string $table
     * @param array $data
     * @param array $options
     * @return null|string
     */
    public function insert(string $table, array $data, array $options = []): ?string
    {
        $keys = implode(', ', array_keys($data));
        $data_phs = ':d_' . implode(', :d_', array_keys($data));
        $query = "INSERT INTO {$table} ({$keys}) VALUES ({$data_phs});";
        $params = $this->boundDebugString([], [], $data);
        Log::debug("PDO:QUERY [$query][$params]");
        $dbh = $this->handler();
        $stmt = $dbh->prepare($query);
        foreach ($data as $key => $value) {
            $stmt->bindValue(':d_' . $key, $value);
        }
        $result = $stmt->execute();
        return ($result === true) ? $dbh->lastInsertId() : null;
    }

    /**
     * @param string $table
     * @param array $conditions
     * @param array $options
     * @return int
     */
    public function delete(string $table, array $conditions, array $options = []): int
    {
        $where = $this->where($conditions);
        if (empty($where)) {
            return 0;
        }
        $limit = $this->limit($options);
        $query = "DELETE FROM {$table} {$where}{$limit};";
        $params = $this->boundDebugString($conditions, $options);
        Log::debug("PDO:QUERY [$query][$params]");
        $dbh = $this->handler();
        $stmt = $dbh->prepare($query);
        foreach ($conditions as $key => $value) {
            $stmt->bindValue(":c_{$key}", $value);
        }
        $this->bindPaginationToStatement($options, $stmt);
        $result = $stmt->execute();
        return ($result === true) ? $stmt->rowCount() : 0;
    }
}


1			<?php
2
3			namespace alkemann\h2l\data;
4
5			use alkemann\h2l\exceptions\ConnectionError;
6			use alkemann\h2l\interfaces\Source;
7			use alkemann\h2l\Log;
8			use PDO as _PDO;
9
10			/**
11			* Class PDO
12			*
13			* @package alkemann\h2l\data
14			*/
15			class PDO implements Source
16			{
17			/**
18			* @var array
19			*/
20			protected array $config = [];
21
22			/**
23			* @var null\|_PDO
24			*/
25			protected $db = null;
26
27			/** @var string */
28			private string $pdo_class = _PDO::class;
29
30			/**
31			* @param array $config
32			* @param string $pdo_class
33			*/
34			public function __construct(array $config = [], string $pdo_class = _PDO::class)
35			{
36			$this->pdo_class = $pdo_class;
37
38			if (count($config) === 1 && array_key_exists('url', $config)) {
39			$config = parse_url($config['url']);
40			if (is_array($config)) {
			0 ignored issues – show introduced 2019-11-01 14:01 UTC by Report Bug Copy Issue Report The condition `is_array($config)` is always `true`. Loading history...
41			$config['db'] = ltrim($config['path'] ?? '', '/');
42			} else {
43			// @codeCoverageIgnoreStart
44			// parse_url always returns an array
45			$config = [];
46			// @codeCoverageIgnoreEnd
47			}
48			}
49
50			$defaults = [
51			'host' => 'localhost',
52			'db' => 'test',
53			'user' => null,
54			'pass' => null
55			];
56			$this->config = $config + $defaults;
57			}
58
59			/**
60			* @psalm-suppress MoreSpecificReturnType
61			* @psalm-suppress PropertyTypeCoercion
62			* @psalm-suppress LessSpecificReturnStatement
63			* @return _PDO
64			*/
65			private function handler(): object
66			{
67			if ($this->db) {
68			return $this->db;
69			}
70
71			$scheme = $this->config['scheme'] ?? 'mysql';
72			$host = $this->config['host'];
73			$db = $this->config['db'];
74			$user = $this->config['user'];
75			$pass = $this->config['pass'] ?? '';
76			$port = ($this->config['port'] ?? false) ? ";port={$this->config['port']}" : '';
77			if (empty($this->config['query'])) {
78			$modifiers = '';
79			} else {
80			$modifiers = ';' . str_replace('&', ';', $this->config['query']);
81			}
82			$opts = [
83			_PDO::ATTR_EMULATE_PREPARES => false,
84			_PDO::ATTR_ERRMODE => _PDO::ERRMODE_EXCEPTION,
85
86			'useUnicode' => true,
87			'characterEncoding' => 'UTF-8',
88			];
89			$dsn = "{$scheme}:host={$host}{$port}{$modifiers};dbname={$db}";
90			$class = $this->pdo_class;
91			try {
92			$this->db = new $class($dsn, $user, $pass, $opts);
93
94			// @TODO use this?
95			// $this->db->setAttribute( _PDO::ATTR_EMULATE_PREPARES, false);
96			} catch (\PDOException $e) {
97			throw new ConnectionError("Unable to connect to $host : $db with user $user");
98			}
99			return $this->db;
100			}
101
102			/**
103			* @param mixed $query
104			* @param array $params
105			* @return array\|false false on error
106			*/
107			public function query($query, array $params = [])
108			{
109			Log::debug("PDO:QUERY [$query]");
110			$result = $this->handler()->query($query);
111			return $result ? $result->fetchAll(_PDO::FETCH_ASSOC) : false;
112			}
113
114			/**
115			* @param string $table
116			* @param array $conditions
117			* @param array $options
118			* @return null\|array
119			* @throws \Error on finding more than 1 match
120			*/
121			public function one(string $table, array $conditions, array $options = []): ?array
122			{
123			$result = $this->find($table, $conditions, $options);
124			if ($result instanceof \Traversable) {
125			$result = iterator_to_array($result);
126			}
127			$hits = sizeof($result);
128			if ($hits === 0) {
129			return null;
130			}
131			if ($hits > 1) {
132			throw new \Error("One request found more than 1 match!");
133			}
134
135			return $result[0];
136			}
137
138			/**
139			* @param string $table
140			* @param array $conditions
141			* @param array $options
142			* @return iterable
143			*/
144			public function find(string $table, array $conditions, array $options = []): iterable
145			{
146			$where = $this->where($conditions);
147			$limit = $this->limit($options);
148			$order = $this->order($options);
149			$query = "SELECT * FROM {$table} {$where}{$order}{$limit};";
150			$params = $this->boundDebugString($conditions, $options);
151			Log::debug("PDO:QUERY [$query][$params]");
152			$dbh = $this->handler();
153			$stmt = $dbh->prepare($query);
154			foreach ($conditions as $key => $value) {
155			if (is_array($value)) {
156			$index = 0;
157			foreach ($value as $v) {
158			$i = 1 + $index++;
159			$stmt->bindValue(":c_{$key}_{$i}", $v);
160			}
161			} else {
162			$stmt->bindValue(":c_{$key}", $value);
163			}
164			}
165			$this->bindPaginationToStatement($options, $stmt);
166			$result = $stmt->execute();
167			if ($result === false) {
168			return new \EmptyIterator();
169			}
170			// @codeCoverageIgnoreStart
171			if ($stmt instanceof \PDOStatement) {
172			$stmt->setFetchMode(_PDO::FETCH_ASSOC);
173			}
174			// @codeCoverageIgnoreEnd
175			return $stmt;
176			}
177
178			/**
179			* @param array $conditions
180			* @return string
181			*/
182			private function where(array $conditions): string
183			{
184			if (empty($conditions)) {
185			return "";
186			}
187			$fun = function($o, $v) use ($conditions) : string {
188			if (is_array($conditions[$v])) {
189			$qa = [];
190			$index = 0;
191			foreach ($conditions[$v] as $value) {
192			$i = 1 + $index++;
193			$qa[] = ":c_{$v}_{$i}";
194			}
195			$qs = join(', ', $qa);
196			return "{$o} AND {$v} IN ( $qs )";
197			} else {
198			return "{$o} AND {$v} = :c_{$v}";
199			}
200			};
201			$where = trim(array_reduce(array_keys($conditions), $fun, ""), ' AND ');
202			return "WHERE {$where} ";
203			}
204
205			private function limit(array $options): string
206			{
207			return array_key_exists('limit', $options) ? "LIMIT :o_limit OFFSET :o_offset " : '';
208			}
209
210			private function order(array $options): string
211			{
212			if (array_key_exists('order', $options)) {
213			// @TODO Add more protection?
214			return "ORDER BY {$options['order']} ";
215			}
216			return '';
217			}
218
219			private function boundDebugString(array $conditions, array $options, array $data = []): string
220			{
221			$out = [];
222			foreach ($conditions as $k => $v) {
223			if (is_array($v)) {
224			$v = join(',', $v);
225			}
226			$out[] = "c_{$k}:'{$v}'";
227			}
228			foreach ($data as $k => $v) {
229			$out[] = "d_{$k}:{$v}";
230			}
231			foreach ($options as $k => $v) {
232			$out[] = "o_{$k}:{$v}";
233			}
234			return join(", ", $out);
235			}
236
237			/**
238			* @param array $options
239			* @param \PDOStatement $stmt
240			*/
241			private function bindPaginationToStatement(array $options, $stmt): void
242			{
243			if (array_key_exists('limit', $options)) {
244			$stmt->bindValue(":o_offset", (int) ($options['offset'] ?? 0), _PDO::PARAM_INT);
245			$stmt->bindValue(":o_limit", (int) $options['limit'], _PDO::PARAM_INT);
246			}
247			}
248
249			/**
250			* @param string $table
251			* @param array $conditions
252			* @param array $data
253			* @param array $options
254			* @return int
255			*/
256			public function update(string $table, array $conditions, array $data, array $options = []): int
257			{
258			if (empty($conditions) \|\| empty($data)) {
259			return 0;
260			}
261
262			$datasql = $this->data($data);
263			$where = $this->where($conditions);
264			$query = "UPDATE {$table} SET {$datasql} {$where};";
265
266			$params = $this->boundDebugString($conditions, $options, $data);
267			Log::debug("PDO:QUERY [$query][$params]");
268			$dbh = $this->handler();
269			$stmt = $dbh->prepare($query);
270			foreach ($data as $key => $value) {
271			$stmt->bindValue(":d_{$key}", $value);
272			}
273			foreach ($conditions as $key => $value) {
274			$stmt->bindValue(":c_{$key}", $value);
275			}
276			$result = $stmt->execute();
277			return ($result === true) ? $stmt->rowCount() : 0;
278			}
279
280			private function data(array $data): string
281			{
282			/**
283			* @param string $o
284			* @param mixed $v
285			* @return string
286			*/
287			$f = static fn(string $o, $v): string => "{$o}, {$v} = :d_{$v}";
288			return trim((string) array_reduce(array_keys($data), $f, ""), ", ");
289			}
290
291			/**
292			* @param string $table
293			* @param array $data
294			* @param array $options
295			* @return null\|string
296			*/
297			public function insert(string $table, array $data, array $options = []): ?string
298			{
299			$keys = implode(', ', array_keys($data));
300			$data_phs = ':d_' . implode(', :d_', array_keys($data));
301			$query = "INSERT INTO {$table} ({$keys}) VALUES ({$data_phs});";
302			$params = $this->boundDebugString([], [], $data);
303			Log::debug("PDO:QUERY [$query][$params]");
304			$dbh = $this->handler();
305			$stmt = $dbh->prepare($query);
306			foreach ($data as $key => $value) {
307			$stmt->bindValue(':d_' . $key, $value);
308			}
309			$result = $stmt->execute();
310			return ($result === true) ? $dbh->lastInsertId() : null;
311			}
312
313			/**
314			* @param string $table
315			* @param array $conditions
316			* @param array $options
317			* @return int
318			*/
319			public function delete(string $table, array $conditions, array $options = []): int
320			{
321			$where = $this->where($conditions);
322			if (empty($where)) {
323			return 0;
324			}
325			$limit = $this->limit($options);
326			$query = "DELETE FROM {$table} {$where}{$limit};";
327			$params = $this->boundDebugString($conditions, $options);
328			Log::debug("PDO:QUERY [$query][$params]");
329			$dbh = $this->handler();
330			$stmt = $dbh->prepare($query);
331			foreach ($conditions as $key => $value) {
332			$stmt->bindValue(":c_{$key}", $value);
333			}
334			$this->bindPaginationToStatement($options, $stmt);
335			$result = $stmt->execute();
336			return ($result === true) ? $stmt->rowCount() : 0;
337			}
338			}
339

alkemann / h2l

PDO::bindPaginationToStatement() A last analyzed 2022-04-08 19:01 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

PDO::bindPaginationToStatement() A
last analyzed 2022-04-08 19:01 UTC