metasync / otto / class-metasync-otto-excluded-urls-database.php

class-metasync-otto-excluded-urls-database.php in Search Atlas SEO – OTTO AI SEO Automation for WordPress 2.6.6, at otto/class-metasync-otto-excluded-urls-database.php

553 lines 14.9 KB

Raw Download Zip

1	<?php
2
3	/**
4	* The database operations for OTTO excluded URLs.
5	*
6	* @since 2.6.0
7	* @package Metasync
8	* @subpackage Metasync/otto
9	* @author Engineering Team <[email protected]>
10	*/
11	class Metasync_Otto_Excluded_URLs_Database
12	{
13	public static $table_name = "metasync_otto_excluded_urls";
14
15	private function get_table_name()
16	{
17	global $wpdb;
18	return $wpdb->prefix . self::$table_name;
19	}
20
21	/**
22	* Ensure table structure is up to date
23	*/
24	private function ensure_table_structure()
25	{
26	global $wpdb;
27	$table_name = $this->get_table_name();
28
29	// Check if table exists
30	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
31	// Table doesn't exist, run full migration
32	require_once dirname(__FILE__, 2) . '/database/class-db-migrations.php';
33	MetaSync_DBMigration::activation();
34	return;
35	}
36
37	// Fetch all column names once to avoid multiple SHOW COLUMNS calls
38	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
39	$existing_columns = $wpdb->get_col("SHOW COLUMNS FROM `{$table_name}`");
40
41	// is_permanent — added in v2.7.x; missing on sites that skipped the version migration
42	if (!in_array('is_permanent', $existing_columns, true)) {
43	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
44	$wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN `is_permanent` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`");
45	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
46	$wpdb->query("ALTER TABLE `{$table_name}` ADD KEY `is_permanent` (`is_permanent`)");
47	}
48
49	// auto_excluded — added in v2.7.4; missing on older installs
50	if (!in_array('auto_excluded', $existing_columns, true)) {
51	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
52	$wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN `auto_excluded` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_permanent`");
53	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
54	$wpdb->query("ALTER TABLE `{$table_name}` ADD KEY `auto_excluded` (`auto_excluded`)");
55	}
56
57	// recheck_after — added alongside auto_excluded; missing on the same older installs
58	if (!in_array('recheck_after', $existing_columns, true)) {
59	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
60	$wpdb->query("ALTER TABLE `{$table_name}` ADD COLUMN `recheck_after` DATETIME NULL DEFAULT NULL AFTER `auto_excluded`");
61	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
62	$wpdb->query("ALTER TABLE `{$table_name}` ADD KEY `recheck_after` (`recheck_after`)");
63	// Backfill: set recheck_after = created_at + 7 days for auto-excluded URLs
64	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
65	$wpdb->query("UPDATE `{$table_name}` SET recheck_after = DATE_ADD(created_at, INTERVAL 7 DAY) WHERE auto_excluded = 1 AND (recheck_after IS NULL OR recheck_after = '0000-00-00 00:00:00')");
66	}
67
68	// status_auto_excluded composite index — speeds up the cache-miss query in metasync_is_otto_url_manually_excluded()
69	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
70	$has_composite_index = $wpdb->get_var("SHOW INDEX FROM `{$table_name}` WHERE Key_name = 'status_auto_excluded'");
71	if (empty($has_composite_index)) {
72	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
73	$wpdb->query("ALTER TABLE `{$table_name}` ADD KEY `status_auto_excluded` (`status`, `auto_excluded`)");
74	}
75	}
76
77	/**
78	* Get all excluded URLs with pagination
79	*
80	* @param int $per_page Number of items per page
81	* @param int $page_number Current page number
82	* @return array Array of excluded URL records
83	*/
84	public function get_paginated_records($per_page = 10, $page_number = 1)
85	{
86	global $wpdb;
87
88	// Ensure table exists before querying
89	$this->ensure_table_structure();
90
91	$table_name = $this->get_table_name();
92
93	$offset = ($page_number - 1) * $per_page;
94
95	$results = $wpdb->get_results(
96	$wpdb->prepare(
97	"SELECT * FROM `$table_name` ORDER BY created_at DESC LIMIT %d OFFSET %d",
98	$per_page,
99	$offset
100	)
101	);
102
103	return $results;
104	}
105
106	/**
107	* Get a single record by ID
108	*
109	* @param int $id Record ID
110	* @return object\|null Record object or null if not found
111	*/
112	public function get_record_by_id($id)
113	{
114	global $wpdb;
115
116	$this->ensure_table_structure();
117
118	$table_name = $this->get_table_name();
119
120	return $wpdb->get_row($wpdb->prepare(
121	"SELECT * FROM `$table_name` WHERE id = %d",
122	$id
123	));
124	}
125
126	/**
127	* Get total count of excluded URLs
128	*
129	* @return int Total count
130	*/
131	public function get_total_count()
132	{
133	global $wpdb;
134
135	// Ensure table exists before querying
136	$this->ensure_table_structure();
137
138	$table_name = $this->get_table_name();
139
140	$count = $wpdb->get_var("SELECT COUNT(*) FROM `$table_name`");
141
142	return intval($count);
143	}
144
145	/**
146	* Get all active excluded URLs (for checking during OTTO execution)
147	* Uses caching for performance
148	*
149	* @return array Array of excluded URL patterns
150	*/
151	public function get_all_active_urls()
152	{
153	global $wpdb;
154	$table_name = $this->get_table_name();
155
156	// Check cache first
157	$cache_key = 'metasync_otto_excluded_urls';
158	$cached_urls = wp_cache_get($cache_key, 'metasync');
159
160	if ($cached_urls !== false) {
161	return $cached_urls;
162	}
163
164	// Ensure table exists before querying
165	$this->ensure_table_structure();
166
167	$results = $wpdb->get_results(
168	$wpdb->prepare(
169	"SELECT url_pattern, pattern_type FROM `$table_name` WHERE status = %s ORDER BY created_at DESC",
170	'active'
171	)
172	);
173
174	// Cache for 1 hour
175	wp_cache_set($cache_key, $results, 'metasync', HOUR_IN_SECONDS);
176
177	return $results;
178	}
179
180	/**
181	* Check if a URL matches any excluded URL patterns
182	*
183	* @param string $url URL to check
184	* @return bool True if URL is excluded, false otherwise
185	*/
186	public function is_url_excluded($url)
187	{
188	$excluded_urls = $this->get_all_active_urls();
189
190	if (empty($excluded_urls)) {
191	return false;
192	}
193
194	// Normalize URL for comparison
195	$url = trim($url);
196	$url = rtrim($url, '/');
197
198	foreach ($excluded_urls as $excluded) {
199	$pattern = trim($excluded->url_pattern);
200	$pattern = rtrim($pattern, '/');
201	$pattern_type = $excluded->pattern_type;
202
203	switch ($pattern_type) {
204	case 'exact':
205	if ($url === $pattern) {
206	return true;
207	}
208	break;
209
210	case 'contain':
211	if (strpos($url, $pattern) !== false) {
212	return true;
213	}
214	break;
215
216	case 'start':
217	if (strpos($url, $pattern) === 0) {
218	return true;
219	}
220	break;
221
222	case 'end':
223	if (substr($url, -strlen($pattern)) === $pattern) {
224	return true;
225	}
226	break;
227
228	case 'regex':
229	// Normalize with delimiters
230	$test_pattern = Metasync_Redirection::normalize_regex_pattern($pattern);
231	if (@preg_match($test_pattern, $url)) {
232	return true;
233	}
234	break;
235	}
236	}
237
238	return false;
239	}
240
241	/**
242	* Add a new excluded URL
243	*
244	* @param array $args URL data to insert
245	* @return bool\|string True on success, false on failure, 'duplicate' if already exists, 'reactivated' if inactive entry was reactivated
246	*/
247	public function add($args)
248	{
249	global $wpdb;
250
251	// Ensure table structure is up to date
252	$this->ensure_table_structure();
253
254	$created_at = current_time('mysql');
255	$args = wp_parse_args(
256	$args,
257	[
258	'url_pattern' => '',
259	'pattern_type' => 'exact',
260	'description' => '',
261	'status' => 'active',
262	'is_permanent' => 0,
263	'auto_excluded' => 0,
264	'recheck_after' => null,
265	'created_at' => $created_at,
266	]
267	);
268
269	// Auto-excluded URLs: set recheck_after to 7 days from creation by default
270	if (!empty($args['auto_excluded']) && $args['recheck_after'] === null) {
271	$args['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days', strtotime($created_at)));
272	}
273
274	// Validate URL pattern
275	if (empty($args['url_pattern'])) {
276	return false;
277	}
278
279	// Normalize URL pattern for comparison (trim and remove trailing slash)
280	$normalized_pattern = rtrim(trim($args['url_pattern']), '/');
281
282	// Check for duplicate URL pattern with same type
283	$table_name = $this->get_table_name();
284	$existing = $wpdb->get_row($wpdb->prepare(
285	"SELECT id, status FROM `$table_name` WHERE TRIM(TRAILING '/' FROM url_pattern) = %s AND pattern_type = %s",
286	$normalized_pattern,
287	$args['pattern_type']
288	));
289
290	if ($existing) {
291
292	// If existing entry is inactive, reactivate it instead of creating duplicate
293	if ($existing->status === 'inactive') {
294	$update_data = ['status' => 'active', 'description' => $args['description']];
295	if (!empty($args['auto_excluded'])) {
296	$update_data['auto_excluded'] = 1;
297	$update_data['recheck_after'] = gmdate('Y-m-d H:i:s', strtotime('+7 days'));
298	}
299	$wpdb->update($table_name, $update_data, ['id' => $existing->id]);
300	$this->clear_cache();
301	return 'reactivated';
302	}
303
304	return 'duplicate';
305	}
306
307	// Validate pattern type
308	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
309	if (!in_array($args['pattern_type'], $valid_types)) {
310	$args['pattern_type'] = 'exact';
311	}
312
313	// Validate regex pattern if type is regex
314	if ($args['pattern_type'] === 'regex') {
315	if (@preg_match($args['url_pattern'], '') === false) {
316	return false; // Invalid regex pattern
317	}
318	}
319
320	$result = $wpdb->insert($this->get_table_name(), $args);
321
322	// Clear cache after adding
323	$this->clear_cache();
324
325	return $result !== false;
326	}
327
328	/**
329	* Update an excluded URL record
330	*
331	* @param array $args Values to update
332	* @param int $id Record ID
333	* @return bool True on success, false on failure
334	*/
335	public function update($args, $id)
336	{
337	global $wpdb;
338
339	// Ensure table structure is up to date
340	$this->ensure_table_structure();
341
342	$table_name = $this->get_table_name();
343	$row = $wpdb->get_row($wpdb->prepare("SELECT * FROM `$table_name` WHERE `id` = %d", $id));
344
345	if (!$row) {
346	return false;
347	}
348
349	// Validate pattern type if provided
350	if (isset($args['pattern_type'])) {
351	$valid_types = ['exact', 'contain', 'start', 'end', 'regex'];
352	if (!in_array($args['pattern_type'], $valid_types)) {
353	$args['pattern_type'] = 'exact';
354	}
355	}
356
357	// Validate regex pattern if type is regex
358	if (isset($args['pattern_type']) && $args['pattern_type'] === 'regex') {
359	if (isset($args['url_pattern']) && @preg_match($args['url_pattern'], '') === false) {
360	return false; // Invalid regex pattern
361	}
362	}
363
364	$result = $wpdb->update($table_name, $args, ['id' => $id]);
365
366	// Clear cache after updating
367	$this->clear_cache();
368
369	return $result !== false;
370	}
371
372	/**
373	* Delete excluded URL records
374	*
375	* @param array $items Array of record IDs to delete
376	* @return bool True on success, false on failure
377	*/
378	public function delete($items)
379	{
380	global $wpdb;
381
382	// Ensure table exists before querying
383	$this->ensure_table_structure();
384
385	$table_name = $this->get_table_name();
386
387	if (!is_array($items) \|\| empty($items)) {
388	return false;
389	}
390
391	// Sanitize IDs
392	$items = array_map('intval', $items);
393	$ids = implode(',', array_fill(0, count($items), '%d'));
394
395	$result = $wpdb->query($wpdb->prepare(
396	"DELETE FROM `$table_name` WHERE `id` IN ($ids)",
397	$items
398	));
399
400	// Clear cache after deleting
401	$this->clear_cache();
402
403	return $result !== false;
404	}
405
406	/**
407	* Update status of excluded URL records
408	*
409	* @param array $items Array of record IDs
410	* @param string $status New status (active/inactive)
411	* @return bool True on success, false on failure
412	*/
413	public function update_status($items, $status)
414	{
415	global $wpdb;
416
417	// Ensure table exists before querying
418	$this->ensure_table_structure();
419
420	$table_name = $this->get_table_name();
421
422	if (!is_array($items) \|\| empty($items)) {
423	return false;
424	}
425
426	// Validate status
427	if (!in_array($status, ['active', 'inactive'])) {
428	return false;
429	}
430
431	// Sanitize IDs
432	$items = array_map('intval', $items);
433	$ids = implode(', ', array_fill(0, count($items), '%d'));
434
435	$set_status = $wpdb->prepare(
436	"UPDATE `$table_name` SET `status` = %s",
437	$status
438	);
439	$where = $wpdb->prepare(
440	" WHERE `id` IN ( $ids )",
441	$items
442	);
443
444	$query = "{$set_status}{$where}";
445	$result = $wpdb->query($query);
446
447	// Clear cache after updating status
448	$this->clear_cache();
449
450	return $result !== false;
451	}
452
453	/**
454	* Clear excluded URLs cache
455	*/
456	public function clear_cache()
457	{
458	wp_cache_delete('metasync_otto_excluded_urls', 'metasync');
459	if (defined('METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY')) {
460	delete_transient(METASYNC_OTTO_EXCLUDED_TRANSIENT_KEY);
461	}
462	}
463
464	/**
465	* Get auto-excluded 404 URLs that are due for recheck
466	* Uses recheck_after timestamp: returns records where recheck_after <= now
467	* Excludes permanent exclusions (is_permanent = 1) - those are never rechecked
468	* Limited to max 50 URLs per run to avoid overloading
469	*
470	* @param int $limit Maximum number of URLs to return (default 50)
471	* @return array Array of records with id, url_pattern, created_at, is_permanent, recheck_after
472	*/
473	public function get_auto_excluded_404_urls_due_for_recheck($limit = 50)
474	{
475	global $wpdb;
476
477	$this->ensure_table_structure();
478
479	$table_name = $this->get_table_name();
480	$now = current_time('mysql');
481	$limit = max(1, min(100, intval($limit)));
482
483	return $wpdb->get_results($wpdb->prepare(
484	"SELECT id, url_pattern, pattern_type, created_at, is_permanent, recheck_after FROM `$table_name`
485	WHERE auto_excluded = 1 AND status = %s AND pattern_type = %s
486	AND (is_permanent = 0 OR is_permanent IS NULL)
487	AND recheck_after IS NOT NULL AND recheck_after <= %s
488	ORDER BY recheck_after ASC
489	LIMIT %d",
490	'active',
491	'exact',
492	$now,
493	$limit
494	));
495	}
496
497	/**
498	* Search excluded URLs with filters
499	*
500	* @param array $filters Search filters
501	* @return array Array of matching records
502	*/
503	public function search($filters = [])
504	{
505	global $wpdb;
506
507	// Ensure table exists before querying
508	$this->ensure_table_structure();
509
510	$table_name = $this->get_table_name();
511
512	$where_conditions = ['1=1'];
513	$where_values = [];
514
515	if (!empty($filters['search'])) {
516	$where_conditions[] = "(url_pattern LIKE %s OR description LIKE %s)";
517	$search_term = '%' . $wpdb->esc_like($filters['search']) . '%';
518	$where_values[] = $search_term;
519	$where_values[] = $search_term;
520	}
521
522	if (!empty($filters['status'])) {
523	$where_conditions[] = "status = %s";
524	$where_values[] = $filters['status'];
525	}
526
527	if (!empty($filters['pattern_type'])) {
528	$where_conditions[] = "pattern_type = %s";
529	$where_values[] = $filters['pattern_type'];
530	}
531
532	$where_clause = implode(' AND ', $where_conditions);
533	$order_by = !empty($filters['order_by']) ? sanitize_sql_orderby($filters['order_by']) : 'created_at';
534	$order = !empty($filters['order']) && strtoupper($filters['order']) === 'ASC' ? 'ASC' : 'DESC';
535
536	// Add pagination support
537	$limit_clause = '';
538	if (isset($filters['per_page']) && isset($filters['offset'])) {
539	$limit_clause = " LIMIT %d OFFSET %d";
540	$where_values[] = intval($filters['per_page']);
541	$where_values[] = intval($filters['offset']);
542	}
543
544	$query = "SELECT * FROM `$table_name` WHERE $where_clause ORDER BY $order_by $order" . $limit_clause;
545
546	if (!empty($where_values)) {
547	return $wpdb->get_results($wpdb->prepare($query, $where_values));
548	} else {
549	return $wpdb->get_results($query);
550	}
551	}
552	}
553