| @@ -1,5 +1,6 @@ | ||
| 1 | 1 | <?php |
| 2 | + | |
| 2 | 3 | /** |
| 3 | 4 | * Database Schema Manager Class |
| 4 | 5 | * |
| 5 | 6 | * Comprehensive database schema implementation for ThinkRank SEO plugin. |
| @@ -18,8 +19,13 @@ | ||
| 18 | 19 | declare(strict_types=1); |
| 19 | 20 | |
| 20 | 21 | namespace ThinkRank\Database; |
| 21 | 22 | |
| 23 | +// Prevent direct access | |
| 24 | +if (!defined('ABSPATH')) { | |
| 25 | + exit; | |
| 26 | +} | |
| 27 | + | |
| 22 | 28 | /** |
| 23 | 29 | * Database Schema Manager Class |
| 24 | 30 | * |
| 25 | 31 | * Handles creation, management, and optimization of all SEO database tables. |
| @@ -42,11 +48,84 @@ | ||
| 42 | 48 | * |
| 43 | 49 | * @since 1.0.0 |
| 44 | 50 | * @var string |
| 45 | 51 | */ |
| 46 | - private string $db_version = '1.0.0'; | |
| 52 | + private string $db_version = '1.9.2'; | |
| 47 | 53 | |
| 48 | 54 | /** |
| 55 | + * Widest single indexed COLUMN InnoDB accepts on a COMPACT/REDUNDANT row | |
| 56 | + * format, in bytes. | |
| 57 | + * | |
| 58 | + * The limit is per column, not per key: a key may total well over this as | |
| 59 | + * long as no one column contributes more than 767 bytes. MySQL 5.7+ and | |
| 60 | + * MariaDB 10.2+ default to DYNAMIC and raise it to 3072, but MySQL 5.6-era | |
| 61 | + * servers (and anything with innodb_large_prefix off) enforce 767 — and on | |
| 62 | + * a UNIQUE key it is fatal, because uniqueness cannot be guaranteed from a | |
| 63 | + * truncated prefix, so the whole CREATE TABLE is rejected and the table | |
| 64 | + * never exists (#298). A non-unique key is silently truncated instead. | |
| 65 | + * | |
| 66 | + * utf8mb4 costs 4 bytes per character, so varchar(191) = 764 bytes is the | |
| 67 | + * widest column that fits — the same reason WordPress core uses 191. | |
| 68 | + * | |
| 69 | + * @since 1.30.0 | |
| 70 | + * @var int | |
| 71 | + */ | |
| 72 | + private const MAX_INDEX_COLUMN_BYTES = 767; | |
| 73 | + | |
| 74 | + /** | |
| 75 | + * Wide keys replaced by prefixed equivalents, as new name => legacy name. | |
| 76 | + * | |
| 77 | + * dbDelta never drops or rewrites an existing index, so installs created | |
| 78 | + * before #298 keep their full-width key. The prefixed key is added under a | |
| 79 | + * new name (dbDelta only adds what is absent) and the legacy one is dropped | |
| 80 | + * here once its replacement is confirmed present — never before, so a | |
| 81 | + * failed ALTER leaves the table exactly as it was. | |
| 82 | + * | |
| 83 | + * @since 1.30.0 | |
| 84 | + * @var array<string, array<string, string>> | |
| 85 | + */ | |
| 86 | + private const REPLACED_WIDE_INDEXES = [ | |
| 87 | + 'seo_settings' => ['unique_setting_v2' => 'unique_setting'], | |
| 88 | + 'seo_social' => ['unique_social_meta_v2' => 'unique_social_meta'], | |
| 89 | + 'ai_cache' => ['unique_cache_key' => 'cache_key'], | |
| 90 | + ]; | |
| 91 | + | |
| 92 | + /** | |
| 93 | + * Transient caching a verified-complete schema, so the missing-table probe | |
| 94 | + * costs one query per hour on a healthy site rather than one per request. | |
| 95 | + * | |
| 96 | + * @since 1.28.0 | |
| 97 | + * @var string | |
| 98 | + */ | |
| 99 | + private const TABLES_VERIFIED_TRANSIENT = 'thinkrank_schema_verified'; | |
| 100 | + | |
| 101 | + /** | |
| 102 | + * Option holding why the last create_tables() run left a table missing. | |
| 103 | + * | |
| 104 | + * A rejected CREATE TABLE is the one schema failure the plugin cannot | |
| 105 | + * recover from on its own: needs_update() re-runs creation on every request | |
| 106 | + * precisely because a missing table is normally self-healing, so a database | |
| 107 | + * that refuses the statement loops silently forever while every settings | |
| 108 | + * screen fails. dbDelta swallows the error, so capture it here — it names | |
| 109 | + * the cause (denied CREATE privilege, unsupported collation, index width) | |
| 110 | + * that nothing else on the site reports. | |
| 111 | + * | |
| 112 | + * @since 1.32.1 | |
| 113 | + * @var string | |
| 114 | + */ | |
| 115 | + private const CREATE_FAILURE_OPTION = 'thinkrank_schema_create_error'; | |
| 116 | + | |
| 117 | + /** | |
| 118 | + * Throttle for the create-failure log line. Creation is re-attempted on | |
| 119 | + * every request while a table is missing, and one log entry per request | |
| 120 | + * per table would bury the error it is meant to surface. | |
| 121 | + * | |
| 122 | + * @since 1.32.1 | |
| 123 | + * @var string | |
| 124 | + */ | |
| 125 | + private const CREATE_FAILURE_LOGGED_TRANSIENT = 'thinkrank_schema_create_error_logged'; | |
| 126 | + | |
| 127 | + /** | |
| 49 | 128 | * Database table definitions with specifications |
| 50 | 129 | * |
| 51 | 130 | * Consolidated table definitions for all ThinkRank tables (11 total): |
| 52 | 131 | * - SEO Tables (7): Core SEO functionality with context-aware structure |
| @@ -109,9 +188,11 @@ | ||
| 109 | 188 | 'description' => 'AI response caching for performance optimization', |
| 110 | 189 | 'primary_key' => 'id', |
| 111 | 190 | 'indexes' => ['cache_key', 'expires_at', 'created_at'], |
| 112 | 191 | 'composite_indexes' => [ |
| 113 | - 'cache_lookup' => ['cache_key', 'expires_at'], | |
| 192 | + // cache_key is varchar(255) — 1020 bytes in utf8mb4, so it is | |
| 193 | + // prefixed here for the same reason as the unique key (#298). | |
| 194 | + 'cache_lookup' => ['cache_key(191)', 'expires_at'], | |
| 114 | 195 | 'cleanup_expired' => ['expires_at', 'created_at'] |
| 115 | 196 | ], |
| 116 | 197 | 'foreign_keys' => [] |
| 117 | 198 | ], |
| @@ -145,8 +226,32 @@ | ||
| 145 | 226 | 'user_score_date' => ['user_id', 'overall_score', 'created_at'], |
| 146 | 227 | 'post_latest' => ['post_id', 'calculated_at'] |
| 147 | 228 | ], |
| 148 | 229 | 'foreign_keys' => [] |
| 230 | + ], | |
| 231 | + 'instant_indexing_logs' => [ | |
| 232 | + 'description' => 'Log of IndexNow URL submissions', | |
| 233 | + 'primary_key' => 'id', | |
| 234 | + 'indexes' => ['url', 'status', 'response_code', 'created_at'], | |
| 235 | + 'foreign_keys' => [] | |
| 236 | + ], | |
| 237 | + 'email_report_logs' => [ | |
| 238 | + 'description' => 'Audit + dedupe log for scheduled SEO email reports', | |
| 239 | + 'primary_key' => 'id', | |
| 240 | + 'indexes' => ['site_id', 'status', 'sent_at', 'period_start'], | |
| 241 | + 'composite_indexes' => [ | |
| 242 | + 'dedupe_key' => ['site_id', 'period_start', 'recipient_hash'], | |
| 243 | + 'site_recent' => ['site_id', 'sent_at'] | |
| 244 | + ], | |
| 245 | + 'foreign_keys' => [] | |
| 246 | + ], | |
| 247 | + | |
| 248 | + // === AI VISIBILITY TABLES (1) === | |
| 249 | + 'ai_traffic' => [ | |
| 250 | + 'description' => 'Daily aggregate counters for AI referral traffic, AI crawler hits, and the all-traffic baseline', | |
| 251 | + 'primary_key' => 'id', | |
| 252 | + 'indexes' => ['day', 'kind'], | |
| 253 | + 'foreign_keys' => [] | |
| 149 | 254 | ] |
| 150 | 255 | ]; |
| 151 | 256 | |
| 152 | 257 | /** |
| @@ -163,12 +268,14 @@ | ||
| 163 | 268 | * @since 1.0.0 |
| 164 | 269 | * @var array |
| 165 | 270 | */ |
| 166 | 271 | private array $table_categories = [ |
| 167 | - 'seo' => ['seo_settings', 'seo_analysis', 'seo_keywords', 'seo_schema', 'seo_social', 'seo_performance', 'seo_local'], | |
| 272 | + 'seo' => ['seo_settings', 'seo_analysis', 'seo_keywords', 'seo_schema', 'seo_social', 'seo_performance', 'seo_local', 'instant_indexing_logs'], | |
| 168 | 273 | 'ai' => ['ai_cache', 'ai_usage'], |
| 169 | 274 | 'content' => ['content_briefs'], |
| 170 | - 'scoring' => ['seo_scores'] | |
| 275 | + 'scoring' => ['seo_scores'], | |
| 276 | + 'reporting' => ['email_report_logs'], | |
| 277 | + 'ai_visibility' => ['ai_traffic'] | |
| 171 | 278 | ]; |
| 172 | 279 | |
| 173 | 280 | /** |
| 174 | 281 | * Constructor |
| @@ -177,14 +284,18 @@ | ||
| 177 | 284 | */ |
| 178 | 285 | public function __construct() { |
| 179 | 286 | global $wpdb; |
| 180 | 287 | $this->wpdb = $wpdb; |
| 181 | - | |
| 288 | + | |
| 182 | 289 | // Set database configuration |
| 183 | - $this->db_config = [ | |
| 184 | - 'charset' => $wpdb->charset ?: 'utf8mb4', | |
| 185 | - 'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci' | |
| 186 | - ]; | |
| 290 | + // Ask WordPress for the clause rather than assembling one. Charset and | |
| 291 | + // collation are not independent: DB_COLLATE is empty on most installs, | |
| 292 | + // so a per-value fallback pairs the site's real charset with a default | |
| 293 | + // collation that may not belong to it — `CHARACTER SET utf8 COLLATE | |
| 294 | + // utf8mb4_unicode_ci` is rejected outright (MySQL 1253), and dbDelta | |
| 295 | + // reports nothing, so every table silently fails to be created. | |
| 296 | + // get_charset_collate() omits COLLATE when there is none to state. | |
| 297 | + $this->db_config = ['charset_collate' => $wpdb->get_charset_collate()]; | |
| 187 | 298 | } |
| 188 | 299 | |
| 189 | 300 | /** |
| 190 | 301 | * Create all database tables |
| @@ -210,24 +321,31 @@ | ||
| 210 | 321 | foreach ($this->table_definitions as $table_name => $definition) { |
| 211 | 322 | try { |
| 212 | 323 | $full_table_name = $this->get_table_name($table_name); |
| 213 | 324 | $sql = $this->get_table_sql($table_name); |
| 214 | - | |
| 325 | + | |
| 215 | 326 | // Create table using dbDelta for WordPress compatibility |
| 216 | 327 | $result = dbDelta($sql); |
| 217 | - | |
| 328 | + | |
| 329 | + // dbDelta reports nothing when the database refuses the | |
| 330 | + // statement, so wpdb's own error is the only account of why. | |
| 331 | + // Read it now: table_exists() runs a query of its own, and | |
| 332 | + // every wpdb query starts by clearing last_error. | |
| 333 | + $db_error = (string) $this->wpdb->last_error; | |
| 334 | + | |
| 218 | 335 | // Verify table creation |
| 219 | 336 | if ($this->table_exists($full_table_name)) { |
| 220 | 337 | $results['tables_created'][] = $full_table_name; |
| 221 | - | |
| 338 | + | |
| 222 | 339 | // Create indexes |
| 223 | 340 | $this->create_table_indexes($table_name); |
| 224 | - | |
| 341 | + | |
| 225 | 342 | // Add constraints if needed |
| 226 | 343 | $this->add_table_constraints($table_name); |
| 227 | 344 | } else { |
| 228 | 345 | $results['tables_failed'][] = $full_table_name; |
| 229 | - $results['errors'][] = "Failed to create table: {$full_table_name}"; | |
| 346 | + $results['errors'][] = "Failed to create table: {$full_table_name}" | |
| 347 | + . ('' !== $db_error ? ' — ' . $db_error : ''); | |
| 230 | 348 | $results['success'] = false; |
| 231 | 349 | } |
| 232 | 350 | } catch (\Exception $e) { |
| 233 | 351 | $results['tables_failed'][] = $this->get_table_name($table_name); |
| @@ -235,18 +353,76 @@ | ||
| 235 | 353 | $results['success'] = false; |
| 236 | 354 | } |
| 237 | 355 | } |
| 238 | 356 | |
| 357 | + // Retire the pre-#298 full-width keys now that their prefixed | |
| 358 | + // replacements are in place. | |
| 359 | + $this->drop_replaced_wide_indexes(); | |
| 360 | + | |
| 361 | + // Evict REST envelope keys that earlier saves stored as settings. | |
| 362 | + $this->purge_envelope_setting_rows(); | |
| 363 | + | |
| 364 | + // And every other key no manager declares, stored the same way. | |
| 365 | + $this->purge_unknown_setting_rows(); | |
| 366 | + | |
| 239 | 367 | // Update database version |
| 240 | 368 | if ($results['success']) { |
| 241 | 369 | update_option('thinkrank_seo_db_version', $this->db_version); |
| 242 | 370 | update_option('thinkrank_seo_db_created', current_time('mysql')); |
| 371 | + delete_option(self::CREATE_FAILURE_OPTION); | |
| 372 | + } else { | |
| 373 | + $this->record_create_failure($results['errors']); | |
| 243 | 374 | } |
| 244 | 375 | |
| 376 | + // The schema just changed, so any cached "verified complete" answer is | |
| 377 | + // stale either way — drop it and let the next probe re-check. | |
| 378 | + delete_transient(self::TABLES_VERIFIED_TRANSIENT); | |
| 379 | + | |
| 245 | 380 | return $results; |
| 246 | 381 | } |
| 247 | 382 | |
| 248 | 383 | /** |
| 384 | + * Keep the reason a table could not be created, and say it out loud once. | |
| 385 | + * | |
| 386 | + * @since 1.32.1 | |
| 387 | + * | |
| 388 | + * @param string[] $errors Failure messages from create_tables(). | |
| 389 | + * @return void | |
| 390 | + */ | |
| 391 | + private function record_create_failure(array $errors): void { | |
| 392 | + $reason = implode('; ', array_filter($errors)); | |
| 393 | + | |
| 394 | + if ('' === $reason) { | |
| 395 | + return; | |
| 396 | + } | |
| 397 | + | |
| 398 | + update_option(self::CREATE_FAILURE_OPTION, $reason, false); | |
| 399 | + | |
| 400 | + if (get_transient(self::CREATE_FAILURE_LOGGED_TRANSIENT)) { | |
| 401 | + return; | |
| 402 | + } | |
| 403 | + | |
| 404 | + set_transient(self::CREATE_FAILURE_LOGGED_TRANSIENT, 1, HOUR_IN_SECONDS); | |
| 405 | + | |
| 406 | + // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log -- deliberate diagnostic; the UI can only report that a table is missing, never why. | |
| 407 | + error_log('ThinkRank [schema]: table creation failed — ' . $reason); | |
| 408 | + } | |
| 409 | + | |
| 410 | + /** | |
| 411 | + * Why the last table creation attempt failed, if it did. | |
| 412 | + * | |
| 413 | + * Read by the SEO managers so a "settings table does not exist" message can | |
| 414 | + * name the database error behind it instead of guessing at causes. | |
| 415 | + * | |
| 416 | + * @since 1.32.1 | |
| 417 | + * | |
| 418 | + * @return string Failure reason, or '' if creation last succeeded. | |
| 419 | + */ | |
| 420 | + public static function get_last_create_failure(): string { | |
| 421 | + return (string) get_option(self::CREATE_FAILURE_OPTION, ''); | |
| 422 | + } | |
| 423 | + | |
| 424 | + /** | |
| 249 | 425 | * Drop all database tables |
| 250 | 426 | * |
| 251 | 427 | * @since 1.0.0 |
| 252 | 428 | * |
| @@ -262,12 +438,12 @@ | ||
| 262 | 438 | |
| 263 | 439 | foreach (array_keys($this->table_definitions) as $table_name) { |
| 264 | 440 | try { |
| 265 | 441 | $full_table_name = $this->get_table_name($table_name); |
| 266 | - | |
| 267 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Plugin deactivation requires direct schema changes, DDL cannot be prepared, table name is validated | |
| 442 | + | |
| 443 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Plugin deactivation requires direct schema changes, DDL cannot be prepared, table name is validated | |
| 268 | 444 | $result = $this->wpdb->query("DROP TABLE IF EXISTS `{$full_table_name}`"); |
| 269 | - | |
| 445 | + | |
| 270 | 446 | if ($result !== false) { |
| 271 | 447 | $results['tables_dropped'][] = $full_table_name; |
| 272 | 448 | } else { |
| 273 | 449 | $results['tables_failed'][] = $full_table_name; |
| @@ -298,12 +474,78 @@ | ||
| 298 | 474 | * @return bool True if update needed |
| 299 | 475 | */ |
| 300 | 476 | public function needs_update(): bool { |
| 301 | 477 | $current_version = get_option('thinkrank_seo_db_version', '0.0.0'); |
| 302 | - return version_compare($current_version, $this->db_version, '<'); | |
| 478 | + | |
| 479 | + if (version_compare($current_version, $this->db_version, '<')) { | |
| 480 | + return true; | |
| 481 | + } | |
| 482 | + | |
| 483 | + // Version-only gating has now failed twice (#252, #270): if tables are | |
| 484 | + // added but the version isn't moved — or a table is dropped, or an | |
| 485 | + // upgrade half-completes — the stored version matches, the gate says | |
| 486 | + // "nothing to do", and the feature is dead with no way back except | |
| 487 | + // deactivate/reactivate. So also heal when a registered table is | |
| 488 | + // actually missing. dbDelta only creates what's absent, making this | |
| 489 | + // safe to re-run. | |
| 490 | + return !empty($this->missing_tables()); | |
| 303 | 491 | } |
| 304 | 492 | |
| 305 | 493 | /** |
| 494 | + * Registered tables that don't exist in the database. | |
| 495 | + * | |
| 496 | + * One `SHOW TABLES LIKE` for all of them, and the healthy answer is cached | |
| 497 | + * so a correct install pays at most one extra query per hour rather than | |
| 498 | + * one per request. The cache is cleared whenever tables are created. | |
| 499 | + * | |
| 500 | + * @since 1.28.0 | |
| 501 | + * | |
| 502 | + * @return string[] Missing table names (full, prefixed). | |
| 503 | + */ | |
| 504 | + public function missing_tables(): array { | |
| 505 | + $cached = get_transient(self::TABLES_VERIFIED_TRANSIENT); | |
| 506 | + if ($cached === $this->db_version) { | |
| 507 | + return []; | |
| 508 | + } | |
| 509 | + | |
| 510 | + $expected = []; | |
| 511 | + foreach (array_keys($this->table_definitions) as $table) { | |
| 512 | + $expected[] = $this->get_table_name($table); | |
| 513 | + } | |
| 514 | + | |
| 515 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema probe; result cached below. | |
| 516 | + $existing = (array) $this->wpdb->get_col( | |
| 517 | + // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement. | |
| 518 | + $this->wpdb->prepare( | |
| 519 | + 'SHOW TABLES LIKE %s', | |
| 520 | + // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- table name is $wpdb->prefix plus a literal, and every value is passed as a placeholder replacement. | |
| 521 | + $this->wpdb->esc_like($this->wpdb->prefix . 'thinkrank_') . '%' | |
| 522 | + ) | |
| 523 | + ); | |
| 524 | + // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared | |
| 525 | + // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared | |
| 526 | + | |
| 527 | + $missing = array_values(array_diff($expected, $existing)); | |
| 528 | + | |
| 529 | + if (empty($missing)) { | |
| 530 | + set_transient(self::TABLES_VERIFIED_TRANSIENT, $this->db_version, HOUR_IN_SECONDS); | |
| 531 | + } | |
| 532 | + | |
| 533 | + return $missing; | |
| 534 | + } | |
| 535 | + | |
| 536 | + /** | |
| 537 | + * The schema version this plugin build expects (the target of needs_update). | |
| 538 | + * | |
| 539 | + * @since 1.23.0 | |
| 540 | + * | |
| 541 | + * @return string Expected schema version, e.g. "1.2.0". | |
| 542 | + */ | |
| 543 | + public function get_schema_version(): string { | |
| 544 | + return $this->db_version; | |
| 545 | + } | |
| 546 | + | |
| 547 | + /** | |
| 306 | 548 | * Get database status and information |
| 307 | 549 | * |
| 308 | 550 | * @since 1.0.0 |
| 309 | 551 | * |
| @@ -321,9 +563,9 @@ | ||
| 321 | 563 | |
| 322 | 564 | foreach (array_keys($this->table_definitions) as $table_name) { |
| 323 | 565 | $full_table_name = $this->get_table_name($table_name); |
| 324 | 566 | $table_info = $this->get_table_info($full_table_name); |
| 325 | - | |
| 567 | + | |
| 326 | 568 | $status['tables'][$table_name] = $table_info; |
| 327 | 569 | $status['total_records'] += $table_info['row_count']; |
| 328 | 570 | $status['database_size'] += $table_info['data_size']; |
| 329 | 571 | } |
| @@ -349,20 +591,20 @@ | ||
| 349 | 591 | |
| 350 | 592 | foreach (array_keys($this->table_definitions) as $table_name) { |
| 351 | 593 | try { |
| 352 | 594 | $full_table_name = $this->get_table_name($table_name); |
| 353 | - | |
| 595 | + | |
| 354 | 596 | // Get table size before optimization |
| 355 | 597 | $size_before = $this->get_table_size($full_table_name); |
| 356 | - | |
| 598 | + | |
| 357 | 599 | // Optimize table |
| 358 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table optimization requires direct database access, DDL cannot be prepared, table name is validated | |
| 600 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table optimization requires direct database access, DDL cannot be prepared, table name is validated | |
| 359 | 601 | $result = $this->wpdb->query("OPTIMIZE TABLE `{$full_table_name}`"); |
| 360 | - | |
| 602 | + | |
| 361 | 603 | if ($result !== false) { |
| 362 | 604 | $size_after = $this->get_table_size($full_table_name); |
| 363 | 605 | $space_saved = $size_before - $size_after; |
| 364 | - | |
| 606 | + | |
| 365 | 607 | $results['tables_optimized'][] = [ |
| 366 | 608 | 'table' => $full_table_name, |
| 367 | 609 | 'space_saved' => $space_saved |
| 368 | 610 | ]; |
| @@ -402,11 +644,11 @@ | ||
| 402 | 644 | * @param string $table_name Full table name |
| 403 | 645 | * @return bool True if table exists |
| 404 | 646 | */ |
| 405 | 647 | private function table_exists(string $table_name): bool { |
| 406 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema validation requires direct database access | |
| 648 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema validation requires direct database access | |
| 407 | 649 | $result = $this->wpdb->get_var( |
| 408 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders | |
| 650 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders | |
| 409 | 651 | $this->wpdb->prepare("SHOW TABLES LIKE %s", $table_name) |
| 410 | 652 | ); |
| 411 | 653 | |
| 412 | 654 | return $result === $table_name; |
| @@ -418,13 +660,21 @@ | ||
| 418 | 660 | * @since 1.0.0 |
| 419 | 661 | * |
| 420 | 662 | * @param string $table_name Table name |
| 421 | 663 | * @return string SQL for table creation |
| 664 | + * | |
| 665 | + * @throws \InvalidArgumentException On failure. | |
| 422 | 666 | */ |
| 423 | 667 | private function get_table_sql(string $table_name): string { |
| 424 | 668 | $full_table_name = $this->get_table_name($table_name); |
| 425 | - $charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}"; | |
| 426 | 669 | |
| 670 | + // Pin the engine instead of inheriting the server's | |
| 671 | + // default_storage_engine. The indexes below assume InnoDB: MyISAM caps | |
| 672 | + // a key at 1000 bytes (seo_settings and seo_social exceed it) and | |
| 673 | + // rejects descending indexes (seo_schema), so on a server defaulting | |
| 674 | + // to MyISAM those tables were never created (#725). | |
| 675 | + $charset_collate = trim('ENGINE=InnoDB ' . $this->db_config['charset_collate']); | |
| 676 | + | |
| 427 | 677 | switch ($table_name) { |
| 428 | 678 | // SEO Tables |
| 429 | 679 | case 'seo_settings': |
| 430 | 680 | return $this->get_seo_settings_table_sql($full_table_name, $charset_collate); |
| @@ -440,9 +690,9 @@ | ||
| 440 | 690 | return $this->get_seo_performance_table_sql($full_table_name, $charset_collate); |
| 441 | 691 | case 'seo_local': |
| 442 | 692 | return $this->get_seo_local_table_sql($full_table_name, $charset_collate); |
| 443 | 693 | |
| 444 | - // AI/Core Tables | |
| 694 | + // AI/Core Tables | |
| 445 | 695 | case 'ai_cache': |
| 446 | 696 | return $this->get_ai_cache_table_sql($full_table_name, $charset_collate); |
| 447 | 697 | case 'ai_usage': |
| 448 | 698 | return $this->get_ai_usage_table_sql($full_table_name, $charset_collate); |
| @@ -449,9 +699,17 @@ | ||
| 449 | 699 | case 'content_briefs': |
| 450 | 700 | return $this->get_content_briefs_table_sql($full_table_name, $charset_collate); |
| 451 | 701 | case 'seo_scores': |
| 452 | 702 | return $this->get_seo_scores_table_sql($full_table_name, $charset_collate); |
| 703 | + case 'instant_indexing_logs': | |
| 704 | + return $this->get_instant_indexing_logs_table_sql($full_table_name, $charset_collate); | |
| 705 | + case 'email_report_logs': | |
| 706 | + return $this->get_email_report_logs_table_sql($full_table_name, $charset_collate); | |
| 453 | 707 | |
| 708 | + // AI Visibility Tables | |
| 709 | + case 'ai_traffic': | |
| 710 | + return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate); | |
| 711 | + | |
| 454 | 712 | default: |
| 455 | 713 | throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name)); |
| 456 | 714 | } |
| 457 | 715 | } |
| @@ -480,9 +738,9 @@ | ||
| 480 | 738 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 481 | 739 | created_by bigint(20) unsigned NULL, |
| 482 | 740 | updated_by bigint(20) unsigned NULL, |
| 483 | 741 | PRIMARY KEY (setting_id), |
| 484 | - UNIQUE KEY unique_setting (context_type, context_id, setting_category, setting_key), | |
| 742 | + UNIQUE KEY unique_setting_v2 (context_type, context_id, setting_category, setting_key(191)), | |
| 485 | 743 | KEY idx_context (context_type, context_id), |
| 486 | 744 | KEY idx_category (setting_category), |
| 487 | 745 | KEY idx_active (is_active), |
| 488 | 746 | KEY idx_created (created_at), |
| @@ -625,9 +883,9 @@ | ||
| 625 | 883 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 626 | 884 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 627 | 885 | created_by bigint(20) unsigned NULL, |
| 628 | 886 | PRIMARY KEY (social_id), |
| 629 | - UNIQUE KEY unique_social_meta (context_type, context_id, platform, meta_key), | |
| 887 | + UNIQUE KEY unique_social_meta_v2 (context_type, context_id, platform, meta_key(191)), | |
| 630 | 888 | KEY idx_context (context_type, context_id), |
| 631 | 889 | KEY idx_platform (platform), |
| 632 | 890 | KEY idx_type (meta_type), |
| 633 | 891 | KEY idx_optimized (is_optimized), |
| @@ -733,9 +991,9 @@ | ||
| 733 | 991 | cache_data longtext NOT NULL, |
| 734 | 992 | expires_at bigint(20) unsigned NOT NULL, |
| 735 | 993 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 736 | 994 | PRIMARY KEY (id), |
| 737 | - UNIQUE KEY cache_key (cache_key), | |
| 995 | + UNIQUE KEY unique_cache_key (cache_key(191)), | |
| 738 | 996 | KEY expires_at_idx (expires_at), |
| 739 | 997 | KEY created_at_idx (created_at) |
| 740 | 998 | ) {$charset_collate};"; |
| 741 | 999 | } |
| @@ -795,8 +1053,33 @@ | ||
| 795 | 1053 | ) {$charset_collate};"; |
| 796 | 1054 | } |
| 797 | 1055 | |
| 798 | 1056 | /** |
| 1057 | + * Get SQL for Instant Indexing Logs table | |
| 1058 | + * | |
| 1059 | + * @since 1.0.0 | |
| 1060 | + * | |
| 1061 | + * @param string $table_name Full table name | |
| 1062 | + * @param string $charset_collate Charset and collation | |
| 1063 | + * @return string SQL for table creation | |
| 1064 | + */ | |
| 1065 | + private function get_instant_indexing_logs_table_sql(string $table_name, string $charset_collate): string { | |
| 1066 | + return "CREATE TABLE `{$table_name}` ( | |
| 1067 | + id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1068 | + url varchar(2048) NOT NULL, | |
| 1069 | + status varchar(50) NOT NULL, | |
| 1070 | + response_code int(11) NULL, | |
| 1071 | + response_message text NULL, | |
| 1072 | + created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| 1073 | + PRIMARY KEY (id), | |
| 1074 | + KEY idx_url (url(191)), | |
| 1075 | + KEY idx_status (status), | |
| 1076 | + KEY idx_response_code (response_code), | |
| 1077 | + KEY idx_created (created_at) | |
| 1078 | + ) {$charset_collate};"; | |
| 1079 | + } | |
| 1080 | + | |
| 1081 | + /** | |
| 799 | 1082 | * Get SQL for SEO Scores table |
| 800 | 1083 | * |
| 801 | 1084 | * @since 1.0.0 |
| 802 | 1085 | * |
| @@ -812,8 +1095,10 @@ | ||
| 812 | 1095 | overall_score int(11) NOT NULL, |
| 813 | 1096 | score_breakdown longtext NOT NULL, |
| 814 | 1097 | suggestions longtext NOT NULL, |
| 815 | 1098 | grade varchar(2) NOT NULL, |
| 1099 | + readability_score varchar(100) DEFAULT NULL, | |
| 1100 | + content_quality varchar(100) DEFAULT NULL, | |
| 816 | 1101 | algorithm_version varchar(20) NOT NULL DEFAULT '2024.1', |
| 817 | 1102 | calculated_at datetime NOT NULL, |
| 818 | 1103 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 819 | 1104 | PRIMARY KEY (id), |
| @@ -824,8 +1109,48 @@ | ||
| 824 | 1109 | ) {$charset_collate};"; |
| 825 | 1110 | } |
| 826 | 1111 | |
| 827 | 1112 | /** |
| 1113 | + * Get SQL for Email Report Logs table. | |
| 1114 | + * | |
| 1115 | + * Audit + dedupe log for scheduled SEO email reports. The | |
| 1116 | + * `unique_send` constraint on (site_id, period_start, recipient_hash) | |
| 1117 | + * is what prevents a given site from being sent the same period twice | |
| 1118 | + * to the same recipient — required by the PRD. | |
| 1119 | + * | |
| 1120 | + * `recipient_hash` is a sha256 of the lowercased, sorted recipient list | |
| 1121 | + * (so [a@x, b@x] and [b@x, a@x] dedupe to the same row). | |
| 1122 | + * | |
| 1123 | + * @since 1.9.0 | |
| 1124 | + * | |
| 1125 | + * @param string $table_name Full table name. | |
| 1126 | + * @param string $charset_collate Charset and collation. | |
| 1127 | + * @return string SQL for table creation. | |
| 1128 | + */ | |
| 1129 | + private function get_email_report_logs_table_sql(string $table_name, string $charset_collate): string { | |
| 1130 | + return "CREATE TABLE `{$table_name}` ( | |
| 1131 | + id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1132 | + site_id bigint(20) unsigned NOT NULL DEFAULT 0, | |
| 1133 | + period_start datetime NOT NULL, | |
| 1134 | + period_end datetime NOT NULL, | |
| 1135 | + recipient_hash char(64) NOT NULL, | |
| 1136 | + recipient_count smallint(5) unsigned NOT NULL DEFAULT 1, | |
| 1137 | + frequency_days smallint(5) unsigned NOT NULL DEFAULT 30, | |
| 1138 | + status varchar(20) NOT NULL DEFAULT 'pending', | |
| 1139 | + attempts smallint(5) unsigned NOT NULL DEFAULT 1, | |
| 1140 | + error_message text NULL, | |
| 1141 | + sent_at datetime NULL, | |
| 1142 | + created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
| 1143 | + PRIMARY KEY (id), | |
| 1144 | + UNIQUE KEY unique_send (site_id, period_start, recipient_hash), | |
| 1145 | + KEY idx_site (site_id), | |
| 1146 | + KEY idx_status (status), | |
| 1147 | + KEY idx_sent (sent_at), | |
| 1148 | + KEY idx_period (period_start) | |
| 1149 | + ) {$charset_collate};"; | |
| 1150 | + } | |
| 1151 | + | |
| 1152 | + /** | |
| 828 | 1153 | * Create indexes for a specific table |
| 829 | 1154 | * |
| 830 | 1155 | * @since 1.0.0 |
| 831 | 1156 | * |
| @@ -848,9 +1173,9 @@ | ||
| 848 | 1173 | } |
| 849 | 1174 | |
| 850 | 1175 | $index_sql = $this->get_index_sql($full_table_name, $index_name); |
| 851 | 1176 | if ($index_sql) { |
| 852 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Index creation requires direct schema changes, DDL cannot be prepared | |
| 1177 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index creation requires direct schema changes, DDL cannot be prepared | |
| 853 | 1178 | $result = $this->wpdb->query($index_sql); |
| 854 | 1179 | if (false === $result) { |
| 855 | 1180 | $success = false; |
| 856 | 1181 | } |
| @@ -871,9 +1196,9 @@ | ||
| 871 | 1196 | } |
| 872 | 1197 | |
| 873 | 1198 | $index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns); |
| 874 | 1199 | if ($index_sql) { |
| 875 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Composite index creation requires direct schema changes, DDL cannot be prepared | |
| 1200 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Composite index creation requires direct schema changes, DDL cannot be prepared | |
| 876 | 1201 | $result = $this->wpdb->query($index_sql); |
| 877 | 1202 | if (false === $result) { |
| 878 | 1203 | $success = false; |
| 879 | 1204 | } |
| @@ -907,9 +1232,9 @@ | ||
| 907 | 1232 | foreach ($definition['foreign_keys'] as $constraint) { |
| 908 | 1233 | try { |
| 909 | 1234 | $constraint_sql = $this->get_constraint_sql($full_table_name, $constraint); |
| 910 | 1235 | if ($constraint_sql) { |
| 911 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Constraint creation requires direct schema changes, DDL cannot be prepared | |
| 1236 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Constraint creation requires direct schema changes, DDL cannot be prepared | |
| 912 | 1237 | $result = $this->wpdb->query($constraint_sql); |
| 913 | 1238 | if (false === $result) { |
| 914 | 1239 | $success = false; |
| 915 | 1240 | } |
| @@ -951,10 +1276,15 @@ | ||
| 951 | 1276 | if (empty($columns)) { |
| 952 | 1277 | return ''; |
| 953 | 1278 | } |
| 954 | 1279 | |
| 955 | - // Escape column names | |
| 956 | - $escaped_columns = array_map(function($column) { | |
| 1280 | + // Escape column names, preserving an optional key prefix — `col(191)` | |
| 1281 | + // stays a prefix rather than becoming part of the column name (#298). | |
| 1282 | + $escaped_columns = array_map(function ($column) { | |
| 1283 | + if (preg_match('/^([A-Za-z0-9_]+)\((\d+)\)$/', trim($column), $matches)) { | |
| 1284 | + return "`{$matches[1]}`({$matches[2]})"; | |
| 1285 | + } | |
| 1286 | + | |
| 957 | 1287 | return "`{$column}`"; |
| 958 | 1288 | }, $columns); |
| 959 | 1289 | |
| 960 | 1290 | $columns_sql = implode(', ', $escaped_columns); |
| @@ -1003,16 +1333,16 @@ | ||
| 1003 | 1333 | |
| 1004 | 1334 | $info['exists'] = true; |
| 1005 | 1335 | |
| 1006 | 1336 | // Get row count |
| 1007 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table statistics require direct database access, table name cannot be prepared, table name is validated | |
| 1337 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table statistics require direct database access, table name cannot be prepared, table name is validated | |
| 1008 | 1338 | $row_count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$table_name}`"); |
| 1009 | 1339 | $info['row_count'] = (int) $row_count; |
| 1010 | 1340 | |
| 1011 | 1341 | // Get table size information |
| 1012 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size information requires direct database access | |
| 1342 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size information requires direct database access | |
| 1013 | 1343 | $size_info = $this->wpdb->get_row( |
| 1014 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders | |
| 1344 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders | |
| 1015 | 1345 | $this->wpdb->prepare( |
| 1016 | 1346 | "SELECT |
| 1017 | 1347 | data_length as data_size, |
| 1018 | 1348 | index_length as index_size, |
| @@ -1020,11 +1350,11 @@ | ||
| 1020 | 1350 | create_time as created, |
| 1021 | 1351 | update_time as updated |
| 1022 | 1352 | FROM information_schema.TABLES |
| 1023 | 1353 | WHERE table_schema = %s AND table_name = %s", |
| 1024 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use | |
| 1354 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use | |
| 1025 | 1355 | DB_NAME, |
| 1026 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter | |
| 1356 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter | |
| 1027 | 1357 | $table_name |
| 1028 | 1358 | ), |
| 1029 | 1359 | ARRAY_A |
| 1030 | 1360 | ); |
| @@ -1048,18 +1378,18 @@ | ||
| 1048 | 1378 | * @param string $table_name Full table name |
| 1049 | 1379 | * @return int Table size in bytes |
| 1050 | 1380 | */ |
| 1051 | 1381 | private function get_table_size(string $table_name): int { |
| 1052 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Table size calculation requires direct database access | |
| 1382 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table size calculation requires direct database access | |
| 1053 | 1383 | $size = $this->wpdb->get_var( |
| 1054 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders | |
| 1384 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders | |
| 1055 | 1385 | $this->wpdb->prepare( |
| 1056 | 1386 | "SELECT (data_length + index_length) as total_size |
| 1057 | 1387 | FROM information_schema.TABLES |
| 1058 | 1388 | WHERE table_schema = %s AND table_name = %s", |
| 1059 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use | |
| 1389 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use | |
| 1060 | 1390 | DB_NAME, |
| 1061 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter | |
| 1391 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter | |
| 1062 | 1392 | $table_name |
| 1063 | 1393 | ) |
| 1064 | 1394 | ); |
| 1065 | 1395 | |
| @@ -1165,9 +1495,9 @@ | ||
| 1165 | 1495 | } |
| 1166 | 1496 | |
| 1167 | 1497 | $index_sql = $this->get_composite_index_sql($full_table_name, $index_name, $columns); |
| 1168 | 1498 | if ($index_sql) { |
| 1169 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared -- Performance index creation requires direct schema changes, DDL cannot be prepared | |
| 1499 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Performance index creation requires direct schema changes, DDL cannot be prepared | |
| 1170 | 1500 | $result = $this->wpdb->query($index_sql); |
| 1171 | 1501 | if (false === $result) { |
| 1172 | 1502 | $success = false; |
| 1173 | 1503 | // Index creation failed - logged in database operations |
| @@ -1184,8 +1514,199 @@ | ||
| 1184 | 1514 | return $success; |
| 1185 | 1515 | } |
| 1186 | 1516 | |
| 1187 | 1517 | /** |
| 1518 | + * Drop the full-width keys replaced by prefixed ones in #298. | |
| 1519 | + * | |
| 1520 | + * Installs created before the fix carry a key that spans more bytes than a | |
| 1521 | + * 767-byte-limit server accepts; the prefixed replacement is added by | |
| 1522 | + * dbDelta under a new name, and only once that replacement is confirmed | |
| 1523 | + * present is the legacy key dropped. If the ALTER that adds the prefixed | |
| 1524 | + * key failed — the one realistic cause being two existing rows that differ | |
| 1525 | + * only past the prefix — nothing is dropped and the table keeps working | |
| 1526 | + * exactly as before. | |
| 1527 | + * | |
| 1528 | + * @since 1.30.0 | |
| 1529 | + * | |
| 1530 | + * @return void | |
| 1531 | + */ | |
| 1532 | + /** | |
| 1533 | + * Delete settings rows that hold a REST envelope instead of a setting. | |
| 1534 | + * | |
| 1535 | + * A caller that posted a settings endpoint's whole response body back as | |
| 1536 | + * `settings` wrote `settings`, `schema`, `context_type` and `context_id` | |
| 1537 | + * as rows. get_settings() returns every stored row, so those four then | |
| 1538 | + * round-tripped into every later request — a serialized copy of the | |
| 1539 | + * settings plus their JSON schema, several KB per save. Nothing reads | |
| 1540 | + * them; sanitize_settings() now drops them on the way in, and this clears | |
| 1541 | + * what is already stored. | |
| 1542 | + * | |
| 1543 | + * @since 2.0.1 | |
| 1544 | + * | |
| 1545 | + * @return void | |
| 1546 | + */ | |
| 1547 | + private function purge_envelope_setting_rows(): void { | |
| 1548 | + $table = $this->get_table_name('seo_settings'); | |
| 1549 | + | |
| 1550 | + if (!$this->table_exists($table)) { | |
| 1551 | + return; | |
| 1552 | + } | |
| 1553 | + | |
| 1554 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-off cleanup; the table name comes from $wpdb->prefix and the keys are placeholders. | |
| 1555 | + $this->wpdb->query( | |
| 1556 | + $this->wpdb->prepare( | |
| 1557 | + "DELETE FROM `{$table}` WHERE `setting_key` IN (%s, %s, %s, %s)", | |
| 1558 | + 'settings', | |
| 1559 | + 'schema', | |
| 1560 | + 'context_type', | |
| 1561 | + 'context_id' | |
| 1562 | + ) | |
| 1563 | + ); | |
| 1564 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 1565 | + | |
| 1566 | + if (function_exists('wp_cache_flush_group')) { | |
| 1567 | + wp_cache_flush_group('thinkrank_seo'); | |
| 1568 | + } | |
| 1569 | + } | |
| 1570 | + | |
| 1571 | + /** | |
| 1572 | + * Settings categories owned by an SEO manager, and the class that owns them. | |
| 1573 | + * | |
| 1574 | + * Used only by purge_unknown_setting_rows(). A category absent here is left | |
| 1575 | + * alone rather than guessed at. | |
| 1576 | + * | |
| 1577 | + * @since 2.0.1 | |
| 1578 | + * | |
| 1579 | + * @var array<string, string> | |
| 1580 | + */ | |
| 1581 | + private const SETTINGS_CATEGORY_MANAGERS = [ | |
| 1582 | + 'site_identity' => \ThinkRank\SEO\Site_Identity_Manager::class, | |
| 1583 | + 'sitemap' => \ThinkRank\SEO\Sitemap_Generator::class, | |
| 1584 | + 'image_seo' => \ThinkRank\SEO\Image_SEO_Manager::class, | |
| 1585 | + 'schema_management_system' => \ThinkRank\SEO\Schema_Management_System::class, | |
| 1586 | + 'llms_txt' => \ThinkRank\SEO\LLMs_Txt_Manager::class, | |
| 1587 | + 'social_meta' => \ThinkRank\SEO\Social_Meta_Manager::class, | |
| 1588 | + 'seo_settings' => \ThinkRank\SEO\SEO_Settings_Manager::class, | |
| 1589 | + 'content_optimization_manager' => \ThinkRank\SEO\Content_Optimization_Manager::class, | |
| 1590 | + 'performance_monitoring_manager' => \ThinkRank\SEO\Performance_Monitoring_Manager::class, | |
| 1591 | + 'ai_content_analyzer' => \ThinkRank\SEO\AI_Content_Analyzer::class, | |
| 1592 | + ]; | |
| 1593 | + | |
| 1594 | + /** | |
| 1595 | + * Delete settings rows holding keys no manager declares. | |
| 1596 | + * | |
| 1597 | + * The envelope purge above cleared four specific keys; this clears the | |
| 1598 | + * general case behind them (#452). Any key a client posted was written as | |
| 1599 | + * a row, and because get_settings() returns every row for a category — and | |
| 1600 | + * save_settings() merges what it read before writing — a stray was echoed | |
| 1601 | + * into every later response and rewritten on every save, so it never aged | |
| 1602 | + * out on its own. | |
| 1603 | + * | |
| 1604 | + * Deliberately conservative: a category with no manager in the map, and a | |
| 1605 | + * manager that cannot be constructed, are skipped rather than cleared, and | |
| 1606 | + * the judgement is the manager's own accepts_setting_key() — the same gate | |
| 1607 | + * the save path now applies, so the migration cannot delete a row the | |
| 1608 | + * plugin would accept today. | |
| 1609 | + * | |
| 1610 | + * @since 2.0.1 | |
| 1611 | + * | |
| 1612 | + * @return void | |
| 1613 | + */ | |
| 1614 | + private function purge_unknown_setting_rows(): void { | |
| 1615 | + $table = $this->get_table_name('seo_settings'); | |
| 1616 | + | |
| 1617 | + if (!$this->table_exists($table)) { | |
| 1618 | + return; | |
| 1619 | + } | |
| 1620 | + | |
| 1621 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- one-off cleanup; the table name comes from $wpdb->prefix and every value is a placeholder. | |
| 1622 | + foreach (self::SETTINGS_CATEGORY_MANAGERS as $category => $class) { | |
| 1623 | + if (!class_exists($class)) { | |
| 1624 | + continue; | |
| 1625 | + } | |
| 1626 | + | |
| 1627 | + try { | |
| 1628 | + $manager = new $class(); | |
| 1629 | + } catch (\Throwable $e) { | |
| 1630 | + continue; | |
| 1631 | + } | |
| 1632 | + | |
| 1633 | + if (!method_exists($manager, 'accepts_setting_key')) { | |
| 1634 | + continue; | |
| 1635 | + } | |
| 1636 | + | |
| 1637 | + $rows = $this->wpdb->get_results( | |
| 1638 | + $this->wpdb->prepare( | |
| 1639 | + "SELECT DISTINCT `setting_key`, `context_type` FROM `{$table}` WHERE `setting_category` = %s", | |
| 1640 | + $category | |
| 1641 | + ) | |
| 1642 | + ); | |
| 1643 | + | |
| 1644 | + if (empty($rows)) { | |
| 1645 | + continue; | |
| 1646 | + } | |
| 1647 | + | |
| 1648 | + $unknown = []; | |
| 1649 | + | |
| 1650 | + foreach ($rows as $row) { | |
| 1651 | + $context = (string) $row->context_type; | |
| 1652 | + | |
| 1653 | + if (!$manager->accepts_setting_key((string) $row->setting_key, $context)) { | |
| 1654 | + $unknown[] = (string) $row->setting_key; | |
| 1655 | + } | |
| 1656 | + } | |
| 1657 | + | |
| 1658 | + $unknown = array_values(array_unique($unknown)); | |
| 1659 | + | |
| 1660 | + if (empty($unknown)) { | |
| 1661 | + continue; | |
| 1662 | + } | |
| 1663 | + | |
| 1664 | + $placeholders = implode(', ', array_fill(0, count($unknown), '%s')); | |
| 1665 | + | |
| 1666 | + $this->wpdb->query( | |
| 1667 | + $this->wpdb->prepare( | |
| 1668 | + "DELETE FROM `{$table}` WHERE `setting_category` = %s AND `setting_key` IN ({$placeholders})", | |
| 1669 | + array_merge([$category], $unknown) | |
| 1670 | + ) | |
| 1671 | + ); | |
| 1672 | + } | |
| 1673 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 1674 | + | |
| 1675 | + if (function_exists('wp_cache_flush_group')) { | |
| 1676 | + wp_cache_flush_group('thinkrank_seo'); | |
| 1677 | + } | |
| 1678 | + } | |
| 1679 | + | |
| 1680 | + private function drop_replaced_wide_indexes(): void { | |
| 1681 | + foreach (self::REPLACED_WIDE_INDEXES as $table => $renames) { | |
| 1682 | + $full_table_name = $this->get_table_name($table); | |
| 1683 | + | |
| 1684 | + if (!$this->table_exists($full_table_name)) { | |
| 1685 | + continue; | |
| 1686 | + } | |
| 1687 | + | |
| 1688 | + foreach ($renames as $current_index => $legacy_index) { | |
| 1689 | + if (!$this->index_exists($full_table_name, $legacy_index)) { | |
| 1690 | + continue; | |
| 1691 | + } | |
| 1692 | + | |
| 1693 | + if (!$this->index_exists($full_table_name, $current_index)) { | |
| 1694 | + // The replacement is not there yet; keep the old key so the | |
| 1695 | + // upsert still has a unique constraint to collide against. | |
| 1696 | + continue; | |
| 1697 | + } | |
| 1698 | + | |
| 1699 | + // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot be prepared; both names come from a class constant and the table name from $wpdb->prefix. | |
| 1700 | + $this->wpdb->query( | |
| 1701 | + "ALTER TABLE `{$full_table_name}` DROP INDEX `" . esc_sql($legacy_index) . '`' | |
| 1702 | + ); | |
| 1703 | + // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter | |
| 1704 | + } | |
| 1705 | + } | |
| 1706 | + } | |
| 1707 | + | |
| 1708 | + /** | |
| 1188 | 1709 | * Check if an index exists on a table |
| 1189 | 1710 | * |
| 1190 | 1711 | * @since 1.0.0 |
| 1191 | 1712 | * |
| @@ -1193,19 +1714,19 @@ | ||
| 1193 | 1714 | * @param string $index_name Index name |
| 1194 | 1715 | * @return bool Whether index exists |
| 1195 | 1716 | */ |
| 1196 | 1717 | private function index_exists(string $table_name, string $index_name): bool { |
| 1197 | - // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Index existence check requires direct database access | |
| 1718 | + // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Index existence check requires direct database access | |
| 1198 | 1719 | $result = $this->wpdb->get_var( |
| 1199 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- SQL is properly prepared with placeholders | |
| 1720 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL is properly prepared with placeholders | |
| 1200 | 1721 | $this->wpdb->prepare( |
| 1201 | 1722 | "SELECT COUNT(*) FROM information_schema.statistics |
| 1202 | 1723 | WHERE table_schema = %s AND table_name = %s AND index_name = %s", |
| 1203 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- DB_NAME is a WordPress constant, safe to use | |
| 1724 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DB_NAME is a WordPress constant, safe to use | |
| 1204 | 1725 | DB_NAME, |
| 1205 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $table_name is validated and used as parameter | |
| 1726 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $table_name is validated and used as parameter | |
| 1206 | 1727 | $table_name, |
| 1207 | - // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- $index_name is validated and used as parameter | |
| 1728 | + // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $index_name is validated and used as parameter | |
| 1208 | 1729 | $index_name |
| 1209 | 1730 | ) |
| 1210 | 1731 | ); |
| 1211 | 1732 | |
| @@ -1212,33 +1733,57 @@ | ||
| 1212 | 1733 | return (int) $result > 0; |
| 1213 | 1734 | } |
| 1214 | 1735 | |
| 1215 | 1736 | /** |
| 1216 | - * Check if MySQL supports JSON column type with caching | |
| 1737 | + * Check if the database server supports the JSON column type | |
| 1217 | 1738 | * |
| 1218 | - * Uses WordPress's built-in database version detection and caches the result | |
| 1219 | - * to avoid repeated database queries during schema creation. | |
| 1739 | + * MySQL added JSON in 5.7.8 and MariaDB in 10.2.7. MariaDB's own 10.x | |
| 1740 | + * number passes any MySQL threshold, and on older PHP the server string | |
| 1741 | + * carries a `5.5.5-` replication prefix that db_version() reads as the | |
| 1742 | + * version, so MariaDB's version is taken from the server string itself. | |
| 1743 | + * Neither lookup queries the database. | |
| 1220 | 1744 | * |
| 1221 | 1745 | * @since 1.0.0 |
| 1222 | - * @return bool True if MySQL 5.7+ supports JSON columns | |
| 1746 | + * @return bool True if the server supports JSON columns | |
| 1223 | 1747 | */ |
| 1224 | 1748 | private function get_mysql_json_support(): bool { |
| 1225 | - // Check if we have cached result | |
| 1226 | - static $json_support = null; | |
| 1227 | - | |
| 1228 | - if ($json_support !== null) { | |
| 1229 | - return $json_support; | |
| 1230 | - } | |
| 1231 | - | |
| 1232 | - // Use WordPress's built-in database version method | |
| 1233 | 1749 | global $wpdb; |
| 1234 | 1750 | |
| 1235 | - // Get MySQL version using WordPress method (safer than direct query) | |
| 1236 | - $mysql_version = $wpdb->db_version(); | |
| 1751 | + $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : ''; | |
| 1237 | 1752 | |
| 1238 | - // Cache the result for subsequent calls | |
| 1239 | - $json_support = version_compare($mysql_version, '5.7.0', '>='); | |
| 1753 | + if (preg_match('/(\d+(?:\.\d+)+)-MariaDB/i', $server_info, $matches)) { | |
| 1754 | + return version_compare($matches[1], '10.2.7', '>='); | |
| 1755 | + } | |
| 1240 | 1756 | |
| 1241 | - return $json_support; | |
| 1757 | + return version_compare((string) $wpdb->db_version(), '5.7.8', '>='); | |
| 1242 | 1758 | } |
| 1243 | 1759 | |
| 1760 | + /** | |
| 1761 | + * Get SQL for the AI traffic table. | |
| 1762 | + * | |
| 1763 | + * Daily aggregate counters only — one row per (day, kind, source, path). | |
| 1764 | + * `kind` is 'referral' (human visit from an AI platform), 'crawler' (AI | |
| 1765 | + * bot user-agent), or 'baseline' (all human pageviews, for the share-of- | |
| 1766 | + * traffic figure). No IPs, no user agents, no per-visit rows: aggregates | |
| 1767 | + * keep the table small and the feature privacy-clean. | |
| 1768 | + * | |
| 1769 | + * @since 1.27.0 | |
| 1770 | + * | |
| 1771 | + * @param string $table_name Full table name | |
| 1772 | + * @param string $charset_collate Charset and collation | |
| 1773 | + * @return string SQL for table creation | |
| 1774 | + */ | |
| 1775 | + private function get_ai_traffic_table_sql(string $table_name, string $charset_collate): string { | |
| 1776 | + return "CREATE TABLE `{$table_name}` ( | |
| 1777 | + id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1778 | + day date NOT NULL, | |
| 1779 | + kind varchar(12) NOT NULL, | |
| 1780 | + source varchar(40) NOT NULL DEFAULT '', | |
| 1781 | + path varchar(191) NOT NULL DEFAULT '', | |
| 1782 | + hits bigint(20) unsigned NOT NULL DEFAULT 1, | |
| 1783 | + PRIMARY KEY (id), | |
| 1784 | + UNIQUE KEY uniq_bucket (day, kind, source, path), | |
| 1785 | + KEY idx_day (day), | |
| 1786 | + KEY idx_kind (kind) | |
| 1787 | + ) {$charset_collate};"; | |
| 1788 | + } | |
| 1244 | 1789 | } |
