doHooks(); } return self::$instance; } public function doHooks(){ add_action('wp_ajax_get_orphan_tables_options', array($this,'get_orphan_count')); add_action('wp_ajax_get_orphan_view_list', array($this,'get_orphan_view_list')); add_action('wp_ajax_delete_orphan_list', array($this,'delete_orphan_list')); add_action('wp_ajax_get_modified_tables', array($this,'get_modified_tables')); add_action('wp_ajax_get_orphant_selected_tab', array($this,'get_orphant_selected_tab')); } public function get_orphant_selected_tab(){ if (!is_user_logged_in() || !current_user_can('manage_options')) { wp_send_json_error(['message' => 'Unauthorized access.'], 403); return; } $tab = sanitize_text_field($_POST['tab']); if($tab === 'undefined'){ $tab_value = get_option('smack_orphant_tab'); if(empty($tab_value)){ $tab_name = 'post'; update_option('smack_orphant_tab',$tab_name); }else{ update_option('smack_orphant_tab',$tab_value); } }else{ update_option('smack_orphant_tab',$tab); } $tab_address = get_option('smack_orphant_tab'); $result['tab'] = $tab_address; $result['success'] = true; echo wp_json_encode($result); wp_die(); } public function get_orphan_count(){ if (!is_user_logged_in() || !current_user_can('manage_options')) { wp_send_json_error(['message' => 'Unauthorized access.'], 403); return; } global $wpdb; $result['orphan_postmeta'] = $wpdb->get_var( "SELECT COUNT(meta_id) FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts)" ); $result['orphan_commentmeta']=$wpdb->get_var( "SELECT COUNT(meta_id) FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments)" ); $result['orphan_usermeta']=$wpdb->get_var( "SELECT COUNT(umeta_id) FROM $wpdb->usermeta WHERE user_id NOT IN (SELECT ID FROM $wpdb->users)" ); $result['orphan_termmeta']=$wpdb->get_var( "SELECT COUNT(meta_id) FROM $wpdb->termmeta WHERE term_id NOT IN (SELECT term_id FROM $wpdb->terms)" ); $orphan_term_relationships_sql = implode( "','", array_map( 'esc_sql', $this->get_excluded_taxonomies() ) ); $result['orphan_term_relationships'] = $wpdb->get_var( "SELECT COUNT(object_id) FROM $wpdb->term_relationships AS tr INNER JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy NOT IN ('$orphan_term_relationships_sql') AND tr.object_id NOT IN (SELECT ID FROM $wpdb->posts)" ); // phpcs:ignore $result['unused_terms']=$wpdb->get_var( $wpdb->prepare( "SELECT COUNT(t.term_id) FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.count = %d AND t.term_id NOT IN (" . implode( ',', $this->get_excluded_termids() ) . ')', 0 ) ); // phpcs:ignore $postmeta_query = $wpdb->get_col( $wpdb->prepare( "SELECT COUNT(meta_id) AS count FROM $wpdb->postmeta GROUP BY post_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( is_array( $postmeta_query ) ) { $result['duplicated_postmeta']=array_sum( array_map( 'intval', $postmeta_query ) ); } $commentsmeta_query = $wpdb->get_col( $wpdb->prepare( "SELECT COUNT(meta_id) AS count FROM $wpdb->commentmeta GROUP BY comment_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( is_array( $commentsmeta_query ) ) { $result['duplicated_commentmeta']=array_sum( array_map( 'intval', $commentsmeta_query ) ); } $usermeta_query = $wpdb->get_col( $wpdb->prepare( "SELECT COUNT(umeta_id) AS count FROM $wpdb->usermeta GROUP BY user_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( is_array( $usermeta_query ) ) { $result['duplicated_usermeta'] = array_sum( array_map( 'intval', $usermeta_query ) ); } $termmeta_query = $wpdb->get_col( $wpdb->prepare( "SELECT COUNT(meta_id) AS count FROM $wpdb->termmeta GROUP BY term_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( is_array( $termmeta_query ) ) { $result['duplicated_termmeta'] = array_sum( array_map( 'intval', $termmeta_query ) ); } $result['oembed_postmeta']=$wpdb->get_var( $wpdb->prepare( "SELECT COUNT(meta_id) FROM $wpdb->postmeta WHERE meta_key LIKE(%s)", '%_oembed_%' ) ); $result['sucess']='true'; echo wp_json_encode($result); wp_die(); } public function get_orphan_view_list(){ if (!is_user_logged_in() || !current_user_can('manage_options')) { wp_send_json_error(['message' => 'Unauthorized access.'], 403); return; } global $wpdb; $details = array(); $list=$_POST['list']; switch ( $list ) { case 'orphan_postmeta': $orphan_post_details = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts) LIMIT %d", $this->limit_details ) ); $result['orphan_postmeta']=$orphan_post_details; break; case 'orphan_commentmeta': $orphan_comments_details = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments) LIMIT %d", $this->limit_details ) ); $result['orphan_commentmeta']=$orphan_comments_details; break; case 'orphan_usermeta': $orphan_user_details = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM $wpdb->usermeta WHERE user_id NOT IN (SELECT ID FROM $wpdb->users) LIMIT %d", $this->limit_details ) ); $result['orphan_usermeta']=$orphan_user_details; break; case 'orphan_termmeta': $orphan_term_details = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM $wpdb->termmeta WHERE term_id NOT IN (SELECT term_id FROM $wpdb->terms) LIMIT %d", $this->limit_details ) ); $result['orphan_termmeta']=$orphan_term_details; break; case 'orphan_term_relationships': $orphan_term_relationships_sql = implode( "','", array_map( 'esc_sql', $this->get_excluded_taxonomies() ) ); $orphan_relationship_details = $wpdb->get_col( $wpdb->prepare( "SELECT tt.taxonomy FROM $wpdb->term_relationships AS tr INNER JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy NOT IN ('$orphan_term_relationships_sql') AND tr.object_id NOT IN (SELECT ID FROM $wpdb->posts) LIMIT %d", $this->limit_details ) ); // phpcs:ignore $result['orphan_term_relationships']=$orphan_relationship_details; break; case 'unused_terms': $unused_details = $wpdb->get_col( $wpdb->prepare( "SELECT t.name FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.count = %d AND t.term_id NOT IN (" . implode( ',', $this->get_excluded_termids() ) . ') LIMIT %d', 0, $this->limit_details ) ); // phpcs:ignore $result['unused_terms']=$unused_details; break; case 'duplicated_postmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT COUNT(meta_id) AS count, meta_key FROM $wpdb->postmeta GROUP BY post_id, meta_key, meta_value HAVING count > %d LIMIT %d", 1, $this->limit_details ) ); $details = array(); if ( $query ) { foreach ( $query as $meta ) { $duplicate_post_details[] = $meta->meta_key; } } $result['duplicated_postmeta']=$duplicate_post_details; break; case 'duplicated_commentmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT COUNT(meta_id) AS count, meta_key FROM $wpdb->commentmeta GROUP BY comment_id, meta_key, meta_value HAVING count > %d LIMIT %d", 1, $this->limit_details ) ); $details = array(); if ( $query ) { foreach ( $query as $meta ) { $duplicate_comment_details[] = $meta->meta_key; } } $result['duplicated_commentmeta']=$duplicate_comment_details; break; case 'duplicated_usermeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT COUNT(umeta_id) AS count, meta_key FROM $wpdb->usermeta GROUP BY user_id, meta_key, meta_value HAVING count > %d LIMIT %d", 1, $this->limit_details ) ); $details = array(); if ( $query ) { foreach ( $query as $meta ) { $duplicate_user_details[] = $meta->meta_key; } } $result['duplicated_usermeta']=$duplicate_user_details; break; case 'duplicated_termmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT COUNT(meta_id) AS count, meta_key FROM $wpdb->termmeta GROUP BY term_id, meta_key, meta_value HAVING count > %d LIMIT %d", 1, $this->limit_details ) ); $details = array(); if ( $query ) { foreach ( $query as $meta ) { $duplicate_term_details[] = $meta->meta_key; } } $result['duplicated_termmeta']=$duplicate_term_details; break; case 'oembed_postmeta': $oembed_post_details = $wpdb->get_col( $wpdb->prepare( "SELECT meta_key FROM $wpdb->postmeta WHERE meta_key LIKE(%s) LIMIT %d", '%_oembed_%', $this->limit_details ) ); $result['oembed_postmeta']=$oembed_post_details; break; } $result['sucess']='true'; echo wp_json_encode($result); wp_die(); } public function delete_orphan_list(){ if (!is_user_logged_in() || !current_user_can('manage_options')) { wp_send_json_error(['message' => 'Unauthorized access.'], 403); return; } global $wpdb; $list=$_POST['delete_list']; switch ( $list ) { case 'orphan_postmeta': $query = $wpdb->get_results( "SELECT post_id, meta_key FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts)" ); if ( $query ) { foreach ( $query as $meta ) { $post_id = (int) $meta->post_id; if ( 0 === $post_id ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s", $post_id, $meta->meta_key ) ); } else { delete_post_meta( $post_id, $meta->meta_key ); } } } break; case 'orphan_commentmeta': $query = $wpdb->get_results( "SELECT comment_id, meta_key FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments)" ); if ( $query ) { foreach ( $query as $meta ) { $comment_id = (int) $meta->comment_id; if ( 0 === $comment_id ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->commentmeta WHERE comment_id = %d AND meta_key = %s", $comment_id, $meta->meta_key ) ); } else { delete_comment_meta( $comment_id, $meta->meta_key ); } } } break; case 'orphan_usermeta': $query = $wpdb->get_results( "SELECT user_id, meta_key FROM $wpdb->usermeta WHERE user_id NOT IN (SELECT ID FROM $wpdb->users)" ); if ( $query ) { foreach ( $query as $meta ) { $user_id = (int) $meta->user_id; if ( 0 === $user_id ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->usermeta WHERE user_id = %d AND meta_key = %s", $user_id, $meta->meta_key ) ); } else { delete_user_meta( $user_id, $meta->meta_key ); } } } break; case 'orphan_termmeta': $query = $wpdb->get_results( "SELECT term_id, meta_key FROM $wpdb->termmeta WHERE term_id NOT IN (SELECT term_id FROM $wpdb->terms)" ); if ( $query ) { foreach ( $query as $meta ) { $term_id = (int) $meta->term_id; if ( 0 === $term_id ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->termmeta WHERE term_id = %d AND meta_key = %s", $term_id, $meta->meta_key ) ); } else { delete_term_meta( $term_id, $meta->meta_key ); } } } break; case 'orphan_term_relationships': $query = $wpdb->get_results( "SELECT tr.object_id, tr.term_taxonomy_id, tt.term_id, tt.taxonomy FROM $wpdb->term_relationships AS tr INNER JOIN $wpdb->term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy NOT IN ('" . implode( '\',\'', $this->get_excluded_taxonomies() ) . "') AND tr.object_id NOT IN (SELECT ID FROM $wpdb->posts)" ); // phpcs:ignore if ( $query ) { foreach ( $query as $tax ) { $wp_remove_object_terms = wp_remove_object_terms( (int) $tax->object_id, (int) $tax->term_id, $tax->taxonomy ); if ( true !== $wp_remove_object_terms ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->term_relationships WHERE object_id = %d AND term_taxonomy_id = %d", $tax->object_id, $tax->term_taxonomy_id ) ); } } } break; case 'unused_terms': $query = $wpdb->get_results( $wpdb->prepare( "SELECT tt.term_taxonomy_id, t.term_id, tt.taxonomy FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.count = %d AND t.term_id NOT IN (" . implode( ',', $this->get_excluded_termids() ) . ')', 0 ) ); // phpcs:ignore if ( $query ) { $check_wp_terms = false; foreach ( $query as $tax ) { if ( taxonomy_exists( $tax->taxonomy ) ) { wp_delete_term( (int) $tax->term_id, $tax->taxonomy ); } else { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->term_taxonomy WHERE term_taxonomy_id = %d", (int) $tax->term_taxonomy_id ) ); $check_wp_terms = true; } } // We need this for invalid taxonomies. if ( $check_wp_terms ) { $wpdb->get_results( "DELETE FROM $wpdb->terms WHERE term_id NOT IN (SELECT term_id FROM $wpdb->term_taxonomy)" ); } } break; case 'duplicated_postmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT GROUP_CONCAT(meta_id ORDER BY meta_id DESC) AS ids, post_id, COUNT(*) AS count FROM $wpdb->postmeta GROUP BY post_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( $query ) { foreach ( $query as $meta ) { $ids = array_map( 'intval', explode( ',', $meta->ids ) ); array_pop( $ids ); $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->postmeta WHERE meta_id IN (" . implode( ',', $ids ) . ') AND post_id = %d', (int) $meta->post_id ) ); // phpcs:ignore } } break; case 'duplicated_commentmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT GROUP_CONCAT(meta_id ORDER BY meta_id DESC) AS ids, comment_id, COUNT(*) AS count FROM $wpdb->commentmeta GROUP BY comment_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( $query ) { foreach ( $query as $meta ) { $ids = array_map( 'intval', explode( ',', $meta->ids ) ); array_pop( $ids ); $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->commentmeta WHERE meta_id IN (" . implode( ',', $ids ) . ') AND comment_id = %d', (int) $meta->comment_id ) ); // phpcs:ignore } } break; case 'duplicated_usermeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT GROUP_CONCAT(umeta_id ORDER BY umeta_id DESC) AS ids, user_id, COUNT(*) AS count FROM $wpdb->usermeta GROUP BY user_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( $query ) { foreach ( $query as $meta ) { $ids = array_map( 'intval', explode( ',', $meta->ids ) ); array_pop( $ids ); $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->usermeta WHERE umeta_id IN (" . implode( ',', $ids ) . ') AND user_id = %d', (int) $meta->user_id ) ); // phpcs:ignore } } break; case 'duplicated_termmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT GROUP_CONCAT(meta_id ORDER BY meta_id DESC) AS ids, term_id, COUNT(*) AS count FROM $wpdb->termmeta GROUP BY term_id, meta_key, meta_value HAVING count > %d", 1 ) ); if ( $query ) { foreach ( $query as $meta ) { $ids = array_map( 'intval', explode( ',', $meta->ids ) ); array_pop( $ids ); $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->termmeta WHERE meta_id IN (" . implode( ',', $ids ) . ') AND term_id = %d', (int) $meta->term_id ) ); // phpcs:ignore } } break; case 'oembed_postmeta': $query = $wpdb->get_results( $wpdb->prepare( "SELECT post_id, meta_key FROM $wpdb->postmeta WHERE meta_key LIKE(%s)", '%_oembed_%' ) ); if ( $query ) { foreach ( $query as $meta ) { $post_id = (int) $meta->post_id; if ( 0 === $post_id ) { $wpdb->query( $wpdb->prepare( "DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s", $post_id, $meta->meta_key ) ); } else { delete_post_meta( $post_id, $meta->meta_key ); } } } break; } $result['sucess']='true'; echo wp_json_encode($result); wp_die(); } private function get_excluded_taxonomies() { $excluded_taxonomies = array(); $excluded_taxonomies[] = 'link_category'; return apply_filters( 'wp_sweep_excluded_taxonomies', $excluded_taxonomies ); } private function get_excluded_termids() { $default_term_ids = $this->get_default_taxonomy_termids(); if ( ! is_array( $default_term_ids ) ) { $default_term_ids = array(); } $parent_term_ids = $this->get_parent_termids(); if ( ! is_array( $parent_term_ids ) ) { $parent_term_ids = array(); } $excluded_termids = array_merge( $default_term_ids, $parent_term_ids ); return apply_filters( 'wp_sweep_excluded_termids', $excluded_termids ); } private function get_default_taxonomy_termids() { $taxonomies = get_taxonomies(); $default_term_ids = array(); if ( $taxonomies ) { $tax = array_keys( $taxonomies ); if ( $tax ) { foreach ( $tax as $t ) { $term_id = (int) get_option( 'default_' . $t ); if ( $term_id > 0 ) { $default_term_ids[] = $term_id; } } } } return $default_term_ids; } private function get_parent_termids() { global $wpdb; return $wpdb->get_col( $wpdb->prepare( "SELECT tt.parent FROM $wpdb->terms AS t INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.parent > %d", 0 ) ); } public function get_modified_tables(){ if (!is_user_logged_in() || !current_user_can('manage_options')) { wp_send_json_error(['message' => 'Unauthorized access.'], 403); return; } global $wpdb; require_once(ABSPATH.'/wp-admin/includes/schema.php'); $wp_db_schema = wp_get_db_schema('all'); // Separate individual queries into an array $queries = explode(';', $wp_db_schema); if (''==$queries[count($queries)-1]) { array_pop($queries); } $wpTablesList = array(); foreach ($queries as $query) { if (preg_match("|CREATE TABLE ([^ ]*)|", $query, $matches)) { $wpTablesList[trim( strtolower($matches[1]), '`' )] = $query; } } $changedTables = array(); $i = 1; foreach ($wpTablesList as $table=>$createQuery) { update_option('pgc_scanprogress_current', $i); update_option('pgc_scanprogress_status', $table); // orginal structure columns list $origColumns = pgc_extract_field_names($createQuery); // fact structrue columns list $query = "describe $table"; $factColumns = $wpdb->get_results($query); foreach ($factColumns as $factColumn) { if (!isset($origColumns[strtolower($factColumn->Field)])) { if (!isset($changedTables[$table])) { $changedTables[$table] = array(); } $changedTables[$table][$factColumn->Field] = new \stdClass(); $changedTables[$table][$factColumn->Field]->plugin_name = ''; $changedTables[$table][$factColumn->Field]->plugin_state = ''; } } } if (count($changedTables)>0) { $modified_tables = []; $temp = 0; foreach ($changedTables as $tableName=>$columnData) { foreach ($columnData as $column=>$plugin) { $modified_tables[$temp]['table_name']=$tableName; $modified_tables[$temp]['Extra_table']=$column; $temp++; } } } echo wp_json_encode([ 'response' => [ 'modified_tables'=>$modified_tables, ], 'status' => 200, 'success' => true, ]); wp_die(); } }