WP Offload Media Lite upload existing files

Question

Can someone help me on how to upload and link existing files to s3. WP Offload Media Lite provide this feature but it is paid. Any alternate way to do this?

Answer 1

Solution: I am able to do this with just a simple SQL Query and move wp-content/uploads folder directly to s3. No replace is required for updating link. Table of WP Offload Media Lite plugin used for linking 'wp_as3cf_items'

Query for linking Existing files to s3:

INSERT IGNORE INTO wp_as3cf_items (provider,region,bucket,path,original_path,is_private,source_type,source_id,source_path,original_source_path,extra_info,originator,is_verified) SELECT  'aws', 'us-_REGION_HERE', 'BUCKET_NAME_HERE', concat('wp-content/uploads/',SUBSTRING_INDEX(guid, 'wp-content/uploads/', -1) ) AS path, concat('wp-content/uploads/',SUBSTRING_INDEX(guid, 'wp-content/uploads/', -1)) AS original_path, 0, 'media-library', id as source_id, SUBSTRING_INDEX(guid, 'wp-content/uploads/', -1) AS source_path, SUBSTRING_INDEX(guid, 'wp-content/uploads/', -1) AS original_source_path, 'a:2:{s:13:"private_sizes";a:0:{}s:14:"private_prefix";s:0:"";}', 0, 1  FROM `wp_posts` WHERE `post_type` = 'attachment';

For Multisite, do this for each site with table prefix.

Answer 2

As you are using the free version so you have to manually upload all the existing files into AWS S3 then you need to run the following function to update the wp_as3cf_items table

function wh_syncWPMediaLiteTable() {
    global $wpdb;
    $wpUploadDir = rtrim(wp_get_upload_dir()[‘basedir’], ‘/’);
    $itemsTable = ‘as3cf_items’;
    $offloadSettings = get_option(‘tantan_wordpress_s3’);
    $lastAttachmentID = get_option(‘_site_transient_wh_last_sync_attachment’, 0);
    //print_r($offloadSettings);
    $objectPrefix = rtrim($offloadSettings[‘object-prefix’], ‘/’);
    $format = [
        ‘provider’ => ‘%s’,
        ‘region’ => ‘%s’,
        ‘bucket’ => ‘%s’,
        ‘path’ => ‘%s’,
        ‘original_path’ => ‘%s’,
        ‘is_private’ => ‘%d’,
        ‘source_type’ => ‘%s’,
        ‘source_id’ => ‘%d’,
        ‘source_path’ => ‘%s’,
        ‘original_source_path’ => ‘%s’,
        ‘extra_info’ => ‘%s’,
        ‘originator’ => ‘%d’,
        ‘is_verified’ => ‘%d’
    ];
    ksort($format);
    $format = array_values($format);
    #Getting the list of attachments
    $sql = $wpdb->prepare("SELECT p.`ID`,
                            MAX(CASE WHEN pm.meta_key = ‘_wp_attached_file’ THEN pm.meta_value END ) as ‘attached_file’,
                            MAX(CASE WHEN pm.meta_key = ‘_wp_attachment_metadata’ THEN pm.meta_value END ) as ‘attachment_metadata’,
                            ai.`source_id`
                            FROM `{$wpdb->prefix}posts` AS p
                            LEFT JOIN `{$wpdb->prefix}postmeta` AS pm ON p.`ID` = pm.`post_id`
                            LEFT JOIN `{$wpdb->prefix}{$itemsTable}` AS ai ON p.`ID` = ai.`source_id`
                            AND ai.`source_id` IS NULL
                            WHERE p.`post_type` = %s
                            AND p.`ID` > %d
                            GROUP BY p.`ID`
                            LIMIT %d", ‘attachment’, $lastAttachmentID, 10);
    $attachments = $wpdb->get_results($sql);
    if (empty($attachments)) {
        #trigger a mail to site admin that no attachments are left
        die(‘All the images are synced into WP Offload Media Lite table.’);
    }
    foreach ($attachments as $key => $attachment) {
        $lastAttachmentID = $attachment->ID;
        $attachmentMetadata = maybe_unserialize($attachment->attachment_metadata);
        $fullFilePath = $wpUploadDir . ‘/’ . $attachmentMetadata[‘file’];
        #Checking image exists or not if not then no point of adding it into WP Offload Media’s table
        if (!file_exists($fullFilePath)) {
            continue;
        }
        print_r($attachmentMetadata);
        #preparing data
        $data = [
            ‘provider’ => ‘aws’,
            ‘region’ => $offloadSettings[‘region’],
            ‘bucket’ => $offloadSettings[‘bucket’],
            ‘path’ => $objectPrefix . ‘/’ . $attachment->attached_file,
            ‘original_path’ => $objectPrefix . ‘/’ . $attachment->attached_file,
            ‘is_private’ => 0,
            ‘source_type’ => ‘media-library’,
            ‘source_id’ => $attachment->ID,
            ‘source_path’ => $attachment->attached_file,
            ‘original_source_path’ => $attachment->attached_file,
            ‘extra_info’ => ‘a:2:{s:13:"private_sizes";a:0:{}s:14:"private_prefix";s:0:"";}’,
            ‘originator’ => 0,
            ‘is_verified’ => 1
        ];
        ksort($data);
        #Adding record into WP Offload Media’s table
        $wpdb->insert("{$wpdb->prefix}{$itemsTable}", $data, $format);
    }
    update_option(‘_site_transient_wh_last_sync_attachment’, $lastAttachmentID, false);
}

The above function is fully tested and work perfectly

Reference: How to setup free CDN for WordPress using AWS S3

Hope this helps!