Migration_22.php — Independent Analytics – WordPress Analytics Plugin 2.2.0

independent-analytics / IAWP / Migrations / Migration_22.php

independent-analytics / IAWP / Migrations Last commit date

Migration_22.php

92 lines

1	<?php
2
3	namespace IAWP_SCOPED\IAWP\Migrations;
4
5	use IAWP_SCOPED\IAWP\Query;
6	/** @internal */
7	class Migration_22 extends Step_Migration
8	{
9	/**
10	* @return int
11	*/
12	protected function database_version() : int
13	{
14	return 22;
15	}
16	/**
17	* @return array
18	*/
19	protected function queries() : array
20	{
21	return [$this->remove_index(), $this->add_columns(), $this->clip_domains(), $this->reduce_domain_column_size(), $this->populate_group_data(), $this->update_non_grouped_referrers(), $this->match_session_with_new_referrers(), $this->remove_duplicates(), $this->restore_index(), $this->create_direct_referrer(), $this->link_direct_sessions_to_direct_referrer(), $this->drop_referrer_groups()];
22	}
23	private function remove_index() : ?string
24	{
25	$referrers_table = Query::get_table_name(Query::REFERRERS);
26	if (!self::has_index($referrers_table, 'referrers_domain_index')) {
27	return null;
28	}
29	return "\n ALTER TABLE {$referrers_table} DROP INDEX referrers_domain_index;\n ";
30	}
31	private function add_columns() : string
32	{
33	$referrers_table = Query::get_table_name(Query::REFERRERS);
34	return "\n ALTER TABLE {$referrers_table}\n ADD COLUMN type ENUM('Ad', 'Direct', 'Referrer', 'Search', 'Social'),\n ADD COLUMN referrer VARCHAR(128)\n ";
35	}
36	private function clip_domains() : string
37	{
38	$referrers_table = Query::get_table_name(Query::REFERRERS);
39	return "\n UPDATE {$referrers_table}\n SET domain = SUBSTRING(domain, 1, 128)\n WHERE LENGTH(domain) > 128;\n ";
40	}
41	private function reduce_domain_column_size() : string
42	{
43	$referrers_table = Query::get_table_name(Query::REFERRERS);
44	return "\n ALTER TABLE {$referrers_table}\n MODIFY COLUMN domain VARCHAR(128) NOT NULL;\n ";
45	}
46	private function populate_group_data() : string
47	{
48	$referrers_table = Query::get_table_name(Query::REFERRERS);
49	$referrer_groups_table = Query::get_table_name(Query::REFERRER_GROUPS);
50	return "\n UPDATE {$referrers_table} AS referrers\n JOIN {$referrer_groups_table} AS referrer_groups ON referrers.domain = referrer_groups.domain_to_match\n SET\n referrers.referrer = referrer_groups.name,\n referrers.type = referrer_groups.type,\n referrers.domain = referrer_groups.domain\n ";
51	}
52	private function update_non_grouped_referrers() : string
53	{
54	$referrers_table = Query::get_table_name(Query::REFERRERS);
55	return "\n UPDATE {$referrers_table} AS referrers\n SET type = 'Referrer', referrer = IF(referrers.domain LIKE 'www.%', SUBSTR(referrers.domain, 5), referrers.domain)\n WHERE referrers.referrer IS NULL\n ";
56	}
57	private function match_session_with_new_referrers() : string
58	{
59	$referrers_table = Query::get_table_name(Query::REFERRERS);
60	$sessions_table = Query::get_table_name(Query::SESSIONS);
61	return "\n UPDATE {$sessions_table} AS sessions\n JOIN {$referrers_table} AS referrers ON sessions.referrer_id = referrers.id\n JOIN (\n SELECT\n MIN(referrers.id) AS referrer_id,\n domain,\n type,\n referrer\n FROM {$sessions_table} AS sessions\n JOIN {$referrers_table} AS referrers ON sessions.referrer_id = referrers.id\n WHERE domain IS NOT NULL AND type IS NOT NULL AND referrer IS NOT NULL\n GROUP BY domain, type, referrer\n ) AS first_match\n ON referrers.domain = first_match.domain\n AND referrers.type = first_match.type\n AND referrers.referrer = first_match.referrer\n SET sessions.referrer_id = first_match.referrer_id\n ";
62	}
63	private function remove_duplicates() : string
64	{
65	$referrers_table = Query::get_table_name(Query::REFERRERS);
66	$sessions_table = Query::get_table_name(Query::SESSIONS);
67	return "\n DELETE referrers FROM {$referrers_table} AS referrers\n LEFT JOIN {$sessions_table} AS sessions on referrers.id = sessions.referrer_id\n WHERE sessions.session_id IS NULL\n ";
68	}
69	private function restore_index() : string
70	{
71	$referrers_table = Query::get_table_name(Query::REFERRERS);
72	return "\n CREATE UNIQUE INDEX referrers_domain_index ON {$referrers_table} (domain);\n ";
73	}
74	private function create_direct_referrer() : string
75	{
76	global $wpdb;
77	$referrers_table = Query::get_table_name(Query::REFERRERS);
78	return $wpdb->prepare("\n INSERT INTO {$referrers_table}\n (domain, type, referrer)\n VALUES (%s, %s, %s); \n ", '', 'Direct', 'Direct');
79	}
80	private function link_direct_sessions_to_direct_referrer() : string
81	{
82	$referrers_table = Query::get_table_name(Query::REFERRERS);
83	$sessions_table = Query::get_table_name(Query::SESSIONS);
84	return "\n UPDATE {$sessions_table} AS sessions\n SET\n sessions.referrer_id = (\n SELECT id FROM {$referrers_table} WHERE domain = ''\n )\n WHERE sessions.referrer_id IS NULL \n ";
85	}
86	private function drop_referrer_groups() : string
87	{
88	$referrer_groups_table = Query::get_table_name(Query::REFERRER_GROUPS);
89	return "\n DROP TABLE IF EXISTS {$referrer_groups_table}\n ";
90	}
91	}
92