Migration_7.php — Independent Analytics – WordPress Analytics Plugin 1.17

independent-analytics / IAWP / Migrations / Migration_7.php

independent-analytics / IAWP / Migrations Last commit date

Migration.php 3 years ago Migration_1_0.php 3 years ago Migration_1_6.php 3 years ago Migration_1_8.php 3 years ago Migration_1_9.php 3 years ago Migration_2.php 3 years ago Migration_3.php 3 years ago Migration_4.php 3 years ago Migration_5.php 3 years ago Migration_6.php 3 years ago Migration_7.php 3 years ago Migration_8.php 3 years ago Migration_Job.php 3 years ago

Migration_7.php

116 lines

1	<?php
2
3	namespace IAWP\Migrations;
4
5	use IAWP\Query;
6
7	class Migration_7 extends Migration
8	{
9	/**
10	* @var string
11	*/
12	protected $database_version = '7';
13
14	/**
15	* @return void
16	*/
17	protected function handle(): void
18	{
19	global $wpdb;
20
21	update_option('iawp_need_clear_cache', true);
22
23	$charset_collate = $wpdb->get_charset_collate();
24
25	$sessions_table = Query::get_table_name(Query::SESSIONS);
26	$views_table = Query::get_table_name(Query::VIEWS);
27	$visitors_table = Query::get_table_name(Query::VISITORS);
28
29	$wpdb->query("DROP TABLE IF EXISTS $sessions_table;");
30	$wpdb->query("CREATE TABLE $sessions_table (
31	session_id bigint(20) UNSIGNED AUTO_INCREMENT,
32	visitor_id varchar(256),
33	initial_view_id bigint(20) UNSIGNED,
34	referrer_id bigint(20) UNSIGNED,
35	campaign_id bigint(20) UNSIGNED,
36	created_at datetime NOT NULL,
37	legacy_view boolean DEFAULT false NOT NULL,
38	PRIMARY KEY (session_id)
39	) $charset_collate;");
40
41	$wpdb->query("
42	ALTER TABLE $views_table
43	ADD (
44	session_id bigint(20) UNSIGNED
45	),
46	ADD INDEX (visitor_id);
47	");
48
49	$wpdb->query("
50	INSERT INTO $sessions_table (visitor_id, initial_view_id, referrer_id, campaign_id, created_at, legacy_view)
51	SELECT IF(visitors.visitor_token = '', NULL, visitors.visitor_token) AS visitor_id,
52	views.id AS initial_view_id,
53	views.referrer_id,
54	views.campaign_id,
55	views.viewed_at AS created_at,
56	1 AS legacy_view
57	FROM $views_table AS views
58	LEFT JOIN $visitors_table AS visitors ON views.visitor_id = visitors.id;
59	");
60
61	// Add the indices after bulk insert
62	$wpdb->query("
63	ALTER TABLE $sessions_table
64	ADD INDEX(visitor_id),
65	ADD INDEX(initial_view_id),
66	ADD INDEX(referrer_id),
67	ADD INDEX(campaign_id);
68	");
69
70	$wpdb->query("
71	UPDATE
72	$views_table AS views
73	INNER JOIN $sessions_table AS sessions ON views.id = sessions.initial_view_id
74	SET
75	views.session_id = sessions.session_id;
76	");
77
78	$wpdb->query("
79	ALTER TABLE $views_table DROP COLUMN referrer_id, DROP INDEX visitor_id, DROP COLUMN visitor_id, DROP COLUMN campaign_id, ADD INDEX (session_id);
80	");
81
82	$visitors_tmp_table = Query::get_table_name(Query::VISITORS_TMP);
83	$visitors_archive_table = Query::get_table_name(Query::VISITORS_1_16_ARCHIVE);
84
85	$wpdb->query("DROP TABLE IF EXISTS $visitors_tmp_table;");
86	$wpdb->query("CREATE TABLE $visitors_tmp_table (
87	visitor_id varchar(256),
88	country_code varchar(256),
89	city varchar(256),
90	subdivision varchar(256),
91	country varchar(256),
92	continent varchar(256),
93	PRIMARY KEY (visitor_id)
94	) $charset_collate;");
95
96	$wpdb->query("INSERT INTO $visitors_tmp_table (visitor_id, country_code, city, subdivision, country, continent)
97	SELECT
98	visitor_token,
99	country_code,
100	city,
101	subdivision,
102	country,
103	continent
104	FROM
105	$visitors_table AS visitors ON DUPLICATE KEY UPDATE country_code = visitors.country_code,
106	city = visitors.city,
107	subdivision = visitors.subdivision,
108	country = visitors.country,
109	continent = visitors.continent;
110	");
111
112	$wpdb->query("RENAME TABLE $visitors_table TO $visitors_archive_table;");
113	$wpdb->query("RENAME TABLE $visitors_tmp_table TO $visitors_table;");
114	}
115	}
116