MySqlConnection::formatClause()   A
last analyzed

Complexity

Conditions 5
Paths 5

Size

Total Lines 28
Code Lines 15

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 16
CRAP Score 5

Importance

Changes 0
Metric Value
cc 5
eloc 15
nc 5
nop 3
dl 0
loc 28
ccs 16
cts 16
cp 1
crap 5
rs 9.4555
c 0
b 0
f 0
1
<?php
2
3
namespace Simply\Database\Connection;
4
5
use Simply\Database\Connection\Provider\ConnectionProvider;
6
7
/**
8
 * Provides the basic functionality to operate on a MySQL database.
9
 * @author Riikka Kalliomäki <[email protected]>
10
 * @copyright Copyright (c) 2018 Riikka Kalliomäki
11
 * @license http://opensource.org/licenses/mit-license.php MIT License
12
 */
13
class MySqlConnection implements Connection
14
{
15
    /** @var ConnectionProvider The provider for the PDO connection */
16
    private $provider;
17
18
    /**
19
     * MySqlConnection constructor.
20
     * @param ConnectionProvider $provider The provider for the PDO connection
21
     */
22 25
    public function __construct(ConnectionProvider $provider)
23
    {
24 25
        $this->provider = $provider;
25 25
    }
26
27 25
    public function getConnection(): \PDO
28
    {
29 25
        return $this->provider->getConnection();
30
    }
31
32 17
    public function insert(string $table, array $values, & $primaryKey = null): \PDOStatement
33
    {
34 17
        $parameters = [];
35 17
        $result = $this->query($this->formatQuery([
36 17
            'INSERT INTO' => sprintf('%s (%s)', $this->formatTable($table), $this->formatFields(array_keys($values))),
37 16
            'VALUES' => $this->formatParameters($values, $parameters),
38 16
        ]), $parameters);
39
40 15
        if ($primaryKey !== null) {
41 15
            $primaryKey = $this->getConnection()->lastInsertId();
42
        }
43
44 15
        return $result;
45
    }
46
47 15
    public function select(
48
        array $fields,
49
        string $table,
50
        array $where,
51
        array $orderBy = [],
52
        int $limit = null
53
    ): \PDOStatement {
54 15
        $parameters = [];
55
56 15
        return $this->query($this->formatQuery([
57 15
            'SELECT' => $this->formatFields($fields),
58 14
            'FROM' => $this->formatTable($table),
59 13
            'WHERE' => $where ? $this->formatConditions($where, $parameters) : '',
60 13
            'ORDER BY' => $this->formatOrder($orderBy),
61 12
            'LIMIT' => $orderBy ? $this->formatLimit($limit, $parameters) : '',
62 12
        ]), $parameters);
63
    }
64
65 9
    public function update(string $table, array $values, array $where): \PDOStatement
66
    {
67 9
        $parameters = [];
68
69 9
        return $this->query($this->formatQuery([
70 9
            'UPDATE' => $this->formatTable($table),
71 9
            'SET' => $this->formatAssignments($values, $parameters),
72 8
            'WHERE' => $this->formatConditions($where, $parameters),
73 7
        ]), $parameters);
74
    }
75
76 6
    public function delete(string $table, array $where): \PDOStatement
77
    {
78 6
        $parameters = [];
79
80 6
        return $this->query($this->formatQuery([
81 6
            'DELETE FROM' => $this->formatTable($table),
82 6
            'WHERE' => $this->formatConditions($where, $parameters),
83 5
        ]), $parameters);
84
    }
85
86 24
    public function formatFields(array $fields, string $table = '', string $prefix = ''): string
87
    {
88 24
        if ($fields === []) {
89 2
            throw new \InvalidArgumentException('No fields provided for the query');
90
        }
91
92 22
        $format = '%2$s';
93
94 22
        if ($table !== '') {
95 4
            $format = '%1$s.' . $format;
96
        }
97
98 22
        if ($prefix !== '') {
99 4
            $format .= ' AS %3$s';
100
        }
101
102
        return implode(', ', array_map(function (string $field) use ($format, $table, $prefix): string {
103 22
            return sprintf(
104 22
                $format,
105 22
                $this->escapeIdentifier($table),
106 22
                $this->escapeIdentifier($field),
107 22
                $this->escapeIdentifier($prefix . $field)
108
            );
109 22
        }, $fields));
110
    }
111
112 26
    public function formatTable(string $table, string $alias = ''): string
113
    {
114 26
        if ($table === '') {
115 1
            throw new \InvalidArgumentException('No table provided for the query');
116
        }
117
118 25
        if ($alias !== '') {
119 4
            return sprintf('%s AS %s', $this->escapeIdentifier($table), $this->escapeIdentifier($alias));
120
        }
121
122 24
        return $this->escapeIdentifier($table);
123
    }
124
125
    /**
126
     * Formats the conditions for the SQL query.
127
     * @param array $conditions The conditions for the SQL query
128
     * @param array $parameters Array of parameters to fill
129
     * @return string Formatted conditions for the query
130
     */
131 14
    private function formatConditions(array $conditions, array & $parameters): string
132
    {
133 14
        if ($conditions === []) {
134 2
            throw new \InvalidArgumentException('No conditions provided for the query');
135
        }
136
137 12
        $clauses = [];
138
139 12
        foreach ($conditions as $field => $value) {
140 12
            $clauses[] = $this->formatClause($field, $value, $parameters);
141
        }
142
143 12
        return implode(' AND ', $clauses);
144
    }
145
146
    /**
147
     * Formats a single condition for the SQL query.
148
     * @param string $field The name of the field
149
     * @param mixed $value Expected value for the fields
150
     * @param array $parameters Array of parameters to fill
151
     * @return string The formatted condition for the query
152
     */
153 12
    private function formatClause(string $field, $value, array & $parameters): string
154
    {
155 12
        $escaped = $this->escapeIdentifier($field);
156
157 12
        if (\is_array($value)) {
158 3
            if (\in_array(null, $value, true)) {
159
                $value = array_filter($value, function ($value): bool {
160 1
                    return $value !== null;
161 1
                });
162
163 1
                if ($value !== []) {
164 1
                    $placeholders = $this->formatParameters($value, $parameters);
165 1
                    return "($escaped IN $placeholders OR $escaped IS NULL)";
166
                }
167
168 1
                return "$escaped IS NULL";
169
            }
170
171 3
            $placeholders = $this->formatParameters($value, $parameters);
172 3
            return "$escaped IN $placeholders";
173
        }
174
175 12
        if ($value === null) {
176 1
            return "$escaped IS NULL";
177
        }
178
179 12
        $parameters[] = $value;
180 12
        return "$escaped = ?";
181
    }
182
183
    /**
184
     * Formats a list of parenthesis enclosed parameters for an SQL query.
185
     * @param array $values List of values
186
     * @param array $parameters Array of parameters to fill
187
     * @return string The formatted parenthesis enclosed list of parameter placeholders
188
     */
189 16
    private function formatParameters(array $values, array & $parameters): string
190
    {
191 16
        array_push($parameters, ... array_values($values));
192 16
        return sprintf('(%s)', implode(', ', array_fill(0, \count($values), '?')));
193
    }
194
195
    /**
196
     * Formats the order clause for a select query.
197
     * @param array $order The order for the select query
198
     * @return string The formatted ORDER BY clause for the query
199
     */
200 13
    private function formatOrder(array $order): string
201
    {
202 13
        $clauses = [];
203
204 13
        foreach ($order as $field => $direction) {
205 3
            $clauses[] = sprintf('%s %s', $this->escapeIdentifier($field), $this->formatDirection($direction));
206
        }
207
208 12
        return implode(', ', $clauses);
209
    }
210
211
    /**
212
     * Returns the appropriate SQL sorting order for the sorting constant.
213
     * @param int $order The sorting constant
214
     * @return string The SQL representation for the given sorting order
215
     */
216 3
    private function formatDirection(int $order): string
217
    {
218 3
        if ($order === self::ORDER_ASCENDING) {
219 2
            return 'ASC';
220
        }
221
222 2
        if ($order === self::ORDER_DESCENDING) {
223 1
            return 'DESC';
224
        }
225
226 1
        throw new \InvalidArgumentException('Invalid sorting direction');
227
    }
228
229
    /**
230
     * Formats the LIMIT clause for a select query.
231
     * @param int|null $limit The maximum number of rows to return or null for no limit
232
     * @param array $parameters Array of parameters to fill
233
     * @return string The formatted LIMIT clause for the select query
234
     */
235 2
    private function formatLimit(?int $limit, array & $parameters): string
236
    {
237 2
        if ($limit === null) {
238 1
            return '';
239
        }
240
241 2
        $parameters[] = $limit;
242 2
        return '?';
243
    }
244
245
    /**
246
     * Formats value assignments in UPDATE query.
247
     * @param array $values Associate array of columns and values
248
     * @param array $parameters Array of parameters to fill
249
     * @return string The formatted list of assignments for the UPDATE query
250
     */
251 9
    private function formatAssignments(array $values, array & $parameters): string
252
    {
253 9
        if ($values === []) {
254 1
            throw new \InvalidArgumentException('No values provided for the query');
255
        }
256
257 8
        $assignments = [];
258
259 8
        foreach ($values as $field => $value) {
260 8
            $assignments[] = sprintf('%s = ?', $this->escapeIdentifier($field));
261 8
            $parameters[] = $value;
262
        }
263
264 8
        return implode(', ', $assignments);
265
    }
266
267
    /**
268
     * Escapes a MySQL query identifier.
269
     * @param string $identifier The identifier to escape
270
     * @return string The escaped identifier
271
     */
272 26
    private function escapeIdentifier(string $identifier): string
273
    {
274 26
        return "`$identifier`";
275
    }
276
277
    /**
278
     * Formats an arbitrary SQL query based on given clauses.
279
     * @param array $clauses Associative array of SQL clauses and their contents
280
     * @return string The complete formatted SQL query
281
     */
282 16
    private function formatQuery(array $clauses): string
283
    {
284 16
        $parts = [];
285
286 16
        foreach ($clauses as $clause => $value) {
287 16
            if ($value === '') {
288 12
                continue;
289
            }
290
291 16
            $parts[] = sprintf('%s %s', $clause, $value);
292
        }
293
294 16
        return implode(' ', $parts);
295
    }
296
297 17
    public function query(string $sql, array $parameters = []): \PDOStatement
298
    {
299 17
        $query = $this->getConnection()->prepare($sql);
300
301 17
        if (! $query instanceof \PDOStatement) {
302 1
            throw new \UnexpectedValueException('Unexpected value returned by prepare query');
303
        }
304
305 16
        foreach ($parameters as $name => $value) {
306 16
            $this->bindQueryParameter($query, \is_int($name) ? $name + 1 : $name, $value);
307
        }
308
309 15
        $query->execute();
310
311 15
        return $query;
312
    }
313
314
    /**
315
     * Binds an SQL query parameter to the PDO statement fo the query.
316
     * @param \PDOStatement $query The PDO statement for binding the value
317
     * @param int|string $name The name of the placeholder
318
     * @param mixed $value The value to bind
319
     * @return bool True if the binding was successful, false if not
320
     */
321 16
    private function bindQueryParameter(\PDOStatement $query, $name, $value): bool
322
    {
323
        switch (true) {
324 16
            case \is_string($value):
325 15
                return $query->bindValue($name, $value, \PDO::PARAM_STR);
326 16
            case \is_float($value):
327 3
                return $query->bindValue($name, var_export($value, true), \PDO::PARAM_STR);
328 16
            case \is_int($value):
329 15
                return $query->bindValue($name, $value, \PDO::PARAM_INT);
330 16
            case \is_bool($value):
331 15
                return $query->bindValue($name, $value ? 1 : 0, \PDO::PARAM_INT);
332 15
            case $value === null:
333 14
                return $query->bindValue($name, null, \PDO::PARAM_NULL);
334
            default:
335 1
                throw new \InvalidArgumentException('Invalid parameter value type');
336
        }
337
    }
338
}
339