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-posts-meta.php
869 lines
| 1 | <?php |
| 2 | |
| 3 | // Exit if accessed directly |
| 4 | if ( ! defined( 'ABSPATH' ) ) |
| 5 | exit; |
| 6 | |
| 7 | /** |
| 8 | * ADBC posts meta class. |
| 9 | * |
| 10 | * This class provides the posts meta functions. |
| 11 | */ |
| 12 | class ADBC_Posts_Meta { |
| 13 | |
| 14 | private const BIG_POSTMETA_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 posts meta list for the endpoint. |
| 19 | * |
| 20 | * @param array $filters Output of sanitize_filters(). |
| 21 | * |
| 22 | * @return WP_REST_Response The list of posts meta. |
| 23 | */ |
| 24 | public static function get_posts_meta_list( $filters ) { |
| 25 | |
| 26 | // Prepare variables |
| 27 | $posts_meta_list = []; |
| 28 | $total_posts_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 posts meta in batches of $limit to avoid memory issues |
| 40 | |
| 41 | $posts_meta = self::get_posts_meta_list_batch( $filters, $limit, $offset ); |
| 42 | $fetched_count = count( $posts_meta ); |
| 43 | |
| 44 | if ( ADBC_VERSION_TYPE === 'PREMIUM' ) |
| 45 | ADBC_Scan_Results::instance()->load_scan_results_to_items_rows( $posts_meta, 'posts_meta' ); |
| 46 | else |
| 47 | ADBC_Common_Model::load_scan_results_to_items_for_free_version( $posts_meta ); |
| 48 | |
| 49 | ADBC_Hardcoded_Items::instance()->load_hardcoded_scan_results_to_items_rows( $posts_meta, 'posts_meta' ); // Load hardcoded items to the posts meta rows |
| 50 | |
| 51 | foreach ( $posts_meta as $index => $post_meta ) { |
| 52 | |
| 53 | $scan_counter->refresh_categorization_count( $post_meta->belongs_to ); |
| 54 | |
| 55 | if ( ! ADBC_Common_Model::is_item_satisfies_belongs_to( $filters, $post_meta->belongs_to ) ) |
| 56 | continue; |
| 57 | |
| 58 | $total_posts_meta++; // Count posts 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 | $posts_meta_list[] = [ |
| 64 | // This id is used to identify the post meta in the frontend and take actions on it |
| 65 | 'composite_id' => [ |
| 66 | 'items_type' => 'posts_meta', |
| 67 | 'site_id' => (int) $post_meta->site_id, |
| 68 | 'id' => (int) $post_meta->meta_id, |
| 69 | 'name' => $post_meta->name, |
| 70 | ], |
| 71 | 'id' => $post_meta->meta_id, |
| 72 | 'name' => $post_meta->name, // Used in the known addons modal & "show value modal". To be generic and work for all items types. |
| 73 | 'meta_key' => $post_meta->name, |
| 74 | 'value' => $post_meta->value, |
| 75 | 'size' => $post_meta->size, |
| 76 | 'post_id' => $post_meta->post_id, |
| 77 | 'site_id' => $post_meta->site_id, |
| 78 | 'belongs_to' => $post_meta->belongs_to, |
| 79 | 'known_plugins' => $post_meta->known_plugins, |
| 80 | 'known_themes' => $post_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 $posts_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( $posts_meta_list, $scan_counter, 'posts_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_posts_meta / $filters['items_per_page'] ) ); |
| 98 | |
| 99 | return ADBC_Rest::success( "", [ |
| 100 | 'items' => $posts_meta_list, |
| 101 | 'total_items' => $total_posts_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 posts 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 posts meta that satisfy the filters. |
| 117 | */ |
| 118 | private static function get_posts_meta_list_batch( $filters, $limit, $offset ) { |
| 119 | |
| 120 | global $wpdb; |
| 121 | |
| 122 | $sites_list = ADBC_Sites::instance()->get_sites_list( $filters['site_id'] ); |
| 123 | |
| 124 | // If there is only ONE site to query (single-site install or a specific site selected in filters), |
| 125 | // we can avoid UNION and the derived table, and query that table directly. |
| 126 | $is_single_site_query = ( count( $sites_list ) === 1 ); |
| 127 | |
| 128 | /* ──────────────────────────────────────────────────────────── |
| 129 | * Build a safe ORDER BY clause |
| 130 | * ────────────────────────────────────────────────────────────*/ |
| 131 | $allowed_columns = [ |
| 132 | 'meta_key' => '`name`', |
| 133 | 'size' => '`size`', |
| 134 | 'post_id' => '`post_id`', |
| 135 | 'site_id' => '`site_id`', |
| 136 | ]; |
| 137 | |
| 138 | $sort_col = $filters['sort_by'] ?? ''; |
| 139 | $sort_dir = strtoupper( $filters['sort_order'] ?? 'ASC' ); |
| 140 | $sort_dir = ( $sort_dir === 'DESC' ) ? 'DESC' : 'ASC'; |
| 141 | |
| 142 | // Add 'order by' clause if the column is allowed. |
| 143 | $order_by_sql = isset( $allowed_columns[ $sort_col ] ) |
| 144 | ? "ORDER BY {$allowed_columns[ $sort_col ]} {$sort_dir}" |
| 145 | : ''; |
| 146 | |
| 147 | /* ──────────────────────────────────────────────────────────── |
| 148 | * Single-site path (no UNION, no derived table) |
| 149 | * ────────────────────────────────────────────────────────────*/ |
| 150 | if ( $is_single_site_query ) { |
| 151 | |
| 152 | $site = reset( $sites_list ); |
| 153 | $table_name = $site['prefix'] . 'postmeta'; |
| 154 | $site_id = $site['id']; |
| 155 | |
| 156 | $sql = self::prepare_posts_meta_list_sql_for_single_site( |
| 157 | $site_id, |
| 158 | $table_name, |
| 159 | $filters, |
| 160 | $order_by_sql, |
| 161 | $limit, |
| 162 | $offset |
| 163 | ); |
| 164 | |
| 165 | return $wpdb->get_results( $sql, OBJECT ); |
| 166 | } |
| 167 | |
| 168 | /* ──────────────────────────────────────────────────────────── |
| 169 | * Multisite path (UNION across all sites) |
| 170 | * ────────────────────────────────────────────────────────────*/ |
| 171 | |
| 172 | $union_queries = []; |
| 173 | |
| 174 | $needs_collation_fix = ! ADBC_Database::is_collaction_unified( 'postmeta', false, $filters['site_id'] ); |
| 175 | |
| 176 | // Offset starts from 0, so we need to add the limit to it to increment the |
| 177 | // number of rows to fetch in each iteration. |
| 178 | $total_rows_to_fetch = $offset + $limit; |
| 179 | |
| 180 | foreach ( $sites_list as $site ) { |
| 181 | $table_name = $site['prefix'] . 'postmeta'; // Get the postmeta table name for the current site |
| 182 | $site_id = $site['id']; // Get the site ID for the current site |
| 183 | $union_queries[] = self::prepare_posts_meta_list_sql_for_union( |
| 184 | $site_id, |
| 185 | $table_name, |
| 186 | $filters, |
| 187 | $order_by_sql, |
| 188 | $total_rows_to_fetch, |
| 189 | $needs_collation_fix |
| 190 | ); |
| 191 | } |
| 192 | |
| 193 | $union_sql = implode( "\nUNION ALL\n", $union_queries ); |
| 194 | |
| 195 | $sql = $wpdb->prepare( |
| 196 | "SELECT * |
| 197 | FROM ( {$union_sql} ) AS rows_merged |
| 198 | {$order_by_sql} |
| 199 | LIMIT %d OFFSET %d |
| 200 | ", |
| 201 | $limit, |
| 202 | $offset |
| 203 | ); |
| 204 | |
| 205 | return $wpdb->get_results( $sql, OBJECT ); |
| 206 | } |
| 207 | |
| 208 | /** |
| 209 | * Prepare a SQL query string to get the posts meta list for a single site |
| 210 | * (no UNION, no derived table). |
| 211 | * |
| 212 | * @param int $site_id Site ID to query. |
| 213 | * @param string $table_name Postmeta table name to query. |
| 214 | * @param array $filters Output of sanitize_filters(). |
| 215 | * @param string $order_by_sql SQL query clause to order the results. |
| 216 | * @param int $limit Limit for the number of rows to return. |
| 217 | * @param int $offset Offset for the number of rows to return. |
| 218 | * |
| 219 | * @return string SQL query to get the posts meta list for a single site. |
| 220 | */ |
| 221 | private static function prepare_posts_meta_list_sql_for_single_site( $site_id, $table_name, $filters, $order_by_sql, $limit, $offset ) { |
| 222 | |
| 223 | global $wpdb; |
| 224 | |
| 225 | $truncate_length = self::TRUNCATE_LENGTH; |
| 226 | $params = [ absint( $site_id ) ]; // %d for site_id in SELECT |
| 227 | $where = []; |
| 228 | $duplicate_join_sql = ''; |
| 229 | |
| 230 | /* ──────────────────────────────────────────────────────────── |
| 231 | * 1. Unused filter |
| 232 | * ────────────────────────────────────────────────────────────*/ |
| 233 | if ( isset( $filters['unused'] ) && in_array( $filters['unused'], [ 'yes', 'no' ], true ) ) { |
| 234 | $posts_table = str_replace( 'postmeta', 'posts', $table_name ); |
| 235 | $unused_sql = "main.post_id NOT IN (SELECT ID FROM {$posts_table})"; |
| 236 | if ( 'yes' === $filters['unused'] ) { |
| 237 | $where[] = $unused_sql; |
| 238 | } else { |
| 239 | $where[] = "NOT ( {$unused_sql} )"; |
| 240 | } |
| 241 | } |
| 242 | |
| 243 | /* ──────────────────────────────────────────────────────────── |
| 244 | * 2. Size ≥ threshold |
| 245 | * ────────────────────────────────────────────────────────────*/ |
| 246 | if ( ! empty( $filters['size'] ) && (int) $filters['size'] > 0 ) { |
| 247 | $bytes = ADBC_Common_Utils::convert_size_to_bytes( |
| 248 | $filters['size'], |
| 249 | $filters['size_unit'] |
| 250 | ); |
| 251 | $where[] = 'OCTET_LENGTH(main.`meta_value`) >= %d'; |
| 252 | $params[] = $bytes; |
| 253 | } |
| 254 | |
| 255 | /* ──────────────────────────────────────────────────────────── |
| 256 | * 3. Search filter |
| 257 | * ────────────────────────────────────────────────────────────*/ |
| 258 | if ( ! empty( $filters['search_for'] ) && ! empty( $filters['search_in'] ) ) { |
| 259 | |
| 260 | $needle = '%' . $wpdb->esc_like( $filters['search_for'] ) . '%'; |
| 261 | |
| 262 | switch ( $filters['search_in'] ) { |
| 263 | case 'name': |
| 264 | $where[] = 'main.`meta_key` LIKE %s'; |
| 265 | $params[] = $needle; |
| 266 | break; |
| 267 | |
| 268 | case 'value': |
| 269 | $where[] = 'main.`meta_value` LIKE %s'; |
| 270 | $params[] = $needle; |
| 271 | break; |
| 272 | |
| 273 | case 'all': |
| 274 | // Search in both columns |
| 275 | $where[] = '(main.`meta_key` LIKE %s OR main.`meta_value` LIKE %s)'; |
| 276 | $params[] = $needle; // for meta_key |
| 277 | $params[] = $needle; // for meta_value |
| 278 | break; |
| 279 | } |
| 280 | } |
| 281 | |
| 282 | /* ──────────────────────────────────────────────────────────── |
| 283 | * 4. Duplicated filter (optimized, no correlated subquery) |
| 284 | * ────────────────────────────────────────────────────────────*/ |
| 285 | if ( isset( $filters['duplicated'] ) && in_array( $filters['duplicated'], [ 'yes', 'no' ], true ) ) { |
| 286 | |
| 287 | // Build a derived table of groups: per (post_id, meta_key, value-hash), |
| 288 | // compute min(meta_id) and count(*). |
| 289 | $dup_subquery = " |
| 290 | SELECT |
| 291 | post_id, |
| 292 | meta_key, |
| 293 | CRC32(meta_value) AS vhash, |
| 294 | MIN(meta_id) AS min_meta_id, |
| 295 | COUNT(*) AS cnt |
| 296 | FROM {$table_name} |
| 297 | GROUP BY post_id, meta_key, CRC32(meta_value) |
| 298 | "; |
| 299 | |
| 300 | $duplicate_join_sql = " |
| 301 | LEFT JOIN ( {$dup_subquery} ) dupg |
| 302 | ON dupg.post_id = main.post_id |
| 303 | AND dupg.meta_key = main.meta_key |
| 304 | AND dupg.vhash = CRC32(main.meta_value) |
| 305 | "; |
| 306 | |
| 307 | if ( 'yes' === $filters['duplicated'] ) { |
| 308 | // Only rows that belong to a group with more than one row, |
| 309 | // AND that are NOT the minimal meta_id (i.e. the “duplicate” ones). |
| 310 | $where[] = '(dupg.cnt > 1 AND main.meta_id > dupg.min_meta_id)'; |
| 311 | } else { |
| 312 | // Only rows that are NOT duplicates: |
| 313 | // - either not in any group (cnt is NULL) |
| 314 | // - or they are the minimal meta_id in their group. |
| 315 | $where[] = '(dupg.cnt IS NULL OR main.meta_id = dupg.min_meta_id)'; |
| 316 | } |
| 317 | } |
| 318 | |
| 319 | $where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; |
| 320 | |
| 321 | // Add limit & offset at the end of the params array |
| 322 | $params[] = absint( $limit ); |
| 323 | $params[] = absint( $offset ); |
| 324 | |
| 325 | /* ──────────────────────────────────────────────────────────── |
| 326 | * Final SQL |
| 327 | * ────────────────────────────────────────────────────────────*/ |
| 328 | $sql = $wpdb->prepare( |
| 329 | "SELECT |
| 330 | main.`meta_key` AS name, |
| 331 | main.`meta_id` AS meta_id, |
| 332 | main.`post_id` AS post_id, |
| 333 | SUBSTRING(main.`meta_value`, 1, {$truncate_length}) AS value, |
| 334 | OCTET_LENGTH(main.`meta_value`) AS size, |
| 335 | %d AS site_id |
| 336 | FROM {$table_name} main |
| 337 | {$duplicate_join_sql} |
| 338 | {$where_sql} |
| 339 | {$order_by_sql} |
| 340 | LIMIT %d OFFSET %d |
| 341 | ", |
| 342 | ...$params |
| 343 | ); |
| 344 | |
| 345 | return $sql; |
| 346 | } |
| 347 | |
| 348 | /** |
| 349 | * Prepare a SQL query string to get the posts meta list that satisfy the UI filters. It will be used in a UNION query to get all posts meta in all sites. |
| 350 | * |
| 351 | * @param int $site_id Site ID to query. |
| 352 | * @param string $table_name Postmeta table name to query. |
| 353 | * @param array $filters Output of sanitize_filters(). |
| 354 | * @param string $order_by_sql SQL query clause to order the results. |
| 355 | * @param int $total_rows_to_fetch Limit for the number of rows to return. |
| 356 | * |
| 357 | * @return string SQL query to get the posts meta list. |
| 358 | */ |
| 359 | private static function prepare_posts_meta_list_sql_for_union( $site_id, $table_name, $filters, $order_by_sql, $total_rows_to_fetch, $needs_collation_fix = false ) { |
| 360 | |
| 361 | global $wpdb; |
| 362 | |
| 363 | $truncate_length = self::TRUNCATE_LENGTH; |
| 364 | $params = [ absint( $site_id ) ]; // Place the site_id at the beginning of the params array |
| 365 | $where = []; |
| 366 | $duplicate_join_sql = ''; |
| 367 | |
| 368 | /* ──────────────────────────────────────────────────────────── |
| 369 | * Unused filter |
| 370 | * ────────────────────────────────────────────────────────────*/ |
| 371 | if ( isset( $filters['unused'] ) && in_array( $filters['unused'], [ 'yes', 'no' ], true ) ) { |
| 372 | $posts_table = str_replace( 'postmeta', 'posts', $table_name ); |
| 373 | $unused_sql = "main.post_id NOT IN (SELECT ID FROM {$posts_table})"; |
| 374 | if ( 'yes' === $filters['unused'] ) { |
| 375 | $where[] = $unused_sql; |
| 376 | } else { |
| 377 | $where[] = "NOT ( {$unused_sql} )"; |
| 378 | } |
| 379 | } |
| 380 | |
| 381 | /* ──────────────────────────────────────────────────────────── |
| 382 | * Size ≥ threshold |
| 383 | * ────────────────────────────────────────────────────────────*/ |
| 384 | if ( ! empty( $filters['size'] ) && (int) $filters['size'] > 0 ) { |
| 385 | $bytes = ADBC_Common_Utils::convert_size_to_bytes( |
| 386 | $filters['size'], |
| 387 | $filters['size_unit'] |
| 388 | ); |
| 389 | $where[] = 'OCTET_LENGTH(main.`meta_value`) >= %d'; |
| 390 | $params[] = $bytes; |
| 391 | } |
| 392 | |
| 393 | /* ──────────────────────────────────────────────────────────── |
| 394 | * Search filter |
| 395 | * ────────────────────────────────────────────────────────────*/ |
| 396 | if ( ! empty( $filters['search_for'] ) && ! empty( $filters['search_in'] ) ) { |
| 397 | |
| 398 | $needle = '%' . $wpdb->esc_like( $filters['search_for'] ) . '%'; |
| 399 | |
| 400 | switch ( $filters['search_in'] ) { |
| 401 | case 'name': |
| 402 | $where[] = 'main.`meta_key` LIKE %s'; |
| 403 | $params[] = $needle; |
| 404 | break; |
| 405 | |
| 406 | case 'value': |
| 407 | $where[] = 'main.`meta_value` LIKE %s'; |
| 408 | $params[] = $needle; |
| 409 | break; |
| 410 | |
| 411 | case 'all': |
| 412 | // Search in both columns |
| 413 | $where[] = '(main.`meta_key` LIKE %s OR main.`meta_value` LIKE %s)'; |
| 414 | $params[] = $needle; // for meta_key |
| 415 | $params[] = $needle; // for meta_value |
| 416 | break; |
| 417 | } |
| 418 | } |
| 419 | |
| 420 | /* ──────────────────────────────────────────────────────────── |
| 421 | * Duplicated filter (optimized, no correlated subquery) |
| 422 | * ────────────────────────────────────────────────────────────*/ |
| 423 | if ( isset( $filters['duplicated'] ) && in_array( $filters['duplicated'], [ 'yes', 'no' ], true ) ) { |
| 424 | |
| 425 | // Build a derived table of groups: per (post_id, meta_key, value-hash), |
| 426 | // compute min(meta_id) and count(*). |
| 427 | $dup_subquery = " |
| 428 | SELECT |
| 429 | post_id, |
| 430 | meta_key, |
| 431 | CRC32(meta_value) AS vhash, |
| 432 | MIN(meta_id) AS min_meta_id, |
| 433 | COUNT(*) AS cnt |
| 434 | FROM {$table_name} |
| 435 | GROUP BY post_id, meta_key, CRC32(meta_value) |
| 436 | "; |
| 437 | |
| 438 | $duplicate_join_sql = " |
| 439 | LEFT JOIN ( {$dup_subquery} ) dupg |
| 440 | ON dupg.post_id = main.post_id |
| 441 | AND dupg.meta_key = main.meta_key |
| 442 | AND dupg.vhash = CRC32(main.meta_value) |
| 443 | "; |
| 444 | |
| 445 | if ( 'yes' === $filters['duplicated'] ) { |
| 446 | // Only rows that belong to a group with more than one row, |
| 447 | // AND that are NOT the minimal meta_id (i.e. the “duplicate” ones). |
| 448 | $where[] = '(dupg.cnt > 1 AND main.meta_id > dupg.min_meta_id)'; |
| 449 | } else { |
| 450 | // Only rows that are NOT duplicates: |
| 451 | // - either not in any group (cnt is NULL) |
| 452 | // - or they are the minimal meta_id in their group. |
| 453 | $where[] = '(dupg.cnt IS NULL OR main.meta_id = dupg.min_meta_id)'; |
| 454 | } |
| 455 | } |
| 456 | |
| 457 | $where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; |
| 458 | |
| 459 | // Add the limit to the params array |
| 460 | $params[] = absint( $total_rows_to_fetch ); |
| 461 | |
| 462 | $collation = ! empty( $wpdb->collate ) ? $wpdb->collate : 'utf8mb4_unicode_ci'; |
| 463 | |
| 464 | $name_expr = $needs_collation_fix |
| 465 | ? "CONVERT(main.`meta_key` USING utf8mb4) COLLATE {$collation}" |
| 466 | : "main.`meta_key`"; |
| 467 | |
| 468 | $value_expr = $needs_collation_fix |
| 469 | ? "CONVERT(SUBSTRING(main.`meta_value`, 1, {$truncate_length}) USING utf8mb4) COLLATE {$collation}" |
| 470 | : "SUBSTRING(main.`meta_value`, 1, {$truncate_length})"; |
| 471 | |
| 472 | /* ──────────────────────────────────────────────────────────── |
| 473 | * Final SQL |
| 474 | * ────────────────────────────────────────────────────────────*/ |
| 475 | $sql = $wpdb->prepare( |
| 476 | "SELECT |
| 477 | {$name_expr} AS name, |
| 478 | main.`meta_id` AS meta_id, |
| 479 | main.`post_id` AS post_id, |
| 480 | {$value_expr} AS value, |
| 481 | OCTET_LENGTH(main.`meta_value`) AS size, |
| 482 | %d AS site_id |
| 483 | FROM {$table_name} main |
| 484 | {$duplicate_join_sql} |
| 485 | {$where_sql} |
| 486 | {$order_by_sql} |
| 487 | LIMIT %d |
| 488 | ", |
| 489 | ...$params |
| 490 | ); |
| 491 | |
| 492 | return '(' . $sql . ')'; |
| 493 | } |
| 494 | |
| 495 | /** |
| 496 | * Get the count of big posts meta in all sites. |
| 497 | * |
| 498 | * @return int Total count of big posts meta. |
| 499 | */ |
| 500 | public static function count_big_posts_meta() { |
| 501 | |
| 502 | global $wpdb; |
| 503 | $total_big_posts_meta = 0; |
| 504 | |
| 505 | $sites_prefixes = array_keys( ADBC_Sites::instance()->get_all_prefixes() ); |
| 506 | |
| 507 | foreach ( $sites_prefixes as $site_prefix ) { |
| 508 | |
| 509 | $table = $site_prefix . "postmeta"; |
| 510 | |
| 511 | $count = (int) $wpdb->get_var( |
| 512 | $wpdb->prepare( |
| 513 | "SELECT COUNT(*) |
| 514 | FROM `$table` |
| 515 | WHERE OCTET_LENGTH(meta_value) > %d", |
| 516 | self::BIG_POSTMETA_THRESHOLD_WARNING |
| 517 | ) |
| 518 | ); |
| 519 | |
| 520 | $total_big_posts_meta += $count; |
| 521 | } |
| 522 | |
| 523 | return $total_big_posts_meta; |
| 524 | } |
| 525 | |
| 526 | /** |
| 527 | * Count duplicated postmeta across all sites. |
| 528 | * A duplicated postmeta is defined as same (meta_key, meta_value, post_id) |
| 529 | * where only one row per group is considered "original" and the rest are duplicates. |
| 530 | * |
| 531 | * @return int |
| 532 | */ |
| 533 | public static function count_duplicated_posts_meta() { |
| 534 | |
| 535 | global $wpdb; |
| 536 | |
| 537 | $total = 0; |
| 538 | |
| 539 | $sites_prefixes = array_keys( ADBC_Sites::instance()->get_all_prefixes() ); |
| 540 | |
| 541 | foreach ( $sites_prefixes as $site_prefix ) { |
| 542 | |
| 543 | $table = $site_prefix . 'postmeta'; |
| 544 | |
| 545 | // For each (post_id, meta_key, CRC32(meta_value)) group: |
| 546 | // - cnt = number of rows |
| 547 | // - duplicates in that group = cnt - 1 (if cnt > 1) |
| 548 | // Total duplicates = SUM(cnt - 1) over all groups where cnt > 1. |
| 549 | $count = (int) $wpdb->get_var( " |
| 550 | SELECT COALESCE(SUM(g.cnt - 1), 0) |
| 551 | FROM ( |
| 552 | SELECT |
| 553 | post_id, |
| 554 | meta_key, |
| 555 | CRC32(meta_value) AS vhash, |
| 556 | COUNT(*) AS cnt |
| 557 | FROM {$table} |
| 558 | GROUP BY post_id, meta_key, CRC32(meta_value) |
| 559 | HAVING cnt > 1 |
| 560 | ) AS g |
| 561 | " ); |
| 562 | |
| 563 | $total += $count; |
| 564 | } |
| 565 | |
| 566 | return $total; |
| 567 | } |
| 568 | |
| 569 | /** |
| 570 | * Count unused postmeta across all sites. |
| 571 | * An unused postmeta is a meta referencing a non-existing post_id. |
| 572 | * |
| 573 | * @return int |
| 574 | */ |
| 575 | public static function count_unused_posts_meta() { |
| 576 | |
| 577 | global $wpdb; |
| 578 | $total = 0; |
| 579 | |
| 580 | $sites = ADBC_Sites::instance()->get_sites_list(); |
| 581 | foreach ( $sites as $site ) { |
| 582 | $postmeta = $site['prefix'] . 'postmeta'; |
| 583 | $posts = $site['prefix'] . 'posts'; |
| 584 | $count = (int) $wpdb->get_var( " |
| 585 | SELECT COUNT(*) |
| 586 | FROM {$postmeta} main |
| 587 | LEFT JOIN {$posts} p ON p.ID = main.post_id |
| 588 | WHERE p.ID IS NULL |
| 589 | " ); |
| 590 | $total += $count; |
| 591 | } |
| 592 | |
| 593 | return $total; |
| 594 | } |
| 595 | |
| 596 | /** |
| 597 | * Count the total number of posts meta that are not scanned. |
| 598 | * |
| 599 | * @return int Total not scanned posts meta. |
| 600 | */ |
| 601 | public static function count_total_not_scanned_posts_meta() { |
| 602 | |
| 603 | $sites_prefixes = array_keys( ADBC_Sites::instance()->get_all_prefixes() ); |
| 604 | $total_not_scanned = 0; |
| 605 | $limit = ADBC_Settings::instance()->get_setting( 'database_rows_batch' ); |
| 606 | |
| 607 | foreach ( $sites_prefixes as $site_prefix ) { |
| 608 | |
| 609 | $offset = 0; |
| 610 | |
| 611 | do { // Loop through all posts meta in batches of $limit to avoid memory issues |
| 612 | |
| 613 | $posts_meta = self::get_posts_meta_names( $site_prefix, $limit, $offset, false ); |
| 614 | $fetched_count = count( $posts_meta ); |
| 615 | $not_scanned_count = 0; |
| 616 | |
| 617 | if ( ADBC_VERSION_TYPE === 'PREMIUM' ) |
| 618 | $not_scanned_count = ADBC_Scan_Utils::count_not_scanned_items_in_list( "posts_meta", $posts_meta ); |
| 619 | else |
| 620 | $not_scanned_count = ADBC_Common_Model::count_not_scanned_items_in_list_for_free( "posts_meta", $posts_meta ); |
| 621 | |
| 622 | $total_not_scanned += $not_scanned_count; |
| 623 | |
| 624 | $offset += $limit; |
| 625 | |
| 626 | } while ( $fetched_count == $limit ); // Continue if the last batch was full |
| 627 | |
| 628 | } |
| 629 | |
| 630 | return $total_not_scanned; |
| 631 | |
| 632 | } |
| 633 | |
| 634 | /** |
| 635 | * Get post meta keys for a specific site. |
| 636 | * |
| 637 | * @param string $site_prefix Site prefix. |
| 638 | * @param int $limit Limit. |
| 639 | * @param int $offset Offset. |
| 640 | * @param boolean $keyed wether or not to key the array by names |
| 641 | * |
| 642 | * @return array Post meta keys. |
| 643 | */ |
| 644 | public static function get_posts_meta_names( $site_prefix, $limit, $offset, $keyed = true ) { |
| 645 | |
| 646 | global $wpdb; |
| 647 | $table = $site_prefix . 'postmeta'; // $site_prefix is safe to use here as it is validated in the calling function. |
| 648 | |
| 649 | $query = $wpdb->prepare( |
| 650 | "SELECT meta_key FROM `$table` |
| 651 | LIMIT %d OFFSET %d", |
| 652 | absint( $limit ), |
| 653 | absint( $offset ) |
| 654 | ); |
| 655 | |
| 656 | $posts_meta_names = $wpdb->get_col( $query ); |
| 657 | |
| 658 | if ( $keyed ) |
| 659 | return array_fill_keys( $posts_meta_names, true ); |
| 660 | else |
| 661 | return $posts_meta_names; |
| 662 | |
| 663 | } |
| 664 | |
| 665 | /** |
| 666 | * Get post meta keys from their ids in a specific site prefix. |
| 667 | * |
| 668 | * @param string $site_prefix Site prefix of the post meta. |
| 669 | * @param array $posts_meta_ids Post meta ids to get their keys. |
| 670 | * |
| 671 | * @return array Associative post meta keys. |
| 672 | */ |
| 673 | public static function get_posts_meta_names_from_ids( $site_prefix, $posts_meta_ids ) { |
| 674 | |
| 675 | global $wpdb; |
| 676 | $table = $site_prefix . 'postmeta'; // $site_prefix is safe to use here as it is validated in the calling function. |
| 677 | |
| 678 | if ( empty( $posts_meta_ids ) ) |
| 679 | return []; |
| 680 | |
| 681 | $in_placeholders = implode( ',', array_fill( 0, count( $posts_meta_ids ), '%d' ) ); |
| 682 | |
| 683 | $query = $wpdb->prepare( |
| 684 | "SELECT DISTINCT meta_key |
| 685 | FROM `$table` |
| 686 | WHERE meta_id IN ($in_placeholders)", |
| 687 | ...$posts_meta_ids |
| 688 | ); |
| 689 | |
| 690 | $posts_meta_names = $wpdb->get_col( $query ); |
| 691 | |
| 692 | // transform the posts_meta names array to associative array with the option name as key and true as value |
| 693 | $posts_meta_names = array_fill_keys( $posts_meta_names, true ); |
| 694 | |
| 695 | return $posts_meta_names; |
| 696 | |
| 697 | } |
| 698 | |
| 699 | /** |
| 700 | * Delete grouped posts meta. Posts meta are grouped by site ID as key. |
| 701 | * |
| 702 | * @param array $grouped_selected Grouped selected posts meta to delete. |
| 703 | * |
| 704 | * @return array An array of posts meta names that were not processed (not deleted). |
| 705 | */ |
| 706 | public static function delete_posts_meta( $grouped_selected ) { |
| 707 | |
| 708 | $cleanup_method = ADBC_Settings::instance()->get_setting( 'sql_or_native_cleanup_method' ); |
| 709 | |
| 710 | if ( $cleanup_method === 'native' ) { |
| 711 | return self::delete_posts_meta_native( $grouped_selected ); |
| 712 | } |
| 713 | |
| 714 | return self::delete_posts_meta_sql( $grouped_selected ); |
| 715 | |
| 716 | } |
| 717 | |
| 718 | /** |
| 719 | * Deletes posts meta using WordPress native delete logic (current logic, unchanged). |
| 720 | * |
| 721 | * @param array $grouped_selected |
| 722 | * @return array Not processed posts meta names. |
| 723 | */ |
| 724 | protected static function delete_posts_meta_native( $grouped_selected ) { |
| 725 | |
| 726 | global $wpdb; |
| 727 | |
| 728 | $not_processed = []; |
| 729 | |
| 730 | foreach ( $grouped_selected as $site_id => $group ) { |
| 731 | |
| 732 | ADBC_Sites::instance()->switch_to_blog_id( $site_id ); |
| 733 | |
| 734 | foreach ( $group as $selected ) { |
| 735 | |
| 736 | // try deleting using standard wordpress function |
| 737 | $success = delete_metadata_by_mid( 'post', $selected['id'] ); |
| 738 | |
| 739 | // try deleting using direct sql by meta id to be sure there's no problem in the name |
| 740 | if ( ! $success ) |
| 741 | $success = $wpdb->delete( $wpdb->postmeta, array( 'meta_id' => $selected['id'] ) ); |
| 742 | |
| 743 | if ( ! $success ) |
| 744 | $not_processed[] = $selected['name']; |
| 745 | |
| 746 | } |
| 747 | |
| 748 | ADBC_Sites::instance()->restore_blog(); |
| 749 | |
| 750 | } |
| 751 | |
| 752 | return $not_processed; |
| 753 | |
| 754 | } |
| 755 | |
| 756 | /** |
| 757 | * Deletes posts meta using direct SQL (bulk delete by meta_id) for each site. |
| 758 | * |
| 759 | * @param array $grouped_selected |
| 760 | * @return array Not processed posts meta names. |
| 761 | */ |
| 762 | protected static function delete_posts_meta_sql( $grouped_selected ) { |
| 763 | |
| 764 | global $wpdb; |
| 765 | |
| 766 | $not_processed = []; |
| 767 | |
| 768 | foreach ( $grouped_selected as $site_id => $group ) { |
| 769 | |
| 770 | $ids = []; |
| 771 | foreach ( $group as $selected ) |
| 772 | $ids[] = $selected['id']; |
| 773 | |
| 774 | ADBC_Sites::instance()->switch_to_blog_id( $site_id ); |
| 775 | |
| 776 | $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) ); |
| 777 | |
| 778 | // Bulk delete. |
| 779 | $sql = "DELETE FROM {$wpdb->postmeta} WHERE meta_id IN ($placeholders)"; |
| 780 | $sql = $wpdb->prepare( $sql, ...$ids ); |
| 781 | $wpdb->query( $sql ); |
| 782 | |
| 783 | // Identify any remaining rows (not processed) deterministically (get names directly). |
| 784 | $check_sql = "SELECT meta_key FROM {$wpdb->postmeta} WHERE meta_id IN ($placeholders)"; |
| 785 | $check_sql = $wpdb->prepare( $check_sql, ...$ids ); |
| 786 | |
| 787 | $remaining_keys = $wpdb->get_col( $check_sql ); |
| 788 | |
| 789 | if ( ! empty( $remaining_keys ) ) { |
| 790 | foreach ( $remaining_keys as $meta_key ) { |
| 791 | $not_processed[] = $meta_key; |
| 792 | } |
| 793 | } |
| 794 | |
| 795 | ADBC_Sites::instance()->restore_blog(); |
| 796 | |
| 797 | } |
| 798 | |
| 799 | return $not_processed; |
| 800 | |
| 801 | } |
| 802 | |
| 803 | /** |
| 804 | * Get posts meta names that still exist anywhere across the network from a provided list. |
| 805 | * |
| 806 | * @param array $posts_meta_names List of meta_key names to check for existence. |
| 807 | * |
| 808 | * @return array Existing names found across all sites. |
| 809 | */ |
| 810 | public static function get_posts_meta_names_that_exists_from_list( $posts_meta_names ) { |
| 811 | |
| 812 | global $wpdb; |
| 813 | |
| 814 | if ( empty( $posts_meta_names ) || ! is_array( $posts_meta_names ) ) |
| 815 | return []; |
| 816 | |
| 817 | $branches = []; |
| 818 | $sites = ADBC_Sites::instance()->get_sites_list(); |
| 819 | $in_placeholders = implode( ',', array_fill( 0, count( $posts_meta_names ), '%s' ) ); |
| 820 | |
| 821 | foreach ( $sites as $site ) { |
| 822 | $table = $site['prefix'] . 'postmeta'; |
| 823 | $sql = $wpdb->prepare( |
| 824 | "SELECT DISTINCT meta_key AS name |
| 825 | FROM `{$table}` |
| 826 | WHERE meta_key IN ( {$in_placeholders} )", |
| 827 | ...$posts_meta_names |
| 828 | ); |
| 829 | $branches[] = '(' . $sql . ')'; |
| 830 | } |
| 831 | |
| 832 | if ( empty( $branches ) ) |
| 833 | return []; |
| 834 | |
| 835 | $union_sql = implode( "\nUNION ALL\n", $branches ); |
| 836 | $query = "SELECT DISTINCT name FROM ( {$union_sql} ) AS existing_names"; |
| 837 | |
| 838 | $existing_names = $wpdb->get_col( $query ); |
| 839 | |
| 840 | return array_values( array_unique( array_filter( (array) $existing_names ) ) ); |
| 841 | } |
| 842 | |
| 843 | /** |
| 844 | * Get total posts meta count across all sites. |
| 845 | * |
| 846 | * @return int Total posts meta count. |
| 847 | */ |
| 848 | public static function get_total_posts_meta_count() { |
| 849 | |
| 850 | global $wpdb; |
| 851 | $total_posts_meta = 0; |
| 852 | |
| 853 | $sites = ADBC_Sites::instance()->get_sites_list(); |
| 854 | |
| 855 | foreach ( $sites as $site ) { |
| 856 | |
| 857 | $table = $site['prefix'] . "postmeta"; |
| 858 | |
| 859 | $count = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `$table`" ); |
| 860 | |
| 861 | $total_posts_meta += $count; |
| 862 | |
| 863 | } |
| 864 | |
| 865 | return $total_posts_meta; |
| 866 | |
| 867 | } |
| 868 | |
| 869 | } |