| @@ -19,8 +19,13 @@ | ||
| 19 | 19 | declare(strict_types=1); |
| 20 | 20 | |
| 21 | 21 | namespace ThinkRank\Database; |
| 22 | 22 | |
| 23 | +// Prevent direct access | |
| 24 | +if (!defined('ABSPATH')) { | |
| 25 | + exit; | |
| 26 | +} | |
| 27 | + | |
| 23 | 28 | /** |
| 24 | 29 | * Database Schema Manager Class |
| 25 | 30 | * |
| 26 | 31 | * Handles creation, management, and optimization of all SEO database tables. |
| @@ -43,11 +48,84 @@ | ||
| 43 | 48 | * |
| 44 | 49 | * @since 1.0.0 |
| 45 | 50 | * @var string |
| 46 | 51 | */ |
| 47 | - private string $db_version = '1.2.0'; | |
| 52 | + private string $db_version = '1.9.2'; | |
| 48 | 53 | |
| 49 | 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 | + /** | |
| 50 | 128 | * Database table definitions with specifications |
| 51 | 129 | * |
| 52 | 130 | * Consolidated table definitions for all ThinkRank tables (11 total): |
| 53 | 131 | * - SEO Tables (7): Core SEO functionality with context-aware structure |
| @@ -110,9 +188,11 @@ | ||
| 110 | 188 | 'description' => 'AI response caching for performance optimization', |
| 111 | 189 | 'primary_key' => 'id', |
| 112 | 190 | 'indexes' => ['cache_key', 'expires_at', 'created_at'], |
| 113 | 191 | 'composite_indexes' => [ |
| 114 | - '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'], | |
| 115 | 195 | 'cleanup_expired' => ['expires_at', 'created_at'] |
| 116 | 196 | ], |
| 117 | 197 | 'foreign_keys' => [] |
| 118 | 198 | ], |
| @@ -150,9 +230,9 @@ | ||
| 150 | 230 | ], |
| 151 | 231 | 'instant_indexing_logs' => [ |
| 152 | 232 | 'description' => 'Log of IndexNow URL submissions', |
| 153 | 233 | 'primary_key' => 'id', |
| 154 | - 'indexes' => ['status', 'response_code', 'created_at'], | |
| 234 | + 'indexes' => ['url', 'status', 'response_code', 'created_at'], | |
| 155 | 235 | 'foreign_keys' => [] |
| 156 | 236 | ], |
| 157 | 237 | 'email_report_logs' => [ |
| 158 | 238 | 'description' => 'Audit + dedupe log for scheduled SEO email reports', |
| @@ -162,8 +242,16 @@ | ||
| 162 | 242 | 'dedupe_key' => ['site_id', 'period_start', 'recipient_hash'], |
| 163 | 243 | 'site_recent' => ['site_id', 'sent_at'] |
| 164 | 244 | ], |
| 165 | 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' => [] | |
| 166 | 254 | ] |
| 167 | 255 | ]; |
| 168 | 256 | |
| 169 | 257 | /** |
| @@ -184,9 +272,10 @@ | ||
| 184 | 272 | 'seo' => ['seo_settings', 'seo_analysis', 'seo_keywords', 'seo_schema', 'seo_social', 'seo_performance', 'seo_local', 'instant_indexing_logs'], |
| 185 | 273 | 'ai' => ['ai_cache', 'ai_usage'], |
| 186 | 274 | 'content' => ['content_briefs'], |
| 187 | 275 | 'scoring' => ['seo_scores'], |
| 188 | - 'reporting' => ['email_report_logs'] | |
| 276 | + 'reporting' => ['email_report_logs'], | |
| 277 | + 'ai_visibility' => ['ai_traffic'] | |
| 189 | 278 | ]; |
| 190 | 279 | |
| 191 | 280 | /** |
| 192 | 281 | * Constructor |
| @@ -197,12 +286,16 @@ | ||
| 197 | 286 | global $wpdb; |
| 198 | 287 | $this->wpdb = $wpdb; |
| 199 | 288 | |
| 200 | 289 | // Set database configuration |
| 201 | - $this->db_config = [ | |
| 202 | - 'charset' => $wpdb->charset ?: 'utf8mb4', | |
| 203 | - 'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci' | |
| 204 | - ]; | |
| 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()]; | |
| 205 | 298 | } |
| 206 | 299 | |
| 207 | 300 | /** |
| 208 | 301 | * Create all database tables |
| @@ -232,8 +325,14 @@ | ||
| 232 | 325 | |
| 233 | 326 | // Create table using dbDelta for WordPress compatibility |
| 234 | 327 | $result = dbDelta($sql); |
| 235 | 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 | + | |
| 236 | 335 | // Verify table creation |
| 237 | 336 | if ($this->table_exists($full_table_name)) { |
| 238 | 337 | $results['tables_created'][] = $full_table_name; |
| 239 | 338 | |
| @@ -243,9 +342,10 @@ | ||
| 243 | 342 | // Add constraints if needed |
| 244 | 343 | $this->add_table_constraints($table_name); |
| 245 | 344 | } else { |
| 246 | 345 | $results['tables_failed'][] = $full_table_name; |
| 247 | - $results['errors'][] = "Failed to create table: {$full_table_name}"; | |
| 346 | + $results['errors'][] = "Failed to create table: {$full_table_name}" | |
| 347 | + . ('' !== $db_error ? ' — ' . $db_error : ''); | |
| 248 | 348 | $results['success'] = false; |
| 249 | 349 | } |
| 250 | 350 | } catch (\Exception $e) { |
| 251 | 351 | $results['tables_failed'][] = $this->get_table_name($table_name); |
| @@ -253,18 +353,76 @@ | ||
| 253 | 353 | $results['success'] = false; |
| 254 | 354 | } |
| 255 | 355 | } |
| 256 | 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 | + | |
| 257 | 367 | // Update database version |
| 258 | 368 | if ($results['success']) { |
| 259 | 369 | update_option('thinkrank_seo_db_version', $this->db_version); |
| 260 | 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']); | |
| 261 | 374 | } |
| 262 | 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 | + | |
| 263 | 380 | return $results; |
| 264 | 381 | } |
| 265 | 382 | |
| 266 | 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 | + /** | |
| 267 | 425 | * Drop all database tables |
| 268 | 426 | * |
| 269 | 427 | * @since 1.0.0 |
| 270 | 428 | * |
| @@ -316,12 +474,67 @@ | ||
| 316 | 474 | * @return bool True if update needed |
| 317 | 475 | */ |
| 318 | 476 | public function needs_update(): bool { |
| 319 | 477 | $current_version = get_option('thinkrank_seo_db_version', '0.0.0'); |
| 320 | - 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()); | |
| 321 | 491 | } |
| 322 | 492 | |
| 323 | 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 | + /** | |
| 324 | 537 | * The schema version this plugin build expects (the target of needs_update). |
| 325 | 538 | * |
| 326 | 539 | * @since 1.23.0 |
| 327 | 540 | * |
| @@ -447,13 +660,21 @@ | ||
| 447 | 660 | * @since 1.0.0 |
| 448 | 661 | * |
| 449 | 662 | * @param string $table_name Table name |
| 450 | 663 | * @return string SQL for table creation |
| 664 | + * | |
| 665 | + * @throws \InvalidArgumentException On failure. | |
| 451 | 666 | */ |
| 452 | 667 | private function get_table_sql(string $table_name): string { |
| 453 | 668 | $full_table_name = $this->get_table_name($table_name); |
| 454 | - $charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}"; | |
| 455 | 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 | + | |
| 456 | 677 | switch ($table_name) { |
| 457 | 678 | // SEO Tables |
| 458 | 679 | case 'seo_settings': |
| 459 | 680 | return $this->get_seo_settings_table_sql($full_table_name, $charset_collate); |
| @@ -483,8 +704,12 @@ | ||
| 483 | 704 | return $this->get_instant_indexing_logs_table_sql($full_table_name, $charset_collate); |
| 484 | 705 | case 'email_report_logs': |
| 485 | 706 | return $this->get_email_report_logs_table_sql($full_table_name, $charset_collate); |
| 486 | 707 | |
| 708 | + // AI Visibility Tables | |
| 709 | + case 'ai_traffic': | |
| 710 | + return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate); | |
| 711 | + | |
| 487 | 712 | default: |
| 488 | 713 | throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name)); |
| 489 | 714 | } |
| 490 | 715 | } |
| @@ -513,9 +738,9 @@ | ||
| 513 | 738 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 514 | 739 | created_by bigint(20) unsigned NULL, |
| 515 | 740 | updated_by bigint(20) unsigned NULL, |
| 516 | 741 | PRIMARY KEY (setting_id), |
| 517 | - 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)), | |
| 518 | 743 | KEY idx_context (context_type, context_id), |
| 519 | 744 | KEY idx_category (setting_category), |
| 520 | 745 | KEY idx_active (is_active), |
| 521 | 746 | KEY idx_created (created_at), |
| @@ -658,9 +883,9 @@ | ||
| 658 | 883 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 659 | 884 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 660 | 885 | created_by bigint(20) unsigned NULL, |
| 661 | 886 | PRIMARY KEY (social_id), |
| 662 | - 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)), | |
| 663 | 888 | KEY idx_context (context_type, context_id), |
| 664 | 889 | KEY idx_platform (platform), |
| 665 | 890 | KEY idx_type (meta_type), |
| 666 | 891 | KEY idx_optimized (is_optimized), |
| @@ -766,9 +991,9 @@ | ||
| 766 | 991 | cache_data longtext NOT NULL, |
| 767 | 992 | expires_at bigint(20) unsigned NOT NULL, |
| 768 | 993 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 769 | 994 | PRIMARY KEY (id), |
| 770 | - UNIQUE KEY cache_key (cache_key), | |
| 995 | + UNIQUE KEY unique_cache_key (cache_key(191)), | |
| 771 | 996 | KEY expires_at_idx (expires_at), |
| 772 | 997 | KEY created_at_idx (created_at) |
| 773 | 998 | ) {$charset_collate};"; |
| 774 | 999 | } |
| @@ -845,8 +1070,9 @@ | ||
| 845 | 1070 | response_code int(11) NULL, |
| 846 | 1071 | response_message text NULL, |
| 847 | 1072 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 848 | 1073 | PRIMARY KEY (id), |
| 1074 | + KEY idx_url (url(191)), | |
| 849 | 1075 | KEY idx_status (status), |
| 850 | 1076 | KEY idx_response_code (response_code), |
| 851 | 1077 | KEY idx_created (created_at) |
| 852 | 1078 | ) {$charset_collate};"; |
| @@ -909,8 +1135,9 @@ | ||
| 909 | 1135 | recipient_hash char(64) NOT NULL, |
| 910 | 1136 | recipient_count smallint(5) unsigned NOT NULL DEFAULT 1, |
| 911 | 1137 | frequency_days smallint(5) unsigned NOT NULL DEFAULT 30, |
| 912 | 1138 | status varchar(20) NOT NULL DEFAULT 'pending', |
| 1139 | + attempts smallint(5) unsigned NOT NULL DEFAULT 1, | |
| 913 | 1140 | error_message text NULL, |
| 914 | 1141 | sent_at datetime NULL, |
| 915 | 1142 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 916 | 1143 | PRIMARY KEY (id), |
| @@ -1049,10 +1276,15 @@ | ||
| 1049 | 1276 | if (empty($columns)) { |
| 1050 | 1277 | return ''; |
| 1051 | 1278 | } |
| 1052 | 1279 | |
| 1053 | - // Escape column names | |
| 1280 | + // Escape column names, preserving an optional key prefix — `col(191)` | |
| 1281 | + // stays a prefix rather than becoming part of the column name (#298). | |
| 1054 | 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 | + | |
| 1055 | 1287 | return "`{$column}`"; |
| 1056 | 1288 | }, $columns); |
| 1057 | 1289 | |
| 1058 | 1290 | $columns_sql = implode(', ', $escaped_columns); |
| @@ -1282,8 +1514,199 @@ | ||
| 1282 | 1514 | return $success; |
| 1283 | 1515 | } |
| 1284 | 1516 | |
| 1285 | 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 | + /** | |
| 1286 | 1709 | * Check if an index exists on a table |
| 1287 | 1710 | * |
| 1288 | 1711 | * @since 1.0.0 |
| 1289 | 1712 | * |
| @@ -1310,32 +1733,57 @@ | ||
| 1310 | 1733 | return (int) $result > 0; |
| 1311 | 1734 | } |
| 1312 | 1735 | |
| 1313 | 1736 | /** |
| 1314 | - * Check if MySQL supports JSON column type with caching | |
| 1737 | + * Check if the database server supports the JSON column type | |
| 1315 | 1738 | * |
| 1316 | - * Uses WordPress's built-in database version detection and caches the result | |
| 1317 | - * 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. | |
| 1318 | 1744 | * |
| 1319 | 1745 | * @since 1.0.0 |
| 1320 | - * @return bool True if MySQL 5.7+ supports JSON columns | |
| 1746 | + * @return bool True if the server supports JSON columns | |
| 1321 | 1747 | */ |
| 1322 | 1748 | private function get_mysql_json_support(): bool { |
| 1323 | - // Check if we have cached result | |
| 1324 | - static $json_support = null; | |
| 1749 | + global $wpdb; | |
| 1325 | 1750 | |
| 1326 | - if ($json_support !== null) { | |
| 1327 | - return $json_support; | |
| 1751 | + $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : ''; | |
| 1752 | + | |
| 1753 | + if (preg_match('/(\d+(?:\.\d+)+)-MariaDB/i', $server_info, $matches)) { | |
| 1754 | + return version_compare($matches[1], '10.2.7', '>='); | |
| 1328 | 1755 | } |
| 1329 | 1756 | |
| 1330 | - // Use WordPress's built-in database version method | |
| 1331 | - global $wpdb; | |
| 1757 | + return version_compare((string) $wpdb->db_version(), '5.7.8', '>='); | |
| 1758 | + } | |
| 1332 | 1759 | |
| 1333 | - // Get MySQL version using WordPress method (safer than direct query) | |
| 1334 | - $mysql_version = $wpdb->db_version(); | |
| 1335 | - | |
| 1336 | - // Cache the result for subsequent calls | |
| 1337 | - $json_support = version_compare($mysql_version, '5.7.0', '>='); | |
| 1338 | - | |
| 1339 | - return $json_support; | |
| 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};"; | |
| 1340 | 1788 | } |
| 1341 | 1789 | } |
