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.0, at sync-history/class-metasync-sync-history-database.php

350 lines 8.7 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 function get_table_name()
16	{
17	global $wpdb;
18	return $wpdb->prefix . self::$table_name;
19	}
20
21	/**
22	* Check if the table exists and create it if it doesn't
23	*/
24	public function maybe_create_table()
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	$this->create_table();
32	}
33	}
34
35	/**
36	* Static method to ensure table exists - can be called without instantiating the class
37	*/
38	public static function ensure_table_exists()
39	{
40	$instance = new self();
41	$instance->maybe_create_table();
42	}
43
44	/**
45	* Create the sync history table
46	*/
47	private function create_table()
48	{
49	global $wpdb;
50	$table_name = $this->get_table_name();
51	$collate = $wpdb->get_charset_collate();
52
53	$sql = "CREATE TABLE {$table_name} (
54	id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
55	title VARCHAR(255) NOT NULL DEFAULT '',
56	source VARCHAR(50) NOT NULL DEFAULT '',
57	status VARCHAR(25) NOT NULL DEFAULT 'draft',
58	content_type VARCHAR(50) NOT NULL DEFAULT '',
59	url TEXT NULL,
60	meta_data TEXT NULL,
61	created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
62	PRIMARY KEY id (id),
63	KEY source (source),
64	KEY status (status),
65	KEY created_at (created_at),
66	KEY idx_dedup (source, created_at),
67	KEY idx_search (title(50), source, created_at)
68	) $collate;";
69
70	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
71
72	$result = dbDelta($sql);
73
74	// Log result for debugging
75	if (defined('WP_DEBUG') && WP_DEBUG) {
76	error_log("Metasync Sync History Table Creation Result: " . print_r($result, true));
77	}
78
79	// Verify table was created
80	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table_name)) != $table_name) {
81	error_log("Failed to create metasync_sync_history table: " . $wpdb->last_error);
82	}
83	}
84
85	public function getAllRecords($limit = 30, $offset = 0, $filters = [])
86	{
87	// Ensure table exists
88	$this->maybe_create_table();
89
90	global $wpdb;
91	$tableName = $this->get_table_name();
92
93	$where_conditions = [];
94	$where_values = [];
95
96	// Apply filters
97	if (!empty($filters['source'])) {
98	$where_conditions[] = "source = %s";
99	$where_values[] = $filters['source'];
100	}
101
102	// Handle status filter - match both 'publish' and 'published' for published status
103	if (!empty($filters['status'])) {
104	if ($filters['status'] === 'published') {
105	$where_conditions[] = "(status = %s OR status = %s)";
106	$where_values[] = 'published';
107	$where_values[] = 'publish';
108	} else {
109	$where_conditions[] = "status = %s";
110	$where_values[] = $filters['status'];
111	}
112	}
113
114	if (!empty($filters['date_from'])) {
115	$where_conditions[] = "created_at >= %s";
116	$where_values[] = $filters['date_from'];
117	}
118
119	if (!empty($filters['date_to'])) {
120	$where_conditions[] = "created_at <= %s";
121	$where_values[] = $filters['date_to'];
122	}
123
124	$where_clause = '';
125	if (!empty($where_conditions)) {
126	$where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
127	}
128
129	# PERFORMANCE OPTIMIZATION: Select specific columns instead of *
130	# Reduces data transfer and memory usage by 20-30%
131	$query = "SELECT id, title, source, status, content_type, url, created_at FROM `$tableName` $where_clause ORDER BY created_at DESC LIMIT %d OFFSET %d";
132	$where_values[] = $limit;
133	$where_values[] = $offset;
134
135	if (!empty($where_values)) {
136	return $wpdb->get_results($wpdb->prepare($query, $where_values));
137	} else {
138	return $wpdb->get_results($wpdb->prepare($query, $limit, $offset));
139	}
140	}
141
142	/**
143	* Add a sync history record.
144	* @param array $args Values to insert.
145	*/
146	public function add($args)
147	{
148	// Ensure table exists
149	$this->maybe_create_table();
150
151	global $wpdb;
152
153	$args = wp_parse_args(
154	$args,
155	[
156	'title' => '',
157	'source' => '',
158	'status' => 'draft',
159	'content_type' => '',
160	'url' => '',
161	'meta_data' => '',
162	'created_at' => current_time('mysql'),
163	]
164	);
165
166	// Maybe delete logs if record exceed defined limit.
167	$limit = 1000;
168	if ($limit && $this->get_count() >= $limit) {
169	$this->cleanup_old_records();
170	}
171
172	return $wpdb->insert($this->get_table_name(), $args);
173	}
174
175	/**
176	* Get total number of sync history items.
177	* @return int
178	*/
179	public function get_count($filters = [])
180	{
181	// Ensure table exists
182	$this->maybe_create_table();
183
184	global $wpdb;
185	$tableName = $this->get_table_name();
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	$where_clause = '';
219	if (!empty($where_conditions)) {
220	$where_clause = 'WHERE ' . implode(' AND ', $where_conditions);
221	}
222
223	$query = "SELECT COUNT(*) FROM `$tableName` $where_clause";
224
225	if (!empty($where_values)) {
226	return (int) $wpdb->get_var($wpdb->prepare($query, $where_values));
227	} else {
228	return (int) $wpdb->get_var($query);
229	}
230	}
231
232	/**
233	* Get a single record by ID.
234	* @param int $id
235	* @return object\|null
236	*/
237	public function get_by_id($id)
238	{
239	global $wpdb;
240	$tableName = $this->get_table_name();
241	return $wpdb->get_row($wpdb->prepare(
242	"SELECT * FROM `$tableName` WHERE id = %d LIMIT 1",
243	intval($id)
244	));
245	}
246
247	/**
248	* Delete records older than a given number of days.
249	* @param int $days
250	* @return int Number of rows deleted
251	*/
252	public function delete_older_than_days($days = 90)
253	{
254	global $wpdb;
255	$tableName = $this->get_table_name();
256	return (int) $wpdb->query($wpdb->prepare(
257	"DELETE FROM `$tableName` WHERE created_at < DATE_SUB(NOW(), INTERVAL %d DAY)",
258	intval($days)
259	));
260	}
261
262	/**
263	* Delete specific records.
264	* @param array $items
265	* @return int
266	*/
267	public function delete($items)
268	{
269	global $wpdb;
270	$tableName = $this->get_table_name();
271	if (!is_array($items) \|\| empty($items)) return 0;
272	$ids = implode(',', array_fill(0, count($items), '%d'));
273	return $wpdb->query($wpdb->prepare(
274	"
275	DELETE FROM `$tableName`
276	WHERE `id` IN ($ids) ",
277	$items
278	));
279	}
280
281	/**
282	* Clear all sync history records.
283	*/
284	public function clear_logs()
285	{
286	global $wpdb;
287	$tableName = $this->get_table_name();
288	$wpdb->query("TRUNCATE TABLE {$tableName}");
289	}
290
291	/**
292	* Clean up old records, keeping only the most recent ones.
293	*
294	* Uses a two-step PHP fetch-then-delete approach instead of a nested
295	* subquery DELETE. On MySQL 5.x the correlated subquery pattern caused
296	* the inner SELECT to be re-evaluated per candidate row, holding table
297	* locks during active syncing.
298	*/
299	public function cleanup_old_records($keep_count = 500)
300	{
301	global $wpdb;
302	$tableName = $this->get_table_name();
303
304	$ids = $wpdb->get_col($wpdb->prepare(
305	"SELECT id FROM `$tableName` ORDER BY created_at DESC LIMIT %d",
306	$keep_count
307	));
308
309	if (empty($ids)) {
310	return;
311	}
312
313	$ids = array_map('intval', $ids);
314
315	// Nothing to prune — table has fewer rows than the retention limit.
316	if (count($ids) < $keep_count) {
317	return;
318	}
319
320	$placeholders = implode(',', array_fill(0, count($ids), '%d'));
321
322	$wpdb->query($wpdb->prepare(
323	"DELETE FROM `$tableName` WHERE id NOT IN ($placeholders)",
324	$ids
325	));
326	}
327
328	/**
329	* Get sync statistics.
330	*/
331	public function get_statistics()
332	{
333	global $wpdb;
334	$tableName = $this->get_table_name();
335
336	// Count both 'publish' and 'published' for published_count (handles legacy data)
337	$stats = $wpdb->get_row("
338	SELECT
339	COUNT(*) as total_records,
340	SUM(CASE WHEN status IN ('published', 'publish') THEN 1 ELSE 0 END) as published_count,
341	SUM(CASE WHEN status = 'draft' THEN 1 ELSE 0 END) as draft_count,
342	SUM(CASE WHEN source = 'OTTO SEO' THEN 1 ELSE 0 END) as otto_count,
343	SUM(CASE WHEN source = 'Content Genius' THEN 1 ELSE 0 END) as content_genius_count
344	FROM `$tableName`
345	");
346
347	return $stats;
348	}
349	}
350