WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Fiber lightweight concurrency
Database Schema for Binary Data
When dealing with binary data within WordPress custom tables, efficiency is paramount. Storing large binary blobs directly in standard VARCHAR or TEXT columns is a performance anti-pattern. Instead, we’ll leverage the `LONGBLOB` data type for efficient storage. This recipe outlines the creation of a custom table and the PHP code to manage binary assets.
First, let’s define the SQL for our custom table. We’ll name it wp_binary_assets. It will include an auto-incrementing primary key, a unique identifier for the asset, a timestamp for creation, and the binary data itself.
SQL Table Definition
CREATE TABLE wp_binary_assets (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
asset_uuid VARCHAR(36) NOT NULL UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
asset_data LONGBLOB NOT NULL,
PRIMARY KEY (id),
KEY idx_asset_uuid (asset_uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
PHP Class for Binary Asset Management
We’ll encapsulate the logic for interacting with this table within a PHP class. This class will handle insertion, retrieval, and deletion of binary assets. For concurrency, we’ll introduce Fiber support, allowing non-blocking I/O operations if integrated with an asynchronous framework, though for direct database operations, it primarily offers a cleaner way to manage stateful operations without complex callback chains.
Asset Storage (Insertion)
The store_asset method takes a binary string and a UUID, then inserts it into the database. We’ll use prepared statements to prevent SQL injection and ensure data integrity. The use of wpdb is standard for WordPress database interactions.
class BinaryAssetManager {
private wpdb $wpdb;
private string $table_name;
public function __construct() {
global $wpdb;
$this->wpdb = $wpdb;
$this->table_name = $this->wpdb->prefix . 'binary_assets';
}
/**
* Stores binary data in the custom table.
*
* @param string $uuid A unique identifier for the asset.
* @param string $binary_data The raw binary data.
* @return bool True on success, false on failure.
*/
public function store_asset(string $uuid, string $binary_data): bool {
if (empty($uuid) || empty($binary_data)) {
return false;
}
// Ensure the table exists. In a real plugin, this would be handled by an activation hook.
$this->ensure_table_exists();
$prepared_uuid = sanitize_text_field($uuid);
$binary_data_escaped = $this->wpdb->prepare( '%s', $binary_data ); // Prepare for LONGBLOB
$result = $this->wpdb->insert(
$this->table_name,
array(
'asset_uuid' => $prepared_uuid,
'asset_data' => $binary_data_escaped,
),
array(
'%s', // asset_uuid
'%s', // asset_data (wpdb handles LONGBLOB correctly with %s)
)
);
return $result !== false;
}
/**
* Ensures the custom table exists. For production, this should be in an activation hook.
*/
private function ensure_table_exists() {
if ($this->wpdb->get_var("SHOW TABLES LIKE '{$this->table_name}'") !== $this->table_name) {
// This is a simplified example. In a real plugin, use dbDelta for schema management.
// For demonstration, we'll assume it's created manually or via an activation hook.
// Example using dbDelta (requires 'upgrade.php' to be included):
// require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
// dbDelta("CREATE TABLE {$this->table_name} ( ... );");
}
}
}
Asset Retrieval (Reading)
The get_asset method retrieves the binary data associated with a given UUID. It returns the raw binary string or false if the asset is not found or an error occurs. We’ll use $wpdb->get_var for fetching a single value.
class BinaryAssetManager {
// ... (previous code) ...
/**
* Retrieves binary data by its UUID.
*
* @param string $uuid The unique identifier of the asset.
* @return string|false The raw binary data, or false if not found or error.
*/
public function get_asset(string $uuid): string|false {
if (empty($uuid)) {
return false;
}
$prepared_uuid = sanitize_text_field($uuid);
$query = $this->wpdb->prepare(
"SELECT asset_data FROM {$this->table_name} WHERE asset_uuid = %s",
$prepared_uuid
);
$asset_data = $this->wpdb->get_var($query);
if ($this->wpdb->last_error) {
// Log error: error_log("Database error retrieving asset: " . $this->wpdb->last_error);
return false;
}
return $asset_data === null ? false : $asset_data;
}
}
Asset Deletion
The delete_asset method removes an asset from the database based on its UUID. This is crucial for data lifecycle management.
class BinaryAssetManager {
// ... (previous code) ...
/**
* Deletes an asset by its UUID.
*
* @param string $uuid The unique identifier of the asset to delete.
* @return int|false The number of rows affected, or false on failure.
*/
public function delete_asset(string $uuid): int|false {
if (empty($uuid)) {
return false;
}
$prepared_uuid = sanitize_text_field($uuid);
$result = $this->wpdb->delete(
$this->table_name,
array( 'asset_uuid' => $prepared_uuid ),
array( '%s' )
);
if ($this->wpdb->last_error) {
// Log error: error_log("Database error deleting asset: " . $this->wpdb->last_error);
return false;
}
return $result;
}
}
Leveraging Fibers for Concurrency (Conceptual)
While direct database operations are typically synchronous, Fibers can be instrumental in orchestrating multiple such operations or integrating with asynchronous I/O layers. For instance, if you were performing multiple asset uploads or downloads concurrently within a single request context (e.g., a background job processing queue), Fibers provide a more structured way to manage these tasks compared to traditional callbacks or promises in a PHP environment that supports them.
Consider a scenario where you need to process a batch of assets. Without Fibers, this might involve complex loops and error handling. With Fibers, you can yield control back to the event loop (if present) or simply manage sequential execution within a single thread more cleanly.
Example: Batch Asset Processing with Fibers
This example demonstrates how Fibers *could* be used to manage multiple asset operations. Note that for standard WordPress HTTP requests, the overhead of Fibers might not yield significant performance gains unless integrated with a specific asynchronous framework or a long-running process.
// Assuming BinaryAssetManager is instantiated and available
$assetManager = new BinaryAssetManager();
// Sample data: array of UUIDs and their corresponding binary content
$assets_to_process = [
'uuid-1' => file_get_contents('/path/to/file1.bin'),
'uuid-2' => file_get_contents('/path/to/file2.bin'),
'uuid-3' => file_get_contents('/path/to/file3.bin'),
];
// Create a Fiber for each asset operation
$fibers = [];
foreach ($assets_to_process as $uuid => $data) {
$fibers[$uuid] = new Fiber(function () use ($assetManager, $uuid, $data) {
try {
echo "Starting upload for {$uuid}...\n";
$success = $assetManager->store_asset($uuid, $data);
if ($success) {
echo "Successfully uploaded {$uuid}.\n";
return true; // Fiber returns success
} else {
echo "Failed to upload {$uuid}.\n";
return false; // Fiber returns failure
}
} catch (Throwable $e) {
// Log error: error_log("Fiber error for {$uuid}: " . $e->getMessage());
echo "Exception during upload for {$uuid}: " . $e->getMessage() . "\n";
return false;
}
});
}
// Execute the Fibers sequentially (or in a more complex scheduler)
foreach ($fibers as $uuid => $fiber) {
if ($fiber->isSuspended()) {
$fiber->resume(); // Start or resume the fiber
}
// In a real async scenario, you'd manage execution and resumption more dynamically.
// For synchronous execution, resume() is called once.
// The Fiber will run to completion or until it yields (if yield is used).
// Here, it runs to completion as store_asset is synchronous.
}
// Check results (optional)
foreach ($fibers as $uuid => $fiber) {
$result = $fiber->isTerminated() ? $fiber->getReturn() : 'Not terminated';
echo "Result for {$uuid}: " . var_export($result, true) . "\n";
}
// Example of retrieving an asset using a Fiber (conceptual)
$uuid_to_retrieve = 'uuid-1';
$retrieve_fiber = new Fiber(function () use ($assetManager, $uuid_to_retrieve) {
echo "Attempting to retrieve {$uuid_to_retrieve}...\n";
$data = $assetManager->get_asset($uuid_to_retrieve);
if ($data !== false) {
echo "Retrieved {$uuid_to_retrieve} (" . strlen($data) . " bytes).\n";
return $data;
} else {
echo "Failed to retrieve {$uuid_to_retrieve}.\n";
return false;
}
});
if ($retrieve_fiber->isSuspended()) {
$retrieved_data = $retrieve_fiber->resume();
// Process $retrieved_data
}
Performance Considerations and Best Practices
- Database Indexing: Ensure `asset_uuid` is indexed for fast lookups. The `idx_asset_uuid` in the schema definition handles this.
- Data Size Limits: Be mindful of MySQL’s `max_allowed_packet` setting. If you’re storing very large binaries, you might need to increase this value.
- Alternative Storage: For extremely large files or high-traffic scenarios, consider offloading binary storage to dedicated object storage services (e.g., AWS S3, Google Cloud Storage) and storing only the URL or reference in your custom table.
- Error Handling: Robust error logging and handling are critical, especially when dealing with I/O operations and database interactions.
- Activation Hook: The `ensure_table_exists` method is a placeholder. In a production plugin, use WordPress’s activation hook (`register_activation_hook`) with `dbDelta()` to manage table creation and updates reliably.
- Security: Always sanitize inputs and use prepared statements to prevent SQL injection. Ensure appropriate file permissions if storing files on the filesystem.
By combining efficient database schema design with careful PHP implementation, you can effectively manage binary assets within WordPress custom tables. The conceptual use of Fibers highlights a path towards more structured concurrency, particularly beneficial in complex background processing tasks.