db.php — Independent Analytics – WordPress Analytics Plugin 1.7

independent-analytics / inc / db.php

independent-analytics / inc Last commit date

models 4 years ago queries 4 years ago utils 4 years ago chart.php 4 years ago db.php 4 years ago delete_data_ajax.php 4 years ago filters.php 4 years ago filters_ajax.php 4 years ago rest_api.php 4 years ago settings.php 4 years ago settings_ajax.php 4 years ago super_secret_content_generator.php 4 years ago table.php 4 years ago table_referrers.php 4 years ago table_views.php 4 years ago track_resource_changes.php 4 years ago

db.php

264 lines

1	<?php
2
3	namespace IAWP;
4
5	class DB
6	{
7	public function __construct()
8	{
9	}
10
11	private static function table_prefix()
12	{
13	global $wpdb;
14
15	return $wpdb->prefix . 'independent_analytics_';
16	}
17
18	public static function referrers_table()
19	{
20	return self::table_prefix() . 'referrers';
21	}
22
23	public static function views_table()
24	{
25	return self::table_prefix() . 'views';
26	}
27
28	public static function visitors_table()
29	{
30	return self::table_prefix() . 'visitors';
31	}
32
33	public static function resources_table()
34	{
35	return self::table_prefix() . 'resources';
36	}
37
38	public function create_or_migrate_database()
39	{
40	$active_db_version = get_option('iawp_db_version');
41	$no_installed_db = !$active_db_version;
42
43	if ($no_installed_db) {
44	$this->migrate_to_1_0();
45	}
46
47	if (version_compare($active_db_version, '1.6', '<')) {
48	self::migrate_to_1_6();
49	}
50
51	update_option('iawp_db_version', IAWP_VERSION);
52	}
53
54	private function migrate_to_1_0()
55	{
56	global $wpdb;
57
58	$charset_collate = $wpdb->get_charset_collate();
59
60	$referrers_table = $this->referrers_table();
61	$wpdb->query("DROP TABLE IF EXISTS $referrers_table");
62	$wpdb->query("CREATE TABLE $referrers_table (
63	id bigint(20) UNSIGNED AUTO_INCREMENT,
64	url varchar(2048) NOT NULL,
65	PRIMARY KEY (id)
66	) $charset_collate");
67
68	$views_table = $this->views_table();
69	$wpdb->query("DROP TABLE IF EXISTS $views_table");
70	$wpdb->query("CREATE TABLE $views_table (
71	id bigint(20) UNSIGNED AUTO_INCREMENT,
72	referrer_id bigint(20) UNSIGNED,
73	resource_id bigint(20) UNSIGNED NOT NULL,
74	viewed_at datetime DEFAULT CURRENT_TIMESTAMP,
75	PRIMARY KEY (id)
76	) $charset_collate");
77
78	$resources_table = $this->resources_table();
79	$wpdb->query("DROP TABLE IF EXISTS $resources_table");
80	$wpdb->query("CREATE TABLE $resources_table (
81	id bigint(20) UNSIGNED AUTO_INCREMENT,
82	resource varchar(256) NOT NULL,
83	page bigint(20) UNSIGNED NOT NULL DEFAULT 1,
84	singular_id bigint(20) UNSIGNED,
85	author_id bigint(20) UNSIGNED,
86	date_archive varchar(256),
87	search_query varchar(256),
88	post_type varchar(256),
89	term_id bigint(20) UNSIGNED,
90	not_found_url varchar(256),
91	cached_title varchar(256),
92	cached_url varchar(256),
93	cached_type varchar(256),
94	cached_type_label varchar(256),
95	cached_author_id bigint(20) UNSIGNED,
96	cached_author varchar(256),
97	cached_date varchar(256),
98	PRIMARY KEY (id)
99	) $charset_collate");
100	}
101
102	private function migrate_to_1_6()
103	{
104	global $wpdb;
105
106	$charset_collate = $wpdb->get_charset_collate();
107
108	$views_table = $this->views_table();
109	$wpdb->query("ALTER TABLE $views_table ADD visitor_id bigint(20) UNSIGNED");
110
111	$visitors_table = $this->visitors_table();
112	$wpdb->query("DROP TABLE IF EXISTS $visitors_table");
113	$wpdb->query("CREATE TABLE $visitors_table (
114	id bigint(20) UNSIGNED AUTO_INCREMENT,
115	visitor_token varchar(256),
116	PRIMARY KEY (id)
117	) $charset_collate");
118	}
119
120	public function wipe_db()
121	{
122	delete_option('iawp_db_version');
123	delete_option('iawp_dark_mode');
124	delete_option('iawp_dow');
125	delete_option('iawp_track_authenticated_users');
126	delete_option('iawp_daily_salt');
127	delete_option('iawp_daily_salt_set');
128	$this->create_or_migrate_database();
129	}
130
131	public function record_view($payload, $referrer_url, $visitor_token, $viewed_at = null)
132	{
133	global $wpdb;
134	$views_table = $this->views_table();
135	$view = [];
136
137	if (isset($referrer_url)) {
138	$valid_url = filter_var($referrer_url, FILTER_VALIDATE_URL);
139	$parsed_url = parse_url($referrer_url);
140
141	if ($valid_url && $parsed_url != false) {
142	$referrer = $this->get_or_create_referrer($parsed_url['scheme'] . '://' . $parsed_url['host']);
143	$view['referrer_id'] = $referrer->id;
144	}
145	}
146
147	$visitor = $this->get_or_create_visitor($visitor_token);
148	$view['visitor_id'] = $visitor->id;
149
150	$resource = $this->get_or_create_resource($payload);
151	$view['resource_id'] = $resource->id;
152
153	if (isset($viewed_at)) {
154	$view['viewed_at'] = $viewed_at;
155	} else {
156	$date = new \DateTime();
157	$view['viewed_at'] = $date->format('Y-m-d H:i:s');
158	}
159
160	try {
161	$wpdb->insert($views_table, $view);
162	} catch (Exception $e) {
163	}
164	}
165
166	private function get_or_create_resource($payload)
167	{
168	global $wpdb;
169	$resources_table = $this->resources_table();
170	$query = '';
171
172	switch ($payload['resource']) {
173	case 'singular':
174	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND singular_id = %d", $payload['resource'], $payload['page'], $payload['singular_id']);
175	break;
176	case 'author_archive':
177	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND author_id = %d", $payload['resource'], $payload['page'], $payload['author_id']);
178	break;
179	case 'date_archive':
180	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND date_archive = %s", $payload['resource'], $payload['page'], $payload['date_archive']);
181	break;
182	case 'post_type_archive':
183	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND post_type = %s", $payload['resource'], $payload['page'], $payload['post_type']);
184	break;
185	case 'term_archive':
186	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND term_id = %s", $payload['resource'], $payload['page'], $payload['term_id']);
187	break;
188	case 'search':
189	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND search_query = %s", $payload['resource'], $payload['page'], $payload['search_query']);
190	break;
191	case 'home':
192	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d", $payload['resource'], $payload['page']);
193	break;
194	case '404':
195	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND not_found_url = %s", $payload['resource'], $payload['page'], $payload['not_found_url']);
196	break;
197	}
198
199	$resource = $wpdb->get_row($query);
200
201	if (is_null($resource)) {
202	$wpdb->insert($resources_table, $payload);
203	$resource = $wpdb->get_row($query);
204	}
205
206	// Todo - This probably shouldn't happen here... A call should be make to the page and then the page should
207	// know if it's a page type that needs to be cached or not.
208	switch ($resource->resource) {
209	case 'singular':
210	(new Page_Singular($resource))->update_cache();
211	break;
212	case 'author_archive':
213	(new Page_Author_Archive($resource))->update_cache();
214	break;
215	case 'post_type_archive':
216	(new Page_Post_Type_Archive($resource))->update_cache();
217	break;
218	case 'term_archive':
219	(new Page_Term_Archive($resource))->update_cache();
220	break;
221	}
222
223	return $resource;
224	}
225
226	// Why not use an index? Indexing a varchar(2048) isn't possible.
227	private function get_or_create_referrer($referrer_url)
228	{
229	global $wpdb;
230	$referrers_table = $this->referrers_table();
231	$referrer_url = trim($referrer_url);
232
233	if (strlen($referrer_url) == 0) {
234	return null;
235	}
236
237	$referrer = $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE url = %s", $referrer_url));
238
239	if (!is_null($referrer)) {
240	return $referrer;
241	}
242
243	$wpdb->insert($referrers_table, ['url' => $referrer_url]);
244
245	return $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE url = %s", $referrer_url));
246	}
247
248	private function get_or_create_visitor($visitor_token)
249	{
250	global $wpdb;
251	$visitors_table = self::visitors_table();
252
253	$visitor = $wpdb->get_row($wpdb->prepare("SELECT * FROM $visitors_table WHERE visitor_token = %s", $visitor_token));
254
255	if (!is_null($visitor)) {
256	return $visitor;
257	}
258
259	$wpdb->insert($visitors_table, ['visitor_token' => $visitor_token]);
260
261	return $wpdb->get_row($wpdb->prepare("SELECT * FROM $visitors_table WHERE visitor_token = %s", $visitor_token));
262	}
263	}
264