|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace Lagdo\DbAdmin\DbAdmin; |
|
4
|
|
|
|
|
5
|
|
|
use Lagdo\DbAdmin\Driver\Db\ConnectionInterface; |
|
6
|
|
|
|
|
7
|
|
|
/** |
|
8
|
|
|
* Admin command functions |
|
9
|
|
|
*/ |
|
10
|
|
|
class CommandAdmin extends AbstractAdmin |
|
11
|
|
|
{ |
|
12
|
|
|
/** |
|
13
|
|
|
* Connection for exploring indexes and EXPLAIN (to not replace FOUND_ROWS()) |
|
14
|
|
|
* //! PDO - silent error |
|
15
|
|
|
* |
|
16
|
|
|
* @var ConnectionInterface |
|
17
|
|
|
*/ |
|
18
|
|
|
protected $connection = null; |
|
19
|
|
|
|
|
20
|
|
|
/** |
|
21
|
|
|
* Open a second connection to the server |
|
22
|
|
|
* |
|
23
|
|
|
* @return ConnectionInterface|null |
|
24
|
|
|
*/ |
|
25
|
|
|
private function connection() |
|
26
|
|
|
{ |
|
27
|
|
|
if ($this->connection === null && $this->driver->database() !== '') { |
|
28
|
|
|
// Connection for exploring indexes and EXPLAIN (to not replace FOUND_ROWS()) |
|
29
|
|
|
//! PDO - silent error |
|
30
|
|
|
$connection = $this->driver->createConnection(); |
|
31
|
|
|
$connection->open($this->driver->database(), $this->driver->schema()); |
|
32
|
|
|
$this->connection = $connection; |
|
33
|
|
|
} |
|
34
|
|
|
return $this->connection; |
|
35
|
|
|
} |
|
36
|
|
|
|
|
37
|
|
|
/** |
|
38
|
|
|
* @param mixed $value |
|
39
|
|
|
* |
|
40
|
|
|
* @return string |
|
41
|
|
|
*/ |
|
42
|
|
|
// protected function editLink($value) |
|
43
|
|
|
// { |
|
44
|
|
|
// $link = ''; |
|
45
|
|
|
// if (isset($links[$key]) && !$columns[$links[$key]]) { |
|
46
|
|
|
// if ($orgtables && $this->driver->jush() == 'sql') { // MySQL EXPLAIN |
|
47
|
|
|
// $table = $row[\array_search('table=', $links)]; |
|
48
|
|
|
// $link = /*ME .*/ $links[$key] . |
|
49
|
|
|
// \urlencode($orgtables[$table] != '' ? $orgtables[$table] : $table); |
|
50
|
|
|
// } else { |
|
51
|
|
|
// $link = /*ME .*/ 'edit=' . \urlencode($links[$key]); |
|
52
|
|
|
// foreach ($indexes[$links[$key]] as $col => $j) { |
|
53
|
|
|
// $link .= '&where' . \urlencode('[' . |
|
54
|
|
|
// $this->util->bracketEscape($col) . ']') . '=' . \urlencode($row[$j]); |
|
55
|
|
|
// } |
|
56
|
|
|
// } |
|
57
|
|
|
// } elseif ($this->util->isUrl($val)) { |
|
58
|
|
|
// $link = $val; |
|
59
|
|
|
// } |
|
60
|
|
|
// } |
|
61
|
|
|
|
|
62
|
|
|
/** |
|
63
|
|
|
* @param array $row |
|
64
|
|
|
* @param array $blobs |
|
65
|
|
|
* |
|
66
|
|
|
* @return string |
|
67
|
|
|
*/ |
|
68
|
|
|
protected function values(array $row, array $blobs) |
|
69
|
|
|
{ |
|
70
|
|
|
$values = []; |
|
71
|
|
|
foreach ($row as $key => $value) { |
|
72
|
|
|
// $link = $this->editLink($val); |
|
73
|
|
|
if ($value === null) { |
|
74
|
|
|
$value = '<i>NULL</i>'; |
|
75
|
|
|
} elseif (isset($blobs[$key]) && $blobs[$key] && !$this->util->isUtf8($value)) { |
|
76
|
|
|
//! link to download |
|
77
|
|
|
$value = '<i>' . $this->trans->lang('%d byte(s)', \strlen($value)) . '</i>'; |
|
78
|
|
|
} else { |
|
79
|
|
|
$value = $this->util->html($value); |
|
80
|
|
|
if (isset($types[$key]) && $types[$key] == 254) { // 254 - char |
|
|
|
|
|
|
81
|
|
|
$value = "<code>$value</code>"; |
|
82
|
|
|
} |
|
83
|
|
|
} |
|
84
|
|
|
$values[$key] = $value; |
|
85
|
|
|
} |
|
86
|
|
|
return $values; |
|
87
|
|
|
} |
|
88
|
|
|
|
|
89
|
|
|
/** |
|
90
|
|
|
* @param mixed $statement |
|
91
|
|
|
* @param int $rowCount |
|
92
|
|
|
* @param int $limit |
|
93
|
|
|
* |
|
94
|
|
|
* @return string |
|
95
|
|
|
*/ |
|
96
|
|
|
private function message($statement, int $rowCount, int $limit) |
|
|
|
|
|
|
97
|
|
|
{ |
|
98
|
|
|
$numRows = $statement->rowCount(); |
|
99
|
|
|
$message = ''; |
|
100
|
|
|
if ($numRows > 0) { |
|
101
|
|
|
if ($limit > 0 && $numRows > $limit) { |
|
102
|
|
|
$message = $this->trans->lang('%d / ', $limit); |
|
103
|
|
|
} |
|
104
|
|
|
$message .= $this->trans->lang('%d row(s)', $numRows); |
|
105
|
|
|
} |
|
106
|
|
|
return $message; |
|
107
|
|
|
} |
|
108
|
|
|
|
|
109
|
|
|
/** |
|
110
|
|
|
* @param mixed $field |
|
111
|
|
|
* @param array $orgtables |
|
112
|
|
|
* |
|
113
|
|
|
* @return string |
|
114
|
|
|
*/ |
|
115
|
|
|
// protected function indexes($field, array $orgtables) |
|
116
|
|
|
// { |
|
117
|
|
|
// static $links = []; // colno => orgtable - create links from these columns |
|
118
|
|
|
// static $indexes = []; // orgtable => array(column => colno) - primary keys |
|
119
|
|
|
// static $columns = []; // orgtable => array(column => ) - not selected columns in primary key |
|
120
|
|
|
|
|
121
|
|
|
// if (!empty($this->orgtables) && $this->driver->jush() == 'sql') { // MySQL EXPLAIN |
|
122
|
|
|
// $links[$j] = ($name == 'table' ? 'table=' : ($name == 'possible_keys' ? 'indexes=' : null)); |
|
123
|
|
|
// } elseif ($orgtable != '') { |
|
124
|
|
|
// if (!isset($indexes[$orgtable])) { |
|
125
|
|
|
// // find primary key in each table |
|
126
|
|
|
// $indexes[$orgtable] = []; |
|
127
|
|
|
// foreach ($this->driver->indexes($orgtable, $connection) as $index) { |
|
128
|
|
|
// if ($index->type == 'PRIMARY') { |
|
129
|
|
|
// $indexes[$orgtable] = \array_flip($index->columns); |
|
130
|
|
|
// break; |
|
131
|
|
|
// } |
|
132
|
|
|
// } |
|
133
|
|
|
// $columns[$orgtable] = $indexes[$orgtable]; |
|
134
|
|
|
// } |
|
135
|
|
|
// if (isset($columns[$orgtable][$orgname])) { |
|
136
|
|
|
// unset($columns[$orgtable][$orgname]); |
|
137
|
|
|
// $indexes[$orgtable][$orgname] = $j; |
|
138
|
|
|
// $links[$j] = $orgtable; |
|
139
|
|
|
// } |
|
140
|
|
|
// } |
|
141
|
|
|
// } |
|
142
|
|
|
|
|
143
|
|
|
/** |
|
144
|
|
|
* Print select result |
|
145
|
|
|
* From editing.inc.php |
|
146
|
|
|
* |
|
147
|
|
|
* @param mixed $statement |
|
148
|
|
|
* @param int $limit |
|
149
|
|
|
* @param array $orgtables |
|
150
|
|
|
* |
|
151
|
|
|
* @return array |
|
152
|
|
|
*/ |
|
153
|
|
|
protected function select($statement, $limit = 0, $orgtables = []) |
|
|
|
|
|
|
154
|
|
|
{ |
|
155
|
|
|
$blobs = []; // colno => bool - display bytes for blobs |
|
156
|
|
|
$types = []; // colno => type - display char in <code> |
|
157
|
|
|
$tables = []; // table => orgtable - mapping to use in EXPLAIN |
|
158
|
|
|
$headers = []; |
|
159
|
|
|
|
|
160
|
|
|
$colCount = 0; |
|
|
|
|
|
|
161
|
|
|
$rowCount = 0; |
|
162
|
|
|
$details = []; |
|
163
|
|
|
// Fetch the first row. |
|
164
|
|
|
if (!($row = $statement->fetchRow())) { |
|
165
|
|
|
// Empty resultset. |
|
166
|
|
|
$message = $this->trans->lang('No rows.'); |
|
167
|
|
|
return \compact('tables', 'headers', 'details', 'message'); |
|
168
|
|
|
} |
|
169
|
|
|
|
|
170
|
|
|
// Table headers. |
|
171
|
|
|
$colCount = \count($row); |
|
172
|
|
|
for ($j = 0; $j < $colCount; $j++) { |
|
173
|
|
|
$field = $statement->fetchField(); |
|
174
|
|
|
// PostgreSQL fix: the table field can be missing. |
|
175
|
|
|
$tables[$field->tableName()] = $field->orgTable(); |
|
176
|
|
|
// $this->indexes($field); |
|
177
|
|
|
if ($field->isBinary()) { |
|
178
|
|
|
$blobs[$j] = true; |
|
179
|
|
|
} |
|
180
|
|
|
$types[$j] = $field->type(); // Some drivers don't set the type field. |
|
181
|
|
|
$headers[] = $this->util->html($field->name()); |
|
182
|
|
|
} |
|
183
|
|
|
|
|
184
|
|
|
// Table rows (the first was already fetched). |
|
185
|
|
|
do { |
|
186
|
|
|
$rowCount++; |
|
187
|
|
|
$details[] = $this->values($row, $blobs); |
|
188
|
|
|
} while (($limit === 0 || $rowCount < $limit) && ($row = $statement->fetchRow())); |
|
189
|
|
|
|
|
190
|
|
|
$message = $this->message($statement, $rowCount, $limit); |
|
191
|
|
|
return \compact('tables', 'headers', 'details', 'message'); |
|
192
|
|
|
} |
|
193
|
|
|
|
|
194
|
|
|
/** |
|
195
|
|
|
* @param string $queries The queries to execute |
|
196
|
|
|
* @param int $offset |
|
197
|
|
|
* |
|
198
|
|
|
* @return int |
|
199
|
|
|
*/ |
|
200
|
|
|
private function nextQueryPos(string &$queries, int &$offset) |
|
201
|
|
|
{ |
|
202
|
|
|
static $delimiter = ';'; |
|
203
|
|
|
|
|
204
|
|
|
$space = "(?:\\s|/\\*[\s\S]*?\\*/|(?:#|-- )[^\n]*\n?|--\r?\n)"; |
|
205
|
|
|
if ($offset == 0 && \preg_match("~^$space*+DELIMITER\\s+(\\S+)~i", $queries, $match)) { |
|
206
|
|
|
$delimiter = $match[1]; |
|
207
|
|
|
$queries = \substr($queries, \strlen($match[0])); |
|
208
|
|
|
return 0; |
|
209
|
|
|
} |
|
210
|
|
|
|
|
211
|
|
|
// TODO: Move this to driver implementations |
|
212
|
|
|
$parse = '[\'"' . |
|
213
|
|
|
($this->driver->jush() == "sql" ? '`#' : |
|
214
|
|
|
($this->driver->jush() == "sqlite" ? '`[' : |
|
215
|
|
|
($this->driver->jush() == "mssql" ? '[' : ''))) . ']|/\*|-- |$' . |
|
216
|
|
|
($this->driver->jush() == "pgsql" ? '|\$[^$]*\$' : ''); |
|
217
|
|
|
// should always match |
|
218
|
|
|
\preg_match('(' . \preg_quote($delimiter) . "\\s*|$parse)", $queries, $match, PREG_OFFSET_CAPTURE, $offset); |
|
219
|
|
|
list($found, $pos) = $match[0]; |
|
220
|
|
|
|
|
221
|
|
|
if (!\is_string($found) && \rtrim($queries) == '') { |
|
222
|
|
|
return -1; |
|
223
|
|
|
} |
|
224
|
|
|
$offset = $pos + \strlen($found); |
|
225
|
|
|
|
|
226
|
|
|
if (!empty($found) && \rtrim($found) != $delimiter) { |
|
227
|
|
|
// find matching quote or comment end |
|
228
|
|
|
while (\preg_match('(' . ($found == '/*' ? '\*/' : ($found == '[' ? ']' : |
|
229
|
|
|
(\preg_match('~^-- |^#~', $found) ? "\n" : \preg_quote($found) . "|\\\\."))) . '|$)s', |
|
230
|
|
|
$queries, $match, PREG_OFFSET_CAPTURE, $offset)) { |
|
231
|
|
|
//! respect sql_mode NO_BACKSLASH_ESCAPES |
|
232
|
|
|
$s = $match[0][0]; |
|
233
|
|
|
$offset = $match[0][1] + \strlen($s); |
|
234
|
|
|
if ($s[0] != "\\") { |
|
235
|
|
|
break; |
|
236
|
|
|
} |
|
237
|
|
|
} |
|
238
|
|
|
return 0; |
|
239
|
|
|
} |
|
240
|
|
|
|
|
241
|
|
|
return \intval($pos); |
|
242
|
|
|
} |
|
243
|
|
|
|
|
244
|
|
|
/** |
|
245
|
|
|
* @param string $query The query to execute |
|
246
|
|
|
* @param array $results |
|
247
|
|
|
* @param int $limit The max number of rows to return |
|
248
|
|
|
* @param bool $errorStops Stop executing the requests in case of error |
|
249
|
|
|
* @param bool $onlyErrors Return only errors |
|
250
|
|
|
* |
|
251
|
|
|
* @return bool |
|
252
|
|
|
*/ |
|
253
|
|
|
private function executeQuery(string $query, array &$results, int $limit, bool $errorStops, bool $onlyErrors) |
|
254
|
|
|
{ |
|
255
|
|
|
$space = "(?:\\s|/\\*[\s\S]*?\\*/|(?:#|-- )[^\n]*\n?|--\r?\n)"; |
|
256
|
|
|
$messages = []; |
|
257
|
|
|
$errors = []; |
|
258
|
|
|
$select = null; |
|
259
|
|
|
|
|
260
|
|
|
if ($this->driver->jush() == 'sqlite' && \preg_match("~^$space*+ATTACH\\b~i", $query, $match)) { |
|
261
|
|
|
// PHP doesn't support setting SQLITE_LIMIT_ATTACHED |
|
262
|
|
|
// $errors[] = " <a href='#sql-$commands'>$commands</a>"; |
|
263
|
|
|
$errors[] = $this->trans->lang('ATTACH queries are not supported.'); |
|
264
|
|
|
$results[] = \compact('query', 'errors', 'messages', 'select'); |
|
265
|
|
|
// return $errorStops ? false : true; |
|
266
|
|
|
return !$errorStops; |
|
267
|
|
|
} |
|
268
|
|
|
|
|
269
|
|
|
$connection = $this->connection(); |
|
270
|
|
|
//! Don't allow changing of character_set_results, convert encoding of displayed query |
|
271
|
|
|
if ($this->driver->multiQuery($query) && $connection !== null && \preg_match("~^$space*+USE\\b~i", $query)) { |
|
272
|
|
|
$connection->query($query); |
|
273
|
|
|
} |
|
274
|
|
|
|
|
275
|
|
|
do { |
|
276
|
|
|
$statement = $this->driver->storedResult(); |
|
277
|
|
|
|
|
278
|
|
|
if ($this->driver->hasError()) { |
|
279
|
|
|
$error = $this->driver->error(); |
|
280
|
|
|
if ($this->driver->hasErrno()) { |
|
281
|
|
|
$error = '(' . $this->driver->errno() . "): $error"; |
|
282
|
|
|
} |
|
283
|
|
|
$errors[] = $error; |
|
284
|
|
|
} else { |
|
285
|
|
|
$affected = $this->driver->affectedRows(); // Getting warnigns overwrites this |
|
286
|
|
|
if ($statement !== null) { |
|
287
|
|
|
if (!$onlyErrors) { |
|
288
|
|
|
$select = $this->select($statement, $limit); |
|
289
|
|
|
$messages[] = $select['message']; |
|
290
|
|
|
} |
|
291
|
|
|
} else { |
|
292
|
|
|
if (!$onlyErrors) { |
|
293
|
|
|
// $title = $this->util->html($this->driver->info()); |
|
294
|
|
|
$messages[] = $this->trans->lang('Query executed OK, %d row(s) affected.', $affected); // . "$time"; |
|
295
|
|
|
} |
|
296
|
|
|
} |
|
297
|
|
|
} |
|
298
|
|
|
|
|
299
|
|
|
$results[] = \compact('query', 'errors', 'messages', 'select'); |
|
300
|
|
|
|
|
301
|
|
|
if ($this->driver->hasError() && $errorStops) { |
|
302
|
|
|
return false; |
|
303
|
|
|
} |
|
304
|
|
|
|
|
305
|
|
|
// $start = \microtime(true); |
|
306
|
|
|
} while ($this->driver->nextResult()); |
|
307
|
|
|
|
|
308
|
|
|
return true; |
|
309
|
|
|
} |
|
310
|
|
|
|
|
311
|
|
|
/** |
|
312
|
|
|
* Execute a set of queries |
|
313
|
|
|
* |
|
314
|
|
|
* @param string $queries The queries to execute |
|
315
|
|
|
* @param int $limit The max number of rows to return |
|
316
|
|
|
* @param bool $errorStops Stop executing the requests in case of error |
|
317
|
|
|
* @param bool $onlyErrors Return only errors |
|
318
|
|
|
* |
|
319
|
|
|
* @return array |
|
320
|
|
|
*/ |
|
321
|
|
|
public function executeCommands(string $queries, int $limit, bool $errorStops, bool $onlyErrors) |
|
322
|
|
|
{ |
|
323
|
|
|
if (\function_exists('memory_get_usage')) { |
|
324
|
|
|
// @ - may be disabled, 2 - substr and trim, 8e6 - other variables |
|
325
|
|
|
try { |
|
326
|
|
|
\ini_set('memory_limit', \max($this->util->iniBytes('memory_limit'), |
|
327
|
|
|
2 * \strlen($queries) + \memory_get_usage() + 8e6)); |
|
328
|
|
|
} |
|
329
|
|
|
catch(\Exception $e) { |
|
330
|
|
|
// Do nothing if the option is not modified. |
|
331
|
|
|
} |
|
332
|
|
|
} |
|
333
|
|
|
|
|
334
|
|
|
// if($queries != '' && \strlen($queries) < 1e6) { // don't add big queries |
|
335
|
|
|
// $q = $queries . (\preg_match("~;[ \t\r\n]*\$~", $queries) ? '' : ';'); //! doesn't work with DELIMITER | |
|
336
|
|
|
// if(!$history || \reset(\end($history)) != $q) { // no repeated queries |
|
337
|
|
|
// \restart_session(); |
|
338
|
|
|
// $history[] = [$q, \time()]; //! add elapsed time |
|
339
|
|
|
// \set_session('queries', $history_all); // required because reference is unlinked by stop_session() |
|
340
|
|
|
// \stop_session(); |
|
341
|
|
|
// } |
|
342
|
|
|
// } |
|
343
|
|
|
|
|
344
|
|
|
$offset = 0; |
|
345
|
|
|
$empty = true; |
|
346
|
|
|
$commands = 0; |
|
347
|
|
|
$timestamps = []; |
|
348
|
|
|
// $total_start = \microtime(true); |
|
349
|
|
|
// \parse_str($_COOKIE['adminer_export'], $adminer_export); |
|
350
|
|
|
// $dump_format = $this->util->dumpFormat(); |
|
351
|
|
|
// unset($dump_format['sql']); |
|
352
|
|
|
|
|
353
|
|
|
$results = []; |
|
354
|
|
|
while ($queries != '') { |
|
355
|
|
|
$pos = $this->nextQueryPos($queries, $offset); |
|
356
|
|
|
if ($pos < 0) { |
|
357
|
|
|
break; |
|
358
|
|
|
} |
|
359
|
|
|
if ($pos === 0) { |
|
360
|
|
|
continue; |
|
361
|
|
|
} |
|
362
|
|
|
|
|
363
|
|
|
// end of a query |
|
364
|
|
|
$messages = []; |
|
365
|
|
|
$select = null; |
|
|
|
|
|
|
366
|
|
|
|
|
367
|
|
|
$empty = false; |
|
368
|
|
|
$query = \substr($queries, 0, $pos); |
|
369
|
|
|
$queries = \substr($queries, $offset); |
|
370
|
|
|
$offset = 0; |
|
371
|
|
|
$commands++; |
|
372
|
|
|
// $print = "<pre id='sql-$commands'><code class='jush-$this->driver->jush()'>" . |
|
373
|
|
|
// $this->util->sqlCommandQuery($q) . "</code></pre>\n"; |
|
374
|
|
|
if (!$this->executeQuery($query, $results, $limit, $errorStops, $onlyErrors) && $errorStops) { |
|
375
|
|
|
break; |
|
376
|
|
|
} |
|
377
|
|
|
} |
|
378
|
|
|
|
|
379
|
|
|
if ($empty) { |
|
380
|
|
|
$messages[] = $this->trans->lang('No commands to execute.'); |
|
381
|
|
|
} elseif ($onlyErrors) { |
|
382
|
|
|
$messages[] = $this->trans->lang('%d query(s) executed OK.', $commands - \count($errors)); |
|
|
|
|
|
|
383
|
|
|
// $timestamps[] = $this->trans->formatTime($total_start); |
|
384
|
|
|
} |
|
385
|
|
|
// elseif($errors && $commands > 1) |
|
386
|
|
|
// { |
|
387
|
|
|
// $errors[] = $this->trans->lang('Error in query') . ': ' . \implode('', $errors); |
|
388
|
|
|
// } |
|
389
|
|
|
//! MS SQL - SET SHOWPLAN_ALL OFF |
|
390
|
|
|
|
|
391
|
|
|
$errors = []; // No error returned here. |
|
392
|
|
|
return \compact('results', 'messages', 'errors', 'timestamps'); |
|
393
|
|
|
} |
|
394
|
|
|
} |
|
395
|
|
|
|
lagdo /
jaxon-dbadmin
Loading history...