Migration_13.php — Independent Analytics – WordPress Analytics Plugin 2.15.0

independent-analytics / IAWP / Migrations / Migration_13.php

independent-analytics / IAWP / Migrations Last commit date

Migration_13.php

96 lines

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