Migration_22.php at 2.7.1 · Independent Analytics – WordPress Analytics Plugin

independent-analytics / IAWP / Migrations / Migration_22.php

Migration_22.php

93 lines 5.7 KB

Raw Download Zip

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