WordPress Development Recipe: Efficient binary storage and retrieval in custom tables using Union and Intersection Types
Database Schema Design for Binary Data
When developing custom WordPress plugins that require storing and retrieving binary data, such as images, documents, or serialized objects, directly within custom database tables, a robust schema design is paramount. While WordPress’s built-in media library handles many common use cases, direct storage offers greater control and can be more efficient for specific application logic. We’ll explore a schema that leverages a primary table for metadata and a secondary table for the actual binary content, linked via a foreign key. This approach allows for efficient querying of metadata without loading large binary blobs unnecessarily.
Creating Custom Tables
The first step is to define and create the necessary database tables. We’ll create two tables: wp_custom_binary_metadata for storing information about the binary files and wp_custom_binary_data for the raw binary content. This separation is key for performance.
The wp_custom_binary_metadata table will store:
id: Primary key, auto-incrementing.file_name: The original name of the file (e.g., ‘document.pdf’).mime_type: The MIME type of the file (e.g., ‘application/pdf’).file_size: The size of the file in bytes.upload_time: Timestamp of when the file was uploaded.user_id: The ID of the user who uploaded the file.custom_field_1,custom_field_2: Example custom fields for additional metadata.
The wp_custom_binary_data table will store:
id: Primary key, auto-incrementing.metadata_id: Foreign key referencingwp_custom_binary_metadata.id.binary_content: The actual binary data, stored as aLONGBLOBorMEDIUMBLOBdepending on expected file sizes.
To create these tables, you can use a WordPress activation hook. Ensure you prefix your table names with the WordPress table prefix ($wpdb->prefix) to ensure compatibility.
Activation Hook for Table Creation
Place the following PHP code within your plugin’s main file or an included setup file.
/**
* Plugin activation hook to create custom tables.
*/
function my_custom_plugin_activate() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$table_data = $wpdb->prefix . 'custom_binary_data';
// Create metadata table
$sql_metadata = "CREATE TABLE $table_metadata (
id mediumint(9) NOT NULL AUTO_INCREMENT,
file_name varchar(255) NOT NULL,
mime_type varchar(100) NOT NULL,
file_size bigint(20) UNSIGNED NOT NULL,
upload_time datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_id bigint(20) UNSIGNED NOT NULL,
custom_field_1 varchar(255) NULL,
custom_field_2 text NULL,
PRIMARY KEY (id),
KEY idx_user_id (user_id)
) $charset_collate;";
// Create binary data table
// Use MEDIUMBLOB for typical file sizes, LONGBLOB for very large files.
$sql_data = "CREATE TABLE $table_data (
id mediumint(9) NOT NULL AUTO_INCREMENT,
metadata_id mediumint(9) NOT NULL,
binary_content MEDIUMBLOB NOT NULL,
PRIMARY KEY (id),
KEY idx_metadata_id (metadata_id),
FOREIGN KEY (metadata_id) REFERENCES $table_metadata(id) ON DELETE CASCADE
) $charset_collate;";
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
dbDelta( $sql_metadata );
dbDelta( $sql_data );
}
register_activation_hook( __FILE__, 'my_custom_plugin_activate' );
Uploading Binary Data
When a user uploads a file, you’ll need to handle the file upload process, store the metadata in wp_custom_binary_metadata, and then store the binary content in wp_custom_binary_data. It’s crucial to perform this in a transactional manner if possible, or at least with robust error handling.
Here’s a PHP example demonstrating how to handle a file upload and store it:
/**
* Handles the upload and storage of a binary file.
*
* @param array $file_data The $_FILES data for the uploaded file.
* @return int|false The ID of the inserted metadata record, or false on failure.
*/
function handle_binary_upload( $file_data ) {
global $wpdb;
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$table_data = $wpdb->prefix . 'custom_binary_data';
// Basic validation
if ( ! isset( $file_data['tmp_name'] ) || empty( $file_data['tmp_name'] ) || ! is_uploaded_file( $file_data['tmp_name'] ) ) {
error_log( 'Invalid file upload data.' );
return false;
}
$file_name = sanitize_file_name( $file_data['name'] );
$mime_type = sanitize_mime_type( $file_data['type'] );
$file_size = $file_data['size'];
$user_id = get_current_user_id();
// Read the binary content
$binary_content = file_get_contents( $file_data['tmp_name'] );
if ( $binary_content === false ) {
error_log( 'Failed to read file content for: ' . $file_name );
return false;
}
// Start a transaction (if your DB engine supports it, e.g., InnoDB)
$wpdb->query( 'START TRANSACTION;' );
// Insert metadata
$metadata_insert_result = $wpdb->insert(
$table_metadata,
array(
'file_name' => $file_name,
'mime_type' => $mime_type,
'file_size' => $file_size,
'user_id' => $user_id,
'custom_field_1' => 'some_value', // Example custom field
),
array(
'%s', // file_name
'%s', // mime_type
'%d', // file_size
'%d', // user_id
'%s', // custom_field_1
)
);
if ( $metadata_insert_result === false ) {
$wpdb->query( 'ROLLBACK;' );
error_log( 'Failed to insert metadata for file: ' . $file_name . '. Error: ' . $wpdb->last_error );
return false;
}
$metadata_id = $wpdb->insert_id;
// Insert binary data
$data_insert_result = $wpdb->insert(
$table_data,
array(
'metadata_id' => $metadata_id,
'binary_content' => $binary_content,
),
array(
'%d', // metadata_id
'%s', // binary_content (WordPress handles blob insertion correctly)
)
);
if ( $data_insert_result === false ) {
$wpdb->query( 'ROLLBACK;' );
error_log( 'Failed to insert binary content for metadata ID: ' . $metadata_id . '. Error: ' . $wpdb->last_error );
// Optionally, delete the metadata entry if data insertion fails
$wpdb->delete( $table_metadata, array( 'id' => $metadata_id ) );
return false;
}
// Commit transaction
$wpdb->query( 'COMMIT;' );
return $metadata_id;
}
// Example usage within a WordPress AJAX handler or form submission:
/*
if ( isset( $_FILES['my_binary_file'] ) ) {
$new_id = handle_binary_upload( $_FILES['my_binary_file'] );
if ( $new_id ) {
echo "File uploaded successfully with metadata ID: " . $new_id;
} else {
echo "File upload failed.";
}
}
*/
Retrieving Binary Data
Retrieving the data involves querying the metadata table first. If you need the binary content, you then query the data table using the metadata_id. This two-step process is efficient because you can fetch file names, sizes, and other metadata without loading the potentially large binary blobs.
Fetching Metadata Only
/**
* Retrieves metadata for binary files.
*
* @param array $args Optional. Arguments to filter the query.
* e.g., 'user_id', 'file_name', 'id'.
* @return array|null Array of metadata records, or null if none found.
*/
function get_binary_metadata( $args = array() ) {
global $wpdb;
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$query_args = array();
$where_clauses = array();
$where_values = array();
$where_types = array();
if ( ! empty( $args ) ) {
foreach ( $args as $key => $value ) {
// Sanitize keys to prevent SQL injection
$sanitized_key = sanitize_key( $key );
if ( in_array( $sanitized_key, array( 'id', 'user_id', 'file_name', 'mime_type' ) ) ) {
$where_clauses[] = "$sanitized_key = %s"; // Use %s for all to be safe, will cast later
$where_values[] = $value;
$where_types[] = '%s'; // Placeholder for type casting
}
}
}
$sql = "SELECT * FROM $table_metadata";
if ( ! empty( $where_clauses ) ) {
$sql .= " WHERE " . implode( ' AND ', $where_clauses );
}
$sql .= " ORDER BY upload_time DESC";
// Prepare and execute the query
if ( ! empty( $where_values ) ) {
// Cast values based on expected types
for ( $i = 0; $i < count( $where_values ); $i++ ) {
switch ( sanitize_key( array_keys( $args )[ $i ] ) ) {
case 'id':
case 'user_id':
$where_values[ $i ] = intval( $where_values[ $i ] );
$where_types[ $i ] = '%d';
break;
default:
$where_values[ $i ] = sanitize_text_field( $where_values[ $i ] );
$where_types[ $i ] = '%s';
break;
}
}
$sql = $wpdb->prepare( $sql, $where_types );
}
$results = $wpdb->get_results( $wpdb->prepare( $sql, $where_values ) );
if ( $wpdb->last_error ) {
error_log( 'Error fetching binary metadata: ' . $wpdb->last_error );
return null;
}
return $results;
}
// Example: Get all metadata for the current user
// $user_files = get_binary_metadata( array( 'user_id' => get_current_user_id() ) );
// if ( $user_files ) {
// foreach ( $user_files as $file_meta ) {
// echo "File: " . esc_html( $file_meta->file_name ) . " (Size: " . esc_html( $file_meta->file_size ) . " bytes)
";
// }
// } else {
// echo "No files found.";
// }
Fetching Metadata and Binary Content
To retrieve both the metadata and the actual binary content, we’ll perform a JOIN operation. This is generally efficient as the database can optimize the join. However, be mindful of the memory implications when fetching large binary blobs.
/**
* Retrieves metadata and binary content for a specific file.
*
* @param int $metadata_id The ID of the metadata record.
* @return object|null The file record (metadata + content), or null if not found.
*/
function get_binary_content_by_metadata_id( $metadata_id ) {
global $wpdb;
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$table_data = $wpdb->prefix . 'custom_binary_data';
$metadata_id = absint( $metadata_id );
if ( $metadata_id === 0 ) {
return null;
}
$sql = $wpdb->prepare(
"SELECT
m.*,
d.binary_content
FROM
$table_metadata AS m
JOIN
$table_data AS d ON m.id = d.metadata_id
WHERE
m.id = %d",
$metadata_id
);
$file_record = $wpdb->get_row( $sql );
if ( $wpdb->last_error ) {
error_log( 'Error fetching binary content for metadata ID ' . $metadata_id . ': ' . $wpdb->last_error );
return null;
}
return $file_record;
}
// Example: Retrieve and output a PDF file
/*
$file_id_to_retrieve = 123; // Replace with an actual metadata ID
$file_data = get_binary_content_by_metadata_id( $file_id_to_retrieve );
if ( $file_data ) {
header( 'Content-Type: ' . $file_data->mime_type );
header( 'Content-Disposition: inline; filename="' . basename( $file_data->file_name ) . '"' );
header( 'Content-Length: ' . $file_data->file_size );
echo $file_data->binary_content;
exit;
} else {
echo "File not found or an error occurred.";
}
*/
Efficient Querying with UNION and INTERSECT (Conceptual)
While the previous examples focus on direct retrieval, the prompt mentions “Union and Intersection Types.” In a relational database context, this typically refers to set operations on query results. For our binary storage, these operations would apply to the metadata, not the binary content itself. This allows for complex filtering and combination of file sets based on their attributes.
Conceptual Example: Files Uploaded by Two Users (UNION)
Imagine you want to retrieve a list of all files uploaded by either User A or User B. A UNION operation on the metadata table can achieve this. Note that UNION by default removes duplicates; use UNION ALL if you want to keep them.
/**
* Retrieves metadata for files uploaded by either of two specified users.
* Uses UNION to combine results.
*
* @param int $user_id_1 ID of the first user.
* @param int $user_id_2 ID of the second user.
* @return array|null Array of metadata records, or null if none found.
*/
function get_files_by_two_users_union( $user_id_1, $user_id_2 ) {
global $wpdb;
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$user_id_1 = absint( $user_id_1 );
$user_id_2 = absint( $user_id_2 );
if ( $user_id_1 === 0 || $user_id_2 === 0 ) {
return null;
}
// Ensure we don't query the same user twice if IDs are identical
if ( $user_id_1 === $user_id_2 ) {
return get_binary_metadata( array( 'user_id' => $user_id_1 ) );
}
$sql = $wpdb->prepare(
"SELECT id, file_name, mime_type, file_size, upload_time, user_id
FROM $table_metadata
WHERE user_id = %d
UNION
SELECT id, file_name, mime_type, file_size, upload_time, user_id
FROM $table_metadata
WHERE user_id = %d
ORDER BY upload_time DESC",
$user_id_1,
$user_id_2
);
$results = $wpdb->get_results( $sql );
if ( $wpdb->last_error ) {
error_log( 'Error fetching files via UNION: ' . $wpdb->last_error );
return null;
}
return $results;
}
// Example usage:
// $user1_id = 1;
// $user2_id = 5;
// $combined_files = get_files_by_two_users_union( $user1_id, $user2_id );
// if ( $combined_files ) {
// echo "Files from User {$user1_id} or User {$user2_id}:
";
// foreach ( $combined_files as $file ) {
// echo "- " . esc_html( $file->file_name ) . " (by User " . esc_html( $file->user_id ) . ")
";
// }
// }
Conceptual Example: Files Uploaded by User A AND Having a Specific MIME Type (INTERSECT)
An INTERSECT operation (or its equivalent using INNER JOIN or WHERE EXISTS) finds records that satisfy multiple conditions simultaneously. For instance, finding all PDF files uploaded by a specific user.
/**
* Retrieves metadata for files uploaded by a specific user AND having a specific MIME type.
* Uses an INNER JOIN to simulate INTERSECT.
*
* @param int $user_id The user ID.
* @param string $mime_type The MIME type to filter by (e.g., 'application/pdf').
* @return array|null Array of metadata records, or null if none found.
*/
function get_files_by_user_and_mime_intersect( $user_id, $mime_type ) {
global $wpdb;
$table_metadata = $wpdb->prefix . 'custom_binary_metadata';
$user_id = absint( $user_id );
$mime_type = sanitize_mime_type( $mime_type );
if ( $user_id === 0 || empty( $mime_type ) ) {
return null;
}
// Using INNER JOIN is the standard SQL way to achieve INTERSECT logic for this scenario.
// Some databases support INTERSECT directly, but JOIN is more portable.
$sql = $wpdb->prepare(
"SELECT
m.id, m.file_name, m.mime_type, m.file_size, m.upload_time, m.user_id
FROM
$table_metadata AS m
WHERE
m.user_id = %d AND m.mime_type = %s
ORDER BY m.upload_time DESC",
$user_id,
$mime_type
);
$results = $wpdb->get_results( $sql );
if ( $wpdb->last_error ) {
error_log( 'Error fetching files via INTERSECT logic: ' . $wpdb->last_error );
return null;
}
return $results;
}
// Example usage:
// $target_user_id = 1;
// $target_mime = 'application/pdf';
// $pdf_files = get_files_by_user_and_mime_intersect( $target_user_id, $target_mime );
// if ( $pdf_files ) {
// echo "PDF files uploaded by User {$target_user_id}:
";
// foreach ( $pdf_files as $file ) {
// echo "- " . esc_html( $file->file_name ) . "
";
// }
// }
These set operations on metadata allow for sophisticated querying of your custom binary storage, enabling complex filtering and data aggregation without the performance penalty of loading all binary content upfront.