1
|
|
|
<?php |
2
|
|
|
namespace Kir\MySQL\Databases; |
3
|
|
|
|
4
|
|
|
use PDO; |
5
|
|
|
use PDOException; |
6
|
|
|
use RuntimeException; |
7
|
|
|
use UnexpectedValueException; |
8
|
|
|
use Kir\MySQL\Builder; |
9
|
|
|
use Kir\MySQL\Builder\Exception; |
10
|
|
|
use Kir\MySQL\Builder\QueryStatement; |
11
|
|
|
use Kir\MySQL\Database; |
12
|
|
|
use Kir\MySQL\Databases\MySQL\MySQLExceptionInterpreter; |
13
|
|
|
use Kir\MySQL\QueryLogger\QueryLoggers; |
14
|
|
|
use Kir\MySQL\Tools\AliasRegistry; |
15
|
|
|
use Kir\MySQL\Tools\VirtualTables; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
*/ |
19
|
|
|
class MySQL implements Database { |
20
|
|
|
/** @var array */ |
21
|
|
|
private static $tableFields = []; |
22
|
|
|
/** @var PDO */ |
23
|
|
|
private $pdo; |
24
|
|
|
/** @var bool */ |
25
|
|
|
private $outerTransaction = false; |
26
|
|
|
/** @var AliasRegistry */ |
27
|
|
|
private $aliasRegistry; |
28
|
|
|
/** @var int */ |
29
|
|
|
private $transactionLevel = 0; |
30
|
|
|
/** @var QueryLoggers */ |
31
|
|
|
private $queryLoggers = 0; |
32
|
|
|
/** @var VirtualTables */ |
33
|
|
|
private $virtualTables = null; |
34
|
|
|
/** @var MySQLExceptionInterpreter */ |
35
|
|
|
private $exceptionInterpreter = 0; |
36
|
|
|
/** @var array */ |
37
|
|
|
private $options; |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* @param PDO $pdo |
41
|
|
|
* @param array $options |
42
|
|
|
*/ |
43
|
|
|
public function __construct(PDO $pdo, array $options = []) { |
44
|
|
|
if($pdo->getAttribute(PDO::ATTR_ERRMODE) === PDO::ERRMODE_SILENT) { |
45
|
|
|
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); |
46
|
|
|
} |
47
|
|
|
$this->pdo = $pdo; |
48
|
|
|
$this->aliasRegistry = new AliasRegistry(); |
49
|
|
|
$this->queryLoggers = new QueryLoggers(); |
50
|
|
|
$this->exceptionInterpreter = new MySQLExceptionInterpreter(); |
51
|
|
|
$defaultOptions = [ |
52
|
|
|
'select-options' => [], |
53
|
|
|
'insert-options' => [], |
54
|
|
|
'update-options' => [], |
55
|
|
|
'delete-options' => [], |
56
|
|
|
]; |
57
|
|
|
$this->options = array_merge($defaultOptions, $options); |
58
|
|
|
} |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* @return QueryLoggers |
62
|
|
|
*/ |
63
|
|
|
public function getQueryLoggers() { |
64
|
|
|
return $this->queryLoggers; |
65
|
|
|
} |
66
|
|
|
|
67
|
|
|
/** |
68
|
|
|
* @return AliasRegistry |
69
|
|
|
*/ |
70
|
|
|
public function getAliasRegistry() { |
71
|
|
|
return $this->aliasRegistry; |
72
|
|
|
} |
73
|
|
|
|
74
|
|
|
/** |
75
|
|
|
* @return VirtualTables |
76
|
|
|
*/ |
77
|
|
|
public function getVirtualTables() { |
78
|
|
|
if($this->virtualTables === null) { |
79
|
|
|
$this->virtualTables = new VirtualTables(); |
80
|
|
|
} |
81
|
|
|
return $this->virtualTables; |
82
|
|
|
} |
83
|
|
|
|
84
|
|
|
/** |
85
|
|
|
* @param string $query |
86
|
|
|
* @throws Exception |
87
|
|
|
* @return QueryStatement |
88
|
|
|
*/ |
89
|
|
|
public function query($query) { |
90
|
|
|
return $this->buildQueryStatement($query, function ($query) { |
91
|
|
|
$stmt = $this->pdo->query($query); |
92
|
|
|
return $stmt; |
93
|
|
|
}); |
94
|
|
|
} |
95
|
|
|
|
96
|
|
|
/** |
97
|
|
|
* @param string $query |
98
|
|
|
* @throws Exception |
99
|
|
|
* @return QueryStatement |
100
|
|
|
*/ |
101
|
|
|
public function prepare($query) { |
102
|
|
|
return $this->buildQueryStatement((string) $query, function ($query) { |
103
|
|
|
$stmt = $this->pdo->prepare($query); |
104
|
|
|
return $stmt; |
105
|
|
|
}); |
106
|
|
|
} |
107
|
|
|
|
108
|
|
|
/** |
109
|
|
|
* @param string $query |
110
|
|
|
* @param array $params |
111
|
|
|
* @return int |
112
|
|
|
*/ |
113
|
|
|
public function exec($query, array $params = []) { |
114
|
|
|
return $this->exceptionHandler(function () use ($query, $params) { |
115
|
|
|
$stmt = $this->pdo->prepare($query); |
116
|
|
|
$timer = microtime(true); |
117
|
|
|
$stmt->execute($params); |
118
|
|
|
$this->queryLoggers->log($query, microtime(true) - $timer); |
119
|
|
|
$result = $stmt->rowCount(); |
120
|
|
|
$stmt->closeCursor(); |
121
|
|
|
return $result; |
122
|
|
|
}); |
123
|
|
|
} |
124
|
|
|
|
125
|
|
|
/** |
126
|
|
|
* @return string |
127
|
|
|
*/ |
128
|
|
|
public function getLastInsertId() { |
129
|
|
|
return $this->pdo->lastInsertId(); |
130
|
|
|
} |
131
|
|
|
|
132
|
|
|
/** |
133
|
|
|
* @param string $table |
134
|
|
|
* @return array |
135
|
|
|
*/ |
136
|
|
|
public function getTableFields($table) { |
137
|
|
|
$table = $this->select()->aliasReplacer()->replace($table); |
138
|
|
|
if(array_key_exists($table, self::$tableFields)) { |
139
|
|
|
return self::$tableFields[$table]; |
140
|
|
|
} |
141
|
|
|
$stmt = $this->pdo->query("DESCRIBE {$table}"); |
142
|
|
|
$rows = $stmt->fetchAll(\PDO::FETCH_ASSOC); |
143
|
|
|
self::$tableFields[$table] = array_map(function ($row) { return $row['Field']; }, $rows); |
144
|
|
|
$stmt->closeCursor(); |
145
|
|
|
return self::$tableFields[$table]; |
146
|
|
|
} |
147
|
|
|
|
148
|
|
|
/** |
149
|
|
|
* @param mixed $expression |
150
|
|
|
* @param array $arguments |
151
|
|
|
* @return string |
152
|
|
|
*/ |
153
|
|
|
public function quoteExpression($expression, array $arguments = array()) { |
154
|
|
|
$index = -1; |
155
|
|
|
$func = function () use ($arguments, &$index) { |
156
|
|
|
$index++; |
157
|
|
|
if(array_key_exists($index, $arguments)) { |
158
|
|
|
$argument = $arguments[$index]; |
159
|
|
|
$value = $this->quote($argument); |
160
|
|
|
} elseif(count($arguments) > 0) { |
161
|
|
|
$args = $arguments; |
162
|
|
|
$value = array_pop($args); |
163
|
|
|
$value = $this->quote($value); |
164
|
|
|
} else { |
165
|
|
|
$value = 'NULL'; |
166
|
|
|
} |
167
|
|
|
return $value; |
168
|
|
|
}; |
169
|
|
|
$result = preg_replace_callback('/(\\?)/', $func, $expression); |
170
|
|
|
return (string) $result; |
171
|
|
|
} |
172
|
|
|
|
173
|
|
|
/** |
174
|
|
|
* @param mixed $value |
175
|
|
|
* @return string |
176
|
|
|
*/ |
177
|
|
|
public function quote($value) { |
178
|
|
|
if(is_null($value)) { |
179
|
|
|
$result = 'NULL'; |
180
|
|
|
} elseif($value instanceof Builder\DBExpr) { |
181
|
|
|
$result = $value->getExpression(); |
182
|
|
|
} elseif($value instanceof Builder\Select) { |
183
|
|
|
$result = sprintf('(%s)', (string) $value); |
184
|
|
|
} elseif(is_array($value)) { |
185
|
|
|
$result = join(', ', array_map(function ($value) { return $this->quote($value); }, $value)); |
186
|
|
|
} else { |
187
|
|
|
$result = $this->pdo->quote($value); |
188
|
|
|
} |
189
|
|
|
return $result; |
190
|
|
|
} |
191
|
|
|
|
192
|
|
|
/** |
193
|
|
|
* @param string $field |
194
|
|
|
* @return string |
195
|
|
|
*/ |
196
|
|
|
public function quoteField($field) { |
197
|
|
|
if (is_numeric($field) || !is_string($field)) { |
198
|
|
|
throw new UnexpectedValueException('Field name is invalid'); |
199
|
|
|
} |
200
|
|
|
if(strpos($field, '`') !== false) { |
201
|
|
|
return (string) $field; |
202
|
|
|
} |
203
|
|
|
$parts = explode('.', $field); |
204
|
|
|
return '`'.join('`.`', $parts).'`'; |
205
|
|
|
} |
206
|
|
|
|
207
|
|
|
/** |
208
|
|
|
* @param array $fields |
209
|
|
|
* @return Builder\RunnableSelect |
210
|
|
|
*/ |
211
|
|
View Code Duplication |
public function select(array $fields = null) { |
|
|
|
|
212
|
|
|
$select = array_key_exists('select-factory', $this->options) |
213
|
|
|
? call_user_func($this->options['select-factory'], $this, $this->options['select-options']) |
214
|
|
|
: new Builder\RunnableSelect($this, $this->options['select-options']); |
215
|
|
|
if($fields !== null) { |
216
|
|
|
$select->fields($fields); |
217
|
|
|
} |
218
|
|
|
return $select; |
219
|
|
|
} |
220
|
|
|
|
221
|
|
|
/** |
222
|
|
|
* @param array $fields |
223
|
|
|
* @return Builder\RunnableInsert |
224
|
|
|
*/ |
225
|
|
View Code Duplication |
public function insert(array $fields = null) { |
|
|
|
|
226
|
|
|
$insert = array_key_exists('insert-factory', $this->options) |
227
|
|
|
? call_user_func($this->options['insert-factory'], $this, $this->options['insert-options']) |
228
|
|
|
: new Builder\RunnableInsert($this, $this->options['insert-options']); |
229
|
|
|
if($fields !== null) { |
230
|
|
|
$insert->addAll($fields); |
231
|
|
|
} |
232
|
|
|
return $insert; |
233
|
|
|
} |
234
|
|
|
|
235
|
|
|
/** |
236
|
|
|
* @param array $fields |
237
|
|
|
* @return Builder\RunnableUpdate |
238
|
|
|
*/ |
239
|
|
View Code Duplication |
public function update(array $fields = null) { |
|
|
|
|
240
|
|
|
$update = array_key_exists('update-factory', $this->options) |
241
|
|
|
? call_user_func($this->options['update-factory'], $this, $this->options['update-options']) |
242
|
|
|
: new Builder\RunnableUpdate($this, $this->options['update-options']); |
243
|
|
|
if($fields !== null) { |
244
|
|
|
$update->setAll($fields); |
245
|
|
|
} |
246
|
|
|
return $update; |
247
|
|
|
} |
248
|
|
|
|
249
|
|
|
/** |
250
|
|
|
* @return Builder\RunnableDelete |
251
|
|
|
*/ |
252
|
|
|
public function delete() { |
253
|
|
|
return array_key_exists('delete-factory', $this->options) |
254
|
|
|
? call_user_func($this->options['delete-factory'], $this, $this->options['delete-options']) |
255
|
|
|
: new Builder\RunnableDelete($this, $this->options['delete-options']); |
256
|
|
|
} |
257
|
|
|
|
258
|
|
|
/** |
259
|
|
|
* @return $this |
260
|
|
|
*/ |
261
|
|
|
public function transactionStart() { |
262
|
|
|
if((int) $this->transactionLevel === 0) { |
263
|
|
|
if($this->pdo->inTransaction()) { |
264
|
|
|
$this->outerTransaction = true; |
265
|
|
|
} else { |
266
|
|
|
$this->pdo->beginTransaction(); |
267
|
|
|
} |
268
|
|
|
} |
269
|
|
|
$this->transactionLevel++; |
270
|
|
|
return $this; |
271
|
|
|
} |
272
|
|
|
|
273
|
|
|
/** |
274
|
|
|
* @return $this |
275
|
|
|
*/ |
276
|
|
|
public function transactionCommit() { |
277
|
|
|
return $this->transactionEnd(function () { |
278
|
|
|
$this->pdo->commit(); |
279
|
|
|
}); |
280
|
|
|
} |
281
|
|
|
|
282
|
|
|
/** |
283
|
|
|
* @return $this |
284
|
|
|
*/ |
285
|
|
|
public function transactionRollback() { |
286
|
|
|
return $this->transactionEnd(function () { |
287
|
|
|
$this->pdo->rollBack(); |
288
|
|
|
}); |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
/** |
292
|
|
|
* @param callable|null $callback |
293
|
|
|
* @return mixed |
294
|
|
|
*/ |
295
|
|
|
public function dryRun($callback = null) { |
296
|
|
|
if(!$this->pdo->inTransaction()) { |
297
|
|
|
$this->transactionStart(); |
298
|
|
|
try { |
299
|
|
|
return call_user_func($callback, $this); |
300
|
|
|
} finally { |
301
|
|
|
$this->transactionRollback(); |
302
|
|
|
} |
303
|
|
|
} else { |
304
|
|
|
$uniqueId = $this->genUniqueId(); |
305
|
|
|
$this->exec("SAVEPOINT {$uniqueId}"); |
306
|
|
|
try { |
307
|
|
|
return call_user_func($callback, $this); |
308
|
|
|
} finally { |
309
|
|
|
$this->exec("ROLLBACK TO {$uniqueId}"); |
310
|
|
|
} |
311
|
|
|
} |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
/** |
315
|
|
|
* @param callable|null $callback |
316
|
|
|
* @return mixed |
317
|
|
|
*/ |
318
|
|
|
public function transaction(callable $callback = null) { |
319
|
|
|
$result = null; |
320
|
|
|
if(!$this->pdo->inTransaction()) { |
321
|
|
|
$this->transactionStart(); |
322
|
|
|
try { |
323
|
|
|
$result = call_user_func($callback, $this); |
324
|
|
|
$exception = null; |
|
|
|
|
325
|
|
|
$this->transactionCommit(); |
326
|
|
|
} finally { |
327
|
|
|
if($this->pdo->inTransaction()) { |
328
|
|
|
$this->transactionRollback(); |
329
|
|
|
} |
330
|
|
|
} |
331
|
|
|
} else { |
332
|
|
|
$uniqueId = $this->genUniqueId(); |
333
|
|
|
$this->exec("SAVEPOINT {$uniqueId}"); |
334
|
|
|
$finally = function () use ($uniqueId) { |
335
|
|
|
$this->exec("ROLLBACK TO {$uniqueId}"); |
336
|
|
|
}; |
337
|
|
|
try { |
338
|
|
|
$result = call_user_func($callback, $this); |
339
|
|
|
$finally = function () use ($uniqueId) { |
340
|
|
|
$this->exec("RELEASE SAVEPOINT {$uniqueId}"); |
341
|
|
|
}; |
342
|
|
|
} finally { |
343
|
|
|
$finally(); |
344
|
|
|
} |
345
|
|
|
} |
346
|
|
|
return $result; |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
/** |
350
|
|
|
* @param callable $fn |
351
|
|
|
* @return $this |
352
|
|
|
* @throws RuntimeException |
353
|
|
|
*/ |
354
|
|
|
private function transactionEnd($fn) { |
355
|
|
|
$this->transactionLevel--; |
356
|
|
|
if($this->transactionLevel < 0) { |
357
|
|
|
throw new RuntimeException("Transaction-Nesting-Problem: Trying to invoke commit on a already closed transaction"); |
358
|
|
|
} |
359
|
|
|
if((int) $this->transactionLevel === 0) { |
360
|
|
|
if($this->outerTransaction) { |
361
|
|
|
$this->outerTransaction = false; |
362
|
|
|
} else { |
363
|
|
|
call_user_func($fn); |
364
|
|
|
} |
365
|
|
|
} |
366
|
|
|
return $this; |
367
|
|
|
} |
368
|
|
|
|
369
|
|
|
/** |
370
|
|
|
* @param string $query |
371
|
|
|
* @param callable $fn |
372
|
|
|
* @return QueryStatement |
373
|
|
|
* @throws RuntimeException |
374
|
|
|
*/ |
375
|
|
|
private function buildQueryStatement($query, $fn) { |
376
|
|
|
$stmt = call_user_func($fn, $query); |
377
|
|
|
if(!$stmt) { |
378
|
|
|
throw new RuntimeException("Could not execute statement:\n{$query}"); |
379
|
|
|
} |
380
|
|
|
$stmtWrapper = new QueryStatement($stmt, $query, $this->exceptionInterpreter, $this->queryLoggers); |
381
|
|
|
return $stmtWrapper; |
382
|
|
|
} |
383
|
|
|
|
384
|
|
|
/** |
385
|
|
|
* @param callable $fn |
386
|
|
|
* @return mixed |
387
|
|
|
*/ |
388
|
|
|
private function exceptionHandler($fn) { |
389
|
|
|
try { |
390
|
|
|
return call_user_func($fn); |
391
|
|
|
} catch (PDOException $e) { |
392
|
|
|
$this->exceptionInterpreter->throwMoreConcreteException($e); |
393
|
|
|
} |
394
|
|
|
return null; |
395
|
|
|
} |
396
|
|
|
|
397
|
|
|
/** |
398
|
|
|
* @return string |
399
|
|
|
*/ |
400
|
|
|
private function genUniqueId() { |
401
|
|
|
// Generate a unique id from a former random-uuid-generator |
402
|
|
|
return sprintf('ID%04x%04x%04x%04x%04x%04x%04x%04x', |
403
|
|
|
mt_rand(0, 0xffff), |
404
|
|
|
mt_rand(0, 0xffff), |
405
|
|
|
mt_rand(0, 0xffff), |
406
|
|
|
mt_rand(0, 0x0fff) | 0x4000, |
407
|
|
|
mt_rand(0, 0x3fff) | 0x8000, |
408
|
|
|
mt_rand(0, 0xffff), |
409
|
|
|
mt_rand(0, 0xffff), |
410
|
|
|
mt_rand(0, 0xffff) |
411
|
|
|
); |
412
|
|
|
} |
413
|
|
|
} |
414
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.