← All changes | includes/database/class-database-schema.php +32 -155 2.0.2 → 2.7.0 View file →

		@@ -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.
		@@ -239,35 +244,14 @@
239	244	],
240	245	'foreign_keys' => []
241	246	],
242	247
243		- // === AI VISIBILITY TABLES (2) ===
	248	+ // === AI VISIBILITY TABLES (1) ===
244	249	'ai_traffic' => [
245	250	'description' => 'Daily aggregate counters for AI referral traffic, AI crawler hits, and the all-traffic baseline',
246	251	'primary_key' => 'id',
247	252	'indexes' => ['day', 'kind'],
248	253	'foreign_keys' => []
249		- ],
250		- 'brand_visibility_checks' => [
251		- 'description' => 'History of AI brand-visibility checks run through the configured AI provider',
252		- 'primary_key' => 'id',
253		- 'indexes' => ['checked_at', 'query_text'],
254		- 'foreign_keys' => []
255		- ],
256		- 'bv_runs' => [
257		- 'description' => 'Brand Visibility v2 analysis runs: one row per run, with its config snapshot, progress counters and computed aggregates',
258		- 'primary_key' => 'id',
259		- 'indexes' => ['status', 'started_at', 'finished_at'],
260		- 'foreign_keys' => []
261		- ],
262		- 'bv_tasks' => [
263		- 'description' => 'Brand Visibility v2 units of work: one row per query x platform x sample, processed off-request by cron ticks',
264		- 'primary_key' => 'id',
265		- 'indexes' => ['run_id', 'status'],
266		- 'composite_indexes' => [
267		- 'run_status' => ['run_id', 'status'],
268		- ],
269		- 'foreign_keys' => []
270	254	]
271	255	];
272	256
273	257	/**
		@@ -289,9 +273,9 @@
289	273	'ai' => ['ai_cache', 'ai_usage'],
290	274	'content' => ['content_briefs'],
291	275	'scoring' => ['seo_scores'],
292	276	'reporting' => ['email_report_logs'],
293		- 'ai_visibility' => ['ai_traffic', 'brand_visibility_checks', 'bv_runs', 'bv_tasks']
	277	+ 'ai_visibility' => ['ai_traffic']
294	278	];
295	279
296	280	/**
297	281	* Constructor
		@@ -341,8 +325,14 @@
341	325
342	326	// Create table using dbDelta for WordPress compatibility
343	327	$result = dbDelta($sql);
344	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	+
345	335	// Verify table creation
346	336	if ($this->table_exists($full_table_name)) {
347	337	$results['tables_created'][] = $full_table_name;
348	338
		@@ -351,12 +341,8 @@
351	341
352	342	// Add constraints if needed
353	343	$this->add_table_constraints($table_name);
354	344	} else {
355		- // dbDelta reports nothing when the database refuses the
356		- // statement, so wpdb's own error is the only account of why.
357		- $db_error = (string) $this->wpdb->last_error;
358		-
359	345	$results['tables_failed'][] = $full_table_name;
360	346	$results['errors'][] = "Failed to create table: {$full_table_name}"
361	347	. ('' !== $db_error ? ' — ' . $db_error : '');
362	348	$results['success'] = false;
		@@ -679,10 +665,16 @@
679	665	* @throws \InvalidArgumentException On failure.
680	666	*/
681	667	private function get_table_sql(string $table_name): string {
682	668	$full_table_name = $this->get_table_name($table_name);
683		- $charset_collate = $this->db_config['charset_collate'];
684	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	+
685	677	switch ($table_name) {
686	678	// SEO Tables
687	679	case 'seo_settings':
688	680	return $this->get_seo_settings_table_sql($full_table_name, $charset_collate);
		@@ -715,14 +707,8 @@
715	707
716	708	// AI Visibility Tables
717	709	case 'ai_traffic':
718	710	return $this->get_ai_traffic_table_sql($full_table_name, $charset_collate);
719		- case 'bv_runs':
720		- return $this->get_bv_runs_table_sql($full_table_name, $charset_collate);
721		- case 'bv_tasks':
722		- return $this->get_bv_tasks_table_sql($full_table_name, $charset_collate);
723		- case 'brand_visibility_checks':
724		- return $this->get_brand_visibility_checks_table_sql($full_table_name, $charset_collate);
725	711
726	712	default:
727	713	throw new \InvalidArgumentException('Unknown table: ' . esc_html($table_name));
728	714	}
		@@ -1747,34 +1733,29 @@
1747	1733	return (int) $result > 0;
1748	1734	}
1749	1735
1750	1736	/**
1751		- * Check if MySQL supports JSON column type with caching
	1737	+ * Check if the database server supports the JSON column type
1752	1738	*
1753		- * Uses WordPress's built-in database version detection and caches the result
1754		- * 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.
1755	1744	*
1756	1745	* @since 1.0.0
1757		- * @return bool True if MySQL 5.7+ supports JSON columns
	1746	+ * @return bool True if the server supports JSON columns
1758	1747	*/
1759	1748	private function get_mysql_json_support(): bool {
1760		- // Check if we have cached result
1761		- static $json_support = null;
1762		-
1763		- if ($json_support !== null) {
1764		- return $json_support;
1765		- }
1766		-
1767		- // Use WordPress's built-in database version method
1768	1749	global $wpdb;
1769	1750
1770		- // Get MySQL version using WordPress method (safer than direct query)
1771		- $mysql_version = $wpdb->db_version();
	1751	+ $server_info = method_exists($wpdb, 'db_server_info') ? (string) $wpdb->db_server_info() : '';
1772	1752
1773		- // Cache the result for subsequent calls
1774		- $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	+ }
1775	1756
1776		- return $json_support;
	1757	+ return version_compare((string) $wpdb->db_version(), '5.7.8', '>=');
1777	1758	}
1778	1759
1779	1760	/**
1780	1761	* Get SQL for the AI traffic table.
		@@ -1802,111 +1783,7 @@
1802	1783	PRIMARY KEY (id),
1803	1784	UNIQUE KEY uniq_bucket (day, kind, source, path),
1804	1785	KEY idx_day (day),
1805	1786	KEY idx_kind (kind)
1806		- ) {$charset_collate};";
1807		- }
1808		-
1809		- /**
1810		- * Get SQL for the brand visibility checks table.
1811		- *
1812		- * One row per (query, check run): whether the AI provider's answer
1813		- * mentioned the brand and/or cited the site's domain, plus a short
1814		- * excerpt for context.
1815		- *
1816		- * @since 1.27.0
1817		- *
1818		- * @param string $table_name Full table name
1819		- * @param string $charset_collate Charset and collation
1820		- * @return string SQL for table creation
1821		- */
1822		- private function get_brand_visibility_checks_table_sql(string $table_name, string $charset_collate): string {
1823		- return "CREATE TABLE `{$table_name}` (
1824		- id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
1825		- checked_at datetime NOT NULL,
1826		- query_text varchar(191) NOT NULL,
1827		- provider varchar(20) NOT NULL DEFAULT '',
1828		- model varchar(80) NOT NULL DEFAULT '',
1829		- mentioned tinyint(1) NOT NULL DEFAULT 0,
1830		- cited tinyint(1) NOT NULL DEFAULT 0,
1831		- excerpt text NULL,
1832		- answer longtext NULL,
1833		- PRIMARY KEY (id),
1834		- KEY idx_checked (checked_at),
1835		- KEY idx_query (query_text)
1836		- ) {$charset_collate};";
1837		- }
1838		-
1839		- /**
1840		- * Brand Visibility v2 — analysis runs.
1841		- *
1842		- * One row per "Run analysis". `config` snapshots the brand profile,
1843		- * competitors, queries and platforms the run was started with, so a run's
1844		- * results stay interpretable after the user edits their setup. `results`
1845		- * holds the computed aggregates (index, mention rate, share of voice,
1846		- * per-platform and per-query breakdowns) written once by the finalizer.
1847		- *
1848		- * @since 1.28.0
1849		- *
1850		- * @param string $table_name Full table name.
1851		- * @param string $charset_collate Charset/collation clause.
1852		- * @return string CREATE TABLE statement.
1853		- */
1854		- private function get_bv_runs_table_sql(string $table_name, string $charset_collate): string {
1855		- return "CREATE TABLE `{$table_name}` (
1856		- id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
1857		- status varchar(20) NOT NULL DEFAULT 'queued',
1858		- started_at datetime NOT NULL,
1859		- finished_at datetime NULL,
1860		- tasks_total int(11) NOT NULL DEFAULT 0,
1861		- tasks_done int(11) NOT NULL DEFAULT 0,
1862		- tasks_failed int(11) NOT NULL DEFAULT 0,
1863		- config longtext NULL,
1864		- results longtext NULL,
1865		- error text NULL,
1866		- PRIMARY KEY (id),
1867		- KEY idx_status (status),
1868		- KEY idx_started (started_at),
1869		- KEY idx_finished (finished_at)
1870		- ) {$charset_collate};";
1871		- }
1872		-
1873		- /**
1874		- * Brand Visibility v2 — individual probe tasks.
1875		- *
1876		- * One row per (query x platform x sample). Sampling is the whole point:
1877		- * a single LLM answer is noise, so a mention rate is only meaningful as
1878		- * mentions/samples. Rows are processed off-request by cron ticks, which is
1879		- * what keeps a 100+ call run from timing out a REST request, and what lets
1880		- * an interrupted run resume instead of restarting.
1881		- *
1882		- * @since 1.28.0
1883		- *
1884		- * @param string $table_name Full table name.
1885		- * @param string $charset_collate Charset/collation clause.
1886		- * @return string CREATE TABLE statement.
1887		- */
1888		- private function get_bv_tasks_table_sql(string $table_name, string $charset_collate): string {
1889		- return "CREATE TABLE `{$table_name}` (
1890		- id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
1891		- run_id bigint(20) unsigned NOT NULL,
1892		- query_text varchar(500) NOT NULL,
1893		- query_type varchar(20) NOT NULL DEFAULT 'branded',
1894		- platform varchar(20) NOT NULL DEFAULT '',
1895		- sample_index tinyint(3) unsigned NOT NULL DEFAULT 0,
1896		- status varchar(20) NOT NULL DEFAULT 'pending',
1897		- attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
1898		- mentioned tinyint(1) NOT NULL DEFAULT 0,
1899		- cited tinyint(1) NOT NULL DEFAULT 0,
1900		- sentiment varchar(10) NOT NULL DEFAULT '',
1901		- competitors text NULL,
1902		- excerpt text NULL,
1903		- answer longtext NULL,
1904		- error text NULL,
1905		- updated_at datetime NULL,
1906		- PRIMARY KEY (id),
1907		- KEY idx_run (run_id),
1908		- KEY idx_status (status),
1909		- KEY run_status (run_id, status)
1910	1787	) {$charset_collate};";
1911	1788	}
1912	1789	}