betterdocs / includes / Core / Migration.php

Migration.php in BetterDocs – AI Documentation, Knowledge Base, MCP Server, Docs, Wikis, FAQ & Chatbot 4.9.2, at includes/Core/Migration.php

211 lines 8.0 KB

Raw Download Zip

1	<?php
2	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- one-shot data/schema migrations; caching unwanted.
3	namespace WPDeveloper\BetterDocs\Core;
4
5	if ( ! defined( 'ABSPATH' ) ) {
6	exit;
7	}
8
9	use WP_Query;
10	use WPDeveloper\BetterDocs\Utils\Base;
11	use WPDeveloper\BetterDocs\Utils\Database;
12	use WPDeveloper\BetterDocs\Dependencies\DI\Container;
13
14	class Migration extends Base {
15	/**
16	* Database
17	* @var Database
18	*/
19	private $database;
20	private $settings;
21	private $container;
22
23	public function __construct( Container $container ) {
24	$this->container = $container;
25	$this->database = $container->get( Database::class );
26	$this->settings = $container->get( Settings::class );
27	}
28
29	public function init( $version ) {
30	if ( $version > 250 ) {
31	for ( $_version = 250; $_version <= $version; $_version++ ) {
32	if ( method_exists( $this, "v$_version" ) ) {
33	call_user_func( [ $this, "v$_version" ] );
34	}
35	}
36	}
37
38	$this->search_migration();
39	$this->fix_search_table_collation();
40	$this->backfill_search_keyword_hash();
41
42	/**
43	* Settings Migration
44	*/
45	$this->settings->migration( $version );
46	}
47
48	public function search_migration() {
49	global $wpdb;
50	if ( ! $this->database->get( 'betterdocs_search_data_migration', false ) ) {
51	$search_data = $this->database->get( 'betterdocs_search_data' );
52	if ( ! empty( $search_data ) ) {
53	$search_data_arr = unserialize( $search_data );
54	foreach ( $search_data_arr as $key => $value ) {
55	$args = [
56	'post_type' => 'docs',
57	'post_status' => 'publish',
58	'posts_per_page' => -1,
59	'suppress_filters' => true, // phpcs:ignore WordPressVIPMinimum.Hooks.PreGetPosts.PreGetPosts,WordPressVIPMinimum.Performance.WPQueryParams.SuppressFilters_suppress_filters -- migration must run on raw posts without language filters.
60	's' => $key
61	];
62
63	$loop = new WP_Query( $args );
64	if ( $loop->have_posts() ) {
65	$count = $value;
66	$not_found_count = 0;
67	} else {
68	$count = 0;
69	$not_found_count = $value;
70	}
71
72	// Use BINARY comparison to avoid collation mismatch errors
73	$keyword = $wpdb->get_var(
74	$wpdb->prepare(
75	"
76	SELECT keyword
77	FROM {$wpdb->prefix}betterdocs_search_keyword
78	WHERE BINARY keyword = %s",
79	$key
80	)
81	);
82
83	if ( $keyword == null ) {
84	$insert = $wpdb->query(
85	$wpdb->prepare(
86	"INSERT INTO {$wpdb->prefix}betterdocs_search_keyword
87	( keyword, keyword_hash )
88	VALUES ( %s, %s )",
89	[
90	$key,
91	md5( $key )
92	]
93	)
94	);
95
96	if ( $insert ) {
97	$wpdb->query(
98	$wpdb->prepare(
99	"INSERT INTO {$wpdb->prefix}betterdocs_search_log
100	(keyword_id, count, not_found_count, created_at)
101	VALUES (%d, %d, %d, %s)",
102	[
103	$wpdb->insert_id,
104	$count,
105	$not_found_count,
106	gmdate( 'Y-m-d' )
107	]
108	)
109	);
110	}
111	}
112	}
113
114	$this->database->save( 'betterdocs_search_data_migration', '1.0' );
115	}
116	}
117	}
118
119	/**
120	* Fix collation issues in search tables
121	* Converts tables to proper UTF-8 charset and collation to prevent
122	* "Illegal mix of collations" errors when searching with non-Latin characters
123	*
124	* @since 1.0.2
125	* @return void
126	*/
127	public function fix_search_table_collation() {
128	global $wpdb;
129
130	// Check if migration already ran
131	if ( $this->database->get( 'betterdocs_search_collation_fixed', false ) ) {
132	return;
133	}
134
135	// Get the WordPress default charset and collation; restrict to a safe identifier
136	// alphabet because they're interpolated into ALTER TABLE statements below.
137	$charset = preg_replace( '/[^A-Za-z0-9_]/', '', $wpdb->charset ? $wpdb->charset : 'utf8mb4' );
138	$collate = preg_replace( '/[^A-Za-z0-9_]/', '', $wpdb->collate ? $wpdb->collate : 'utf8mb4_unicode_520_ci' );
139
140	// Fix search_keyword table
141	$search_keyword_table = $wpdb->prefix . 'betterdocs_search_keyword';
142	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange -- one-shot collation migration; identifiers come from $wpdb only.
143	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $search_keyword_table ) ) === $search_keyword_table ) {
144	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
145	$wpdb->query( "ALTER TABLE `{$search_keyword_table}` CONVERT TO CHARACTER SET {$charset} COLLATE {$collate}" );
146	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
147	$wpdb->query( "ALTER TABLE `{$search_keyword_table}` MODIFY keyword TEXT CHARACTER SET {$charset} COLLATE {$collate} NOT NULL" );
148	}
149
150	// Fix search_log table
151	$search_log_table = $wpdb->prefix . 'betterdocs_search_log';
152	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
153	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $search_log_table ) ) === $search_log_table ) {
154	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
155	$wpdb->query( "ALTER TABLE `{$search_log_table}` CONVERT TO CHARACTER SET {$charset} COLLATE {$collate}" );
156	}
157
158	// Mark migration as complete
159	$this->database->save( 'betterdocs_search_collation_fixed', '1.0' );
160	}
161
162	/**
163	* Populate keyword_hash for rows that predate the column.
164	*
165	* The column is added by dbDelta with an empty default; until it holds the
166	* hash, insert_search_keyword() cannot find those rows by index and would
167	* insert duplicates. Runs in bounded batches so a large keyword table does
168	* not stall the request that triggers the upgrade — anything left over is
169	* picked up on the next admin load.
170	*
171	* @since 1.0.3
172	* @return void
173	*/
174	public function backfill_search_keyword_hash() {
175	global $wpdb;
176
177	if ( $this->database->get( 'betterdocs_search_keyword_hash_filled', false ) ) {
178	return;
179	}
180
181	$table = $wpdb->prefix . 'betterdocs_search_keyword';
182
183	// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- one-shot schema backfill; identifier comes from $wpdb.
184	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
185	return;
186	}
187
188	// Bail if the column never made it (dbDelta failed) — retry next load.
189	if ( ! $wpdb->get_results( "SHOW COLUMNS FROM `{$table}` LIKE 'keyword_hash'" ) ) {
190	return;
191	}
192
193	$batches = 0;
194	do {
195	$updated = $wpdb->query(
196	"UPDATE `{$table}` SET keyword_hash = MD5( keyword ) WHERE keyword_hash = '' LIMIT 2000"
197	);
198	$batches++;
199	} while ( $updated > 0 && $batches < 25 );
200
201	// Only finish once nothing is left, so a table larger than 50k keywords
202	// resumes instead of being marked done half-filled.
203	$remaining = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}` WHERE keyword_hash = ''" );
204	// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
205
206	if ( 0 === $remaining ) {
207	$this->database->save( 'betterdocs_search_keyword_hash_filled', '1.0.3' );
208	}
209	}
210	}
211