PluginProbe ʕ •ᴥ•ʔ
Advanced Database Cleaner – Optimize & Clean Database to Speed Up Site Performance / 4.2.0
Advanced Database Cleaner – Optimize & Clean Database to Speed Up Site Performance v4.2.0
4.2.0 trunk 1.0.0 1.1.0 1.1.1 1.2.0 1.2.1 1.2.2 1.2.3 1.3.0 1.3.1 1.3.5 1.3.6 1.3.7 2.0.0 3.0.0 3.0.1 3.0.2 3.0.3 3.0.4 3.1.0 3.1.1 3.1.2 3.1.3 3.1.4 3.1.5 3.1.6 3.1.7 4.0.0 4.0.1 4.0.2 4.0.3 4.0.4 4.0.5 4.0.6 4.0.7 4.1.0 4.1.1
advanced-database-cleaner / includes / models / class-adbc-users-meta.php
advanced-database-cleaner / includes / models Last commit date
class-adbc-common-model.php 7 months ago class-adbc-cron-jobs.php 2 months ago class-adbc-database.php 5 months ago class-adbc-options.php 4 months ago class-adbc-post-types.php 3 months ago class-adbc-posts-meta.php 4 months ago class-adbc-sites.php 7 months ago class-adbc-tables.php 3 months ago class-adbc-transients.php 4 months ago class-adbc-users-meta.php 4 months ago
class-adbc-users-meta.php
535 lines
1 <?php
2
3 // Exit if accessed directly
4 if ( ! defined( 'ABSPATH' ) )
5 exit;
6
7 /**
8 * ADBC users meta class.
9 *
10 * This class provides the users meta functions.
11 */
12 class ADBC_Users_Meta {
13
14 private const BIG_USERMETA_THRESHOLD_WARNING = 150 * 1024; // 150 KB. (If you change this value, change it as well in js filter message and slice)
15 private const TRUNCATE_LENGTH = 20; // Length to truncate the meta value for display
16
17 /**
18 * Get the users meta list for the endpoint.
19 *
20 * @param array $filters Output of sanitize_filters().
21 *
22 * @return WP_REST_Response The list of users meta.
23 */
24 public static function get_users_meta_list( $filters ) {
25
26 // Prepare variables
27 $users_meta_list = [];
28 $total_users_meta = 0;
29
30 $scan_counter = new ADBC_Scan_Counter();
31
32 $startRecord = ( $filters['current_page'] - 1 ) * $filters['items_per_page'];
33 $endRecord = $startRecord + $filters['items_per_page'];
34 $currentRecord = 0;
35
36 $limit = ADBC_Settings::instance()->get_setting( 'database_rows_batch' );
37 $offset = 0;
38
39 do { // Loop through all users meta in batches of $limit to avoid memory issues
40
41 $users_meta = self::get_users_meta_list_batch( $filters, $limit, $offset );
42 $fetched_count = count( $users_meta );
43
44 if ( ADBC_VERSION_TYPE === 'PREMIUM' )
45 ADBC_Scan_Results::instance()->load_scan_results_to_items_rows( $users_meta, 'users_meta' ); // Load scan results to the users meta rows
46 else
47 ADBC_Common_Model::load_scan_results_to_items_for_free_version( $users_meta ); // Load scan results to the users meta rows for free version
48
49 ADBC_Hardcoded_Items::instance()->load_hardcoded_scan_results_to_items_rows( $users_meta, 'users_meta' ); // Load hardcoded items to the users meta rows
50
51 foreach ( $users_meta as $index => $user_meta ) {
52
53 $scan_counter->refresh_categorization_count( $user_meta->belongs_to );
54
55 if ( ! ADBC_Common_Model::is_item_satisfies_belongs_to( $filters, $user_meta->belongs_to ) )
56 continue;
57
58 $total_users_meta++; // Count users meta that satisfy all filters and belongs_to
59
60 // Only process the current batch if it's within the desired page range
61 if ( $currentRecord >= $startRecord && $currentRecord < $endRecord ) {
62
63 $users_meta_list[] = [
64 // This id is used to identify the user meta in the frontend and take actions on it
65 'composite_id' => [
66 'items_type' => 'users_meta',
67 'site_id' => $user_meta->site_id,
68 'id' => (int) $user_meta->umeta_id,
69 'name' => $user_meta->name,
70 ],
71 'id' => $user_meta->umeta_id,
72 'name' => $user_meta->name, // Used in the known addons modal & "show value modal". To be generic and work for all items types.
73 'meta_key' => $user_meta->name,
74 'value' => $user_meta->value,
75 'size' => $user_meta->size,
76 'user_id' => $user_meta->user_id,
77 'site_id' => $user_meta->site_id,
78 'belongs_to' => $user_meta->belongs_to,
79 'known_plugins' => $user_meta->known_plugins,
80 'known_themes' => $user_meta->known_themes,
81 ];
82 }
83
84 $currentRecord++;
85 }
86
87 $offset += $limit;
88
89 } while ( $fetched_count == $limit ); // Continue if the last batch was full
90
91 // Loop over the $users_meta_list and $scan_counter add the plugins/themes names from the dictionary if they are empty
92 // This is because load_scan_results_to_rows() only loads the names of the plugins/themes that are currently installed
93 if ( ADBC_VERSION_TYPE === 'PREMIUM' )
94 ADBC_Dictionary::add_missing_addons_names_from_dictionary( $users_meta_list, $scan_counter, 'users_meta' );
95
96 // Calculate total number of pages to verify that the current page sent by the user is within the range
97 $total_real_pages = max( 1, ceil( $total_users_meta / $filters['items_per_page'] ) );
98
99 return ADBC_Rest::success( "", [
100 'items' => $users_meta_list,
101 'total_items' => $total_users_meta,
102 'real_current_page' => min( $filters['current_page'], $total_real_pages ),
103 'categorization_count' => $scan_counter->get_categorization_count(),
104 'plugins_count' => $scan_counter->get_plugins_count(),
105 'themes_count' => $scan_counter->get_themes_count(),
106 ] );
107 }
108
109 /**
110 * Get the users meta list that satisfy the UI filters.
111 *
112 * @param array $filters Output of sanitize_filters().
113 * @param int $limit Limit for the number of rows to return.
114 * @param int $offset Offset for the number of rows to return.
115 *
116 * @return array List of users meta that satisfy the filters.
117 */
118 private static function get_users_meta_list_batch( $filters, $limit, $offset ) {
119
120 global $wpdb;
121 $truncate_length = self::TRUNCATE_LENGTH;
122 $site_id = get_current_blog_id(); // get the main site id
123
124 $params[] = absint( $site_id );
125 $duplicate_join_sql = '';
126
127 /* ────────────────────────────────────────────────────────────
128 * Build a safe ORDER BY clause
129 * ────────────────────────────────────────────────────────────*/
130 $allowed_columns = [
131 'meta_key' => '`name`',
132 'size' => 'size',
133 'user_id' => '`user_id`',
134 ];
135
136 $sort_col = $filters['sort_by'] ?? '';
137 $sort_dir = strtoupper( $filters['sort_order'] ?? 'ASC' );
138 $sort_dir = ( $sort_dir === 'DESC' ) ? 'DESC' : 'ASC';
139
140 // Add 'order by' clause if the column is allowed.
141 $order_by_sql = isset( $allowed_columns[ $sort_col ] )
142 ? "ORDER BY {$allowed_columns[ $sort_col ]} {$sort_dir}"
143 : '';
144
145 /* ────────────────────────────────────────────────────────────
146 * Assemble the dynamic WHERE parts
147 * ────────────────────────────────────────────────────────────*/
148 $where = [];
149
150 /* — Unused filter — */
151 if ( isset( $filters['unused'] ) && in_array( $filters['unused'], [ 'yes', 'no' ], true ) ) {
152 $unused_sql = "main.user_id NOT IN (SELECT ID FROM {$wpdb->users})";
153 if ( $filters['unused'] === 'yes' ) {
154 $where[] = $unused_sql;
155 } else {
156 $where[] = "NOT ( {$unused_sql} )";
157 }
158 }
159
160 /* — Size ≥ threshold — */
161 if ( ! empty( $filters['size'] ) && (int) $filters['size'] > 0 ) {
162 $bytes = ADBC_Common_Utils::convert_size_to_bytes(
163 $filters['size'],
164 $filters['size_unit']
165 );
166 $where[] = 'OCTET_LENGTH(main.`meta_value`) >= %d';
167 $params[] = $bytes;
168 }
169
170 /* — Search filter — */
171 if ( ! empty( $filters['search_for'] ) && ! empty( $filters['search_in'] ) ) {
172
173 $needle = '%' . $wpdb->esc_like( $filters['search_for'] ) . '%';
174
175 switch ( $filters['search_in'] ) {
176 case 'name':
177 $where[] = 'main.`meta_key` LIKE %s';
178 $params[] = $needle;
179 break;
180
181 case 'value':
182 $where[] = 'main.`meta_value` LIKE %s';
183 $params[] = $needle;
184 break;
185
186 case 'all':
187 // Search in both columns
188 $where[] = '(main.`meta_key` LIKE %s OR main.`meta_value` LIKE %s)';
189 $params[] = $needle; // for meta_key
190 $params[] = $needle; // for meta_value
191 break;
192 }
193 }
194
195 /* — Duplicated filter (optimized, no correlated subquery) — */
196 if ( isset( $filters['duplicated'] ) && in_array( $filters['duplicated'], [ 'yes', 'no' ], true ) ) {
197
198 // Build a derived table of groups: per (user_id, meta_key, value-hash),
199 // compute min(umeta_id) and count(*).
200 $dup_subquery = "
201 SELECT
202 user_id,
203 meta_key,
204 CRC32(meta_value) AS vhash,
205 MIN(umeta_id) AS min_umeta_id,
206 COUNT(*) AS cnt
207 FROM {$wpdb->usermeta}
208 GROUP BY user_id, meta_key, CRC32(meta_value)
209 ";
210
211 $duplicate_join_sql = "
212 LEFT JOIN ( {$dup_subquery} ) dupg
213 ON dupg.user_id = main.user_id
214 AND dupg.meta_key = main.meta_key
215 AND dupg.vhash = CRC32(main.meta_value)
216 ";
217
218 if ( $filters['duplicated'] === 'yes' ) {
219 // Only rows that belong to a group with more than one row,
220 // AND that are NOT the minimal umeta_id (i.e. the “duplicate” ones).
221 $where[] = '(dupg.cnt > 1 AND main.umeta_id > dupg.min_umeta_id)';
222 } else {
223 // Only rows that are NOT duplicates:
224 // - either not in any group (cnt is NULL)
225 // - or they are the minimal umeta_id in their group.
226 $where[] = '(dupg.cnt IS NULL OR main.umeta_id = dupg.min_umeta_id)';
227 }
228 }
229
230 $where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : '';
231 $params[] = absint( $limit );
232 $params[] = absint( $offset );
233
234 // Final SQL and fetch
235 $sql = $wpdb->prepare(
236 "SELECT
237 main.`meta_key` AS name,
238 main.`umeta_id` AS umeta_id,
239 main.`user_id` AS user_id,
240 %d AS site_id,
241 SUBSTRING(main.`meta_value`, 1, {$truncate_length}) AS value,
242 OCTET_LENGTH(main.`meta_value`) AS size
243 FROM {$wpdb->usermeta} main
244 {$duplicate_join_sql}
245 {$where_sql}
246 {$order_by_sql}
247 LIMIT %d OFFSET %d
248 ",
249 ...$params
250 );
251
252 return $wpdb->get_results( $sql, OBJECT );
253 }
254
255 /**
256 * Get the count of big users meta.
257 *
258 * @return int Total count of big users meta.
259 */
260 public static function count_big_users_meta() {
261
262 global $wpdb;
263
264 $count = (int) $wpdb->get_var(
265 $wpdb->prepare(
266 "SELECT COUNT(*)
267 FROM {$wpdb->usermeta}
268 WHERE OCTET_LENGTH(meta_value) > %d
269 ",
270 self::BIG_USERMETA_THRESHOLD_WARNING
271 )
272 );
273
274 return $count;
275 }
276
277 /**
278 * Count the total number of users meta that are not scanned.
279 *
280 * @return int Total not scanned users meta.
281 */
282 public static function count_total_not_scanned_users_meta() {
283
284 $total_not_scanned = 0;
285 $limit = ADBC_Settings::instance()->get_setting( 'database_rows_batch' );
286 $offset = 0;
287
288 do { // Loop through all users meta in batches of $limit to avoid memory issues
289
290 $users_meta = self::get_users_meta_names( '', $limit, $offset, false );
291 $fetched_count = count( $users_meta );
292 $not_scanned_count = 0;
293
294 if ( ADBC_VERSION_TYPE === 'PREMIUM' )
295 $not_scanned_count = ADBC_Scan_Utils::count_not_scanned_items_in_list( 'users_meta', $users_meta );
296 else
297 $not_scanned_count = ADBC_Common_Model::count_not_scanned_items_in_list_for_free( 'users_meta', $users_meta );
298
299 $total_not_scanned += $not_scanned_count;
300
301 $offset += $limit;
302
303 } while ( $fetched_count == $limit ); // Continue if the last batch was full
304
305 return $total_not_scanned;
306
307 }
308
309 /**
310 * Get users meta keys for a specific site.
311 *
312 * @param string $site_prefix Site prefix (not used for usermeta as it's a single table).
313 * @param int $limit Limit.
314 * @param int $offset Offset.
315 * @param boolean $keyed wether or not to key the array by names
316 *
317 * @return array Users meta keys.
318 */
319 public static function get_users_meta_names( $site_prefix, $limit, $offset, $keyed = true ) {
320
321 global $wpdb;
322
323 $query = $wpdb->prepare(
324 "SELECT meta_key
325 FROM {$wpdb->usermeta}
326 LIMIT %d OFFSET %d",
327 absint( $limit ),
328 absint( $offset )
329 );
330
331 $users_meta_names = $wpdb->get_col( $query );
332
333 if ( $keyed )
334 return array_fill_keys( $users_meta_names, true );
335 else
336 return $users_meta_names;
337
338 }
339
340 /**
341 * Get users meta keys from their ids in a specific site prefix.
342 *
343 * @param string $site_prefix Site prefix (not used for usermeta as it's a single table).
344 * @param array $users_meta_ids Users meta ids to get their keys.
345 *
346 * @return array Associative users meta keys.
347 */
348 public static function get_users_meta_names_from_ids( $site_prefix, $users_meta_ids ) {
349
350 global $wpdb;
351
352 if ( empty( $users_meta_ids ) )
353 return [];
354
355 $in_placeholders = implode( ',', array_fill( 0, count( $users_meta_ids ), '%d' ) );
356
357 $query = $wpdb->prepare(
358 "SELECT meta_key
359 FROM {$wpdb->usermeta}
360 WHERE umeta_id IN ($in_placeholders)",
361 ...$users_meta_ids
362 );
363
364 $users_meta_names = $wpdb->get_col( $query );
365
366 // transform the users_meta names array to associative array with the option name as key and true as value
367 $users_meta_names = array_fill_keys( $users_meta_names, true );
368
369 return $users_meta_names;
370
371 }
372
373 /**
374 * Delete grouped users meta.
375 *
376 * @param array $selected_items Selected users meta to delete.
377 *
378 * @return array An array of users meta names that were not processed (not deleted).
379 */
380 public static function delete_users_meta( $selected_items ) {
381
382 $cleanup_method = ADBC_Settings::instance()->get_setting( 'sql_or_native_cleanup_method' );
383
384 if ( $cleanup_method === 'native' ) {
385 return self::delete_users_meta_native( $selected_items );
386 }
387
388 return self::delete_users_meta_sql( $selected_items );
389
390 }
391
392 /**
393 * Deletes users meta using WordPress native delete logic (current logic, unchanged).
394 *
395 * @param array $selected_items
396 * @return array Not processed users meta names.
397 */
398 protected static function delete_users_meta_native( $selected_items ) {
399
400 global $wpdb;
401
402 $not_processed = [];
403
404 foreach ( $selected_items as $selected ) {
405
406 // try deleting using standard wordpress function
407 $success = delete_metadata_by_mid( 'user', $selected['id'] );
408
409 // try deleting using direct sql by umeta id to be sure there's no problem in the name
410 if ( ! $success )
411 $success = $wpdb->delete( $wpdb->usermeta, array( 'umeta_id' => $selected['id'] ) );
412
413 if ( ! $success )
414 $not_processed[] = $selected['name'];
415
416 }
417
418 return $not_processed;
419
420 }
421
422 /**
423 * Deletes users meta using direct SQL (bulk delete by umeta_id).
424 *
425 * @param array $selected_items
426 * @return array Not processed users meta names.
427 */
428 protected static function delete_users_meta_sql( $selected_items ) {
429
430 global $wpdb;
431
432 $not_processed = [];
433
434 $ids = [];
435 foreach ( $selected_items as $selected )
436 $ids[] = $selected['id'];
437
438 $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );
439
440 // Bulk delete.
441 $sql = "DELETE FROM {$wpdb->usermeta} WHERE umeta_id IN ($placeholders)";
442 $sql = $wpdb->prepare( $sql, ...$ids );
443 $wpdb->query( $sql );
444
445 // Identify any remaining rows (not processed) deterministically (names == meta_key).
446 $check_sql = "SELECT meta_key FROM {$wpdb->usermeta} WHERE umeta_id IN ($placeholders)";
447 $check_sql = $wpdb->prepare( $check_sql, ...$ids );
448
449 $not_processed = $wpdb->get_col( $check_sql );
450
451 return $not_processed;
452
453 }
454
455 /**
456 * Get users meta names that still exist from a provided list.
457 * Note: usermeta is a single table for the whole installation (main site), no per-site variants.
458 *
459 * @param array $users_meta_names List of meta_key names to check for existence.
460 *
461 * @return array Existing names found in usermeta table.
462 */
463 public static function get_users_meta_names_that_exists_from_list( $users_meta_names ) {
464
465 global $wpdb;
466
467 if ( empty( $users_meta_names ) || ! is_array( $users_meta_names ) )
468 return [];
469
470 $in_placeholders = implode( ',', array_fill( 0, count( $users_meta_names ), '%s' ) );
471
472 $sql = $wpdb->prepare(
473 "SELECT DISTINCT meta_key AS name
474 FROM {$wpdb->usermeta}
475 WHERE meta_key IN ( {$in_placeholders} )",
476 ...$users_meta_names
477 );
478
479 $existing_names = $wpdb->get_col( $sql );
480
481 return array_values( array_unique( array_filter( (array) $existing_names ) ) );
482 }
483
484 /**
485 * Count duplicated usermeta
486 *
487 * @return int Total duplicated users meta.
488 */
489 public static function count_duplicated_users_meta() {
490 global $wpdb;
491
492 return (int) $wpdb->get_var( "
493 SELECT COALESCE(SUM(g.cnt - 1), 0)
494 FROM (
495 SELECT
496 user_id,
497 meta_key,
498 CRC32(meta_value) AS vhash,
499 COUNT(*) AS cnt
500 FROM {$wpdb->usermeta}
501 GROUP BY user_id, meta_key, CRC32(meta_value)
502 HAVING cnt > 1
503 ) AS g
504 " );
505 }
506
507 /**
508 * Count unused usermeta (user_id not existing)
509 *
510 * @return int Total unused users meta.
511 */
512 public static function count_unused_users_meta() {
513 global $wpdb;
514 return (int) $wpdb->get_var( "
515 SELECT COUNT(*)
516 FROM {$wpdb->usermeta} main
517 LEFT JOIN {$wpdb->users} u ON u.ID = main.user_id
518 WHERE u.ID IS NULL
519 " );
520 }
521
522 /**
523 * Get total users meta count.
524 *
525 * @return int Total users meta count.
526 */
527 public static function get_total_users_meta_count() {
528 global $wpdb;
529 return (int) $wpdb->get_var( "
530 SELECT COUNT(*)
531 FROM {$wpdb->usermeta}
532 " );
533 }
534
535 }