Migration_23.php — Independent Analytics – WordPress Analytics Plugin 2.8.4

independent-analytics / IAWP / Migrations / Migration_23.php

independent-analytics / IAWP / Migrations Last commit date

Migration_23.php

114 lines

1	<?php
2
3	namespace IAWP\Migrations;
4
5	use IAWP\Query;
6	/** @internal */
7	class Migration_23 extends \IAWP\Migrations\Step_Migration
8	{
9	/**
10	* @return int
11	*/
12	protected function database_version() : int
13	{
14	return 23;
15	}
16	/**
17	* @return array
18	*/
19	protected function queries() : array
20	{
21	return [$this->maybe_drop_device_types_table(), $this->create_device_types_table(), $this->maybe_drop_device_oss_table(), $this->create_device_oss_table(), $this->maybe_drop_device_browsers_table(), $this->create_device_browsers_table(), $this->add_columns_to_sessions(), $this->populate_device_types(), $this->populate_device_oss(), $this->populate_device_browsers(), $this->link_sessions_with_types(), $this->link_sessions_with_oss(), $this->link_sessions_with_browsers(), $this->drop_device_id_column_from_sessions(), $this->drop_original_devices_table()];
22	}
23	private function maybe_drop_device_types_table() : string
24	{
25	$device_types_table = Query::get_table_name(Query::DEVICE_TYPES);
26	return "\n DROP TABLE IF EXISTS {$device_types_table}\n ";
27	}
28	private function create_device_types_table() : string
29	{
30	global $wpdb;
31	$charset_collate = $wpdb->get_charset_collate();
32	$device_types_table = Query::get_table_name(Query::DEVICE_TYPES);
33	return "\n CREATE TABLE {$device_types_table} (\n device_type_id bigint(20) UNSIGNED AUTO_INCREMENT,\n device_type varchar(64) NOT NULL UNIQUE,\n PRIMARY KEY (device_type_id)\n ) {$charset_collate}\n ";
34	}
35	private function maybe_drop_device_oss_table() : string
36	{
37	$device_oss_table = Query::get_table_name(Query::DEVICE_OSS);
38	return "\n DROP TABLE IF EXISTS {$device_oss_table}\n ";
39	}
40	private function create_device_oss_table() : string
41	{
42	global $wpdb;
43	$charset_collate = $wpdb->get_charset_collate();
44	$device_oss_table = Query::get_table_name(Query::DEVICE_OSS);
45	return "\n CREATE TABLE {$device_oss_table} (\n device_os_id bigint(20) UNSIGNED AUTO_INCREMENT,\n device_os varchar(64) NOT NULL UNIQUE,\n PRIMARY KEY (device_os_id)\n ) {$charset_collate}\n ";
46	}
47	private function maybe_drop_device_browsers_table() : string
48	{
49	$device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS);
50	return "\n DROP TABLE IF EXISTS {$device_browsers_table}\n ";
51	}
52	private function create_device_browsers_table() : string
53	{
54	global $wpdb;
55	$charset_collate = $wpdb->get_charset_collate();
56	$device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS);
57	return "\n CREATE TABLE {$device_browsers_table} (\n device_browser_id bigint(20) UNSIGNED AUTO_INCREMENT,\n device_browser varchar(64) NOT NULL UNIQUE,\n PRIMARY KEY (device_browser_id)\n ) {$charset_collate}\n ";
58	}
59	private function add_columns_to_sessions() : string
60	{
61	$sessions_table = Query::get_table_name(Query::SESSIONS);
62	return "\n ALTER TABLE {$sessions_table}\n ADD COLUMN device_type_id BIGINT(20) UNSIGNED,\n ADD COLUMN device_os_id BIGINT(20) UNSIGNED,\n ADD COLUMN device_browser_id BIGINT(20) UNSIGNED,\n ADD INDEX (device_type_id),\n ADD INDEX (device_os_id),\n ADD INDEX (device_browser_id);\n ";
63	}
64	private function populate_device_types() : string
65	{
66	$devices_table = Query::get_table_name(Query::DEVICES);
67	$device_types_table = Query::get_table_name(Query::DEVICE_TYPES);
68	return "\n INSERT INTO {$device_types_table} (device_type)\n SELECT DISTINCT type\n FROM {$devices_table} WHERE type IS NOT NULL\n ";
69	}
70	private function populate_device_oss() : string
71	{
72	$devices_table = Query::get_table_name(Query::DEVICES);
73	$device_oss_table = Query::get_table_name(Query::DEVICE_OSS);
74	return "\n INSERT INTO {$device_oss_table} (device_os)\n SELECT DISTINCT os\n FROM {$devices_table} WHERE os IS NOT NULL\n ";
75	}
76	private function populate_device_browsers()
77	{
78	$devices_table = Query::get_table_name(Query::DEVICES);
79	$device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS);
80	return "\n INSERT INTO {$device_browsers_table} (device_browser)\n SELECT DISTINCT browser\n FROM {$devices_table} WHERE browser IS NOT NULL\n ";
81	}
82	private function link_sessions_with_types() : string
83	{
84	$sessions_table = Query::get_table_name(Query::SESSIONS);
85	$devices_table = Query::get_table_name(Query::DEVICES);
86	$device_types_table = Query::get_table_name(Query::DEVICE_TYPES);
87	return "\n UPDATE {$sessions_table} AS sessions\n JOIN {$devices_table} AS devices ON sessions.device_id = devices.device_id\n JOIN {$device_types_table} AS device_types ON devices.type = device_types.device_type\n SET sessions.device_type_id = device_types.device_type_id;\n ";
88	}
89	private function link_sessions_with_oss() : string
90	{
91	$sessions_table = Query::get_table_name(Query::SESSIONS);
92	$devices_table = Query::get_table_name(Query::DEVICES);
93	$device_oss_table = Query::get_table_name(Query::DEVICE_OSS);
94	return "\n UPDATE {$sessions_table} AS sessions\n JOIN {$devices_table} AS devices ON sessions.device_id = devices.device_id\n JOIN {$device_oss_table} AS device_oss ON devices.os = device_oss.device_os\n SET sessions.device_os_id = device_oss.device_os_id;\n ";
95	}
96	private function link_sessions_with_browsers() : string
97	{
98	$sessions_table = Query::get_table_name(Query::SESSIONS);
99	$devices_table = Query::get_table_name(Query::DEVICES);
100	$device_browsers_table = Query::get_table_name(Query::DEVICE_BROWSERS);
101	return "\n UPDATE {$sessions_table} AS sessions\n JOIN {$devices_table} AS devices ON sessions.device_id = devices.device_id\n JOIN {$device_browsers_table} AS device_browsers ON devices.browser = device_browsers.device_browser\n SET sessions.device_browser_id = device_browsers.device_browser_id;\n ";
102	}
103	private function drop_device_id_column_from_sessions() : string
104	{
105	$sessions_table = Query::get_table_name(Query::SESSIONS);
106	return "\n ALTER TABLE {$sessions_table} DROP COLUMN device_id;\n ";
107	}
108	private function drop_original_devices_table() : string
109	{
110	$devices_table = Query::get_table_name(Query::DEVICES);
111	return "\n DROP TABLE IF EXISTS {$devices_table};\n ";
112	}
113	}
114