| @@ -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,9 +48,9 @@ | ||
| 43 | 48 | * |
| 44 | 49 | * @since 1.0.0 |
| 45 | 50 | * @var string |
| 46 | 51 | */ |
| 47 | - private string $db_version = '1.9.0'; | |
| 52 | + private string $db_version = '1.9.2'; | |
| 48 | 53 | |
| 49 | 54 | /** |
| 50 | 55 | * Widest single indexed COLUMN InnoDB accepts on a COMPACT/REDUNDANT row |
| 51 | 56 | * format, in bytes. |
| @@ -93,8 +98,34 @@ | ||
| 93 | 98 | */ |
| 94 | 99 | private const TABLES_VERIFIED_TRANSIENT = 'thinkrank_schema_verified'; |
| 95 | 100 | |
| 96 | 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 | + /** | |
| 97 | 128 | * Database table definitions with specifications |
| 98 | 129 | * |
| 99 | 130 | * Consolidated table definitions for all ThinkRank tables (11 total): |
| 100 | 131 | * - SEO Tables (7): Core SEO functionality with context-aware structure |
| @@ -213,35 +244,14 @@ | ||
| 213 | 244 | ], |
| 214 | 245 | 'foreign_keys' => [] |
| 215 | 246 | ], |
| 216 | 247 | |
| 217 | - // === AI VISIBILITY TABLES (2) === | |
| 248 | + // === AI VISIBILITY TABLES (1) === | |
| 218 | 249 | 'ai_traffic' => [ |
| 219 | 250 | 'description' => 'Daily aggregate counters for AI referral traffic, AI crawler hits, and the all-traffic baseline', |
| 220 | 251 | 'primary_key' => 'id', |
| 221 | 252 | 'indexes' => ['day', 'kind'], |
| 222 | 253 | 'foreign_keys' => [] |
| 223 | - ], | |
| 224 | - 'brand_visibility_checks' => [ | |
| 225 | - 'description' => 'History of AI brand-visibility checks run through the configured AI provider', | |
| 226 | - 'primary_key' => 'id', | |
| 227 | - 'indexes' => ['checked_at', 'query_text'], | |
| 228 | - 'foreign_keys' => [] | |
| 229 | - ], | |
| 230 | - 'bv_runs' => [ | |
| 231 | - 'description' => 'Brand Visibility v2 analysis runs: one row per run, with its config snapshot, progress counters and computed aggregates', | |
| 232 | - 'primary_key' => 'id', | |
| 233 | - 'indexes' => ['status', 'started_at', 'finished_at'], | |
| 234 | - 'foreign_keys' => [] | |
| 235 | - ], | |
| 236 | - 'bv_tasks' => [ | |
| 237 | - 'description' => 'Brand Visibility v2 units of work: one row per query x platform x sample, processed off-request by cron ticks', | |
| 238 | - 'primary_key' => 'id', | |
| 239 | - 'indexes' => ['run_id', 'status'], | |
| 240 | - 'composite_indexes' => [ | |
| 241 | - 'run_status' => ['run_id', 'status'], | |
| 242 | - ], | |
| 243 | - 'foreign_keys' => [] | |
| 244 | 254 | ] |
| 245 | 255 | ]; |
| 246 | 256 | |
| 247 | 257 | /** |
| @@ -263,9 +273,9 @@ | ||
| 263 | 273 | 'ai' => ['ai_cache', 'ai_usage'], |
| 264 | 274 | 'content' => ['content_briefs'], |
| 265 | 275 | 'scoring' => ['seo_scores'], |
| 266 | 276 | 'reporting' => ['email_report_logs'], |
| 267 | - 'ai_visibility' => ['ai_traffic', 'brand_visibility_checks', 'bv_runs', 'bv_tasks'] | |
| 277 | + 'ai_visibility' => ['ai_traffic'] | |
| 268 | 278 | ]; |
| 269 | 279 | |
| 270 | 280 | /** |
| 271 | 281 | * Constructor |
| @@ -276,12 +286,16 @@ | ||
| 276 | 286 | global $wpdb; |
| 277 | 287 | $this->wpdb = $wpdb; |
| 278 | 288 | |
| 279 | 289 | // Set database configuration |
| 280 | - $this->db_config = [ | |
| 281 | - 'charset' => $wpdb->charset ?: 'utf8mb4', | |
| 282 | - 'collate' => $wpdb->collate ?: 'utf8mb4_unicode_ci' | |
| 283 | - ]; | |
| 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()]; | |
| 284 | 298 | } |
| 285 | 299 | |
| 286 | 300 | /** |
| 287 | 301 | * Create all database tables |
| @@ -311,8 +325,14 @@ | ||
| 311 | 325 | |
| 312 | 326 | // Create table using dbDelta for WordPress compatibility |
| 313 | 327 | $result = dbDelta($sql); |
| 314 | 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 | + | |
| 315 | 335 | // Verify table creation |
| 316 | 336 | if ($this->table_exists($full_table_name)) { |
| 317 | 337 | $results['tables_created'][] = $full_table_name; |
| 318 | 338 | |
| @@ -322,9 +342,10 @@ | ||
| 322 | 342 | // Add constraints if needed |
| 323 | 343 | $this->add_table_constraints($table_name); |
| 324 | 344 | } else { |
| 325 | 345 | $results['tables_failed'][] = $full_table_name; |
| 326 | - $results['errors'][] = "Failed to create table: {$full_table_name}"; | |
| 346 | + $results['errors'][] = "Failed to create table: {$full_table_name}" | |
| 347 | + . ('' !== $db_error ? ' — ' . $db_error : ''); | |
| 327 | 348 | $results['success'] = false; |
| 328 | 349 | } |
| 329 | 350 | } catch (\Exception $e) { |
| 330 | 351 | $results['tables_failed'][] = $this->get_table_name($table_name); |
| @@ -336,12 +357,21 @@ | ||
| 336 | 357 | // Retire the pre-#298 full-width keys now that their prefixed |
| 337 | 358 | // replacements are in place. |
| 338 | 359 | $this->drop_replaced_wide_indexes(); |
| 339 | 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 | + | |
| 340 | 367 | // Update database version |
| 341 | 368 | if ($results['success']) { |
| 342 | 369 | update_option('thinkrank_seo_db_version', $this->db_version); |
| 343 | 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']); | |
| 344 | 374 | } |
| 345 | 375 | |
| 346 | 376 | // The schema just changed, so any cached "verified complete" answer is |
| 347 | 377 | // stale either way — drop it and let the next probe re-check. |
| @@ -350,8 +380,49 @@ | ||
| 350 | 380 | return $results; |
| 351 | 381 | } |
| 352 | 382 | |
| 353 | 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 | + /** | |
| 354 | 425 | * Drop all database tables |
| 355 | 426 | * |
| 356 | 427 | * @since 1.0.0 |
| 357 | 428 | * |
| @@ -594,10 +665,16 @@ | ||
| 594 | 665 | * @throws \InvalidArgumentException On failure. |
| 595 | 666 | */ |
| 596 | 667 | private function get_table_sql(string $table_name): string { |
| 597 | 668 | $full_table_name = $this->get_table_name($table_name); |
| 598 | - $charset_collate = "DEFAULT CHARACTER SET {$this->db_config['charset']} COLLATE {$this->db_config['collate']}"; | |
| 599 | 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 | + | |
| 600 | 677 | switch ($table_name) { |
| 601 | 678 | // SEO Tables |
| 602 | 679 | case 'seo_settings': |
| 603 | 680 | return $this->get_seo_settings_table_sql($full_table_name, $charset_collate); |
| @@ -630,14 +707,8 @@ | ||
| 630 | 707 | |
| 631 | 708 | // AI Visibility Tables |
| 632 | 709 | case 'ai_traffic': |
| 633 | 710 | return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate); |
| 634 | - case 'bv_runs': | |
| 635 | - return $this->get_bv_runs_table_sql($full_table_name, $charset_collate); | |
| 636 | - case 'bv_tasks': | |
| 637 | - return $this->get_bv_tasks_table_sql($full_table_name, $charset_collate); | |
| 638 | - case 'brand_visibility_checks': | |
| 639 | - return $this->get_brand_visibility_checks_table_sql($full_table_name, $charset_collate); | |
| 640 | 711 | |
| 641 | 712 | default: |
| 642 | 713 | throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name)); |
| 643 | 714 | } |
| @@ -1457,8 +1528,156 @@ | ||
| 1457 | 1528 | * @since 1.30.0 |
| 1458 | 1529 | * |
| 1459 | 1530 | * @return void |
| 1460 | 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 | + | |
| 1461 | 1680 | private function drop_replaced_wide_indexes(): void { |
| 1462 | 1681 | foreach (self::REPLACED_WIDE_INDEXES as $table => $renames) { |
| 1463 | 1682 | $full_table_name = $this->get_table_name($table); |
| 1464 | 1683 | |
| @@ -1514,34 +1733,29 @@ | ||
| 1514 | 1733 | return (int) $result > 0; |
| 1515 | 1734 | } |
| 1516 | 1735 | |
| 1517 | 1736 | /** |
| 1518 | - * Check if MySQL supports JSON column type with caching | |
| 1737 | + * Check if the database server supports the JSON column type | |
| 1519 | 1738 | * |
| 1520 | - * Uses WordPress's built-in database version detection and caches the result | |
| 1521 | - * 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. | |
| 1522 | 1744 | * |
| 1523 | 1745 | * @since 1.0.0 |
| 1524 | - * @return bool True if MySQL 5.7+ supports JSON columns | |
| 1746 | + * @return bool True if the server supports JSON columns | |
| 1525 | 1747 | */ |
| 1526 | 1748 | private function get_mysql_json_support(): bool { |
| 1527 | - // Check if we have cached result | |
| 1528 | - static $json_support = null; | |
| 1529 | - | |
| 1530 | - if ($json_support !== null) { | |
| 1531 | - return $json_support; | |
| 1532 | - } | |
| 1533 | - | |
| 1534 | - // Use WordPress's built-in database version method | |
| 1535 | 1749 | global $wpdb; |
| 1536 | 1750 | |
| 1537 | - // Get MySQL version using WordPress method (safer than direct query) | |
| 1538 | - $mysql_version = $wpdb->db_version(); | |
| 1751 | + $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : ''; | |
| 1539 | 1752 | |
| 1540 | - // Cache the result for subsequent calls | |
| 1541 | - $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 | + } | |
| 1542 | 1756 | |
| 1543 | - return $json_support; | |
| 1757 | + return version_compare((string) $wpdb->db_version(), '5.7.8', '>='); | |
| 1544 | 1758 | } |
| 1545 | 1759 | |
| 1546 | 1760 | /** |
| 1547 | 1761 | * Get SQL for the AI traffic table. |
| @@ -1569,111 +1783,7 @@ | ||
| 1569 | 1783 | PRIMARY KEY (id), |
| 1570 | 1784 | UNIQUE KEY uniq_bucket (day, kind, source, path), |
| 1571 | 1785 | KEY idx_day (day), |
| 1572 | 1786 | KEY idx_kind (kind) |
| 1573 | - ) {$charset_collate};"; | |
| 1574 | - } | |
| 1575 | - | |
| 1576 | - /** | |
| 1577 | - * Get SQL for the brand visibility checks table. | |
| 1578 | - * | |
| 1579 | - * One row per (query, check run): whether the AI provider's answer | |
| 1580 | - * mentioned the brand and/or cited the site's domain, plus a short | |
| 1581 | - * excerpt for context. | |
| 1582 | - * | |
| 1583 | - * @since 1.27.0 | |
| 1584 | - * | |
| 1585 | - * @param string $table_name Full table name | |
| 1586 | - * @param string $charset_collate Charset and collation | |
| 1587 | - * @return string SQL for table creation | |
| 1588 | - */ | |
| 1589 | - private function get_brand_visibility_checks_table_sql(string $table_name, string $charset_collate): string { | |
| 1590 | - return "CREATE TABLE `{$table_name}` ( | |
| 1591 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1592 | - checked_at datetime NOT NULL, | |
| 1593 | - query_text varchar(191) NOT NULL, | |
| 1594 | - provider varchar(20) NOT NULL DEFAULT '', | |
| 1595 | - model varchar(80) NOT NULL DEFAULT '', | |
| 1596 | - mentioned tinyint(1) NOT NULL DEFAULT 0, | |
| 1597 | - cited tinyint(1) NOT NULL DEFAULT 0, | |
| 1598 | - excerpt text NULL, | |
| 1599 | - answer longtext NULL, | |
| 1600 | - PRIMARY KEY (id), | |
| 1601 | - KEY idx_checked (checked_at), | |
| 1602 | - KEY idx_query (query_text) | |
| 1603 | - ) {$charset_collate};"; | |
| 1604 | - } | |
| 1605 | - | |
| 1606 | - /** | |
| 1607 | - * Brand Visibility v2 — analysis runs. | |
| 1608 | - * | |
| 1609 | - * One row per "Run analysis". `config` snapshots the brand profile, | |
| 1610 | - * competitors, queries and platforms the run was started with, so a run's | |
| 1611 | - * results stay interpretable after the user edits their setup. `results` | |
| 1612 | - * holds the computed aggregates (index, mention rate, share of voice, | |
| 1613 | - * per-platform and per-query breakdowns) written once by the finalizer. | |
| 1614 | - * | |
| 1615 | - * @since 1.28.0 | |
| 1616 | - * | |
| 1617 | - * @param string $table_name Full table name. | |
| 1618 | - * @param string $charset_collate Charset/collation clause. | |
| 1619 | - * @return string CREATE TABLE statement. | |
| 1620 | - */ | |
| 1621 | - private function get_bv_runs_table_sql(string $table_name, string $charset_collate): string { | |
| 1622 | - return "CREATE TABLE `{$table_name}` ( | |
| 1623 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1624 | - status varchar(20) NOT NULL DEFAULT 'queued', | |
| 1625 | - started_at datetime NOT NULL, | |
| 1626 | - finished_at datetime NULL, | |
| 1627 | - tasks_total int(11) NOT NULL DEFAULT 0, | |
| 1628 | - tasks_done int(11) NOT NULL DEFAULT 0, | |
| 1629 | - tasks_failed int(11) NOT NULL DEFAULT 0, | |
| 1630 | - config longtext NULL, | |
| 1631 | - results longtext NULL, | |
| 1632 | - error text NULL, | |
| 1633 | - PRIMARY KEY (id), | |
| 1634 | - KEY idx_status (status), | |
| 1635 | - KEY idx_started (started_at), | |
| 1636 | - KEY idx_finished (finished_at) | |
| 1637 | - ) {$charset_collate};"; | |
| 1638 | - } | |
| 1639 | - | |
| 1640 | - /** | |
| 1641 | - * Brand Visibility v2 — individual probe tasks. | |
| 1642 | - * | |
| 1643 | - * One row per (query x platform x sample). Sampling is the whole point: | |
| 1644 | - * a single LLM answer is noise, so a mention rate is only meaningful as | |
| 1645 | - * mentions/samples. Rows are processed off-request by cron ticks, which is | |
| 1646 | - * what keeps a 100+ call run from timing out a REST request, and what lets | |
| 1647 | - * an interrupted run resume instead of restarting. | |
| 1648 | - * | |
| 1649 | - * @since 1.28.0 | |
| 1650 | - * | |
| 1651 | - * @param string $table_name Full table name. | |
| 1652 | - * @param string $charset_collate Charset/collation clause. | |
| 1653 | - * @return string CREATE TABLE statement. | |
| 1654 | - */ | |
| 1655 | - private function get_bv_tasks_table_sql(string $table_name, string $charset_collate): string { | |
| 1656 | - return "CREATE TABLE `{$table_name}` ( | |
| 1657 | - id bigint(20) unsigned NOT NULL AUTO_INCREMENT, | |
| 1658 | - run_id bigint(20) unsigned NOT NULL, | |
| 1659 | - query_text varchar(500) NOT NULL, | |
| 1660 | - query_type varchar(20) NOT NULL DEFAULT 'branded', | |
| 1661 | - platform varchar(20) NOT NULL DEFAULT '', | |
| 1662 | - sample_index tinyint(3) unsigned NOT NULL DEFAULT 0, | |
| 1663 | - status varchar(20) NOT NULL DEFAULT 'pending', | |
| 1664 | - attempts tinyint(3) unsigned NOT NULL DEFAULT 0, | |
| 1665 | - mentioned tinyint(1) NOT NULL DEFAULT 0, | |
| 1666 | - cited tinyint(1) NOT NULL DEFAULT 0, | |
| 1667 | - sentiment varchar(10) NOT NULL DEFAULT '', | |
| 1668 | - competitors text NULL, | |
| 1669 | - excerpt text NULL, | |
| 1670 | - answer longtext NULL, | |
| 1671 | - error text NULL, | |
| 1672 | - updated_at datetime NULL, | |
| 1673 | - PRIMARY KEY (id), | |
| 1674 | - KEY idx_run (run_id), | |
| 1675 | - KEY idx_status (status), | |
| 1676 | - KEY run_status (run_id, status) | |
| 1677 | 1787 | ) {$charset_collate};"; |
| 1678 | 1788 | } |
| 1679 | 1789 | } |
