WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Constructor Property Promotion
Database Schema Design for Binary Data
When developing custom WordPress plugins that require storing binary data (e.g., images, PDFs, serialized objects), directly embedding this data within the standard WordPress post meta or options tables is often inefficient and can lead to performance bottlenecks. A more robust approach involves creating dedicated custom database tables. For this recipe, we’ll design a table to store binary blobs along with associated metadata. We’ll use a `BLOB` data type for the binary content, which is suitable for variable-length binary data. For efficiency and to avoid potential issues with very large BLOBs, we’ll also store a `VARCHAR` for the MIME type and a `BIGINT` for the file size.
Creating the Custom Table with SQL
The following SQL statement defines the structure for our custom table. This table will hold the binary data, its associated metadata, and a reference to the WordPress post it belongs to. We’ll include an auto-incrementing primary key, a foreign key to the WordPress posts table, the MIME type, the file size, and the actual binary data.
CREATE TABLE wp_plugin_binary_storage (
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
post_id BIGINT(20) UNSIGNED NOT NULL,
mime_type VARCHAR(100) NOT NULL,
file_size BIGINT(20) UNSIGNED NOT NULL,
binary_data LONGBLOB NOT NULL,
uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (post_id) REFERENCES wp_posts(ID) ON DELETE CASCADE,
INDEX idx_post_id (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
To ensure this table is created when your plugin is activated, you’ll typically hook into the plugin activation process. This involves a function that checks if the table already exists and, if not, executes the SQL.
/**
* Plugin activation hook.
*/
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,
post_id BIGINT(20) UNSIGNED NOT NULL,
mime_type VARCHAR(100) NOT NULL,
file_size BIGINT(20) UNSIGNED NOT NULL,
binary_data LONGBLOB NOT NULL,
uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
FOREIGN KEY (post_id) REFERENCES {$wpdb->prefix}posts(ID) ON DELETE CASCADE,
INDEX idx_post_id (post_id)
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql );
}
register_activation_hook( __FILE__, 'my_plugin_activate' );
Leveraging Constructor Property Promotion for Data Handling
Constructor Property Promotion (introduced in PHP 8.0) offers a concise way to declare and initialize properties. This significantly reduces boilerplate code, making your data models cleaner and more maintainable. We’ll create a PHP class to represent a binary data entry in our custom table, utilizing this feature.
Binary Data Model Class
This class will serve as a Data Transfer Object (DTO) for our binary storage. It maps directly to the columns in our custom table. Notice how the constructor elegantly declares and assigns properties.
class BinaryStorageEntry {
public function __construct(
public int $id = 0,
public int $post_id,
public string $mime_type,
public int $file_size,
public string $binary_data, // Note: Storing raw binary as string for simplicity here. Consider alternatives for very large data.
public ?string $uploaded_at = null
) {}
/**
* Converts the object to an array suitable for database insertion.
*
* @return array
*/
public function toDatabaseArray(): array {
return [
'post_id' => $this->post_id,
'mime_type' => $this->mime_type,
'file_size' => $this->file_size,
'binary_data' => $this->binary_data,
];
}
/**
* Creates an instance from a database row.
*
* @param object $row Database row object.
* @return self
*/
public static function fromDatabaseRow(object $row): self {
return new self(
id: (int) $row->id,
post_id: (int) $row->post_id,
mime_type: $row->mime_type,
file_size: (int) $row->file_size,
binary_data: $row->binary_data, // Again, string representation of binary data.
uploaded_at: $row->uploaded_at
);
}
}
Implementing Storage and Retrieval Functions
Now, let’s create the core logic for interacting with our custom table. We’ll use the global $wpdb object for database operations. For storing binary data, we’ll need to handle file uploads or direct binary string input. For retrieval, we’ll fetch the data and return it, potentially as a stream or a raw string.
Storing Binary Data
This function takes a WordPress post ID, the binary content (as a string), and its MIME type. It then creates a BinaryStorageEntry object and inserts it into the database.
/**
* Stores binary data in the custom table.
*
* @param int $post_id The ID of the associated WordPress post.
* @param string $binary_content The binary data as a string.
* @param string $mime_type The MIME type of the data (e.g., 'image/jpeg').
*
* @return int|false The ID of the inserted row on success, false on failure.
*/
function store_binary_data( int $post_id, string $binary_content, string $mime_type ): int|false {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
if ( empty( $binary_content ) || empty( $mime_type ) ) {
return false;
}
$file_size = strlen( $binary_content );
$entry = new BinaryStorageEntry(
post_id: $post_id,
mime_type: sanitize_mime_type( $mime_type ), // Basic sanitization
file_size: $file_size,
binary_data: $binary_content // Direct assignment
);
$result = $wpdb->insert(
$table_name,
$entry->toDatabaseArray(),
[ '%d', '%s', '%d', '%s' ] // Data formats for post_id, mime_type, file_size, binary_data
);
if ( $result === false ) {
// Log error or handle failure
return false;
}
return $wpdb->insert_id;
}
/**
* Basic MIME type sanitization.
*
* @param string $mime_type
* @return string
*/
function sanitize_mime_type( string $mime_type ): string {
// A more robust validation might be needed depending on requirements.
// This is a basic example.
return sanitize_text_field( $mime_type );
}
Retrieving Binary Data
This function retrieves a specific binary entry by its ID or by the associated post ID. It returns a BinaryStorageEntry object or null if not found.
/**
* Retrieves binary data from the custom table.
*
* @param int $entry_id The ID of the binary storage entry.
*
* @return BinaryStorageEntry|null The entry object if found, null otherwise.
*/
function get_binary_entry_by_id( int $entry_id ): ?BinaryStorageEntry {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$row = $wpdb->get_row( $wpdb->prepare(
"SELECT * FROM $table_name WHERE id = %d",
$entry_id
) );
if ( ! $row ) {
return null;
}
return BinaryStorageEntry::fromDatabaseRow( $row );
}
/**
* Retrieves binary data associated with a specific post.
* Can return multiple entries if a post has multiple associated files.
*
* @param int $post_id The ID of the associated WordPress post.
*
* @return BinaryStorageEntry[] An array of entry objects.
*/
function get_binary_entries_by_post_id( int $post_id ): array {
global $wpdb;
$table_name = $wpdb->prefix . 'plugin_binary_storage';
$entries = [];
$rows = $wpdb->get_results( $wpdb->prepare(
"SELECT * FROM $table_name WHERE post_id = %d ORDER BY uploaded_at DESC",
$post_id
) );
if ( ! $rows ) {
return $entries;
}
foreach ( $rows as $row ) {
$entries[] = BinaryStorageEntry::fromDatabaseRow( $row );
}
return $entries;
}
Serving Binary Data
Once retrieved, you’ll often want to serve this binary data directly to the browser. This typically involves setting appropriate HTTP headers and then outputting the raw content. This is commonly done within a WordPress AJAX handler or a custom endpoint.
/**
* Serves binary data to the browser.
*
* @param BinaryStorageEntry $entry The binary storage entry object.
*/
function serve_binary_content( BinaryStorageEntry $entry ): void {
// Prevent direct access if this is not intended to be a public endpoint.
// Add appropriate security checks here.
header( 'Content-Type: ' . $entry->mime_type );
header( 'Content-Length: ' . $entry->file_size );
header( 'Content-Disposition: inline; filename="downloaded_file"' ); // Suggest a filename if applicable
header( 'Cache-Control: private, max-age=3600' ); // Example caching headers
header( 'Expires: ' . gmdate( 'D, d M Y H:i:s', time() + 3600 ) . ' GMT' );
// For very large files, consider streaming.
// For simplicity, we output the string directly.
echo $entry->binary_data;
exit; // Important to stop further WordPress execution.
}
// Example usage within an AJAX handler or custom endpoint:
// add_action( 'wp_ajax_my_plugin_get_file', 'handle_get_file_request' );
// function handle_get_file_request() {
// if ( isset( $_GET['entry_id'] ) && current_user_can( 'edit_posts' ) ) { // Example capability check
// $entry_id = intval( $_GET['entry_id'] );
// $entry = get_binary_entry_by_id( $entry_id );
//
// if ( $entry ) {
// serve_binary_content( $entry );
// } else {
// wp_send_json_error( 'File not found.' );
// }
// } else {
// wp_send_json_error( 'Invalid request or insufficient permissions.' );
// }
// }
Considerations for Large Binary Data
Storing very large binary files directly in the database as `LONGBLOB` can still lead to performance issues, including increased database size, slower backups, and potential memory limits during retrieval. For such scenarios, consider these alternatives:
- Filesystem Storage with Database References: Store the files on the server’s filesystem (e.g., in a dedicated uploads directory) and only store the file path, MIME type, and size in your custom database table. This is often the most scalable approach.
- Object Storage (S3, etc.): For highly scalable and distributed storage, integrate with services like Amazon S3. Your database would then store the object key, bucket name, and metadata.
- Streaming: When retrieving large files, avoid loading the entire content into PHP memory. Implement streaming mechanisms to read and output the data in chunks.
The Constructor Property Promotion pattern remains highly beneficial regardless of the storage backend, as it provides a clean, object-oriented way to manage the metadata associated with your binary assets.