| 1 |
<?php |
| 2 |
|
| 3 |
if (!defined('ABSPATH')) { |
| 4 |
exit; |
| 5 |
} |
| 6 |
|
| 7 |
/** |
| 8 |
* Read-side queries for the logsv2 table feeding the admin Logs page, |
| 9 |
* autocomplete dropdowns, and the daily-activity trend chart. |
| 10 |
* |
| 11 |
* Responsibilities: |
| 12 |
* - getLogRecords: paginated/sortable admin table read with field allow-listing. |
| 13 |
* - getLogsIDandURL / getLogsIDandURLLike: dropdown lookups by URL value. |
| 14 |
* - getDistinctLoggedUrls: distinct URL list for the GSC sitemap probe. |
| 15 |
* - getDailyActivityTrend: cached daily counts (404 vs redirect) for charting. |
| 16 |
* |
| 17 |
* Extracted from LogsRepository under M201. Consumed by the LogsRepository |
| 18 |
* facade; the rollup-derived MAX(logsv2.id) used to invalidate the trend |
| 19 |
* cache is passed in by the facade so test rollups swapped via |
| 20 |
* TrackingLogsRepository::setRollupForTests() propagate naturally. |
| 21 |
*/ |
| 22 |
class ABJ_404_Solution_LogsReadQueries { |
| 23 |
|
| 24 |
/** @var int Max age for cached daily-activity trend data. */ |
| 25 |
const TREND_DATA_CACHE_TTL_SECONDS = 900; |
| 26 |
|
| 27 |
/** |
| 28 |
* Default size of the recency window scanned to find distinct logged URLs. |
| 29 |
* The GSC URL probe only needs recent traffic, so we cap rows read from |
| 30 |
* logsv2 before deduplication. |
| 31 |
*/ |
| 32 |
const DEFAULT_RECENT_LOG_WINDOW = 5000; |
| 33 |
|
| 34 |
/** Hard ceiling on the recency window so a misbehaving caller cannot exhaust memory. */ |
| 35 |
const MAX_RECENT_LOG_WINDOW = 50000; |
| 36 |
|
| 37 |
/** Default cap on the number of distinct URLs returned. */ |
| 38 |
const DEFAULT_DISTINCT_URL_CAP = 500; |
| 39 |
|
| 40 |
/** Hard ceiling on the distinct URL cap. */ |
| 41 |
const MAX_DISTINCT_URL_CAP = 5000; |
| 42 |
|
| 43 |
/** @var ABJ_404_Solution_DatabaseCore */ |
| 44 |
private $dbCore; |
| 45 |
|
| 46 |
/** @var ABJ_404_Solution_Functions */ |
| 47 |
private $f; |
| 48 |
|
| 49 |
/** @var ABJ_404_Solution_Logging */ |
| 50 |
private $logger; |
| 51 |
|
| 52 |
public function __construct( |
| 53 |
ABJ_404_Solution_DatabaseCore $dbCore, |
| 54 |
ABJ_404_Solution_Functions $f, |
| 55 |
$logger |
| 56 |
) { |
| 57 |
$this->dbCore = $dbCore; |
| 58 |
$this->f = $f; |
| 59 |
$this->logger = $logger; |
| 60 |
} |
| 61 |
|
| 62 |
/** |
| 63 |
* Fetch the set of distinct recently-requested URLs from logsv2. |
| 64 |
* |
| 65 |
* Both bounds are caller-supplied so the cap is visible at the |
| 66 |
* repository boundary instead of being hidden inside the SQL file. |
| 67 |
* Values are clamped to [1, MAX_*] to keep this read safe even when |
| 68 |
* callers (or test fixtures) pass garbage. |
| 69 |
* |
| 70 |
* @param int $recentLogWindow Max rows scanned from logsv2 (clamped to [1, MAX_RECENT_LOG_WINDOW]). |
| 71 |
* @param int $distinctUrlCap Max distinct URLs returned (clamped to [1, MAX_DISTINCT_URL_CAP]). |
| 72 |
* @return array<int, string> |
| 73 |
*/ |
| 74 |
public function getDistinctLoggedUrls( |
| 75 |
int $recentLogWindow = self::DEFAULT_RECENT_LOG_WINDOW, |
| 76 |
int $distinctUrlCap = self::DEFAULT_DISTINCT_URL_CAP |
| 77 |
): array { |
| 78 |
$recentLogWindow = max(1, min(self::MAX_RECENT_LOG_WINDOW, $recentLogWindow)); |
| 79 |
$distinctUrlCap = max(1, min(self::MAX_DISTINCT_URL_CAP, $distinctUrlCap)); |
| 80 |
$query = ABJ_404_Solution_FileSystemService::readFileContents(__DIR__ . "/../sql/getDistinctLoggedUrls.sql"); |
| 81 |
$query = $this->f->str_replace('{recent_window}', (string)$recentLogWindow, $query); |
| 82 |
$query = $this->f->str_replace('{distinct_cap}', (string)$distinctUrlCap, $query); |
| 83 |
$results = $this->dbCore->queryAndGetResults($query); |
| 84 |
$rows = is_array($results['rows']) ? $results['rows'] : array(); |
| 85 |
$urls = array(); |
| 86 |
foreach ($rows as $row) { |
| 87 |
$url = isset($row['requested_url']) && is_string($row['requested_url']) ? $row['requested_url'] : ''; |
| 88 |
if ($url !== '') { |
| 89 |
$urls[] = $url; |
| 90 |
} |
| 91 |
} |
| 92 |
return $urls; |
| 93 |
} |
| 94 |
|
| 95 |
/** |
| 96 |
* @param string $specificURL |
| 97 |
* @return array<int, array<string, mixed>> |
| 98 |
*/ |
| 99 |
public function getLogsIDandURL($specificURL = '') { |
| 100 |
$whereClause = ''; |
| 101 |
if ($specificURL != '') { |
| 102 |
$specificURL = $this->f->sanitizeInvalidUTF8($specificURL); |
| 103 |
$escapedURL = esc_sql($specificURL); |
| 104 |
$whereClause = "where requested_url = '" . $escapedURL . "'"; |
| 105 |
} |
| 106 |
$query = ABJ_404_Solution_FileSystemService::readFileContents(__DIR__ . "/../sql/getLogsIDandURL.sql"); |
| 107 |
$query = $this->f->str_replace('{where_clause_here}', $whereClause, $query); |
| 108 |
$results = $this->dbCore->queryAndGetResults($query); |
| 109 |
return is_array($results['rows']) ? $results['rows'] : array(); |
| 110 |
} |
| 111 |
|
| 112 |
/** |
| 113 |
* @param string $specificURL |
| 114 |
* @param string|int $limitResults |
| 115 |
* @return array<int, array<string, mixed>> |
| 116 |
*/ |
| 117 |
public function getLogsIDandURLLike($specificURL, $limitResults) { |
| 118 |
global $wpdb; |
| 119 |
$whereClause = ''; |
| 120 |
if ($specificURL != '') { |
| 121 |
$likePattern = '%' . $wpdb->esc_like($specificURL) . '%'; |
| 122 |
$escapedURL = esc_sql($likePattern); |
| 123 |
$whereClause = "where lower(requested_url) like lower('" . $escapedURL . "')\n"; |
| 124 |
$whereClause .= "and min_log_id = true"; |
| 125 |
} |
| 126 |
$query = ABJ_404_Solution_FileSystemService::readFileContents(__DIR__ . "/../sql/getLogsIDandURLForAjax.sql"); |
| 127 |
$query = $this->f->str_replace('{where_clause_here}', $whereClause, $query); |
| 128 |
$query = $this->f->str_replace('{limit-results}', 'limit ' . absint($limitResults), $query); |
| 129 |
$results = $this->dbCore->queryAndGetResults($query); |
| 130 |
return is_array($results['rows']) ? $results['rows'] : array(); |
| 131 |
} |
| 132 |
|
| 133 |
/** |
| 134 |
* Admin logs page read with allow-listed orderby + pagination. |
| 135 |
* |
| 136 |
* @param array<string, mixed> $tableOptions Boundary-normalized callers pass typed |
| 137 |
* log/page values; this method keeps |
| 138 |
* query-specific allowlists and bounds. |
| 139 |
* @return array<int, array<string, mixed>> |
| 140 |
*/ |
| 141 |
public function getLogRecords($tableOptions) { |
| 142 |
$logsid_included = ''; |
| 143 |
$logsid = ''; |
| 144 |
$logsIdValue = $this->positiveIntOption($tableOptions, 'logsid', 0); |
| 145 |
if ($logsIdValue > 0) { |
| 146 |
$logsid_included = 'specific logs id included. */'; |
| 147 |
$logsid = (string)$logsIdValue; |
| 148 |
} |
| 149 |
$orderbyExpressionByName = array( |
| 150 |
'timestamp' => '{wp_abj404_logsv2}.timestamp', |
| 151 |
'requested_url' => '{wp_abj404_logsv2}.requested_url', |
| 152 |
'url' => 'url', |
| 153 |
'id' => '{wp_abj404_logsv2}.id', |
| 154 |
'min_log_id' => '{wp_abj404_logsv2}.min_log_id', |
| 155 |
); |
| 156 |
$orderby = $this->stringOption($tableOptions, 'orderby', ''); |
| 157 |
$orderby = array_key_exists($orderby, $orderbyExpressionByName) ? $orderby : 'timestamp'; |
| 158 |
$orderbyExpression = $orderbyExpressionByName[$orderby]; |
| 159 |
$order = strtoupper($this->stringOption($tableOptions, 'order', '')); |
| 160 |
if (!in_array($order, array('ASC', 'DESC'), true)) { |
| 161 |
$order = 'DESC'; |
| 162 |
} |
| 163 |
$paged = $this->positiveIntOption($tableOptions, 'paged', 1); |
| 164 |
$perpage = $this->positiveIntOption($tableOptions, 'perpage', ABJ404_OPTION_DEFAULT_PERPAGE); |
| 165 |
$start = ($paged - 1) * $perpage; |
| 166 |
$query = ABJ_404_Solution_FileSystemService::readFileContents(__DIR__ . "/../sql/getLogRecords.sql"); |
| 167 |
$query = $this->f->str_replace('{logsid_included}', $logsid_included, $query); |
| 168 |
$query = $this->f->str_replace('{logsid}', $logsid, $query); |
| 169 |
$query = $this->f->str_replace('{orderby}', $orderbyExpression, $query); |
| 170 |
$query = $this->f->str_replace('{order}', $order, $query); |
| 171 |
$query = $this->f->str_replace('{start}', (string)$start, $query); |
| 172 |
$query = $this->f->str_replace('{perpage}', (string)$perpage, $query); |
| 173 |
$results = $this->dbCore->queryAndGetResults($query); |
| 174 |
$rawRows = $results['rows']; |
| 175 |
return is_array($rawRows) ? $rawRows : array(); |
| 176 |
} |
| 177 |
|
| 178 |
/** |
| 179 |
* @param array<string, mixed> $options |
| 180 |
*/ |
| 181 |
private function positiveIntOption(array $options, string $key, int $default): int { |
| 182 |
$raw = $options[$key] ?? $default; |
| 183 |
if (!is_scalar($raw)) { |
| 184 |
return $default; |
| 185 |
} |
| 186 |
$raw = trim((string)$raw); |
| 187 |
if ($raw === '' || preg_match('/^\d+$/', $raw) !== 1) { |
| 188 |
return $default; |
| 189 |
} |
| 190 |
$value = intval($raw); |
| 191 |
return $value > 0 ? $value : $default; |
| 192 |
} |
| 193 |
|
| 194 |
/** |
| 195 |
* @param array<string, mixed> $options |
| 196 |
*/ |
| 197 |
private function stringOption(array $options, string $key, string $default): string { |
| 198 |
$raw = $options[$key] ?? $default; |
| 199 |
return is_string($raw) ? $raw : $default; |
| 200 |
} |
| 201 |
|
| 202 |
/** |
| 203 |
* Daily activity trend for the dashboard chart. Cached for 15 min keyed |
| 204 |
* on (blog id, day count, current MAX logsv2 id) so the cache invalidates |
| 205 |
* naturally as new rows arrive. |
| 206 |
* |
| 207 |
* @param int $days Number of days (clamped to 1-90) |
| 208 |
* @param ABJ_404_Solution_LogsRepositoryInterface $repo Source of the cache-busting MAX(logsv2.id) read; passing the facade (rather than the raw rollup) ensures subclass overrides propagate. |
| 209 |
* @return array<int, array<string, mixed>> |
| 210 |
*/ |
| 211 |
public function getDailyActivityTrend(int $days, ABJ_404_Solution_LogsRepositoryInterface $repo): array { |
| 212 |
$days = max(1, min(90, $days)); |
| 213 |
$blogId = 1; |
| 214 |
if (function_exists('get_current_blog_id')) { |
| 215 |
$blogId = function_exists('absint') ? absint(get_current_blog_id()) : abs(intval(get_current_blog_id())); |
| 216 |
if ($blogId <= 0) { $blogId = 1; } |
| 217 |
} |
| 218 |
$maxLogId = 0; |
| 219 |
try { |
| 220 |
$maxLogId = intval($repo->getMaxLogId()); |
| 221 |
if ($maxLogId < 0) { $maxLogId = 0; } |
| 222 |
} catch (Throwable $e) { |
| 223 |
$this->logger->debugMessage(__FUNCTION__ . ' getMaxLogId() failed: ' . $e->getMessage() . '. Falling back to maxLogId=0 (cache key uses 0).'); |
| 224 |
$maxLogId = 0; |
| 225 |
} |
| 226 |
$cacheKey = 'abj404_trend_v2_' . $blogId . '_' . $days . '_' . $maxLogId; |
| 227 |
if (function_exists('get_transient')) { $cached = get_transient($cacheKey); if (is_array($cached)) { return $cached; } } |
| 228 |
$logsTable = $this->dbCore->doTableNameReplacements('{wp_abj404_logsv2}'); |
| 229 |
$now = abj_clock()->now(); |
| 230 |
$cutoff = $now - ($days * 86400); |
| 231 |
$notFoundDest = '404'; |
| 232 |
$query = "SELECT FLOOR(`timestamp` / 86400) AS `day_index`, SUM(CASE WHEN `dest_url` = %s THEN 1 ELSE 0 END) AS `hits_404`, SUM(CASE WHEN `dest_url` <> %s THEN 1 ELSE 0 END) AS `hits_redirect` FROM " . $logsTable . " WHERE `timestamp` >= " . intval($cutoff) . " GROUP BY FLOOR(`timestamp` / 86400) ORDER BY `day_index` ASC"; |
| 233 |
$result = $this->dbCore->queryAndGetResults($query, array('query_params' => array($notFoundDest, $notFoundDest))); |
| 234 |
$hadError = !empty($result['timed_out']) || (isset($result['last_error']) && $result['last_error'] !== ''); |
| 235 |
$rows = (isset($result['rows']) && is_array($result['rows'])) ? $result['rows'] : array(); |
| 236 |
$byDayIndex = array(); |
| 237 |
foreach ($rows as $row) { |
| 238 |
if (!is_array($row)) { continue; } |
| 239 |
if (!isset($row['day_index']) || !is_numeric($row['day_index'])) { continue; } |
| 240 |
$dayIndex = intval($row['day_index']); |
| 241 |
$byDayIndex[$dayIndex] = array('hits_404' => intval($row['hits_404'] ?? 0), 'hits_redirect' => intval($row['hits_redirect'] ?? 0), 'new_captures' => intval($row['hits_404'] ?? 0)); |
| 242 |
} |
| 243 |
$output = array(); |
| 244 |
for ($i = $days - 1; $i >= 0; $i--) { |
| 245 |
$dayIndex = intdiv($now - ($i * 86400), 86400); |
| 246 |
$date = gmdate('Y-m-d', $dayIndex * 86400); |
| 247 |
$counts = $byDayIndex[$dayIndex] ?? array('hits_404' => 0, 'hits_redirect' => 0, 'new_captures' => 0); |
| 248 |
$output[] = array('date' => $date) + $counts; |
| 249 |
} |
| 250 |
if (!$hadError && function_exists('set_transient')) { set_transient($cacheKey, $output, self::TREND_DATA_CACHE_TTL_SECONDS); } |
| 251 |
return $output; |
| 252 |
} |
| 253 |
} |
| 254 |
|
