metasync / redirections / class-metasync-redirection-database.php

class-metasync-redirection-database.php in Search Atlas SEO – OTTO AI SEO Automation for WordPress 2.7.2, at redirections/class-metasync-redirection-database.php

421 lines 18.8 KB

Raw Download Zip

1	<?php
2
3	/**
4	* The database operations for the redirections.
5	*
6	* @since 1.0.0
7	* @package Metasync
8	* @subpackage Metasync/redirections
9	* @author Engineering Team <[email protected]>
10	*/
11
12	if (!defined('ABSPATH')) {
13	exit;
14	}
15
16	if (!class_exists('Metasync_Redirection_Database')) {
17	class Metasync_Redirection_Database
18	{
19	public static $table_name = "metasync_redirections";
20	private static $structure_verified = false;
21
22	private function get_table_name()
23	{
24	global $wpdb;
25	return $wpdb->prefix . self::$table_name;
26	}
27
28	/**
29	* Ensure table structure is up to date
30	*/
31	private function ensure_table_structure()
32	{
33	// Run schema inspection at most once per request — table schema only changes on activation/upgrade, handled by class-db-migrations.php.
34	if (self::$structure_verified) { return; }
35	self::$structure_verified = true;
36
37	global $wpdb;
38	$table_name = $this->get_table_name();
39
40	// Check if table exists
41	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
42	// Table doesn't exist, run full migration
43	require_once dirname(__FILE__, 2) . '/database/class-db-migrations.php';
44	MetaSync_DBMigration::activation();
45	return;
46	}
47
48	// Check if required columns exist
49	$columns = $wpdb->get_col("DESCRIBE {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — column introspection, admin write paths only, once per request
50
51	if (!in_array('pattern_type', $columns)) {
52	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact' AFTER status"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
53	}
54	if (!in_array('regex_pattern', $columns)) {
55	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN regex_pattern TEXT NULL AFTER pattern_type"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
56	}
57	if (!in_array('description', $columns)) {
58	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN description TEXT NULL AFTER regex_pattern"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
59	}
60	if (!in_array('created_at', $columns)) {
61	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER description"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
62	}
63	if (!in_array('updated_at', $columns)) {
64	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER created_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
65	}
66	if (!in_array('last_accessed_at', $columns)) {
67	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD COLUMN last_accessed_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER updated_at"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
68	}
69
70	// Widen pattern_type for installs whose column predates wildcard
71	// matching — column-exists checks never re-ALTER, so the enum would
72	// otherwise keep silently rejecting wildcard rows.
73	if (in_array('pattern_type', $columns)) {
74	$column_info = $wpdb->get_row($wpdb->prepare("SHOW COLUMNS FROM {$wpdb->prefix}metasync_redirections LIKE %s", 'pattern_type')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — enum widening for pre-wildcard installs, admin write paths only
75	if ($column_info && strpos((string) $column_info->Type, 'wildcard') === false) {
76	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections MODIFY COLUMN pattern_type ENUM('exact', 'contain', 'start', 'end', 'regex', 'wildcard') NOT NULL DEFAULT 'exact'"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — enum widening for pre-wildcard installs, admin write paths only
77	}
78	}
79
80	// Check and add indexes
81	$indexes = $wpdb->get_results("SHOW INDEX FROM {$wpdb->prefix}metasync_redirections"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — index introspection, admin write paths only, once per request
82	$index_names = array_column($indexes, 'Key_name');
83
84	if (!in_array('pattern_type', $index_names)) {
85	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY pattern_type (pattern_type)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
86	}
87	if (!in_array('created_at', $index_names)) {
88	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY created_at (created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
89	}
90	if (!in_array('status_created', $index_names)) {
91	$wpdb->query("ALTER TABLE {$wpdb->prefix}metasync_redirections ADD KEY status_created (status, created_at)"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop for installs that missed a migration; admin write paths only, once per request via the $structure_verified gate
92	}
93
94	// Set default pattern_type for existing records
95	$wpdb->query("UPDATE {$wpdb->prefix}metasync_redirections SET pattern_type = 'exact' WHERE pattern_type IS NULL OR pattern_type = ''"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- self-healing schema backstop — one-time backfill bound to the column additions above
96	}
97
98	/**
99	* Manually trigger table structure update
100	* Can be called from admin or via AJAX if needed
101	*/
102	public function force_table_update()
103	{
104	self::$structure_verified = false;
105	$this->ensure_table_structure();
106	return true;
107	}
108
109	public function getAllRecords()
110	{
111	global $wpdb;
112	return $wpdb->get_results("SELECT * FROM `{$wpdb->prefix}metasync_redirections`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
113	}
114
115	public function getAllActiveRecords()
116	{
117	global $wpdb;
118
119	// Check cache first
120	$cache_key = 'metasync_active_redirections';
121	$cached_redirections = wp_cache_get($cache_key, 'metasync');
122
123	if ($cached_redirections !== false) {
124	return $cached_redirections;
125	}
126
127	// id tiebreaker: created_at has one-second resolution, so rows created in
128	// the same second (bulk import, migrations) would come back in arbitrary
129	// order — and the engine's first-match-wins scan would then be
130	// nondeterministic between requests.
131	$redirections = $wpdb->get_results($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE status = %s ORDER BY created_at DESC, id DESC", 'active')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — this read is the cache SOURCE for the object-cached active set (invalidated on every write)
132
133	// Cache for 1 hour
134	wp_cache_set($cache_key, $redirections, 'metasync', HOUR_IN_SECONDS);
135
136	return $redirections;
137	}
138
139	/**
140	* Find a single redirection by ID
141	* @param int $id The redirection ID
142	* @return object\|null The redirection record or null if not found
143	*/
144	public function find($id)
145	{
146	global $wpdb;
147	return $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE id = %d", intval($id))); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
148	}
149
150	/**
151	* Add a record.
152	* @param array $args Values to insert.
153	*/
154	public function add($args)
155	{
156	global $wpdb;
157
158	// Ensure table structure is up to date
159	$this->ensure_table_structure();
160
161	$args = wp_parse_args(
162	$args,
163	[
164	'sources_from' => [],
165	'url_redirect_to' => site_url(),
166	'http_code' => 301,
167	'hits_count' => 0,
168	'status' => 'active',
169	'pattern_type' => 'exact',
170	'regex_pattern' => null,
171	'description' => '',
172	'created_at' => current_time('mysql'),
173	'updated_at' => current_time('mysql'),
174	]
175	);
176
177	// Serialize sources_from if array (rest of codebase uses serialized format)
178	if ( is_array( $args['sources_from'] ) ) {
179	$sources = $args['sources_from'];
180	$args['sources_from'] = serialize( array_combine( array_values( $sources ), array_fill( 0, count( $sources ), 'exact' ) ) ?: [] );
181	}
182
183	$result = $wpdb->insert( $this->get_table_name(), $args ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
184
185	// Clear cache after adding
186	$this->clear_cache();
187
188	return $result !== false ? (int) $wpdb->insert_id : false;
189	}
190
191	/**
192	* Update a record.
193	* @param array $args Values to update.
194	* @param string $id
195	*/
196	public function update($args, $id)
197	{
198	global $wpdb;
199
200	// Ensure table structure is up to date
201	$this->ensure_table_structure();
202
203	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `{$wpdb->prefix}metasync_redirections` WHERE `id` = %s ", $id)); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
204	if (!$row) return false;
205
206	$args['updated_at'] = current_time('mysql');
207	$result = $wpdb->update($wpdb->prefix . 'metasync_redirections', $args, ['id' => $id]); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
208
209	// Clear cache after updating
210	$this->clear_cache();
211
212	// Row count on success (0 = no-op write), false on query failure —
213	// callers checking `=== false` could never fire against void.
214	return $result;
215	}
216
217	/**
218	* Get total number of rows in the DB table).
219	*/
220	public function get_count()
221	{
222	global $wpdb;
223	return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}metasync_redirections`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
224	}
225
226	/**
227	* Delete a redirection record.
228	*/
229	public function delete($items)
230	{
231	global $wpdb;
232	if (!is_array($items) \|\| empty($items)) return;
233	$wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
234	'DELETE FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE `id` IN (' . implode(',', array_fill(0, count($items), '%d')) . ')',
235	$items
236	));
237
238	// Clear cache after deleting
239	$this->clear_cache();
240	}
241
242	/**
243	* activate a redirection record.
244	*/
245	public function update_status($items, $status)
246	{
247	global $wpdb;
248	if (!is_array($items) \|\| empty($items)) return;
249	$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
250	$wpdb->prepare(
251	'UPDATE `' . $wpdb->prefix . 'metasync_redirections`
252	SET `status` = %s, `updated_at` = %s
253	WHERE `id` IN (' . implode(', ', array_fill(0, count($items), '%d')) . ')',
254	array_merge(array($status, current_time('mysql')), array_map('intval', $items))
255	)
256	);
257
258	// Clear cache after updating status
259	$this->clear_cache();
260	}
261
262	/**
263	* Update if URL is matched and hit.
264	* @param object $row Record to update.
265	*/
266	public function update_counter($row)
267	{
268	global $wpdb;
269	// Atomic increment. The $row object comes from getAllActiveRecords(),
270	// which can be an hour-stale cache hit, and concurrent requests that
271	// both read the same hits_count would lose every hit but one. Bump in
272	// SQL so the stored counter is correct regardless of what $row saw.
273	$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; the hot front-end read (getAllActiveRecords) is object-cached with invalidation on every write
274	$wpdb->prepare(
275	"UPDATE `{$wpdb->prefix}metasync_redirections` SET `hits_count` = `hits_count` + 1, `last_accessed_at` = %s WHERE `id` = %d",
276	current_time('mysql'),
277	absint($row->id)
278	)
279	);
280	}
281
282	/**
283	* Clear redirection cache
284	*/
285	public function clear_cache()
286	{
287	wp_cache_delete('metasync_active_redirections', 'metasync');
288	// Redirect sources must leave the sitemap promptly: the sitemap
289	// generator listens for this on every add/update/delete/status write
290	// and busts its (30-day) caches so the next crawl regenerates.
291	do_action('metasync_redirections_changed');
292	}
293
294	/**
295	* Columns that may be used for ORDER BY. Every entry maps to the literal
296	* column name, so only allowlisted identifiers can reach the query.
297	*/
298	private function get_sortable_columns()
299	{
300	return [
301	'id', 'sources_from', 'url_redirect_to', 'http_code', 'hits_count',
302	'status', 'pattern_type', 'created_at', 'updated_at', 'last_accessed_at', 'description',
303	];
304	}
305
306	/**
307	* Build the ORDER BY clause for search()/count() from the allowlist.
308	* Falls back to the default ordering for unknown/invalid input.
309	*/
310	private function get_order_by_sql($filters)
311	{
312	$direction = (!empty($filters['order']) && strtoupper($filters['order']) === 'ASC') ? 'ASC' : 'DESC';
313	$requested = isset($filters['order_by']) ? (string) $filters['order_by'] : '';
314	if (!in_array($requested, $this->get_sortable_columns(), true)) {
315	$requested = 'created_at';
316	}
317	return sanitize_sql_orderby($requested . ' ' . $direction);
318	}
319
320	/**
321	* Search redirections with filters
322	* @param array $filters Search filters
323	*/
324	public function search_redirections($filters = [])
325	{
326	global $wpdb;
327
328	$where_conditions = ['1=1'];
329	$where_values = [];
330
331	if (!empty($filters['search'])) {
332	$where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)";
333	$search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
334	$where_values[] = $search_term;
335	$where_values[] = $search_term;
336	$where_values[] = $search_term;
337	}
338
339	if (!empty($filters['status'])) {
340	$where_conditions[] = "status = %s";
341	$where_values[] = $filters['status'];
342	}
343
344	if (!empty($filters['pattern_type'])) {
345	$where_conditions[] = "pattern_type = %s";
346	$where_values[] = $filters['pattern_type'];
347	}
348
349	if (!empty($filters['http_code'])) {
350	$where_conditions[] = "http_code = %d";
351	$where_values[] = intval($filters['http_code']);
352	}
353
354	$order_by_sql = $this->get_order_by_sql($filters) ?: 'created_at DESC';
355
356	// The query is assembled from literal placeholder fragments only; the
357	// ORDER BY fragment comes from the sortable-column allowlist wrapped in
358	// sanitize_sql_orderby(). All dynamic values bind via prepare() below.
359	$query = 'SELECT * FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE ' . implode(' AND ', $where_conditions)
360	. ' ORDER BY ' . $order_by_sql
361	. (isset($filters['per_page'], $filters['offset']) ? ' LIMIT %d OFFSET %d' : '');
362	$values = isset($filters['per_page'], $filters['offset'])
363	? array_merge($where_values, array(intval($filters['per_page']), intval($filters['offset'])))
364	: $where_values;
365
366	// With no filters the assembled query is literal-only (no
367	// placeholders); prepare() rejects placeholder-less queries, so only
368	// route it through prepare() when there are values to bind.
369	if (empty($values)) {
370	return $wpdb->get_results($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, nothing to bind
371	}
372	return $wpdb->get_results($wpdb->prepare($query, $values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments + allowlisted ORDER BY, values bound via prepare()
373	}
374
375	/**
376	* Count total redirections with filters (for pagination)
377	* @param array $filters Search filters
378	*/
379	public function count_redirections($filters = [])
380	{
381	global $wpdb;
382
383	$where_conditions = ['1=1'];
384	$where_values = [];
385
386	if (!empty($filters['search'])) {
387	$where_conditions[] = "(sources_from LIKE %s OR url_redirect_to LIKE %s OR description LIKE %s)";
388	$search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
389	$where_values[] = $search_term;
390	$where_values[] = $search_term;
391	$where_values[] = $search_term;
392	}
393
394	if (!empty($filters['status'])) {
395	$where_conditions[] = "status = %s";
396	$where_values[] = $filters['status'];
397	}
398
399	if (!empty($filters['pattern_type'])) {
400	$where_conditions[] = "pattern_type = %s";
401	$where_values[] = $filters['pattern_type'];
402	}
403
404	if (!empty($filters['http_code'])) {
405	$where_conditions[] = "http_code = %d";
406	$where_values[] = intval($filters['http_code']);
407	}
408
409	// The query is assembled from literal placeholder fragments only; all
410	// dynamic values bind via prepare() below.
411	$query = 'SELECT COUNT(*) FROM `' . $wpdb->prefix . 'metasync_redirections` WHERE ' . implode(' AND ', $where_conditions);
412
413	// Placeholder-less queries must skip prepare() (see search above).
414	if (empty($where_values)) {
415	return $wpdb->get_var($query); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, nothing to bind
416	}
417	return $wpdb->get_var($wpdb->prepare($query, $where_values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, values bound via prepare()
418	}
419	}
420	}
421