PluginProbe
404 Solution / trunk
404 Solution vtrunk
4.3.5 4.3.4 4.3.3 4.3.2 4.3.1 4.3.0 4.2.0 4.1.19 4.1.18 4.1.17 4.1.16 4.1.15 4.1.13 4.1.12 4.1.11 4.1.10 4.1.9 4.1.8 4.1.7 4.1.6 4.1.5 4.1.4 4.1.3 trunk 2.30.0 All 109 releases
404-solution / includes / stats / StatsReadRepository.php

StatsReadRepository.php in 404 Solution trunk, at includes/stats/StatsReadRepository.php

459 lines 18.6 KB
No matching file
Up and down to move Enter to open Esc to close
Raw Download Zip
1 <?php
2
3 if (!defined('ABSPATH')) {
4 exit;
5 }
6
7 /**
8 * Reads aggregate statistics used by admin dashboards and periodic summaries.
9 */
10 class ABJ_404_Solution_StatsReadRepository {
11
12 /** @var int Max age for cached stats-periodic aggregates. */
13 const PERIODIC_STATS_CACHE_TTL_SECONDS = 300;
14 /** @var int Minimum interval before recalculating expensive stats aggregates. */
15 const PERIODIC_STATS_REFRESH_COOLDOWN_SECONDS = 30;
16
17 /** @var ABJ_404_Solution_DatabaseQueryInterface */
18 private $dbCore;
19 /** @var ABJ_404_Solution_LogsRepositoryInterface */
20 private $logsRepo;
21 /** @var ABJ_404_Solution_Logging */
22 private $logger;
23 /** @var ABJ_404_Solution_StatsRefreshLock */
24 private $refreshLock;
25
26 /**
27 * @param ABJ_404_Solution_DatabaseQueryInterface $dbCore
28 * @param ABJ_404_Solution_LogsRepositoryInterface $logsRepo
29 * @param ABJ_404_Solution_Logging $logging
30 * @param ABJ_404_Solution_StatsRefreshLock $refreshLock
31 */
32 public function __construct(
33 ABJ_404_Solution_DatabaseQueryInterface $dbCore,
34 ABJ_404_Solution_LogsRepositoryInterface $logsRepo,
35 $logging,
36 ABJ_404_Solution_StatsRefreshLock $refreshLock
37 ) {
38 $this->dbCore = $dbCore;
39 $this->logsRepo = $logsRepo;
40 $this->logger = $logging;
41 $this->refreshLock = $refreshLock;
42 }
43
44 /**
45 * @param string $query
46 * @param array<int|string, mixed> $valueParams
47 * @return int
48 */
49 public function getStatsCount($query, array $valueParams) {
50 if ($query == '') {
51 return 0;
52 }
53
54 $result = $this->dbCore->queryAndGetResults($query, array('query_params' => $valueParams));
55
56 if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
57 return 0;
58 }
59
60 $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
61 if (empty($rows)) {
62 $this->logger->debugMessage("getStatsCount returned no results for query: " . esc_html($query));
63 return 0;
64 }
65
66 $first = $rows[0];
67 if (is_array($first)) {
68 $value = reset($first);
69 } else {
70 $value = $first;
71 }
72 return $this->toInt($value, 0);
73 }
74
75 /**
76 * @param int $sinceTimestamp
77 * @param string $notFoundDest
78 * @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int}
79 */
80 public function getPeriodicStatsSummary($sinceTimestamp, $notFoundDest = '404') {
81 $sinceTimestamp = absint($sinceTimestamp);
82 $notFoundDest = sanitize_text_field((string)$notFoundDest);
83 if ($notFoundDest === '') {
84 $notFoundDest = '404';
85 }
86
87 $zero = $this->zeroPeriodicStats();
88
89 $logsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_logsv2}');
90 $sql = "SELECT
91 COUNT(CASE WHEN dest_url = %s THEN 1 END) AS disp404,
92 COUNT(DISTINCT CASE WHEN dest_url = %s THEN requested_url END) AS distinct404,
93 COUNT(DISTINCT CASE WHEN dest_url = %s THEN user_ip END) AS visitors404,
94 COUNT(DISTINCT CASE WHEN dest_url = %s THEN referrer END) AS refer404,
95 COUNT(CASE WHEN dest_url <> %s THEN 1 END) AS redirected,
96 COUNT(DISTINCT CASE WHEN dest_url <> %s THEN requested_url END) AS distinctredirected,
97 COUNT(DISTINCT CASE WHEN dest_url <> %s THEN user_ip END) AS distinctvisitors,
98 COUNT(DISTINCT CASE WHEN dest_url <> %s THEN referrer END) AS distinctrefer
99 FROM {$logsTable}
100 WHERE timestamp >= %d";
101
102 $result = $this->dbCore->queryAndGetResults($sql, array(
103 'query_params' => array(
104 $notFoundDest, $notFoundDest, $notFoundDest, $notFoundDest,
105 $notFoundDest, $notFoundDest, $notFoundDest, $notFoundDest,
106 $sinceTimestamp,
107 ),
108 ));
109
110 if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
111 return $zero;
112 }
113
114 $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
115 if (empty($rows) || !is_array($rows[0] ?? null)) {
116 return $zero;
117 }
118 $row = $rows[0];
119
120 foreach ($zero as $key => $unused) {
121 $zero[$key] = array_key_exists($key, $row) ? $this->toInt($row[$key], 0) : 0;
122 }
123
124 return $zero;
125 }
126
127 /**
128 * @param string $notFoundDest
129 * @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>}
130 */
131 public function getPeriodicStatsSummariesCached($notFoundDest = '404') {
132 $now = abj_clock()->now();
133 $thresholds = $this->computePeriodicThresholds($now);
134
135 $emptyPayload = $this->emptyPeriodicPayload();
136 $cacheKey = 'abj404_stats_periodic_v1_' . $this->currentBlogId() . '_' . md5(
137 $notFoundDest . '|' . $thresholds['today'] . '|' . $thresholds['month'] . '|' . $thresholds['year']
138 );
139 $cached = function_exists('get_transient') ? get_transient($cacheKey) : null;
140
141 $isCachedValid = (is_array($cached) && isset($cached['periods']) && is_array($cached['periods']));
142 $cachedPeriods = $isCachedValid ? $this->normalizePeriodicPayload($cached['periods']) : $emptyPayload;
143 $currentMaxLogId = -1;
144 try {
145 $currentMaxLogId = $this->toInt($this->logsRepo->getMaxLogId(), -1);
146 } catch (Throwable $unused) { // allow-silent-catch: cache-key derivation; -1 means "no cached entry, recompute" which is the correct degraded behavior
147 $currentMaxLogId = -1;
148 }
149
150 if ($isCachedValid) {
151 $refreshedAt = $this->toInt($cached['refreshed_at'] ?? 0, 0);
152 $ageSeconds = max(0, abj_clock()->now() - $refreshedAt);
153 $cachedMaxLogId = $this->toInt($cached['max_log_id'] ?? -1, -1);
154 if ($currentMaxLogId >= 0 && $cachedMaxLogId === $currentMaxLogId) {
155 return $cachedPeriods;
156 }
157 if ($ageSeconds < self::PERIODIC_STATS_REFRESH_COOLDOWN_SECONDS) {
158 return $cachedPeriods;
159 }
160 }
161
162 $lockKey = 'stats-periodic:' . $cacheKey;
163 $lockAcquired = $this->refreshLock->acquire($lockKey);
164 if (!$lockAcquired && $isCachedValid) {
165 return $cachedPeriods;
166 }
167
168 try {
169 $result = array(
170 'today' => $this->getPeriodicStatsSummary($thresholds['today'], $notFoundDest),
171 'month' => $this->getPeriodicStatsSummary($thresholds['month'], $notFoundDest),
172 'year' => $this->getPeriodicStatsSummary($thresholds['year'], $notFoundDest),
173 'all' => $this->getPeriodicStatsSummary($thresholds['all'], $notFoundDest),
174 );
175
176 if (function_exists('set_transient')) {
177 set_transient(
178 $cacheKey,
179 array(
180 'refreshed_at' => abj_clock()->now(),
181 'max_log_id' => $currentMaxLogId,
182 'periods' => $result,
183 ),
184 self::PERIODIC_STATS_CACHE_TTL_SECONDS
185 );
186 }
187
188 return $result;
189 } finally {
190 if ($lockAcquired) {
191 $this->refreshLock->release($lockKey);
192 }
193 }
194 }
195
196 /** @return int */
197 public function getEarliestLogTimestamp() {
198 // allow-unbounded-select: MIN(timestamp) aggregate; returns a single row
199 $query = 'SELECT min(timestamp) as timestamp FROM {wp_abj404_logsv2}';
200
201 $result = $this->dbCore->queryAndGetResults($query);
202
203 if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
204 return -1;
205 }
206
207 $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
208 if (empty($rows)) {
209 return -1;
210 }
211
212 $first = $rows[0];
213 $value = is_array($first) ? reset($first) : $first;
214 if ($value === null || $value === false || $value === '') {
215 return -1;
216 }
217 return $this->toInt($value, -1);
218 }
219
220 /**
221 * Count redirect rows grouped into match-confidence bands for the stats
222 * page Match Confidence card.
223 *
224 * A NULL score is the "manual" band (no automated scoring took place);
225 * scored rows fall into high/medium/low per
226 * {@see ABJ_404_Solution_ScoreThresholds}. Disabled rows and rows with
227 * status 0 are excluded. Routed through queryAndGetResults() so the
228 * 5x SUM(CASE...) aggregate inherits the centralized 60s SELECT timeout
229 * (the redirects table can be very large on busy sites).
230 *
231 * This is the single owner of the confidence-band SQL and thresholds:
232 * the view layer asks for the counts and only formats them.
233 *
234 * @return array{high:int,medium:int,low:int,manual:int,avg:float|null,total:int}|null
235 * Band counts plus the rounded average score (avg is null when no scored
236 * rows exist), or null when the query timed out, errored, or returned no
237 * aggregate row so the caller can skip rendering the card.
238 */
239 public function getConfidenceBandCounts() {
240 $redirectsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_redirects}');
241
242 $high = ABJ_404_Solution_ScoreThresholds::HIGH;
243 $medium = ABJ_404_Solution_ScoreThresholds::MEDIUM;
244 $sql = "SELECT
245 SUM(CASE WHEN score IS NULL THEN 1 ELSE 0 END) AS manual_count,
246 SUM(CASE WHEN score >= {$high} THEN 1 ELSE 0 END) AS high_count,
247 SUM(CASE WHEN score >= {$medium} AND score < {$high} THEN 1 ELSE 0 END) AS medium_count,
248 SUM(CASE WHEN score IS NOT NULL AND score < {$medium} THEN 1 ELSE 0 END) AS low_count,
249 AVG(score) AS avg_score
250 FROM `{$redirectsTable}`
251 WHERE disabled = %d AND status != %d";
252
253 $result = $this->dbCore->queryAndGetResults($sql, array('query_params' => array(0, 0)));
254 if (!empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] != '')) {
255 return null;
256 }
257 $rows = is_array($result['rows'] ?? null) ? $result['rows'] : array();
258 if (empty($rows) || !is_array($rows[0] ?? null)) {
259 return null;
260 }
261 $row = $rows[0];
262
263 $highCount = $this->toInt($row['high_count'] ?? 0, 0);
264 $mediumCount = $this->toInt($row['medium_count'] ?? 0, 0);
265 $lowCount = $this->toInt($row['low_count'] ?? 0, 0);
266 $manualCount = $this->toInt($row['manual_count'] ?? 0, 0);
267 $avgRaw = $row['avg_score'] ?? null;
268 $avgScore = is_numeric($avgRaw) ? round((float)$avgRaw, 1) : null;
269
270 return array(
271 'high' => $highCount,
272 'medium' => $mediumCount,
273 'low' => $lowCount,
274 'manual' => $manualCount,
275 'avg' => $avgScore,
276 'total' => $highCount + $mediumCount + $lowCount + $manualCount,
277 );
278 }
279
280 /** @return array<string, mixed> */
281 public function buildStatsDashboardSnapshotData() {
282 $redirectsTable = $this->dbCore->doTableNameReplacements("{wp_abj404_redirects}");
283
284 $auto301 = $this->getStatsCount(
285 "select count(id) from $redirectsTable where disabled = 0 and code = 301 and status = %d",
286 array(ABJ404_STATUS_AUTO)
287 );
288 $auto302 = $this->getStatsCount(
289 "select count(id) from $redirectsTable where disabled = 0 and code = 302 and status = %d",
290 array(ABJ404_STATUS_AUTO)
291 );
292 $manual301 = $this->getStatsCount(
293 "select count(id) from $redirectsTable where disabled = 0 and code = 301 and status = %d",
294 array(ABJ404_STATUS_MANUAL)
295 );
296 $manual302 = $this->getStatsCount(
297 "select count(id) from $redirectsTable where disabled = 0 and code = 302 and status = %d",
298 array(ABJ404_STATUS_MANUAL)
299 );
300 $trashedRedirects = $this->getStatsCount(
301 "select count(id) from $redirectsTable where disabled = 1 and (status = %d or status = %d)",
302 array(ABJ404_STATUS_AUTO, ABJ404_STATUS_MANUAL)
303 );
304
305 $captured = $this->getStatsCount(
306 "select count(id) from $redirectsTable where disabled = 0 and status = %d",
307 array(ABJ404_STATUS_CAPTURED)
308 );
309 $ignored = $this->getStatsCount(
310 "select count(id) from $redirectsTable where disabled = 0 and status in (%d, %d)",
311 array(ABJ404_STATUS_IGNORED, ABJ404_STATUS_LATER)
312 );
313 $trashedCaptured = $this->getStatsCount(
314 "select count(id) from $redirectsTable where disabled = 1 and (status in (%d, %d, %d) )",
315 array(ABJ404_STATUS_CAPTURED, ABJ404_STATUS_IGNORED, ABJ404_STATUS_LATER)
316 );
317
318 $now = abj_clock()->now();
319 $thresholds = $this->computePeriodicThresholds($now);
320 $periods = array();
321 foreach ($thresholds as $periodKey => $ts) {
322 $periods[$periodKey] = $this->getPeriodicStatsSummary($ts, '404');
323 }
324
325 return array(
326 'redirects' => array(
327 'auto301' => intval($auto301),
328 'auto302' => intval($auto302),
329 'manual301' => intval($manual301),
330 'manual302' => intval($manual302),
331 'trashed' => intval($trashedRedirects),
332 ),
333 'captured' => array(
334 'captured' => intval($captured),
335 'ignored' => intval($ignored),
336 'trashed' => intval($trashedCaptured),
337 ),
338 'periods' => $periods,
339 );
340 }
341
342 /**
343 * 'today'/'month'/'year' period-start boundaries, anchored to the WP
344 * site's configured timezone (SiteTimezone) rather than PHP's implicit
345 * default timezone -- matching the convention established by
346 * RedirectScheduleTimezone and CronScheduler::scheduleDailyInWindowIfMissing().
347 * getPeriodicStatsSummary() compares these thresholds against `timestamp`,
348 * a true-UTC DB column, so deriving them with date()/mktime() (which read
349 * and reconstruct calendar fields using PHP's default timezone) silently
350 * picks the wrong local day/month/year start whenever the host's PHP
351 * default timezone differs from the site's configured timezone.
352 *
353 * @param int $now Unix epoch (true UTC) to anchor the boundaries to.
354 * @return array{today:int,month:int,year:int,all:int}
355 */
356 private function computePeriodicThresholds(int $now): array {
357 $siteTimezone = ABJ_404_Solution_SiteTimezone::resolve();
358 try {
359 $siteNow = (new DateTimeImmutable('@' . $now))->setTimezone($siteTimezone);
360 $today = (new DateTimeImmutable($siteNow->format('Y-m-d') . ' 00:00:00', $siteTimezone))->getTimestamp();
361 $firstOfMonth = (new DateTimeImmutable($siteNow->format('Y-m') . '-01 00:00:00', $siteTimezone))->getTimestamp();
362 $firstOfYear = (new DateTimeImmutable($siteNow->format('Y') . '-01-01 00:00:00', $siteTimezone))->getTimestamp();
363 } catch (Exception $e) {
364 // $siteNow->format() output is always a well-formed date string,
365 // so this is unreachable in practice; degrade to UTC-anchored
366 // boundaries rather than let a periodic stats read fail entirely.
367 $this->logger->warn('StatsReadRepository: failed to compute site-timezone-anchored ' .
368 'periodic thresholds, falling back to UTC: ' . $e->getMessage());
369 $today = gmmktime(0, 0, 0, (int)gmdate('m', $now), (int)gmdate('d', $now), (int)gmdate('Y', $now));
370 $firstOfMonth = gmmktime(0, 0, 0, (int)gmdate('m', $now), 1, (int)gmdate('Y', $now));
371 $firstOfYear = gmmktime(0, 0, 0, 1, 1, (int)gmdate('Y', $now));
372 }
373
374 return array(
375 'today' => (int)$today,
376 'month' => (int)$firstOfMonth,
377 'year' => (int)$firstOfYear,
378 'all' => 0,
379 );
380 }
381
382 /** @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int} */
383 private function zeroPeriodicStats(): array {
384 return array(
385 'disp404' => 0,
386 'distinct404' => 0,
387 'visitors404' => 0,
388 'refer404' => 0,
389 'redirected' => 0,
390 'distinctredirected' => 0,
391 'distinctvisitors' => 0,
392 'distinctrefer' => 0,
393 );
394 }
395
396 /** @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>} */
397 private function emptyPeriodicPayload(): array {
398 $zero = $this->zeroPeriodicStats();
399 return array(
400 'today' => $zero,
401 'month' => $zero,
402 'year' => $zero,
403 'all' => $zero,
404 );
405 }
406
407 /**
408 * @param mixed $periods
409 * @return array{today:array<string,int>,month:array<string,int>,year:array<string,int>,all:array<string,int>}
410 */
411 private function normalizePeriodicPayload($periods): array {
412 $payload = $this->emptyPeriodicPayload();
413 if (!is_array($periods)) {
414 return $payload;
415 }
416 foreach (array('today', 'month', 'year', 'all') as $periodKey) {
417 if (isset($periods[$periodKey]) && is_array($periods[$periodKey])) {
418 $payload[$periodKey] = $this->normalizePeriodicStats($periods[$periodKey]);
419 }
420 }
421 return $payload;
422 }
423
424 /**
425 * @param array<int|string, mixed> $stats
426 * @return array{disp404:int,distinct404:int,visitors404:int,refer404:int,redirected:int,distinctredirected:int,distinctvisitors:int,distinctrefer:int}
427 */
428 private function normalizePeriodicStats(array $stats): array {
429 $zero = $this->zeroPeriodicStats();
430 foreach ($zero as $key => $unused) {
431 $zero[$key] = array_key_exists($key, $stats) ? $this->toInt($stats[$key], 0) : 0;
432 }
433 return $zero;
434 }
435
436 /** @return int */
437 private function currentBlogId(): int {
438 $blogId = 1;
439 if (function_exists('get_current_blog_id')) {
440 $blogId = absint(get_current_blog_id());
441 if ($blogId <= 0) {
442 $blogId = 1;
443 }
444 }
445 return $blogId;
446 }
447
448 /** @param mixed $value @param int $default @return int */
449 private function toInt($value, int $default): int {
450 if ($value === null) {
451 return $default;
452 }
453 if (is_scalar($value)) {
454 return intval($value);
455 }
456 return $default;
457 }
458 }
459