-
Notifications
You must be signed in to change notification settings - Fork 14
/
class-trackserver-db.php
163 lines (138 loc) · 6.96 KB
/
class-trackserver-db.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
<?php
if ( ! defined( 'TRACKSERVER_PLUGIN_DIR' ) ) {
die( 'No, sorry.' );
}
class Trackserver_Db {
/**
* Database version that this code base needs.
*
* @since 1.0
*/
private $db_version = 29;
// Singleton
protected static $instance;
private $trackserver; // Reference to the main object
private $tbl_tracks;
private $tbl_locations;
public function __construct( $trackserver ) {
$this->trackserver = $trackserver;
$this->tbl_tracks = $this->trackserver->tbl_tracks;
$this->tbl_locations = $this->trackserver->tbl_locations;
}
/**
* Create a singleton if it doesn't exist and return it.
*
* @since 5.0
*/
public static function get_instance( $trackserver ) {
if ( ! self::$instance ) {
self::$instance = new self( $trackserver );
}
return self::$instance;
}
/**
* Create database tables
*/
public function create_tables() {
global $wpdb;
if ( ! $this->trackserver->options['db_version'] ) {
$sql = 'CREATE TABLE IF NOT EXISTS ' . $this->tbl_locations . " (
`id` int(11) NOT NULL AUTO_INCREMENT,
`trip_id` int(11) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`altitude` double NOT NULL,
`speed` double NOT NULL,
`heading` double NOT NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
`occurred` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
`comment` varchar(255) NOT NULL,
`hidden` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `occurred` (`occurred`),
KEY `trip_id` (`trip_id`),
KEY `trip_id_occurred` (`trip_id`,`occurred`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$sql = 'CREATE TABLE IF NOT EXISTS ' . $this->tbl_tracks . " (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`created` timestamp NOT NULL DEFAULT '1971-01-01 00:00:00',
`source` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`distance` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;";
$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$this->trackserver->update_option( 'db_version', $this->db_version );
}
}
/**
* Check if the database schema is the correct version and upgrade if necessary.
*
* @since 1.0
*
* @global object $wpdb The WordPress database interface
*/
public function check_update_db() {
global $wpdb;
$installed_version = (int) $this->trackserver->options['db_version'];
if ( $installed_version > 0 && $installed_version !== $this->db_version ) {
// Get a list of column names for the tracks table
$sql = 'SELECT * FROM ' . $this->tbl_tracks . ' LIMIT 0,1';
$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$colnames = $wpdb->get_col_info( 'name' );
// Get a list of column names for the locations table
$sql = 'SELECT * FROM ' . $this->tbl_tracks . ' LIMIT 0,1';
$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$colnames_locations = $wpdb->get_col_info( 'name' );
// Upgrade table if necessary. Add upgrade SQL statements here, and
// update $db_version at the top of the file
$upgrade_sql = array();
$upgrade_sql[5] = 'ALTER TABLE ' . $this->tbl_tracks . ' CHANGE `created` `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP';
$upgrade_sql[6] = 'ALTER TABLE ' . $this->tbl_tracks . ' ADD `created` TIMESTAMP NOT NULL AFTER `updated`';
$upgrade_sql[7] = 'ALTER TABLE ' . $this->tbl_tracks . ' ADD `source` VARCHAR( 255 ) NOT NULL AFTER `created`';
$upgrade_sql[8] = 'ALTER TABLE ' . $this->tbl_tracks . ' ADD `distance` INT( 11 ) NOT NULL AFTER `comment`';
$upgrade_sql[9] = 'ALTER TABLE ' . $this->tbl_tracks . ' ADD INDEX ( `user_id` )';
$upgrade_sql[10] = 'ALTER TABLE ' . $this->tbl_locations . ' ADD INDEX ( `trip_id` )';
$upgrade_sql[11] = 'ALTER TABLE ' . $this->tbl_locations . ' ADD INDEX ( `occurred` )';
// Fix the 'update'/'updated' mess in the tracks table
if ( in_array( 'update', $colnames, true ) ) {
$upgrade_sql[13] = 'ALTER TABLE ' . $this->tbl_tracks . ' DROP `update`';
}
if ( ! in_array( 'updated', $colnames, true ) ) {
$upgrade_sql[14] = 'ALTER TABLE ' . $this->tbl_tracks . ' ADD `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() AFTER `name`';
}
$upgrade_sql[15] = 'UPDATE ' . $this->tbl_tracks . ' t SET t.updated=(SELECT max(occurred) FROM `' . $this->tbl_locations . '` WHERE trip_id = t.id)';
$upgrade_sql[16] = 'ALTER TABLE ' . $this->tbl_locations . " ADD `hidden` TINYINT(1) NOT NULL DEFAULT '0' AFTER `comment`";
// Fix the missing 'hidden' column in fresh installs of v4.0
if ( ! in_array( 'hidden', $colnames_locations, true ) ) {
$upgrade_sql[17] = 'ALTER TABLE ' . $this->tbl_locations . " ADD `hidden` TINYINT(1) NOT NULL DEFAULT '0' AFTER `comment`";
}
// Change the default value for timestamps to be compatible with MySQL 5.7+
$upgrade_sql[18] = 'ALTER TABLE ' . $this->tbl_locations . " ALTER occurred SET DEFAULT '1971-01-01 00:00:00', ALTER created SET DEFAULT '1971-01-01 00:00:00'";
$upgrade_sql[19] = 'ALTER TABLE ' . $this->tbl_tracks . " ALTER created SET DEFAULT '1971-01-01 00:00:00'";
// Change the charset of the text columns in the tracks table, to be able to hold unicode content.
$upgrade_sql[25] = 'ALTER TABLE ' . $this->tbl_tracks . ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci';
$upgrade_sql[26] = 'ALTER TABLE ' . $this->tbl_tracks . ' CHANGE `name` `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL';
$upgrade_sql[27] = 'ALTER TABLE ' . $this->tbl_tracks . ' CHANGE `source` `source` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL';
$upgrade_sql[28] = 'ALTER TABLE ' . $this->tbl_tracks . ' CHANGE `comment` `comment` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL';
// Add multi-column index on the locations table if missing. This index can be missing on new installs between 4.3 and 4.3.2.
$sql = 'SHOW INDEX FROM ' . $this->tbl_locations . " WHERE Key_name='trip_id_occurred'";
$indexes = $wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
if ( count( $indexes ) === 0 ) {
$upgrade_sql[29] = 'ALTER TABLE ' . $this->tbl_locations . ' ADD INDEX `trip_id_occurred` (`trip_id`, `occurred`)';
}
for ( $i = $installed_version + 1; $i <= $this->db_version; $i++ ) {
if ( array_key_exists( $i, $upgrade_sql ) ) {
$wpdb->query( $upgrade_sql[ $i ] ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
}
}
$this->trackserver->update_option( 'db_version', $this->db_version );
}
}
} // class