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