§ Программный код
Редакция: 5 мар 2024
1use mysqli;
2
3class DatabaseConnector {
4
5
6 protected $db;
7
8 protected $db_host;
9 protected $db_user;
10 protected $db_pass;
11 protected $db_name;
12 protected $db_port;
13 protected $db_codepage;
14
15
16
17
22 public function __construct($cdb, string $codepage = 'utf8') {
23
24 $this->db_port = null;
25
26
27 if (is_array($cdb)) {
28
29 $this->db_host = $cdb['host'] ?? '';
30 $this->db_user = $cdb['username'] ?? '';
31 $this->db_pass = $cdb['password'] ?? '';
32 $this->db_name = $cdb['database'] ?? '';
33 $codepage = $cdb['charset'] ?? $codepage;
34 }
35
36 else if (preg_match('~-h\s+([^\s+]+)\s+-u\s+([^\s+]+)\s+-p([^\s+]*)\s+([^\s+]+)~', $cdb, $c)) {
37 list(, $this->db_host, $this->db_user, $this->db_pass, $this->db_name) = $c;
38 }
39
40 else if (preg_match('~^([0-9.]+):(\d+)$~', $cdb, $c)) {
41
42 $this->db_host = $c[1];
43 $this->db_user = '';
44 $this->db_pass = '';
45 $this->db_name = '';
46 $this->db_port = $c[2];
47 }
48 else {
49 die('['.date('Y-m-d H:i:s')." Invalid MySQL connection string\n");
50 }
51
52 $this->db_codepage = $codepage;
53 }
54
55
56 public function connect() {
57
58 if (empty($this->db)) {
59
60 $err_cstr = "$this->db_host:$this->db_user:$this->db_name";
61 try {
62 $this->db = new mysqli($this->db_host, $this->db_user, $this->db_pass, $this->db_name, $this->db_port);
63 } catch (\Exception $e) {
64 die("CONNECT ERROR: " . $e->getMessage() . " [$err_cstr]");
65 }
66
67 if ($this->db->connect_error) {
68 die('['.date('Y-m-d H:i:s')." Can't connect to Db... [$err_cstr]\n");
69 }
70
71 $this->db->query("SET NAMES $this->db_codepage");
72 }
73 }
74
75
76 public function close() {
77
78 if ($this->db) {
79 $this->db->close();
80 $this->db = null;
81 }
82 }
83
84
85 public function use($dbname) {
86
87 $this->connect();
88 $this->query("USE $dbname");
89 return $this;
90 }
91
92
93 public function query($sql, $param = "") {
94
95 $this->connect();
96 $q = $this->db->query($sql);
97
98 if ($this->db->errno) {
99 throw new \Exception("Error in mysql: " . $this->db->error . " [$sql]");
100 }
101
102 $param = explode(':', $param);
103
104
105 if ($param[0] == 'rows') {
106
107 $rows = [];
108 $keyname = $param[1] ?? "";
109 foreach ($q as $item) $rows[] = $keyname ? $item[$keyname] : $item;
110 return $rows;
111 }
112
113 return $q;
114 }
115
116
117 public function affected_rows() {
118 return $this->db->affected_rows;
119 }
120
121
122 public function db() {
123 return $this->db;
124 }
125
126
127 public function escape($s) {
128
129 $this->connect();
130
131 if (is_null($s))
132 return 'NULL';
133 else if (is_integer($s) || is_float($s) || is_double($s))
134 return $s;
135 else if (is_object($s) || is_array($s))
136 return "'".$this->db->real_escape_string(json_encode($s, JSON_UNESCAPED_SLASHES))."'";
137 else
138 return "'".$this->db->real_escape_string($s)."'";
139 }
140
141
142 public function escape_arr(array $data) {
143
144 foreach ($data as $id => $value)
145 $data[$id] = $this->escape($value);
146
147 return $data;
148 }
149
150
151
152
153 protected function where($where) {
154
155 $arr = [];
156 foreach ($where as $k => $v)
157 if (is_array($v))
158 foreach ($v as $a => $b) $arr[$k][] = "$a = ".$this->escape($b);
159 else
160 $arr[0][] = "$k = ".$this->escape($v);
161
162 return join(" OR ", array_map(function ($a) { return '(' . join(" AND ", $a) . ')'; }, $arr));
163 }
164
165
166
167
168 public function select($table, $where) {
169
170 $param = explode(':', $table);
171 $table = $param[0];
172 $field = $param[1] ?? '*';
173
174 return $this->query("SELECT $field FROM $table WHERE " . $this->where($where));
175 }
176
177
178 public function insert($table, array $data, $ignore = 'IGNORE') {
179
180 $data = $this->escape_arr($data);
181 $sql = "INSERT $ignore INTO $table (`".join("`, `", array_keys($data))."`) ";
182 $sql .= "VALUES (".join(", ", array_values($data)).")";
183 $this->query($sql);
184
185 return (int) $this->db->insert_id;
186 }
187
188
189 public function update($table, array $data, array $where) {
190
191 $sets = [];
192 foreach ($this->escape_arr($data) as $id => $value) $sets[] = "`$id` = $value";
193 $this->query("UPDATE $table SET ".join(', ', $sets)." WHERE " . $this->where($where));
194 }
195
196
197
198
199
200 public function wherein($tablekey, $arr, $where = '') {
201
202 $rows = [];
203 $r = explode(':', $tablekey);
204 $table = $r[0];
205 $key = ($r[1] ?? '') ?: 'id';
206 $field = ($r[2] ?? '') ?: '*';
207
208 if ($arr = $this->escape_arr(array_unique($arr))) {
209
210 $ext = $key[0];
211 $key = ltrim($key, '*');
212 $sql = "SELECT $field FROM $table WHERE $key IN (".join(',', $arr).")" . ($where ? " AND $where" : "");
213 foreach ($this->query($sql) as $row) {
214 if ($ext == '*') {
215 $kval = $row[$key]; unset($row[$key]); $rows[$kval] = $row;
216 } else {
217 $rows[] = $row;
218 }
219 }
220 }
221
222 return $rows;
223 }
224
225
226 public function deletein($tablekey, $arr) {
227
228 $r = explode(':', $tablekey);
229 $table = $r[0];
230 $key = ($r[1] ?? '') ?: 'id';
231
232 $this->query("DELETE FROM $table WHERE $key IN (".join(',', $this->escape_arr($arr)).")");
233 return $this->db->affected_rows;
234 }
235
236
237
238
239 public function batch_insert($table, $rows, $names = []) {
240
241 if (empty($rows)) return 0;
242
243 $names = $names ?: array_keys(current($rows));
244 $sql = "INSERT IGNORE INTO $table (`".join("`,`", $names)."`) VALUES ";
245 foreach ($rows as $id => $row) {
246 $rows[$id] = is_array($row) ? '('.join(",", array_values($this->escape_arr($row))).')' : '('.$this->escape($row).')';
247 }
248 $this->query($sql . join(',', $rows));
249 return $this->db->affected_rows;
250 }
251
252
253 public function batch_update($table, $rows, $only_sql = false) {
254
255 if (empty($rows)) return 0;
256
257 $param = explode(':', $table);
258 $table = $param[0];
259 $key = ($param[1] ?? '') ?: 'id';
260 $pluck = ($param[2] ?? '') ?: '';
261
262 $sets = [];
263 $keys = [];
264 $update = [];
265
266 foreach ($rows as $id => $row) {
267
268
269 if ($pluck) $row = [$pluck => $row];
270
271 $keys[] = ($id = $this->escape($id));
272 foreach ($this->escape_arr($row) as $name => $v)
273 $update[$name][$id] = $v;
274 }
275
276 foreach ($update as $name => $values) {
277
278 $sets[$name] = "$name = CASE\n";
279 foreach ($values as $id => $value) $sets[$name] .= " WHEN $key = $id THEN $value\n";
280 $sets[$name] .= "ELSE $name END";
281 }
282
283 $sql = "UPDATE $table SET " . join(', ', $sets) . " WHERE $key IN (".join(',', $keys).")";
284 if ($only_sql) {
285 return $sql;
286 } else {
287 $this->query($sql);
288 return (int) $this->db->affected_rows;
289 }
290 }
291
292
293
294
295
296 public function chunk($table, $pt = null) {
297
298 $request = explode(':', $table);
299 $table = $request[0];
300 $keyname = ($request[1] ?? '') ?: 'id';
301 $grpcnt = ($request[2] ?? '') ?: 0;
302
303
304 $arr = is_array($pt);
305 $field = $arr && isset($pt[0]) ? $pt[0] : '*';
306 $where = $arr && isset($pt['w']) ? $pt['w'] : (is_string($pt) ? $pt : '');
307 $join = $arr && isset($pt['j']) ? $pt['j'] : '';
308 $steps = $arr && isset($pt['s']) ? $pt['s'] : 5000;
309
310 $gkeyname = $keyname;
311 if ($grpcnt && preg_match('~\.(\w+)$~', $keyname, $c)) $gkeyname = $c[1];
312
313 list($min, $max) = $this->query("SELECT MIN($keyname), MAX($keyname) FROM $table")->fetch_row();
314
315 $step = (int)(($max - $min) / $steps);
316 $step = max($step, 100);
317 $rows = [];
318
319 for ($id = $min; $id < $max; $id += $step) {
320
321 $sql = "SELECT $field FROM $table $join ".
322 "WHERE ($keyname >= $id AND $keyname < $id + $step)" . ($where ? " AND $where" : "");
323
324 foreach ($this->query($sql) as $row) {
325 if ($grpcnt) {
326 $rows[$row[$gkeyname]] = $row;
327 if (count($rows) >= $grpcnt) {
328 yield $rows;
329 $rows = [];
330 }
331 } else {
332 yield $row;
333 }
334 }
335 }
336
337 if ($grpcnt && $rows) yield $rows;
338 }
339
340
341 public function pluck($rows, $name, $intval = false) {
342
343 foreach ($rows as $id => $row) {
344 $value = $row[$name] ?? null;
345 $rows[$id] = $intval ? (int) $value : $value;
346 }
347
348 return $rows;
349 }
350
351
352
353 public function tables($filter = '') {
354
355 $rows = [];
356 foreach ($this->query("SELECT table_name, data_length + index_length AS size FROM information_schema.TABLES WHERE table_schema = '$this->db_name' ORDER BY `table_name` ASC") as $row) {
357 $tb = $row['table_name'];
358 if (!$filter || preg_match($filter, $tb))
359 $rows[$tb] = $row['size'];
360 }
361 return $rows;
362 }
363
364
365 public function minmax($table, $key = 'id') {
366 return $this->query("SELECT MIN($key), MAX($key), COUNT(*) FROM $table")->fetch_row();
367 }
368
369
370 public function count($table, $where = '') {
371 return $this->query("SELECT COUNT(*) FROM $table".($where ? " WHERE $where" : ""))->fetch_row()[0];
372 }
373
374
375 public function kk($rows) { return join(',', $this->escape_arr(array_keys($rows))); }
376 public function kv($rows) { return join(',', $this->escape_arr($rows)); }
377
378
379
380
381
382 public function reserve_queue($table, $thread, $quantity = 250) {
383
384 $basic = explode(':', $table);
385 $table = $basic[0];
386 $field = ($basic[1] ?? '') ?: 'id';
387 $key = ($basic[2] ?? '') ?: 'status';
388 $sql = "SELECT $field FROM $table WHERE $key = -$thread";
389
390
391 if ($thread < 0) return [-$thread];
392
393 if ($rows = $this->query($sql, "rows:$field")) {
394 return $rows;
395 } else {
396 $this->query("UPDATE $table SET $key = -$thread WHERE $key = 0 LIMIT $quantity");
397 return $this->query($sql, "rows:$field");
398 }
399 }
400
401
402 public function status_group($tablename, $status_field = 'status') {
403
404 $rows = [];
405 foreach ($this->query("SELECT COUNT(*) g, `$status_field` FROM $tablename GROUP BY `$status_field`") as $row) {
406 $rows[$row[$status_field]] = (int) $row['g'];
407 }
408 return $rows;
409 }
410}