WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Generator functions

Database Schema for Binary Data Storage

When dealing with binary data within WordPress, particularly for custom e-commerce features or complex product attributes, storing it directly in the `wp_posts` or `wp_postmeta` tables can lead to performance degradation and bloat. A more robust approach involves creating dedicated custom database tables. For binary data, the `LONGBLOB` or `MEDIUMBLOB` data types are suitable, depending on the expected size of the files. We’ll define a simple table structure for this recipe.

Consider a table named `wp_custom_binary_data` with the following schema:

CREATE TABLE wp_custom_binary_data (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    file_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size BIGINT(20) UNSIGNED NOT NULL,
    storage_path VARCHAR(512) NULL DEFAULT NULL,
    blob_data LONGBLOB NULL DEFAULT NULL,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_post_id (post_id),
    KEY idx_file_name (file_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Here, `post_id` allows associating binary data with a specific WordPress post (e.g., a product). `file_name`, `mime_type`, and `file_size` are metadata for easier identification and management. `storage_path` is an optional field for file system storage, while `blob_data` is where the actual binary content will reside if stored directly in the database. For this recipe, we’ll focus on `blob_data` storage.

PHP Generator Functions for Efficient Retrieval

Retrieving large binary blobs can consume significant memory. PHP generator functions (using `yield`) are ideal for streaming data without loading the entire blob into memory at once. This is crucial for large files or when processing multiple binary assets.

Let’s create a function to fetch binary data for a given `post_id` using a generator.

/**
 * Retrieves binary data chunks for a given post ID using a generator.
 *
 * @param int $post_id The ID of the WordPress post.
 * @return Generator|false A generator yielding binary data chunks, or false on error.
 */
function get_binary_data_chunks(int $post_id): Generator|false
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_binary_data';

    // Prepare the query to fetch the blob data.
    // We select only the blob_data to minimize data transfer.
    $query = $wpdb->prepare(
        "SELECT blob_data FROM {$table_name} WHERE post_id = %d",
        $post_id
    );

    // Execute the query.
    $result = $wpdb->get_row($query, OBJECT);

    if (!$result || empty($result->blob_data)) {
        // Return false or throw an exception if no data is found or an error occurred.
        return false;
    }

    // For simplicity, we're yielding the entire blob as one chunk here.
    // In a more advanced scenario with extremely large blobs, you might
    // fetch data in smaller, fixed-size chunks from the database if supported
    // by the driver or by implementing custom chunking logic.
    // However, PHP's get_row typically fetches the whole row.
    // The generator's benefit here is in *how* this data is processed downstream.
    yield $result->blob_data;

    // If you were fetching from a file stream or a database that supports
    // streaming results, you would loop and yield here.
    // Example conceptual loop (not directly applicable to $wpdb->get_row):
    /*
    while ($chunk = fetch_next_chunk_from_stream($stream)) {
        yield $chunk;
    }
    */
}

Processing Binary Data with Generators

The real power of the generator comes when you consume the yielded data. Instead of loading a potentially massive blob into a variable, you iterate over the chunks. This is particularly useful for tasks like sending files to the client, processing images, or performing cryptographic operations on large data sets.

Here’s an example of how to use the `get_binary_data_chunks` generator to output a file to the browser.

/**
 * Outputs binary data for a given post ID to the browser.
 *
 * @param int $post_id The ID of the WordPress post.
 * @param string $file_name The desired filename for the download.
 * @param string $mime_type The MIME type of the file.
 */
function output_binary_file(int $post_id, string $file_name, string $mime_type): void
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_binary_data';

    // First, retrieve metadata about the file to set headers correctly.
    $metadata = $wpdb->get_row(
        $wpdb->prepare(
            "SELECT file_name, mime_type, file_size FROM {$table_name} WHERE post_id = %d LIMIT 1",
            $post_id
        )
    );

    if (!$metadata) {
        wp_die('File not found.', 'Error', ['response' => 404]);
    }

    // Set appropriate HTTP headers for file download.
    header('Content-Description: File Transfer');
    header('Content-Type: ' . $metadata->mime_type);
    header('Content-Disposition: attachment; filename="' . basename($metadata->file_name) . '"');
    header('Expires: 0');
    header('Cache-Control: must-revalidate');
    header('Pragma: public');
    header('Content-Length: ' . $metadata->file_size);

    // Ensure no output before headers.
    if (ob_get_level()) {
        ob_end_clean();
    }

    // Use the generator to stream the data.
    $data_generator = get_binary_data_chunks($post_id);

    if ($data_generator) {
        foreach ($data_generator as $chunk) {
            echo $chunk; // Echo each chunk as it's yielded.
        }
    } else {
        wp_die('Failed to retrieve file content.', 'Error', ['response' => 500]);
    }

    exit; // Terminate script execution after sending the file.
}

// Example usage:
// Assuming you have a URL like yoursite.com/download-file/?post_id=123
/*
add_action('template_redirect', function() {
    if (isset($_GET['post_id']) && current_user_can('read')) { // Add capability check
        $post_id = intval($_GET['post_id']);
        // In a real scenario, you'd fetch file_name and mime_type from DB
        // or pass them as parameters securely.
        // For this example, we'll assume they are known or fetched.
        // Let's fetch them here for completeness.
        global $wpdb;
        $table_name = $wpdb->prefix . 'custom_binary_data';
        $file_info = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT file_name, mime_type FROM {$table_name} WHERE post_id = %d LIMIT 1",
                $post_id
            )
        );

        if ($file_info) {
            output_binary_file($post_id, $file_info->file_name, $file_info->mime_type);
        } else {
            wp_die('File metadata not found.', 'Error', ['response' => 404]);
        }
    }
});
*/

Uploading and Storing Binary Data

Storing binary data requires careful handling of file uploads and database insertions. The `$_FILES` superglobal in PHP provides information about uploaded files. We’ll create a function to handle the upload process and store the data in our custom table.

/**
 * Handles the upload and storage of binary data to the custom table.
 *
 * @param int $post_id The ID of the WordPress post to associate the data with.
 * @param array $file_input The $_FILES array entry for the uploaded file.
 * @return int|false The ID of the newly inserted row, or false on failure.
 */
function store_binary_data(int $post_id, array $file_input): int|false
{
    if (!isset($file_input['error']) || $file_input['error'] !== UPLOAD_ERR_OK) {
        // Handle upload errors.
        return false;
    }

    $file_tmp_path = $file_input['tmp_name'];
    $file_name = basename($file_input['name']);
    $mime_type = mime_content_type($file_tmp_path); // More reliable than relying on extension
    $file_size = $file_input['size'];

    // Read the file content.
    $blob_data = file_get_contents($file_tmp_path);
    if ($blob_data === false) {
        // Handle error reading file.
        return false;
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'custom_binary_data';

    $inserted = $wpdb->insert(
        $table_name,
        [
            'post_id'     => $post_id,
            'file_name'   => $file_name,
            'mime_type'   => $mime_type,
            'file_size'   => $file_size,
            'blob_data'   => $blob_data, // Storing directly in BLOB
            'storage_path' => null, // Not using file system storage in this example
        ],
        [
            '%d', // post_id
            '%s', // file_name
            '%s', // mime_type
            '%d', // file_size
            '%s', // blob_data (as a string for BLOB)
            '%s', // storage_path
        ]
    );

    if ($inserted === false) {
        // Handle database insertion error.
        return false;
    }

    return $wpdb->insert_id;
}

// Example usage within a form submission handler:
/*
add_action('admin_post_upload_custom_binary', function() {
    if (!isset($_POST['_wpnonce']) || !wp_verify_nonce($_POST['_wpnonce'], 'upload_binary_nonce')) {
        wp_die('Security check failed.');
    }

    if (isset($_FILES['custom_binary_file']) && $_FILES['custom_binary_file']['error'] !== UPLOAD_ERR_NO_FILE) {
        $post_id = isset($_POST['post_id']) ? intval($_POST['post_id']) : 0;
        if ($post_id && current_user_can('edit_post', $post_id)) {
            $result_id = store_binary_data($post_id, $_FILES['custom_binary_file']);
            if ($result_id) {
                // Redirect with success message
                wp_redirect(admin_url('post.php?post=' . $post_id . '&action=edit&message=1'));
            } else {
                // Redirect with error message
                wp_redirect(admin_url('post.php?post=' . $post_id . '&action=edit&message=2')); // Custom error message code
            }
            exit;
        }
    }
    // Redirect back if no file or invalid post ID
    wp_redirect(admin_url('edit.php'));
    exit;
});
*/

Considerations for Large Files and Performance

While storing binary data in the database is convenient, it has implications:

• Database Size: Large binary blobs can significantly increase the size of your database, impacting backup times, migration complexity, and overall server resource usage.
• Memory Limits: Even with generators, the initial `file_get_contents` and database fetch might still load the entire blob into memory on the PHP side before it’s yielded. For extremely large files (hundreds of MBs or GBs), this can exceed PHP’s `memory_limit`.
• Database Performance: Frequent reads/writes of large BLOBs can strain database I/O.

For scenarios involving very large files, consider these alternatives:

• File System Storage: Store files on the server’s file system (e.g., in `wp-content/uploads/custom-binaries/`) and only store the file path and metadata in the database. This is generally the most scalable approach for large assets.
• External Object Storage: Utilize services like Amazon S3, Google Cloud Storage, or Azure Blob Storage. Store the object URL or key in your custom table. This offloads storage and delivery entirely.
• Database Chunking (Advanced): If you must store in the DB and face memory issues, you could split the `blob_data` into multiple rows or use database-specific streaming APIs if available, though this adds significant complexity.

The generator pattern remains beneficial regardless of the storage method, as it decouples data retrieval from processing, allowing for efficient consumption of data streams.