suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Lib / Db / Sqlite / Func.php
27.1 KB、693行 生 Blame 履歴
1<?php
2/*************************************************************
3# 076 License
4
5Copyright (c) テクニカル諏訪子
6
7Permission is hereby granted to any person obtaining a copy of the software
8Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
9sublicense, and/or sell copies of the Software, subject to the following conditions:
10
11 1. **Origin Attribution**:
12 - You must not misrepresent the origin of the Software; you must not claim
13 you created the original Software.
14 - If the Software is used in a product, you must either:
15 a. Provide clear attribution in the product's documentation, user interface,
16 or other visible areas, **OR**
17 b. Pay the original developers a fee they specify in writing.
18 2. **Usage Restriction**:
19 - The Software, or any derivative works, dependencies, or libraries
20 incorporating it, must not be used for censorship or to suppress freedom of
21 speech, expression, or creativity. Prohibited uses include, but are not
22 limited to:
23 - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
24 ideas, or objective reality.
25 - Tools or systems designed to restrict access to information or
26 artistic works.
27 3. **Notice Preservation**:
28 - This license and the above copyright notice must remain intact in all copies
29 of the source code.
30
31THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
32INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
33PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
34HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
35CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
36OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
37 */
38namespace Std\Lib\Db\Sqlite;
39
40use Std\Lib\Db\DbException;
41
42/**
43 * SQLの関数（スカラー、日付、JSON）と集約関数
44 */
45final class Func {
46 public const AGGREGATES = ['count', 'sum', 'total', 'avg', 'min', 'max', 'group_concat', 'string_agg'];
47
48 private static array $likeCache = [];
49
50 /** 集約関数か（minとmaxは引数が1つの場合だけ） */
51 public static function isAggregate(string $name, int $argc, bool $star): bool {
52 if ($name === 'count') return $star || $argc <= 1;
53 if ($name === 'min' || $name === 'max') return $argc === 1;
54 return in_array($name, self::AGGREGATES, true);
55 }
56
57 /**
58 * スカラー関数
59 *
60 * @param string $name 小文字の名前
61 * @param array $a 引数の値
62 * @param Engine $eng last_insert_rowid()等
63 * @param array $colls 引数の照合順序（min・max用）
64 * @return mixed
65 */
66 public static function call(string $name, array $a, Engine $eng, array $colls = []): mixed {
67 $n = count($a);
68 $need = function(int $min, ?int $max = null) use ($n, $name): void {
69 $max ??= $min;
70 if ($n < $min || ($max >= 0 && $n > $max)) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
71 };
72 switch ($name) {
73 case 'abs':
74 $need(1);
75 $v = $a[0];
76 if ($v === null) return null;
77 $v = Value::toNumber($v);
78 if ($v === PHP_INT_MIN) throw new DbException('整数のオーバーフロー', 1, 'HY000');
79 return is_int($v) ? abs($v) : abs((float)$v);
80 case 'changes':
81 return $eng->changes;
82 case 'total_changes':
83 return $eng->totalChanges;
84 case 'last_insert_rowid':
85 return $eng->lastInsertRowid;
86 case 'char':
87 return implode('', array_map(fn($c) => mb_chr((int)Value::toInt($c) ?: 0xFFFD, 'UTF-8') ?: '', $a));
88 case 'coalesce':
89 if ($n < 2) throw new DbException('関数coalesce()の引数の数が違います', 1, 'HY000');
90 foreach ($a as $v) if ($v !== null) return $v;
91 return null;
92 case 'ifnull':
93 $need(2);
94 return $a[0] ?? $a[1];
95 case 'iif':
96 $need(3);
97 return Value::truth($a[0]) ? $a[1] : $a[2];
98 case 'nullif':
99 $need(2);
100 return Record::compare($a[0], $a[1], $colls[0] ?? 'BINARY') === 0 ? null : $a[0];
101 case 'concat':
102 return implode('', array_map(fn($v) => (string)Value::toText($v), $a));
103 case 'concat_ws':
104 if ($n < 1) throw new DbException('関数concat_ws()の引数の数が違います', 1, 'HY000');
105 if ($a[0] === null) return null;
106 $sep = Value::toText($a[0]);
107 return implode($sep, array_map(fn($v) => Value::toText($v), array_filter(array_slice($a, 1), fn($v) => $v !== null)));
108 case 'hex':
109 $need(1);
110 if ($a[0] === null) return '';
111 return strtoupper(bin2hex((string)Value::toText($a[0])));
112 case 'unhex':
113 $need(1, 2);
114 if ($a[0] === null) return null;
115 $s = (string)Value::toText($a[0]);
116 if ($n === 2 && $a[1] !== null) $s = str_replace(str_split((string)Value::toText($a[1])), '', $s);
117 if (strlen($s) % 2 || ($s !== '' && !ctype_xdigit($s))) return null;
118 return new Blob((string)hex2bin($s));
119 case 'instr':
120 $need(2);
121 if ($a[0] === null || $a[1] === null) return null;
122 if ($a[0] instanceof Blob || $a[1] instanceof Blob) {
123 $p = strpos((string)$a[0], (string)$a[1]);
124 return $p === false ? 0 : $p + 1;
125 }
126 $p = mb_strpos((string)Value::toText($a[0]), (string)Value::toText($a[1]), 0, 'UTF-8');
127 return $p === false ? 0 : $p + 1;
128 case 'length':
129 $need(1);
130 if ($a[0] === null) return null;
131 if ($a[0] instanceof Blob) return strlen($a[0]->data);
132 return mb_strlen((string)Value::toText($a[0]), 'UTF-8');
133 case 'octet_length':
134 $need(1);
135 return $a[0] === null ? null : strlen((string)Value::toText($a[0]));
136 case 'likely':
137 case 'unlikely':
138 $need(1);
139 return $a[0];
140 case 'likelihood':
141 $need(2);
142 return $a[0];
143 case 'lower':
144 $need(1);
145 return $a[0] === null ? null : strtolower((string)Value::toText($a[0]));
146 case 'upper':
147 $need(1);
148 return $a[0] === null ? null : strtoupper((string)Value::toText($a[0]));
149 case 'trim':
150 case 'ltrim':
151 case 'rtrim':
152 $need(1, 2);
153 if ($a[0] === null || ($n === 2 && $a[1] === null)) return null;
154 $chars = $n === 2 ? (string)Value::toText($a[1]) : ' ';
155 $s = (string)Value::toText($a[0]);
156 if ($chars === '') return $s;
157 $set = preg_quote($chars, '/');
158 $u = mb_check_encoding($chars, 'UTF-8') && mb_check_encoding($s, 'UTF-8') ? 'u' : '';
159 if ($name !== 'rtrim') $s = preg_replace("/^[{$set}]+/{$u}", '', $s);
160 if ($name !== 'ltrim') $s = preg_replace("/[{$set}]+$/{$u}", '', $s);
161 return $s;
162 case 'max':
163 case 'min':
164 if ($n < 2) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
165 $best = null;
166 foreach ($a as $i => $v) {
167 if ($v === null) return null;
168 if ($best === null || ($name === 'max' ? Record::compare($v, $best, $colls[$i] ?? 'BINARY') > 0 : Record::compare($v, $best, $colls[$i] ?? 'BINARY') < 0)) $best = $v;
169 }
170 return $best;
171 case 'printf':
172 case 'format':
173 if ($n < 1) throw new DbException("関数{$name}()の引数の数が違います", 1, 'HY000');
174 return $a[0] === null ? null : self::printf((string)Value::toText($a[0]), array_slice($a, 1));
175 case 'quote':
176 $need(1);
177 return Value::quote($a[0]);
178 case 'random':
179 return random_int(PHP_INT_MIN, PHP_INT_MAX);
180 case 'randomblob':
181 $need(1);
182 return new Blob(random_bytes(max(1, (int)Value::toInt($a[0]))));
183 case 'zeroblob':
184 $need(1);
185 return new Blob(str_repeat("\0", max(0, (int)Value::toInt($a[0]))));
186 case 'replace':
187 $need(3);
188 if ($a[0] === null || $a[1] === null || $a[2] === null) return null;
189 $from = (string)Value::toText($a[1]);
190 return $from === '' ? $a[0] : str_replace($from, (string)Value::toText($a[2]), (string)Value::toText($a[0]));
191 case 'round':
192 $need(1, 2);
193 if ($a[0] === null) return null;
194 $d = $n === 2 ? (int)Value::toInt($a[1]) : 0;
195 return round((float)Value::toNumber($a[0]), max(0, min(30, $d)));
196 case 'sign':
197 $need(1);
198 $v = $a[0];
199 if ($v === null || (is_string($v) && Value::parseNumber($v, true) === null) || $v instanceof Blob) return null;
200 $v = Value::toNumber($v);
201 return $v > 0 ? 1 : ($v < 0 ? -1 : 0);
202 case 'sqlite_version':
203 return '3.45.1';
204 case 'substr':
205 case 'substring':
206 $need(2, 3);
207 if ($a[0] === null || $a[1] === null || ($n === 3 && $a[2] === null)) return null;
208 return self::substr($a[0], (int)Value::toInt($a[1]), $n === 3 ? (int)Value::toInt($a[2]) : null);
209 case 'typeof':
210 $need(1);
211 return Value::type($a[0]);
212 case 'unicode':
213 $need(1);
214 if ($a[0] === null) return null;
215 $s = (string)Value::toText($a[0]);
216 return $s === '' ? null : mb_ord(mb_substr($s, 0, 1, 'UTF-8'), 'UTF-8');
217 case 'glob':
218 $need(2);
219 return $a[0] === null || $a[1] === null ? null : (int)self::glob((string)Value::toText($a[1]), (string)Value::toText($a[0]));
220 case 'like':
221 $need(2, 3);
222 if ($a[0] === null || $a[1] === null) return null;
223 return (int)self::like((string)Value::toText($a[1]), (string)Value::toText($a[0]), $n === 3 ? Value::toText($a[2]) : null);
224 case 'current_timestamp':
225 return gmdate('Y-m-d H:i:s');
226 case 'current_date':
227 return gmdate('Y-m-d');
228 case 'current_time':
229 return gmdate('H:i:s');
230 case 'date':
231 case 'time':
232 case 'datetime':
233 case 'julianday':
234 case 'unixepoch':
235 case 'strftime':
236 case 'timediff':
237 return self::dateFunc($name, $a);
238 case 'json':
239 $need(1);
240 $d = self::jsonDecode($a[0]);
241 return json_encode($d, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION);
242 case 'json_valid':
243 $need(1);
244 if ($a[0] === null) return null;
245 json_decode((string)Value::toText($a[0]));
246 return (int)(json_last_error() === JSON_ERROR_NONE);
247 case 'json_quote':
248 $need(1);
249 return self::jsonText(self::toJsonValue($a[0]));
250 case 'json_array':
251 return self::jsonText(array_map([self::class, 'toJsonValue'], $a));
252 case 'json_object':
253 if ($n % 2) throw new DbException('json_object()の引数は偶数です', 1, 'HY000');
254 $o = new \stdClass();
255 for ($i = 0; $i < $n; $i += 2) $o->{(string)Value::toText($a[$i])} = self::toJsonValue($a[$i + 1]);
256 return self::jsonText($o);
257 case 'json_extract':
258 case 'json_extract_text':
259 case 'json_extract_json':
260 if ($n < 2) throw new DbException('json_extract()の引数の数が違います', 1, 'HY000');
261 if ($a[0] === null) return null;
262 $doc = self::jsonDecode($a[0]);
263 $out = [];
264 foreach (array_slice($a, 1) as $path) {
265 $found = true;
266 $v = self::jsonPath($doc, $path, $name !== 'json_extract', $found);
267 if ($name === 'json_extract_json') return $found ? self::jsonText($v) : null;
268 $out[] = $v;
269 }
270 if ($n === 2) {
271 $v = $out[0];
272 if (is_array($v) || is_object($v)) return self::jsonText($v);
273 if (is_bool($v)) return (int)$v;
274 return $v;
275 }
276 return self::jsonText($out);
277 case 'json_type':
278 $need(1, 2);
279 if ($a[0] === null) return null;
280 $doc = self::jsonDecode($a[0]);
281 $found = true;
282 $v = $n === 2 ? self::jsonPath($doc, $a[1], false, $found) : $doc;
283 if (!$found) return null;
284 return match (true) {
285 $v === null => 'null', $v === true => 'true', $v === false => 'false', is_int($v) => 'integer', is_float($v) => 'real',
286 is_string($v) => 'text', is_array($v) => 'array', default => 'object',
287 };
288 case 'json_array_length':
289 $need(1, 2);
290 if ($a[0] === null) return null;
291 $doc = self::jsonDecode($a[0]);
292 $found = true;
293 $v = $n === 2 ? self::jsonPath($doc, $a[1], false, $found) : $doc;
294 return is_array($v) ? count($v) : 0;
295 }
296 throw new DbException("関数がありません：{$name}()", 1, 'HY000');
297 }
298
299 private static function substr(mixed $v, int $start, ?int $len): mixed {
300 $blob = $v instanceof Blob;
301 $s = $blob ? $v->data : (string)Value::toText($v);
302 $total = $blob ? strlen($s) : mb_strlen($s, 'UTF-8');
303 // SQLiteの規則：1から、0は最初の前、負は最後から
304 if ($start < 0) {
305 $start = $total + $start + 1;
306 if ($start < 1) {
307 if ($len !== null) $len += $start - 1;
308 $start = 1;
309 }
310 } else if ($start === 0) {
311 $start = 1;
312 if ($len !== null) $len--;
313 }
314 if ($len !== null && $len < 0) {
315 $start += $len;
316 $len = -$len;
317 if ($start < 1) {
318 $len += $start - 1;
319 $start = 1;
320 }
321 }
322 $len = $len === null ? null : max(0, $len);
323 $r = $blob ? (string)substr($s, $start - 1, $len ?? PHP_INT_MAX) : mb_substr($s, $start - 1, $len, 'UTF-8');
324 return $blob ? new Blob($r) : $r;
325 }
326
327 /** LIKE（ASCIIは大文字と小文字を区別しない） */
328 public static function like(string $pattern, string $s, ?string $escape = null): bool {
329 $key = 'L'.$escape."\0".$pattern;
330 if (!isset(self::$likeCache[$key])) {
331 if (count(self::$likeCache) > 500) self::$likeCache = [];
332 $re = '';
333 $u = mb_check_encoding($pattern, 'UTF-8');
334 $chars = $u ? mb_str_split($pattern, 1, 'UTF-8') : str_split($pattern);
335 for ($i = 0; $i < count($chars); $i++) {
336 $c = $chars[$i];
337 if ($escape !== null && $c === $escape) {
338 $re .= preg_quote(strtolower($chars[++$i] ?? ''), '/');
339 } else if ($c === '%') {
340 $re .= '.*';
341 } else if ($c === '_') {
342 $re .= '.';
343 } else {
344 $re .= preg_quote(strtolower($c), '/');
345 }
346 }
347 self::$likeCache[$key] = '/^'.$re.'$/s'.($u ? 'u' : '');
348 }
349 return (bool)@preg_match(self::$likeCache[$key], strtolower($s));
350 }
351
352 /** GLOB（大文字と小文字を区別する） */
353 public static function glob(string $pattern, string $s): bool {
354 $key = 'G'.$pattern;
355 if (!isset(self::$likeCache[$key])) {
356 $re = '';
357 $u = mb_check_encoding($pattern, 'UTF-8');
358 $chars = $u ? mb_str_split($pattern, 1, 'UTF-8') : str_split($pattern);
359 for ($i = 0; $i < count($chars); $i++) {
360 $c = $chars[$i];
361 if ($c === '*') {
362 $re .= '.*';
363 } else if ($c === '?') {
364 $re .= '.';
365 } else if ($c === '[') {
366 $j = $i + 1;
367 $set = '';
368 if (($chars[$j] ?? '') === '^') {
369 $set .= '^';
370 $j++;
371 }
372 if (($chars[$j] ?? '') === ']') {
373 $set .= '\]';
374 $j++;
375 }
376 while ($j < count($chars) && $chars[$j] !== ']') {
377 $set .= $chars[$j] === '-' ? '-' : preg_quote($chars[$j], '/');
378 $j++;
379 }
380 if ($j >= count($chars)) {
381 $re .= '\[';
382 continue;
383 }
384 $re .= '['.$set.']';
385 $i = $j;
386 } else {
387 $re .= preg_quote($c, '/');
388 }
389 }
390 self::$likeCache[$key] = '/^'.$re.'$/s'.($u ? 'u' : '');
391 }
392 return (bool)@preg_match(self::$likeCache[$key], $s);
393 }
394
395 /** printf()（%d、%s、%f、%x、%q、%Q等） */
396 private static function printf(string $fmt, array $args): string {
397 $i = 0;
398 return preg_replace_callback('/%([-+ 0#,!]*)(\d+|\*)?(?:\.(\d+|\*))?(ll|l)?([diouxXeEfgGcsqQwz%])/', function(array $m) use (&$i, $args): string {
399 [, $flags, $width, $prec, , $conv] = $m + ['', '', '', '', '', ''];
400 if ($conv === '%') return '%';
401 if ($width === '*') $width = (string)Value::toInt($args[$i++] ?? 0);
402 if ($prec === '*') $prec = (string)Value::toInt($args[$i++] ?? 0);
403 $v = $args[$i++] ?? null;
404 $flags = str_replace([',', '!'], '', $flags);
405 $spec = '%'.$flags.$width.($prec !== '' ? '.'.$prec : '');
406 switch ($conv) {
407 case 'd':
408 case 'i':
409 return sprintf($spec.'d', (int)Value::toInt($v));
410 case 'u':
411 return sprintf($spec.'u', (int)Value::toInt($v));
412 case 'o':
413 case 'x':
414 case 'X':
415 return sprintf($spec.$conv, (int)Value::toInt($v));
416 case 'e':
417 case 'E':
418 case 'f':
419 case 'g':
420 case 'G':
421 return sprintf($spec.($conv === 'f' ? 'F' : $conv), (float)Value::toNumber($v));
422 case 'c':
423 return mb_substr((string)Value::toText($v), 0, 1, 'UTF-8');
424 case 'q':
425 return $v === null ? '(NULL)' : str_replace("'", "''", (string)Value::toText($v));
426 case 'Q':
427 return $v === null ? 'NULL' : "'".str_replace("'", "''", (string)Value::toText($v))."'";
428 case 'w':
429 return $v === null ? '(NULL)' : str_replace('"', '""', (string)Value::toText($v));
430 default:
431 return sprintf($spec.'s', $v === null ? '' : (string)Value::toText($v));
432 }
433 }, $fmt);
434 }
435
436 ///////////////
437 // 日付
438
439 /**
440 * 時間の値をUNIXの秒（マイクロ秒付き）にする
441 *
442 * @return float|null
443 */
444 public static function timeValue(mixed $v): ?float {
445 if ($v === null) return null;
446 if (is_int($v) || is_float($v)) {
447 // ユリウス日
448 return ((float)$v - 2440587.5) * 86400.0;
449 }
450 $s = trim((string)Value::toText($v));
451 if (strcasecmp($s, 'now') === 0) return microtime(true);
452 if (preg_match('/^(\d{4})-(\d{2})-(\d{2})(?:[ T](\d{2}):(\d{2})(?::(\d{2})(\.\d+)?)?)?\s*(Z|[+-]\d{2}:?\d{2})?$/i', $s, $m)) {
453 $t = gmmktime((int)($m[4] ?? 0), (int)($m[5] ?? 0), (int)($m[6] ?? 0), (int)$m[2], (int)$m[3], (int)$m[1]);
454 $t += (float)('0'.($m[7] ?? ''));
455 if (!empty($m[8]) && strtoupper($m[8]) !== 'Z') {
456 $sign = $m[8][0] === '-' ? -1 : 1;
457 $hm = str_replace(':', '', substr($m[8], 1));
458 $t -= $sign * ((int)substr($hm, 0, 2) * 3600 + (int)substr($hm, 2, 2) * 60);
459 }
460 return (float)$t;
461 }
462 if (preg_match('/^(\d{2}):(\d{2})(?::(\d{2})(\.\d+)?)?$/', $s, $m)) {
463 return (float)gmmktime((int)$m[1], (int)$m[2], (int)($m[3] ?? 0), 1, 1, 2000) + (float)('0'.($m[4] ?? ''));
464 }
465 $n = Value::parseNumber($s, true);
466 if ($n !== null) return ((float)$n - 2440587.5) * 86400.0;
467 return null;
468 }
469
470 private static function dateFunc(string $name, array $a): mixed {
471 $fmt = null;
472 if ($name === 'strftime') {
473 if (empty($a) || $a[0] === null) return null;
474 $fmt = (string)Value::toText($a[0]);
475 $a = array_slice($a, 1);
476 }
477 if ($name === 'timediff') {
478 if (count($a) !== 2) throw new DbException('timediff()の引数の数が違います', 1, 'HY000');
479 $x = self::timeValue($a[0]);
480 $y = self::timeValue($a[1]);
481 if ($x === null || $y === null) return null;
482 $d = $x - $y;
483 $sign = $d < 0 ? '-' : '+';
484 $d = abs($d);
485 $days = (int)floor($d / 86400);
486 return sprintf('%s%04d-%02d-%02d %02d:%02d:%06.3f', $sign, 0, 0, $days, (int)($d / 3600) % 24, (int)($d / 60) % 60, fmod($d, 60));
487 }
488 $v = empty($a) ? 'now' : $a[0];
489 $mods = array_slice($a, 1);
490 // unixepochの修飾子
491 $t = null;
492 foreach ($mods as $m) {
493 if (is_string($m) && strcasecmp(trim($m), 'unixepoch') === 0 && (is_int($v) || is_float($v) || (is_string($v) && is_numeric($v)))) {
494 $t = (float)Value::toNumber($v);
495 }
496 }
497 if ($t === null) $t = self::timeValue($v);
498 if ($t === null) return null;
499 foreach ($mods as $m) {
500 if ($m === null) return null;
501 $m = strtolower(trim((string)Value::toText($m)));
502 if ($m === 'unixepoch' || $m === 'utc' || $m === 'auto' || $m === 'subsec' || $m === 'subsecond') continue;
503 if ($m === 'localtime') {
504 $t += (int)date('Z', (int)$t);
505 continue;
506 }
507 if (preg_match('/^([+-]?\d+(?:\.\d+)?)\s*(second|minute|hour|day|month|year)s?$/', $m, $mm)) {
508 $q = (float)$mm[1];
509 switch ($mm[2]) {
510 case 'second': $t += $q; break;
511 case 'minute': $t += $q * 60; break;
512 case 'hour': $t += $q * 3600; break;
513 case 'day': $t += $q * 86400; break;
514 case 'month':
515 case 'year':
516 $frac = $t - floor($t);
517 $d = new \DateTimeImmutable('@'.(int)floor($t));
518 $months = (int)$q * ($mm[2] === 'year' ? 12 : 1);
519 $y = (int)$d->format('Y');
520 $mo = (int)$d->format('n') + $months;
521 $y += intdiv($mo - 1 - (($mo - 1) % 12 + 12) % 12, 12);
522 $mo = (($mo - 1) % 12 + 12) % 12 + 1;
523 $t = gmmktime((int)$d->format('G'), (int)$d->format('i'), (int)$d->format('s'), $mo, (int)$d->format('j'), $y) + $frac;
524 break;
525 }
526 continue;
527 }
528 if (preg_match('/^start of (day|month|year)$/', $m, $mm)) {
529 $d = gmdate('Y-m-d', (int)floor($t));
530 [$y, $mo, $dd] = explode('-', $d);
531 $t = (float)match ($mm[1]) { 'day' => gmmktime(0, 0, 0, (int)$mo, (int)$dd, (int)$y), 'month' => gmmktime(0, 0, 0, (int)$mo, 1, (int)$y), default => gmmktime(0, 0, 0, 1, 1, (int)$y) };
532 continue;
533 }
534 if (preg_match('/^weekday (\d)$/', $m, $mm)) {
535 $w = (int)gmdate('w', (int)floor($t));
536 $t += ((((int)$mm[1] - $w) % 7 + 7) % 7) * 86400;
537 continue;
538 }
539 return null;
540 }
541 $sec = (int)floor($t);
542 $frac = $t - $sec;
543 switch ($name) {
544 case 'date':
545 return gmdate('Y-m-d', $sec);
546 case 'time':
547 return gmdate('H:i:s', $sec);
548 case 'datetime':
549 return gmdate('Y-m-d H:i:s', $sec);
550 case 'julianday':
551 return $t / 86400.0 + 2440587.5;
552 case 'unixepoch':
553 return $sec;
554 }
555 // strftime
556 return preg_replace_callback('/%(.)/', function(array $m) use ($sec, $frac, $t): string {
557 return match ($m[1]) {
558 'd' => gmdate('d', $sec), 'e' => str_pad(gmdate('j', $sec), 2, ' ', STR_PAD_LEFT), 'f' => sprintf('%06.3f', (float)gmdate('s', $sec) + $frac),
559 'F' => gmdate('Y-m-d', $sec), 'H' => gmdate('H', $sec), 'I' => gmdate('h', $sec), 'j' => sprintf('%03d', (int)gmdate('z', $sec) + 1),
560 'J' => (string)($t / 86400.0 + 2440587.5), 'k' => str_pad(gmdate('G', $sec), 2, ' ', STR_PAD_LEFT), 'l' => str_pad(gmdate('g', $sec), 2, ' ', STR_PAD_LEFT),
561 'm' => gmdate('m', $sec), 'M' => gmdate('i', $sec), 'p' => gmdate('A', $sec), 'P' => gmdate('a', $sec), 'R' => gmdate('H:i', $sec),
562 's' => (string)$sec, 'S' => gmdate('s', $sec), 'T' => gmdate('H:i:s', $sec), 'u' => gmdate('N', $sec), 'w' => gmdate('w', $sec),
563 'W' => sprintf('%02d', intdiv((int)gmdate('z', $sec) + 7 - (((int)gmdate('w', $sec) + 6) % 7), 7)), 'Y' => gmdate('Y', $sec),
564 'G' => gmdate('o', $sec), 'g' => gmdate('y', $sec), 'V' => gmdate('W', $sec), 'U' => sprintf('%02d', intdiv((int)gmdate('z', $sec) + 7 - (int)gmdate('w', $sec), 7)),
565 '%' => '%',
566 default => '',
567 };
568 }, $fmt ?? '');
569 }
570
571 ///////////////
572 // JSON
573
574 private static function jsonDecode(mixed $v): mixed {
575 if ($v === null) return null;
576 $d = json_decode((string)Value::toText($v), false, 512, JSON_BIGINT_AS_STRING);
577 if (json_last_error() !== JSON_ERROR_NONE) throw new DbException('不正なJSON', 1, 'HY000');
578 return $d;
579 }
580
581 private static function jsonText(mixed $v): string {
582 return (string)json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRESERVE_ZERO_FRACTION);
583 }
584
585 public static function toJsonValue(mixed $v): mixed {
586 if ($v instanceof Blob) throw new DbException('JSONにBLOBは使えません', 1, 'HY000');
587 return $v;
588 }
589
590 /** $.a.b[0]、->の場合は「a」・数値も */
591 private static function jsonPath(mixed $doc, mixed $path, bool $arrow, bool &$found): mixed {
592 if ($path === null) {
593 $found = false;
594 return null;
595 }
596 if ($arrow && is_int($path)) $path = '$['.$path.']';
597 $p = (string)Value::toText($path);
598 if ($arrow && !str_starts_with($p, '$')) $p = '$.'.$p;
599 if (!str_starts_with($p, '$')) throw new DbException("不正なJSONのパス：{$p}", 1, 'HY000');
600 preg_match_all('/\.(?:"((?:[^"\\\\]|\\\\.)*)"|([^.\[]+))|\[(#-)?(\d+)\]/', substr($p, 1), $m, PREG_SET_ORDER);
601 $cur = $doc;
602 foreach ($m as $part) {
603 if (isset($part[4]) && $part[4] !== '') {
604 if (!is_array($cur)) {
605 $found = false;
606 return null;
607 }
608 $i = (int)$part[4];
609 if (!empty($part[3])) $i = count($cur) - $i;
610 if (!array_key_exists($i, $cur)) {
611 $found = false;
612 return null;
613 }
614 $cur = $cur[$i];
615 } else {
616 $k = $part[1] !== '' ? stripcslashes($part[1]) : $part[2];
617 if (!is_object($cur) || !property_exists($cur, $k)) {
618 $found = false;
619 return null;
620 }
621 $cur = $cur->$k;
622 }
623 }
624 return $cur;
625 }
626
627 ///////////////
628 // 集約関数
629
630 /** 集約の初期の状態 */
631 public static function aggInit(string $name): array {
632 return ['n' => 0, 'v' => null, 'sum' => 0, 'float' => false, 'parts' => [], 'seen' => []];
633 }
634
635 /** 1行分を足す */
636 public static function aggStep(string $name, array &$st, array $args, bool $star, string $coll): void {
637 if ($star) {
638 $st['n']++;
639 return;
640 }
641 $v = $args[0] ?? null;
642 if ($v === null) return;
643 switch ($name) {
644 case 'count':
645 $st['n']++;
646 return;
647 case 'sum':
648 case 'total':
649 case 'avg':
650 $num = Value::toNumber($v);
651 $st['n']++;
652 // 整数以外（文字列も）が有ればREAL
653 if (!is_int($v) || $st['float']) {
654 $st['float'] = true;
655 $st['sum'] = (float)$st['sum'] + (float)$num;
656 } else {
657 $s = $st['sum'] + $num;
658 if (is_float($s)) {
659 if ($name === 'sum') throw new DbException('整数のオーバーフロー', 1, 'HY000');
660 $st['float'] = true;
661 }
662 $st['sum'] = $s;
663 }
664 return;
665 case 'min':
666 case 'max':
667 if ($st['n'] === 0 || ($name === 'min' ? Record::compare($v, $st['v'], $coll) < 0 : Record::compare($v, $st['v'], $coll) > 0)) $st['v'] = $v;
668 $st['n']++;
669 return;
670 case 'group_concat':
671 case 'string_agg':
672 $sep = array_key_exists(1, $args) ? $args[1] : ($name === 'string_agg' ? null : ',');
673 if ($name === 'string_agg' && count($args) < 2) throw new DbException('string_agg()の引数の数が違います', 1, 'HY000');
674 if ($st['n'] > 0) $st['parts'][] = (string)Value::toText($sep);
675 $st['parts'][] = (string)Value::toText($v);
676 $st['n']++;
677 return;
678 }
679 }
680
681 /** 結果 */
682 public static function aggFinal(string $name, array $st): mixed {
683 return match ($name) {
684 'count' => $st['n'],
685 'sum' => $st['n'] === 0 ? null : $st['sum'],
686 'total' => (float)$st['sum'],
687 'avg' => $st['n'] === 0 ? null : (float)$st['sum'] / $st['n'],
688 'min', 'max' => $st['v'],
689 'group_concat', 'string_agg' => $st['n'] === 0 ? null : implode('', $st['parts']),
690 default => null,
691 };
692 }
693}