1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* @package CleverStyle Framework |
4
|
|
|
* @author Nazar Mokrynskyi <[email protected]> |
5
|
|
|
* @copyright Copyright (c) 2011-2016, Nazar Mokrynskyi |
6
|
|
|
* @license MIT License, see license.txt |
7
|
|
|
*/ |
8
|
|
|
namespace cs\DB; |
9
|
|
|
use |
10
|
|
|
Exception; |
11
|
|
|
|
12
|
|
|
abstract class _Abstract { |
13
|
|
|
/** |
14
|
|
|
* Is connection established |
15
|
|
|
* |
16
|
|
|
* @var bool |
17
|
|
|
*/ |
18
|
|
|
protected $connected = false; |
19
|
|
|
/** |
20
|
|
|
* DB type, may be used for constructing requests, accounting particular features of current DB (lowercase name) |
21
|
|
|
* |
22
|
|
|
* @var string |
23
|
|
|
*/ |
24
|
|
|
protected $db_type = ''; |
25
|
|
|
/** |
26
|
|
|
* Current DB |
27
|
|
|
* |
28
|
|
|
* @var string |
29
|
|
|
*/ |
30
|
|
|
protected $database; |
31
|
|
|
/** |
32
|
|
|
* Current prefix |
33
|
|
|
* |
34
|
|
|
* @var string |
35
|
|
|
*/ |
36
|
|
|
protected $prefix; |
37
|
|
|
/** |
38
|
|
|
* Total time of requests execution |
39
|
|
|
* |
40
|
|
|
* @var int |
41
|
|
|
*/ |
42
|
|
|
protected $time; |
43
|
|
|
/** |
44
|
|
|
* Array for storing of data of the last executed request |
45
|
|
|
* |
46
|
|
|
* @var array |
47
|
|
|
*/ |
48
|
|
|
protected $query = [ |
49
|
|
|
'time' => '', |
50
|
|
|
'text' => '' |
51
|
|
|
]; |
52
|
|
|
/** |
53
|
|
|
* Array for storing data of all executed requests |
54
|
|
|
* |
55
|
|
|
* @var array |
56
|
|
|
*/ |
57
|
|
|
protected $queries = [ |
58
|
|
|
'num' => '', |
59
|
|
|
'time' => [], |
60
|
|
|
'text' => [] |
61
|
|
|
]; |
62
|
|
|
/** |
63
|
|
|
* Connection time |
64
|
|
|
* |
65
|
|
|
* @var float |
66
|
|
|
*/ |
67
|
|
|
protected $connecting_time; |
68
|
|
|
/** |
69
|
|
|
* @var bool |
70
|
|
|
*/ |
71
|
|
|
protected $in_transaction = false; |
72
|
|
|
/** |
73
|
|
|
* Connecting to the DB |
74
|
|
|
* |
75
|
|
|
* @param string $database |
76
|
|
|
* @param string $user |
77
|
|
|
* @param string $password |
78
|
|
|
* @param string $host |
79
|
|
|
* @param string $prefix |
80
|
|
|
*/ |
81
|
|
|
abstract function __construct ($database, $user = '', $password = '', $host = 'localhost', $prefix = ''); |
82
|
|
|
/** |
83
|
|
|
* Query |
84
|
|
|
* |
85
|
|
|
* SQL request into DB |
86
|
|
|
* |
87
|
|
|
* @abstract |
88
|
|
|
* |
89
|
|
|
* @param string|string[] $query SQL query string or array, may be a format string in accordance with the first parameter of sprintf() function |
90
|
|
|
* @param string|string[] $params May be array of arguments for formatting of <b>$query</b><br> |
91
|
|
|
* or string - in this case it will be first argument for formatting of <b>$query</b> |
92
|
|
|
* @param string[] $param if <b>$params</b> is string - this parameter will be second argument for formatting of <b>$query</b>. |
93
|
|
|
* If you need more arguments - add them after this one, function will accept them. |
94
|
|
|
* |
95
|
|
|
* @return false|object|resource |
96
|
|
|
*/ |
97
|
26 |
|
function q ($query, $params = [], ...$param) { |
98
|
26 |
|
$normalized = $this->prepare_and_normalize_arguments($query, func_get_args()); |
99
|
26 |
|
if (!$normalized) { |
100
|
|
|
return false; |
101
|
|
|
} |
102
|
26 |
|
list($query, $params) = $normalized; |
103
|
|
|
/** |
104
|
|
|
* Executing multiple queries |
105
|
|
|
*/ |
106
|
26 |
|
if (is_array($query)) { |
107
|
10 |
|
return $this->execute_multiple($query, $params); |
108
|
|
|
} |
109
|
26 |
|
return $this->execute_single($query, $params); |
110
|
|
|
} |
111
|
|
|
/** |
112
|
|
|
* @param string|string[] $query |
113
|
|
|
* @param array $arguments |
114
|
|
|
* |
115
|
|
|
* @return array|false |
|
|
|
|
116
|
|
|
*/ |
117
|
26 |
|
protected function prepare_and_normalize_arguments ($query, $arguments) { |
118
|
26 |
|
if (!$query || !$arguments) { |
119
|
|
|
return false; |
120
|
|
|
} |
121
|
26 |
|
$query = str_replace('[prefix]', $this->prefix, $query); |
122
|
26 |
|
switch (count($arguments)) { |
123
|
|
|
default: |
124
|
14 |
|
$params = array_slice($arguments, 1); |
125
|
14 |
|
break; |
126
|
26 |
|
case 1: |
127
|
24 |
|
$params = []; |
128
|
24 |
|
break; |
129
|
26 |
|
case 2: |
130
|
24 |
|
$params = (array)$arguments[1]; |
131
|
24 |
|
break; |
132
|
|
|
} |
133
|
26 |
|
foreach ($params as &$param) { |
134
|
26 |
|
$param = $this->s($param, false); |
135
|
|
|
} |
136
|
|
|
return [ |
137
|
26 |
|
$query, |
138
|
26 |
|
$params |
139
|
|
|
]; |
140
|
|
|
} |
141
|
|
|
/** |
142
|
|
|
* @param string[] $queries |
143
|
|
|
* @param string[] $params |
144
|
|
|
* |
145
|
|
|
* @return false|object|resource |
146
|
|
|
*/ |
147
|
10 |
|
protected function execute_multiple ($queries, $params) { |
148
|
10 |
|
$time_from = microtime(true); |
149
|
10 |
|
foreach ($queries as &$q) { |
150
|
10 |
|
$q = $params ? vsprintf($q, $params) : $q; |
151
|
|
|
} |
152
|
10 |
|
unset($q); |
153
|
10 |
|
$this->queries['num'] += count($queries); |
154
|
10 |
|
$result = $this->q_multi_internal($queries); |
155
|
10 |
|
$this->time += round(microtime(true) - $time_from, 6); |
156
|
10 |
|
return $result; |
157
|
|
|
} |
158
|
|
|
/** |
159
|
|
|
* @param string $query |
160
|
|
|
* @param string[] $params |
161
|
|
|
* |
162
|
|
|
* @return false|object|resource |
163
|
|
|
*/ |
164
|
26 |
|
protected function execute_single ($query, $params) { |
165
|
26 |
|
$time_from = microtime(true); |
166
|
26 |
|
$this->query['text'] = empty($params) ? $query : vsprintf($query, $params); |
167
|
26 |
|
if (DEBUG) { |
168
|
|
|
$this->queries['text'][] = $this->query['text']; |
169
|
|
|
} |
170
|
26 |
|
$result = $this->q_internal($this->query['text']); |
171
|
26 |
|
$this->query['time'] = round(microtime(true) - $time_from, 6); |
172
|
26 |
|
$this->time += $this->query['time']; |
173
|
26 |
|
if (DEBUG) { |
174
|
|
|
$this->queries['time'][] = $this->query['time']; |
175
|
|
|
} |
176
|
26 |
|
++$this->queries['num']; |
177
|
26 |
|
return $result; |
178
|
|
|
} |
179
|
|
|
/** |
180
|
|
|
* SQL request to DB |
181
|
|
|
* |
182
|
|
|
* @abstract |
183
|
|
|
* |
184
|
|
|
* @param string $query |
185
|
|
|
* |
186
|
|
|
* @return false|object|resource |
187
|
|
|
*/ |
188
|
|
|
abstract protected function q_internal ($query); |
189
|
|
|
/** |
190
|
|
|
* Multiple SQL request to DB |
191
|
|
|
* |
192
|
|
|
* @abstract |
193
|
|
|
* |
194
|
|
|
* @param string[] $query |
195
|
|
|
* |
196
|
|
|
* @return false|object|resource |
197
|
|
|
*/ |
198
|
|
|
abstract protected function q_multi_internal ($query); |
199
|
|
|
/** |
200
|
|
|
* Fetch |
201
|
|
|
* |
202
|
|
|
* Fetch a result row as an associative array |
203
|
|
|
* |
204
|
|
|
* @abstract |
205
|
|
|
* |
206
|
|
|
* @param false|object|resource $query_result |
207
|
|
|
* @param bool $single_column If <b>true</b> function will return not array with one element, but directly its value |
208
|
|
|
* @param bool $array If <b>true</b> returns array of associative arrays of all fetched rows |
209
|
|
|
* @param bool $indexed If <b>false</b> - associative array will be returned |
210
|
|
|
* |
211
|
|
|
* @return array[]|false|int|int[]|string|string[] |
212
|
|
|
*/ |
213
|
|
|
abstract function f ($query_result, $single_column = false, $array = false, $indexed = false); |
214
|
|
|
/** |
215
|
|
|
* Query, Fetch |
216
|
|
|
* |
217
|
|
|
* Short for `::f(::q())`, arguments are exactly the same as in `::q()` |
218
|
|
|
* |
219
|
|
|
* @param string[] $query |
220
|
|
|
* |
221
|
|
|
* @return array|false |
222
|
|
|
*/ |
223
|
24 |
|
function qf (...$query) { |
224
|
24 |
|
return $this->f($this->q(...$query)); |
225
|
|
|
} |
226
|
|
|
/** |
227
|
|
|
* Query, Fetch, Array |
228
|
|
|
* |
229
|
|
|
* Short for `::f(::q(), false, true)`, arguments are exactly the same as in `::q()` |
230
|
|
|
* |
231
|
|
|
* @param string[] $query |
232
|
|
|
* |
233
|
|
|
* @return array[]|false |
234
|
|
|
*/ |
235
|
11 |
|
function qfa (...$query) { |
236
|
11 |
|
return $this->f($this->q(...$query), false, true); |
237
|
|
|
} |
238
|
|
|
/** |
239
|
|
|
* Query, Fetch, Single |
240
|
|
|
* |
241
|
|
|
* Short for `::f(::q(), true)`, arguments are exactly the same as in `::q()` |
242
|
|
|
* |
243
|
|
|
* @param string[] $query |
244
|
|
|
* |
245
|
|
|
* @return false|int|string |
246
|
|
|
*/ |
247
|
14 |
|
function qfs (...$query) { |
248
|
14 |
|
return $this->f($this->q(...$query), true); |
249
|
|
|
} |
250
|
|
|
/** |
251
|
|
|
* Query, Fetch, Array, Single |
252
|
|
|
* |
253
|
|
|
* Short for `::f(::q(), true, true)`, arguments are exactly the same as in `::q()` |
254
|
|
|
* |
255
|
|
|
* @param string[] $query |
256
|
|
|
* |
257
|
|
|
* @return false|int[]|string[] |
258
|
|
|
*/ |
259
|
17 |
|
function qfas (...$query) { |
260
|
17 |
|
return $this->f($this->q(...$query), true, true); |
261
|
|
|
} |
262
|
|
|
/** |
263
|
|
|
* Method for simplified inserting of several rows |
264
|
|
|
* |
265
|
|
|
* @param string $query |
266
|
|
|
* @param array|array[] $params Array of array of parameters for inserting |
267
|
|
|
* @param bool $join If true - inserting of several rows will be combined in one query. For this, be sure, that your query has keyword |
268
|
|
|
* <i>VALUES</i> in uppercase. Part of query after this keyword will be multiplied with coma separator. |
269
|
|
|
* |
270
|
|
|
* @return bool |
271
|
|
|
*/ |
272
|
14 |
|
function insert ($query, $params, $join = true) { |
273
|
14 |
|
if (!$query || !$params) { |
274
|
|
|
return false; |
275
|
|
|
} |
276
|
14 |
|
if ($join) { |
277
|
14 |
|
$query = explode('VALUES', $query, 2); |
278
|
14 |
|
$query[1] = explode(')', $query[1], 2); |
279
|
|
|
$query = [ |
280
|
14 |
|
$query[0], |
281
|
14 |
|
$query[1][0].')', |
282
|
14 |
|
$query[1][1] |
283
|
|
|
]; |
284
|
14 |
|
if (!isset($query[1]) || !$query[1]) { |
285
|
|
|
return false; |
286
|
|
|
} |
287
|
14 |
|
$query[1] .= str_repeat(",$query[1]", count($params) - 1); |
288
|
14 |
|
$query = $query[0].'VALUES'.$query[1].$query[2]; |
289
|
14 |
|
return (bool)$this->q( |
290
|
|
|
$query, |
291
|
14 |
|
array_merge(...array_map('array_values', _array($params))) |
292
|
|
|
); |
293
|
|
|
} else { |
294
|
|
|
$result = true; |
295
|
|
|
foreach ($params as $p) { |
296
|
|
|
$result = $result && (bool)$this->q($query, $p); |
297
|
|
|
} |
298
|
|
|
return $result; |
299
|
|
|
} |
300
|
|
|
} |
301
|
|
|
/** |
302
|
|
|
* Id |
303
|
|
|
* |
304
|
|
|
* Get id of last inserted row |
305
|
|
|
* |
306
|
|
|
* @abstract |
307
|
|
|
* |
308
|
|
|
* @return int |
309
|
|
|
*/ |
310
|
|
|
abstract function id (); |
311
|
|
|
/** |
312
|
|
|
* Affected |
313
|
|
|
* |
314
|
|
|
* Get number of affected rows during last query |
315
|
|
|
* |
316
|
|
|
* @abstract |
317
|
|
|
* |
318
|
|
|
* @return int |
319
|
|
|
*/ |
320
|
|
|
abstract function affected (); |
321
|
|
|
/** |
322
|
|
|
* Execute transaction |
323
|
|
|
* |
324
|
|
|
* All queries done inside callback will be within single transaction, throwing any exception or returning boolean `false` from callback will cause |
325
|
|
|
* rollback. Nested transaction calls will be wrapped into single big outer transaction, so you might call it safely if needed. |
326
|
|
|
* |
327
|
|
|
* @param callable $callback This instance will be used as single argument |
328
|
|
|
* |
329
|
|
|
* @return bool |
330
|
|
|
* |
331
|
|
|
* @throws Exception Re-throws exception thrown inside callback |
332
|
|
|
*/ |
333
|
24 |
|
function transaction ($callback) { |
334
|
24 |
|
$start_transaction = !$this->in_transaction; |
335
|
24 |
|
if ($start_transaction) { |
336
|
24 |
|
$this->in_transaction = true; |
337
|
24 |
|
if (!$this->q_internal('BEGIN')) { |
338
|
|
|
return false; |
339
|
|
|
} |
340
|
|
|
} |
341
|
|
|
try { |
342
|
24 |
|
$result = $callback($this); |
343
|
2 |
|
} catch (Exception $e) { |
344
|
2 |
|
$this->transaction_rollback(); |
345
|
2 |
|
throw $e; |
346
|
|
|
} |
347
|
24 |
|
if ($result === false) { |
348
|
2 |
|
$this->transaction_rollback(); |
349
|
2 |
|
return false; |
350
|
24 |
|
} elseif ($start_transaction) { |
351
|
24 |
|
$this->in_transaction = false; |
352
|
24 |
|
return (bool)$this->q_internal('COMMIT'); |
353
|
|
|
} |
354
|
2 |
|
return true; |
355
|
|
|
} |
356
|
2 |
|
protected function transaction_rollback () { |
357
|
2 |
|
if ($this->in_transaction) { |
358
|
2 |
|
$this->in_transaction = false; |
359
|
2 |
|
$this->q_internal('ROLLBACK'); |
360
|
|
|
} |
361
|
2 |
|
} |
362
|
|
|
/** |
363
|
|
|
* Free result memory |
364
|
|
|
* |
365
|
|
|
* @abstract |
366
|
|
|
* |
367
|
|
|
* @param false|object|resource $query_result |
368
|
|
|
*/ |
369
|
|
|
abstract function free ($query_result); |
370
|
|
|
/** |
371
|
|
|
* Get columns list of table |
372
|
|
|
* |
373
|
|
|
* @param string $table |
374
|
|
|
* @param false|string $like |
375
|
|
|
* |
376
|
|
|
* @return string[] |
377
|
|
|
*/ |
378
|
|
|
abstract function columns ($table, $like = false); |
379
|
|
|
/** |
380
|
|
|
* Get tables list |
381
|
|
|
* |
382
|
|
|
* @param false|string $like |
383
|
|
|
* |
384
|
|
|
* @return string[] |
385
|
|
|
*/ |
386
|
|
|
abstract function tables ($like = false); |
387
|
|
|
/** |
388
|
|
|
* Safe |
389
|
|
|
* |
390
|
|
|
* Preparing string for using in SQL query |
391
|
|
|
* SQL Injection Protection |
392
|
|
|
* |
393
|
|
|
* @param string|string[] $string |
394
|
|
|
* @param bool $single_quotes_around |
395
|
|
|
* |
396
|
|
|
* @return string|string[] |
397
|
|
|
*/ |
398
|
26 |
|
function s ($string, $single_quotes_around = true) { |
399
|
26 |
|
if (is_array($string)) { |
400
|
2 |
|
foreach ($string as &$s) { |
401
|
2 |
|
$s = $this->s_internal($s, $single_quotes_around); |
402
|
|
|
} |
403
|
2 |
|
return $string; |
404
|
|
|
} |
405
|
26 |
|
return $this->s_internal($string, $single_quotes_around); |
406
|
|
|
} |
407
|
|
|
/** |
408
|
|
|
* Preparing string for using in SQL query |
409
|
|
|
* SQL Injection Protection |
410
|
|
|
* |
411
|
|
|
* @param string $string |
412
|
|
|
* @param bool $single_quotes_around |
413
|
|
|
* |
414
|
|
|
* @return string |
415
|
|
|
*/ |
416
|
|
|
abstract protected function s_internal ($string, $single_quotes_around); |
417
|
|
|
/** |
418
|
|
|
* Get information about server |
419
|
|
|
* |
420
|
|
|
* @return string |
421
|
|
|
*/ |
422
|
|
|
abstract function server (); |
423
|
|
|
/** |
424
|
|
|
* Connection state |
425
|
|
|
* |
426
|
|
|
* @return bool |
427
|
|
|
*/ |
428
|
32 |
|
function connected () { |
429
|
32 |
|
return $this->connected; |
430
|
|
|
} |
431
|
|
|
/** |
432
|
|
|
* Database type (lowercase, for example <i>mysql</i>) |
433
|
|
|
* |
434
|
|
|
* @return string |
435
|
|
|
*/ |
436
|
|
|
function db_type () { |
437
|
|
|
return $this->db_type; |
438
|
|
|
} |
439
|
|
|
/** |
440
|
|
|
* Database name |
441
|
|
|
* |
442
|
|
|
* @return string |
443
|
|
|
*/ |
444
|
|
|
function database () { |
445
|
|
|
return $this->database; |
446
|
|
|
} |
447
|
|
|
/** |
448
|
|
|
* Queries array, has 3 properties:<ul> |
449
|
|
|
* <li>num - total number of performed queries |
450
|
|
|
* <li>time - array with time of each query execution |
451
|
|
|
* <li>text - array with text text of each query |
452
|
|
|
* |
453
|
|
|
* @return array |
454
|
|
|
*/ |
455
|
2 |
|
function queries () { |
456
|
2 |
|
return $this->queries; |
457
|
|
|
} |
458
|
|
|
/** |
459
|
|
|
* Last query information, has 2 properties:<ul> |
460
|
|
|
* <li>time - execution time |
461
|
|
|
* <li>text - query text |
462
|
|
|
* |
463
|
|
|
* @return array |
464
|
|
|
*/ |
465
|
|
|
function query () { |
466
|
|
|
return $this->query; |
467
|
|
|
} |
468
|
|
|
/** |
469
|
|
|
* Total working time (including connection, queries execution and other delays) |
470
|
|
|
* |
471
|
|
|
* @return int |
472
|
|
|
*/ |
473
|
2 |
|
function time () { |
474
|
2 |
|
return $this->time; |
475
|
|
|
} |
476
|
|
|
/** |
477
|
|
|
* Connecting time |
478
|
|
|
* |
479
|
|
|
* @return float |
480
|
|
|
*/ |
481
|
2 |
|
function connecting_time () { |
482
|
2 |
|
return $this->connecting_time; |
483
|
|
|
} |
484
|
|
|
/** |
485
|
|
|
* Cloning restriction |
486
|
|
|
* |
487
|
|
|
* @final |
488
|
|
|
*/ |
489
|
|
|
final function __clone () { |
490
|
|
|
} |
491
|
|
|
/** |
492
|
|
|
* Disconnecting from DB |
493
|
|
|
*/ |
494
|
|
|
abstract function __destruct (); |
495
|
|
|
} |
496
|
|
|
|
This check looks for the generic type
array
as a return type and suggests a more specific type. This type is inferred from the actual code.