db.php — Independent Analytics – WordPress Analytics Plugin 1.3

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 filters-ajax.php 4 years ago filters.php 4 years ago rest-api.php 4 years ago settings-ajax.php 4 years ago settings.php 4 years ago super-secret-content-generator.php 4 years ago table-referrers.php 4 years ago table-views.php 4 years ago table.php 4 years ago track-resource-changes.php 4 years ago

db.php

209 lines

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