1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/* |
4
|
|
|
* dbFacile - A Database API that should have existed from the start |
5
|
|
|
* Version 0.4.3 |
6
|
|
|
* |
7
|
|
|
* This code is covered by the MIT license http://en.wikipedia.org/wiki/MIT_License |
8
|
|
|
* |
9
|
|
|
* By Alan Szlosek from http://www.greaterscope.net/projects/dbFacile |
10
|
|
|
* |
11
|
|
|
* The non-OO version of dbFacile. It's a bit simplistic, but gives you the |
12
|
|
|
* really useful bits in non-class form. |
13
|
|
|
* |
14
|
|
|
* Usage |
15
|
|
|
* 1. Connect to MySQL as you normally would ... this code uses an existing connection |
16
|
|
|
* 2. Use dbFacile as you normally would, without the object context |
17
|
|
|
* 3. Oh, and dbFetchAll() is now dbFetchRows() |
18
|
|
|
*/ |
19
|
|
|
|
20
|
|
|
use Illuminate\Database\QueryException; |
21
|
|
|
use LibreNMS\Config; |
22
|
|
|
use LibreNMS\DB\Eloquent; |
23
|
|
|
use LibreNMS\Exceptions\DatabaseConnectException; |
24
|
|
|
use LibreNMS\Util\Laravel; |
25
|
|
|
|
26
|
|
|
function dbIsConnected() |
27
|
|
|
{ |
28
|
|
|
return Eloquent::isConnected(); |
29
|
|
|
} |
30
|
|
|
|
31
|
|
|
/** |
32
|
|
|
* Connect to the database. |
33
|
|
|
* Will use global config variables if they are not sent: db_host, db_user, db_pass, db_name, db_port, db_socket |
34
|
|
|
* |
35
|
|
|
* @param string $db_host |
36
|
|
|
* @param string $db_user |
37
|
|
|
* @param string $db_pass |
38
|
|
|
* @param string $db_name |
39
|
|
|
* @param string $db_port |
40
|
|
|
* @param string $db_socket |
41
|
|
|
* @return \Illuminate\Database\Connection |
42
|
|
|
* |
43
|
|
|
* @throws DatabaseConnectException |
44
|
|
|
*/ |
45
|
|
|
function dbConnect($db_host = null, $db_user = '', $db_pass = '', $db_name = '', $db_port = null, $db_socket = null) |
46
|
|
|
{ |
47
|
|
|
if (Eloquent::isConnected()) { |
48
|
|
|
return Eloquent::DB(); |
49
|
|
|
} |
50
|
|
|
|
51
|
|
|
if (! extension_loaded('pdo_mysql')) { |
52
|
|
|
throw new DatabaseConnectException('PHP pdo_mysql extension not loaded!'); |
53
|
|
|
} |
54
|
|
|
|
55
|
|
|
try { |
56
|
|
|
if (! is_null($db_host) || ! empty($db_name)) { |
57
|
|
|
// legacy connection override |
58
|
|
|
\Config::set('database.connections.setup', [ |
59
|
|
|
'driver' => 'mysql', |
60
|
|
|
'host' => $db_host, |
61
|
|
|
'port' => $db_port, |
62
|
|
|
'database' => $db_name, |
63
|
|
|
'username' => $db_user, |
64
|
|
|
'password' => $db_pass, |
65
|
|
|
'unix_socket' => $db_socket, |
66
|
|
|
'charset' => 'utf8mb4', |
67
|
|
|
'collation' => 'utf8mb4_unicode_ci', |
68
|
|
|
'prefix' => '', |
69
|
|
|
'strict' => true, |
70
|
|
|
'engine' => null, |
71
|
|
|
]); |
72
|
|
|
\Config::set('database.default', 'setup'); |
73
|
|
|
} |
74
|
|
|
|
75
|
|
|
Eloquent::boot(); |
76
|
|
|
} catch (PDOException $e) { |
77
|
|
|
throw new DatabaseConnectException($e->getMessage(), $e->getCode(), $e); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
return Eloquent::DB(); |
81
|
|
|
} |
82
|
|
|
|
83
|
|
|
/** |
84
|
|
|
* Performs a query using the given string. |
85
|
|
|
* |
86
|
|
|
* @param string $sql |
87
|
|
|
* @param array $parameters |
88
|
|
|
* @return bool if query was successful or not |
89
|
|
|
*/ |
90
|
|
|
function dbQuery($sql, $parameters = []) |
91
|
|
|
{ |
92
|
|
|
try { |
93
|
|
|
if (empty($parameters)) { |
94
|
|
|
// don't use prepared statements for queries without parameters |
95
|
|
|
return Eloquent::DB()->getPdo()->exec($sql) !== false; |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
return Eloquent::DB()->statement($sql, (array) $parameters); |
99
|
|
|
} catch (PDOException $pdoe) { |
100
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
101
|
|
|
|
102
|
|
|
return false; |
103
|
|
|
} |
104
|
|
|
} |
105
|
|
|
|
106
|
|
|
/** |
107
|
|
|
* @param array $data |
108
|
|
|
* @param string $table |
109
|
|
|
* @return null|int |
110
|
|
|
*/ |
111
|
|
|
function dbInsert($data, $table) |
112
|
|
|
{ |
113
|
|
|
$time_start = microtime(true); |
114
|
|
|
|
115
|
|
|
$sql = 'INSERT IGNORE INTO `' . $table . '` (`' . implode('`,`', array_keys($data)) . '`) VALUES (' . implode(',', dbPlaceHolders($data)) . ')'; |
116
|
|
|
|
117
|
|
|
try { |
118
|
|
|
$result = Eloquent::DB()->insert($sql, (array) $data); |
119
|
|
|
} catch (PDOException $pdoe) { |
120
|
|
|
dbHandleException(new QueryException($sql, $data, $pdoe)); |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
recordDbStatistic('insert', $time_start); |
124
|
|
|
if ($result) { |
125
|
|
|
return Eloquent::DB()->getPdo()->lastInsertId(); |
126
|
|
|
} else { |
127
|
|
|
return null; |
128
|
|
|
} |
129
|
|
|
}//end dbInsert() |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* Passed an array and a table name, it attempts to insert the data into the table. |
133
|
|
|
* $data is an array (rows) of key value pairs. keys are fields. Rows need to have same fields. |
134
|
|
|
* Check for boolean false to determine whether insert failed |
135
|
|
|
* |
136
|
|
|
* @param array $data |
137
|
|
|
* @param string $table |
138
|
|
|
* @return bool |
139
|
|
|
*/ |
140
|
|
|
function dbBulkInsert($data, $table) |
141
|
|
|
{ |
142
|
|
|
$time_start = microtime(true); |
143
|
|
|
|
144
|
|
|
// check that data isn't an empty array |
145
|
|
|
if (empty($data)) { |
146
|
|
|
return false; |
147
|
|
|
} |
148
|
|
|
|
149
|
|
|
// make sure we have fields to insert |
150
|
|
|
$fields = array_keys(reset($data)); |
151
|
|
|
if (empty($fields)) { |
152
|
|
|
return false; |
153
|
|
|
} |
154
|
|
|
|
155
|
|
|
// Break into managable chunks to prevent situations where insert |
156
|
|
|
// fails due to prepared statement having too many placeholders. |
157
|
|
|
$data_chunks = array_chunk($data, 10000, true); |
158
|
|
|
|
159
|
|
|
foreach ($data_chunks as $data_chunk) { |
160
|
|
|
try { |
161
|
|
|
$result = Eloquent::DB()->table($table)->insert((array) $data_chunk); |
162
|
|
|
|
163
|
|
|
recordDbStatistic('insert', $time_start); |
|
|
|
|
164
|
|
|
|
165
|
|
|
return $result; |
166
|
|
|
} catch (PDOException $pdoe) { |
167
|
|
|
// FIXME query? |
168
|
|
|
dbHandleException(new QueryException("Bulk insert $table", $data_chunk, $pdoe)); |
169
|
|
|
} |
170
|
|
|
} |
171
|
|
|
|
172
|
|
|
return false; |
173
|
|
|
}//end dbBulkInsert() |
174
|
|
|
|
175
|
|
|
/** |
176
|
|
|
* Passed an array, table name, WHERE clause, and placeholder parameters, it attempts to update a record. |
177
|
|
|
* Returns the number of affected rows |
178
|
|
|
* |
179
|
|
|
* @param array $data |
180
|
|
|
* @param string $table |
181
|
|
|
* @param string $where |
182
|
|
|
* @param array $parameters |
183
|
|
|
* @return bool|int |
184
|
|
|
*/ |
185
|
|
|
function dbUpdate($data, $table, $where = null, $parameters = []) |
186
|
|
|
{ |
187
|
|
|
$time_start = microtime(true); |
188
|
|
|
|
189
|
|
|
// need field name and placeholder value |
190
|
|
|
// but how merge these field placeholders with actual $parameters array for the WHERE clause |
191
|
|
|
$sql = 'UPDATE `' . $table . '` set '; |
192
|
|
|
foreach ($data as $key => $value) { |
193
|
|
|
$sql .= '`' . $key . '`='; |
194
|
|
|
if (is_array($value)) { |
195
|
|
|
$sql .= reset($value); |
196
|
|
|
unset($data[$key]); |
197
|
|
|
} else { |
198
|
|
|
$sql .= '?'; |
199
|
|
|
} |
200
|
|
|
$sql .= ','; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
// strip keys |
204
|
|
|
$data = array_values($data); |
205
|
|
|
|
206
|
|
|
$sql = substr($sql, 0, -1); |
207
|
|
|
// strip off last comma |
208
|
|
|
if ($where) { |
209
|
|
|
$sql .= ' WHERE ' . $where; |
210
|
|
|
$data = array_merge($data, $parameters); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
try { |
214
|
|
|
$result = Eloquent::DB()->update($sql, (array) $data); |
215
|
|
|
|
216
|
|
|
recordDbStatistic('update', $time_start); |
217
|
|
|
|
218
|
|
|
return $result; |
219
|
|
|
} catch (PDOException $pdoe) { |
220
|
|
|
dbHandleException(new QueryException($sql, $data, $pdoe)); |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
return false; |
224
|
|
|
}//end dbUpdate() |
225
|
|
|
|
226
|
|
|
function dbDelete($table, $where = null, $parameters = []) |
227
|
|
|
{ |
228
|
|
|
$time_start = microtime(true); |
229
|
|
|
|
230
|
|
|
$sql = 'DELETE FROM `' . $table . '`'; |
231
|
|
|
if ($where) { |
232
|
|
|
$sql .= ' WHERE ' . $where; |
233
|
|
|
} |
234
|
|
|
|
235
|
|
|
try { |
236
|
|
|
$result = Eloquent::DB()->delete($sql, (array) $parameters); |
237
|
|
|
} catch (PDOException $pdoe) { |
238
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
239
|
|
|
} |
240
|
|
|
|
241
|
|
|
recordDbStatistic('delete', $time_start); |
242
|
|
|
|
243
|
|
|
return $result; |
244
|
|
|
}//end dbDelete() |
245
|
|
|
|
246
|
|
|
/** |
247
|
|
|
* Delete orphaned entries from a table that no longer have a parent in parent_table |
248
|
|
|
* Format of parents array is as follows table.table_key_column<.target_key_column> |
249
|
|
|
* |
250
|
|
|
* @param string $target_table The table to delete entries from |
251
|
|
|
* @param array $parents an array of parent tables to check. |
252
|
|
|
* @return bool|int |
253
|
|
|
*/ |
254
|
|
|
function dbDeleteOrphans($target_table, $parents) |
255
|
|
|
{ |
256
|
|
|
$time_start = microtime(true); |
257
|
|
|
|
258
|
|
|
if (empty($parents)) { |
259
|
|
|
// don't delete all entries if parents is missing |
260
|
|
|
return false; |
261
|
|
|
} |
262
|
|
|
|
263
|
|
|
$target_table = $target_table; |
264
|
|
|
$sql = "DELETE T FROM `$target_table` T"; |
265
|
|
|
$where = []; |
266
|
|
|
|
267
|
|
|
foreach ((array) $parents as $parent) { |
268
|
|
|
$parent_parts = explode('.', $parent); |
269
|
|
|
if (count($parent_parts) == 2) { |
270
|
|
|
[$parent_table, $parent_column] = $parent_parts; |
271
|
|
|
$target_column = $parent_column; |
272
|
|
|
} elseif (count($parent_parts) == 3) { |
273
|
|
|
[$parent_table, $parent_column, $target_column] = $parent_parts; |
274
|
|
|
} else { |
275
|
|
|
// invalid input |
276
|
|
|
return false; |
277
|
|
|
} |
278
|
|
|
|
279
|
|
|
$sql .= " LEFT JOIN `$parent_table` ON `$parent_table`.`$parent_column` = T.`$target_column`"; |
280
|
|
|
$where[] = " `$parent_table`.`$parent_column` IS NULL"; |
281
|
|
|
} |
282
|
|
|
|
283
|
|
|
$query = "$sql WHERE" . implode(' AND', $where); |
284
|
|
|
|
285
|
|
|
try { |
286
|
|
|
$result = Eloquent::DB()->delete($query); |
287
|
|
|
} catch (PDOException $pdoe) { |
288
|
|
|
dbHandleException(new QueryException($query, [], $pdoe)); |
289
|
|
|
} |
290
|
|
|
|
291
|
|
|
recordDbStatistic('delete', $time_start); |
292
|
|
|
|
293
|
|
|
return $result; |
294
|
|
|
} |
295
|
|
|
|
296
|
|
|
/* |
297
|
|
|
* Fetches all of the rows (associatively) from the last performed query. |
298
|
|
|
* Most other retrieval functions build off this |
299
|
|
|
* */ |
300
|
|
|
|
301
|
|
|
function dbFetchRows($sql, $parameters = []) |
302
|
|
|
{ |
303
|
|
|
global $PDO_FETCH_ASSOC; |
304
|
|
|
$time_start = microtime(true); |
305
|
|
|
|
306
|
|
|
try { |
307
|
|
|
$PDO_FETCH_ASSOC = true; |
308
|
|
|
$rows = Eloquent::DB()->select($sql, (array) $parameters); |
309
|
|
|
|
310
|
|
|
recordDbStatistic('fetchrows', $time_start); |
311
|
|
|
|
312
|
|
|
return $rows; |
313
|
|
|
} catch (PDOException $pdoe) { |
314
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
315
|
|
|
} finally { |
316
|
|
|
$PDO_FETCH_ASSOC = false; |
317
|
|
|
} |
318
|
|
|
|
319
|
|
|
return []; |
320
|
|
|
}//end dbFetchRows() |
321
|
|
|
|
322
|
|
|
/* |
323
|
|
|
* This is intended to be the method used for large result sets. |
324
|
|
|
* It is intended to return an iterator, and act upon buffered data. |
325
|
|
|
* */ |
326
|
|
|
|
327
|
|
|
function dbFetch($sql, $parameters = []) |
328
|
|
|
{ |
329
|
|
|
return dbFetchRows($sql, $parameters); |
330
|
|
|
/* |
331
|
|
|
// for now, don't do the iterator thing |
332
|
|
|
$result = dbQuery($sql, $parameters); |
333
|
|
|
if($result) { |
334
|
|
|
// return new iterator |
335
|
|
|
return new dbIterator($result); |
336
|
|
|
} else { |
337
|
|
|
return null; // ?? |
338
|
|
|
} |
339
|
|
|
*/ |
340
|
|
|
}//end dbFetch() |
341
|
|
|
|
342
|
|
|
/* |
343
|
|
|
* Like fetch(), accepts any number of arguments |
344
|
|
|
* The first argument is an sprintf-ready query stringTypes |
345
|
|
|
* */ |
346
|
|
|
|
347
|
|
|
function dbFetchRow($sql = null, $parameters = []) |
348
|
|
|
{ |
349
|
|
|
global $PDO_FETCH_ASSOC; |
350
|
|
|
$time_start = microtime(true); |
351
|
|
|
|
352
|
|
|
try { |
353
|
|
|
$PDO_FETCH_ASSOC = true; |
354
|
|
|
$row = Eloquent::DB()->selectOne($sql, (array) $parameters); |
355
|
|
|
|
356
|
|
|
recordDbStatistic('fetchrow', $time_start); |
357
|
|
|
|
358
|
|
|
return $row; |
359
|
|
|
} catch (PDOException $pdoe) { |
360
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
361
|
|
|
} finally { |
362
|
|
|
$PDO_FETCH_ASSOC = false; |
363
|
|
|
} |
364
|
|
|
|
365
|
|
|
return []; |
366
|
|
|
}//end dbFetchRow() |
367
|
|
|
|
368
|
|
|
/* |
369
|
|
|
* Fetches the first call from the first row returned by the query |
370
|
|
|
* */ |
371
|
|
|
|
372
|
|
|
function dbFetchCell($sql, $parameters = []) |
373
|
|
|
{ |
374
|
|
|
global $PDO_FETCH_ASSOC; |
375
|
|
|
$time_start = microtime(true); |
376
|
|
|
|
377
|
|
|
try { |
378
|
|
|
$PDO_FETCH_ASSOC = true; |
379
|
|
|
$row = Eloquent::DB()->selectOne($sql, (array) $parameters); |
380
|
|
|
recordDbStatistic('fetchcell', $time_start); |
381
|
|
|
if ($row) { |
382
|
|
|
return reset($row); |
383
|
|
|
// shift first field off first row |
384
|
|
|
} |
385
|
|
|
} catch (PDOException $pdoe) { |
386
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
387
|
|
|
} finally { |
388
|
|
|
$PDO_FETCH_ASSOC = false; |
389
|
|
|
} |
390
|
|
|
|
391
|
|
|
return null; |
392
|
|
|
}//end dbFetchCell() |
393
|
|
|
|
394
|
|
|
/* |
395
|
|
|
* This method is quite different from fetchCell(), actually |
396
|
|
|
* It fetches one cell from each row and places all the values in 1 array |
397
|
|
|
* */ |
398
|
|
|
|
399
|
|
|
function dbFetchColumn($sql, $parameters = []) |
400
|
|
|
{ |
401
|
|
|
global $PDO_FETCH_ASSOC; |
402
|
|
|
$time_start = microtime(true); |
403
|
|
|
|
404
|
|
|
$cells = []; |
405
|
|
|
|
406
|
|
|
try { |
407
|
|
|
$PDO_FETCH_ASSOC = true; |
408
|
|
|
foreach (Eloquent::DB()->select($sql, (array) $parameters) as $row) { |
409
|
|
|
$cells[] = reset($row); |
410
|
|
|
} |
411
|
|
|
$PDO_FETCH_ASSOC = false; |
412
|
|
|
|
413
|
|
|
recordDbStatistic('fetchcolumn', $time_start); |
414
|
|
|
|
415
|
|
|
return $cells; |
416
|
|
|
} catch (PDOException $pdoe) { |
417
|
|
|
dbHandleException(new QueryException($sql, $parameters, $pdoe)); |
418
|
|
|
} finally { |
419
|
|
|
$PDO_FETCH_ASSOC = false; |
420
|
|
|
} |
421
|
|
|
|
422
|
|
|
return []; |
423
|
|
|
}//end dbFetchColumn() |
424
|
|
|
|
425
|
|
|
/* |
426
|
|
|
* Should be passed a query that fetches two fields |
427
|
|
|
* The first will become the array key |
428
|
|
|
* The second the key's value |
429
|
|
|
*/ |
430
|
|
|
|
431
|
|
|
function dbFetchKeyValue($sql, $parameters = []) |
432
|
|
|
{ |
433
|
|
|
$data = []; |
434
|
|
|
foreach (dbFetch($sql, $parameters) as $row) { |
435
|
|
|
$key = array_shift($row); |
436
|
|
|
if (sizeof($row) == 1) { |
437
|
|
|
// if there were only 2 fields in the result |
438
|
|
|
// use the second for the value |
439
|
|
|
$data[$key] = array_shift($row); |
440
|
|
|
} else { |
441
|
|
|
// if more than 2 fields were fetched |
442
|
|
|
// use the array of the rest as the value |
443
|
|
|
$data[$key] = $row; |
444
|
|
|
} |
445
|
|
|
} |
446
|
|
|
|
447
|
|
|
return $data; |
448
|
|
|
}//end dbFetchKeyValue() |
449
|
|
|
|
450
|
|
|
/** |
451
|
|
|
* Legacy dbFacile indicates DB::raw() as a value wrapped in an array |
452
|
|
|
* |
453
|
|
|
* @param array $data |
454
|
|
|
* @return array |
455
|
|
|
*/ |
456
|
|
|
function dbArrayToRaw($data) |
457
|
|
|
{ |
458
|
|
|
array_walk($data, function (&$item) { |
459
|
|
|
if (is_array($item)) { |
460
|
|
|
$item = Eloquent::DB()->raw(reset($item)); |
461
|
|
|
} |
462
|
|
|
}); |
463
|
|
|
|
464
|
|
|
return $data; |
465
|
|
|
} |
466
|
|
|
|
467
|
|
|
function dbHandleException(QueryException $exception) |
468
|
|
|
{ |
469
|
|
|
$message = $exception->getMessage(); |
470
|
|
|
|
471
|
|
|
if ($exception->getCode() == 2002) { |
472
|
|
|
$message = 'Could not connect to database! ' . $message; |
473
|
|
|
} |
474
|
|
|
|
475
|
|
|
// ? bindings should already be replaced, just replace named bindings |
476
|
|
|
foreach ($exception->getBindings() as $key => $value) { |
477
|
|
|
if (is_string($key)) { |
478
|
|
|
$message = str_replace(":$key", $value, $message); |
479
|
|
|
} |
480
|
|
|
} |
481
|
|
|
|
482
|
|
|
$message .= $exception->getTraceAsString(); |
483
|
|
|
|
484
|
|
|
if (Laravel::isBooted()) { |
485
|
|
|
Log::error($message); |
486
|
|
|
} else { |
487
|
|
|
c_echo('%rSQL Error!%n '); |
488
|
|
|
echo $message . PHP_EOL; |
489
|
|
|
} |
490
|
|
|
|
491
|
|
|
// TODO remove this |
492
|
|
|
// exit; |
493
|
|
|
} |
494
|
|
|
|
495
|
|
|
/** |
496
|
|
|
* Given a data array, this returns an array of placeholders |
497
|
|
|
* These may be question marks, or ":email" type |
498
|
|
|
* |
499
|
|
|
* @param array $values |
500
|
|
|
* @return array |
501
|
|
|
*/ |
502
|
|
|
function dbPlaceHolders(&$values) |
503
|
|
|
{ |
504
|
|
|
$data = []; |
505
|
|
|
foreach ($values as $key => $value) { |
506
|
|
|
if (is_array($value)) { |
507
|
|
|
// array wrapped values are raw sql |
508
|
|
|
$data[] = reset($value); |
509
|
|
|
unset($values[$key]); |
510
|
|
|
} elseif (is_numeric($key)) { |
511
|
|
|
$data[] = '?'; |
512
|
|
|
} else { |
513
|
|
|
$data[] = ':' . $key; |
514
|
|
|
} |
515
|
|
|
} |
516
|
|
|
|
517
|
|
|
return $data; |
518
|
|
|
}//end dbPlaceHolders() |
519
|
|
|
|
520
|
|
|
function dbBeginTransaction() |
521
|
|
|
{ |
522
|
|
|
Eloquent::DB()->beginTransaction(); |
523
|
|
|
}//end dbBeginTransaction() |
524
|
|
|
|
525
|
|
|
function dbCommitTransaction() |
526
|
|
|
{ |
527
|
|
|
Eloquent::DB()->commit(); |
528
|
|
|
}//end dbCommitTransaction() |
529
|
|
|
|
530
|
|
|
function dbRollbackTransaction() |
531
|
|
|
{ |
532
|
|
|
Eloquent::DB()->rollBack(); |
533
|
|
|
}//end dbRollbackTransaction() |
534
|
|
|
|
535
|
|
|
/** |
536
|
|
|
* Generate a string of placeholders to pass to fill in a list |
537
|
|
|
* result will look like this: (?, ?, ?, ?) |
538
|
|
|
* |
539
|
|
|
* @param $count |
540
|
|
|
* @return string placholder list |
541
|
|
|
*/ |
542
|
|
|
function dbGenPlaceholders($count) |
543
|
|
|
{ |
544
|
|
|
return '(' . implode(',', array_fill(0, $count, '?')) . ')'; |
545
|
|
|
} |
546
|
|
|
|
547
|
|
|
/** |
548
|
|
|
* Update statistics for db operations |
549
|
|
|
* |
550
|
|
|
* @param string $stat fetchcell, fetchrow, fetchrows, fetchcolumn, update, insert, delete |
551
|
|
|
* @param float $start_time The time the operation started with 'microtime(true)' |
552
|
|
|
* @return float The calculated run time |
553
|
|
|
*/ |
554
|
|
|
function recordDbStatistic($stat, $start_time) |
555
|
|
|
{ |
556
|
|
|
global $db_stats, $db_stats_last; |
557
|
|
|
|
558
|
|
|
if (! isset($db_stats)) { |
559
|
|
|
$db_stats = [ |
560
|
|
|
'ops' => [ |
561
|
|
|
'insert' => 0, |
562
|
|
|
'update' => 0, |
563
|
|
|
'delete' => 0, |
564
|
|
|
'fetchcell' => 0, |
565
|
|
|
'fetchcolumn' => 0, |
566
|
|
|
'fetchrow' => 0, |
567
|
|
|
'fetchrows' => 0, |
568
|
|
|
], |
569
|
|
|
'time' => [ |
570
|
|
|
'insert' => 0.0, |
571
|
|
|
'update' => 0.0, |
572
|
|
|
'delete' => 0.0, |
573
|
|
|
'fetchcell' => 0.0, |
574
|
|
|
'fetchcolumn' => 0.0, |
575
|
|
|
'fetchrow' => 0.0, |
576
|
|
|
'fetchrows' => 0.0, |
577
|
|
|
], |
578
|
|
|
]; |
579
|
|
|
$db_stats_last = $db_stats; |
580
|
|
|
} |
581
|
|
|
|
582
|
|
|
$runtime = microtime(true) - $start_time; |
583
|
|
|
$db_stats['ops'][$stat]++; |
584
|
|
|
$db_stats['time'][$stat] += $runtime; |
585
|
|
|
|
586
|
|
|
//double accounting corrections |
587
|
|
|
if ($stat == 'fetchcolumn') { |
588
|
|
|
$db_stats['ops']['fetchrows']--; |
589
|
|
|
$db_stats['time']['fetchrows'] -= $runtime; |
590
|
|
|
} |
591
|
|
|
if ($stat == 'fetchcell') { |
592
|
|
|
$db_stats['ops']['fetchrow']--; |
593
|
|
|
$db_stats['time']['fetchrow'] -= $runtime; |
594
|
|
|
} |
595
|
|
|
|
596
|
|
|
return $runtime; |
597
|
|
|
} |
598
|
|
|
|
599
|
|
|
/** |
600
|
|
|
* Synchronize a relationship to a list of related ids |
601
|
|
|
* |
602
|
|
|
* @param string $table |
603
|
|
|
* @param string $target_column column name for the target |
604
|
|
|
* @param int $target column target id |
605
|
|
|
* @param string $list_column related column names |
606
|
|
|
* @param array $list list of related ids |
607
|
|
|
* @return array [$inserted, $deleted] |
608
|
|
|
*/ |
609
|
|
|
function dbSyncRelationship($table, $target_column = null, $target = null, $list_column = null, $list = null) |
610
|
|
|
{ |
611
|
|
|
$inserted = 0; |
612
|
|
|
|
613
|
|
|
$delete_query = "`$target_column`=? AND `$list_column`"; |
614
|
|
|
$delete_params = [$target]; |
615
|
|
|
if (! empty($list)) { |
616
|
|
|
$delete_query .= ' NOT IN ' . dbGenPlaceholders(count($list)); |
617
|
|
|
$delete_params = array_merge($delete_params, $list); |
618
|
|
|
} |
619
|
|
|
$deleted = (int) dbDelete($table, $delete_query, $delete_params); |
620
|
|
|
|
621
|
|
|
$db_list = dbFetchColumn("SELECT `$list_column` FROM `$table` WHERE `$target_column`=?", [$target]); |
622
|
|
|
foreach ($list as $item) { |
623
|
|
|
if (! in_array($item, $db_list)) { |
624
|
|
|
dbInsert([$target_column => $target, $list_column => $item], $table); |
625
|
|
|
$inserted++; |
626
|
|
|
} |
627
|
|
|
} |
628
|
|
|
|
629
|
|
|
return [$inserted, $deleted]; |
630
|
|
|
} |
631
|
|
|
|
632
|
|
|
/** |
633
|
|
|
* Synchronize a relationship to a list of relations |
634
|
|
|
* |
635
|
|
|
* @param string $table |
636
|
|
|
* @param array $relationships array of relationship pairs with columns as keys and ids as values |
637
|
|
|
* @return array [$inserted, $deleted] |
638
|
|
|
*/ |
639
|
|
|
function dbSyncRelationships($table, $relationships = []) |
640
|
|
|
{ |
641
|
|
|
$changed = [[0, 0]]; |
642
|
|
|
[$target_column, $list_column] = array_keys(reset($relationships)); |
643
|
|
|
|
644
|
|
|
$grouped = []; |
645
|
|
|
foreach ($relationships as $relationship) { |
646
|
|
|
$grouped[$relationship[$target_column]][] = $relationship[$list_column]; |
647
|
|
|
} |
648
|
|
|
|
649
|
|
|
foreach ($grouped as $target => $list) { |
650
|
|
|
$changed[] = dbSyncRelationship($table, $target_column, $target, $list_column, $list); |
651
|
|
|
} |
652
|
|
|
|
653
|
|
|
return [array_sum(array_column($changed, 0)), array_sum(array_column($changed, 1))]; |
654
|
|
|
} |
655
|
|
|
|