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

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