mainwp / modules / logs / classes / class-log-install.php

class-log-install.php in MainWP Dashboard: Self-hosted WordPress Management for Agencies 6.1, at modules/logs/classes/class-log-install.php

457 lines 18.0 KB

Raw Download Zip

1	<?php
2	/**
3	*
4	* This file handles all interactions with the Log DB.
5	*
6	* @package MainWP/Dashboard
7	*/
8
9	namespace MainWP\Dashboard\Module\Log;
10
11	use MainWP\Dashboard\MainWP_Install;
12	use MainWP\Dashboard\MainWP_Utility;
13
14	// Exit if accessed directly.
15	if ( ! defined( 'ABSPATH' ) ) {
16	exit;
17	}
18
19	/**
20	* Class Log_Install
21	*
22	* @package MainWP\Dashboard
23	*/
24	class Log_Install extends MainWP_Install {
25
26	/**
27	* Protected variable to hold the database version info.
28	*
29	* @var string DB version info.
30	*/
31	public $log_db_version = '1.0.1.51'; // NOSONAR - no IP.
32
33	/**
34	* Protected variable to hold the database option name.
35	*
36	* @var string DB version info.
37	*/
38	protected $log_db_option_key = 'mainwp_module_log_db_version';
39
40	/**
41	* Private static variable to hold the single instance of the class.
42	*
43	* @static
44	*
45	* @var mixed Default null
46	*/
47	private static $instance = null;
48
49	/**
50	* Method instance()
51	*
52	* Return public static instance.
53	*
54	* @static
55	* @return instance of class.
56	*/
57	public static function instance() {
58	if ( null === static::$instance ) {
59	static::$instance = new self();
60	}
61	return static::$instance;
62	}
63
64	/**
65	* Method handle to install module log tables.
66	*/
67	public function install() {
68
69	global $wpdb;
70
71	// get_site_option is multisite aware!
72	$currentVersion = get_option( $this->log_db_option_key );
73
74	$rslt = $this->query( "SHOW TABLES LIKE '" . $this->table_name( 'wp_logs' ) . "'" );
75	if ( empty( static::num_rows( $rslt ) ) ) {
76	$currentVersion = false;
77	}
78
79	if ( $currentVersion === $this->log_db_version ) {
80	return;
81	}
82
83	$suppress = $this->wpdb->suppress_errors();
84	$this->update_log_db_60_before_dbDelta( $currentVersion );
85	$this->wpdb->suppress_errors( $suppress );
86
87	$charset_collate = $wpdb->get_charset_collate();
88
89	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_logs' ) . " (
90	log_id bigint(20) NOT NULL auto_increment,
91	site_id bigint(20) unsigned NULL,
92	log_type_id bigint NOT NULL DEFAULT 0,
93	item varchar(256) NOT NULL DEFAULT '',
94	user_id int(11) unsigned NOT NULL DEFAULT '0',
95	user_login varchar(100) NOT NULL,
96	action varchar(100) NOT NULL,
97	context varchar(100) NOT NULL,
98	connector varchar(100) NOT NULL,
99	state tinyint(1) unsigned NULL,
100	created BIGINT(20) UNSIGNED NOT NULL,
101	duration float(11,4) NOT NULL DEFAULT '0',
102	dismiss tinyint(1) NOT NULL DEFAULT 0,
103	KEY site_id (site_id),
104	KEY user_id (user_id),
105	KEY user_login (user_login),
106	KEY created (created),
107	KEY duration (duration),
108	KEY context (context),
109	KEY connector (connector),
110	KEY action (action),
111	KEY state (state),
112	KEY `idx_site_created` (`site_id`, `created`),
113	KEY item (item(191))";
114
115	if ( empty( $currentVersion ) ) {
116	$tbl .= ',
117	PRIMARY KEY (log_id)';
118	}
119	$tbl .= ') ' . $charset_collate;
120	$sql[] = $tbl;
121
122	$tbl = 'CREATE TABLE ' . $this->table_name( 'wp_logs_meta' ) . ' (
123	meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
124	meta_log_id bigint(20) unsigned NOT NULL,
125	meta_key varchar(200) NOT NULL,
126	meta_value mediumtext NOT NULL,
127	KEY meta_log_id (meta_log_id),
128	UNIQUE KEY meta_unique (meta_log_id, meta_key(191))';
129
130	if ( empty( $currentVersion ) ) {
131	$tbl .= ',
132	PRIMARY KEY (`meta_id`) ';
133	}
134
135	$tbl .= ') ' . $charset_collate;
136	$sql[] = $tbl;
137
138	require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // NOSONAR - WP compatible.
139
140	global $wpdb;
141
142	if ( MainWP_Utility::instance()->is_disabled_functions( 'error_log' ) \|\| ! function_exists( '\error_log' ) ) {
143	error_reporting(0); // phpcs:ignore -- try to disabled the error_log somewhere in WP.
144	}
145
146	$suppress = $this->wpdb->suppress_errors();
147	foreach ( $sql as $query ) {
148	dbDelta( $query );
149	}
150	$this->update_log_db( $currentVersion );
151	$this->update_log_db_60( $currentVersion );
152
153	$this->wpdb->suppress_errors( $suppress );
154
155	$this->create_archive_tables( $currentVersion );
156
157	MainWP_Utility::update_option( $this->log_db_option_key, $this->log_db_version );
158	$wpdb->suppress_errors( $suppress );
159	}
160
161	/**
162	* Method update module log tables.
163	*
164	* @param string $currentVersion Current db version.
165	*/
166	public function update_log_db( $currentVersion ) { // phpcs:ignore -- NOSONAR - complex.
167
168	$is_db_ver_with_archive = version_compare( $currentVersion, '1.0.1.8', '>=' ); // NOSONAR - non-ip.
169
170	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.9', '<' ) ) { // NOSONAR - non-ip.
171	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs' ) . ' MODIFY COLUMN item varchar(256) NOT NULL DEFAULT ""' ); //phpcs:ignore -- ok.
172	}
173
174	if ( ! empty( $currentVersion ) && $is_db_ver_with_archive && version_compare( $currentVersion, '1.0.1.10', '<' ) ) { // NOSONAR - non-ip.
175	$this->create_archive_tables( $currentVersion, true );
176	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD COLUMN archived_at int(11) NOT NULL DEFAULT 0' ); //phpcs:ignore -- ok.
177	}
178
179	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.11', '<' ) ) { // NOSONAR - non-ip.
180	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs' ) . ' ADD INDEX item (item(191))' ); //phpcs:ignore -- ok.
181	}
182
183	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.16', '<' ) ) { // NOSONAR - non-ip.
184
185	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs' ) . ' DROP COLUMN object_id' ); //phpcs:ignore -- ok.
186	if ( $is_db_ver_with_archive ) {
187	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' DROP INDEX created' ); //phpcs:ignore -- ok.
188	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' DROP INDEX index_site_object_id' ); //phpcs:ignore -- ok.
189	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' DROP COLUMN object_id' ); //phpcs:ignore -- ok.
190
191	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' MODIFY COLUMN created double NOT NULL' ); //phpcs:ignore -- ok.
192	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD INDEX created ( created )' ); //phpcs:ignore -- ok.
193	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD INDEX idx_site_created(site_id, created)' ); //phpcs:ignore -- ok.
194	}
195	}
196
197	if ( ! empty( $currentVersion ) && $is_db_ver_with_archive && version_compare( $currentVersion, '1.0.1.20', '<' ) ) { // NOSONAR - non-ip.
198	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD COLUMN user_login varchar(100) NOT NULL' ); //phpcs:ignore -- ok.
199	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD INDEX user_login ( user_login )' ); //phpcs:ignore -- ok.
200	}
201
202	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.26', '<' ) ) { // NOSONAR - non-ip.
203	$count = Log_DB_Helper::instance()->count_legacy_dismissed();
204	if ( ! empty( $count ) ) {
205	update_option( 'mainwp_module_logs_updates_dismissed_db_process_status', 'require_update' );
206	}
207	}
208
209	if ( $is_db_ver_with_archive && version_compare( $currentVersion, '1.0.1.29', '<' ) ) { // NOSONAR - non-ip.
210	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' MODIFY log_id bigint(20) NOT NULL' ); //phpcs:ignore -- ok.
211	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_meta_archive' ) . ' MODIFY meta_id bigint(20) unsigned' ); //phpcs:ignore -- ok.
212	}
213	if ( version_compare( $currentVersion, '1.0.1.7', '>=' ) && version_compare( $currentVersion, '1.0.1.9', '<' ) ) { // NOSONAR - non-ip.
214	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs' ) . ' DROP INDEX state' ); //phpcs:ignore -- ok.
215	}
216
217	if ( ! empty( $currentVersion ) && $is_db_ver_with_archive && version_compare( $currentVersion, '1.0.1.35', '<' ) ) { // NOSONAR - non-ip.
218	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD COLUMN log_type_id bigint NOT NULL DEFAULT 0' ); //phpcs:ignore -- ok.
219	}
220	}
221
222	/**
223	* Method update module log tables.
224	*
225	* @param string $currentVersion Current db version.
226	*/
227	private function update_log_db_60_before_dbDelta( $currentVersion ) {
228
229	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.48', '<' ) ) { // NOSONAR - non-ip.
230	global $wpdb;
231
232	$meta_table = esc_sql( $this->table_name( 'wp_logs_meta' ) );
233
234	$existing_indexes = $wpdb->get_col( "SHOW INDEX FROM {$meta_table}", 2 ); // phpcs:ignore -- ok. Column 2 is Key_name.
235	$drop_clauses = array();
236	foreach ( array( 'meta_log_id_key', 'meta_key' ) as $index_name ) {
237	if ( in_array( $index_name, $existing_indexes, true ) ) {
238	$drop_clauses[] = 'DROP INDEX ' . esc_sql( $index_name );
239	}
240	}
241	if ( ! empty( $drop_clauses ) ) {
242	$wpdb->query( 'ALTER TABLE ' . $meta_table . ' ' . implode( ', ', $drop_clauses ) ); // phpcs:ignore -- ok.
243	}
244
245	// Check if duplicates exist.
246	//phpcs:ignore -- ok.
247	$has_duplicates = $wpdb->get_var( "
248	SELECT COUNT(*) FROM (
249	SELECT 1
250	FROM {$meta_table}
251	GROUP BY meta_log_id, meta_key
252	HAVING COUNT(*) > 1
253	) dup
254	"
255	);
256
257	if ( $has_duplicates > 0 ) {
258	// Backup duplicates first.
259	//phpcs:ignore -- ok.
260	$wpdb->query( "CREATE TABLE IF NOT EXISTS {$meta_table}_dup_backup LIKE {$meta_table}" );
261
262	//phpcs:ignore -- custom query ok.
263	$wpdb->query(
264	"
265	INSERT IGNORE INTO {$meta_table}_dup_backup
266	SELECT *
267	FROM {$meta_table}
268	WHERE (meta_log_id, meta_key) IN (
269	SELECT meta_log_id, meta_key
270	FROM {$meta_table}
271	GROUP BY meta_log_id, meta_key
272	HAVING COUNT(*) > 1
273	)
274	"
275	);
276
277	// Delete duplicates, keep newest (MAX meta_id).
278	//phpcs:ignore -- ok.
279	$wpdb->query( "
280	DELETE t
281	FROM {$meta_table} t
282	JOIN (
283	SELECT meta_log_id, meta_key, MAX(meta_id) AS keep_id
284	FROM {$meta_table}
285	GROUP BY meta_log_id, meta_key
286	HAVING COUNT(*) > 1
287	) dup
288	ON t.meta_log_id = dup.meta_log_id
289	AND t.meta_key = dup.meta_key
290	WHERE t.meta_id <> dup.keep_id
291	"
292	);
293	}
294	}
295	}
296
297	/**
298	* Method update module log tables.
299	*
300	* @param string $currentVersion Current db version.
301	*/
302	private function update_log_db_60( $currentVersion ) {
303	$is_db_ver_with_archive = version_compare( $currentVersion, '1.0.1.8', '>=' ); // NOSONAR - non-ip.
304	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.40', '<' ) ) { // NOSONAR - non-ip.
305
306	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs' ) . ' MODIFY COLUMN created BIGINT(20) UNSIGNED NOT NULL' ); //phpcs:ignore -- ok.
307
308	// to save microsecords.
309	if ( $is_db_ver_with_archive ) {
310	$table = esc_sql( $this->table_name( 'wp_logs' ) );
311	$limit = 1000;
312	do {
313	// Update a safe batch.
314	$updated = $this->wpdb->query(
315	$this->wpdb->prepare( //phpcs:ignore -- esc table name ok.
316	"
317	UPDATE {$table}
318	SET created = created * 1000000
319	WHERE created < %d
320	LIMIT %d
321	",
322	10000000000,
323	$limit
324	)
325	);
326	usleep( 100000 ); // 100ms.
327	} while ( $updated > 0 );
328	}
329
330	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' MODIFY COLUMN created BIGINT(20) UNSIGNED NOT NULL' ); //phpcs:ignore -- ok.
331
332	if ( $is_db_ver_with_archive ) {
333	$table = $this->table_name( 'wp_logs_archive' );
334	$limit = 1000;
335	do {
336	// Update a safe batch.
337	$updated = $this->wpdb->query( // phpcs:ignore -- esc table name ok.
338	$this->wpdb->prepare( //phpcs:ignore -- escaped table name ok.
339	"
340	UPDATE {$table}
341	SET created = created * 1000000
342	WHERE created < %d
343	LIMIT %d
344	",
345	10000000000,
346	$limit //phpcs:ignore -- escaped ok.
347	)
348	);
349	usleep( 100000 ); // 100ms.
350	} while ( $updated > 0 );
351	}
352	}
353
354	$this->update_changes_logs_meta_60( $currentVersion );
355	}
356
357	/**
358	* Method update_changes_logs_meta_60().
359	*
360	* @param mixed $currentVersion
361	* @return void
362	*/
363	private function update_changes_logs_meta_60( $currentVersion ) {
364
365	if ( ! empty( $currentVersion ) && version_compare( $currentVersion, '1.0.1.46', '<' ) ) { // NOSONAR - non-ip.
366
367	$log_table = esc_sql( $this->table_name( 'wp_logs' ) );
368	$meta_table = esc_sql( $this->table_name( 'wp_logs_meta' ) );
369
370	$meta_items = $this->wpdb->get_results( // phpcs:ignore -- custom escaped query ok.
371	"
372	SELECT m.meta_log_id, m.meta_value
373	FROM {$log_table} l
374	JOIN {$meta_table} m
375	ON l.log_id = m.meta_log_id
376	WHERE m.meta_key = 'extra_info'
377	AND (l.context = 'plugin' OR l.context = 'theme')
378	",
379	ARRAY_A
380	);
381
382	foreach ( $meta_items as $row ) {
383
384	$extra_info = json_decode( $row['meta_value'], true );
385	if ( ! is_array( $extra_info ) ) {
386	continue;
387	}
388
389	$key = '';
390	$val = '';
391
392	if ( ! empty( $extra_info['slug'] ) ) {
393	$key = 'slug';
394	$val = $extra_info['slug'];
395	} elseif ( ! empty( $extra_info['name'] ) ) {
396	$key = 'name';
397	$val = $extra_info['name'];
398	}
399
400	if ( empty( $key ) \|\| empty( $val ) ) {
401	continue;
402	}
403
404	$this->wpdb->replace(
405	$meta_table,
406	array(
407	'meta_log_id' => (int) $row['meta_log_id'],
408	'meta_key' => $key, //phpcs:ignore -- ok.
409	'meta_value' => $val, //phpcs:ignore -- ok.
410	),
411	array( '%d', '%s', '%s' )
412	);
413	}
414	}
415	}
416
417	/**
418	* Method get_current_logs_db_ver().
419	*/
420	public function get_current_logs_db_ver() {
421	return get_option( $this->log_db_option_key );
422	}
423
424	/**
425	* Create archive_ logs tables.
426	*
427	* @param string $currentVersion Current db version.
428	* @param bool $create_if_not_exist Whether to force creation of archive tables if they don't exist.
429	*/
430	public function create_archive_tables( $currentVersion = '', $create_if_not_exist = false ) {
431
432	$archive = $this->table_name( 'wp_logs_archive' );
433
434	if ( empty( $currentVersion ) \|\| $create_if_not_exist ) {
435	$this->wpdb->query( 'CREATE TABLE IF NOT EXISTS ' . $archive . ' LIKE ' . $this->table_name( 'wp_logs' ) ); //phpcs:ignore -- ok.
436	$this->wpdb->query( 'CREATE TABLE IF NOT EXISTS ' . $this->table_name( 'wp_logs_meta_archive' ) . ' LIKE ' . $this->table_name( 'wp_logs_meta' ) ); //phpcs:ignore -- ok.
437	}
438
439	if ( empty( $currentVersion ) ) {
440	$this->wpdb->query( 'ALTER TABLE ' . $this->table_name( 'wp_logs_archive' ) . ' ADD COLUMN archived_at int(11) NOT NULL DEFAULT 0' ); //phpcs:ignore -- ok.
441	} else {
442	$column = $this->wpdb->get_results( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $archive is an internal MainWP table identifier; column name is bound via prepare().
443	$this->wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table identifiers cannot be placeholders; $archive comes from table_name().
444	"SHOW COLUMNS FROM {$archive} LIKE %s",
445	'archived_at'
446	)
447	);
448
449	if ( empty( $column ) ) {
450	$this->wpdb->query( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $archive is an internal MainWP table identifier; ALTER TABLE has no user input.
451	"ALTER TABLE {$archive} ADD COLUMN archived_at INT(11) NOT NULL DEFAULT 0"
452	);
453	}
454	}
455	}
456	}
457