Migration_13.php — Independent Analytics – WordPress Analytics Plugin 1.30.0

independent-analytics / IAWP / Migrations / Migration_13.php

independent-analytics / IAWP / Migrations Last commit date

Migration_13.php

95 lines

1	<?php
2
3	namespace IAWP_SCOPED\IAWP\Migrations;
4
5	use IAWP_SCOPED\IAWP\Query;
6	class Migration_13 extends Migration
7	{
8	/**
9	* @var string
10	*/
11	protected $database_version = '13';
12	/**
13	* @return void
14	*/
15	protected function handle() : void
16	{
17	$this->remove_city_neighborhood_data();
18	$this->add_index_for_visitors_table();
19	$this->create_cities_table();
20	$this->create_countries_table();
21	$this->add_foreign_keys_to_sessions();
22	$this->populate_countries();
23	$this->populate_cities();
24	$this->link_sessions_to_city_and_country();
25	$this->drop_visitors_table();
26	}
27	private function add_index_for_visitors_table()
28	{
29	global $wpdb;
30	$visitors_table = Query::get_table_name(Query::VISITORS);
31	$wpdb->query("\n CREATE INDEX visitors_migration_index\n ON {$visitors_table} (country_code, subdivision, city)\n ");
32	}
33	private function create_cities_table()
34	{
35	global $wpdb;
36	$charset_and_collation = $wpdb->get_charset_collate();
37	$cities_table = Query::get_table_name(Query::CITIES);
38	$wpdb->query("DROP TABLE IF EXISTS {$cities_table}");
39	$wpdb->query("\n CREATE TABLE {$cities_table} (\n city_id bigint(20) UNSIGNED AUTO_INCREMENT,\n country_id bigint(20) UNSIGNED NOT NULL,\n subdivision varchar(64) NOT NULL,\n city varchar(64) NOT NULL,\n PRIMARY KEY (city_id)\n ) {$charset_and_collation}\n ");
40	$wpdb->query("\n CREATE UNIQUE INDEX cities_unique_index\n ON {$cities_table} (country_id, subdivision, city)\n ");
41	}
42	private function create_countries_table()
43	{
44	global $wpdb;
45	$charset_and_collation = $wpdb->get_charset_collate();
46	$countries_table = Query::get_table_name(Query::COUNTRIES);
47	$wpdb->query("DROP TABLE IF EXISTS {$countries_table}");
48	$wpdb->query("\n CREATE TABLE {$countries_table} (\n country_id bigint(20) UNSIGNED AUTO_INCREMENT,\n country_code varchar(4) NOT NULL,\n country varchar(64) NOT NULL,\n continent varchar(16) NOT NULL,\n PRIMARY KEY (country_id)\n ) {$charset_and_collation}\n ");
49	$wpdb->query("\n CREATE UNIQUE INDEX countries_unique_index\n ON {$countries_table} (country_code, country, continent)\n ");
50	}
51	private function add_foreign_keys_to_sessions()
52	{
53	global $wpdb;
54	$sessions_table = Query::get_table_name(Query::SESSIONS);
55	$wpdb->query("\n ALTER TABLE {$sessions_table}\n ADD COLUMN city_id BIGINT(20) UNSIGNED,\n ADD COLUMN country_id BIGINT(20) UNSIGNED;\n ");
56	$wpdb->query("\n ALTER TABLE {$sessions_table}\n ADD INDEX(city_id),\n ADD INDEX(country_id);\n ");
57	}
58	private function remove_city_neighborhood_data()
59	{
60	global $wpdb;
61	$visitors_table = Query::get_table_name(Query::VISITORS);
62	$wpdb->query("\n UPDATE\n {$visitors_table}\n SET\n city = TRIM(SUBSTRING_INDEX(city, '(', 1)) \n ");
63	}
64	private function populate_countries()
65	{
66	global $wpdb;
67	$countries_tables = Query::get_table_name(Query::COUNTRIES);
68	$visitors_table = Query::get_table_name(Query::VISITORS);
69	$wpdb->query("\n INSERT IGNORE INTO {$countries_tables} (continent, country_code, country)\n SELECT\n continent,\n country_code,\n country\n FROM\n {$visitors_table}\n WHERE\n continent IS NOT NULL\n AND country_code IS NOT NULL\n AND country IS NOT NULL\n GROUP BY\n continent,\n country_code,\n country \n ");
70	}
71	private function populate_cities()
72	{
73	global $wpdb;
74	$cities_tables = Query::get_table_name(Query::CITIES);
75	$countries_table = Query::get_table_name(Query::COUNTRIES);
76	$visitors_table = Query::get_table_name(Query::VISITORS);
77	$wpdb->query("\n INSERT IGNORE INTO {$cities_tables} (country_id, subdivision, city)\n SELECT\n countries.country_id,\n subdivision,\n city\n FROM\n {$visitors_table} AS visitors\n LEFT JOIN {$countries_table} AS countries ON visitors.country_code = countries.country_code\n WHERE\n subdivision IS NOT NULL\n AND city IS NOT NULL\n GROUP BY\n countries.country_id,\n subdivision,\n city\n ");
78	}
79	private function link_sessions_to_city_and_country()
80	{
81	global $wpdb;
82	$cities_tables = Query::get_table_name(Query::CITIES);
83	$countries_table = Query::get_table_name(Query::COUNTRIES);
84	$visitors_table = Query::get_table_name(Query::VISITORS);
85	$sessions_table = Query::get_table_name(Query::SESSIONS);
86	$wpdb->query("\n UPDATE\n {$sessions_table} AS sessions\n LEFT JOIN {$visitors_table} AS visitors ON sessions.visitor_id = visitors.visitor_id\n LEFT JOIN (\n SELECT\n countries.country_id,\n cities.city_id,\n countries.country_code,\n cities.subdivision,\n cities.city\n FROM\n {$cities_tables} AS cities\n LEFT JOIN {$countries_table} AS countries ON cities.country_id = countries.country_id) AS locations ON visitors.country_code = locations.country_code\n AND visitors.subdivision = locations.subdivision\n AND visitors.city = locations.city\n SET sessions.country_id = locations.country_id, sessions.city_id = locations.city_id\n ");
87	}
88	private function drop_visitors_table()
89	{
90	global $wpdb;
91	$visitors_table = Query::get_table_name(Query::VISITORS);
92	$wpdb->query("DROP TABLE IF EXISTS {$visitors_table}");
93	}
94	}
95