WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using WeakMaps for caching

Database Schema for Binary Data

When dealing with binary data (e.g., images, serialized objects, encrypted payloads) within a WordPress plugin, storing it directly in post meta or options tables is often inefficient and can lead to performance bottlenecks. A more robust approach involves creating custom database tables. For binary data, we’ll use the BLOB (Binary Large Object) data type. This recipe outlines a strategy for managing such data, focusing on efficient retrieval and caching using PHP’s WeakMap.

Let’s define a simple custom table structure. We’ll assume a primary key, a unique identifier for the data (e.g., a post ID, a user ID, or a custom slug), and the BLOB column itself. We’ll also include timestamps for auditing.

SQL Schema Definition

CREATE TABLE wp_custom_binary_data (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    data_key VARCHAR(255) NOT NULL UNIQUE,
    binary_content LONGBLOB NOT NULL,
    created_at DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
    updated_at DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL,
    PRIMARY KEY (id),
    KEY data_key (data_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This SQL statement creates a table named wp_custom_binary_data. The data_key column serves as a unique identifier for each binary entry, allowing for easy lookup. binary_content is of type LONGBLOB, suitable for storing large binary data. The created_at and updated_at columns are standard practice for tracking record modifications.

PHP Class for Data Management

We’ll encapsulate the database interactions within a PHP class. This class will handle insertion, retrieval, and deletion of binary data. Crucially, it will also incorporate a caching mechanism using PHP’s WeakMap to store recently accessed binary content in memory, reducing redundant database queries.

Data Storage and Retrieval Logic

The core functionality involves interacting with the WordPress database API. We’ll use $wpdb for all database operations. The get_binary_data method will first check the cache before querying the database.

// Assume this class is part of your plugin's core functionality.
// It should be instantiated and managed appropriately.

class CustomBinaryStorage {
    private static $instance;
    private $wpdb;
    private $table_name;
    private $cache; // This will be our WeakMap

    // Use a WeakMap for caching. Keys are strings (data_key), values are the binary content.
    // WeakMap allows garbage collection of cached items if the key object is no longer referenced.
    // For string keys, this is less impactful than object keys, but it's a good pattern.
    private function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_name = $this->wpdb->prefix . 'custom_binary_data';
        // Initialize WeakMap. Note: WeakMap keys must be objects.
        // For string keys, we'll use a workaround or a different caching strategy if strict WeakMap behavior is needed.
        // A simple array can serve as a cache for string keys, but it won't auto-evict.
        // For this example, let's use a standard array as a simple in-memory cache.
        // If true WeakMap behavior is critical, consider using SPLWeakMap if available and appropriate for your PHP version.
        $this->cache = []; // Using a simple array for demonstration.
    }

    // Singleton pattern
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Stores binary data in the custom table.
     *
     * @param string $data_key A unique identifier for the data.
     * @param string $binary_content The binary data to store (as a string).
     * @return bool True on success, false on failure.
     */
    public function store_binary_data(string $data_key, string $binary_content): bool {
        $timestamp = current_time('mysql');
        $result = $this->wpdb->insert(
            $this->table_name,
            [
                'data_key' => $data_key,
                'binary_content' => $binary_content,
                'created_at' => $timestamp,
                'updated_at' => $timestamp,
            ],
            ['%s', '%s', '%s', '%s']
        );

        if (false === $result) {
            // Log error if needed
            error_log("Failed to store binary data for key: " . $data_key . " - " . $this->wpdb->last_error);
            return false;
        }

        // Invalidate cache if data is updated or inserted
        unset($this->cache[$data_key]);
        return true;
    }

    /**
     * Retrieves binary data from the custom table.
     *
     * @param string $data_key The unique identifier for the data.
     * @return string|false The binary content as a string, or false if not found or an error occurred.
     */
    public function get_binary_data(string $data_key) {
        // 1. Check cache first
        if (isset($this->cache[$data_key])) {
            return $this->cache[$data_key];
        }

        // 2. Query the database
        $query = $this->wpdb->prepare(
            "SELECT binary_content FROM {$this->table_name} WHERE data_key = %s",
            $data_key
        );

        $binary_content = $this->wpdb->get_var($query);

        if (null === $binary_content) {
            // Data not found
            return false;
        }

        // 3. Store in cache for future requests
        $this->cache[$data_key] = $binary_content;

        return $binary_content;
    }

    /**
     * Deletes binary data from the custom table.
     *
     * @param string $data_key The unique identifier for the data.
     * @return bool True on success, false on failure.
     */
    public function delete_binary_data(string $data_key): bool {
        $result = $this->wpdb->delete(
            $this->table_name,
            ['data_key' => $data_key],
            ['%s']
        );

        if (false === $result) {
            // Log error if needed
            error_log("Failed to delete binary data for key: " . $data_key . " - " . $this->wpdb->last_error);
            return false;
        }

        // Remove from cache if deleted
        unset($this->cache[$data_key]);
        return true;
    }

    /**
     * Updates existing binary data.
     *
     * @param string $data_key The unique identifier for the data.
     * @param string $new_binary_content The new binary data.
     * @return bool True on success, false on failure.
     */
    public function update_binary_data(string $data_key, string $new_binary_content): bool {
        $timestamp = current_time('mysql');
        $result = $this->wpdb->update(
            $this->table_name,
            [
                'binary_content' => $new_binary_content,
                'updated_at' => $timestamp,
            ],
            ['data_key' => $data_key],
            ['%s', '%s'], // New content and timestamp
            ['%s']       // data_key
        );

        if (false === $result) {
            // Log error if needed
            error_log("Failed to update binary data for key: " . $data_key . " - " . $this->wpdb->last_error);
            return false;
        }

        // Invalidate cache on update
        unset($this->cache[$data_key]);
        return true;
    }

    /**
     * Clears the entire cache.
     */
    public function clear_cache(): void {
        $this->cache = [];
    }
}

In the CustomBinaryStorage class:

• The constructor initializes $wpdb and sets the table name. It also initializes $cache as a simple array. For true WeakMap behavior with object keys, you would use $this->cache = new \WeakMap();. However, WeakMap keys must be objects. If your keys are strings, a standard array or a more sophisticated caching library (like Redis or Memcached via a WordPress object cache plugin) would be more appropriate. For this recipe, the array demonstrates the caching concept.
• The get_instance() method implements the Singleton pattern, ensuring only one instance of the class is active.
• store_binary_data() inserts a new record into the table. It also invalidates any cached entry for the given data_key to ensure data consistency.
• get_binary_data() is the core retrieval method. It first checks the $cache. If the data is found, it’s returned immediately. Otherwise, it queries the database, stores the result in the cache, and then returns it.
• delete_binary_data() removes a record and its corresponding cache entry.
• update_binary_data() modifies an existing record and clears the cache.
• clear_cache() provides a way to flush all cached data.

Integration and Usage Example

To use this class within your WordPress plugin, you would typically instantiate it and call its methods. For example, when processing an uploaded image that needs to be stored and later retrieved for display:

// Get the instance of our storage class
$storage = CustomBinaryStorage::get_instance();

// Example: Storing an uploaded image's content
if (isset($_FILES['my_image']) && $_FILES['my_image']['error'] === UPLOAD_ERR_OK) {
    $file_tmp_path = $_FILES['my_image']['tmp_name'];
    $file_content = file_get_contents($file_tmp_path);
    $data_key = 'user_avatar_' . get_current_user_id(); // Example key

    if ($file_content !== false) {
        // Check if data already exists to decide between store or update
        $existing_data = $storage->get_binary_data($data_key);
        if ($existing_data === false) {
            $success = $storage->store_binary_data($data_key, $file_content);
            if ($success) {
                echo "Image stored successfully!";
            } else {
                echo "Failed to store image.";
            }
        } else {
            // Data exists, perform an update
            $success = $storage->update_binary_data($data_key, $file_content);
            if ($success) {
                echo "Image updated successfully!";
            } else {
                echo "Failed to update image.";
            }
        }
    }
}

// Example: Retrieving and displaying an image
$user_id = get_current_user_id();
$data_key_to_retrieve = 'user_avatar_' . $user_id;
$image_data = $storage->get_binary_data($data_key_to_retrieve);

if ($image_data !== false) {
    // Output the image directly. Ensure correct Content-Type header is set.
    // This would typically be done in a separate AJAX handler or a dedicated endpoint.
    // For demonstration, imagine this is in a context where headers can be set.
    // header('Content-Type: image/jpeg'); // Adjust MIME type as needed
    // echo $image_data;
    echo "<p>Image data retrieved. Displaying placeholder.</p>";
    echo "<img src='path/to/your/image-display-endpoint?key=" . urlencode($data_key_to_retrieve) . "' alt='User Avatar'>";
} else {
    echo "<p>No avatar found for this user.</p>";
}

// Example: Deleting an image
// $storage->delete_binary_data($data_key_to_retrieve);

When outputting binary data directly (e.g., for an image), it’s crucial to set the correct Content-Type HTTP header before echoing the data. This is typically handled in a dedicated WordPress AJAX action or a custom rewrite rule that points to a PHP script responsible for serving the binary content.

Considerations for Production

• Error Handling: The provided code includes basic error logging. In a production environment, implement more robust error tracking and reporting.
• Cache Invalidation: While unset($this->cache[$data_key]) is used, complex scenarios might require more sophisticated invalidation strategies, especially if data can be modified by multiple processes or external systems.
• Cache Size: The simple array cache can grow indefinitely. For very large datasets or high traffic, consider implementing a cache eviction policy (e.g., LRU – Least Recently Used) or integrating with external caching systems like Redis or Memcached via WordPress’s object cache API.
• Security: Ensure that access to binary data is properly authenticated and authorized. The data_key should not expose sensitive information directly.
• Database Performance: For extremely large BLOBs or very high read/write volumes, consider database tuning, indexing strategies, and potentially offloading binary storage to dedicated object storage services (like AWS S3) if the WordPress database becomes a bottleneck.
• PHP Version: If using \WeakMap, ensure your PHP version supports it (PHP 7.4+). If not, a standard array or a different caching mechanism is necessary.
• Data Serialization: If storing complex PHP objects, ensure they are properly serialized before storing and unserialized upon retrieval.

This recipe provides a foundational pattern for efficient binary data management within custom WordPress tables, leveraging in-memory caching to optimize retrieval performance. Always adapt and extend these patterns based on your specific application’s requirements and scale.