metasync / sync-history / class-metasync-sync-history-database.php

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

336 lines 11.1 KB

Raw Download Zip

1	<?php
2
3	/**
4	* The database operations for the sync history monitor.
5	*
6	* @since 1.0.0
7	* @package Metasync
8	* @subpackage Metasync/sync-history
9	* @author Engineering Team <[email protected]>
10	*/
11	class Metasync_Sync_History_Database
12	{
13	public static $table_name = "metasync_sync_history";
14
15	private static $structure_verified = false;
16
17	private function get_table_name()
18	{
19	global $wpdb;
20	return $wpdb->prefix . self::$table_name;
21	}
22
23	/**
24	* Check if the table exists and create it if it doesn't
25	*/
26	public function maybe_create_table()
27	{
28	// Run schema inspection at most once per request — table schema only
29	// changes on activation/upgrade, handled by class-db-migrations.php.
30	if (self::$structure_verified) { return; }
31	self::$structure_verified = true;
32
33	global $wpdb;
34	$table_name = $this->get_table_name();
35
36	// Check if table exists
37	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- lazy table-creation backstop, once per request via the $structure_verified gate; primary creation runs in the upgrade routine
38	$this->create_table();
39	}
40	}
41
42	/**
43	* Static method to ensure table exists - can be called without instantiating the class
44	*/
45	public static function ensure_table_exists()
46	{
47	$instance = new self();
48	$instance->maybe_create_table();
49	}
50
51	/**
52	* Create the sync history table
53	*/
54	private function create_table()
55	{
56	global $wpdb;
57	$table_name = $this->get_table_name();
58	$collate = $wpdb->get_charset_collate();
59
60	$sql = "CREATE TABLE {$table_name} (
61	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
62	title VARCHAR(255) NOT NULL DEFAULT '',
63	source VARCHAR(50) NOT NULL DEFAULT '',
64	status VARCHAR(25) NOT NULL DEFAULT 'draft',
65	content_type VARCHAR(50) NOT NULL DEFAULT '',
66	url TEXT NULL,
67	meta_data TEXT NULL,
68	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
69	PRIMARY KEY id (id),
70	KEY source (source),
71	KEY status (status),
72	KEY created_at (created_at),
73	KEY idx_dedup (source, created_at),
74	KEY idx_search (title(50), source, created_at)
75	) $collate;";
76
77	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
78
79	$result = dbDelta($sql);
80
81	// Log result for debugging
82	if (defined('WP_DEBUG') && WP_DEBUG) {
83	// phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log, WordPress.PHP.DevelopmentFunctions.error_log_print_r -- debug-gated, no secrets
84	error_log("Metasync Sync History Table Creation Result: " . print_r($result, true));
85	}
86
87	// Verify table was created
88	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- post-create verification inside the lazy table-creation backstop, once per request
89	error_log("Failed to create metasync_sync_history table: " . $wpdb->last_error); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log, WordPress.DB.DirectDatabaseQuery -- genuine failure path, bounded, no secrets; error_log() message text is not SQL (the word "create" trips DirectDatabaseQuery)
90	}
91	}
92
93	public function getAllRecords($limit = 30, $offset = 0, $filters = [])
94	{
95	// Ensure table exists
96	$this->maybe_create_table();
97
98	global $wpdb;
99
100	$where_conditions = [];
101	$where_values = [];
102
103	// Apply filters
104	if (!empty($filters['source'])) {
105	$where_conditions[] = "source = %s";
106	$where_values[] = $filters['source'];
107	}
108
109	// Handle status filter - match both 'publish' and 'published' for published status
110	if (!empty($filters['status'])) {
111	if ($filters['status'] === 'published') {
112	$where_conditions[] = "(status = %s OR status = %s)";
113	$where_values[] = 'published';
114	$where_values[] = 'publish';
115	} else {
116	$where_conditions[] = "status = %s";
117	$where_values[] = $filters['status'];
118	}
119	}
120
121	if (!empty($filters['date_from'])) {
122	$where_conditions[] = "created_at >= %s";
123	$where_values[] = $filters['date_from'];
124	}
125
126	if (!empty($filters['date_to'])) {
127	$where_conditions[] = "created_at <= %s";
128	$where_values[] = $filters['date_to'];
129	}
130
131	# PERFORMANCE OPTIMIZATION: Select specific columns instead of *
132	# Reduces data transfer and memory usage by 20-30%
133	// Query assembled from literal placeholder fragments only; values bind via prepare().
134	$query = "SELECT id, title, source, status, content_type, url, created_at FROM `{$wpdb->prefix}metasync_sync_history`"
135	. (!empty($where_conditions) ? ' WHERE ' . implode(' AND ', $where_conditions) : '')
136	. ' ORDER BY created_at DESC LIMIT %d OFFSET %d';
137	$where_values[] = $limit;
138	$where_values[] = $offset;
139
140	return $wpdb->get_results($wpdb->prepare($query, $where_values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, values bound via prepare()
141	}
142
143	/**
144	* Add a sync history record.
145	* @param array $args Values to insert.
146	*/
147	public function add($args)
148	{
149	// Ensure table exists
150	$this->maybe_create_table();
151
152	global $wpdb;
153
154	$args = wp_parse_args(
155	$args,
156	[
157	'title' => '',
158	'source' => '',
159	'status' => 'draft',
160	'content_type' => '',
161	'url' => '',
162	'meta_data' => '',
163	'created_at' => current_time('mysql'),
164	]
165	);
166
167	// Maybe delete logs if record exceed defined limit.
168	$limit = 1000;
169	if ($limit && $this->get_count() >= $limit) {
170	$this->cleanup_old_records();
171	}
172
173	return $wpdb->insert($this->get_table_name(), $args); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
174	}
175
176	/**
177	* Get total number of sync history items.
178	* @return int
179	*/
180	public function get_count($filters = [])
181	{
182	// Ensure table exists
183	$this->maybe_create_table();
184
185	global $wpdb;
186
187	$where_conditions = [];
188	$where_values = [];
189
190	// Apply filters
191	if (!empty($filters['source'])) {
192	$where_conditions[] = "source = %s";
193	$where_values[] = $filters['source'];
194	}
195
196	// Handle status filter - match both 'publish' and 'published' for published status
197	if (!empty($filters['status'])) {
198	if ($filters['status'] === 'published') {
199	$where_conditions[] = "(status = %s OR status = %s)";
200	$where_values[] = 'published';
201	$where_values[] = 'publish';
202	} else {
203	$where_conditions[] = "status = %s";
204	$where_values[] = $filters['status'];
205	}
206	}
207
208	if (!empty($filters['date_from'])) {
209	$where_conditions[] = "created_at >= %s";
210	$where_values[] = $filters['date_from'];
211	}
212
213	if (!empty($filters['date_to'])) {
214	$where_conditions[] = "created_at <= %s";
215	$where_values[] = $filters['date_to'];
216	}
217
218	// Query assembled from literal placeholder fragments only; values bind via prepare().
219	$query = "SELECT COUNT(*) FROM `{$wpdb->prefix}metasync_sync_history`"
220	. (!empty($where_conditions) ? ' WHERE ' . implode(' AND ', $where_conditions) : '');
221
222	if (!empty($where_values)) {
223	return (int) $wpdb->get_var($wpdb->prepare($query, $where_values)); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- literal fragments only, values bound via prepare()
224	}
225	return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$wpdb->prefix}metasync_sync_history`"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
226	}
227
228	/**
229	* Get a single record by ID.
230	* @param int $id
231	* @return object\|null
232	*/
233	public function get_by_id($id)
234	{
235	global $wpdb;
236	return $wpdb->get_row($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
237	"SELECT * FROM `{$wpdb->prefix}metasync_sync_history` WHERE id = %d LIMIT 1",
238	intval($id)
239	));
240	}
241
242	/**
243	* Delete records older than a given number of days.
244	* @param int $days
245	* @return int Number of rows deleted
246	*/
247	public function delete_older_than_days($days = 90)
248	{
249	global $wpdb;
250	return (int) $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
251	"DELETE FROM `{$wpdb->prefix}metasync_sync_history` WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
252	intval($days)
253	));
254	}
255
256	/**
257	* Delete specific records.
258	* @param array $items
259	* @return int
260	*/
261	public function delete($items)
262	{
263	global $wpdb;
264	if (!is_array($items) \|\| empty($items)) return 0;
265	return $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
266	'DELETE FROM `' . $wpdb->prefix . 'metasync_sync_history` WHERE `id` IN (' . implode(',', array_fill(0, count($items), '%d')) . ')',
267	$items
268	));
269	}
270
271	/**
272	* Clear all sync history records.
273	*/
274	public function clear_logs()
275	{
276	global $wpdb;
277	$wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_sync_history");
278	}
279
280	/**
281	* Clean up old records, keeping only the most recent ones.
282	*
283	* Uses a two-step PHP fetch-then-delete approach instead of a nested
284	* subquery DELETE. On MySQL 5.x the correlated subquery pattern caused
285	* the inner SELECT to be re-evaluated per candidate row, holding table
286	* locks during active syncing.
287	*/
288	public function cleanup_old_records($keep_count = 500)
289	{
290	global $wpdb;
291
292	$ids = $wpdb->get_col($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
293	"SELECT id FROM `{$wpdb->prefix}metasync_sync_history` ORDER BY created_at DESC LIMIT %d",
294	$keep_count
295	));
296
297	if (empty($ids)) {
298	return;
299	}
300
301	$ids = array_map('intval', $ids);
302
303	// Nothing to prune — table has fewer rows than the retention limit.
304	if (count($ids) < $keep_count) {
305	return;
306	}
307
308	$wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
309	'DELETE FROM `' . $wpdb->prefix . 'metasync_sync_history` WHERE id NOT IN (' . implode(',', array_fill(0, count($ids), '%d')) . ')',
310	$ids
311	));
312	}
313
314	/**
315	* Get sync statistics.
316	*/
317	public function get_statistics()
318	{
319	global $wpdb;
320
321	// Count both 'publish' and 'published' for published_count (handles legacy data)
322	// phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository — no WordPress API exists for plugin tables; reads feed admin/cron views needing fresh rows
323	$stats = $wpdb->get_row("
324	SELECT
325	COUNT(*) as total_records,
326	SUM(CASE WHEN status IN ('published', 'publish') THEN 1 ELSE 0 END) as published_count,
327	SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft_count,
328	SUM(CASE WHEN source = 'OTTO SEO' THEN 1 ELSE 0 END) as otto_count,
329	SUM(CASE WHEN source = 'Content Genius' THEN 1 ELSE 0 END) as content_genius_count
330	FROM `{$wpdb->prefix}metasync_sync_history`
331	");
332
333	return $stats;
334	}
335	}
336