WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Readonly classes
Database Schema for Binary Data
When developing custom WordPress plugins that require storing binary data (like images, PDFs, or serialized objects) alongside other structured data, it’s often more efficient and maintainable to create dedicated custom database tables rather than relying solely on the WordPress options API or post meta. This approach offers better performance for querying and managing large binary assets. We’ll define a simple table structure suitable for this purpose.
Consider a scenario where you need to store user-uploaded documents, each associated with a specific user and a descriptive title. A suitable table structure would include columns for a unique identifier, the associated user ID, a title, the MIME type of the file, the file size, and the binary content itself. For the binary content, we’ll use the `LONGBLOB` data type, which is appropriate for storing large binary objects in MySQL.
SQL Schema Definition
Here’s the SQL statement to create the necessary table. This should be executed during your plugin’s activation hook.
CREATE TABLE {$wpdb->prefix}plugin_binary_storage (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
title VARCHAR(255) NOT NULL DEFAULT '',
mime_type VARCHAR(100) NOT NULL DEFAULT '',
file_size BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
binary_data LONGBLOB NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Plugin Activation Hook for Table Creation
To ensure the table is created when your plugin is activated, you’ll hook into the `activate_{plugin_file}` action. This is a standard WordPress practice for managing plugin-specific database structures.
First, define your main plugin file (e.g., `my-binary-storage-plugin.php`). Inside this file, you’ll have the activation logic.
// my-binary-storage-plugin.php
/**
* Plugin Name: My Binary Storage Plugin
* Description: A plugin to demonstrate efficient binary storage in custom tables.
* Version: 1.0
* Author: Your Name
*/
if ( ! defined( 'ABSPATH' ) ) {
exit; // Exit if accessed directly.
}
// Activation hook
register_activation_hook( __FILE__, 'my_plugin_activate' );
function my_plugin_activate() {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE {$table_name} (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
title VARCHAR(255) NOT NULL DEFAULT '',
mime_type VARCHAR(100) NOT NULL DEFAULT '',
file_size BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
binary_data LONGBLOB NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY idx_user_id (user_id)
) {$charset_collate};";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
// Add other plugin functionality here...
Introducing Readonly Classes for Data Representation
PHP 8.1 introduced readonly classes, which are invaluable for creating immutable data objects. When dealing with data retrieved from the database, especially binary content, using readonly classes ensures that the data, once fetched, cannot be accidentally modified. This promotes data integrity and makes your code more predictable.
We’ll create a readonly class to represent a single record from our `plugin_binary_storage` table. This class will hold the properties corresponding to the table’s columns.
Readonly Data Object Definition
/**
* Represents a binary data record from the custom storage table.
* This class is readonly, ensuring immutability after instantiation.
*/
readonly class BinaryStorageRecord {
public int $id;
public int $user_id;
public string $title;
public string $mime_type;
public int $file_size;
public string $binary_data; // Note: Storing large blobs as strings can be memory intensive.
public string $created_at;
public string $updated_at;
public function __construct(
int $id,
int $user_id,
string $title,
string $mime_type,
int $file_size,
string $binary_data,
string $created_at,
string $updated_at
) {
$this->id = $id;
$this->user_id = $user_id;
$this->title = $title;
$this->mime_type = $mime_type;
$this->file_size = $file_size;
$this->binary_data = $binary_data;
$this->created_at = $created_at;
$this->updated_at = $updated_at;
}
/**
* Returns the binary data as a resource stream for potentially large data.
* This is a placeholder; actual implementation might involve stream wrappers or specific handlers.
* For simplicity here, we return the string, but in production, consider alternatives for very large blobs.
*
* @return string The binary data.
*/
public function getBinaryData(): string {
return $this->binary_data;
}
/**
* Returns the MIME type of the stored data.
*
* @return string The MIME type.
*/
public function getMimeType(): string {
return $this->mime_type;
}
/**
* Returns the file size of the stored data.
*
* @return int The file size in bytes.
*/
public function getFileSize(): int {
return $this->file_size;
}
}
Important Note on `LONGBLOB` and Memory Usage: While `LONGBLOB` is suitable for storing binary data directly in the database, fetching large blobs into PHP memory as strings can be highly inefficient and lead to memory exhaustion. For production environments dealing with very large files, consider storing files on the filesystem and only storing the file path or a reference in the database. Alternatively, explore database features like `LOAD_FILE()` or streaming mechanisms if your database driver supports them efficiently. The example above fetches the blob as a string for demonstration purposes.
Implementing Data Storage and Retrieval Functions
Now, let’s create functions to interact with our custom table, leveraging the `BinaryStorageRecord` readonly class for retrieved data.
Storing Binary Data
The `store_binary_data` function will handle inserting new records into the table. It takes the user ID, title, MIME type, and the binary data itself as arguments.
/**
* Stores binary data in the custom table.
*
* @param int $user_id The ID of the user associated with the data.
* @param string $title A descriptive title for the data.
* @param string $mime_type The MIME type of the data (e.g., 'image/jpeg').
* @param string $data The binary data content.
* @return int|false The ID of the newly inserted record on success, false on failure.
*/
function store_binary_data( int $user_id, string $title, string $mime_type, string $data ): int|false {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$inserted = $wpdb->insert(
$table_name,
array(
'user_id' => $user_id,
'title' => sanitize_text_field( $title ),
'mime_type' => sanitize_mime_type( $mime_type ),
'file_size' => strlen( $data ),
'binary_data' => $data, // Direct insertion of binary data.
),
array(
'%d', // user_id
'%s', // title
'%s', // mime_type
'%d', // file_size
'%s', // binary_data (wpdb handles blob/binary correctly with %s)
)
);
if ( false === $inserted ) {
// Log error or handle failure
error_log( "Failed to insert binary data: " . $wpdb->last_error );
return false;
}
return $wpdb->insert_id;
}
Retrieving Binary Data
The `get_binary_record_by_id` function retrieves a single record by its ID and returns it as an instance of our `BinaryStorageRecord` readonly class. This ensures that the retrieved data is treated as immutable.
/**
* Retrieves a binary data record by its ID.
*
* @param int $id The ID of the record to retrieve.
* @return BinaryStorageRecord|null The record object if found, null otherwise.
*/
function get_binary_record_by_id( int $id ): ?BinaryStorageRecord {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE id = %d", $id ), ARRAY_A );
if ( ! $row ) {
return null;
}
// Ensure all expected keys exist, even if they are null from DB (though our schema doesn't allow nulls for core fields)
$row = array_merge( array(
'id' => 0,
'user_id' => 0,
'title' => '',
'mime_type' => '',
'file_size' => 0,
'binary_data' => '',
'created_at' => '0000-00-00 00:00:00',
'updated_at' => '0000-00-00 00:00:00',
), $row );
// Type casting for safety, though readonly class constructor will enforce types.
$id = (int) $row['id'];
$user_id = (int) $row['user_id'];
$title = (string) $row['title'];
$mime_type = (string) $row['mime_type'];
$file_size = (int) $row['file_size'];
$binary_data = (string) $row['binary_data']; // This is where large data is loaded into memory.
$created_at = (string) $row['created_at'];
$updated_at = (string) $row['updated_at'];
return new BinaryStorageRecord(
$id,
$user_id,
$title,
$mime_type,
$file_size,
$binary_data,
$created_at,
$updated_at
);
}
/**
* Retrieves all binary data records for a specific user.
*
* @param int $user_id The ID of the user.
* @return BinaryStorageRecord[] An array of record objects.
*/
function get_binary_records_by_user( int $user_id ): array {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$records = [];
$results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE user_id = %d ORDER BY created_at DESC", $user_id ), ARRAY_A );
if ( $results ) {
foreach ( $results as $row ) {
// Re-use the logic from get_binary_record_by_id for consistency
$row = array_merge( array(
'id' => 0,
'user_id' => 0,
'title' => '',
'mime_type' => '',
'file_size' => 0,
'binary_data' => '',
'created_at' => '0000-00-00 00:00:00',
'updated_at' => '0000-00-00 00:00:00',
), $row );
$id = (int) $row['id'];
$user_id_db = (int) $row['user_id']; // Renamed to avoid conflict with parameter
$title = (string) $row['title'];
$mime_type = (string) $row['mime_type'];
$file_size = (int) $row['file_size'];
$binary_data = (string) $row['binary_data'];
$created_at = (string) $row['created_at'];
$updated_at = (string) $row['updated_at'];
$records[] = new BinaryStorageRecord(
$id,
$user_id_db,
$title,
$mime_type,
$file_size,
$binary_data,
$created_at,
$updated_at
);
}
}
return $records;
}
Example Usage
Here’s how you might use these functions within your plugin’s logic, for instance, when handling a file upload.
// Assume this is part of a WordPress admin page or AJAX handler
// For demonstration, we'll simulate file upload data.
// Simulate uploaded file data
$uploaded_file_content = file_get_contents( '/path/to/your/example.pdf' ); // Replace with actual file path
$uploaded_file_type = 'application/pdf';
$uploaded_file_title = 'User Manual v1.2';
$current_user_id = get_current_user_id(); // Get the logged-in user ID
if ( $uploaded_file_content !== false && $current_user_id > 0 ) {
$new_record_id = store_binary_data(
$current_user_id,
$uploaded_file_title,
$uploaded_file_type,
$uploaded_file_content
);
if ( $new_record_id ) {
echo "Binary data stored successfully with ID: " . $new_record_id . "\n";
// Now, retrieve the data
$retrieved_record = get_binary_record_by_id( $new_record_id );
if ( $retrieved_record instanceof BinaryStorageRecord ) {
echo "Retrieved Record:\n";
echo " ID: " . $retrieved_record->id . "\n";
echo " Title: " . $retrieved_record->title . "\n";
echo " MIME Type: " . $retrieved_record->mime_type . "\n";
echo " File Size: " . $retrieved_record->file_size . " bytes\n";
// Accessing binary data (be cautious with large data)
// echo " Binary Data (first 50 bytes): " . substr( $retrieved_record->binary_data, 0, 50 ) . "...\n";
// Example of serving the file directly (e.g., in an AJAX response or a custom endpoint)
// header( 'Content-Type: ' . $retrieved_record->mime_type );
// header( 'Content-Length: ' . $retrieved_record->file_size );
// header( 'Content-Disposition: inline; filename="' . basename( $retrieved_record->title ) . '"' );
// echo $retrieved_record->binary_data;
// exit;
} else {
echo "Failed to retrieve the stored record.\n";
}
// Example of retrieving all records for a user
$user_records = get_binary_records_by_user( $current_user_id );
echo "Found " . count( $user_records ) . " records for user " . $current_user_id . "\n";
foreach ( $user_records as $record ) {
echo " - Record ID: " . $record->id . ", Title: " . $record->title . "\n";
}
} else {
echo "Failed to store binary data.\n";
}
} else {
echo "Error: Could not read file or user not logged in.\n";
}
Considerations for Production Environments
- Security: Always sanitize user inputs (title, mime type) and validate file types before storing. Ensure appropriate file permissions if storing on the filesystem.
- Performance: For very large binary files, consider storing them on the filesystem and only storing the file path or a reference in the database. This avoids loading large blobs into PHP memory and reduces database load.
- Error Handling: Implement robust error logging for database operations and file handling.
- Database Backups: Ensure your database backup strategy accounts for the size of your `LONGBLOB` columns.
- Scalability: If you anticipate a massive number of binary files, a dedicated object storage solution (like AWS S3, Google Cloud Storage) might be more appropriate than a relational database.
By combining custom database tables with PHP 8.1’s readonly classes, you can build robust, efficient, and maintainable solutions for managing binary data within your WordPress plugins.