db.php — Independent Analytics – WordPress Analytics Plugin 1.5

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

222 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 resources_table()
29	{
30	return self::table_prefix() . 'resources';
31	}
32
33	public function update_db()
34	{
35	$active_db_version = get_option('iawp_db_version');
36	$no_installed_db = !$active_db_version;
37	$outdated_db = $active_db_version != IAWP_VERSION;
38
39	if ($no_installed_db) {
40	$this->create_database();
41	}
42
43	if ($outdated_db) {
44	// Run a series of if statements using version_compare to see which migrations (all?, some?) need to be run
45	}
46
47	update_option('iawp_db_version', IAWP_VERSION);
48	}
49
50	private function create_database()
51	{
52	global $wpdb;
53
54	$charset_collate = $wpdb->get_charset_collate();
55
56	$referrers_table = $this->referrers_table();
57	$wpdb->query("DROP TABLE IF EXISTS $referrers_table");
58	$wpdb->query("CREATE TABLE $referrers_table (
59	id bigint(20) UNSIGNED AUTO_INCREMENT,
60	url varchar(2048) NOT NULL,
61	PRIMARY KEY (id)
62	) $charset_collate");
63
64	$views_table = $this->views_table();
65	$wpdb->query("DROP TABLE IF EXISTS $views_table");
66	$wpdb->query("CREATE TABLE $views_table (
67	id bigint(20) UNSIGNED AUTO_INCREMENT,
68	referrer_id bigint(20) UNSIGNED,
69	resource_id bigint(20) UNSIGNED NOT NULL,
70	viewed_at datetime DEFAULT CURRENT_TIMESTAMP,
71	PRIMARY KEY (id)
72	) $charset_collate");
73
74	$resources_table = $this->resources_table();
75	$wpdb->query("DROP TABLE IF EXISTS $resources_table");
76	$wpdb->query("CREATE TABLE $resources_table (
77	id bigint(20) UNSIGNED AUTO_INCREMENT,
78	resource varchar(256) NOT NULL,
79	page bigint(20) UNSIGNED NOT NULL DEFAULT 1,
80	singular_id bigint(20) UNSIGNED,
81	author_id bigint(20) UNSIGNED,
82	date_archive varchar(256),
83	search_query varchar(256),
84	post_type varchar(256),
85	term_id bigint(20) UNSIGNED,
86	not_found_url varchar(256),
87	cached_title varchar(256),
88	cached_url varchar(256),
89	cached_type varchar(256),
90	cached_type_label varchar(256),
91	cached_author_id bigint(20) UNSIGNED,
92	cached_author varchar(256),
93	cached_date varchar(256),
94	PRIMARY KEY (id)
95	) $charset_collate");
96	}
97
98	public function wipe_db()
99	{
100	global $wpdb;
101
102	$this->create_database();
103	delete_option('iawp_dark_mode');
104	delete_option('iawp_dow');
105	delete_option('iawp_track_authenticated_users');
106	}
107
108	public function record_view($payload, $referrer_url, $viewed_at = null)
109	{
110	global $wpdb;
111	$views_table = $this->views_table();
112	$view = [];
113
114	if (isset($referrer_url)) {
115	$valid_url = filter_var($referrer_url, FILTER_VALIDATE_URL);
116	$parsed_url = parse_url($referrer_url);
117
118	if ($valid_url && $parsed_url != false) {
119	$referrer = $this->get_or_create_referrer($parsed_url['scheme'] . '://' . $parsed_url['host']);
120	$view['referrer_id'] = $referrer->id;
121	}
122	}
123
124	$resource = $this->get_or_create_resource($payload);
125	$view['resource_id'] = $resource->id;
126
127	if (isset($viewed_at)) {
128	$view['viewed_at'] = $viewed_at;
129	} else {
130	$date = new \DateTime();
131	$view['viewed_at'] = $date->format('Y-m-d H:i:s');
132	}
133
134	try {
135	$wpdb->insert($views_table, $view);
136	} catch (Exception $e) {
137	}
138	}
139
140	private function get_or_create_resource($payload)
141	{
142	global $wpdb;
143	$resources_table = $this->resources_table();
144	$query = '';
145
146	switch ($payload['resource']) {
147	case 'singular':
148	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND singular_id = %d", $payload['resource'], $payload['page'], $payload['singular_id']);
149	break;
150	case 'author_archive':
151	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND author_id = %d", $payload['resource'], $payload['page'], $payload['author_id']);
152	break;
153	case 'date_archive':
154	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND date_archive = %s", $payload['resource'], $payload['page'], $payload['date_archive']);
155	break;
156	case 'post_type_archive':
157	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND post_type = %s", $payload['resource'], $payload['page'], $payload['post_type']);
158	break;
159	case 'term_archive':
160	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND term_id = %s", $payload['resource'], $payload['page'], $payload['term_id']);
161	break;
162	case 'search':
163	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d AND search_query = %s", $payload['resource'], $payload['page'], $payload['search_query']);
164	break;
165	case 'home':
166	$query = $wpdb->prepare("SELECT * FROM $resources_table WHERE resource = %s AND page = %d", $payload['resource'], $payload['page']);
167	break;
168	case '404':
169	$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']);
170	break;
171	}
172
173	$resource = $wpdb->get_row($query);
174
175	if (is_null($resource)) {
176	$wpdb->insert($resources_table, $payload);
177	$resource = $wpdb->get_row($query);
178	}
179
180	// Todo - This probably shouldn't happen here... A call should be make to the page and then the page should
181	// know if it's a page type that needs to be cached or not.
182	switch ($resource->resource) {
183	case 'singular':
184	(new Page_Singular($resource))->update_cache();
185	break;
186	case 'author_archive':
187	(new Page_Author_Archive($resource))->update_cache();
188	break;
189	case 'post_type_archive':
190	(new Page_Post_Type_Archive($resource))->update_cache();
191	break;
192	case 'term_archive':
193	(new Page_Term_Archive($resource))->update_cache();
194	break;
195	}
196
197	return $resource;
198	}
199
200	// Why not use an index? Indexing a varchar(2048) isn't possible.
201	private function get_or_create_referrer($referrer_url)
202	{
203	global $wpdb;
204	$referrers_table = $this->referrers_table();
205	$referrer_url = trim($referrer_url);
206
207	if (strlen($referrer_url) == 0) {
208	return null;
209	}
210
211	$referrer = $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE url = %s", $referrer_url));
212
213	if (!is_null($referrer)) {
214	return $referrer;
215	}
216
217	$wpdb->insert($referrers_table, ['url' => $referrer_url]);
218
219	return $wpdb->get_row($wpdb->prepare("SELECT * FROM $referrers_table WHERE url = %s", $referrer_url));
220	}
221	}
222