| @@ -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,49 @@ | ||
| 43 | 48 | * |
| 44 | 49 | * @since 1.0.0 |
| 45 | 50 | * @var string |
| 46 | 51 | */ |
| 47 | - private string $db_version = '1.6.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 | + /** | |
| 50 | 93 | * Transient caching a verified-complete schema, so the missing-table probe |
| 51 | 94 | * costs one query per hour on a healthy site rather than one per request. |
| 52 | 95 | * |
| 53 | 96 | * @since 1.28.0 |
| @@ -55,8 +98,34 @@ | ||
| 55 | 98 | */ |
| 56 | 99 | private const TABLES_VERIFIED_TRANSIENT = 'thinkrank_schema_verified'; |
| 57 | 100 | |
| 58 | 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 | + /** | |
| 59 | 128 | * Database table definitions with specifications |
| 60 | 129 | * |
| 61 | 130 | * Consolidated table definitions for all ThinkRank tables (11 total): |
| 62 | 131 | * - SEO Tables (7): Core SEO functionality with context-aware structure |
| @@ -119,9 +188,11 @@ | ||
| 119 | 188 | 'description' => 'AI response caching for performance optimization', |
| 120 | 189 | 'primary_key' => 'id', |
| 121 | 190 | 'indexes' => ['cache_key', 'expires_at', 'created_at'], |
| 122 | 191 | 'composite_indexes' => [ |
| 123 | - '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'], | |
| 124 | 195 | 'cleanup_expired' => ['expires_at', 'created_at'] |
| 125 | 196 | ], |
| 126 | 197 | 'foreign_keys' => [] |
| 127 | 198 | ], |
| @@ -159,9 +230,9 @@ | ||
| 159 | 230 | ], |
| 160 | 231 | 'instant_indexing_logs' => [ |
| 161 | 232 | 'description' => 'Log of IndexNow URL submissions', |
| 162 | 233 | 'primary_key' => 'id', |
| 163 | - 'indexes' => ['status', 'response_code', 'created_at'], | |
| 234 | + 'indexes' => ['url', 'status', 'response_code', 'created_at'], | |
| 164 | 235 | 'foreign_keys' => [] |
| 165 | 236 | ], |
| 166 | 237 | 'email_report_logs' => [ |
| 167 | 238 | 'description' => 'Audit + dedupe log for scheduled SEO email reports', |
| @@ -173,35 +244,14 @@ | ||
| 173 | 244 | ], |
| 174 | 245 | 'foreign_keys' => [] |
| 175 | 246 | ], |
| 176 | 247 | |
| 177 | - // === AI VISIBILITY TABLES (2) === | |
| 248 | + // === AI VISIBILITY TABLES (1) === | |
| 178 | 249 | 'ai_traffic' => [ |
| 179 | 250 | 'description' => 'Daily aggregate counters for AI referral traffic, AI crawler hits, and the all-traffic baseline', |
| 180 | 251 | 'primary_key' => 'id', |
| 181 | 252 | 'indexes' => ['day', 'kind'], |
| 182 | 253 | 'foreign_keys' => [] |
| 183 | - ], | |
| 184 | - 'brand_visibility_checks' => [ | |
| 185 | - 'description' => 'History of AI brand-visibility checks run through the configured AI provider', | |
| 186 | - 'primary_key' => 'id', | |
| 187 | - 'indexes' => ['checked_at', 'query_text'], | |
| 188 | - 'foreign_keys' => [] | |
| 189 | - ], | |
| 190 | - 'bv_runs' => [ | |
| 191 | - 'description' => 'Brand Visibility v2 analysis runs: one row per run, with its config snapshot, progress counters and computed aggregates', | |
| 192 | - 'primary_key' => 'id', | |
| 193 | - 'indexes' => ['status', 'started_at'], | |
| 194 | - 'foreign_keys' => [] | |
| 195 | - ], | |
| 196 | - 'bv_tasks' => [ | |
| 197 | - 'description' => 'Brand Visibility v2 units of work: one row per query x platform x sample, processed off-request by cron ticks', | |
| 198 | - 'primary_key' => 'id', | |
| 199 | - 'indexes' => ['run_id', 'status'], | |
| 200 | - 'composite_indexes' => [ | |
| 201 | - 'run_status' => ['run_id', 'status'], | |
| 202 | - ], | |
| 203 | - 'foreign_keys' => [] | |
| 204 | 254 | ] |
| 205 | 255 | ]; |
| 206 | 256 | |
| 207 | 257 | /** |
| @@ -223,9 +273,9 @@ | ||
| 223 | 273 | 'ai' => ['ai_cache', 'ai_usage'], |
| 224 | 274 | 'content' => ['content_briefs'], |
| 225 | 275 | 'scoring' => ['seo_scores'], |
| 226 | 276 | 'reporting' => ['email_report_logs'], |
| 227 | - 'ai_visibility' => ['ai_traffic', 'brand_visibility_checks', 'bv_runs', 'bv_tasks'] | |
| 277 | + 'ai_visibility' => ['ai_traffic'] | |
| 228 | 278 | ]; |
| 229 | 279 | |
| 230 | 280 | /** |
| 231 | 281 | * Constructor |
| @@ -236,12 +286,16 @@ | ||
| 236 | 286 | global $wpdb; |
| 237 | 287 | $this->wpdb = $wpdb; |
| 238 | 288 | |
| 239 | 289 | // Set database configuration |
| 240 | - $this->db_config = [ | |
| 241 | - 'charset' => $wpdb->charset ?: 'utf8mb4', | |
| 242 | - 'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci' | |
| 243 | - ]; | |
| 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()]; | |
| 244 | 298 | } |
| 245 | 299 | |
| 246 | 300 | /** |
| 247 | 301 | * Create all database tables |
| @@ -271,8 +325,14 @@ | ||
| 271 | 325 | |
| 272 | 326 | // Create table using dbDelta for WordPress compatibility |
| 273 | 327 | $result = dbDelta($sql); |
| 274 | 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 | + | |
| 275 | 335 | // Verify table creation |
| 276 | 336 | if ($this->table_exists($full_table_name)) { |
| 277 | 337 | $results['tables_created'][] = $full_table_name; |
| 278 | 338 | |
| @@ -282,9 +342,10 @@ | ||
| 282 | 342 | // Add constraints if needed |
| 283 | 343 | $this->add_table_constraints($table_name); |
| 284 | 344 | } else { |
| 285 | 345 | $results['tables_failed'][] = $full_table_name; |
| 286 | - $results['errors'][] = "Failed to create table: {$full_table_name}"; | |
| 346 | + $results['errors'][] = "Failed to create table: {$full_table_name}" | |
| 347 | + . ('' !== $db_error ? ' — ' . $db_error : ''); | |
| 287 | 348 | $results['success'] = false; |
| 288 | 349 | } |
| 289 | 350 | } catch (\Exception $e) { |
| 290 | 351 | $results['tables_failed'][] = $this->get_table_name($table_name); |
| @@ -292,12 +353,25 @@ | ||
| 292 | 353 | $results['success'] = false; |
| 293 | 354 | } |
| 294 | 355 | } |
| 295 | 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 | + | |
| 296 | 367 | // Update database version |
| 297 | 368 | if ($results['success']) { |
| 298 | 369 | update_option('thinkrank_seo_db_version', $this->db_version); |
| 299 | 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']); | |
| 300 | 374 | } |
| 301 | 375 | |
| 302 | 376 | // The schema just changed, so any cached "verified complete" answer is |
| 303 | 377 | // stale either way — drop it and let the next probe re-check. |
| @@ -306,8 +380,49 @@ | ||
| 306 | 380 | return $results; |
| 307 | 381 | } |
| 308 | 382 | |
| 309 | 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 | + /** | |
| 310 | 425 | * Drop all database tables |
| 311 | 426 | * |
| 312 | 427 | * @since 1.0.0 |
| 313 | 428 | * |
| @@ -398,13 +513,17 @@ | ||
| 398 | 513 | } |
| 399 | 514 | |
| 400 | 515 | // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- schema probe; result cached below. |
| 401 | 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. | |
| 402 | 518 | $this->wpdb->prepare( |
| 403 | 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. | |
| 404 | 521 | $this->wpdb->esc_like($this->wpdb->prefix . 'thinkrank_') . '%' |
| 405 | 522 | ) |
| 406 | 523 | ); |
| 524 | + // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared | |
| 525 | + // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared | |
| 407 | 526 | |
| 408 | 527 | $missing = array_values(array_diff($expected, $existing)); |
| 409 | 528 | |
| 410 | 529 | if (empty($missing)) { |
| @@ -541,13 +660,21 @@ | ||
| 541 | 660 | * @since 1.0.0 |
| 542 | 661 | * |
| 543 | 662 | * @param string $table_name Table name |
| 544 | 663 | * @return string SQL for table creation |
| 664 | + * | |
| 665 | + * @throws \InvalidArgumentException On failure. | |
| 545 | 666 | */ |
| 546 | 667 | private function get_table_sql(string $table_name): string { |
| 547 | 668 | $full_table_name = $this->get_table_name($table_name); |
| 548 | - $charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}"; | |
| 549 | 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 | + | |
| 550 | 677 | switch ($table_name) { |
| 551 | 678 | // SEO Tables |
| 552 | 679 | case 'seo_settings': |
| 553 | 680 | return $this->get_seo_settings_table_sql($full_table_name, $charset_collate); |
| @@ -580,14 +707,8 @@ | ||
| 580 | 707 | |
| 581 | 708 | // AI Visibility Tables |
| 582 | 709 | case 'ai_traffic': |
| 583 | 710 | return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate); |
| 584 | - case 'bv_runs': | |
| 585 | - return $this->get_bv_runs_table_sql($full_table_name, $charset_collate); | |
| 586 | - case 'bv_tasks': | |
| 587 | - return $this->get_bv_tasks_table_sql($full_table_name, $charset_collate); | |
| 588 | - case 'brand_visibility_checks': | |
| 589 | - return $this->get_brand_visibility_checks_table_sql($full_table_name, $charset_collate); | |
| 590 | 711 | |
| 591 | 712 | default: |
| 592 | 713 | throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name)); |
| 593 | 714 | } |
| @@ -617,9 +738,9 @@ | ||
| 617 | 738 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 618 | 739 | created_by bigint(20) unsigned NULL, |
| 619 | 740 | updated_by bigint(20) unsigned NULL, |
| 620 | 741 | PRIMARY KEY (setting_id), |
| 621 | - 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)), | |
| 622 | 743 | KEY idx_context (context_type, context_id), |
| 623 | 744 | KEY idx_category (setting_category), |
| 624 | 745 | KEY idx_active (is_active), |
| 625 | 746 | KEY idx_created (created_at), |
| @@ -762,9 +883,9 @@ | ||
| 762 | 883 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 763 | 884 | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, |
| 764 | 885 | created_by bigint(20) unsigned NULL, |
| 765 | 886 | PRIMARY KEY (social_id), |
| 766 | - 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)), | |
| 767 | 888 | KEY idx_context (context_type, context_id), |
| 768 | 889 | KEY idx_platform (platform), |
| 769 | 890 | KEY idx_type (meta_type), |
| 770 | 891 | KEY idx_optimized (is_optimized), |
| @@ -870,9 +991,9 @@ | ||
| 870 | 991 | cache_data longtext NOT NULL, |
| 871 | 992 | expires_at bigint(20) unsigned NOT NULL, |
| 872 | 993 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 873 | 994 | PRIMARY KEY (id), |
| 874 | - UNIQUE KEY cache_key (cache_key), | |
| 995 | + UNIQUE KEY unique_cache_key (cache_key(191)), | |
| 875 | 996 | KEY expires_at_idx (expires_at), |
| 876 | 997 | KEY created_at_idx (created_at) |
| 877 | 998 | ) {$charset_collate};"; |
| 878 | 999 | } |
| @@ -949,8 +1070,9 @@ | ||
| 949 | 1070 | response_code int(11) NULL, |
| 950 | 1071 | response_message text NULL, |
| 951 | 1072 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 952 | 1073 | PRIMARY KEY (id), |
| 1074 | + KEY idx_url (url(191)), | |
| 953 | 1075 | KEY idx_status (status), |
| 954 | 1076 | KEY idx_response_code (response_code), |
| 955 | 1077 | KEY idx_created (created_at) |
| 956 | 1078 | ) {$charset_collate};"; |
| @@ -1013,8 +1135,9 @@ | ||
| 1013 | 1135 | recipient_hash char(64) NOT NULL, |
| 1014 | 1136 | recipient_count smallint(5) unsigned NOT NULL DEFAULT 1, |
| 1015 | 1137 | frequency_days smallint(5) unsigned NOT NULL DEFAULT 30, |
| 1016 | 1138 | status varchar(20) NOT NULL DEFAULT 'pending', |
| 1139 | + attempts smallint(5) unsigned NOT NULL DEFAULT 1, | |
| 1017 | 1140 | error_message text NULL, |
| 1018 | 1141 | sent_at datetime NULL, |
| 1019 | 1142 | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 1020 | 1143 | PRIMARY KEY (id), |
| @@ -1153,10 +1276,15 @@ | ||
| 1153 | 1276 | if (empty($columns)) { |
| 1154 | 1277 | return ''; |
| 1155 | 1278 | } |
| 1156 | 1279 | |
| 1157 | - // 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). | |
| 1158 | 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 | + | |
| 1159 | 1287 | return "`{$column}`"; |
| 1160 | 1288 | }, $columns); |
| 1161 | 1289 | |
| 1162 | 1290 | $columns_sql = implode(', ', $escaped_columns); |
| @@ -1386,8 +1514,199 @@ | ||
| 1386 | 1514 | return $success; |
| 1387 | 1515 | } |
| 1388 | 1516 | |
| 1389 | 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 | + /** | |
| 1390 | 1709 | * Check if an index exists on a table |
| 1391 | 1710 | * |
| 1392 | 1711 | * @since 1.0.0 |
| 1393 | 1712 | * |
| @@ -1414,34 +1733,29 @@ | ||
| 1414 | 1733 | return (int) $result > 0; |
| 1415 | 1734 | } |
| 1416 | 1735 | |
| 1417 | 1736 | /** |
| 1418 | - * Check if MySQL supports JSON column type with caching | |
| 1737 | + * Check if the database server supports the JSON column type | |
| 1419 | 1738 | * |
| 1420 | - * Uses WordPress's built-in database version detection and caches the result | |
| 1421 | - * 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. | |
| 1422 | 1744 | * |
| 1423 | 1745 | * @since 1.0.0 |
| 1424 | - * @return bool True if MySQL 5.7+ supports JSON columns | |
| 1746 | + * @return bool True if the server supports JSON columns | |
| 1425 | 1747 | */ |
| 1426 | 1748 | private function get_mysql_json_support(): bool { |
| 1427 | - // Check if we have cached result | |
| 1428 | - static $json_support = null; | |
| 1429 | - | |
| 1430 | - if ($json_support !== null) { | |
| 1431 | - return $json_support; | |
| 1432 | - } | |
| 1433 | - | |
| 1434 | - // Use WordPress's built-in database version method | |
| 1435 | 1749 | global $wpdb; |
| 1436 | 1750 | |
| 1437 | - // Get MySQL version using WordPress method (safer than direct query) | |
| 1438 | - $mysql_version = $wpdb->db_version(); | |
| 1751 | + $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : ''; | |
| 1439 | 1752 | |
| 1440 | - // Cache the result for subsequent calls | |
| 1441 | - $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 | + } | |
| 1442 | 1756 | |
| 1443 | - return $json_support; | |
| 1757 | + return version_compare((string) $wpdb->db_version(), '5.7.8', '>='); | |
| 1444 | 1758 | } |
| 1445 | 1759 | |
| 1446 | 1760 | /** |
| 1447 | 1761 | * Get SQL for the AI traffic table. |
| @@ -1469,110 +1783,7 @@ | ||
| 1469 | 1783 | PRIMARY KEY (id), |
| 1470 | 1784 | UNIQUE KEY uniq_bucket (day, kind, source, path), |
| 1471 | 1785 | KEY idx_day (day), |
| 1472 | 1786 | KEY idx_kind (kind) |
| 1473 | - ) {$charset_collate};"; | |
| 1474 | - } | |
| 1475 | - | |
| 1476 | - /** | |
| 1477 | - * Get SQL for the brand visibility checks table. | |
| 1478 | - * | |
| 1479 | - * One row per (query, check run): whether the AI provider's answer | |
| 1480 | - * mentioned the brand and/or cited the site's domain, plus a short | |
| 1481 | - * excerpt for context. | |
| 1482 | - * | |
| 1483 | - * @since 1.27.0 | |
| 1484 | - * | |
| 1485 | - * @param string $table_name Full table name | |
| 1486 | - * @param string $charset_collate Charset and collation | |
| 1487 | - * @return string SQL for table creation | |
| 1488 | - */ | |
| 1489 | - private function get_brand_visibility_checks_table_sql(string $table_name, string $charset_collate): string { | |
| 1490 | - return "CREATE TABLE `{$table_name}` ( | |
| 1491 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1492 | - checked_at datetime NOT NULL, | |
| 1493 | - query_text varchar(191) NOT NULL, | |
| 1494 | - provider varchar(20) NOT NULL DEFAULT '', | |
| 1495 | - model varchar(80) NOT NULL DEFAULT '', | |
| 1496 | - mentioned tinyint(1) NOT NULL DEFAULT 0, | |
| 1497 | - cited tinyint(1) NOT NULL DEFAULT 0, | |
| 1498 | - excerpt text NULL, | |
| 1499 | - answer longtext NULL, | |
| 1500 | - PRIMARY KEY (id), | |
| 1501 | - KEY idx_checked (checked_at), | |
| 1502 | - KEY idx_query (query_text) | |
| 1503 | - ) {$charset_collate};"; | |
| 1504 | - } | |
| 1505 | - | |
| 1506 | - /** | |
| 1507 | - * Brand Visibility v2 — analysis runs. | |
| 1508 | - * | |
| 1509 | - * One row per "Run analysis". `config` snapshots the brand profile, | |
| 1510 | - * competitors, queries and platforms the run was started with, so a run's | |
| 1511 | - * results stay interpretable after the user edits their setup. `results` | |
| 1512 | - * holds the computed aggregates (index, mention rate, share of voice, | |
| 1513 | - * per-platform and per-query breakdowns) written once by the finalizer. | |
| 1514 | - * | |
| 1515 | - * @since 1.28.0 | |
| 1516 | - * | |
| 1517 | - * @param string $table_name Full table name. | |
| 1518 | - * @param string $charset_collate Charset/collation clause. | |
| 1519 | - * @return string CREATE TABLE statement. | |
| 1520 | - */ | |
| 1521 | - private function get_bv_runs_table_sql(string $table_name, string $charset_collate): string { | |
| 1522 | - return "CREATE TABLE `{$table_name}` ( | |
| 1523 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1524 | - status varchar(20) NOT NULL DEFAULT 'queued', | |
| 1525 | - started_at datetime NOT NULL, | |
| 1526 | - finished_at datetime NULL, | |
| 1527 | - tasks_total int(11) NOT NULL DEFAULT 0, | |
| 1528 | - tasks_done int(11) NOT NULL DEFAULT 0, | |
| 1529 | - tasks_failed int(11) NOT NULL DEFAULT 0, | |
| 1530 | - config longtext NULL, | |
| 1531 | - results longtext NULL, | |
| 1532 | - error text NULL, | |
| 1533 | - PRIMARY KEY (id), | |
| 1534 | - KEY idx_status (status), | |
| 1535 | - KEY idx_started (started_at) | |
| 1536 | - ) {$charset_collate};"; | |
| 1537 | - } | |
| 1538 | - | |
| 1539 | - /** | |
| 1540 | - * Brand Visibility v2 — individual probe tasks. | |
| 1541 | - * | |
| 1542 | - * One row per (query x platform x sample). Sampling is the whole point: | |
| 1543 | - * a single LLM answer is noise, so a mention rate is only meaningful as | |
| 1544 | - * mentions/samples. Rows are processed off-request by cron ticks, which is | |
| 1545 | - * what keeps a 100+ call run from timing out a REST request, and what lets | |
| 1546 | - * an interrupted run resume instead of restarting. | |
| 1547 | - * | |
| 1548 | - * @since 1.28.0 | |
| 1549 | - * | |
| 1550 | - * @param string $table_name Full table name. | |
| 1551 | - * @param string $charset_collate Charset/collation clause. | |
| 1552 | - * @return string CREATE TABLE statement. | |
| 1553 | - */ | |
| 1554 | - private function get_bv_tasks_table_sql(string $table_name, string $charset_collate): string { | |
| 1555 | - return "CREATE TABLE `{$table_name}` ( | |
| 1556 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1557 | - run_id bigint(20) unsigned NOT NULL, | |
| 1558 | - query_text varchar(500) NOT NULL, | |
| 1559 | - query_type varchar(20) NOT NULL DEFAULT 'branded', | |
| 1560 | - platform varchar(20) NOT NULL DEFAULT '', | |
| 1561 | - sample_index tinyint(3) unsigned NOT NULL DEFAULT 0, | |
| 1562 | - status varchar(20) NOT NULL DEFAULT 'pending', | |
| 1563 | - attempts tinyint(3) unsigned NOT NULL DEFAULT 0, | |
| 1564 | - mentioned tinyint(1) NOT NULL DEFAULT 0, | |
| 1565 | - cited tinyint(1) NOT NULL DEFAULT 0, | |
| 1566 | - sentiment varchar(10) NOT NULL DEFAULT '', | |
| 1567 | - competitors text NULL, | |
| 1568 | - excerpt text NULL, | |
| 1569 | - answer longtext NULL, | |
| 1570 | - error text NULL, | |
| 1571 | - updated_at datetime NULL, | |
| 1572 | - PRIMARY KEY (id), | |
| 1573 | - KEY idx_run (run_id), | |
| 1574 | - KEY idx_status (status), | |
| 1575 | - KEY run_status (run_id, status) | |
| 1576 | 1787 | ) {$charset_collate};"; |
| 1577 | 1788 | } |
| 1578 | 1789 | } |
