S3FixExistingMedia.php

<?php

    ini_set('display_errors', 1);
    error_reporting(E_ALL);

    /* ======================================================
    This script is NOT FULLY TESTED (not tested on Windows Server either)
    USE AT YOUR OWN RISK - development environment Ubuntu Linux 14.04.3 LTS

    The purpose of this script is for small websites and blogs
    to have their existing media to work through Amazon S3
    
    There's a great plugin, WP Offload S3, that we'll be tapping
    into...it works great for new media, but this is a quick
    fix for EXISTING media - media added before you start
    using WP Offload S3.

    This script is not fully tested, but should be useful for
    most situations.  It does create a backup of your database,
    though it's not tested to work 100% in all hosting scenarios.

    Put this file in your WordPress root directory, where wp-config.php is.
    You must also already have these 2 plugins installed and configured:
    - https://wordpress.org/plugins/amazon-web-services/
    - https://wordpress.org/plugins/amazon-s3-and-cloudfront/

    TURN OFF CACHING if you have it on your site.  Re-enable it after
    everything is done.

    ======================================================
    STEPS

    1) Manually upload all of your existing /wp-content/uploads/ files to the
    Amazon S3 bucket (the bucket you configured WP Offload S3 to use for new images)

    2) Add and configure plugins (the 2 listed above)

    3) Run this script from the root directory of your WordPress site

    ======================================================
    Developer: TJ Nevis
    Website: http://blog.TJNevis.com, http://NevisTechnology.com
    More Info: http://blog.tjnevis.com/wordpress-and-amazon-s3-quick-fix-for-existing-media-to-be-served-on-amazon-s3/
    Last Updated: 04/19/2016
    ====================================================== */

    require_once 'wp-config.php';
    require_once 'wp-content/plugins/amazon-s3-and-cloudfront/wordpress-s3.php';
    global $table_prefix;



    echo '<h1>Move Existing Media to Amazon S3</h1>';
    echo '<h3>By TJ Nevis [<a href="http://NevisTechnology.com?ref=S3Script" target="_blank">NevisTechnology.com</a>] Detailed blog post <a href="http://blog.tjnevis.com/wp-offload-s3-quick-fix-for-existing-media/" target="_blank">here</a></h3>';



    /* ======================================================
    First step, we're going to back up the database and
    save it in the the root directory of your site (where this file is)    
    ====================================================== */
    $dbHost = $wpdb->dbhost;
    $dbName = $wpdb->dbname;
    $dbPassword = $wpdb->dbpassword;
    $dbSaveFileLocation = dirname(__FILE__) . '/backup-' . date('Y-m-d-h:i:s', time()) . '.sql';
    $dbUser = $wpdb->dbuser;

    exec("mysqldump --user=$dbUser --password=$dbPassword --host=$dbHost $dbName > $dbSaveFileLocation");

    /* ======================================================
    Let's get set up with by grabbing WP Offload S3 values
    ====================================================== */
    amazon_web_services_require_files(); // Thanks k0nG
    $aws = new Amazon_Web_Services( __FILE__ ); // Thanks k0nG
    $awsS3 = new Amazon_S3_And_CloudFront(__FILE__, $aws); // Thanks k0nG
    $bucket = ( !empty( $awsS3->get_setting('cloudfront') ) ) ? $awsS3->get_setting('cloudfront') : $awsS3->get_setting('bucket'); // If you're using a CDN and the setting is saved, use that for CloudFront URL replcaement, otherwise use the S3 bucket name
    $region = $awsS3->get_setting('region'); // Thanks k0nG
    $folderPrefix = $awsS3->get_setting('object-prefix');

    /* ======================================================
    Check if the user is requesting to remove the existing media updates
    ====================================================== */
    if( isset($_GET['remove']) && $_GET['remove'] ) { // ?removeS3Update=true
        if( $dbConnection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName) ) {

            $removeAmazonS3Info = "DELETE FROM " . $table_prefix . "postmeta WHERE meta_key = 'amazonS3_info';";
            $reversePostContentHref = updatePostContent(
                                            'href',
                                            $table_prefix . 'posts',
                                            get_site_url( $wpdb->blogid ) . '/' . $folderPrefix,
                                            "https://$bucket.s3.amazonaws.com/$folderPrefix",
                                            true
                                        );
            $reversePostContentSrc = updatePostContent(
                                            'src',
                                            $table_prefix . 'posts',
                                            get_site_url( $wpdb->blogid ) . '/' . $folderPrefix,
                                            "https://$bucket.s3.amazonaws.com/$folderPrefix",
                                            true
                                        );

            echo 'RUNNING COMMAND: ' . $removeAmazonS3Info . ' - '; 
            if( $dbConnection->query($removeAmazonS3Info) ) {
                echo ' <strong>TRUE, ' . $dbConnection->affected_rows . ' rows affected</strong><br />';
            }

            echo 'RUNNING COMMAND: ' . $reversePostContentHref . ' - ';
            if( $dbConnection->query($reversePostContentHref) ) {
                echo ' <strong>TRUE, ' . $dbConnection->affected_rows . ' rows affected</strong><br />';
            }

            echo 'RUNNING COMMAND: ' . $reversePostContentSrc . ' - '; 
            if( $dbConnection->query($reversePostContentSrc) ) {
                echo ' <strong>TRUE, ' . $dbConnection->affected_rows . ' rows affected</strong><br />';
            }   

        }

        echo '<h3>DONE with removing records for WP Offload S3 - reverted back to serving from the local server</h3>';

        exit(); // Don't do the rest of the script
    }

    /* ======================================================
    Start fresh, delete data thatis from WP Offload S3    
    ====================================================== */
    $wpdb->delete($table_prefix . 'postmeta',
        array(
            'meta_key'  => 'amazonS3_info'
        )
    );

    /* ======================================================
    Grab the attachments from the database, we'll need
    the meta_value (image name), and the post ID it's related to
    ====================================================== */
    $picturesToUpdate = $wpdb->get_results("SELECT * FROM " . $table_prefix . "postmeta WHERE meta_key = '_wp_attached_file'");

    foreach($picturesToUpdate as $picture) {
        $pictureMetaData = serialize(array(
            'bucket'    => $bucket,
            'key'       => $folderPrefix . $picture->meta_value,
            'region'    => $region // Thanks k0nG
        ));

        /* ======================================================
        Now let's insert the record that WP Offload S3 looks for
        to change the image URL to your S3 URL
        ====================================================== */
        $wpdb->insert($table_prefix . 'postmeta', 
            array(
                'post_id'   => $picture->post_id,
                'meta_key'  => 'amazonS3_info',
                'meta_value'  => $pictureMetaData
            )
        );

    }

    if( $dbConnection = mysqli_connect($dbHost, $dbUser, $dbPassword, $dbName) ) {

        $hrefMySQLUpdate = updatePostContent(
                                'href',
                                $table_prefix . 'posts',
                                get_site_url( $wpdb->blogid ) . '/' . $folderPrefix,
                                "https://$bucket.s3.amazonaws.com/$folderPrefix"
                            );
        $srcMySQLUpdate = updatePostContent(
                                'src',
                                $table_prefix . 'posts',
                                get_site_url( $wpdb->blogid ) . '/' . $folderPrefix,
                                "https://$bucket.s3.amazonaws.com/$folderPrefix"
                            );

        echo 'RUNNING COMMAND: ' . $hrefMySQLUpdate . ' - '; 
        if( $dbConnection->query($hrefMySQLUpdate) ) {
            echo ' <strong>TRUE, ' . $dbConnection->affected_rows . ' rows affected</strong><br />';
        }

        echo 'RUNNING COMMAND: ' . $srcMySQLUpdate . ' - ';
        if( $dbConnection->query($srcMySQLUpdate) ) {
            echo ' <strong>TRUE, ' . $dbConnection->affected_rows . ' rows affected</strong><br />';
        }

    }

    echo '<h3>DONE with adding records for WP Offload S3 to recognize the image on S3</h3>';



    function updatePostContent($type, $table, $blog, $s3bucket, $reverse = FALSE) {
        // $reverse is to remove the post_content updates and put them back to serving locally
        $from   = ( !$reverse ) ? $blog : $s3bucket;
        $to     = ( !$reverse ) ? $s3bucket : $blog;

        return "UPDATE $table SET post_content = replace(post_content, '$type=\"$from', '$type=\"$to');";
    }

?>