Pangea Data Mapping and Integrity

pangea-data-mapping-and-integrity.php

#!/usr/bin/php
<?php
$time_start = microtime( true ); 

//mysql setup
$host = 'localhost';
$user = 'root';
$pass = 'root';
$db   = 'wordpress';

$mysqli = new mysqli( $host, $user, $pass, $db );

$warnings = 0;
$successes = 0;

/*****************************************/
/* MAP EACH POST                         */
/*****************************************/

/* loop through all the blogs */
$blogs_array = array('about', 'autopia', 'beyond_the_beyond', 'business', 'dangerroom', 'design', 'gadgetlab', 'gamelife', 'magazine', 'opinion', 'playbook', 'rawfile', 'reviews', 'threatlevel', 'underwire', 'wiredenterprise', 'wiredscience');
//$blogs_array = array('autopia');

foreach ($blogs_array as $blog) {
    /* left outer join to check both old and new wp_posts to make sure all old published posts are in new table */
    $sql = "SELECT old.ID AS old_id, old.post_date AS old_post_date, old.post_name AS old_post_name, old.post_title AS old_post_title, 
    old.post_status AS old_post_status, old.post_type AS old_post_type, new.ID AS new_id, new.post_name AS new_post_name, new.post_title AS new_post_title
    FROM wp_" . $blog . "_posts old
    LEFT OUTER JOIN wp_posts new
    ON old.post_date = new.post_date
    AND old.post_title = new.post_title
    AND old.post_content = new.post_content
    AND old.post_status = new.post_status
    AND old.post_type = new.post_type
    WHERE old.post_status = 'publish'
    AND old.post_type = 'post' ";
    $res = $mysqli->query( $sql );
    while( $row = $res->fetch_assoc( ) ) {
        $old_id = $row['old_id'];
        $old_post_date = $row['old_post_date'];
        $old_post_name = $row['old_post_name'];
        $old_post_title = $row['old_post_title'];
        $new_id = $row['new_id'];
        $new_post_name = $row['new_post_name'];
        $new_post_title = $row['new_post_title'];

        if ( $new_id ) {

            /* get attachment children - find matching _wp_attached_file */
            $sql2 = "SELECT old_posts.ID AS old_attachment_id, old_postmeta.meta_value AS old_attached_file FROM wp_" . $blog . "_posts old_posts, wp_" . $blog . "_postmeta old_postmeta WHERE old_posts.post_parent = " . $old_id . " AND old_posts.post_type = 'attachment' AND old_posts.ID = old_postmeta.post_id AND old_postmeta.meta_key = '_wp_attached_file'"; 
            $res2 = $mysqli->query( $sql2 );
            $count2 = 0;
            while( $row2 = $res2->fetch_assoc( ) ) {
                // check to make sure the attachment maps
                $old_attachment_id = $row2['old_attachment_id'];
                $old_attached_file = $row2['old_attached_file'];            
                $sql3 = "SELECT new_posts.ID AS new_attachment_id, new_postmeta.meta_value AS new_attached_file 
                FROM wp_posts new_posts, wp_postmeta new_postmeta 
                WHERE new_postmeta.meta_value LIKE '%" . $old_attached_file . 
                "' AND new_posts.post_parent = " . $new_id . 
                " AND new_posts.post_type = 'attachment' 
                AND new_posts.ID = new_postmeta.post_id
                AND new_postmeta.meta_key = '_wp_attached_file'"; 
                $res3 = $mysqli->query( $sql3 );
                if ( $res->num_rows > 0 ) {
                    while( $row3 = $res3->fetch_assoc( ) ) {
                        $new_attachment_id = $row3['new_attachment_id'];
                        $new_attached_file = $row3['new_attached_file'];
                        printf("SUCCESS:  old attachment : " . $old_attachment_id . "|" . $old_attached_file . "| new: " . $new_attachment_id . "|" . $new_attached_file . "\n");
                        $successes++;
                    }
                } else {
                   printf( "WARNING: " . $blog . " attachment post " . $old_attachment_id . " with parent post " . $old_id . "is missing in the new DB for parent post " . $new_id . ".\n" );
                   $warnings++;
                }
            }    

            /* check for term mapping */
            $sql2 = "SELECT old_terms.slug AS old_slug FROM wp_" . $blog . "_terms old_terms, wp_" . $blog . "_term_taxonomy old_term_taxonomy, wp_" . $blog . "_term_relationships old_term_relationships WHERE old_term_relationships.object_id = " . $old_id . " AND old_term_relationships.term_taxonomy_id = old_term_taxonomy.term_taxonomy_id AND old_term_taxonomy.term_id = old_terms.term_id";
            $res2 = $mysqli->query( $sql2 );
            while( $row2 = $res2->fetch_assoc( ) ) {
                $old_slug = $row2['old_slug'];

                $sql3 = "SELECT new_terms.slug AS new_slug FROM wp_terms new_terms, wp_term_taxonomy new_term_taxonomy, wp_term_relationships new_term_relationships WHERE new_term_relationships.object_id = " . $new_id . " AND new_term_relationships.term_taxonomy_id = new_term_taxonomy.term_taxonomy_id AND new_term_taxonomy.term_id = new_terms.term_id AND new_terms.slug = '" . $old_slug . "'";
                $res3 = $mysqli->query( $sql3 );
                while( $row3 = $res3->fetch_assoc( ) ) {
                    if ( $res3->num_rows > 0 ) {
                        $new_slug = $row3['new_slug'];
                        printf("SUCCESS:  post " . $old_id . " old term : " . $old_slug . "| new term: " . $new_slug . "\n");
                        $successes++;
                    } else {
                        printf( "WARNING: term " . $old_slug . " not mapped for post " . $old_id . ".\n" );
                        $warnings++;
                    }
                }
            }

            /* check for valid post author */
            $sql2 = "SELECT new_posts.ID, new_posts.post_author AS new_post_author FROM wp_users new_users, wp_posts new_posts WHERE new_posts.ID = " . $new_id . " AND new_posts.post_author = new_users.ID ";
            $res2 = $mysqli->query( $sql2 );
            if ( $res2->num_rows > 0 ) {
                while( $row2 = $res2->fetch_assoc( ) ) {
                    printf("SUCCESS:  valid post author on post " . $new_id . ".\n");
                    $successes++;
                }
            } else {
                    printf( "WARNING: invalid post author on post " . $new_id . ".\n" );
                    $warnings++;
            }

        } else {
            printf( "WARNING: " . $blog . " post " . $old_id . "|" . $old_post_date . "| is missing or mismatched in the new DB.\n" );
            $warnings++;
        }
    }

    printf( strtoupper( $blog ) . " Data Mapping and Integrity Checks Complete. | " . $warnings . " warnings | " . $successes . " successes \n" );
    $warnings = 0;
    $successes = 0;
}

/* close connection */
$mysqli->close();

$time_end = microtime( true );

//dividing with 60 will give the execution time in minutes otherwise seconds
$execution_time = ( $time_end - $time_start )/60;

//execution time of the script
printf("Total Execution Time: " . $execution_time . " mins \n");

 ?>