1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Lagdo\DbAdmin\DbAdmin; |
4
|
|
|
|
5
|
|
|
use Lagdo\DbAdmin\DbAdmin\Traits\QueryInputTrait; |
6
|
|
|
|
7
|
|
|
use function compact; |
8
|
|
|
use function is_array; |
9
|
|
|
use function count; |
10
|
|
|
use function preg_match; |
11
|
|
|
use function is_bool; |
12
|
|
|
use function array_sum; |
13
|
|
|
|
14
|
|
|
/** |
15
|
|
|
* Admin table query functions |
16
|
|
|
*/ |
17
|
|
|
class TableQueryAdmin extends AbstractAdmin |
18
|
|
|
{ |
19
|
|
|
use QueryInputTrait; |
|
|
|
|
20
|
|
|
|
21
|
|
|
/** |
22
|
|
|
* Get the table fields |
23
|
|
|
* |
24
|
|
|
* @param string $table The table name |
25
|
|
|
* @param array $queryOptions The query options |
26
|
|
|
* |
27
|
|
|
* @return array |
28
|
|
|
*/ |
29
|
|
|
private function getFields(string $table, array $queryOptions): array |
30
|
|
|
{ |
31
|
|
|
// From edit.inc.php |
32
|
|
|
$fields = $this->driver->fields($table); |
33
|
|
|
|
34
|
|
|
//!!!! $queryOptions["select"] is never set here !!!!// |
35
|
|
|
|
36
|
|
|
$where = $this->admin->where($queryOptions, $fields); |
37
|
|
|
$update = $where; |
38
|
|
|
foreach ($fields as $name => $field) { |
39
|
|
|
$generated = $field->generated ?? false; |
40
|
|
|
if (!isset($field->privileges[$update ? "update" : "insert"]) || |
41
|
|
|
$this->util->fieldName($field) == "" || $generated) { |
42
|
|
|
unset($fields[$name]); |
43
|
|
|
} |
44
|
|
|
} |
45
|
|
|
|
46
|
|
|
return [$fields, $where, $update]; |
47
|
|
|
} |
48
|
|
|
|
49
|
|
|
/** |
50
|
|
|
* @param array $fields |
51
|
|
|
* @param array $queryOptions |
52
|
|
|
* |
53
|
|
|
* @return array |
54
|
|
|
*/ |
55
|
|
|
private function getQuerySelect(array $fields, array $queryOptions): array |
56
|
|
|
{ |
57
|
|
|
$select = []; |
58
|
|
|
foreach ($fields as $name => $field) { |
59
|
|
|
if (isset($field->privileges["select"])) { |
60
|
|
|
$as = $this->driver->convertField($field); |
61
|
|
|
if ($queryOptions["clone"] && $field->autoIncrement) { |
62
|
|
|
$as = "''"; |
63
|
|
|
} |
64
|
|
|
if ($this->driver->jush() == "sql" && preg_match("~enum|set~", $field->type)) { |
65
|
|
|
$as = "1*" . $this->driver->escapeId($name); |
66
|
|
|
} |
67
|
|
|
$select[] = ($as ? "$as AS " : "") . $this->driver->escapeId($name); |
68
|
|
|
} |
69
|
|
|
} |
70
|
|
|
if (!$this->driver->support("table")) { |
71
|
|
|
$select = ["*"]; |
72
|
|
|
} |
73
|
|
|
return $select; |
74
|
|
|
} |
75
|
|
|
|
76
|
|
|
/** |
77
|
|
|
* @param string $table |
78
|
|
|
* @param string $where |
79
|
|
|
* @param array $fields |
80
|
|
|
* @param array $queryOptions |
81
|
|
|
* |
82
|
|
|
* @return array|null |
83
|
|
|
*/ |
84
|
|
|
private function getQueryFirstRow(string $table, string $where, array $fields, array $queryOptions) |
85
|
|
|
{ |
86
|
|
|
// From edit.inc.php |
87
|
|
|
$row = null; |
88
|
|
|
if (($where)) { |
89
|
|
|
$select = $this->getQuerySelect($fields, $queryOptions); |
90
|
|
|
$row = []; |
91
|
|
|
if ($select) { |
92
|
|
|
$statement = $this->driver->select($table, $select, [$where], $select, [], |
93
|
|
|
(isset($queryOptions["select"]) ? 2 : 1)); |
94
|
|
|
if (($statement)) { |
95
|
|
|
$row = $statement->fetchAssoc(); |
96
|
|
|
}/* else { |
97
|
|
|
$error = $this->driver->error(); |
98
|
|
|
}*/ |
99
|
|
|
// if(isset($queryOptions["select"]) && (!$row || $statement->fetchAssoc())) |
100
|
|
|
// { |
101
|
|
|
// // $statement->rowCount() != 1 isn't available in all drivers |
102
|
|
|
// $row = null; |
103
|
|
|
// } |
104
|
|
|
} |
105
|
|
|
} |
106
|
|
|
return $row; |
107
|
|
|
} |
108
|
|
|
|
109
|
|
|
/** |
110
|
|
|
* @param array $fields |
111
|
|
|
* @param array|null $row |
112
|
|
|
* @param string $update |
113
|
|
|
* @param array $queryOptions |
114
|
|
|
* |
115
|
|
|
* @return array |
116
|
|
|
*/ |
117
|
|
|
private function getQueryEntries(array $fields, $row, string $update, array $queryOptions): array |
118
|
|
|
{ |
119
|
|
|
$entries = []; |
120
|
|
|
foreach ($fields as $name => $field) { |
121
|
|
|
// $default = $queryOptions["set"][$this->util->bracketEscape($name)] ?? null; |
122
|
|
|
// if($default === null) |
123
|
|
|
// { |
124
|
|
|
$default = $field->default; |
125
|
|
|
if ($field->type == "bit" && preg_match("~^b'([01]*)'\$~", $default, $regs)) { |
126
|
|
|
$default = $regs[1]; |
127
|
|
|
} |
128
|
|
|
// } |
129
|
|
|
$value = ($row !== null ? |
130
|
|
|
($row[$name] != "" && $this->driver->jush() == "sql" && preg_match("~enum|set~", $field->type) ? |
131
|
|
|
(is_array($row[$name]) ? array_sum($row[$name]) : +$row[$name]) : |
132
|
|
|
(is_bool($row[$name]) ? +$row[$name] : $row[$name])) : |
133
|
|
|
(!$update && $field->autoIncrement ? "" : (isset($queryOptions["select"]) ? false : $default))); |
134
|
|
|
$function = ($queryOptions["save"] ? (string)$queryOptions["function"][$name] : |
135
|
|
|
($update && preg_match('~^CURRENT_TIMESTAMP~i', $field->onUpdate) ? "now" : |
136
|
|
|
($value === false ? null : ($value !== null ? '' : 'NULL')))); |
137
|
|
|
if (!$update && $value == $field->default && preg_match('~^[\w.]+\(~', $value)) { |
138
|
|
|
$function = "SQL"; |
139
|
|
|
} |
140
|
|
|
if (preg_match("~time~", $field->type) && preg_match('~^CURRENT_TIMESTAMP~i', $value)) { |
141
|
|
|
$value = ""; |
142
|
|
|
$function = "now"; |
143
|
|
|
} |
144
|
|
|
|
145
|
|
|
$entries[$name] = $this->getFieldInput($field, $value, $function, $queryOptions); |
146
|
|
|
} |
147
|
|
|
return $entries; |
148
|
|
|
} |
149
|
|
|
|
150
|
|
|
/** |
151
|
|
|
* Get data for insert/update on a table |
152
|
|
|
* |
153
|
|
|
* @param string $table The table name |
154
|
|
|
* @param array $queryOptions The query options |
155
|
|
|
* |
156
|
|
|
* @return array |
157
|
|
|
*/ |
158
|
|
|
public function getQueryData(string $table, array $queryOptions = []): array |
159
|
|
|
{ |
160
|
|
|
$isInsert = (count($queryOptions) === 0); // True only on insert. |
161
|
|
|
// Default options |
162
|
|
|
$queryOptions['clone'] = false; |
163
|
|
|
$queryOptions['save'] = false; |
164
|
|
|
|
165
|
|
|
list($fields, $where, $update) = $this->getFields($table, $queryOptions); |
166
|
|
|
$row = $this->getQueryFirstRow($table, $where, $fields, $queryOptions); |
167
|
|
|
|
168
|
|
|
/* TODO: Activate this code when a driver without table support will be supported */ |
169
|
|
|
/*if (!$this->driver->support("table") && empty($fields)) { |
170
|
|
|
$primary = ''; // $this->driver->primaryIdName(); |
171
|
|
|
if (!$where) { |
172
|
|
|
// insert |
173
|
|
|
$statement = $this->driver->select($table, ["*"], [$where], ["*"]); |
174
|
|
|
$row = ($statement ? $statement->fetchAssoc() : false); |
175
|
|
|
if (!$row) { |
176
|
|
|
$row = [$primary => ""]; |
177
|
|
|
} |
178
|
|
|
} |
179
|
|
|
if ($row) { |
180
|
|
|
foreach ($row as $key => $val) { |
181
|
|
|
if (!$where) { |
182
|
|
|
$row[$key] = null; |
183
|
|
|
} |
184
|
|
|
$fields[$key] = [ |
185
|
|
|
"name" => $key, |
186
|
|
|
"null" => ($key !== $primary), |
187
|
|
|
"autoIncrement" => ($key === $primary) |
188
|
|
|
]; |
189
|
|
|
} |
190
|
|
|
} |
191
|
|
|
}*/ |
192
|
|
|
|
193
|
|
|
// From functions.inc.php (function edit_form($table, $fields, $row, $update)) |
194
|
|
|
$entries = []; |
195
|
|
|
$tableName = $this->util->tableName($this->driver->tableStatusOrName($table, true)); |
196
|
|
|
$error = null; |
197
|
|
|
if (($where) && $row === null) { // No row found to edit. |
198
|
|
|
$error = $this->trans->lang('No rows.'); |
199
|
|
|
} elseif (!$fields) { |
|
|
|
|
200
|
|
|
$error = $this->trans->lang('You have no privileges to update this table.'); |
201
|
|
|
} else { |
202
|
|
|
$entries = $this->getQueryEntries($fields, $row, $update, $queryOptions); |
203
|
|
|
} |
204
|
|
|
|
205
|
|
|
$mainActions = [ |
206
|
|
|
'query-back' => $this->trans->lang('Back'), |
207
|
|
|
'query-save' => $this->trans->lang('Save'), |
208
|
|
|
]; |
209
|
|
|
if ($isInsert) { |
210
|
|
|
$mainActions['query-save-select'] = $this->trans->lang('Save and select'); |
211
|
|
|
} |
212
|
|
|
|
213
|
|
|
$fields = $entries; |
214
|
|
|
return compact('mainActions', 'tableName', 'error', 'fields'); |
215
|
|
|
} |
216
|
|
|
|
217
|
|
|
/** |
218
|
|
|
* Insert a new item in a table |
219
|
|
|
* |
220
|
|
|
* @param string $table The table name |
221
|
|
|
* @param array $queryOptions The query options |
222
|
|
|
* |
223
|
|
|
* @return array |
224
|
|
|
*/ |
225
|
|
|
public function insertItem(string $table, array $queryOptions): array |
226
|
|
|
{ |
227
|
|
|
list($fields, ,) = $this->getFields($table, $queryOptions); |
228
|
|
|
|
229
|
|
|
// From edit.inc.php |
230
|
|
|
$values = []; |
231
|
|
|
foreach ($fields as $name => $field) { |
232
|
|
|
$val = $this->util->processInput($field, $queryOptions); |
233
|
|
|
if ($val !== false && $val !== null) { |
234
|
|
|
$values[$this->driver->escapeId($name)] = $val; |
235
|
|
|
} |
236
|
|
|
} |
237
|
|
|
|
238
|
|
|
$result = $this->driver->insert($table, $values); |
239
|
|
|
$lastId = ($result ? $this->driver->lastAutoIncrementId() : 0); |
240
|
|
|
$message = $this->trans->lang('Item%s has been inserted.', ($lastId ? " $lastId" : "")); |
241
|
|
|
|
242
|
|
|
$error = $this->driver->error(); |
243
|
|
|
|
244
|
|
|
return compact('result', 'message', 'error'); |
245
|
|
|
} |
246
|
|
|
|
247
|
|
|
/** |
248
|
|
|
* Update one or more items in a table |
249
|
|
|
* |
250
|
|
|
* @param string $table The table name |
251
|
|
|
* @param array $queryOptions The query options |
252
|
|
|
* |
253
|
|
|
* @return array |
254
|
|
|
*/ |
255
|
|
|
public function updateItem(string $table, array $queryOptions): array |
256
|
|
|
{ |
257
|
|
|
list($fields, $where, $update) = $this->getFields($table, $queryOptions); |
258
|
|
|
|
259
|
|
|
// From edit.inc.php |
260
|
|
|
$indexes = $this->driver->indexes($table); |
261
|
|
|
$uniqueIds = $this->util->uniqueIds($queryOptions["where"], $indexes); |
262
|
|
|
$queryWhere = "\nWHERE $where"; |
263
|
|
|
|
264
|
|
|
$values = []; |
265
|
|
|
foreach ($fields as $name => $field) { |
266
|
|
|
$val = $this->util->processInput($field, $queryOptions); |
267
|
|
|
if ($val !== false && $val !== null) { |
268
|
|
|
$values[$this->driver->escapeId($name)] = $val; |
269
|
|
|
} |
270
|
|
|
} |
271
|
|
|
|
272
|
|
|
$result = $this->driver->update($table, $values, $queryWhere, count($uniqueIds)); |
273
|
|
|
$message = $this->trans->lang('Item has been updated.'); |
274
|
|
|
|
275
|
|
|
$error = $this->driver->error(); |
276
|
|
|
|
277
|
|
|
return compact('result', 'message', 'error'); |
278
|
|
|
} |
279
|
|
|
|
280
|
|
|
/** |
281
|
|
|
* Delete one or more items in a table |
282
|
|
|
* |
283
|
|
|
* @param string $table The table name |
284
|
|
|
* @param array $queryOptions The query options |
285
|
|
|
* |
286
|
|
|
* @return array |
287
|
|
|
*/ |
288
|
|
|
public function deleteItem(string $table, array $queryOptions): array |
289
|
|
|
{ |
290
|
|
|
list($fields, $where, $update) = $this->getFields($table, $queryOptions); |
291
|
|
|
|
292
|
|
|
// From edit.inc.php |
293
|
|
|
$indexes = $this->driver->indexes($table); |
294
|
|
|
$uniqueIds = $this->util->uniqueIds($queryOptions["where"], $indexes); |
295
|
|
|
$queryWhere = "\nWHERE $where"; |
296
|
|
|
|
297
|
|
|
$result = $this->driver->delete($table, $queryWhere, count($uniqueIds)); |
298
|
|
|
$message = $this->trans->lang('Item has been deleted.'); |
299
|
|
|
|
300
|
|
|
$error = $this->driver->error(); |
301
|
|
|
|
302
|
|
|
return compact('result', 'message', 'error'); |
303
|
|
|
} |
304
|
|
|
} |
305
|
|
|
|