← All changes | otto/class-metasync-otto-bot-statistics-database.php +66 -69 2.6.26 → 2.7.2 View file →

		@@ -62,29 +62,8 @@
62	62	*/
63	63	const MAX_LOG_ENTRIES = 100;
64	64
65	65	/**
66		- * WordPress database object
67		- *
68		- * @var wpdb
69		- */
70		- private $wpdb;
71		-
72		- /**
73		- * Full table name with prefix
74		- *
75		- * @var string
76		- */
77		- private $table;
78		-
79		- /**
80		- * Full logs table name with prefix
81		- *
82		- * @var string
83		- */
84		- private $logs_table;
85		-
86		- /**
87	66	* Get singleton instance
88	67	*
89	68	* @return Metasync_Otto_Bot_Statistics_Database
90	69	*/
		@@ -98,13 +77,8 @@
98	77	/**
99	78	* Private constructor
100	79	*/
101	80	private function __construct() {
102		- global $wpdb;
103		- $this->wpdb = $wpdb;
104		- $this->table = $wpdb->prefix . self::$table_name;
105		- $this->logs_table = $wpdb->prefix . self::$logs_table_name;
106		-
107	81	$this->maybe_create_tables();
108	82	}
109	83
110	84	/**
		@@ -137,14 +111,16 @@
137	111	* @param string $from_version Previous DB version for migration logic
138	112	* @return void
139	113	*/
140	114	public function create_tables($from_version = '0') {
	115	+ global $wpdb;
	116	+
141	117	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
142	118
143		- $charset_collate = $this->wpdb->get_charset_collate();
	119	+ $charset_collate = $wpdb->get_charset_collate();
144	120
145	121	// Statistics summary table (unchanged)
146		- $stats_sql = "CREATE TABLE {$this->table} (
	122	+ $stats_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_bot_stats (
147	123	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
148	124	stat_key varchar(100) NOT NULL,
149	125	stat_value bigint(20) NOT NULL DEFAULT 0,
150	126	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		@@ -153,9 +129,9 @@
153	129	KEY updated_at (updated_at)
154	130	) $charset_collate;";
155	131
156	132	// Logs table v2: unique entries with hit_count
157		- $logs_sql = "CREATE TABLE {$this->logs_table} (
	133	+ $logs_sql = "CREATE TABLE {$wpdb->prefix}metasync_otto_bot_logs (
158	134	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
159	135	bot_name varchar(255) NOT NULL,
160	136	bot_type varchar(50) NOT NULL,
161	137	user_agent text NOT NULL,
		@@ -189,13 +165,15 @@
189	165	*
190	166	* @return void
191	167	*/
192	168	private function migrate_v1_to_v2() {
	169	+ global $wpdb;
	170	+
193	171	// Check if old schema (has created_at but not hit_count) needs migration
194		- $has_hit_count = $this->wpdb->get_var(
	172	+ $has_hit_count = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
195	173	"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
196	174	WHERE TABLE_SCHEMA = DATABASE()
197		- AND TABLE_NAME = '{$this->logs_table}'
	175	+ AND TABLE_NAME = '{$wpdb->prefix}metasync_otto_bot_logs'
198	176	AND COLUMN_NAME = 'hit_count'"
199	177	);
200	178
201	179	if ((int)$has_hit_count > 0) {
		@@ -204,9 +182,9 @@
204	182	}
205	183
206	184	// Old rows still have created_at – aggregate them into a temp table, then swap.
207	185	// This is best-effort; if it fails the new schema is empty but functional.
208		- $this->wpdb->query("TRUNCATE TABLE {$this->logs_table}");
	186	+ $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_logs");
209	187	}
210	188
211	189	/**
212	190	* Initialize default statistics if they don't exist
		@@ -213,8 +191,10 @@
213	191	*
214	192	* @return void
215	193	*/
216	194	private function initialize_default_stats() {
	195	+ global $wpdb;
	196	+
217	197	$defaults = array(
218	198	'total_detections',
219	199	'api_calls_saved',
220	200	'search_engine_bots',
		@@ -229,11 +209,11 @@
229	209	);
230	210
231	211	foreach ($defaults as $key) {
232	212	// Only insert if it doesn't exist yet; never reset existing counters
233		- $this->wpdb->query(
234		- $this->wpdb->prepare(
235		- "INSERT IGNORE INTO {$this->table} (stat_key, stat_value) VALUES (%s, 0)",
	213	+ $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	214	+ $wpdb->prepare(
	215	+ "INSERT IGNORE INTO {$wpdb->prefix}metasync_otto_bot_stats (stat_key, stat_value) VALUES (%s, 0)",
236	216	$key
237	217	)
238	218	);
239	219	}
		@@ -257,8 +237,10 @@
257	237	* @param string|null $detection_method How the bot was detected
258	238	* @return bool True on success
259	239	*/
260	240	public function add_detection($bot_name, $bot_type, $user_agent, $ip_address = null, $detection_method = null) {
	241	+ global $wpdb;
	242	+
261	243	$url = isset($_SERVER['REQUEST_URI']) ? home_url($_SERVER['REQUEST_URI']) : '';
262	244	$now = current_time('mysql');
263	245
264	246	$bot_name_clean = sanitize_text_field($bot_name);
		@@ -268,11 +250,11 @@
268	250	$method_clean = sanitize_text_field($detection_method ?? '');
269	251	$url_clean = esc_url_raw($url);
270	252
271	253	// Upsert: insert new or increment existing
272		- $result = $this->wpdb->query(
273		- $this->wpdb->prepare(
274		- "INSERT INTO {$this->logs_table}
	254	+ $result = $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	255	+ $wpdb->prepare(
	256	+ "INSERT INTO {$wpdb->prefix}metasync_otto_bot_logs
275	257	(bot_name, bot_type, user_agent, ip_address, detection_method, url, hit_count, first_seen_at, last_seen_at)
276	258	VALUES (%s, %s, %s, %s, %s, %s, 1, %s, %s)
277	259	ON DUPLICATE KEY UPDATE
278	260	hit_count = hit_count + 1,
		@@ -325,11 +307,13 @@
325	307	*
326	308	* @return bool
327	309	*/
328	310	public function reset_statistics() {
329		- $a = $this->wpdb->query("TRUNCATE TABLE {$this->table}");
330		- $b = $this->wpdb->query("TRUNCATE TABLE {$this->logs_table}");
	311	+ global $wpdb;
331	312
	313	+ $a = $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_stats");
	314	+ $b = $wpdb->query("TRUNCATE TABLE {$wpdb->prefix}metasync_otto_bot_logs");
	315	+
332	316	$this->initialize_default_stats();
333	317
334	318	return $a !== false && $b !== false;
335	319	}
		@@ -343,10 +327,12 @@
343	327	*
344	328	* @return array
345	329	*/
346	330	public function get_statistics() {
347		- $rows = $this->wpdb->get_results(
348		- "SELECT stat_key, stat_value FROM {$this->table}",
	331	+ global $wpdb;
	332	+
	333	+ $rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	334	+ "SELECT stat_key, stat_value FROM {$wpdb->prefix}metasync_otto_bot_stats",
349	335	OBJECT_K
350	336	);
351	337
352	338	$result = array(
		@@ -396,11 +382,13 @@
396	382	* @param int $offset Pagination offset
397	383	* @return array
398	384	*/
399	385	public function get_recent_requests($limit = 100, $offset = 0) {
400		- $results = $this->wpdb->get_results(
401		- $this->wpdb->prepare(
402		- "SELECT * FROM {$this->logs_table}
	386	+ global $wpdb;
	387	+
	388	+ $results = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	389	+ $wpdb->prepare(
	390	+ "SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
403	391	ORDER BY last_seen_at DESC, hit_count DESC
404	392	LIMIT %d OFFSET %d",
405	393	$limit,
406	394	$offset
		@@ -416,9 +404,11 @@
416	404	*
417	405	* @return int
418	406	*/
419	407	public function get_total_log_count() {
420		- return (int)$this->wpdb->get_var("SELECT COUNT(*) FROM {$this->logs_table}");
	408	+ global $wpdb;
	409	+
	410	+ return (int)$wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->prefix}metasync_otto_bot_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
421	411	}
422	412
423	413	/**
424	414	* Get logs filtered by bot type
		@@ -427,11 +417,13 @@
427	417	* @param int $limit Max rows
428	418	* @return array
429	419	*/
430	420	public function get_logs_by_type($bot_type, $limit = 100) {
431		- return $this->wpdb->get_results(
432		- $this->wpdb->prepare(
433		- "SELECT * FROM {$this->logs_table}
	421	+ global $wpdb;
	422	+
	423	+ return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	424	+ $wpdb->prepare(
	425	+ "SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
434	426	WHERE bot_type = %s
435	427	ORDER BY last_seen_at DESC, hit_count DESC
436	428	LIMIT %d",
437	429	$bot_type,
		@@ -449,11 +441,13 @@
449	441	* @param int $limit Max rows
450	442	* @return array
451	443	*/
452	444	public function get_logs_by_date_range($start_date, $end_date, $limit = 1000) {
453		- return $this->wpdb->get_results(
454		- $this->wpdb->prepare(
455		- "SELECT * FROM {$this->logs_table}
	445	+ global $wpdb;
	446	+
	447	+ return $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	448	+ $wpdb->prepare(
	449	+ "SELECT * FROM {$wpdb->prefix}metasync_otto_bot_logs
456	450	WHERE last_seen_at BETWEEN %s AND %s
457	451	ORDER BY last_seen_at DESC, hit_count DESC
458	452	LIMIT %d",
459	453	$start_date,
		@@ -470,10 +464,12 @@
470	464	* @param int $log_id Row ID
471	465	* @return bool
472	466	*/
473	467	public function delete_log($log_id) {
474		- return $this->wpdb->delete(
475		- $this->logs_table,
	468	+ global $wpdb;
	469	+
	470	+ return $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	471	+ $wpdb->prefix . self::$logs_table_name,
476	472	array('id' => (int)$log_id),
477	473	array('%d')
478	474	) !== false;
479	475	}
		@@ -489,12 +485,14 @@
489	485	* @param int $increment Amount to add
490	486	* @return bool
491	487	*/
492	488	private function increment_stat($stat_key, $increment = 1) {
	489	+ global $wpdb;
	490	+
493	491	// Use INSERT ... ON DUPLICATE KEY UPDATE for atomic upsert
494		- return $this->wpdb->query(
495		- $this->wpdb->prepare(
496		- "INSERT INTO {$this->table} (stat_key, stat_value)
	492	+ return $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	493	+ $wpdb->prepare(
	494	+ "INSERT INTO {$wpdb->prefix}metasync_otto_bot_stats (stat_key, stat_value)
497	495	VALUES (%s, %d)
498	496	ON DUPLICATE KEY UPDATE stat_value = stat_value + %d",
499	497	$stat_key,
500	498	$increment,
		@@ -532,8 +530,10 @@
532	530	*
533	531	* @return void
534	532	*/
535	533	private function enforce_log_cap() {
	534	+ global $wpdb;
	535	+
536	536	$count = $this->get_total_log_count();
537	537
538	538	if ($count <= self::MAX_LOG_ENTRIES) {
539	539	return;
		@@ -539,11 +539,11 @@
539	539	return;
540	540	}
541	541
542	542	// Find the ID threshold: keep the newest MAX_LOG_ENTRIES rows
543		- $threshold_id = $this->wpdb->get_var(
544		- $this->wpdb->prepare(
545		- "SELECT id FROM {$this->logs_table}
	543	+ $threshold_id = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	544	+ $wpdb->prepare(
	545	+ "SELECT id FROM {$wpdb->prefix}metasync_otto_bot_logs
546	546	ORDER BY last_seen_at DESC, id DESC
547	547	LIMIT 1 OFFSET %d",
548	548	self::MAX_LOG_ENTRIES
549	549	)
		@@ -549,15 +549,15 @@
549	549	)
550	550	);
551	551
552	552	if ($threshold_id) {
553		- $this->wpdb->query(
554		- $this->wpdb->prepare(
555		- "DELETE FROM {$this->logs_table}
	553	+ $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- custom-table repository for OTTO bot analytics (admin/cron aggregation views)
	554	+ $wpdb->prepare(
	555	+ "DELETE FROM {$wpdb->prefix}metasync_otto_bot_logs
556	556	WHERE id <= %d
557	557	AND id NOT IN (
558	558	SELECT id FROM (
559		- SELECT id FROM {$this->logs_table}
	559	+ SELECT id FROM {$wpdb->prefix}metasync_otto_bot_logs
560	560	ORDER BY last_seen_at DESC, id DESC
561	561	LIMIT %d
562	562	) AS keep_rows
563	563	)",
		@@ -578,13 +578,10 @@
578	578	* @return void
579	579	*/
580	580	public static function drop_tables() {
581	581	global $wpdb;
582		- $table = $wpdb->prefix . self::$table_name;
583		- $logs_table = $wpdb->prefix . self::$logs_table_name;
584		-
585		- $wpdb->query("DROP TABLE IF EXISTS {$table}");
586		- $wpdb->query("DROP TABLE IF EXISTS {$logs_table}");
	582	+ $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}metasync_otto_bot_stats"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- uninstall cleanup — intentional table removal when the plugin is deleted
	583	+ $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}metasync_otto_bot_logs"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- uninstall cleanup — intentional table removal when the plugin is deleted
587	584
588	585	delete_option('metasync_otto_bot_stats_db_version');
589	586	}
590	587	}