Analytics_Schema.php — EmbedPress – PDF Embedder, Embed PDF viewer, YouTube Videos, 3D FlipBook, Social feeds & more 4.5.4

embedpress / EmbedPress / Includes / Classes / Database / Analytics_Schema.php

embedpress / EmbedPress / Includes / Classes / Database Last commit date

Analytics_Schema.php

562 lines

1	<?php
2
3	namespace EmbedPress\Includes\Classes\Database;
4
5	defined('ABSPATH') or die("No direct script access allowed.");
6
7	/**
8	* EmbedPress Analytics Database Schema
9	*
10	* Handles creation and management of analytics database tables
11	*
12	* @package EmbedPress
13	* @author EmbedPress <help@embedpress.com>
14	* @copyright Copyright (C) 2025 WPDeveloper. All rights reserved.
15	* @license GPLv3 or later
16	* @since 4.4.0
17	*/
18	class Analytics_Schema
19	{
20	/**
21	* Database version for schema updates
22	*/
23	const DB_VERSION = '1.0.9';
24
25	/**
26	* Create all analytics tables
27	*
28	* @return void
29	*/
30	public static function create_tables()
31	{
32
33	global $wpdb;
34
35	$charset_collate = $wpdb->get_charset_collate();
36
37	// Check if tables need to be created or updated
38	$current_version = get_option('embedpress_analytics_db_version', '0.0.0');
39
40	// Also check if tables actually exist in database
41	$tables_exist = self::check_tables_exist();
42
43	if (version_compare($current_version, self::DB_VERSION, '<') \|\| !$tables_exist) {
44
45	self::create_content_table($charset_collate);
46	self::create_views_table($charset_collate);
47	self::create_browser_info_table($charset_collate);
48	self::create_milestones_table($charset_collate);
49	self::create_referrers_table($charset_collate);
50
51	// dbDelta silently skips column additions in some MySQL/charset
52	// configurations. Apply explicit ALTER TABLE for newer columns
53	// so upgrades from older installs always pick them up.
54	self::ensure_broken_embed_columns();
55
56	// Clean up old referrer visitor options (no longer needed)
57	self::cleanup_old_referrer_visitor_options();
58
59	// Update database version
60	update_option('embedpress_analytics_db_version', self::DB_VERSION);
61
62	// Log table creation for debugging
63	}
64	}
65
66	/**
67	* Idempotent migration: add the broken-embed status columns/index when
68	* they're missing on existing installs.
69	*
70	* @return void
71	*/
72	private static function ensure_broken_embed_columns()
73	{
74	global $wpdb;
75	$table = $wpdb->prefix . 'embedpress_analytics_content';
76
77	$columns = $wpdb->get_col("SHOW COLUMNS FROM `$table`");
78	if (!is_array($columns)) {
79	return;
80	}
81
82	$additions = [];
83	if (!in_array('last_check_status', $columns, true)) {
84	$additions[] = "ADD COLUMN last_check_status VARCHAR(20) NOT NULL DEFAULT 'unknown' AFTER total_clicks";
85	}
86	if (!in_array('last_check_code', $columns, true)) {
87	$additions[] = "ADD COLUMN last_check_code SMALLINT(4) DEFAULT NULL AFTER last_check_status";
88	}
89	if (!in_array('last_check_message', $columns, true)) {
90	$additions[] = "ADD COLUMN last_check_message VARCHAR(255) DEFAULT NULL AFTER last_check_code";
91	}
92	if (!in_array('last_check_at', $columns, true)) {
93	$additions[] = "ADD COLUMN last_check_at DATETIME DEFAULT NULL AFTER last_check_message";
94	}
95
96	if (!empty($additions)) {
97	$wpdb->query("ALTER TABLE `$table` " . implode(', ', $additions));
98	}
99
100	$indexes = $wpdb->get_results("SHOW INDEX FROM `$table` WHERE Key_name = 'idx_last_check_status'", ARRAY_A);
101	if (empty($indexes)) {
102	$wpdb->query("ALTER TABLE `$table` ADD INDEX idx_last_check_status (last_check_status)");
103	}
104	}
105
106	/**
107	* Check if all required tables exist
108	*
109	* @return bool
110	*/
111	private static function check_tables_exist()
112	{
113	global $wpdb;
114
115	$required_tables = [
116	$wpdb->prefix . 'embedpress_analytics_content',
117	$wpdb->prefix . 'embedpress_analytics_views',
118	$wpdb->prefix . 'embedpress_analytics_browser_info',
119	$wpdb->prefix . 'embedpress_analytics_milestones',
120	$wpdb->prefix . 'embedpress_analytics_referrers'
121	];
122
123	foreach ($required_tables as $table) {
124	$table_exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
125	if (!$table_exists) {
126	return false;
127	}
128	}
129
130	return true;
131	}
132
133	/**
134	* Force create all tables (for debugging/repair)
135	*
136	* @return void
137	*/
138	public static function force_create_tables()
139	{
140	global $wpdb;
141
142	$charset_collate = $wpdb->get_charset_collate();
143
144
145	self::create_content_table($charset_collate);
146	self::create_views_table($charset_collate);
147	self::create_browser_info_table($charset_collate);
148	self::create_milestones_table($charset_collate);
149	self::create_referrers_table($charset_collate);
150
151	// Update database version
152	update_option('embedpress_analytics_db_version', self::DB_VERSION);
153	}
154
155	/**
156	* Create embedpress_analytics_content table
157	* Tracks embedded content by type (Elementor/Gutenberg/Shortcode)
158	*
159	* @param string $charset_collate
160	* @return void
161	*/
162	private static function create_content_table($charset_collate)
163	{
164	global $wpdb;
165
166	$table_name = $wpdb->prefix . 'embedpress_analytics_content';
167
168	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
169	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
170	content_id varchar(255) NOT NULL,
171	content_type varchar(50) NOT NULL DEFAULT 'unknown',
172	embed_type varchar(100) NOT NULL,
173	embed_url text NOT NULL,
174	post_id bigint(20) unsigned DEFAULT NULL,
175	page_url text DEFAULT NULL,
176	title varchar(500) DEFAULT NULL,
177	total_views bigint(20) unsigned DEFAULT 0,
178	total_impressions bigint(20) unsigned DEFAULT 0,
179	total_clicks bigint(20) unsigned DEFAULT 0,
180	last_check_status varchar(20) NOT NULL DEFAULT 'unknown',
181	last_check_code smallint(4) DEFAULT NULL,
182	last_check_message varchar(255) DEFAULT NULL,
183	last_check_at datetime DEFAULT NULL,
184	created_at datetime DEFAULT CURRENT_TIMESTAMP,
185	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
186	PRIMARY KEY (id),
187	UNIQUE KEY unique_page_embed (page_url(191), embed_type(50)),
188	KEY idx_content_type (content_type),
189	KEY idx_embed_type (embed_type),
190	KEY idx_post_id (post_id),
191	KEY idx_created_at (created_at),
192	KEY idx_total_views (total_views),
193	KEY idx_last_check_status (last_check_status)
194	) $charset_collate;";
195
196	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
197	dbDelta($sql);
198	}
199
200	/**
201	* Create embedpress_analytics_views table
202	* Tracks individual views/interactions with embedded content
203	*
204	* @param string $charset_collate
205	* @return void
206	*/
207	private static function create_views_table($charset_collate)
208	{
209	global $wpdb;
210
211	$table_name = $wpdb->prefix . 'embedpress_analytics_views';
212
213	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
214	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
215	content_id varchar(255) NOT NULL,
216	user_id varchar(255) DEFAULT NULL,
217	session_id varchar(255) NOT NULL,
218	user_ip varchar(45) DEFAULT NULL,
219	user_agent text DEFAULT NULL,
220	referrer_url text DEFAULT NULL,
221	page_url text DEFAULT NULL,
222	interaction_type enum('impression', 'click', 'view', 'play', 'pause', 'complete') NOT NULL DEFAULT 'impression',
223	interaction_data longtext DEFAULT NULL,
224	view_duration int(11) unsigned DEFAULT 0,
225	created_at datetime DEFAULT CURRENT_TIMESTAMP,
226	PRIMARY KEY (id),
227	KEY idx_content_id (content_id(191)),
228	KEY idx_user_id (user_id(191)),
229	KEY idx_session_id (session_id(191)),
230	KEY idx_interaction_type (interaction_type),
231	KEY idx_created_at (created_at),
232	KEY idx_user_ip (user_ip),
233	KEY idx_content_interaction (content_id(191), interaction_type),
234	KEY idx_daily_stats (content_id(191), interaction_type, created_at),
235	KEY idx_user_content_interaction (user_id(100), content_id(100), interaction_type),
236	KEY idx_deduplication (user_id(100), content_id(100), interaction_type, created_at)
237	) $charset_collate;";
238
239	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
240	dbDelta($sql);
241	}
242
243	/**
244	* Create embedpress_analytics_browser_info table
245	* Tracks browser and device information for analytics
246	*
247	* @param string $charset_collate
248	* @return void
249	*/
250	private static function create_browser_info_table($charset_collate)
251	{
252	global $wpdb;
253
254	$table_name = $wpdb->prefix . 'embedpress_analytics_browser_info';
255
256	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
257	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
258	user_id varchar(255) DEFAULT NULL,
259	session_id varchar(255) NOT NULL,
260	browser_fingerprint varchar(64) DEFAULT NULL,
261	browser_name varchar(100) DEFAULT NULL,
262	browser_version varchar(50) DEFAULT NULL,
263	operating_system varchar(100) DEFAULT NULL,
264	device_type enum('desktop', 'mobile', 'tablet', 'unknown') DEFAULT 'unknown',
265	screen_resolution varchar(20) DEFAULT NULL,
266	language varchar(10) DEFAULT NULL,
267	timezone varchar(50) DEFAULT NULL,
268	country varchar(100) DEFAULT NULL,
269	city varchar(100) DEFAULT NULL,
270	user_agent text DEFAULT NULL,
271	created_at datetime DEFAULT CURRENT_TIMESTAMP,
272	PRIMARY KEY (id),
273	UNIQUE KEY unique_user_fingerprint (user_id(191), browser_fingerprint(50)),
274	KEY idx_user_id (user_id(191)),
275	KEY idx_session_id (session_id(191)),
276	KEY idx_browser_fingerprint (browser_fingerprint),
277	KEY idx_browser_name (browser_name),
278	KEY idx_operating_system (operating_system),
279	KEY idx_device_type (device_type),
280	KEY idx_country (country),
281	KEY idx_created_at (created_at)
282	) $charset_collate;";
283
284	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
285	dbDelta($sql);
286	}
287
288	/**
289	* Create embedpress_analytics_milestones table
290	* Tracks milestone achievements for upsell features
291	*
292	* @param string $charset_collate
293	* @return void
294	*/
295	private static function create_milestones_table($charset_collate)
296	{
297	global $wpdb;
298
299	$table_name = $wpdb->prefix . 'embedpress_analytics_milestones';
300
301	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
302	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
303	milestone_type enum('total_views', 'total_embeds', 'daily_views', 'monthly_views') NOT NULL,
304	milestone_value bigint(20) unsigned NOT NULL,
305	achieved_value bigint(20) unsigned NOT NULL,
306	is_notified tinyint(1) DEFAULT 0,
307	achieved_at datetime DEFAULT CURRENT_TIMESTAMP,
308	notified_at datetime DEFAULT NULL,
309	PRIMARY KEY (id),
310	KEY idx_milestone_type (milestone_type),
311	KEY idx_milestone_value (milestone_value),
312	KEY idx_is_notified (is_notified),
313	KEY idx_achieved_at (achieved_at)
314	) $charset_collate;";
315
316	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
317	dbDelta($sql);
318	}
319
320	/**
321	* Create embedpress_analytics_referrers table
322	* Tracks referrer URLs with optimized view and click counting
323	*
324	* @param string $charset_collate
325	* @return void
326	*/
327	private static function create_referrers_table($charset_collate)
328	{
329	global $wpdb;
330
331	$table_name = $wpdb->prefix . 'embedpress_analytics_referrers';
332
333	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
334	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
335	referrer_url text NOT NULL,
336	referrer_domain varchar(255) NOT NULL,
337	referrer_source varchar(100) DEFAULT NULL,
338	utm_source varchar(100) DEFAULT NULL,
339	utm_medium varchar(100) DEFAULT NULL,
340	utm_campaign varchar(255) DEFAULT NULL,
341	utm_term varchar(255) DEFAULT NULL,
342	utm_content varchar(255) DEFAULT NULL,
343	total_views bigint(20) unsigned DEFAULT 0,
344	total_clicks bigint(20) unsigned DEFAULT 0,
345	unique_visitors bigint(20) unsigned DEFAULT 0,
346	first_visit datetime DEFAULT CURRENT_TIMESTAMP,
347	last_visit datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
348	created_at datetime DEFAULT CURRENT_TIMESTAMP,
349	updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
350	PRIMARY KEY (id),
351	UNIQUE KEY unique_referrer_url (referrer_url(191)),
352	KEY idx_referrer_domain (referrer_domain(191)),
353	KEY idx_referrer_source (referrer_source),
354	KEY idx_utm_source (utm_source),
355	KEY idx_utm_medium (utm_medium),
356	KEY idx_utm_campaign (utm_campaign(191)),
357	KEY idx_total_views (total_views),
358	KEY idx_total_clicks (total_clicks),
359	KEY idx_unique_visitors (unique_visitors),
360	KEY idx_first_visit (first_visit),
361	KEY idx_last_visit (last_visit),
362	KEY idx_created_at (created_at)
363	) $charset_collate;";
364
365	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
366	dbDelta($sql);
367	}
368
369
370	/**
371	* Run database migrations for version updates
372	* Only new migration: normalize index prefixes to be utf8mb4-safe
373	*
374	* @param string $current_version
375	* @return void
376	*/
377	private static function run_migrations($current_version)
378	{
379	global $wpdb;
380
381	if (version_compare($current_version, '1.0.8', '<')) {
382	// Content table: ensure unique_page_embed uses (page_url(191), embed_type(50))
383	$table = $wpdb->prefix . 'embedpress_analytics_content';
384	$exists = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table));
385	if ($exists) {
386	$idx = $wpdb->get_results("SHOW INDEX FROM $table WHERE Key_name = 'unique_page_embed'");
387	$needs_update = true;
388	if (!empty($idx)) {
389	$ok_page = false;
390	$ok_embed = false;
391	foreach ($idx as $r) {
392	if ($r->Column_name === 'page_url' && intval($r->Sub_part) === 191) {
393	$ok_page = true;
394	}
395	if ($r->Column_name === 'embed_type' && intval($r->Sub_part) === 50) {
396	$ok_embed = true;
397	}
398	}
399	$needs_update = !($ok_page && $ok_embed);
400	}
401	if ($needs_update) {
402	$has_key = $wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_page_embed'");
403	if ($has_key) {
404	$wpdb->query("ALTER TABLE $table DROP INDEX unique_page_embed");
405	}
406	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_page_embed (page_url(191), embed_type(50))");
407	}
408	}
409
410	// Views table: normalize index prefixes
411	$table = $wpdb->prefix . 'embedpress_analytics_views';
412	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
413	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_content_id'")) {
414	$wpdb->query("ALTER TABLE $table DROP INDEX idx_content_id");
415	}
416	$wpdb->query("ALTER TABLE $table ADD INDEX idx_content_id (content_id(191))");
417
418	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_id'")) {
419	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_id");
420	}
421	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_id (user_id(191))");
422
423	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_session_id'")) {
424	$wpdb->query("ALTER TABLE $table DROP INDEX idx_session_id");
425	}
426	$wpdb->query("ALTER TABLE $table ADD INDEX idx_session_id (session_id(191))");
427
428	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_content_interaction'")) {
429	$wpdb->query("ALTER TABLE $table DROP INDEX idx_content_interaction");
430	}
431	$wpdb->query("ALTER TABLE $table ADD INDEX idx_content_interaction (content_id(191), interaction_type)");
432
433	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_daily_stats'")) {
434	$wpdb->query("ALTER TABLE $table DROP INDEX idx_daily_stats");
435	}
436	$wpdb->query("ALTER TABLE $table ADD INDEX idx_daily_stats (content_id(191), interaction_type, created_at)");
437
438	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_content_interaction'")) {
439	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_content_interaction");
440	}
441	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_content_interaction (user_id(100), content_id(100), interaction_type)");
442
443	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_deduplication'")) {
444	$wpdb->query("ALTER TABLE $table DROP INDEX idx_deduplication");
445	}
446	$wpdb->query("ALTER TABLE $table ADD INDEX idx_deduplication (user_id(100), content_id(100), interaction_type, created_at)");
447	}
448
449	// Browser info: normalize prefixes
450	$table = $wpdb->prefix . 'embedpress_analytics_browser_info';
451	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
452	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_user_fingerprint'")) {
453	$wpdb->query("ALTER TABLE $table DROP INDEX unique_user_fingerprint");
454	}
455	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_user_fingerprint (user_id(191), browser_fingerprint(50))");
456
457	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_user_id'")) {
458	$wpdb->query("ALTER TABLE $table DROP INDEX idx_user_id");
459	}
460	$wpdb->query("ALTER TABLE $table ADD INDEX idx_user_id (user_id(191))");
461
462	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_session_id'")) {
463	$wpdb->query("ALTER TABLE $table DROP INDEX idx_session_id");
464	}
465	$wpdb->query("ALTER TABLE $table ADD INDEX idx_session_id (session_id(191))");
466	}
467
468	// Referrers: normalize prefixes
469	$table = $wpdb->prefix . 'embedpress_analytics_referrers';
470	if ($wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $table))) {
471	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'unique_referrer_url'")) {
472	$wpdb->query("ALTER TABLE $table DROP INDEX unique_referrer_url");
473	}
474	$wpdb->query("ALTER TABLE $table ADD UNIQUE KEY unique_referrer_url (referrer_url(191))");
475
476	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_referrer_domain'")) {
477	$wpdb->query("ALTER TABLE $table DROP INDEX idx_referrer_domain");
478	}
479	$wpdb->query("ALTER TABLE $table ADD INDEX idx_referrer_domain (referrer_domain(191))");
480
481	if ($wpdb->get_var("SHOW INDEX FROM $table WHERE Key_name = 'idx_utm_campaign'")) {
482	$wpdb->query("ALTER TABLE $table DROP INDEX idx_utm_campaign");
483	}
484	$wpdb->query("ALTER TABLE $table ADD INDEX idx_utm_campaign (utm_campaign(191))");
485	}
486	}
487	}
488
489	/**
490	* Drop all analytics tables
491	* Used for plugin uninstallation
492	*
493	* @return void
494	*/
495	public static function drop_tables()
496	{
497	global $wpdb;
498
499	$tables = [
500	$wpdb->prefix . 'embedpress_analytics_content',
501	$wpdb->prefix . 'embedpress_analytics_views',
502	$wpdb->prefix . 'embedpress_analytics_browser_info',
503	$wpdb->prefix . 'embedpress_analytics_milestones',
504	$wpdb->prefix . 'embedpress_analytics_referrers'
505	];
506
507	foreach ($tables as $table) {
508	$wpdb->query("DROP TABLE IF EXISTS $table");
509	}
510
511	// Remove database version option
512	delete_option('embedpress_analytics_db_version');
513	}
514
515
516
517	/**
518	* Get table names with prefix
519	*
520	* @return array
521	*/
522	public static function get_table_names()
523	{
524	global $wpdb;
525
526	return [
527	'content' => $wpdb->prefix . 'embedpress_analytics_content',
528	'views' => $wpdb->prefix . 'embedpress_analytics_views',
529	'browser_info' => $wpdb->prefix . 'embedpress_analytics_browser_info',
530	'milestones' => $wpdb->prefix . 'embedpress_analytics_milestones',
531	'referrers' => $wpdb->prefix . 'embedpress_analytics_referrers'
532	];
533	}
534
535	/**
536	* Clean up old referrer visitor options that are no longer needed
537	* These were used before we switched to using the views table for tracking
538	*
539	* @return void
540	*/
541	private static function cleanup_old_referrer_visitor_options()
542	{
543	global $wpdb;
544
545	// Get all options that match the old referrer visitor pattern
546	$options = $wpdb->get_results(
547	"SELECT option_name FROM {$wpdb->options}
548	WHERE option_name LIKE 'embedpress_referrer_visitors_%'"
549	);
550
551	// Delete each option
552	foreach ($options as $option) {
553	delete_option($option->option_name);
554	}
555
556	// Log cleanup if any options were found
557	if (!empty($options)) {
558	error_log('EmbedPress: Cleaned up ' . count($options) . ' old referrer visitor options');
559	}
560	}
561	}
562