PDO::bindPaginationToStatement() - Code Metrics - Inspection of "Postgres SQL support" - alkemann/h2l - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#16)

by Alexander

created 2017-08-15 14:03 UTC

PDO::bindPaginationToStatement() A

↳ 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	9.4285
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 implements Source
{

    /**
     * @var array
     */
    protected $config = [];

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

    public function __construct(array $config = [])
    {
        $defaults = [
            'host' => 'localhost',
            'db' => 'test',
            'user' => null,
            'pass' => null
        ];
        $this->config = $config + $defaults;
    }

    private function handler() //: PDO
    {
        if ($this->db) {
            return $this->db;
        }

        $type = $this->config['type'] ?? 'mysql';
        $host = $this->config['host'];
        $db = $this->config['db'];
        $user = $this->config['user'];
        $pass = $this->config['pass'];
        $opts = [
            _PDO::ATTR_EMULATE_PREPARES => false,
            _PDO::ATTR_ERRMODE => _PDO::ERRMODE_EXCEPTION
        ];
        try {
            $this->db = new _PDO("{$type}:host={$host};dbname={$db}", $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;
    }

    public function query($query, array $params = [])
    {
        Log::debug("PDO:QUERY [$query]");
        $result = $this->handler()->query($query);
        return $result->fetchAll(_PDO::FETCH_ASSOC);
    }

    public function one(string $table, array $conditions, array $options = []): ?array
    {
        $result = $this->find($table, $conditions, $options);
        $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];
    }

    public function find(string $table, array $conditions, array $options = []): iterable
    {
        $where = $this->where($conditions);
        $limit = $this->limit($options);
        $query = "SELECT * 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();
        if ($result === false) {
            return new \EmptyIterator;
        }
        // @codeCoverageIgnoreStart
        if ($stmt instanceof \PDOStatement) {
            $stmt->setFetchMode(_PDO::FETCH_ASSOC);
        }
        // @codeCoverageIgnoreEnd
        return $stmt;
    }

    private function where(array $conditions): string
    {
        if (empty($conditions)) {
            return "";
        }
        $fun = function ($o, $v) {
            return "{$o}{$v} = :c_{$v}";
        };
        $where = array_reduce(array_keys($conditions), $fun, "");
        return "WHERE {$where} ";
    }

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

    private function boundDebugString(array $conditions, array $options, array $data = []): string
    {
        $out = [];
        foreach ($conditions as $k => $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);
        }
    }

    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
    {
        $fun = function ($o, $v) {
            return "{$o}{$v} = :d_{$v}";
        };
        return (string)array_reduce(array_keys($data), $fun, "");
    }

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

    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			class PDO implements Source
11			{
12
13			/**
14			* @var array
15			*/
16			protected $config = [];
17
18			/**
19			* @var _PDO
20			*/
21			protected $db = null;
22
23			public function __construct(array $config = [])
24			{
25			$defaults = [
26			'host' => 'localhost',
27			'db' => 'test',
28			'user' => null,
29			'pass' => null
30			];
31			$this->config = $config + $defaults;
32			}
33
34			private function handler() //: PDO
35			{
36			if ($this->db) {
37			return $this->db;
38			}
39
40			$type = $this->config['type'] ?? 'mysql';
41			$host = $this->config['host'];
42			$db = $this->config['db'];
43			$user = $this->config['user'];
44			$pass = $this->config['pass'];
45			$opts = [
46			_PDO::ATTR_EMULATE_PREPARES => false,
47			_PDO::ATTR_ERRMODE => _PDO::ERRMODE_EXCEPTION
48			];
49			try {
50			$this->db = new _PDO("{$type}:host={$host};dbname={$db}", $user, $pass, $opts);
51
52			// @TODO use this?
53			// $this->db->setAttribute( _PDO::ATTR_EMULATE_PREPARES, false);
54			} catch (\PDOException $e) {
55			throw new ConnectionError("Unable to connect to $host : $db with user $user");
56			}
57			return $this->db;
58			}
59
60			public function query($query, array $params = [])
61			{
62			Log::debug("PDO:QUERY [$query]");
63			$result = $this->handler()->query($query);
64			return $result->fetchAll(_PDO::FETCH_ASSOC);
65			}
66
67			public function one(string $table, array $conditions, array $options = []): ?array
68			{
69			$result = $this->find($table, $conditions, $options);
70			$result = iterator_to_array($result);
71			$hits = sizeof($result);
72			if ($hits === 0) {
73			return null;
74			}
75			if ($hits > 1) {
76			throw new \Error("One request found more than 1 match!");
77			}
78
79			return $result[0];
80			}
81
82			public function find(string $table, array $conditions, array $options = []): iterable
83			{
84			$where = $this->where($conditions);
85			$limit = $this->limit($options);
86			$query = "SELECT * FROM {$table} {$where}{$limit};";
87			$params = $this->boundDebugString($conditions, $options);
88			Log::debug("PDO:QUERY [$query][$params]");
89			$dbh = $this->handler();
90			$stmt = $dbh->prepare($query);
91			foreach ($conditions as $key => $value) {
92			$stmt->bindValue(":c_{$key}", $value);
93			}
94			$this->bindPaginationToStatement($options, $stmt);
95			$result = $stmt->execute();
96			if ($result === false) {
97			return new \EmptyIterator;
98			}
99			// @codeCoverageIgnoreStart
100			if ($stmt instanceof \PDOStatement) {
101			$stmt->setFetchMode(_PDO::FETCH_ASSOC);
102			}
103			// @codeCoverageIgnoreEnd
104			return $stmt;
105			}
106
107			private function where(array $conditions): string
108			{
109			if (empty($conditions)) {
110			return "";
111			}
112			$fun = function ($o, $v) {
113			return "{$o}{$v} = :c_{$v}";
114			};
115			$where = array_reduce(array_keys($conditions), $fun, "");
116			return "WHERE {$where} ";
117			}
118
119			private function limit(array $options): string
120			{
121			return array_key_exists('limit', $options) ? "LIMIT :o_offset,:o_limit " : '';
122			}
123
124			private function boundDebugString(array $conditions, array $options, array $data = []): string
125			{
126			$out = [];
127			foreach ($conditions as $k => $v) {
128			$out[] = "c_{$k}:'{$v}'";
129			}
130			foreach ($data as $k => $v) {
131			$out[] = "d_{$k}:{$v}";
132			}
133			foreach ($options as $k => $v) {
134			$out[] = "o_{$k}:{$v}";
135			}
136			return join(", ", $out);
137			}
138
139			/**
140			* @param array $options
141			* @param \PDOStatement $stmt
142			*/
143			private function bindPaginationToStatement(array $options, $stmt): void
144			{
145			if (array_key_exists('limit', $options)) {
146			$stmt->bindValue(":o_offset", (int)($options['offset'] ?? 0), _PDO::PARAM_INT);
147			$stmt->bindValue(":o_limit", (int)$options['limit'], _PDO::PARAM_INT);
148			}
149			}
150
151			public function update(string $table, array $conditions, array $data, array $options = []): int
152			{
153			if (empty($conditions) \|\| empty($data)) {
154			return 0;
155			}
156
157			$datasql = $this->data($data);
158			$where = $this->where($conditions);
159			$query = "UPDATE {$table} SET {$datasql} {$where};";
160
161			$params = $this->boundDebugString($conditions, $options, $data);
162			Log::debug("PDO:QUERY [$query][$params]");
163			$dbh = $this->handler();
164			$stmt = $dbh->prepare($query);
165			foreach ($data as $key => $value) {
166			$stmt->bindValue(":d_{$key}", $value);
167			}
168			foreach ($conditions as $key => $value) {
169			$stmt->bindValue(":c_{$key}", $value);
170			}
171			$result = $stmt->execute();
172			return ($result === true) ? $stmt->rowCount() : 0;
173			}
174
175			private function data(array $data): string
176			{
177			$fun = function ($o, $v) {
178			return "{$o}{$v} = :d_{$v}";
179			};
180			return (string)array_reduce(array_keys($data), $fun, "");
181			}
182
183			public function insert(string $table, array $data, array $options = []): ?string
184			{
185			$keys = implode(', ', array_keys($data));
186			$data_phs = ':d_' . implode(', :d_', array_keys($data));
187			$query = "INSERT INTO {$table} ({$keys}) VALUES ({$data_phs});";
188			$params = $this->boundDebugString([], [], $data);
189			Log::debug("PDO:QUERY [$query][$params]");
190			$dbh = $this->handler();
191			$stmt = $dbh->prepare($query);
192			foreach ($data as $key => $value) {
193			$stmt->bindValue(':d_' . $key, $value);
194			}
195			$result = $stmt->execute();
196			return ($result === true) ? $dbh->lastInsertId() : null;
197			}
198
199			public function delete(string $table, array $conditions, array $options = []): int
200			{
201			$where = $this->where($conditions);
202			if (empty($where)) {
203			return 0;
204			}
205			$limit = $this->limit($options);
206			$query = "DELETE FROM {$table} {$where}{$limit};";
207			$params = $this->boundDebugString($conditions, $options);
208			Log::debug("PDO:QUERY [$query][$params]");
209			$dbh = $this->handler();
210			$stmt = $dbh->prepare($query);
211			foreach ($conditions as $key => $value) {
212			$stmt->bindValue(":c_{$key}", $value);
213			}
214			$this->bindPaginationToStatement($options, $stmt);
215			$result = $stmt->execute();
216			return ($result === true) ? $stmt->rowCount() : 0;
217			}
218			}
219

alkemann / h2l

Pull Request — master (#16)

PDO::bindPaginationToStatement() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like