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